Records in NZPLSQL

In NZPLSQL, a record can be used in a SELECT operation or FOR statement to hold one database row.

A variable of type RECORD can be used for different selections. Accessing an empty record, or attempting to assign a value to a field of an empty record, results in a runtime error.

A record can be either structured or unstructured:

An unstructured record does not specify a row type, and can be used when the structure of the row being read is unknown. Issue the following statement to declare an unstructured record:
```
recordname RECORD;
```
where recordname represents the name of the record.
A structured record explicitly specifies a row type by means of either an AS keyword or the %ROWTYPE attribute. Issue one of the following statements to declare a structured record:
```
recordname RECORD AS row_type;
recordname tablename%ROWTYPE;
```
where row_type represents the name of a predefined row type and tablename represents the name of a table whose rows structure corresponds to the desired row type.

To define a row type, issue a CREATE TYPE statement, for example:

CREATE TYPE DEPTROW AS ROW (DEPTNO   VARCHAR(3),
                            DEPTNAME VARCHAR(29),
                            MGRNO    CHAR(6),
                            ADMRDEPT CHAR(3),
                            LOCATION CHAR(16))

Assigning a complete selection into a record or row

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. If the query returns several rows, only the first row is moved into the target fields; all others are discarded.

Note: This interpretation of SELECT INTO is different from that of SQL, in which the INTO target is a newly created table. To create a table from a SELECT result from within an NZPLSQL procedure, use a CREATE TABLE AS SELECT statement.

If the target is a row, record, or variable list, the selected values must exactly match the structure of the target. The FROM keyword can be followed by any valid qualification, grouping, or sorting clauses that can be specified for a SELECT statement.

After a record or row is assigned to a record, you can use dot notation to access the fields of that record. For example, to access the first_name and last_name fields in the record users_rec:

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

Checking whether a value was assigned to a record

There are several ways to check whether a value was assigned to a record by a SELECT INTO statement:

Use the special variable named FOUND of type Boolean immediately after the SELECT INTO statement. For example:

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

Use ROW_COUNT >= 1 instead of FOUND.

Use IS NULL or ISNULL conditionals to test whether a record or row is NULL. For example:

DECLARE
    users_rec RECORD AS users_rowtype;
    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;

Iterating through the results of a query

There are two methods for iterating through the results of a query and manipulating the result data. In both methods, the record or row is assigned all the rows that are returned by the select clause and the loop body runs for each row.

Use a FOR...IN statement

The syntax of a FOR...IN statement is:

[<<label>>]
FOR record_or_row IN select_clause 
LOOP
  statements
END LOOP;

A FOR...IN statement can use either a structured or an unstructured record type. If the loop is terminated with an EXIT statement, the last assigned row is still accessible.

For example:

DECLARE
    mviews RECORD AS cs_materialized_views_rowtype;
    -- this record is usable ONLY for the cs_materialized_views table
BEGIN
    CALL cs_log('Refreshing materialized views...');
    FOR mviews IN SELECT * FROM cs_materialized_views ORDER BY sort_key 
    LOOP
    -- Now "mviews" has one record from cs_materialized_views
        RAISE EXCEPTION, 'Can't execute SQL while processing SQL for %',
        mview.my_name;
    END LOOP;
    CALL cs_log('Done refreshing materialized views.');
    return 1;
end;

Use a FOR...IN EXECUTE statement

The syntax of a FOR...IN EXECUTE statement is similar to that of a FOR...IN statement, except that the source SELECT statement is specified as a string expression:

[<<label>>]
FOR record_or_row IN EXECUTE 'select_clause' 
LOOP 
  statements
END LOOP;

A FOR...IN EXECUTE statement must use a structured record type. A FOR...IN EXECUTE statement lets you create a dynamic statement. For example, you can pass in the SELECT statement as a VARCHAR string into the NZPLSQL procedure.