Add a parameter list

When you create an SPL routine, you can define a parameter list so that the routine accepts one or more arguments when it is invoked. The parameter list is optional.

A parameter to an SPL routine must have a name and can be defined with a default value. The following are the categories of data types that a parameter can specify:
  • Built-in data types
  • Opaque data types
  • Distinct data types
  • Row types
  • Collection types
  • Smart large objects (CLOB and BLOB)
The parameter list cannot specify any of the following data types directly:
  • SERIAL
  • SERIAL8
  • BIGSERIAL
  • TEXT
  • BYTE

For the serial data types, however, a routine can return numerically equivalent values that are cast to a corresponding integer type (INT, INT8, or BIGINT). Similarly, for a routine to support the simple large object data types, the parameter list can include the REFERENCES keyword to return a descriptor that points to the storage location of the TEXT or BYTE object.

The following figure shows examples of parameter lists.
Figure 1: Examples of different parameter lists.
CREATE PROCEDURE raise_price(per_cent INT);

CREATE FUNCTION  raise_price(per_cent INT DEFAULT 5);

CREATE PROCEDURE update_emp(n employee_t);
CREATE FUNCTION  update_nums( list1 LIST(ROW (a VARCHAR(10),
                                              b VARCHAR(10),
                                              c INT) NOT NULL ));
When you define a parameter, you accomplish two tasks at once:
  • You request that the user supply a value when the routine is executed.
  • You implicitly define a variable (with the same name as the parameter name) that you can use as a local variable in the body of the routine.

If you define a parameter with a default value, the user can execute the SPL routine with or without the corresponding argument. If the user executes the SPL routine without the argument, the database server assigns the parameter the default value as an argument.

When you invoke an SPL routine, you can give an argument a NULL value. SPL routines handle NULL values by default. However, you cannot give an argument a NULL value if the argument is a collection element.