Let’s talk about parameters. You can’t talk about parameters without talking about procedures and functions (well, ok, also cursors can have parameters). But you could talk about procedures and functions without talking about parameters. Because these subprograms could have no parameter list. Example:
function right_now return date is begin return sysdate; end;
And I suppose in theory you could always avoid using parameters by relying entirely on package-based globals. Example:
create or replace package my_globalized_parameters is
g_date date;
g_id number;
end;
/
create or replace procedure do_stuff_with_date_and_id is
begin
if my_globalized_parameters.g_date < sysdate then
dbms_output.put_line(my_globalized_parameters.g_date);
else
dbms_output.put_line(g_id);
end if;
end;
/
begin
my_globalized_parameters.g_date := sysdate + 10;
my_globalized_parameters.g_id := 100;
do_stuff_with_date_and_id;
end;
/
But…oh my lordy! Hopefully you are all well aware of the dangers of this approach. Generally, you do not want to refer to global variables within your programs; these are hidden dependencies that make it difficult and dangerous to use those programs.
Much better is to make explicit via the parameter exactly what must be passed into the procedure for it work properly, as in:
create or replace procedure do_stuff_with_date_and_id (
p_date in date, p_id in number)
is
begin
if g_date < sysdate then
dbms_output.put_line(p_date);
else
dbms_output.put_line(p_id);
end if;
end;
/
Here’s some nomenclature that we use to talk about parameters:
- formal parameter – the parameter defined in the subprogram DDL
- argument (or argument value) – the literal, expression, variable or constant that is passed to the subprogram for a particular formal parameter
- pass by reference – “The compiler passes the subprogram a pointer to the actual parameter. The actual and formal parameters refer to the same memory location.” (from the doc)
- pass by value – The compiler assigns the value of the actual parameter to the corresponding formal parameter. The actual and formal parameters refer to different memory locations. (from the doc)
Parameters can have one of three modes:
- in – the argument is passed by reference and can only be read (not changed) (the default)
- out – a value is passed (by value) back to the invoker of the subprogram
- in out – the argument is passed by value to the subprogram and can then be modified and passed back to the invoker
Here are some tips I cover in the video regarding parameters:
- Use the nocopy hint to avoid the copy-in/copy-out semantics of the “pass by value” in PL/SQL.
- Whenever possible, preserve backward compatibility when adding new parameters to an existing program (with a default value or overloading).
- Related to that, always append new parameters to the end of the existing parameter list.
- Interestingly enough, an out parameter can be read inside your subprogram.
- If you don’t assign a value to an out parameter, then it is set to null upon return of control to the invoker.
- You can associate arguments with formal parameters with either positional or named notation.
Resources:
