Pl/sql definition part of the second composite data type

Composite type

A composite type can store multiple values, including records and collections. A set is one-dimensional, but it can be set into a multidimensional collection by putting other collections in it. The collection is divided into three kinds of index table, nested table and variable length array. To use a collection, we first create a collection type and then declare a variable of that type. We can use the table and Varray keywords to declare the collection type in any pl/sql block, subroutine, or package declaration section. The scope of a collection is the same as for other types of variables, in a block or subroutine, when the program enters a block or subroutine, and fails when it exits. In the package, the collection is valid the first time we reference the package until the session terminates.

First, record

A record can include more than one member so that multiple values can be saved. The data types of the members in the record can be different. Records must be used to declare the record type and then define the record variable to be used in Pl/sql.

Example 1:

DECLARE
TYPE MyRecord IS RECORD(
name VARCHAR2(10),
age　 SMALLINT
);
mr MyRecord;
BEGIN
mr.name:='zhangsan';
mr.age:=20;
DBMS_OUTPUT.PUT_LINE('姓名:'||mr.name||'　　　 年龄:'||mr.age);
END;

Example 2: DECLARE
TYPE MyRecord IS RECORD(
no　　　 NUMBER(2),
name　 VARCHAR2(14),
city　 scott.dept.loc%TYPE
);
deptRecord MyRecord ;
BEGIN
--SELECT 语句后列的数目与记录中的变量数目相同
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
--SELECT 语句后列的数目少于记录中的变量数目
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 of record, the type of the variable in the record is the same type as 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;