PL/SQL Parameters

by | Jun 16, 2015

In Clean Code, Martin recommends that:

The ideal number of arguments for a function is zero (niladic). Next comes one (monadic), followed closely by two (dyadic). Three arguments (triadic) should be avoided where possible. More than three (polyadic) requires very special justification—and then shouldn’t be used anyway.

Martin works mostly in modern object-orientated languages, so his targets aren’t reasonable for languages that lack means of collecting parameters, for example T-SQL stored procedures. Sure it is possible to pass a multi-dimensional array whose first pair of dimensions are a key value-pairing to a T-SQL procedure, but that makes the code vastly more difficult to understand. (If you understood that sentence, you really ought to try writing SQL Server stored procedures in Common Language Runtime (CLR). CLR is much easier than mastering all of the hacks that T-SQL forces on you.) Our objective when writing code is always to maximize test-ability and read-ability, which typically means Keeping It Short and Simple. In short, the reasonable upper limit of parameters varies widely between programming languages.
However, parameters are always a source of defects. So we should strive to minimize and control parameters.
I recommend two PL/SQL language features in particular.
First, if you’re being paid to write code you should only use named parameters. The other parameter passing modes are the source of many errors. Do this:
[code language=”sql” highlight=”7″] DECLARE
desired_length PLS_INTEGER := 2;
entire VARCHAR2(10) := ‘ABCDEFG’;
start_at PLS_INTEGER := 3;
part VARCHAR2(10);
part := substr(str1 => entire, pos => start_at, len => desired_length);
[/code] Second, whenever possible pass PL/SQL records. I’ve wasted countless hours in my career working with code that passes three or four parameters that describe some “thing” which usually is a row in a table or view. Not only is passing the wrong thing easy to do and hard to spot, but adding (or removing) another column/parameter takes a lot more time.
[code language=”sql” highlight=”2,3″] CREATE OR REPLACE FUNCTION do_not_do_this
(i_table_name VARCHAR2
,i_column_name VARCHAR2
counter PLS_INTEGER;
SELECT count(*)
INTO counter
FROM user_tab_columns
WHERE table_name = i_table_name
AND column_name = i_column_name;
RETURN counter > 0;
END do_not_do_this;
[/code] Code that looks like this is brittle. If I want to add another parameter, say the table owner, then I have to modify the function, the caller, and often a number of other procedures in the call stack until that value is either available or makes sense to obtain. That problem doesn’t happen if we pass a collection.
[code language=”sql” highlight=”2″] CREATE OR REPLACE FUNCTION pass_parameters_like_this
counter PLS_INTEGER;
SELECT count(*)
INTO counter
FROM all_tab_columns
WHERE table_name = i_column.table_name
AND column_name = i_column.column_name
AND owner = i_column.owner;
RETURN counter > 0;
END pass_parameters_like_this;
[/code] In my professional opinion, the most productive innovation of object-oriented languages is that they naturally lead to passing an entire set of data, like the pass_parameters_like_this example rather than as a series of possibly unrelated simple values like the do_not_do_this example.
If you are passing parameters together and they aren’t actually a row somewhere, you should wonder why and try putting them into a table. Data driven code is usually simpler. Refactor with a configuration table and see if that helps. In any case, you should define a record type for parameters that are related but aren’t rows like this:
[code language=”sql”] TYPE ExampleRecordType IS RECORD (
column_id PLS_INTEGER,
owner VARCHAR2(30) NOT NULL,
table_name VARCHAR2(30) NOT NULL,
column_name VARCHAR2(30) NOT NULL
[/code] – Steven



  1. PL/SQL Parameters – Steven Ensslen - […] Read the rest on the Optimal BI blog. […]
Submit a Comment

Your email address will not be published. Required fields are marked *