一个很好的DB2存储过程例子

来源：互联网发布：打码网站源码编辑：程序博客网时间：2024/06/05 21:17
CREATE OR REPLACE PROCEDURE importData(IN importPathAndFileName varchar(100), IN tabSchema varchar(100), IN tabName varchar(100), IN exportMSGPath varchar(100))
    LANGUAGE SQL
    P2:BEGIN ATOMIC
        DECLARE v_importPathAndFileName varchar(100);
        DECLARE v_tabSchema varchar(100);
        DECLARE v_tabName varchar(100);
        DECLARE v_exportMSGPath varchar(100);
        DECLARE v_date varchar(12);
        DECLARE v_datetime varchar(30);
        DECLARE v_id int;
        DECLARE v_sql_import varchar(200);
        DECLARE v_sql_export varchar(200);
        DECLARE loc1 result_set_locator varying;
        DECLARE v_rows_read VARCHAR(100) DEFAULT '';
        DECLARE v_rows_skipped VARCHAR(100) DEFAULT '';
        DECLARE v_rows_inserted VARCHAR(100) DEFAULT '';
        DECLARE v_rows_updated VARCHAR(100) DEFAULT '';
        DECLARE v_rows_rejected VARCHAR(100) DEFAULT '';
        DECLARE v_rows_committed VARCHAR(100) DEFAULT '';
        DECLARE v_msg_retrieval VARCHAR(512) DEFAULT '';
        DECLARE v_msg_removal VARCHAR(512) DEFAULT '';
        DECLARE v_at_end INT DEFAULT 0;
         
        DECLARE SQLCODE INT DEFAULT 0;
        DECLARE SQLSTATE CHAR(5) DEFAULT '00000';
         
        DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_at_end = 1;
        DECLARE CONTINUE HANDLER FOR SQLWARNING
            begin
            for MSG_ID as 
                SELECT COALESCE(MAX(msg_id),0) as id from importMSGOutput
            do
                set v_id = MSG_ID.id + 1;
            end for;
            SET v_datetime = to_char(CURRENT TIMESTAMP, 'yyyy-mm-dd hh24:mi:ss');
            INSERT INTO importMSGOutput VALUES(v_id, v_datetime,'   read rows:'||v_rows_read ,'   skipped rows:'||v_rows_skipped,'   inserted rows:'||v_rows_inserted,'   updated rows:'||v_rows_updated, '   rejected rows:'||v_rows_rejected,'   committed rows:'||v_rows_committed, '   msg_retrieval:'||v_msg_retrieval, '   msg_removal:'||v_msg_removal,'   importPathAndFileName:' || v_importPathAndFileName || ', tabSchema:' || v_tabSchema || ', tabName:' || v_tabName || ', exportMSGPath:' || v_exportMSGPath,'   exception:SQLCODE-'||SQLCODE ||' SQLSTATE-'||SQLSTATE);
             
            end;
        DECLARE UNDO HANDLER FOR SQLEXCEPTION
            begin 
            for MSG_ID as 
                SELECT COALESCE(MAX(msg_id),0) as id from importMSGOutput
            do
                set v_id = MSG_ID.id + 1;
            end for;
            SET v_datetime = to_char(CURRENT TIMESTAMP, 'yyyy-mm-dd hh24:mi:ss');
            INSERT INTO importMSGOutput VALUES(v_id,v_datetime,'   read rows:'||v_rows_read ,'   skipped rows:'||v_rows_skipped,'   inserted rows:'||v_rows_inserted,'   updated rows:'||v_rows_updated, '   rejected rows:'||v_rows_rejected,'   committed rows:'||v_rows_committed, '   msg_retrieval:'||v_msg_retrieval, '   msg_removal:'||v_msg_removal,'   importPathAndFileName:' || v_importPathAndFileName || ', tabSchema:' || v_tabSchema || ', tabName:' || v_tabName || ', exportMSGPath:' || v_exportMSGPath,'   exception:SQLCODE-'||SQLCODE ||' SQLSTATE-'||SQLSTATE);
            end;
         
        SET v_importPathAndFileName = COALESCE(rtrim(ltrim(importPathAndFileName)),'');
        SET v_tabSchema = COALESCE(rtrim(ltrim(tabSchema)),'');
        SET v_tabName = COALESCE(rtrim(ltrim(tabName)),'');
        SET v_exportMSGPath = COALESCE(rtrim(ltrim(exportMSGPath)),'');
        SET v_date = to_char(CURRENT DATE,'yyyymmdd');
        --SET v_datetime = to_char(CURRENT TIMESTAMP, 'yyyy-mm-dd hh24:mi:ss');
 
        IF(v_importPathAndFileName != '' AND v_tabSchema != '' AND v_tabName != '' AND v_exportMSGPath != '') THEN
            SET v_sql_import = 'import from '|| v_importPathAndFileName ||' of DEL modified by codepage=1375 DELPRIORITYCHAR messages on server insert into '|| v_tabSchema ||'.'|| v_tabName;
             
            --SET v_sql = 'import from '|| v_importPathAndFileName ||' of DEL modified by codepage=1375 DELPRIORITYCHAR messages on server insert into '|| v_tabSchema ||'.'|| v_tabName 
            --    ||' (UNIQUE_ID, NEED_TO_PATCH, MASK_REPLACE_STR, MASK_REPLACE_CODE, PATCH_TO_UTF8, BIG5, TABSCHEMA, TABNAME, COLNAME, UPD_ROW_COUNT, LAST_UPD_TS)';
             
            CALL SYSPROC.admin_cmd(v_sql_import);
             
            associate result set locator(loc1) with procedure SYSPROC.admin_cmd;
            allocate cursor1 cursor for result set loc1;
            open cursor1;
             
            FETCH cursor1 INTO v_rows_read,v_rows_skipped,v_rows_inserted, v_rows_updated, v_rows_rejected,v_rows_committed,v_msg_retrieval,v_msg_removal;
            WHILE v_at_end = 0 
            DO
                for MSG_ID as 
                SELECT COALESCE(MAX(msg_id),0) as id from importMSGOutput
                do
                set v_id = MSG_ID.id + 1;
                end for;
                SET v_datetime = to_char(CURRENT TIMESTAMP, 'yyyy-mm-dd hh24:mi:ss');
                --INSERT INTO importMSGOutput VALUES('id:'||v_id,'   datetime:'||v_datetime,'   read rows:'||v_rows_read ,'   skipped rows:'||v_rows_skipped,'   inserted rows:'||v_rows_inserted,'   updated rows:'||v_rows_updated, '   rejected rows:'||v_rows_rejected,'   committed rows:'||v_rows_committed, '   msg_retrieval:'||v_msg_retrieval, '   msg_removal:'||v_msg_removal,'   importPathAndFileName:' || v_importPathAndFileName || ', tabSchema:' || v_tabSchema || ', tabName:' || v_tabName || ', exportMSGPath:' || v_exportMSGPath,'   exception:SQLCODE-'||SQLCODE ||' SQLSTATE-'||SQLSTATE);
                INSERT INTO importMSGOutput VALUES(v_id,v_datetime,'   read rows:'||v_rows_read ,'   skipped rows:'||v_rows_skipped,'   inserted rows:'||v_rows_inserted,'   updated rows:'||v_rows_updated, '   rejected rows:'||v_rows_rejected,'   committed rows:'||v_rows_committed, '   msg_retrieval:'||v_msg_retrieval, '   msg_removal:'||v_msg_removal,'   importPathAndFileName:' || v_importPathAndFileName || ', tabSchema:' || v_tabSchema || ', tabName:' || v_tabName || ', exportMSGPath:' || v_exportMSGPath,'   ');
                --close cs3;
                --FETCH cursor1 INTO v_rows_read,v_rows_skipped,v_rows_inserted, v_rows_updated, v_rows_rejected,v_rows_committed,v_msg_retrieval,v_msg_removal;
            END WHILE;
            SET v_sql_export ='export to ' || v_exportMSGPath || v_tabSchema || '_' || v_tabName || '_' || v_date ||'.load_out of DEL modified by STRIPLZEROS messages on server select * from importMSGOutput';
            --SET v_sql_export ='export to c:\aaaaaaaaaaaaaaaaaaaaa.load_out of DEL modified by STRIPLZEROS messages on server ' || v_msg_retrieval;
            CALL SYSPROC.admin_cmd(v_sql_export);
            close cursor1;
             
            begin
            declare cursor2 cursor with return to caller for
                select * from importMSGOutput where msg_id = (select max(msg_id) from importMSGOutput);
                --v_msg_retrieval;
            open cursor2;
            end;
        ELSEIF(v_exportMSGPath = '') THEN
            for MSG_ID as 
                SELECT COALESCE(MAX(msg_id),0) as id from importMSGOutput
            do
                set v_id = MSG_ID.id + 1;
            end for;
            SET v_datetime = to_char(CURRENT TIMESTAMP, 'yyyy-mm-dd hh24:mi:ss');
            INSERT INTO importMSGOutput VALUES(v_id,v_datetime,'   read rows:'||v_rows_read ,'   skipped rows:'||v_rows_skipped,'   inserted rows:'||v_rows_inserted,'   updated rows:'||v_rows_updated, '   rejected rows:'||v_rows_rejected,'   committed rows:'||v_rows_committed, '   msg_retrieval:'||v_msg_retrieval, '   msg_removal:'||v_msg_removal,'   importPathAndFileName:' || v_importPathAndFileName || ', tabSchema:' || v_tabSchema || ', tabName:' || v_tabName || ', exportMSGPath:' || v_exportMSGPath,'   parameter - exportMSGPath can not null,otherwise can not export file.');
            SET v_sql_export ='export to ' || v_exportMSGPath || v_tabSchema || '_' || v_tabName || '_' || v_date ||'.load_out of DEL modified by STRIPLZEROS messages on server select * from importMSGOutput';
             
            CALL SYSPROC.admin_cmd(v_sql_export);
 
            begin
            declare cursor2 cursor with return to caller for
                select * from importMSGOutput where msg_id = (select max(msg_id) from importMSGOutput);
                --v_msg_retrieval;
            open cursor2;
            end;
        ELSE 
            for MSG_ID as 
                SELECT COALESCE(MAX(msg_id),0) as id from importMSGOutput
            do
                set v_id = MSG_ID.id + 1;
            end for;
            SET v_datetime = to_char(CURRENT TIMESTAMP, 'yyyy-mm-dd hh24:mi:ss');
            INSERT INTO importMSGOutput VALUES(v_id,v_datetime,'   read rows:'||v_rows_read ,'   skipped rows:'||v_rows_skipped,'   inserted rows:'||v_rows_inserted,'   updated rows:'||v_rows_updated, '   rejected rows:'||v_rows_rejected,'   committed rows:'||v_rows_committed, '   msg_retrieval:'||v_msg_retrieval, '   msg_removal:'||v_msg_removal,'   importPathAndFileName:' || v_importPathAndFileName || ', tabSchema:' || v_tabSchema || ', tabName:' || v_tabName || ', exportMSGPath:' || v_exportMSGPath,'   parameter - importPathAndFileName,tabSchema,tabName can not null');
            SET v_sql_export ='export to ' || v_exportMSGPath || v_tabSchema || '_' || v_tabName || '_' || v_date ||'.load_out of DEL modified by STRIPLZEROS messages on server select * from importMSGOutput';
             
            CALL SYSPROC.admin_cmd(v_sql_export);
 
            begin
            declare cursor2 cursor with return to caller for
                select * from importMSGOutput where msg_id = (select max(msg_id) from importMSGOutput);
                --v_msg_retrieval;
            open cursor2;
            end;
        END IF;
    END P2