Oracle PL/SQL开发基础（第十六弹：记录类型）

最新推荐文章于 2021-04-16 00:38:02 发布

艺术就是爆炸

最新推荐文章于 2021-04-16 00:38:02 发布

阅读量838

点赞数 1

分类专栏： Oracle 文章标签： oracle record 记录类型嵌套记录 plsql

本文链接：https://blog.csdn.net/lianjiww/article/details/71429665

版权

Oracle 专栏收录该内容

67 篇文章 16 订阅

订阅专栏

记录类型有些类似于C语言的结构或者，使用记录可以一次性处理多个类型的值。

记录类型简介

记录类型给了程序员自定义程序结构的能力，这种程序结构是指变量类型的集合，这些变量被组织在一起同一进行管理，是的记录类型有些类似于表的一行。可以把记录类型想象成表的一行记录。

在未使用记录类型之前，我们看个例子，定义多个变量来保存emp表中字段的值：

DECLARE
   --定义保存字段值的变量
   v_empno      NUMBER;
   v_ename      VARCHAR2 (20);
   v_job        VARCHAR2 (9);
   v_mgr        NUMBER (4);
   v_hiredate   DATE;
   v_sal        NUMBER (7, 2);
   v_comm       NUMBER (7, 2);
   v_deptno     NUMBER (2);
BEGIN
   --从emp表中取出字段值
   SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno
     INTO v_empno, v_ename, v_job, v_mgr, v_hiredate, v_sal, v_comm, v_deptno
     FROM emp
    WHERE empno = :empno;
   --向emp_copy表中插入变量的值
   INSERT INTO emp_copy
               (empno, ename, job, mgr, hiredate, sal, comm,
                deptno
               )
        VALUES (v_empno, v_ename, v_job, v_mgr, v_hiredate, v_sal, v_comm,
                v_deptno
               );
EXCEPTION  --异常处理块
   WHEN OTHERS
   THEN
      NULL;
END;

这样的写法需要定义多个变量，如果同时对两个以上的表进行操作，会使得变量的定义变得混乱。
如果把所有这些变量考虑为一个单元进行处理，可以将其声明为一个记录类型。使用记录类型修改后：

DECLARE
   --定义记录类型
   TYPE t_emp IS RECORD
   (
   v_empno      NUMBER,
   v_ename      VARCHAR2 (20),
   v_job        VARCHAR2 (9),
   v_mgr        NUMBER (4),
   v_hiredate   DATE,
   v_sal        NUMBER (7, 2),
   v_comm       NUMBER (7, 2),
   v_deptno     NUMBER (2)
   );
   --声明记录类型的变量
   emp_info t_emp;
BEGIN
   --从emp表中取出字段值赋给记录类型
   SELECT *
     INTO emp_info
     FROM emp
    WHERE empno = :empno;
   --向emp_copy表中插入记录类型的值
   INSERT INTO emp_copy VALUES emp_info;
EXCEPTION  --异常处理块
   WHEN OTHERS
   THEN
      NULL;
END;

定义记录类型

如：

DECLARE
   --声明记录类型
   TYPE emp_rec IS RECORD (
      dept_row   dept%ROWTYPE,   --声明来自dept表行的嵌套记录
      empno      NUMBER,         --员工编号
      ename      VARCHAR (20),   --员工名称
      job        VARCHAR (10),   --职位
      sal        NUMBER (7, 2)   --薪资
   );
   --声明记录类型的变量
   emp_info   emp_rec;
BEGIN
   NULL;
END;

可以在声明的时候对记录类型中的成员进行初始化：

DECLARE
   TYPE emp_rec IS RECORD (
      empname    VARCHAR (12)           := '李斯特',      --员工名称，初始值李斯特
      empno      NUMBER        NOT NULL DEFAULT 7369,     --员工编号，默认值7369
      hiredate   DATE                   DEFAULT SYSDATE,  --雇佣日期，默认值当前日期
      sal        NUMBER (7, 2)                            --员工薪资
   );
   --声明emp_rec类型的变量
   empinfo   emp_rec;
BEGIN
   NULL;   
END;

记录类型赋值

简单赋值

语法如下：

record_name.field_name := expression;

如：

DECLARE
   TYPE emp_rec IS RECORD (
      empname    VARCHAR (12)           := '李斯特',      --员工名称，初始值李斯特
      empno      NUMBER        NOT NULL DEFAULT 7369,     --员工编号，默认值7369
      hiredate   DATE                   DEFAULT SYSDATE,  --雇佣日期，默认值当前日期
      sal        NUMBER (7, 2)                            --员工薪资
   );
   --声明emp_rec类型的变量
   empinfo   emp_rec;
BEGIN
   --下面的语句为empinfo记录赋值。
   empinfo.empname:='施密斯';
   empinfo.empno:=7010;
   empinfo.hiredate:=TO_DATE('1982-01-01','YYYY-MM-DD');
   empinfo.sal:=5000;
   --下面的语句输出empinfo记录的值
   DBMS_OUTPUT.PUT_LINE('员工名称：'||empinfo.empname);
   DBMS_OUTPUT.PUT_LINE('员工编号：'||empinfo.empno);
   DBMS_OUTPUT.PUT_LINE('雇佣日期：'||TO_CHAR(empinfo.hiredate,'YYYY-MM-DD'));
   DBMS_OUTPUT.PUT_LINE('员工薪资：'||empinfo.sal);   
END;

记录类型赋值

除了为单个成员逐个赋值外，还可以为整个记录一次性赋值，最常见的方式是将一个记录类型赋予另一个记录类型的值，如：

DECLARE
   --定义记录类型
   TYPE emp_rec IS RECORD (
      empno   NUMBER,
      ename   VARCHAR2 (20)
   );
   --定义与emp_rec具有相同成员的记录类型
   TYPE emp_rec_dept IS RECORD (
      empno   NUMBER,
      ename   VARCHAR2 (20)
   );
   --声明记录类型的变量
   emp_info1   emp_rec;
   emp_info2   emp_rec;
   emp_info3   emp_rec_dept;
   --定义一个内嵌过程用来输出记录信息
   PROCEDURE printrec (empinfo emp_rec)
   AS
   BEGIN
      DBMS_OUTPUT.put_line ('员工编号：' || empinfo.empno);
      DBMS_OUTPUT.put_line ('员工名称：' || empinfo.ename);
   END;
BEGIN
   emp_info1.empno := 7890;    --为emp_info1记录赋值
   emp_info1.ename := '张大千';
   DBMS_OUTPUT.put_line ('emp_info1的信息如下：');
   printrec (emp_info1);      --打印赋值后的emp_info1记录
   emp_info2 := emp_info1;    --将emp_info1记录变量直接赋给emp_info2
   DBMS_OUTPUT.put_line ('emp_info2的信息如下：');
   printrec (emp_info2);      --打印赋值后的emp_info2的记录
   emp_info3:=emp_info1;    --此语句出现错误，不同记录类型的变量不能相互赋值
END;

要注意，如果一个记录类型的变量赋给另一个记录类型，两个记录类型必须完全一致，不是光成员一致就行。
但是，使用%ROWTYPE可以根据数据表的行来定义一个记录类型的变量，记录的所有成员是表中的字段列表，可以讲一个以%ROWTYPE定义的记录类型的变量赋给一个与该记录具有完全相同的记录成员的记录变量，如：

DECLARE
   --定义一个与dept表具有相同列的记录
   TYPE dept_rec IS RECORD (
      deptno   NUMBER (10),
      dname    VARCHAR2 (30),
      loc      VARCHAR2 (30)
   );
   --定义基于dept表的记录类型
   dept_rec_db   dept%ROWTYPE;
   dept_info     dept_rec;
BEGIN
   --使用SELECT语句为记录类型赋值
   SELECT *
     INTO dept_rec_db
     FROM dept
    WHERE deptno = 20;
   --将%ROWTYPE定义的记录赋给标准记录变量
   dept_info := dept_rec_db;
END;

可以看到，尽管dept_info和dept_rec_db并不是相同的dept_rec类型，但是因为%ROWTYPE的运行机制及dept_rec中的记录成员与dept表相同，因此赋值是成功的。

如果要清空一个记录类型的变量，可以简单地为该变量赋一个空的或未初始化的记录类型，即可清空所有的记录成员值。

使用SELECT或FETCH语句赋值

这里先介绍使用SELECT语句赋值。要使用SELECT语句赋值，SELECT语句的选择列表必须要与记录类型的成员个数及类型相匹配，否则Oracle将抛出异常。
如：

DECLARE
   TYPE emp_rec IS RECORD (
      empno   NUMBER (10),
      ename   VARCHAR2 (30),
      job     VARCHAR2 (30)
   );
   --声明记录类型的变量
   emp_info   emp_rec;
BEGIN
   --为记录类型赋值
   SELECT empno,
          ename,
          job
     INTO emp_info
     FROM emp
    WHERE empno = 7369;
    --输出记录类型的值
   DBMS_OUTPUT.put_line (   '员工编号：'
                         || emp_info.empno
                         || CHR (13)
                         || '员工姓名：'
                         || emp_info.ename
                         || CHR (13)
                         || '员工职别：'
                         || emp_info.job
                        );
END;

操纵记录类型

在INSERT语句中使用记录类型

如：

DECLARE
   TYPE dept_rec IS RECORD (
      deptno   NUMBER (2),
      dname    VARCHAR2 (14),
      loc      VARCHAR2 (13)
   );
   --定义2个记录类型的变量
   dept_row     dept%ROWTYPE;
   dept_norow   dept_rec;
BEGIN
   --为记录类型赋值
   dept_row.deptno := 70;
   dept_row.dname := '工程部';
   dept_row.loc := '上海';
   dept_norow.deptno := 80;
   dept_norow.dname := '电脑部';
   dept_norow.loc := '北京';
   --插入%ROWTYPE定义的记录变量到表中
   INSERT INTO dept
        VALUES dept_row;
   --插入普通记录变量的值到表中
   INSERT INTO dept
        VALUES dept_norow;
   --向数据库提交对表的更改
   COMMIT;
END;

在UPDATE语句中使用记录类型

如：

DECLARE
   TYPE dept_rec IS RECORD (         --定义记录类型
      deptno   NUMBER (2),
      dname    VARCHAR2 (14),
      loc      VARCHAR2 (13)
   );
   dept_info   dept_rec;            --定义记录类型的变量
BEGIN
   SELECT *
     INTO dept_info
     FROM dept
    WHERE deptno = 80;              --使用SELECT语句初始化记录类型
   dept_info.dname := '信息管理部'; --更新记录类型的值
   UPDATE dept
      SET ROW = dept_info
    WHERE deptno = dept_info.deptno;--在UPDATE中使用记录变量更新表
END;

要注意，SET ROW右边是不能使用子查询的。

在RETURNING子句中使用记录

可以在DML语句中包含一个RETURNING子句，用来返回被UPDATE、DELETE或INSERT操作所影响到的行，通过RETURNING子句，可以将受影响的行保存到一个记录或者是一个记录集合。如：

DECLARE
   TYPE dept_rec IS RECORD (                                   --定义记录类型
      deptno   NUMBER (2),
      dname    VARCHAR2 (14),
      loc      VARCHAR2 (13)
   );

   dept_info        dept_rec;                             --定义记录类型的变量
   dept_returning   dept%ROWTYPE;                 --定义用于返回结果的记录类型
BEGIN
   SELECT *
     INTO dept_info
     FROM dept
    WHERE deptno = 80;                          --使用SELECT语句初始化记录类型

   dept_info.dname := '信息管理部';                         --更新记录类型的值

   UPDATE    dept
         SET ROW = dept_info
       WHERE deptno = dept_info.deptno          --在UPDATE中使用记录变量更新表，返回受影响的行到记录
   RETURNING deptno,
             dname,
             loc
        INTO dept_returning;

   dept_info.deptno := 12;
   dept_info.dname := '维修部';

   INSERT INTO dept                             --插入新的部门编号记录，返回受影响的行的记录
        VALUES dept_info
     RETURNING deptno,
               dname,
               loc
          INTO dept_returning;

   DELETE FROM dept                            --删除现有的部门，返回受影响的行的记录
         WHERE deptno = dept_info.deptno
     RETURNING deptno,
               dname,
               loc
          INTO dept_returning;
END;

要注意，记录变量是不允许出现在SELECT列表、WHERE子句、GROUP BY子句或ORDER BY子句中的。

使用嵌套记录

如：

DECLARE
   TYPE dept_rec IS RECORD (                            --定义部门记录类型
      deptno   NUMBER (2),
      dname    VARCHAR2 (14),
      loc      VARCHAR2 (13)
   );
   TYPE emp_rec IS RECORD (                             --定义员工记录类型          
      v_empno      NUMBER,
      v_ename      VARCHAR2 (20),
      v_job        VARCHAR2 (9),
      v_mgr        NUMBER (4),
      v_hiredate   DATE,
      v_sal        NUMBER (7, 2),
      v_comm       NUMBER (7, 2),
      v_dept_rec   dept_rec                             --定义嵌套的员工记录
   );  
   emp_info    emp_rec;                                 --员工记录
   dept_info   dept_rec;                                --临时部门记录
BEGIN
   SELECT *                                             --从数据库中取出员工部门的记录
     INTO dept_info
     FROM dept
    WHERE deptno = (SELECT deptno
                      FROM emp
                     WHERE empno = 7369);
   emp_info.v_dept_rec:=dept_info;                       --将部门信息记录赋给嵌套的部门记录
   SELECT empno, ename, job, mgr,                        --为emp表赋值
          hiredate, sal, comm
     INTO emp_info.v_empno, emp_info.v_ename, emp_info.v_job, emp_info.v_mgr,
          emp_info.v_hiredate, emp_info.v_sal, emp_info.v_comm
     FROM emp
    WHERE empno = 7369;
    --输出嵌套记录的员工所在部门信息
    DBMS_OUTPUT.PUT_LINE('员工所属部门为：'||emp_info.v_dept_rec.dname);
END;

艺术就是爆炸

关注

1
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
Oracle PL/SQL开发基础（第十六弹：记录类型）

记录类型有些类似于C语言的结构或者，使用记录可以一次性处理多个类型的值。记录类型简介记录类型给了程序员自定义程序结构的能力，这种程序结构是指变量类型的集合，这些变量被组织在一起同一进行管理，是的记录类型有些类似于表的一行。可以把记录类型想象成表的一行记录。在未使用记录类型之前，我们看个例子，定义多个变量来保存emp表中字段的值：DECLARE --定义保存字段值的变量
复制链接

扫一扫

专栏目录