DB2创建伪表存储过程

调用示例
call BSDCMACW.P_CREATE_TEMP_TABLE('BSDCMACW','test12','01','select * from bsdcmacw.T_ADS_FACT_CWMO_0010');

创建伪表
SET CURRENT SCHEMA = "bsdcmacw";

SET CURRENT PATH = SYSIBM,SYSFUN,SYSPROC,SYSIBMADM,BSDCMACW;

CREATE OR REPLACE PROCEDURE BSDCMACW.P_CREATE_TEMP_TABLE (
    IN P_OWNER	VARCHAR(32),
    IN P_TAB_NAME	VARCHAR(32),
    IN P_SEQ_NO	VARCHAR(32),
    IN P_QUERY_STR	CLOB(1M) )
  SPECIFIC P_CREATE_TEMP_TABLE
  LANGUAGE SQL
  NOT DETERMINISTIC
  EXTERNAL ACTION
  MODIFIES SQL DATA
  CALLED ON NULL INPUT
  INHERIT SPECIAL REGISTERS
  OLD SAVEPOINT LEVEL
/*声明为自治事务*/
--AUTONOMOUS
BEGIN
/*
| Procedure name: p_create_temp_table
|
| Overview: 创建临时表
|
| Author(s): 李军
|
| Modification History:
|   Date        Who           What
|   2013.01    李军          Created
*/
DECLARE SQLCODE INTEGER;
DECLARE SQLSTATE, v_sqlstate CHAR(5);
DECLARE MESSAGE_TEXT VARCHAR(2048);
DECLARE v_sql_str CLOB(1M);
DECLARE v_tab_name, v_full_name, v_tmp_name VARCHAR(128);
DECLARE v_num INT;
DECLARE v_cur CURSOR FOR STMT;
 
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
--捕获出错信息
GET DIAGNOSTICS EXCEPTION 1 MESSAGE_TEXT = MESSAGE_TEXT;
SELECT SQLSTATE INTO v_sqlstate FROM sysibm.dual;
--中间临时表如果存在，则删除
SET v_sql_str = '
SELECT COUNT(*)
FROM syscat.tables t
WHERE t.tabschema = UPPER(''' || p_owner || ''')
AND t.tabname = UPPER(''' || v_tab_name || '_MRN'')
';
--CALL p_exec_into(v_sql_str, v_num);
 
PREPARE STMT FROM v_sql_str;
OPEN v_cur;
FETCH v_cur INTO v_num;
CLOSE v_cur;

IF v_num > 0 THEN
SET v_sql_str = '
DROP TABLE ' || v_tmp_name
;
EXECUTE IMMEDIATE v_sql_str;
COMMIT;
END IF;
--抛出出错信息
SIGNAL SQLSTATE v_sqlstate SET MESSAGE_TEXT = MESSAGE_TEXT;
END;
 
--1. 创建临时表
SET v_tab_name = p_tab_name || '_' || p_seq_no;
SET v_full_name = p_owner || '.' || v_tab_name;
SET v_tmp_name = v_full_name || '_MRN';
SET v_sql_str = 'CREATE TABLE ' || v_tmp_name
|| ' AS (' || p_query_str || ') DEFINITION ONLY IN BSDCMACW'
;
EXECUTE IMMEDIATE v_sql_str;
COMMIT;
 
--2. 插入数据
SET v_sql_str = 'INSERT INTO ' || v_tmp_name || ' '
|| p_query_str
;
EXECUTE IMMEDIATE v_sql_str;
COMMIT;
 
--3. 更改表名
--3.1 若目标临时表与其建表语句中表同名则删除源表
SET v_sql_str = '
SELECT COUNT(*) 
FROM syscat.tables t
WHERE t.tabschema = UPPER(''' || p_owner || ''')
AND t.tabname = UPPER(''' || v_tab_name || ''')
';
--CALL p_exec_into(v_sql_str, v_num);
PREPARE STMT FROM v_sql_str;
OPEN v_cur;
FETCH v_cur INTO v_num;
CLOSE v_cur;

IF v_num > 0 THEN
SET v_sql_str = '
DROP TABLE ' || v_full_name
;
EXECUTE IMMEDIATE v_sql_str;
COMMIT;
END IF;
--3.2 更改表名
SET v_sql_str = 'RENAME TABLE ' || v_tmp_name || ' TO ' || v_tab_name
;
EXECUTE IMMEDIATE v_sql_str;
COMMIT;
 
 
END;

SET CURRENT SCHEMA = BSDCMACW;