Oracle PL/SQL 第五章–复合类型

1. 复合类型

ORACLE 在 PL/SQL 中除了提供象前面介绍的各种类型外,还提供一种称为复合类型的类型—记录和表。

1.1 记录类型

记录类型类似于C语言中的结构数据类型，它把逻辑相关的、分离的、基本数据类型的变量组成一个整体存储起来，它必须包括至少一个标量型或RECORD数据类型的成员，称作PL/SQL RECORD的域(FIELD)，其作用是存放互不相同但逻辑相关的信息。在使用记录数据类型变量时，需要先在声明部分——先定义记录的组成、记录的变量，然后在执行部分引用该记录变量本身或其中的成员。

语法：

TYPE record_name IS RECORD(
   var_name1 data_type1  [NOT NULL]  [:= default_value ],
   var_name2 data_type2  [NOT NULL]  [:= default_value ],
   ......
   var_namen data_typen  [NOT NULL]  [:= default_value ] );

以上代码中，定义成员变量的语法释义：变量名称 数据类型 是否为空[可选] 是否赋默认值[可选]

示例：

DECLARE
   TYPE test_record IS RECORD(
         userName VARCHAR2(30) NOT NULL := 'oracle',
         userInfo VARCHAR2(100));
   record_book test_record;
BEGIN
   record_book.userName :='oracle';
   record_book.userInfo :='oracle PL/SQL';
   DBMS_OUTPUT.PUT_LINE(record_book.userName||'  ' ||record_book.userInfo);
END;

可以用 SELECT INTO语句对记录变量进行赋值,但是必须保证记录字段与查询结果列表中的字段相匹配。

一个记录类型的变量只能保存从数据库中查询出的一行记录，若查询出了多行记录，就会出现错误。

1.2 数组类型

数组是具有相同数据类型的一组成员的集合。每个成员都有一个唯一的下标，分别代表在集合中的位置。在PL/SQL中，数组数据类型是VARRAY。

语法：

TYPE varray_name IS VARRAY(size) OF element_type [NOT NULL];

varray_name是VARRAY数组类型的名称，size（整数）表示可容纳成员数据的最大数量，每个成员的数据类型是element_type。默认成员可以取空值，否则需要使用NOT NULL加以限制。对于VARRAY数据类型来说，必须经过三个步骤，分别是：定义 $\Rightarrow$ 声明 $\Rightarrow$ 初始化。

示例：

DECLARE
--定义一个最多容纳5个VARCHAR(20)数据类型成员的VARRAY数据类型
   TYPE test_varray_type IS VARRAY(5) OF VARCHAR(20);
   
--声明一个该VARRAY数据类型的变量
   test_varray test_varray_type;
 
BEGIN
--用构造函数语法赋予初值
   test_varray := test_varray_type 
         ('北京', '上海', '重庆', '成都', '杭州');
 
   DBMS_OUTPUT.PUT_LINE('城市名称：'||test_varray (1)||'、'
                                    ||test_varray (2)||'、'
                                    ||test_varray (3)||'、'
                                    ||test_varray (4));
   DBMS_OUTPUT.PUT_LINE('赋予初值NULL的第5个成员的值：'||test_varray (5));
--用构造函数语法赋予初值后就可以这样对成员赋值
   test_varray (5) := '深圳';
   DBMS_OUTPUT.PUT_LINE('第5个成员的值：'||test_varray (5));
END;

1.3 使用%TYPE引用数据类型

%TYPE表示：定义的变量与%TYPE引用的变量的数据类型一致(一般是表的某一列)。

使用%TYPE特性的优点在于：

• 所引用的数据库列的数据类型可以不必知道；

• 所引用的数据库列的数据类型可以实时改变，容易保持一致，也不用修改PL/SQL程序。

示例：

DECLARE
   -- 用%TYPE 类型定义与表相配的字段
   TYPE test_record IS RECORD(
        test_no emp.empno%TYPE,
        test_name emp.ename%TYPE,
        test_sal emp.sal%TYPE );
   -- 声明接收数据的变量
   v_emp test_record ;
BEGIN
   SELECT empno, ename, sal INTO v_emp FROM emp WHERE empno=7788;
   DBMS_OUTPUT.PUT_LINE
    (TO_CHAR(v_emp.test_no )||' '||v_emp.test_name ||'  ' || TO_CHAR(v_emp.test_sal ));
END;

1.4 使用%ROWTYPE引用数据类型

PL/SQL 提供%ROWTYPE操作符, 返回一个记录类型, 其数据类型和数据库表的数据结构相一致。

使用%ROWTYPE特性的优点在于：

• 所引用的数据库中列的个数和数据类型可以不必知道；

• 所引用的数据库中列的个数和数据类型可以实时改变，容易保持一致，也不用修改PL/SQL程序。

示例：

DECLARE
    test_empno emp.empno%TYPE :=&no;
    rec emp%ROWTYPE;
BEGIN
    SELECT * INTO rec FROM emp WHERE empno=test_empno ;
    DBMS_OUTPUT.PUT_LINE('姓名:'||rec.ename||'工资:'||rec.sal||'工作时间:'||rec.hiredate);
END;

1.6 TABLE数据类型

定义记录表（或索引表）数据类型。它与记录类型相似，但它是对记录类型的扩展。它可以处理多行记录，类似于高级中的二维数组，使得可以在PL/SQL中模仿数据库中的表。

语法：

TYPE table_name IS TABLE OF element_type [NOT NULL]
INDEX BY [BINARY_INTEGER | PLS_INTEGER | VARRAY2];

关键字INDEX BY表示创建一个主键索引，以便引用记录表变量中的特定行。

扩展：

方法	描述
EXISTS(n)	如果集合的第n个成员存在，则返回true
COUNT	返回已经分配了存储空间即赋值了的成员数量
FIRST	FIRST：返回成员的最低下标值
LAST	LAST：返回成员的最高下标值
PRIOR(n)	返回下标为n的成员的前一个成员的下标。如果没有则返回NULL
NEXT(N)	返回下标为n的成员的后一个成员的下标。如果没有则返回NULL
DELETE	DELETE：删除所有成员 DELETE(n) ：删除第n个成员 DELETE(m, n) ：删除从n到m的成员
EXTEND	EXTEND：添加一个null成员 EXTEND(n)：添加n个null成员 EXTEND(n,i)：添加n个成员，其值与第i个成员相同
LIMIT	返回在varray类型变量中出现的最高下标值

示例：

DECLARE
  TYPE dept_table_type IS TABLE OF
       dept%ROWTYPE INDEX BY BINARY_INTEGER;
  my_dname_table dept_table_type;
  v_count number(2) :=4;
BEGIN
  FOR int IN 1 .. v_count LOOP
    SELECT * INTO my_dname_table(int) FROM dept WHERE deptno=int*10;
  END LOOP;
  FOR int IN my_dname_table.FIRST .. my_dname_table.LAST LOOP
  DBMS_OUTPUT.PUT_LINE('Department number: '||my_dname_table(int).deptno);
  DBMS_OUTPUT.PUT_LINE('Department name: '|| my_dname_table(int).dname);
  END LOOP;
END;

示例：按一维数组使用记录表

DECLARE
--定义记录表数据类型
   TYPE reg_table_type IS TABLE OF varchar2(25)
   INDEX BY BINARY_INTEGER;
--声明记录表数据类型的变量
   v_reg_table REG_TABLE_TYPE;
    
BEGIN
   v_reg_table(1) := 'Europe';
   v_reg_table(2) := 'Americas';
   v_reg_table(3) := 'Asia';
   v_reg_table(4) := 'Middle East and Africa';
   v_reg_table(5) := 'NULL';
 
   DBMS_OUTPUT.PUT_LINE('地区名称：'||v_reg_table (1)||'、'
                                    ||v_reg_table (2)||'、'
                                    ||v_reg_table (3)||'、'
                                    ||v_reg_table (4));
   DBMS_OUTPUT.PUT_LINE('第5个成员的值：'||v_reg_table(5));
END;

示例：按二维数组使用记录表

DECLARE
--定义记录表数据类型
   TYPE emp_table_type IS TABLE OF employees%ROWTYPE
   INDEX BY BINARY_INTEGER;
--声明记录表数据类型的变量
   v_emp_table EMP_TABLE_TYPE;
BEGIN
   SELECT first_name, hire_date, job_id INTO
   v_emp_table(1).first_name,v_emp_table(1).hire_date, v_emp_table(1).job_id
   FROM employees WHERE employee_id = 177;
   SELECT first_name, hire_date, job_id INTO
   v_emp_table(2).first_name,v_emp_table(2).hire_date, v_emp_table(2).job_id
   FROM employees WHERE employee_id = 178;
 
   DBMS_OUTPUT.PUT_LINE('177雇员名称：'||v_emp_table(1).first_name
             ||'  雇佣日期：'||v_emp_table(1).hire_date
             ||'  岗位：'||v_emp_table(1).job_id);
   DBMS_OUTPUT.PUT_LINE('178雇员名称：'||v_emp_table(2).first_name
             ||'  雇佣日期：'||v_emp_table(2).hire_date
             ||'  岗位：'||v_emp_table(2).job_id);
END;

1.7 BIND 变量

绑定变量是在主机环境中定义的变量。在PL/SQL 程序中可以使用绑定变量作为他们将要使用的其它变量。为了在PL/SQL 环境中声明绑定变量，使用命令VARIABLE。例如：

VARIABLE return_code NUMBER
VARIABLE return_msg VARCHAR2(20)

可以通过SQL*Plus命令中的PRINT 显示绑定变量的值。例如：

PRINT return_code
PRINT return_msg

示例：

VARIABLE result NUMBER;
BEGIN
  SELECT (sal*10)+nvl(comm, 0) INTO :result FROM emp
  WHERE empno=7844;
END;
--然后再执行
PRINT result

结束语：我认为赞赏是词汇表里最重要的一个词。当我们理解并且积极地运用了它，赞赏就是最美的和最有力的一个词，同时，它也是所有词语中最容易被忽视和滥用的一个词。我想，在人们追求和谐而满意的生活方式中，不可能再有另外一个词比它具有更神奇的力量了。