从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;