Formatting PL/SQL like SQL

In going through reams of PL/SQL code it dawned on me that procedural language is often formatted differently than structured query language in the same code sample. I decided to take a shot at formatting procedural language code like SQL and I like the result.

declare
	var1 number(9) 	:= 0;
	var2 number(9) 	:= 0;
	var3 number(9) 	:= 0;
	var4 number(9) 	:= 0;
	var5 number(9) 	:= 0;
	var6 number(9) 	:= 0;
	var7 number(9) 	:= 0;
	var8 number(9) 	:= 0;
	var9 number(9) 	:= 0;
begin
	if 	var1 	= var2
	and 	var3 	= var4
	or 	var5 	= var6
	then
		while 	var1 	< var9
		and 	var2 	is null
		or 	var4 	is not null
		loop
			select 	a.col1
			into 	var6
			from 	table1 a
			, 	table2 b
			where 	a.col1 	= b.col1
			and 	a.col2 	= var7
			or 	b.col2 	= var8;

			var1 	:= var1 + 1
		end loop;
	elseif 	var1 	= var2
	and 	var1 	<> var3
	or 	var1 	<> var4
	then
		insert
		into 	table3
		( 	col1
		, 	col2
		, 	col3
		)
		values
		( 	var1
		, 	var2
		, 	var3
		);
	else
		var9 	:= 100;
		if 	var4 	is not null
		then
			var8 	:= 100;
		endif;
	endif;
end

Note that I am using ten character tabs throughout the code and formatting and aligning as I would with SQL. I find the conditional structures in the IF and WHILE statements much clearer this way.

*Footnote: I recently used the TOAD formatter command and have found it surpasses my own standard for formatting. If you have TOAD I highly recommend the use of the formatter for all code. The saving in effort for the maintenance people that follow in your development footsteps is well worth it.

Code Slobs

I have been having quite a time at work these days as I have been assigned the task of doing an impact analysis on the addition of a column into the database schema.  I have found myself without documentation and having to go into the source of hundreds of programs and scripts looking for the implications of the change.  The code is completely unformatted and trying to rectify the situation to save my bleeding eyes has been hell.

What obstacles do I face?  My Team Leader says she doesn’t look at code so she doesn’t care what format it is in.  My Senior Oracle Developer says he formats all his code, but he doesn’t consider code formatting important.   The remaining development team writes functioning code, but has no education in how to format PL/SQL so it’s readable.  In otherwords, I am surrounded by Code Slobs.

How do you deal with code slobs?  First, automation.  I have persuaded all of them to use the TOAD code formatter in the future so people without TOAD can make some sense of their product when doing maintenance.  Second, roll up your sleeves and clean up the legacy code.  I have to go through the code looking for the impact of a column change anyways, so along the way I am formatting all the code I read.  I know there will be more impact analysis tasks and code maintenance, so I am hoping to make it easier for future code readers and maintenance programmers.

So, look out code slobs, I am going into every nook and cranny and sorting out the detrius,  so someone, anyone can have a better chance of giving your work a longer life span, simply by making it readable.