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.

formatting plsql like sql formatting plsql like sql formatting plsql like sql

Advertisements

2 Responses to “Formatting PL/SQL like SQL”

  1. johny Says:

    hi,

    kindly let us know how to use toad formatter….also is there any seetings we can do for naming convetions also…for example constants declared as c_xxx

    thanks in advance
    johny

  2. grant czerepak Says:

    Hello johny,

    TOAD stands for “Tool for Application Developers” and is a product of TOADSoft.

    To use the TOAD formatter right click on the code, select “Formatting tools ” on the menu and “Format Code”.

    Your code will be formatted automatically.

    As far as naming conventions go, you will have to come up with your own. There are no options in TOAD that support this.

    Grant.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: