记录与集合

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. 可变数组

关联数组

定义:只能在 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;
/

嵌套

定义:一维的,无边界的,由同质元素组成的集合。最开始是紧凑的,不过随着后面不断的删除操作,也会变的稀疏。即可以在 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;
/

可变数组

定义:一维的,同质的元素组成。但这种集合类型有边界并且是紧凑的,不会变的稀疏。定义一个可变数组时,必须同时指明它所能容纳的最大元素个数。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 文件夹下。如有其他问题,请在社区内咨询。

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