急！！！求教：DB2 存储过程中PREPARE 语法问题

rick_zhang

见以下存储过程，编译通过，但运行的时候报错。该DECALRE 的CURSOR SQL单独执行是正常的，但放到PREPARE重就运行报错，不知道是怎么回事儿，谁能帮我看看
--以下为存储过程--编译可以通过
CREATE PROCEDURE PROCEDURE1 (  )
        DYNAMIC RESULT SETS 1
------------------------------------------------------------------------
-- SQL 存储过程
------------------------------------------------------------------------
P1: BEGIN
        DECLARE sSql varchar(1000);
        declare tmp char(4);
        set sSql='ALTER TABLE CMS.CMS_AGENT_IMPORT activate NOT LOGGED INITIALLY WITH EMPTY TABLE';
        set tmp = 'tet';
        prepare s1 from sSql;
        execute s1;
        -- 声明游标
        set sSql = 'DECLARE cursor1 CURSOR WITH RETURN FOR SELECT A.AGNTCOY, B.AGNTBR, A.ARACDE, B.CLNTNUM, A.AGNTNUM, CMS.FMT_DATE(A.DTEAPP) AS DTEAPP,B.AGTYPE,(SELECT ZRECRUIT FROM LCNDTA.P_ZMANPF AS C WHERE AGNTCOY = A.AGNTCOY AND AGNTNUM = A.AGNTNUM) AS ZRECRUIT,A.REPORTAG, A.TLAGLICNO, CMS.FMT_DATE(A.TLICEXPDT) AS TLICEXPDT,A.TAGSUSIND, A.PAYMTH, A.PAYFRQ, A.CURRCODE, A.MINSTA, CMS.FMT_DATE(A.DTETRM) AS DTETRM,A.BCMTAB, A.SCMTAB, A.RCMTAB, A.AGENT_CLASS, A.OCMTAB, tmp AS ZWARPF,CURRENT TIMESTAMP AS DATIME FROM LCNDTA.P_AGLFPF AS A, LCNDTA.P_AGNTPF AS B WHERE A.AGNTNUM = B.AGNTNUM AND A.AGNTCOY = B.AGNTCOY';
        prepare s1 from sSql;
        execute s1;

        -- 游标对客户机应用程序保持打开
END P1

---以下为错误输出---
CMS.PROCEDURE1 － 已开始运行。

结果集中返回的数据限于前 50 行。

结果集列中返回的数据限于前 100 个字节或字符。

CMS.PROCEDURE1 － 正在调用存储过程。

创建 存储过程 返回 SQLCODE: -104, SQLSTATE: 42601。

CMS.PROCEDURE1 － 运行以下对象期间出现异常：
数据库管理器发生错误。 在 "BEGIN-OF-STATEMENT" 后面找到异常标记 "DECLARE CURSOR1 CURSOR WITH RETURN FOR SELE"。预期标记可能包括："<space>"。

CMS.PROCEDURE1 － 回滚失败。

CMS.PROCEDURE1 － 运行失败。

---这个SQL单独执行是可以通过的
SELECT A.AGNTCOY, B.AGNTBR, A.ARACDE, B.CLNTNUM, A.AGNTNUM, CMS.FMT_DATE(A.DTEAPP)
AS DTEAPP,B.AGTYPE,(SELECT ZRECRUIT FROM LCNDTA.N_ZMANPF AS C
WHERE AGNTCOY = A.AGNTCOY AND AGNTNUM = A.AGNTNUM) AS ZRECRUIT,A.REPORTAG,
  A.TLAGLICNO, CMS.FMT_DATE(A.TLICEXPDT) AS TLICEXPDT,A.TAGSUSIND, A.PAYMTH,
   A.PAYFRQ, A.CURRCODE, A.MINSTA, CMS.FMT_DATE(A.DTETRM) AS DTETRM,A.BCMTAB,
   A.SCMTAB, A.RCMTAB, A.AGENT_CLASS, A.OCMTAB, '' AS ZWARPF,
   CURRENT TIMESTAMP AS DATIME FROM LCNDTA.P_AGLFPF AS A, LCNDTA.P_AGNTPF AS B
   WHERE A.AGNTNUM = B.AGNTNUM AND A.AGNTCOY = B.AGNTCOY

Rich79

你比没有声明游标，你的sSql中含有声明游标的语法。

DECLARE cursor1 CURSOR WITH RETURN FOR SELECT A.AGNTCOY, B.AGNTBR, A.ARACDE, B.CLNTNUM, A.AGNTNUM, CMS.FMT_DATE(A.DTEAPP) AS DTEAPP,B.AGTYPE,(SELECT ZRECRUIT FROM LCNDTA.P_ZMANPF AS C WHERE AGNTCOY = A.AGNTCOY AND AGNTNUM = A.AGNTNUM) AS ZRECRUIT,A.REPORTAG, A.TLAGLICNO, CMS.FMT_DATE(A.TLICEXPDT) AS TLICEXPDT,A.TAGSUSIND, A.PAYMTH, A.PAYFRQ, A.CURRCODE, A.MINSTA, CMS.FMT_DATE(A.DTETRM) AS DTETRM,A.BCMTAB, A.SCMTAB, A.RCMTAB, A.AGENT_CLASS, A.OCMTAB, tmp AS ZWARPF,CURRENT TIMESTAMP AS DATIME FROM LCNDTA.P_AGLFPF AS A, LCNDTA.P_AGNTPF AS B WHERE A.AGNTNUM = B.AGNTNUM AND A.AGNTCOY = B.AGNTCOY';

OPEN cursor1;

试试