Netezza RECORD Type Variable, Usage and Examples

  • Post author:
  • Post last modified:June 6, 2018
  • Post category:Netezza
  • Reading time:3 mins read

Just like other procedural language, Netezza supports RECORD type variables. Netezza RECORD type variable are similar to row types, but they have no predefined structure associated with it. You can use the record type only inside Netezza stored procedures (SP). They are used in selections and FOR loops to hold one database row from a SELECT operation.

Read about Netezza Stored Procedure here:

Netezza RECORD type Variable Declaration

Below is the syntax for declaring Netezza RECORD types inside stored procedures:

name RECORD;

RECORD types variable can only be assigned at the run-time.

Once the RECORD variable is declared, 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.

Netezza RECORD type Variable Examples

After a record or row is assigned to a RECORD variable, you can use the “.” (dot) notation to access fields:

DECLARE
rec RECORD;
name varchar;
BEGIN
SELECT * INTO rec FROM patients WHERE patient_id=1001;
name := rec.first_name || ' ' || rec.last_name;

Above Netezza procedure code snippet demonstrates how RECORD type variables are used.

Netezza RECORD Type Variable Names

Below are the variable names that you can use along with RECORD types variables. These variables are useful when handling exceptions within stored procedures.

  • FOUND – It is a Boolean variable that can be used immediately after a SELECT INTO to check whether an assignment was successful.
  • NOT FOUND – Used to raise an exception if a SELECT INTO statement does not match on the requested input name
  • ROW_COUNT – Count number of values in RECORD type variable
  • IS NULL – You can use this conditionals to test whether a RECORD or ROW is NULL

For example, below code snippet uses the above mentioned record type NOT FOUND variable to raise exception if employee available:

SELECT * INTO emprec FROM EMP WHERE emp_name = 'Jhon';
IF NOT FOUND THEN
RAISE EXCEPTION ’employee % not found’, emprec;
END IF;

Below is the working example of using RECORD TYPE:

CREATE or REPLACE PROCEDURE NZ_RECTYPE_SP()
RETURNS BOOLEAN
LANGUAGE NZPLSQL AS
BEGIN_PROC
DECLARE
rec RECORD;
rec2 RECORD;
form_type char(255);
BEGIN
SELECT * INTO rec from PAT_DTLS_LOAD where SK = 123;
form_type := rec.PHONE_NO || ' ' || rec.NAME;
RAISE NOTICE 'Name: %', form_type;
SELECT * INTO rec2 from PAT_DTLS_LOAD where SK = 111;
IF NOT FOUND THEN
RAISE EXCEPTION 'SK % not found', rec2.sk;
END IF;
EXCEPTION WHEN OTHERS
THEN RAISE NOTICE 'ERROR: %', SQLERRM;
RETURN TRUE;
END;
END_PROC;
Tags: ,