Declaration

Variable declarations

All variables used in a block must be declared in the declarations section. Two exceptions apply automatically: the loop variable of a FOR loop over an integer range is declared as an integer, and the loop variable of a FOR loop over a cursor result is declared as a record variable.

PL/SQL variables accept any SQL data type, including INTEGER, VARCHAR, and CHAR.

Sample variable declarations

user_id integer;
quantity numeric(5);
url varchar;
myrow tablename%ROWTYPE;
myfield tablename.columnname%TYPE;
arow RECORD;

Syntax

name [ CONSTANT ] type [ COLLATE collation_name ] [ NOT NULL ] [ { DEFAULT | := | = } expression ];

The equal sign (=) can be used to replace the := operator, which is compatible with PL/SQL.

The default value is evaluated each time the block is entered, not at compile time. For example, assigning now() to a TIMESTAMP variable produces the timestamp at function invocation, not when the function was compiled.

Examples

quantity integer DEFAULT 32;
url      varchar := 'http://mysite.com';
user_id  CONSTANT integer := 10;

Declare function parameters

PL/SQL names function parameters $1, $2, and so on. Declare an alias to make the code more readable. Both the alias and the $n identifier can reference the parameter value.

CREATE FUNCTION sales_tax(subtotal real) RETURN real IS
BEGIN
    RETURN subtotal * 0.06;
END;

name ALIAS FOR $n;

CREATE FUNCTION sales_tax(real) RETURN real IS
DECLARE
    subtotal ALIAS FOR $1;
BEGIN
    RETURN subtotal * 0.06;
END;

CREATE FUNCTION instr(varchar, integer) RETURN integer IS
DECLARE
    v_string ALIAS FOR $1;
    index    ALIAS FOR $2;
BEGIN
    -- Compute by using v_string and index.
END;

CREATE FUNCTION concat_selected_fields(in_t sometablename) RETURN text IS
BEGIN
    RETURN in_t.f1 || in_t.f3 || in_t.f5 || in_t.f7;
END;

CREATE FUNCTION sales_tax(subtotal real, OUT tax real) IS
BEGIN
    tax := subtotal * 0.06;
END;

CREATE FUNCTION sum_n_product(x int, y int, OUT sum int, OUT prod int) IS
BEGIN
    sum  := x + y;
    prod := x * y;
END;

CREATE FUNCTION extended_sales(p_itemno int)
RETURN TABLE(quantity int, total numeric) IS
BEGIN
    RETURN QUERY SELECT s.quantity, s.quantity * s.price FROM sales AS s
                 WHERE s.itemno = p_itemno;
END;

CREATE FUNCTION add_three_values(v1 anyelement, v2 anyelement, v3 anyelement)
RETURN anyelement IS
DECLARE
    result ALIAS FOR $0;
BEGIN
    result := v1 + v2 + v3;
    RETURN result;
END;

CREATE FUNCTION add_three_values(v1 anycompatible, v2 anycompatible, v3 anycompatible)
RETURN anycompatible IS
BEGIN
    RETURN v1 + v2 + v3;
END;

SELECT add_three_values(1, 2, 4.7);

CREATE FUNCTION add_three_values(v1 anyelement, v2 anyelement, v3 anyelement,
                                 OUT sum anyelement)
IS
BEGIN
    sum := v1 + v2 + v3;
END;

Declare aliases

newname ALIAS FOR oldname;

The ALIAS FOR syntax works for any variable, not just function parameters. Its primary use is renaming predetermined names such as NEW or OLD in trigger functions.

DECLARE
    prior   ALIAS FOR old;
    updated ALIAS FOR new;

Avoid using ALIAS FOR in an unrestricted way — aliasing the same object under multiple names leads to confusion. Limit its use to overriding predetermined names.

Copy types (%TYPE)

variable%TYPE

%TYPE borrows the data type of a variable or table column. Use it to declare variables that hold database values without hard-coding the data type.

user_id users.user_id%TYPE;

If the referenced column's type changes later, the function definition requires no update. %TYPE is especially useful in polymorphic functions, where the data type of an internal variable may differ between invocations.

Use row types (%ROWTYPE)

name table_name%ROWTYPE;
name composite_type_name;

A row variable holds a complete row from a SELECT or FOR query result, provided the column set matches the declared type. Access individual fields using dot notation: rowvar.field.

Each table has an associated composite type with the same name, so specifying %ROWTYPE is optional in PolarDB. Use it anyway — it makes the code portable across databases.

Function parameters can be composite types representing complete table rows. In that case, the $n identifier is a row variable and its fields are accessible as $1.user_id.

Example

CREATE FUNCTION merge_fields(t_row table1) RETURN text IS
DECLARE
    t2_row table2%ROWTYPE;
BEGIN
    SELECT * INTO t2_row FROM table2 WHERE ... ;
    RETURN t_row.f1 || t2_row.f3 || t_row.f5 || t_row.f7;
END;

SELECT merge_fields(t.*) FROM table1 t WHERE ... ;

Use record types (RECORD)

name RECORD;

Record variables are similar to row variables but have no predefined structure. The structure is set by the first SELECT or FOR assignment and can change with each subsequent assignment. Accessing a field before any value is assigned causes a runtime error.

RECORD is a placeholder, not a true data type. A function declared with return type record is different from a record variable — when the function is called and parsed, its row structure is determined and fixed. A record variable's structure can change at any time.

Collate PL/SQL variables

When a function has parameters of collatable data types, PolarDB identifies a collation per invocation based on the collations of the actual arguments. If no implicit collation conflict exists, that collation is applied to all collatable parameters and local variables automatically.

CREATE FUNCTION less_than(a text, b text) RETURN boolean IS
BEGIN
    RETURN a < b;
END;

-- Uses the common collation of text_field_1 and text_field_2:
SELECT less_than(text_field_1, text_field_2) FROM table1;

-- Uses the C collation:
SELECT less_than(text_field_1, text_field_2 COLLATE "C") FROM table1;

Because the identified collation also applies to local variables, the following function behaves identically to the one above:

CREATE FUNCTION less_than(a text, b text) RETURN boolean IS
DECLARE
    local_a text := a;
    local_b text := b;
BEGIN
    RETURN local_a < local_b;
END;

If no parameters have a collatable type, or if no common collation can be identified, variables use the default collation of their data type (usually the database default). Variables of domain types may have different defaults.

Override the collation in a declaration

DECLARE
    local_a text COLLATE "en_US";

Override the collation in an expression

CREATE FUNCTION less_than_c(a text, b text) RETURN boolean IS
BEGIN
    RETURN a < b COLLATE "C";
END;

An explicit COLLATE clause overrides all collations associated with columns, parameters, and local variables in the expression — the same behavior as in plain SQL.