达梦数据库学习之一:临时表和集合类型

从SqlServer迁移到达梦数据库的主要教虑到以下几个方面

1.临时表

  SQL SERVER中临时表用完表结构是自动删除的，这点有根本性的区别

   达梦临时表有两种类型：会话级的临时表和事务级的临时表。

  1）ON COMMIT DELETE ROWS  --事务级临时表

        它是临时表的默认参数，表示临时表中的数据仅在事务过程（Transaction）中有效，当事务提交（COMMIT）后，临时表的暂时段将被自动截断（TRUNCATE），但是临时表的结构 以及元数据还存储在用户的数据字典中。如果临时表完成它的使命后，最好删除临时表，否则数据库会残留很多临时表的表结构和元数据。

CREATE GLOBAL TEMPORARY TABLE JYB.TMP_SESSION(ID INT,NAME VARCHAR(128)) ON COMMIT DELETE ROWS;

  2）ON COMMIT PRESERVE ROWS --会话级临时表

它表示临时表的内容可以跨事务而存在，不过，当该会话结束时，临时表的暂时段将随着会话的结束而被丢弃，临时表中的数据自然也就随之丢弃。但是临时表的结构以及元数据还存储在用户的数据字典中。如果临时表完成它的使命后，最好删除临时表，否则数据库会残留很多临时表的表结构和元数据。

CREATE GLOBAL TEMPORARY TABLE JYB.TMP_TRAN(ID INT ,NAME VARCHAR(128)) ON COMMIT PRESERVE ROWS;

 3) --WITH AS短语，也叫做子查询部分，是用来定义一个SQL片断，该SQL片断会被整个SQL语句所用到,这个与sql server一样。

with db1 as
  (select * from "SysData"."KT_U_User" where "UserName" like '%张%')
select * from db1;    

先执行select * from "SysData"."KT_U_User" where "UserName" like '%张%'得到一个结果，将这个结果记录为db1 ，再执行select * from db1 语句。db1 表只是一个别名。也就是将重复用到的大批量 的SQL语句，放到with as 中，加一个别名，在后面用到的时候就可以直接用。对于大批量的SQL数据，起到优化的作用。

4.从查询语句中导入临时表（会话级）

 对应于sqlserver的into #tmp1,可以用以下方式实现

  DROP TABLE IF EXISTS TMP1;
  CREATE GLOBAL TEMPORARY TABLE TMP1 ON COMMIT PRESERVE ROWS as (select * from "SysData"."KT_U_User" where "UserName" like '%李%');
  select * from TMP1;

5.动态执行Sql

  要想和sql server一样，还得用到动态执行Sql，先熟习一下 execute immediate

declare 
  sql1 varchar(500);
  UserId varchar(50):='admin';
begin
  sql1:='declare UserName varchar(50); 
begin 
  select "UserName" into UserName from "SysData"."KT_U_User" where "UserId"=?;
  select UserName; 
end;
';
 execute immediate sql1 using UserId;
end;

     SQL SERVER的应用一般在存储过程中创建存储过程，用完了，临时表自动清除比较方便，但是在达梦中临时表需要提前创建成功才能使用，表结构会保留，需要注意

--达梦的存储过程中执行DDL语句需要用动态SQL

CREATE OR REPLACE PROCEDURE "JYB"."P_TMP_SQLDB"
as
 sql1 varchar(500);
BEGIN
  --过程中不支持创建临时表,用动态sql语句，只能一次一条记录
  sql1:='CREATE GLOBAL TEMPORARY TABLE JYB.TMP_SESSION(ID INT,NAME VARCHAR(128)) ON COMMIT DELETE ROWS;';
  execute immediate sql1;
END;

调用

  declare 
    UserName varchar2(50);
    num int; 
  begin 
     --判断临时表是否存在
     select COUNT(*) as "Count" 
        INTO num
        from sysobjects A  where name= 'TMP_SESSION'  
           and EXISTS(select OBJECT_ID from all_objects where OBJECT_TYPE='SCH' and OBJECT_NAME = 'JYB' and OBJECT_ID=A.SCHID);
     --select COUNT(1) INTO num from dba_segments where dba_segments.OWNER=''JYB'' and SEGMENT_NAME=''TMP_SESSION''         
     if num=0 then
        call "JYB"."P_TMP_SQLDB"();
     end if;
  end;
  INSERT INTO JYB.TMP_SESSION(ID,NAME) SELECT 1,'张本中' union SELECT 2,'李新田'; 
  SELECT * FROM JYB.TMP_SESSION;
  drop table JYB.TMP_SESSION;
  

结果显示

 综合实现sql server 的into #table：

CREATE OR REPLACE PROCEDURE "JYB"."P_TMP_SQLDB"
(dbname varchar(100),SqlVar VARCHAR(2000))
as
 sql1 varchar(2500);
BEGIN
  --过程中不支持创建临时表,用动态sql语句
  sql1:='CREATE GLOBAL TEMPORARY TABLE '||dbname||' ON COMMIT PRESERVE ROWS as ('||SqlVar||')';
  execute immediate sql1;  
END;

--------------------------

declare 
  dbname,excsql varchar(200);
  num int; 
begin 
     --判断临时表是否存在
     select COUNT(*) as "Count" 
        INTO num
        from sysobjects A  where name= 'TMP_SESSION'  
           and EXISTS(select OBJECT_ID from all_objects where OBJECT_TYPE='SCH' and OBJECT_NAME = 'JYB' and OBJECT_ID=A.SCHID);
     --select COUNT(1) INTO num from dba_segments where dba_segments.OWNER=''JYB'' and SEGMENT_NAME=''TMP_SESSION''         
     if num=0 then
        excsql:='select * from "SysData"."KT_U_User" where "UserName" like ''%李%''';
        dbname:='JYB.TMP_111';
        call "JYB"."P_TMP_SQLDB"(dbname,excsql);
     end if;   
end;  

SELECT * FROM JYB.TMP_111;
drop table JYB.TMP_111;

2.集合类型

   1).嵌套表

        嵌套表和varray类型用法类似，主要区别在于嵌套表在使用时不用指定元素上限。这里我们采用结合游标制造一个比较复杂的例子，我们先采用一个游标ename_cursor来存储job_id为21的员工姓名信息，然后通过遍历游标把对应的员工姓名存储到嵌套表中，相关代码如下：

DECLARE
   --下面这句是嵌套表的声明，可以看到这里并不用指定元素上限
   TYPE ename_array_type is table of dmhr.employee.employee_name%TYPE;
   --定义一个游标
   CURSOR ename_cursor is select employee_name from dmhr.employee where job_id=21;
   --初始化嵌套表，此时嵌套表为空
   ename_array ename_array_type:=ename_array_type();--初始化嵌套表
   i number:=0;
BEGIN
   for ename in ename_cursor
   loop
     i:=i+1;
     --执行extend函数，为数组增加一个null元素
     ename_array.EXTEND;
     ename_array(i):=ename.employee_name;
     print(ename.employee_name);
   end loop;
   --注意嵌套表下标是从1开始的，而不是0
   for j in 1..ename_array.count() loop
      print(ename_array(j));
   end loop;
END;

  2).数组类型
       在DM PL/SQL中，我们也可以使用直接声明的方式来使用数组类型，也就是array类型。跟大多数编程语言类型，DM PL/SQL中的数组类型可以使用静态或动态的方式定义，具体演示如下。

静态数组：
 

DECLARE
  --TYPE 定义一维数组类型
  TYPE Arr IS ARRAY VARCHAR[3];
  a Arr;
BEGIN
  FOR I IN 1..3 LOOP
    a[I]:=I*10;
    PRINT a[I];
  END LOOP;
END;

   3.)动态数组：

DECLARE
  TYPE Arr IS ARRAY VARCHAR[];
  a Arr;
BEGIN
  a := NEW VARCHAR[4];--动态分配空间
  FOR I IN 1..4 LOOP
    a[I] :=I*4;
    PRINT a[I];
  END LOOP;
END;

   4.)索引表：
   索引表的特点：
       1.可以使用数字或者字母作为下标
       2.以数字做下标时可以使用负数
       3.只能在PL/SQL块中使用，无法作为表字段
下面进行一些测试，首先是数字做下标的索引表

declare
  type index_tab_type is table of varchar(30) index by int;
  v_table index_tab_type;
begin
  v_table(-1):='hello';--设定下标为-1的元素的值
  v_table(1):='and ';--设定下标为1的元素的值
  v_table(5):='world';
  print('元素个数为：'||v_table.count);
  print('下标为1的元素：'||v_table(1));
  print('第一个元素是'||v_table(v_table.first));--输出第一个元素
  print('最后一个元素是'||v_table(v_table.last));--输出最后一个元素
end;

​​​​​​​

 然后是字母做下标的索引表

declare
  type index_tab_type is table of varchar(30) index by varchar(1);
  v_table index_tab_type;
begin
  v_table('a'):='hello';--设定下标为-1的元素的值
  v_table('b'):=' and ';
  v_table('d'):='world';
  print('元素个数：'||v_table.count);
  print('下标为b的元素：'||v_table('b'));
  print('第一个元素'||v_table(v_table.first));
  print('最后一个元素'||v_table(v_table.last));
end;