第9章 PL/SQL中常用的组合数据

银发的半精灵

概述：

在PL/SQL语言中一个标量数据类型变量只能存放单一的值。然而，一个组合数据类型的变量却可以存放多个变量类型的值或多个组合类型的值。与标量数据类型不同， 组合数据类型 包含了内部结构（组件），一旦定义了一个组合数据类型，这个数据类型就可以重用，用来定义一个或多个组合类型的变量。
本文中使用的代码演示涉及的表都是Oracle中系统自带的dept(部门表)、emp(员工表)
如果替代变量赋值，必须先SET verify OFFSET
如果要输出，必须先serveroutput ON

Oracle PL/SQL中组合数据共有两大类：

PL/SQL 记录

在本章中，我们将讨论和学习PL/SQL中的记录。记录是可以容纳不同种类的数据项的数据结构。记录由不同的字段组成，类似于数据库表的一行。

PL/SQL记录 将 逻辑上相关 的但是 类型不同 的数据 当作一个逻辑单元处理 。
一个PL/SQL记录 可以 包含多个不同类型的数据 ，如可以定义一个存储客户详细信息的记录。

PL/SQL 集合

PL/SQL集合将一组（集合）数据当作一个单独的单元来处理。

INDEX BY 表
嵌套表（Nested Table）
变长数组（VARRAY）

创建用户定义的 PL/SQL 记录

创建一个PL/SQL 记录变量的步骤：

1.创建一个PL/SQL记录数据类型
语法：

TYPE 数据类型名 IS RECORD
(字段名, 字段类型);
字段名 数据类型名;

2.使用这个已经创建的PL/SQL记录数据类型来定义（声明）一个PL/SQL记录类型的变量
语法：

字段名 字段类型  变量%TYPE 
 表名.列名%TYPE  表名%ROWTYPE
 :=  DEFAULT 表达式

在以上语法中：

数据类型名（type_name): 为记录（RECORD)类型的名字（这一标识符将用于声明记录类型的变量）。
字段名（field_name): 为记录内部一个字段的名字。
字段类型（field_type): 为该字段的数据类型（可以是除了 REF CURSOR之外的任何PL/SQL 数据类型，当然也可以是％TYPE 和％ROWTYPE属性）。
表达式（expr): 为字段数据类型的表达式或初始值。

可以使用NOT NULL(非空）约束以防止将空值赋予这些字段，但是要确保初始化非空的那些字段。

代码演示：

--声明储存一名新员工的名字、职位、和工资的变量
SET verify OFF  --关闭替代变量的赋值输出结果提示
SET serveroutput ON  --使用DBMS_OUTPUT软件包，必须先开启参数
DECLARE --声明段 
--第1步 创建一个PL/SQL记录数据类型
  TYPE emp_record_type IS RECORD
    (empno	 NUMBER(4)  NOT NULL := &p_empno, --员工号，不允许为空
     ename	emp.ename%TYPE, --员工姓名
     job	emp.job%TYPE, --职位类型
     sal        emp.sal%TYPE,  --工资
     hiredate    emp.hiredate%TYPE);  --雇佣日期
  v_increase_sal NUMBER(8,2) DEFAULT &p_increase_sal;  --加薪工资
--第2步 使用这个已经创建的PL/SQL记录数据类型来定义（声明）一个PL/SQL记录类型的变量
  emp_record  emp_record_type; 
BEGIN
  SELECT empno, ename, job, sal, hiredate
  INTO emp_record
  FROM emp
  WHERE empno = emp_record.empno;
  emp_record.hiredate := SYSDATE;  --雇佣日期等于系统当前日期
  emp_record.sal := emp_record.sal + v_increase_sal;  ----员工工资= 目前薪资+ 加薪工资
  DBMS_OUTPUT.PUT_LINE(emp_record.empno);  --打印输出员工号
  DBMS_OUTPUT.PUT_LINE(emp_record.ename);  --打印输出员工姓名
  DBMS_OUTPUT.




    
PUT_LINE(emp_record.job);   --打印输出职位类型
  DBMS_OUTPUT.PUT_LINE(emp_record.sal);   --打印输出工资
  DBMS_OUTPUT.PUT_LINE(emp_record.hiredate);  --打印输出雇佣日期
END;

使用 %ROWTYPE属性创建记录-基于表的记录

%ROWTYPE 属性

%ROWTYPE返回的是一个记录类型，其数据类型和数据库表的数据结构一致。
声明的变量对应于数据库表或视图中列的集合。
在%ROWTYPE之前加上数据库表名。
记录内字段名和数据类型与参照表或视图中的列相同。

%ROWTYPE优点

可以不必知道数据库中列的数量和类型。
在运行期间，数据库中列的数量和类型可能发生变化，但是却不用修改代码。
在SELECT语句中使用该属性可以有效地检索表中的行。

语法：

identifier reference: ROWTYPE;

在以上语法中：

identifier: 为记录名。
reference: 为表名、视图名、游标（cursor)名或记录所基于的游标变量名（要使这个引用有效，表或视图必须存在）。

代码演示：

--声明一个存储来自员工（ EMP_PL）表中的有关员工信息的变量
emp_record emp_pl%ROWTYPE;

使用%ROWTYPE属性声明记录

代码演示：

---例如我们要存储公司的员工离职详细信息，其中包括离职日期。
--第1步  利用emp表创建ex_emp（离职员工）表的几乎整个结构
--先创建一张 离职员工表，储存公司所有离职员工的详细信息
CREATE TABLE ex_emp
SELECT *
FROM emp
WHERE 1= 2;  //利用 WHERE 1= 2条件，只创建一个与emp一模一样的 ex_emp表结构，并不会同步任何数据
--第2步 新增ex_emp 的“离职日期” 字段
ALTER TABLE  ex_emp ADD (leavedate DATE);
--第3步  
SET verify OFF  //关闭替代变量的赋值输出结果提示
SET serveroutput ON  //使用DBMS_OUTPUT软件包，必须先开启参数
DECLARE
//声明一个记录类型的成员变量
 emp_rec  emp%ROWTYPE;  
BEGIN
//SQL语句操作
//利用替代变量 employee_number 输入一个员工号empno，从员工表emp中提取员工的所有列信息，并存入记录类型的变量emp_rec。
 SELECT * INTO emp_rec
 FROM   emp
 WHERE  empno = &employee_number;
//将记录类型的变量emp_rec的每个字段的值插入到离职员工表ex_emp中。
 INSERT INTO ex_emp(empno, ename, job, mgr, hiredate,  leavedate, sal, comm, deptno)
 VALUES            (emp_rec.empno, emp_rec.ename, emp_rec.job, emp_rec.mgr, emp_rec.
 hiredate, SYSDATE, emp_rec.sal, emp_rec.comm, emp_rec.deptno);
 COMMIT; //提交
END;

使用%ROWTYPE属性插入和修改记录

插入记录的代码演示：

DECLARE
  v_emp_rec  ex_emp%ROWTYPE;
BEGIN
//SQL语句操作
  SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno, hiredate
  INTO   v_emp_rec
  FROM   emp
  WHERE  empno = &employee_number;
--插入语句操作
  INSERT INTO ex_emp
  VALUES v_emp_rec;
END;

修改记录的代码演示：

DECLARE
  v_emp_rec  ex_emp%ROWTYPE;
BEGIN
  SELECT *
  INTO   v_emp_rec
  FROM   ex_emp
  WHERE  empno = &employee_number;
  v_emp_rec.leavedate := CURRENT_DATE;
 //修改语句操作 
//利用 v_emp_rec 记录修改ex_emp表中的整个数据行
  UPDATE ex_emp 
  SET ROW = v_emp_rec 
  WHERE empno = v_emp_rec.empno;
END;

创建 INDEX BY表

定义：

与数组类似的同质元素的集合组成的一种复合数据类型。

特点：

集合中的元素是稀疏分布的，没有限定的边界，只是由整数作为索引将其连接在一起，索引可以是正、负整数或者0

INDEX BY表的属性

由两个组件所组成:

数据类型为 BINARY_INTEGER 或 PLS_INTEGER 的主键（整数或字符串类型）
标量或记录数据类型的列

语法：

TYPE 数据类型名 IS TABLE OF 
列数据类型名  变量%TYPE
表名.列名%TYPE  [NOT NULL] 
表名%ROWTYPE  
INDEX BY PLS_INTEGER 或 BINARY_INTEGER  //  pls_integer 或 binary_integer:整数类型
identifier	type_name;

代码演示：

--声明一个存储员工名字的INDEX BY 表的变量
SET verify OFF
SET serveroutput ON
DECLARE
  TYPE ename_table_type IS TABLE OF emp.ename%TYPE
       INDEX BY PLS_INTEGER;   //  pls_integer 整数类型
  TYPE hiredate_table_type IS TABLE OF DATE
       INDEX BY BINARY_INTEGER;   //  binary_integer:整数类型
  ename_table     ename_table_type; 
  hiredate_table  hiredate_table_type;
  v_count NUMBER(6) := &p_count; 
BEGIN
//For循环语句
  FOR i IN 1..v_count LOOP
    ename_table(i) := '张三';
    hiredate_table(i) := SYSDATE + 14;
//打印
    DBMS_OUTPUT.PUT_LINE(ename_table(i) ||': '|| hiredate_table(i));
  END LOOP;
END;

INDEX BY 表的方法

方法	描述
COUNT	返回持有数据的存储单元的数量
FIRST	含有数据的最小整数存储单元
LAST	返回最后一个有值的元素（包含null值）的存储单元
PRIOR	返回含有数据的上一个存储单元
NEXT	返回含有数据的下一个存储单元
EXISTS	避免读取空存储单元
DELETE	删除元素并释放内存

PRIOR（n）和NEXT（n）方法的使用：

以下这段PL/SQL程序的功能是定义一个下标是变长字符串的关联数组变量，之后利用查询语句从员工表中提取每个部门的员工总数并存入相应的关联数组元素中。
随后，利用方法 FIRST 和 NEXT 按升序列出每个部门中员工的总数。
最后，利用方法 LAST 和P RIOR 按降序列出每个部门名和员工总数。

代码演示：

DECLARE
 -- 第1步 该程序声明了一个名为emp_num_type的INDEX BY表数据类型，其数组元素的数据是 NUMBER型，
 -- 而下标是长度为38位的变长字符类型。
  TYPE emp_num_type IS TABLE OF NUMBER  //声明数组类型
  INDEX BY VARCHAR2(38);
--第2步  声明了一个名为Total_employees的emp_num_type类型的变量。
--还声明了一个长度为38位的变长字符类型变量i.
  Total_employees emp_num_type;   //声明数组类型
  i VARCHAR2(38);
BEGIN
  -- SQL语句:
--第3步  利用SELECT INTO语句将每个部门的员工总数赋予相应的数组元素
  SELECT count(*) INTO Total_employees('ACCOUNTING')
  FROM emp WHERE deptno = 10;
  SELECT count(*) INTO Total_employees('RESEARCH')
  FROM emp WHERE deptno = 20;
  SELECT count(*) INTO Total_employees('SALES')
  FROM emp WHERE deptno = 30;
  --第2步   将数组 Total employees 的第一个下标值赋予变量 i
  i := Total_employees.FIRST;
--第3步  利用软件包DBMS_Output打印信息“按升序列出每个部门中员工总数：”
  DBMS_Output.PUT_LINE('按升序列出每个部门名号员工总数：');
--第4步  是一个WHILE循环体，在这个WHILE循环中主要利用方法 NEXT以升序 的方式显示数组Total_employees中的每一个元素
  WHILE i IS NOT NULL LOOP
    DBMS_Output.PUT_LINE
    ('Total number of employees in ' || i || ' is ' || TO_CHAR(Total_employees(i)));
    i := Total_employees.NEXT(i);
  END LOOP;
--第5步  显示空行，其中 CHR(10)是将10转换成 ASCII码
  DBMS_Output.PUT_LINE(CHR(10));
--第6步  将数组Total_employees的最后一个下标值赋予变量i
  i := Total_employees.LAST;
----第7步  利用软件包DBMS_Output打印信息
  DBMS_Output.PUT_LINE('按降序列出每个部门名号员工总数：');
--第8步  也是一个WHILE循环体，在这个WHILE循环中主要利用方法 PRIOR以降序 的方式显示数组Total_employees中的每一个元素
  WHILE i IS NOT NULL LOOP
    DBMS_Output.PUT_LINE