/*
記錄匯出 table 的筆數
1) 使用 system stored procedure ADMIN_CMD 來執行 export
2) 將 call ADMIN_CMD 包在另一支 procedure 中，
使用 ASSOCIATE RESULT 去接傳回的 result set 即 ROWS_EXPORTED
*/
/* call ADMIN_CMD to export table orion */
C:\>db2 call sysproc.admin_cmd('export to c:\orion.del of del select * from orion')
結果集 1
--------------
ROWS_EXPORTED MSG_RETRIEVAL MSG_REMOVAL
-------------- --------------------- -------------------------
4959 - -
已選取 1 個記錄。
傳回狀態 = 0
/* Create testing table to store ROWS_EXPORTED */
CREATE TABLE EXP_RESULT
(
TBNAME VARCHAR(32),
EXP_ROWS BIGINT
);
/*
Create testing procedure
使用 ASSOCIATE LOCATORS 接 call procedure 傳回的 result set(s)
*/
CREATE PROCEDURE SP_TEST
SPECIFIC SP_TEST
LANGUAGE SQL
BEGIN
DECLARE SQLCODE INTEGER DEFAULT 0;
DECLARE RETCODE INTEGER DEFAULT 0;
DECLARE LOC1 RESULT_SET_LOCATOR VARYING;
DECLARE nRow INT;
BEGIN
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION,SQLWARNING,NOT FOUND
SET RETCODE = SQLCODE;
CALL SYSPROC.ADMIN_CMD('EXPORT TO C:\ORION.DEL OF DEL SELECT * FROM ORION');
ASSOCIATE RESULT SET LOCATOR (LOC1) WITH PROCEDURE SYSPROC.ADMIN_CMD;
ALLOCATE cur1 CURSOR FOR RESULT SET LOC1;
OPEN CUR1;
FETCH CUR1 INTO nRow;
INSERT INTO EXP_RESULT VALUES ('ORION',nRow);
CLOSE CUR1;
COMMIT;
END;
SET RETCODE = 0;
END
/* execute stored procedure */
C:\>db2 call sp_test
傳回狀態 = 0
/* check target table EXP_RESULT */
C:\>db2 select * from exp_result
TBNAME EXP_ROWS
---------- ---------------
ORION 4959
已選取 1 個記錄。
※ 最好 stored procedure: SP_TEST 改寫成 Dynamic SQL 方式
