来结贴CREATE PROCEDURE PROC_BACKUP_GFJ_TABLE( IN IN_ETL_DATE CHARACTER(8), OUT OUT_RETURN_STATUS INTEGER, OUT OUT_RETURN_MSG VARCHAR(2000) )LANGUAGE SQLBEGIN --应用变量定义&n...
显示全部来结贴
CREATE PROCEDURE PROC_BACKUP_GFJ_TABLE(
IN IN_ETL_DATE CHARACTER(8),
OUT OUT_RETURN_STATUS INTEGER,
OUT OUT_RETURN_MSG VARCHAR(2000) )
LANGUAGE SQL
BEGIN
--应用变量定义
DECLARE v_param VARCHAR(4000);--动态ddl命令
DECLARE v_tab_name VARCHAR(100);--临时表名
DECLARE v_sql VARCHAR(8000);---动态sql
declare v_sql2 varchar(8000);
--定义错误变量
DECLARE v_return_status INT DEFAULT 0;--
DECLARE v_msg VARCHAR(2000) DEFAULT '';--
DECLARE SQLCODE INT DEFAULT 0;--
DECLARE SQLSTATE CHAR(5) DEFAULT '00000';--
declare v_errorstatement VARCHAR(50) DEFAULT '';--
declare v_procedure_name VARCHAR(300);--
DECLARE V_ETL_DATE varchar(10);
DECLARE V_DEL_DATE VARCHAR(10);
DECLARE V_TEMP_DATE VARCHAR(8);
--自定义断点
DECLARE v_point CHAR(4) DEFAULT '0000';--
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
GET DIAGNOSTICS EXCEPTION 1 v_msg = MESSAGE_TEXT;--
SET v_errorstatement = ',SQLSTATE=' || SQLSTATE|| ',SQLCODE=' || char(SQLCODE); --
SET v_msg = v_msg ||v_errorstatement||',Breakpoint=' || v_point;--
SET v_msg = v_procedure_name ||' run error! The error message is : '||v_msg;--
SET v_return_status = 1;--
SET out_return_status = v_return_status;--
SET out_return_msg = v_msg;--
ROLLBACK;--
END;--
SET v_procedure_name = 'PROC_TEST_BACK_TABLE';
SET V_ETL_DATE=SUBSTR(IN_ETL_DATE,1,4)||'-'||SUBSTR(IN_ETL_DATE,5,2)||'-'||SUBSTR(IN_ETL_DATE,7,2);
set V_DEL_DATE=CAST((date(V_ETL_DATE) - 6 month) AS VARCHAR(10));
SET V_TEMP_DATE= SUBSTR(V_DEL_DATE,1,4)||SUBSTR(V_DEL_DATE,6,2)||SUBSTR(V_DEL_DATE,9,2);--删除六个月前数据
--删除表支持重跑
SELECT TABNAME INTO v_tab_name FROM SYSCAT.TABLES WHERE UCASE(TABNAME) = 'TEST_'||IN_ETL_DATE AND TABSCHEMA='VCRMDB' WITH UR;--
SET v_point = '0041';
IF UCASE(v_tab_name) = 'TEST_'||IN_ETL_DATE THEN
SET v_sql2 = 'drop table VCRMDB.TEST_'||IN_ETL_DATE;
EXECUTE IMMEDIATE v_sql2;
COMMIT;
SET v_sql = 'create table VCRMDB.TEST_'||IN_ETL_DATE || ' like VCRMDB.TEST';
EXECUTE IMMEDIATE v_sql;
COMMIT;
ELSE
SET v_sql = 'create table VCRMDB.TEST_'||IN_ETL_DATE || ' like VCRMDB.TEST';
EXECUTE IMMEDIATE v_sql;
COMMIT;
END IF;
set v_point = '0021';--
SET v_param = 'LOAD FROM (select * FROM VCRMDB.TEST with ur) OF CURSOR INSERT INTO VCRMDB.TEST_'||IN_ETL_DATE ;
CALL SYSPROC.ADMIN_CMD(v_param);
SELECT TABNAME INTO v_tab_name FROM SYSCAT.TABLES WHERE UCASE(TABNAME) = 'TEST_'||V_TEMP_DATE AND TABSCHEMA='VCRMDB' WITH UR;--
SET v_point = '0052';
IF UCASE(v_tab_name) = 'TEST_'||V_TEMP_DATE THEN
SET v_sql2 = 'drop table VCRMDB.TEST_'||V_TEMP_DATE;
EXECUTE IMMEDIATE v_sql2;
COMMIT;
END IF;
SET OUT_RETURN_STATUS = v_return_status;--
SET OUT_RETURN_MSG = v_msg;--
END@
收起