记录与集合

DMSQL 中的记录类型是一种复合的数据结构，由多个元素组成。概念上类似于数据库表中的行。提供了一种把这些值当作一组进行操作的方法。把字段级别的声明和操作转换成记录级别进行。

下面的代码块演示了如何直接根据一个数据表来声明一个记录类型。下面是 DMHR 用户下的一张表。

CREATE TABLE job
(
    JOB_ID     VARCHAR(10) NOT NULL,
    JOB_TITLE  VARCHAR(35) NOT NULL,
    MIN_SALARY INT,
    MAX_SALARY INT,
    CONSTRAINT JOB_ID_PK NOT CLUSTER PRIMARY KEY(JOB_ID)
);

接着创建一个基于这个表上的记录，通过一个对数据库的查询来填充记录里的字段，通过记录的字段来访问每一列。

DECLARE
  myjob job%ROWTYPE;
BEGIN
  SELECT * INTO myjob FROM job WHERE job_id=62;
  DBMS_OUTPUT.PUT_LINE(myjob.job_title);
END;
/

同时，我们也可以定义自己的记录类型。

DECLARE
  TYPE job_rt IS RECORD (
    job_id job.job_id%type,
    job_title job.job_title%type
  );
  myjob job_rt;
BEGIN
  SELECT job_id, job_title INTO myjob FROM job WHERE job_id=62;
  DBMS_OUTPUT.PUT_LINE(myjob.job_title);
END;
/

为了能够从记录这种数据结构中获得更多的益处，应该坚持如下的原则：

  • 创建相互匹配的游标和记录

每当我们在程序中创建一个游标时，就应该相应的创建一个匹配的记录类型，并且把信息 FETCH 到一个记录中，而不是一个个的变量中。

  • 创建基于表的数据类型

每当我们要在程序中保存来自表的数据时，都应该创建一个全新的基于表的记录来保存数据。这样，只需要声明一个变量就够了，并且每次编译时记录的结构都会自动根据表结构的变动而变动。

  • 把记录作为参数传递，而不是若干个变量

一、记录声明的方法

  • 基于表的记录类型

使用表名 %ROWTYPE 可以声明一个记录类型，记录中每一个字段都和表中的列相互对应。

DECLARE 
  customer_rec CUSTOMER%ROWTYPE;
BEGIN
 SELECT * INTO customer_rec FROM customer WHERE ROWNUM=1;
 PRINT 'c_name is ' || customer_rec.c_name || ',c_address is ' || customer_rec.c_address;
END;
/
  • 基于游标的记录类型
DECLARE 
  CURSOR customer_cur IS SELECT * FROM CUSTOMER WHERE ROWNUM < 10;
  customer_rt customer_cur%ROWTYPE;
BEGIN
  OPEN customer_cur;
  FETCH customer_cur INTO customer_rt;
  PRINT customer_rt.c_phone;
  CLOSE customer_cur;
END;
  • 程序员自定义的记录类型 TYPE...RECORD. 记录的每一个字段都要在 TYPE 语句中明确的定义（字段名 + 字段类型）。
DECLARE 
  TYPE customer_rectype IS RECORD
(
     f_name    customer.c_name%TYPE,
     f_address customer.c_address%TYPE
  );
  customer_rec customer_rectype;
BEGIN
  SELECT c_name, c_address INTO customer_rec FROM customer where ROWNUM=1;
  PRINT 'c_name is ' || customer_rec.f_name || ',c_address is ' || customer_rec.f_address;
END;
/
  • 伪记录

在数据库触发器中操作某张表，达梦数据库为我们提供了两种数据结构：OLD 和 NEW。这两种是伪记录，这两个结构和用 %ROWTYPE 声明是一样的。表中的每一列都对应一个字段。

  • OLD: 代表事务开始前，表中记录的值。
  • NEW: 当前事务结束后，表中的记录的新值。

在触发器中引用 OLD 和 NEW 时，我们必须在这些标识符前面加一个冒号，不过要是在 WHEN 语句中就不需要冒号了。看下面的例子，但修改最低工资后低于原值则抛出异常并回滚 DML 事务。.

CREATE OR REPLACE TRIGGER ChangeMinSalary
AFTER UPDATE OF min_salary ON job FOR EACH ROW
WHEN (OLD.min_salary > NEW.min_salary)
BEGIN
  RAISE_APPLICATION_ERROR(-20001, 'invalid value of min_salary'); 
END;
/

二、集合

类似于列表和一维数组的数据结构。DMSQL 包含 3 种集合类型：

  1. 关联数组
  2. 嵌套
  3. 可变数组

2.1 关联数组

定义：只能在 DMSQL 环境中使用的，一维的，无边界的，稀疏的，由同质元素构成的集合。

DECLARE 
  TYPE fruit_name_t IS TABLE OF VARCHAR(24) INDEX BY INTEGER; --整数索引
  fruit_name fruit_name_t;
  v_ind INTEGER; 
BEGIN
  fruit_name(0) := 'apple';
  fruit_name(1) := 'banana';
  fruit_name(2) := 'pear';
  fruit_name(4) := 'orange';
   
  v_ind := fruit_name.FIRST;
WHILE (v_ind IS NOT NULL) LOOP
  DBMS_OUTPUT.PUT_LINE('v_ind is ' || v_ind);
  DBMS_OUTPUT.PUT_LINE('There is one ' || fruit_name(v_ind) || '.');
  v_ind := fruit_name.NEXT(v_ind);
END LOOP;
END;
/

DECLARE 
  TYPE fruit_name_t IS TABLE OF VARCHAR(24) INDEX BY VARCHAR(24); --字符串索引
  fruit_name fruit_name_t;
  v_ind VARCHAR(24); 
BEGIN
  fruit_name('xiaoming') := 'apple';
  fruit_name('wanliang') := 'banana';
  fruit_name('zhongzhao') := 'pear';
  fruit_name('wpr') := 'orange';
   
  v_ind := fruit_name.FIRST;
  WHILE (v_ind IS NOT NULL) LOOP
      DBMS_OUTPUT.PUT_LINE('v_ind is ' || v_ind);
      DBMS_OUTPUT.PUT_LINE('There is one ' || fruit_name(v_ind) || '.');
      v_ind := fruit_name.NEXT(v_ind);
  END LOOP;
END;
/

2.2 嵌套

定义：一维的，无边界的，由同质元素组成的集合。最开始是紧凑的，不过随着后面不断的删除操作，也会变的稀疏。即可以在 DMSQL 代码块中定义，也可以在 SCHEMA 级别定义。例如，把表中的一列定义成表类型。嵌套表示多重集合，其中的元素没有固定的顺序。

--定义了一个方案级别的嵌套表类型
CREATE TYPE EMP_NAME_T IS TABLE OF VARCHAR(12);
/
   
DECLARE 
  emp_names EMP_NAME_T := EMP_NAME_T();
BEGIN
  emp_names.extend(2);
  emp_names(1) := 'lxm'; 
  emp_names(2) := 'zh';
  FOR i IN emp_names.FIRST .. emp_names.LAST
  LOOP
    DBMS_OUTPUT.PUT_LINE('There is a employee, name is ' || emp_names(i));
  END LOOP;
END;
/

使用集合前先分配存储空间，下标为整数类型且从 1 开始。

DECLARE 
  emp_names EMP_NAME_T := EMP_NAME_T();
BEGIN
  emp_names.extend(2);
  emp_names(1) := 'lxm';
  emp_names(2) := 'zh';
  --emp_names.extend(2);
  --emp_names(3) := 'wpr';
  --emp_names(4) := 'ljl';
  FOR i IN emp_names.FIRST .. emp_names.LAST
  LOOP
    DBMS_OUTPUT.PUT_LINE('The index is ' || i);
    DBMS_OUTPUT.PUT_LINE('There is a employee, name is ' || emp_names(i));
  END LOOP;
  DBMS_OUTPUT.PUT_LINE('------Delete element of 3.');
  emp_names.delete(3);
  FOR i IN emp_names.FIRST .. emp_names.LAST
  LOOP
    DBMS_OUTPUT.PUT_LINE('The index is ' || i);
    DBMS_OUTPUT.PUT_LINE('There is a employee, name is ' || emp_names(i));
  END LOOP;
END;
/

删除第 3 个元素，该集合变的松散，再次使用 FOR 循环连续输出集合元素时，会报[-7191]:The value of the key does not exist. 错误。使用集合的内置方法 exists 可以判断 key 所对应的 value 是否存在。

DECLARE 
  emp_names EMP_NAME_T := EMP_NAME_T();
BEGIN
  emp_names.extend(2);
  emp_names(1) := 'lxm';
  emp_names(2) := 'zh';
  emp_names.extend(2);
  emp_names(3) := 'wpr';
  emp_names(4) := 'ljl';
  FOR i IN emp_names.FIRST .. emp_names.LAST
  LOOP
    DBMS_OUTPUT.PUT_LINE('The index is ' || i);
    DBMS_OUTPUT.PUT_LINE('There is a employee, name is ' || emp_names(i));
  END LOOP;
  DBMS_OUTPUT.PUT_LINE('------Delete element of 3.');
  emp_names.delete(3);
  FOR i IN emp_names.FIRST .. emp_names.LAST
  LOOP
    DBMS_OUTPUT.PUT_LINE('The index is ' || i);
    if ( emp_names.EXISTS(i) ) THEN
      DBMS_OUTPUT.PUT_LINE('There is a employee, name is ' || emp_names(i));
    END IF;
  END LOOP;
END;
/

2.3 可变数组

定义：一维的，同质的元素组成。但这种集合类型有边界并且是紧凑的，不会变的稀疏。定义一个可变数组时，必须同时指明它所能容纳的最大元素个数。VARRAY 即可用于 PL/SQL，也可以用于数据库。但是和嵌套表不一样，它里面的数据是有顺序的。VARRAY 和嵌套表的区别在于元素的个数有上限，只能删除全部的元素，不能逐一删除。

以下举例说明如何通过可变数组，将一组数据插入到对应的表中。

CREATE TYPE flower_name_t IS VARRAY(2) OF VARCHAR(24); 
/
CREATE TYPE flower_color_t IS VARRAY(2) OF VARCHAR(24); 
/
   
CREATE TABLE flower 
(
  flower_name VARCHAR(24),
  flower_color VARCHAR(24)
);
/
   
DECLARE 
  v_flowerName  flower_name_t := flower_name_t();
  v_flowerColor flower_color_t := flower_color_t();
BEGIN
  v_flowerName.extend(2);
  v_flowerName(1) := 'chrysanthemum';
  v_flowerName(2) := 'rose';
   
  v_flowerColor.extend(2);
  v_flowerColor(1) := 'yellow';
  v_flowerColor(2) := 'red';
  
  FOR i IN v_flowerName.FIRST .. v_flowerName.LAST
  LOOP
    DBMS_OUTPUT.PUT_LINE('The index is ' || i);
    DBMS_OUTPUT.PUT_LINE('The ' || v_flowerName(i) || ' color is ' ||  v_flowerColor(i));
  END LOOP;

  FORALL ind IN 1 .. v_flowerName.count
    INSERT INTO flower VALUES(v_flowerName(ind) , v_flowerColor(ind));
END;
/

三、查询方案级别定义了哪些集合

  • 通过下面的数据字典可以查询方案级别定义了各种集合类型。
SELECT * FROM  all_source WHERE type='TYPE' AND owner='DMHR';

数据字典

  • 查询哪些表中使用了自定义类型
SELECT table_name, column_name FROM all_tab_columns WHERE data_type LIKE 'CLASS%' AND table_name='T2';

自定义类型

  • 那些数据库对象依赖自定义的集合类型
SELECT * FROM ALL_DEPENDENCIES WHERE referenced_name='FLOWER_NAME_T';

数据库对象

四、集合的使用

  • 通过批量 DML 操作，提高操作效率。

先看如下代码，类似这样的插入代码需要执行 10 次上下文切换。这个 INSERT 语句需要从 PL/SQL 引擎传递给 SQL 引擎 10 次。

FOR i IN 1 .. 10
LOOP
  INSERT INTO table_name VALUES( ... );
END LOOP;

改写如下：

CREATE TABLE student
(
  stu_id NUMBER,
  stu_name VARCHAR(12)
);

DECLARE 
  --定义集合类型和变量
  TYPE STU_ID_T   IS TABLE OF NUMBER  INDEX BY INTEGER;
  TYPE STU_NAME_T IS TABLE OF VARCHAR(12) INDEX BY INTEGER;
  v_stu_id   STU_ID_T;
  v_stu_name STU_NAME_T;
  v_rows NUMBER;
BEGIN
  FOR i IN 1 .. 10 LOOP
    v_stu_id(i)   := i;
    v_stu_name(i) := DBMS_RANDOM.string('x', 3);
  END LOOP; 

  FORALL i IN 1 .. 10
    INSERT INTO student(stu_id, stu_name) VALUES(v_stu_id(i),  v_stu_name(i));
  COMMIT;

  SELECT COUNT(*) INTO v_rows FROM student;

  DBMS_OUTPUT.PUT_LINE('There is ' || v_rows || ' rows in the test table.');
END;
/

大家可以修改插入表中的行数，行数越大，性能优势越明显。

  • 使用 BULK COLLECT 子句结合集合批量获取数据并存放在内存结构中。
DECLARE 
  TYPE STU_ID_T   IS TABLE OF student.stu_id%TYPE;
  TYPE STU_NAME_T IS TABLE OF student.stu_name%TYPE;
  stu_id_tab STU_ID_T;
  stu_name_tab STU_NAME_T;
BEGIN
  SELECT stu_id, stu_name 
    BULK COLLECT INTO stu_id_tab, stu_name_tab 
    FROM student;

  FOR i IN stu_id_tab.FIRST .. stu_id_tab.LAST
  LOOP
    DBMS_OUTPUT.PUT_LINE('学生' || stu_name_tab(i) || '的学号是' || stu_id_tab(i));
  END LOOP;
END;
/

当嵌套表是通过带 BULK COLLECT 子句的 SELECT 填充时，它们被初始化和自动扩展。输出如下结果：

初始化和自动扩展

BULK COLLECT 子句类似于 SELECT 语句，当不返回任何记录时不引发 NO_DATA_FOUND 异常的游标循环。因此，检查它所生成的集合中是否包含任何数据是必要的。

因为 BULK COLLECT 子句不限制集合的大小并自动扩展它，当 SELECT 语句返回大量数据时，对结果集应该加以限制。这可以利用 BULK COLLECT 与游标 SELECT 语句并通过添加 LIMIT 选项来实现。

DECLARE 
  CURSOR stu_cur IS SELECT stu_id, stu_name FROM student;
  TYPE STU_ID_T   IS TABLE OF student.stu_id%TYPE;
  TYPE STU_NAME_T IS TABLE OF student.stu_name%TYPE;
  stu_id_tab STU_ID_T;
  stu_name_tab STU_NAME_T;
  v_limit NUMBER := 20;
  v_readnum NUMBER := 0;
BEGIN
  OPEN stu_cur;
  LOOP
    --一次批量读取 20 行
    FETCH stu_cur BULK COLLECT INTO stu_id_tab, stu_name_tab LIMIT v_limit;
  

    EXIT WHEN stu_id_tab.COUNT = 0;
    -- --EXIT WHEN stu_cur%NOTFOUND; 使用游标的NOTFOUND属性也可以判断
    v_readnum := v_readnum + 1;
    DBMS_OUTPUT.PUT_LINE('----' || v_readnum　|| '----');
    FOR i IN stu_id_tab.FIRST .. stu_id_tab.LAST
    LOOP
      DBMS_OUTPUT.PUT_LINE('学生' || stu_name_tab(i) || '的学号是' || stu_id_tab(i));
    END LOOP;

  END LOOP;
  CLOSE stu_cur;
END;
/

上面的代码可以修改为把数据读取到用户自定义的集合中，填充集合的代码同上。

TYPE stu_rec IS RECORD 
(
  stu_id student.stu_id%type,
  stu_name student stu_name%type
);
  • RETRUING 子句，BULK COLLECT 在用户 DML 语句时可以配合 RETUING 子句。
DECLARE 
  TYPE STU_ID_T   IS TABLE OF student.stu_id%TYPE;
  TYPE STU_NAME_T IS TABLE OF student.stu_name%TYPE;
  stu_id_tab STU_ID_T;
  stu_name_tab STU_NAME_T;
BEGIN
  DELETE FROM student WHERE stu_id < 3
  RETURNING stu_id, stu_name
    BULK COLLECT INTO stu_id_tab, stu_name_tab;

  DBMS_OUTPUT.PUT_LINE('Delete ' || SQL%ROWCOUNT || ' Rows');

  FOR i IN stu_id_tab.FIRST .. stu_id_tab.LAST
  LOOP
    DBMS_OUTPUT.PUT_LINE('学生' || stu_name_tab(i) || '的学号是' || stu_id_tab(i) || ',已删除');
  END LOOP;
  
  COMMIT;
END;
/

以上语句可以将删除的行信息打印出来。

删除的行信息打印

五、参考文档

更多 SQL 语言使用说明，请参考《DM_SQL 语言使用手册》，手册位于数据库安装路径 /dmdbms/doc 文件夹下。如有其他问题，请在社区内咨询。

微信扫码
分享文档
扫一扫
联系客服