PLSQL composite variables and reference Variables

PLSQL composite variables and reference Variables

 

PLSQL compound variable

A composite variable is a variable used to store multiple values. When you define a composite variable, you must use the PL/SQL Composite data type. PL/SQL includes
PL/SQL records, PL/SQL tables, nested tables, and varray.

1. PLSQL records
Each PL/SQL record generally contains multiple members. When using PL/SQL record, you must define the record type and record variable in the definition section, then, the record variable is referenced in the execution part.
Instance:
Declare
Type emp_record_type is record (
Name EMP. ename % type,
Salary EMP. Sal % type,
Title EMP. Job % type );
Emp_record emp_record_type;
Begin
Select ename, Sal, job into emp_record from EMP where empno = 7788;
Dbms_output.put_line ('employee name: '| emp_record.name );
End;
Note: emp_record_type is the record type, emp_record is the record variable, and emp_record.name indicates the name of the member that references the record variable emp_record.

2. PL/SQL table(Index table)
Unlike advanced language arrays, PL/SQL tables have no limit on the number of elements and their subscripts have no upper or lower limit. When using a PL/SQL table, you must first define the PL/SQL table type and PL/SQL
And then reference the PL/SQL table variable in the execution section.
Instance:
Declare
Type ename_table_type is table of EMP. ename % Type Index by binary_integer;
Ename_table ename_table_type;
Begin
Select ename into ename_table (-1) from EMP where empno = 7788;
Dbms_output.put_line ('employee name: '| ename_table (-1 ));
End;
Note: ename_table_type indicates the table type; EMP. ename % Type specifies the data type and length of PL/SQL table elements; ename_table indicates the table variables; ename_table (-1) indicates the subscript
It is an element of-1.

From Oracle9i, in addition to binary_integer and pls_integer, the subscript of the PL/SQL table can also be of the varchar2 type.
Declare
Type area_table_type is table of number index by varchar2 (10 );
Area_table area_table_type;
Begin
Area_table ('beijing'): = 1;
Area_table ('shanghai'): = 2;
Area_table ('guangzhou '): = 3;
Dbms_output.put_line ('first element: '| area_table.first );
Dbms_output.put_line ('first element: '| area_table.last );
End;
First element: Beijing
First element: Shanghai

 

3. nested tables
Nested tables are similar to arrays of advanced languages. Note that the labels of both the advanced language and the nested table cannot be negative values. The number of elements in the advanced language array is limited, but the number of elements in the nested table is not limited.
A nested table is very similar to a PL/SQL table, but a nested table can be used as the Data Type of a column, while a PL/SQL table cannot be used as the Data Type of a column. When using nested tables in a table, you must first use
The create type statement creates a nested table type.
Instance:
Create or replace type emp_type as object (
Name varchar2 (10), salary number (6, 2), hiredate date); -- create an object type
Create or replace type emp_array is table of emp_type; -- create a nested table Type

(Or directly create a nested table type create or replace type tt_type is table of varchar2 (20) -- use the nested table type in the PL/SQL block.
When using nested table variables in PL/SQL blocks, you must first use the constructor to initialize nested table variables before referencing nested table elements in PL/SQL blocks.
Declare
Type ename_table_type is table of EMP. ename % type;
Ename_table ename_table_type;
Begin
Ename_table: = ename_table_type ('Mary ', 'Mary', 'Mary ');
Select ename into ename_table (2) from EMP where empno = & No;
Dbms_output.put_line ('employee name: '| ename_table (2 ));
End;
  -- Ename_table_type () is the constructor.
 
-- Use a nested table in a table column

Create Table Department (
Deptno number (2), dname varchar2 (10), employee emp_array
) Nested table employee store as employee; -- when using the nested table type as a table column, you must specify a dedicated storage table.

4. varray
Varray is similar to a nested table. It can be used as the data type of columns and object type attributes. However, you must note that there is no limit on the number of elements in the nested table, while varray has a limit on the number of elements.
When using varray, you must first establish the varray type. Similar to nested tables, when used in PL/SQL blocks, you must first use the constructor to initialize array variables before referencing array elements in PL/SQL blocks.
Instance:
Create type article_type as object (
Title varchar2 (30), pubdate date
);
Create type article_array is varray (20) of article_type;
After the varray type is set up, you can reference it as a user-defined data type in a table column or object attribute:
Create Table author (
ID number (6), name varchar2 (10), article article_array
);
-- Note: nested table data needs to be stored in a dedicated storage table, while varray data is stored in the table segment together with other column data.

Reference variable

 

A reference variable is a variable used to store numeric pointers. By using reference variables, applications can share the same object to reduce the occupied space. You can use a cursor when compiling a PL/SQL program.
Variable (ref cursor) and object type variable ref obj_type.
1. Ref cursor
When an explicit cursor is used, you need to specify the corresponding SELECT statement when defining the explicit statement. This explicit cursor is called a static cursor. When using variable variables, you do not need to specify
Select statement.
Instance:
Declare
Type Cl is ref cursor;
Emp_cursor Cl;
V_ename EMP. ename % type;
V_sal EMP. Sal % type;
Begin
Open emp_cursor
Select ename, Sal from EMP where deptno = 10;
Loop
Fetch emp_cursor into v_ename, v_sal;
Exit when emp_cursor % notfound;
Dbms_output.put_line (v_ename );
End loop;
Close emp_cursor;
End;
Note: CL is the ref cursor type, and emp_cursor is the cursor variable. When the cursor is opened, the SELECT statement is specified.
 
2. Ref obj_type
When writing an object type application, you can use ref to reference the object type to share the same object. Ref is actually a pointer to the object instance.
Instance:
Create or replace type home_type as object (
Street varchar2 (50), City varchar2 (20), State varchar2 (20), zipcode varchar2 (6), owner varchar2 (10)
);
Create Table homes of home_type; -- creates an object table homes using the object type

Create Table person (
ID number (6) primary key,
Name varchar2 (10), ADDR ref home_type -- reference object
);