Oracle11gRelease1 (11.1) PL/SQL _ understand the Record type

If you create a records, you must first define a RECORD type and then declare the variable with this type. You can also create or search for a table, view, or PLSQL cursor.

If you create a records, you must first define a RECORD type and then declare the variable with this type. You can also create or search for a table, view, or PL/SQL cursor.

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

If you create a records, you must first define a RECORD type and then declare the variable with this type. You can also create or search for a table, view, or PL/SQL cursor. In short, you want to create a matched Record using the % ROWTYPE attribute.

You can define the RECORD type in the Declaration section of any PL/SQL block, subroutine, or package. When you customize the RECORD type, you cannot specify a not null constraint on the field or give their default values.

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

DECLARETYPE DeptRecTypISRECORD (deptid NUMBER (4)NOT NULL: = 99, dname parameters. department_name % TYPE, loc parameters. location_id % TYPE, region regions % ROWTYPE); dept_rec DeptRecTyp;BEGINDept_rec.dname: ='Purchasing';END;/

Example 2: demonstrate the Declaration and initialize the Record type

: =-1, name VARCHAR2 (64)NOT NULL: ='[Anonymous]');-- Declare record variables of the types declaredRec1 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_tISRECORD (first_name employees. first_name % TYPE, last_name employees. last_name % TYPE, rating NUMBER); rec4 rec4_t;BEGIN-- Read and write fields using dot notationRec1.field1: ='Yesterday'; Rec1.field2: = 65; rec1.field3: = TRUNC (SYSDATE-1 );-- Didn't fill name field, so it takes default valueDBMS_OUTPUT.PUT_LINE (rec2.name );END;/

If you store a Record in the database, you can specify it in the INSERT or UPDATE statement as long as its field matches the column in the table.

You can use % TYPE to specify the column TYPE of the table corresponding to the Record domain TYPE. Even if the column type changes, your code can still run. For example, the length of the VARCHAR2 field or the precision of the NUMBER field is increased.

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 % ROWTYPE instead

-- You only want some columns. -- Declaring cursor doesn't run query or affect performance. CURSOR IS SELECT FROM -- Use % TYPE in field declarations to avoid problems if -- The column types change. IS -- Write each field name, specifying type directly -- (Clumsy and unmaintainable for working with table data -- Use only for all-PL/SQL code ). IS BEGIN NULL END

PL/SQL can define records that contain objects, sets, and other records (built-in records. However, Record cannot be an attribute of the object type.

If a Record is declared as a row of the database table, the column does not need to be listed and the % ROWTYPE attribute is used.

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, apply % ROWTYPE to the view or cursor.

% ROWTYPE because C1Department_id, department_name, location_idParameters; rec1 c1 % ROWTYPE;TYPE DeptRec2RECORD (dept_id departments. department_id % TYPE, dept_name departments. department_name % TYPE, dept_loc departments. location_id % TYPE); rec2 DeptRec2;TYPE DeptRec3RECORD (dept_id NUMBER, dept_name VARCHAR2 (14), dept_loc VARCHAR2 (13); rec3 DeptRec3; ;;/