Parameter passing

In NZPLSQL, parameters are not named. Instead, only the data types are passed, and parameters are referenced by their position.

To name parameters, use the ALIAS FOR syntax. For example:

CREATE OR REPLACE PROCEDURE p1 (int, varchar(ANY)) RETURNS int 
LANGUAGE NZPLSQL AS
BEGIN_PROC
DECLARE
    pId ALIAS FOR $1; 
    pName ALIAS FOR $2;
BEGIN
    INSERT INTO t1 SELECT * FROM t2 WHERE id = pId;

The example highlights the recommended convention to name parameters with a “p” prefix to differentiate them from variables. This convention is helpful because parameters are constant and cannot be modified. If a parameter must be updated (for example, to assign it a default value if it is null), assign the parameter to a variable and update the variable. For example:

DECLARE
    pId ALIAS FOR $1;
    vID integer;

BEGIN
    vId := ISNULL(pId, 0);
    INSERT INTO t1 SELECT * FROM t2 WHERE id = vId;