Oracle 11g Release 1 (11.1) PL/SQL _ understand the Record type

Content

• Define and declare a Record
• Record as a subroutine parameter and function return value
• Assign values to Record
• Compare Record
• Insert Record to database
• Update Record to database
• Constraints on insert and update Record
• Put the query data in Record

Define and declare a Record

To create a records, you must first defineRECORDType, and then declare the variable. You can also create or search for a table, view, or PL/SQL cursor.% ROWTYPEAttribute to create a matchedRecord.

You can define it in the declaration part of any PL/SQL block, subroutine or package.RECORDType. When you customizeRECORDType, you cannot specifyNOT NULLOr give their default values.

Example 1: demonstrate the Declaration and initialize a simple Record type

DECLARE

   TYPE DeptRecTyp IS RECORD (

      deptid NUMBER(4) NOT NULL := 99,

      dname  departments.department_name%TYPE,

      loc    departments.location_id%TYPE,

      region regions%ROWTYPE );

   dept_rec DeptRecTyp;

BEGIN

   dept_rec.dname := 'PURCHASING';

END;

/

Example 2: demonstrate the Declaration and initialize the Record type

DECLARE

-- Declare a record type with 3 fields.

  TYPE rec1_t IS RECORD

    (field1 VARCHAR2(16), field2 NUMBER, field3 DATE);

-- For any fields declared NOT NULL, you must supply a default value.

  TYPE rec2_t IS RECORD (id INTEGER NOT NULL := -1, 

  name VARCHAR2(64) NOT NULL := '[anonymous]');

-- Declare record variables of the types declared

  rec1 rec1_t;

  rec2 rec2_t;

-- Declare a record variable that can hold

-- a row from the EMPLOYEES table.

-- The fields of the record automatically match the names and

-- types of the columns.

-- Don't need a TYPE declaration in this case.

  rec3 employees%ROWTYPE;

-- Or mix fields that are table columns with user-defined fields.

  TYPE rec4_t IS RECORD (first_name employees.first_name%TYPE,

                         last_name employees.last_name%TYPE,

                         rating NUMBER);

  rec4 rec4_t;

BEGIN

-- Read and write fields using dot notation

  rec1.field1 := 'Yesterday';

  rec1.field2 := 65;

  rec1.field3 := TRUNC(SYSDATE-1);

-- Didn't fill name field, so it takes default value

  DBMS_OUTPUT.PUT_LINE(rec2.name);

END;

/

If you storeRecordYou canINSERTOrUPDATEStatement, as long as its field matches the column of the table.

You can use% TYPETo specifyRecordThe column type of the table corresponding to the field type. Even if the column type changes, your code can still run. For exampleVARCHAR2The length of the field, orNUMBERField precision.

Example 3: Use % ROWTYPE to declare a Record to save the information of the department table.

DECLARE

-- Best: use %ROWTYPE instead of specifying each column.

-- Use <cursor>%ROWTYPE instead of <table>%ROWTYPE because 

-- you only want some columns.

-- Declaring cursor doesn't run query or affect performance.

   CURSOR c1 IS

     SELECT department_id, department_name, location_id

     FROM departments;

   rec1 c1%ROWTYPE;

-- Use <column>%TYPE in field declarations to avoid problems if 

-- the column types change.

   TYPE DeptRec2 IS RECORD

     (dept_id   departments.department_id%TYPE,

      dept_name departments.department_name%TYPE,

      dept_loc  departments.location_id%TYPE);

   rec2 DeptRec2;

-- Write each field name, specifying type directly

-- (clumsy and unmaintainable for working with table data

-- use only for all-PL/SQL code).

   TYPE DeptRec3 IS RECORD (dept_id NUMBER,

                            dept_name VARCHAR2(14),

                            dept_loc VARCHAR2(13));

   rec3 DeptRec3;

BEGIN

   NULL;

END;

/

PL/SQL can defineRecord. HoweverRecordIt cannot be an object type attribute.

If you declareRecord, Indicates a row in the database table. columns are not required.% ROWTYPEAttribute.

After adding columns to the table, your code can still run. If you want to represent a subset of a table column or a column of different tables, you can define a view or declare a cursor to select the column on the right and execute any required connections, and then apply it to the view or cursor.% ROWTYPE.

 

1 2 3 Next Page