达梦数据库临时表有2种类型:
on commit preserve rows --session 会话级临时表
on commit delete rows --transaction 事务级临时表
而我们经常在MSSQL中经常操作的是会话级的,通常需要先删后建,但转达梦的时候,我通常使用的是事务级别的临时表比较多。
执行代码:
为了MSSQL转达梦时便于转后对比,临时表的需要在执行存储过程时进行创建和用后删除,其中为了防止命名冲突,临时表的命名规则为特,temp_xxx_@newid, @newid是随机生成的id.
执行代码如下:
SET @newid=(select REPLACE(newid2,'-','') AS nid);-- INTO @newid;
EXECUTE IMMEDIATE 'CREATE GLOBAL TEMPORARY TABLE tmp_xxxview_'+@newid+'(itype VARCHAR(10),id CHAR(36)) ON COMMIT DELETE ROWS';
使用后删除操作
EXECUTE IMMEDIATE 'DROP TABLE tmp_xxxview_'+@newid;
创建存储过程中使用临时表sql如下:
CREATE OR REPLACE PROCEDURE proc_xxxx(yearly INT=0)
IS
@newid CHAR(32);@sql TEXT;
BEGIN
SET @newid=(select REPLACE(newid2,'-','') AS nid);-- INTO @newid;
EXECUTE IMMEDIATE 'CREATE GLOBAL TEMPORARY TABLE tmp_xxxview_'+@newid+'(itype VARCHAR(10),id CHAR(36)) ON COMMIT DELETE ROWS';
SET @sql='
INSERT INTO tmp_xxxview_'+@newid+'
SELECT *
FROM aa;';
--PRINT @sql;
EXECUTE IMMEDIATE @sql;
EXECUTE IMMEDIATE 'DROP TABLE tmp_xxxview_'+@newid;
END proc_xxxx;