PL/SQL Study Notes (2)

Scalar data types are essential. Needless to say, you must master them. The following describes PL/SQL Composite data types.

 

Composite Type
The composite type can store multiple values, including records and sets. A set is one-dimensional, but can be put into another set in the set to become a multi-dimensional set. The set is divided into three types: index table, nested table, and variable-length array. To use
First, we need to create a set type and declare the variables of this type. We can use the table and varray keywords in the declaration part of any PL/SQL block, subroutine or package to declare the set.
Type. The scope of the set is the same as that of other types of variables. In a block or subroutine, the set is valid when the program enters the block or subroutine and fails when it exits. In the package, the set is valid when we reference the package for the first time,
It does not expire until the session ends.

I. Record

A record can contain multiple members to save multiple values. The Data Types of the members in the record can be different. When using a record, you must declare the record type, then define the record variable, and then use it in PL/SQL.

Example 1:

Declare
Type myrecord is record (
Name varchar2 (10 ),
Age smallint
);



Mr myrecord;
Begin
Mr. Name: = 'hangsan ';
Mr. Age: = 20;
Dbms_output.put_line ('name: '| mr. Name | 'Age:' | mr. Age );
End;

Note:


1. You must add set serveroutput on to output data in the console.


2. Assign the variable = in PL/SQL:


3. Declare the block end of the statement and add it;


4. Add the Declaration type statement technology;


5. The new data type is case-insensitive like the original data type in Oracle. For example, you can declare Mr as myrecord or myrecord.


    

Example 2:

DECLARE

Type myrecord is record (
No number (2 ),
Name varchar2 (14 ),
City Scott. Dept. Loc % Type
);
Deptrecord myrecord;
Begin
-- The number of Columns after the SELECT statement is the same as the number of variables in the record.
Select deptno, dname, LOC into deptrecord from Dept where deptno = 30;
Dbms_output.put_line (deptrecord. No | ':' | deptrecord. Name | ':' | deptrecord. City );
End;

Example 3:

DECLARE

Type myrecord is record (
No number (2 ),
Name varchar2 (14 ),
City Scott. Dept. Loc % Type
);
Deptrecord myrecord;
Begin
-- The number of Columns after the SELECT statement is less than the number of variables in the record
Select dname, LOC into deptrecord. Name, deptrecord. City from Dept where deptno = 30;
Deptrecord. No: = 30;
Dbms_output.put_line (deptrecord. No | ':' | deptrecord. Name | ':' | deptrecord. City );
End;

Example 4:

Declare

-- Special definition record method. The type of the variable in the record is the same as that of the dept table row.

deptRecord dept%ROWTYPE;
BEGIN
SELECT * INTO deptRecord FROM DEPT WHERE DEPTNO=30;
DBMS_OUTPUT.PUT_LINE(deptRecord.deptNo||':'||deptRecord.dname||':'||deptRecord.loc);
END;

2. index table (associated array)

An index table is a set of key values. The key is unique and used to find the corresponding value. The key can be an integer or string. The first time you use a key to assign a corresponding value is to add an element, and the subsequent operation is to update the element.

DECLARE
TYPE MyTableTypeName IS TABLE OF ElementType [NOT NULL]
INDEX BY Key_Type;
myName MyTableTypeName;

Mytabletypename is the name of the table type, elementtype is the data type of the element, not
Null indicates that the null value cannot be referenced. key_type is the data type of the underlying object, and binary_integer
Pls_integer or varchar2 (Oracle9i and later ). Myname is the name of the table type variable.

Example 1

DECLARE
TYPE MyTabelType IS TABLE OF dept.dname%TYPE
NOT NULL INDEX BY BINARY_INTEGER;
my MyTabelType;
BEGIN
SELECT DNAME INTO my(-3) FROM DEPT WHERE　 deptno=20;
SELECT DNAME INTO my(-1) FROM DEPT WHERE　 deptno=40;
DBMS_OUTPUT.PUT_LINE('my(-3):'||my(-3));
DBMS_OUTPUT.PUT_LINE('my(-1):'||my(-1));
END;

Example 2:

DECLARE

TYPE MyTableType IS TABLE OF NUMBER
NOT NULL INDEX BY VARCHAR2(20);
my MyTableType;
BEGIN
　 my('China'):=1;
　 my('Japan'):=2;
　 my('USA'):=3;
　 DBMS_OUTPUT.PUT_LINE(my.first);
　 DBMS_OUTPUT.PUT_LINE(my.last);
　 DBMS_OUTPUT.PUT_LINE(my('China'));
　DBMS_OUTPUT.PUT_LINE(my(my.last));
END;

3. nested tables

To use a nested table, first create a nested table type, declare the nested table type variables, initialize the nested table variables, and reference the element values of the nested table variables.

1. Define the nested table Type

TYPE MyNestedTableType IS TABLE OF ElementType [NOT NULL];

Where:

Mynestedtabletype is the name of the nested table type. Elementtype is the type of nested table elements.
Any PL/SQL type other than the cursor type, but for a global nested table (create
The following types are not allowed: binary_integer pls_integer Boolean long
Raw natural natura1positive positiven ref cursor signtype
String. Not null indicates that the element value cannot be null.

 

Example:

Declare
-- Define record
Type deptrecord is record (
No number (2 ),
Name varchar2 (14 ),
City Scott. Dept. Loc % Type
);
-- Define a cursor
Cursor deptcursor is select * from Dept;
-- Use % type to define the element type of a nested table
Type dnamelist is table of dept. dname % type;
-- Use % rowtype of the table when defining the element type of the nested table
Type deptlist1 is table of dept % rowtype;
-- Use the % rowtype of the cursor when defining the element type of the nested table
Type deptlist2 is table of deptcursor % rowtype;
-- Use the record type to define the element type of a nested table
Type deptlist3 is table of deptrecord;
-- Define nested table types in SQL statements
Create type phonelist is table of varchar2 (10 );

2. Declare nested table type variables

Example:

Declare
Type dnamelist is table of dept. dname % type;
Namelist1 dnamelist;
Namelist2 namelist1 % type; -- Use % type to declare nested table type variables
-- Define nested table type variables in stored procedure parameters
Create or replace procedure proc_name (namelist in dnamelist)

Note: The same name cannot be set between the nested table type and the nested table type variable. The case sensitivity cannot be different because PL/SQL is not case sensitive.

3. initialize nested table type variables

The nested table must be initialized by the constructor (the index table does not need to be initialized by the constructor). Otherwise, the nested table variable is null and there is no limit on the number of elements during initialization.

Example:

DECLARE
TYPE DNameList IS TABLE OF dept.dname%TYPE;
my DNameList ;
BEGIN
my:=DNameList('CORESUN','CORESUN','CORESUN');　

Note: When the My variable is initialized with three parameters, the length of the nested table is 3. The length of the nested table is not long enough and must be extended using the extend function. If no parameters are input in the constructor, the length of the nested table is 0.

You can also declare the nested table type variables and initialize them at the same time, for example:

my DNameList:=DNameList('CORESUN','CORESUN','CORESUN');

If not null is not specified when defining element types, null can also be input in the constructor, for example:

my:=DNameList('CORESUN',NULL,'CORESUN');

4. Reference nested table type variables

The element in the nested table is referenced by nested table variables and the index values in parentheses. The subscript of the nested table is numbered from 1 to 231 at maximum.
The subscript can be an integer or an integer expression.

Example:

DECLARE
TYPE DNameList IS TABLE OF dept.dname%TYPE;
my DNameList ;
BEGIN
my:=DNameList(NULL,'CORESUN','CORESUN');　
SELECT DNAME INTO my(2) FROM dept where deptno=20;
my(1):='CORESUN';
--my(4):='CORESUN';
DBMS_OUTPUT.PUT_LINE(my(1));
DBMS_OUTPUT.PUT_LINE(my(2));
END;

Iv. nested table application example

Example 1: create a record-type nested table

DECLARE
TYPE DeptRecord IS RECORD(
　 no　　　 NUMBER(2),
　 name　 VARCHAR2(14),
　 city　 scott.dept.loc%TYPE
);
TYPE DeptRecordList IS TABLE OF DeptRecord;

Example 2: Nested tables are used inside another table.

1. Create a global nested table Type

-- When a nested table is used as the table column type, it must be created first. Such a nested table is called a global nested table.

CREATE OR REPLACE TYPE PHONE_TYPE IS TABLE OF VARCHAR2(20);
/
CREATE TABLE person
(
　 id NUMBER,
　 name VARCHAR2(10),
　 sal NUMBER(6,2),
　 phone PHONE_TYPE
) NESTED TABLE phone STORE AS PHONE_TABLE;

2. Insert data to the person table:

INSERT INTO person VALUES(1,'SCOTT',800,PHONE_TYPE('13256789876','031198765432'));
INSERT INTO PERSON VALUES(20,'CORESUN',2000,PHONE_TYPE('13999999999','13098765345','01064890987'));

Query all column information

SELECT * FROM PERSON;

The result in iSQL * Plus is as follows:

Display information through nested table Variables

Declare
Ptable phone_type;
Begin
Select phone into ptable from person where name = 'Scott ';
For I in 1 .. ptable. Count Loop
Dbms_output.put_line ('number' | I | ':' | ptable (I ));
End loop;

The result is as follows:

1: 13256789876

Number 2: 031198765432

4. Update nested table data:

DECLARE
pTable PHONE_TYPE:=PHONE_TYPE('034223455432','13888888888');
BEGIN
UPDATE person SET phone=pTable WHERE name='SCOTT';
END;

Iv. variable array (varray array)

The varray array is a set data type and can be used as a table column. The subscript of the set starts from 1 and the number of elements is limited. Syntax:

DECLARE
TYPE MyArrayTypeName IS VARRAY(size) OF ELEMENT_TYPE [NOT NULL];
my MyArrayTypeName

Here, myarraytypename is the name of the array type. Size indicates the maximum number of elements stored in the array. element_type indicates the element type. Not null does not allow null values. My is the name of the array variable.

Example 1:

DECLARE
TYPE MyVArrayType IS VARRAY(10) OF dept.dname%TYPE NOT NULL;

-- The constructor can contain a maximum of 10 (size) elements.

-- Neither defining not null nor automatically assigning null values to elements 4-10

nameArray MyVArrayType:=MyVArrayType('1','2','3');
BEGIN
SELECT dname into nameArray(1) from dept where deptno=20;
SELECT dname into nameArray(2) from dept where deptno=30;
DBMS_OUTPUT.PUT_LINE(nameArray(1));
DBMS_OUTPUT.PUT_LINE(nameArray(2));
DBMS_OUTPUT.PUT_LINE(nameArray(3));
END;

The result is as follows: RESEARCH
SALES
3