源码-PL/SQL从入门到精通-第八章-记录与集合-Part 2-CSDN博客

本文链接：https://blog.csdn.net/hpdlzu80100/article/details/51955448
--代码8.8 使用select语句为记录赋值

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;

--代码8.9 在Insert语句中使用记录插入数据
DESC dept;
INSERT INTO dept
  SELECT *
  FROM   dept_copy;
SELECT *
FROM   dept_copy;
SELECT *
FROM   dept;

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;


SELECT *
FROM   dept;


--代码8.10 在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;


--代码8.11 在DML语句中使用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;

--使用嵌套记录
DESC emp;
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;

--8.2.2 定义索引表
-- 雇佣日期索引表集合
TYPE hiredate_idxt IS TABLE OF DATE INDEX BY PLS_INTEGER;
-- 部门编号集合
TYPE deptno_idxt IS TABLE OF dept.deptno%TYPE NOT NULL INDEX BY PLS_INTEGER;
--记录类型的索引表，这个结构允许在PL/SQL程序中创建本的一个本地副本
TYPE emp_idxt IS TABLE OF emp%ROWTYPE INDEX BY NATURAL;
-- 由部门名称标识的部门记录的集合
TYPE deptname_idxt IS TABLE OF dept%ROWTYPE INDEX BY dept.dname%TYPE;
-- 定义集合的集合
TYPE private_collection_tt IS TABLE OF deptname_idxt INDEX BY VARCHAR2(100);

--代码8.13 定义并操作索引表
DECLARE
  TYPE idx_table IS TABLE OF VARCHAR(12) INDEX BY PLS_INTEGER; --定义索引表类型   
  v_emp idx_table; --定义索引表变量
BEGIN
  v_emp(1) := '史密斯'; --随机的为索引表赋值
  v_emp(20) := '克拉克';
  v_emp(40) := '史瑞克';
  v_emp(-10) := '杰瑞';
  IF v_emp.EXISTS(40)
  THEN
    DBMS_OUTPUT.PUT_LINE(v_emp(40));
  END IF;
  --   EXCEPTION
  --   WHEN OTHERS THEN
  --     DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;

--代码8.15 使用%Type类型的索引键
DECLARE
  --定义记录类型的索引表，以dname作为索引键类型
  --dname是VARCHAR2(14)类型
  TYPE idx_dept_table IS TABLE OF dept%ROWTYPE INDEX BY dept.dname%TYPE;
  --声明记录类型的变量
  v_dept idx_dept_table;
  --定义一个游标，用来查询dept表
  CURSOR dept_cur IS
    SELECT *
    FROM   dept;
BEGIN
  --使用游标FOR循环打开游标，检索数据
  FOR deptrow IN dept_cur
  LOOP
    --为索引表中的元素赋值
    v_dept(deptrow.dname) := deptrow;
    --输出部门的LOC列信息
    DBMS_OUTPUT.put_line(v_dept(deptrow.dname).loc);
  END LOOP;
END;


SELECT *
FROM   dept;


DECLARE
  --定义以VARCHAR2作为索引键的索引表
  TYPE idx_deptno_table IS TABLE OF NUMBER(2) INDEX BY VARCHAR2(20);
  --声明记录类型的变量
  v_deptno idx_deptno_table;
BEGIN
  --为索引表赋值
  v_deptno('财务部') := 10;
  v_deptno('研究部') := 20;
  v_deptno('销售部') := 30;
  --引用索引表的内容
  DBMS_OUTPUT.put_line('销售部编号为：' || v_deptno('销售部'));
END;


--代码8.16 嵌套表定义和表变量声明
DECLARE
  TYPE dept_table IS TABLE OF dept%ROWTYPE; --部门信息嵌套表
  TYPE emp_name_table IS TABLE OF VARCHAR2(20); --员工名称嵌套表
  TYPE deptno_table IS TABLE OF NUMBER(2); --部门编号嵌套表
  dept_info dept_table; --声明嵌套表变量
  --声明并初始化嵌套表变量
  emp_name_info emp_name_table := emp_name_table('张小三', '李斯特');
  deptno_info   deptno_table := deptno_table(20, 30, 40);
BEGIN
  NULL;
END;


--代码8.17 嵌套表的初始化与访问
DECLARE
  TYPE emp_name_table IS TABLE OF VARCHAR2(20); --员工名称嵌套表
  TYPE deptno_table IS TABLE OF NUMBER(2); --部门编号嵌套表
  deptno_info   deptno_table;
  emp_name_info emp_name_table := emp_name_table('张小三', '李斯特');
BEGIN
  DBMS_OUTPUT.put_line('员工1：' || emp_name_info(1)); --访问嵌套表元素
  DBMS_OUTPUT.put_line('员工2：' || emp_name_info(2));
  IF deptno_info IS NULL --判断嵌套表是否被初始化
  THEN
    deptno_info := deptno_table(NULL, NULL, NULL, NULL, NULL);
  END IF;
  -- deptno_info.EXTEND(5);                                --扩充元素的个数
  FOR i IN 1 .. 5 --循环遍历嵌套表元数个数
  LOOP
    deptno_info(i) := i * 10;
  END LOOP;
  --显示部门个数
  DBMS_OUTPUT.put_line('部门个数：' || deptno_info.COUNT);
END;