[Oracle] efficient PL/SQL programming (6)-% rowtype usage

 

In the PL/SQL program, we will first retrieve several records from a result set and then process each record, the best practice is to complete it in an SQL statement, but sometimes it may not be implemented because of the requirement. Therefore, we usually define several variables and then perform a loop on the result set, assign a value to a variable. the most typical is select XX into xx. We can also define a record % rowtype to reduce unnecessary code and avoid errors caused by field changes in the table. Example:

Create a test table:

Create Table T1 as select * From user_tables

Create a package head:

Create or replace package type_demo is

Procedure process1 (p_record in T1 % rowtype );
Procedure process_data (p_inputs in varchar2 );

End type_demo;

Create package body:

Create or replace package body type_demo is

Procedure process1 (p_record in T1 % rowtype)
As
Begin
Dbms_output.put_line (p_record.tablespace_name );
End;

Procedure process_data (p_inputs in varchar2)
As
Begin
For X in (select * from T1 where table_name like p_inputs)
Loop
Process1 (X );
End loop;
End;

End type_demo;

The above example shows the record type of the entire table. To obtain the record types of several columns, follow these steps:

Create or replace package type_demo is

Cursor template_cursor
Is select table_name, tablespace_name from T1;

-- Type RC is ref cursor;

Procedure process2 (p_record in template_cursor % rowtype );
Procedure process_data (p_cname in varchar2, p_inputs in varchar2 );
End type_demo;

 

Create or replace package body type_demo is

Procedure process2 (p_record in template_cursor % rowtype)
As
Begin
Dbms_output.put_line (p_record.tablespace_name );
End;

Procedure process_data (p_cname in varchar2, p_inputs in varchar2)
As
-- L_cursor RC;
Rochelle cursor sys_refcursor;
L_rec template_cursor % rowtype;
Begin
Open l_cursor
For
'Select table_name, tablespace_name from T1 where '| p_cname | 'like: x' using p_inputs;
Loop
Fetch l_cursor into l_rec;
Exit when l_cursor % notfound;
Process2 (l_rec );
End loop;
End;

End type_demo;

Note: there is also a data type % Type for obtaining columns.

L_tablename t1.tablename % type; select XX into l_tablename from T1 where...