Assignments

You can use the following query to assign a complete selection into a record or row:

SELECT expressions INTO target FROM ...;

The target value can be a record, a row variable, or a comma-separated list of variables and record-fields or row-fields. This interpretation is different from the SQL interpretation of SELECT INTO, which is that the INTO target is a newly created table. (If you want to create a table from a SELECT result inside an NZPLSQL procedure, use the equivalent syntax CREATE TABLE AS SELECT.)

If a row or a variable list is used as the target, the selected values must exactly match the structure of the target or targets, or a runtime error occurs. The FROM keyword can be followed by any valid qualification, grouping, or sorting that can be given for a SELECT statement.

After a record or row is assigned to a RECORD variable, you can use the "." (dot) notation to access fields in that record as follows:

DECLARE
    users_rec RECORD;
    full_name varchar;
BEGIN
    SELECT * INTO users_rec FROM users WHERE user_id=3;
  full_name := users_rec.first_name || ' ' || users_rec.last_name;

There is a special variable named FOUND of type boolean that can be used immediately after a SELECT INTO to check whether an assignment was successful. The following example uses the NOT FOUND form to raise an exception if a SELECT INTO statement does not match on the requested input name:

SELECT * INTO myrec FROM EMP WHERE empname = myname;
IF NOT FOUND THEN
    RAISE EXCEPTION 'employee % not found', myname;
END IF;

FOUND is very similar to ROW_COUNT. For example, the following statement:

IF FOUND

is equivalent to this statement:

IF ROW_COUNT >= 1

You can also use the IS NULL (or ISNULL) conditionals to test whether a RECORD or ROW is NULL. If the selection returns multiple rows, only the first is moved into the target fields. All others are silently discarded. For example:

DECLARE
    users_rec RECORD;
    full_name varchar;
BEGIN
    SELECT * INTO users_rec FROM users WHERE user_id=3;
    IF users_rec.homepage IS NULL THEN
        -- user entered no homepage, return "http://"
        return 'http://';
    END IF;
END;