我刚做完这个 给你个例子 CREATE OR REPLACE PROCEDURE "YYBB"."WHB_PROC" ( IN "BEGINDATE" VARCHAR(10), IN "INST_NO" VARCHAR(100), &nb...
显示全部我刚做完这个 给你个例子 CREATE OR REPLACE PROCEDURE "YYBB"."WHB_PROC" (
IN "BEGINDATE" VARCHAR(10),
IN "INST_NO" VARCHAR(100),
IN "CURR_NO" VARCHAR(10) )
SPECIFIC "SQL131104170718500"
DYNAMIC RESULT SETS 1
LANGUAGE SQL
NOT DETERMINISTIC
EXTERNAL ACTION
MODIFIES SQL DATA
OLD SAVEPOINT LEVEL
P1: BEGIN
DECLARE currentDate VARCHAR(10);
DECLARE tableName VARCHAR(500);
DECLARE sqlStr VARCHAR(5000);
DECLARE cursor1 CURSOR WITH HOLD WITH RETURN TO CALLER FOR S1;
SET currentDate = (SELECT MAX(sys_date) FROM SYS_RUN_RESULT);
IF(YEAR(DATE(BEGINDATE)) = YEAR(currentDate))
THEN SET tableName = 'I_ACCT_DEPS_CUR_HIS';
ELSE
SET tableName = 'I_ACCT_DEPS_CUR_HIS_' || YEAR(DATE(BEGINDATE));
END IF;
SET sqlStr = 'SELECT A.ACCT_NO ACCT_NO, --帐号
A.ACCT_NAME CUST_NAME, --客户名称
A.BELONG_INST BELONG_INST --所属机构
FROM '|| tableName ||' A
WHERE ''' || BEGINDATE || ''' BETWEEN HIS_START_DATE AND HIS_END_DATE AND A.COUNT_INST IN '''|| INST_NO ||'''
AND A.CURR_NO = '''|| CURR_NO ||'''';
PREPARE S1 FROM sqlStr;
open cursor1;
END P1;
收起