PL/SQL nested record and record set

Multiple unrelated columns are combined to form a PL/SQL record type, which can be processed as a whole. In addition, PL/SQL record types can be performed
Nesting and defining Union arrays and nested tables based on PL/SQL records. This article first reviews several declarations of PL/SQL records. Next, it mainly describes the nesting of PL/SQL records and
Set of records.
For PL/SQL record syntax and PL/SQL record usage in SQL, see PL/SQL --> PL/SQL record

1. The following example also describes the table-based, cursor-based, and user-defined record declare rec_tab dept % rowtype; --> Use rowtype Based on the table type to declare the record variable v_counter pls_integer: = 0; cursor cur_tab is --> declare the cursor select dname, LOC from Dept; rec_cur_tab cur_tab % rowtype; --> Use rowtype to declare the record variable type dept_rec_type is record based on the defined cursor --> User-Defined record type (dname Dept. dname % Type --> you can use the type attribute or custom data type, Loc Dept. loc % type); dept_rec dept_rec_type; --> declare the record variable begin select based on the custom record type * Into rec_tab --> Use select into to assign values to record variables from Dept where deptno = 10; dbms_output.put_line ('------- first print record based on table --------'); dbms_output.put_line ('record is '| rec_tab.dname |', '| rec_tab.loc); open cur_tab; dbms_output.put_line (' ------- next print record based on cursor --------'); loop fetch cur_tab into rec_cur_tab; --> Use fetch into to assign values to the record variable exit when cur_tab % notfound; V_counter: = v_counter + 1; dbms_output.put_line ('record '| v_counter |' is '| rec_cur_tab.dname |', '| rec_cur_tab.loc); End loop; close cur_tab; select dname, loc --> assign an into dept_rec from Dept where deptno = 20 to the custom record variable; dbms_output.put_line ('------- finally print record based on user defined record --------'); dbms_output.put_line ('record is '| dept_rec.dname |', '| dept_rec. Loc); end; 2. Record assignment and reference declare type rec1_t is record --> declare the custom record type (field1 varchar2 (16), field2 date ); type rec2_t is record --> declare the custom record type (ID integer not null: =-1 --> note: in this case, use the not null constraint, so assign the initial value; otherwise, an error is reported, name varchar2 (64) not null: = '[anonymous]'); rec1 rec1_t; --> declare rec1 and rec2 rec2_t; begin rec1.field1: = 'yesterday'; --> when assigning values and referencing values, rec1.field2: = trunc (sysdate-1 ); Dbms_output.put_line ('rec1 values are '| rec1.field1 |', '| rec1.field2); dbms_output.put_line ('rec2 values is' | rec2.name); end; 3. Assign the default value declare type recordtyp is record (field1 number, field2 varchar2 (32) default 'something'); rec1 recordtyp; rec2 recordtyp; begin -- the following value is rec1.field1: = 100 for the variable rec1; rec1.field2: = 'something else'; -- If rec1 is restored to the original state by assigning the value to rec1 using the variable rec2, that is, field1 is null., Field2 is something rec1: = rec2; dbms_output.put_line ('field1 = '| nvl (to_char (rec1.field1),' <null> ') | ', field2 = '| rec1.field2); end; 4. Pass declare type emp_rec_type is record --> Custom record type (ENO number (6 ), esal number (8, 2), ename varchar2 (10); emp_info emp_rec_type; --> declare the record type variable procedure raise_salary (emp_info in out emp_rec_type) is --> the local process is used to increase employee salaries. The parameter is of the In out record type. Begin update EMP set sal = Sal + Sal * emp_info.esal where empno = emp_info.eno returning ename, Sal --> use the returning clause to assign the ename and updated salary to the record variable into emp_info.ename, emp_info.esal; end raise_salary; begin --> main program block emp_info.eno: = 7788; --> assign values to record variables. In this case, emp_info.ename is null and emp_info.esal: = 0.5; raise_salary (emp_info ); dbms_output.put_line ('user' | emp_info.ename | ''s new salary is '| emp_in Fo. esal); end; 5. nested records can include objects, sets, and other records (also known as nested records) in the record type ). However, the record type cannot be used as its attribute in the object type. Declare type name_type is record --> define record type (first_name varchar2 (15), last_name varchar2 (20); Type person_info_type is record --> define record type (ID number (6 ), name name_type --> the type of name is name_type, namely, nesting, job_title jobs. job_title % type); person_rec person_info_type; --> declare the record variable begin select employee_id, first_name, last_name, job_title into person_rec.id, person_rec.name.first_name --> pay attention to the reference method in the nested re On_rec.name.last_name --> enclosing_record. (nested_record or nested_collection ). field_name, person_rec.job_title from employees e join jobs J on E. job_id = J. job_id and rownum <2; dbms_output.put_line ('first name is '| person_rec.name.first_name); dbms_output.put_line ('Last name is' | person_rec.name.last_name); end; 6. All record-based sets in a record set can be called a record set here, that is, the set type is based on the record type. -- The following example shows a record set declare cursor cur_emp is --> declare a cursor select empno, ename, hiredate from EMP where deptno = 20 order by 1; Type emp_tab_type is table of cur_emp % rowtype --> A composite array index by binary_integer is defined based on the cursor type; emp_tab emp_tab_type; --> declare the compound variable v_counter INTEGER: = 0; begin for emp_rec in cur_emp loop v_counter: = v_counter + 1; --> v_counter is used to control the subscript emp_tab (v_counter ). empno: = emp_rec.emp No; --> assign values to compound variables. Pay attention to reference the emp_tab (v_counter) method ). ename: = emp_rec.ename; emp_tab (v_counter ). hiredate: = emp_rec.hiredate; dbms_output.put_line ('recored' | v_counter | 'is' | emp_tab (v_counter ). ename | ',' | emp_tab (v_counter ). hiredate); End loop; end; -- The following example is an insert Table Based on the custom record type. Note that the nested table needs to be extended. We know that the cursor is usually a single record with multiple columns, combined arrays, nested tables, and variable-length arrays are single columns and multiple rows. Therefore, we can combine record types and set types into a two-dimensional table. Therefore, for such operations, more efficiently, you can directly use the bulk collect clause to manipulate it. Example of using bulk collect. Note that using the bulk collect clause makes it unnecessary to manually extend the declare type rec_type is record --> define the record type (ename EMP. ename % type, empno EMP. empno % type, hiredate EMP. hiredate % type); Type emp_tab_type is table of rec_type; --> define the record-type-based nested table emp_tab emp_tab_type: = emp_tab_type (); --> initialize the nested table v_counter INTEGER: = 0; begin for emp_rec in (select * from EMP where deptno = 20) loop v_counter: = v_counter + 1; emp_tab. Extend; --> emp_tab (v_counter) needs to be extended using extend ). empno: = emp_rec.empno; emp_tab (v_counter ). ename: = emp_rec.ename; emp_tab (v_counter ). hiredate: = emp_rec.hiredate; dbms_output.put_line ('recored' | v_counter | 'is' | emp_tab (v_counter ). ename | ',' | emp_tab (v_counter ). hiredate); End loop; end; ----> author: Robinson Cheng ----> blog: http://blog.csdn.net/?son_06=,: a?cannot test the record. The record is null= The following operations are invalid. If dept_rec is null then... If dept_rec1 = dept_rec2 then... B. The record type is different from the variable-length array and nested table and cannot be stored in the database.

For more information, see:

Batch SQL forall statements

Bulk collect clause for batch SQL

Initialization and assignment of PL/SQL Sets

PL/SQL Union arrays and nested tables
PL/SQL variable-length Array
PL/SQL --> PL/SQL records

SQL tuning steps

Efficient SQL statements

Parent cursor, child cursor, and shared cursor

Bind variables and their advantages and disadvantages

Use of the display_cursor function of dbms_xplan

Use of the display function of dbms_xplan

Description of each field module in the execution plan

Use explain plan to obtain the SQL statement execution plan