麻烦看一下什么语法错误
SQL State: 42601
Vendor Code: -104
Message: [SQL0104] Token PC_CURSOR was not valid. Valid tokens: GLOBAL. Cause . . . . . : A syntax error was detected at token PC_CURSOR. Token PC_CURSOR is not a valid token. A partial list of valid tokens is GLOBAL. This list assumes that the statement is correct up to the token. The error may be earlier in the statement, but the syntax of the statement appears to be valid up to this point. Recovery . . . : Do one or more of the following and try the request again: -- Verify the SQL statement in the area of the token PC_CURSOR. Correct the statement. The error could be a missing comma or quotation mark, it could be a misspelled word, or it could be related to the order of clauses. -- If the error token is <END-OF-STATEMENT>, correct the SQL statement because it does not end with a valid clause.
代码：
CREATE PROCEDURE EODSHADOW.PopulateProdChanges()
LANGUAGE SQL
BEGIN
Declare sqlcode integer default 0;
DECLARE SQLSTATE CHAR(5) DEFAULT '00000';
DECLARE v_itemnumber CHAR(12) DEFAULT '';
DECLARE v_skucode CHAR(14) DEFAULT '';
DECLARE GLOBAL TEMPORARY TABLE session.tmp_POTable(
Order int ,
EXPECT_ON decimal(7, 0),
QUANTITY decimal(7, 0)
)
NOT LOGGED WITH REPLACE;
DECLARE GLOBAL TEMPORARY TABLE session.tmp_UPCTable(
Order int,
UPC char(14)
)
NOT LOGGED WITH REPLACE;
--1.Change the processingStatus of all records in ProdChangesXref table from ‘ERPPND’ to ‘ERPQUE’.
UPDATE ProdChangesXRef
SET processingStatus = 'ERPQUE',modifyDt=current timestamp
WHERE processingStatus = 'ERPPND';
--insert deleted prod, sku into ProdChanges
INSERT INTO ProdChanges
(ITMNUMBER
,SKUCode
,Deleted
,processingStatus
,createDt
,modifyDt
,operationType)
SELECT
ITMNUMBER,
SKUCODE,
DELETED,
'ERPPND',
CURRENT TIMESTAMP,
CURRENT TIMESTAMP,
'UPDALL'
FROM ProdChangesXRef
Where DELETED = '1';
--delete records that are inserted into ProdChanges
DELETE FROM ProdChangesXRef x
Where exists (select ITMNUMBER,SKUCODE from ProdChanges
where ProdChanges.ITMNUMBER=x.ITMNUMBER AND ProdChanges.SKUCODE=x.SKUCODE
AND x.processingStatus='ERPQUE' AND x.DELETED = '1');
--insert new/updated prod into ProdChanges
INSERT INTO ProdChanges
(ITMNUMBER
,ProdName
,SKUCode
,ShortSKU
,SKUName
,SKURetailPrice
,SKUCost
,SKUDefPrice
,SKUCreateDate
,SKUSellingWeight
,ItmClass
,ItmStatus
,SellOut
,NonInventory
,LeadDays
,Deleted
,processingStatus
,createDt
,modifyDt
,operationType
,lastSoldDate
)
SELECT
p.ARBECD,
p.AREOTX,
s.BJBESK,
s.BJA8QT,
s.BJBCTX,
s.BJAPPR,
s.BJWDPR,
s.BJSKRP,
CASE s.BJA1NB when 0 then p.ARAVNB else s.BJA1NB end case,
p.ARBLCD,
CASE s.BJMPCD when '' then p.ARBOCD else s.BJMPCD end case,
s.BJCECD,
p.ARBHST,
MIN(pov.DHKYQT),
'0',
'ERPPND',
CURRENT TIMESTAMP,
CURRENT TIMESTAMP,
'UPDALL',
s.BJBBDT
FROM ProdChangesXRef pcx
INNER JOIN CWMUDTA.INITEM p
ON pcx.ITMNUMBER=p.ITMNUMBER
LEFT JOIN CWMUDTA.INSKU s
ON s.BJBECD=pcx.ITMNUMBER
LEFT JOIN CWMUDTA.POVNIT pov
ON pov.ITMNUMBER=pcx.ITMNUMBER AND pov.SKUCODE=s.SKUCODE
WHERE pcx.ProcessingStatus='ERPQUE' AND pcx.SKUCODE='' And pcx.DELETED='0'
GROUP BY pcx.ITMNUMBER, pcx.SKUCODE;
--insert new/updated skus into ProdChanges
INSERT INTO ProdChanges
(ITMNUMBER
,ProdName
,SKUCode
,ShortSKU
,SKUName
,SKURetailPrice
,SKUCost
,SKUDefPrice
,SKUCreateDate
,SKUSellingWeight
,ItmClass
,ItmStatus
,SellOut
,NonInventory
,LeadDays
,Deleted
,processingStatus
,createDt
,modifyDt
,operationType
,lastSoldDate
)
SELECT
p.ARBECD,
p.AREOTX,
s.BJBESK,
s.BJA8QT,
s.BJBCTX,
s.BJAPPR,
s.BJWDPR,
s.BJSKRP,
CASE s.BJA1NB when 0 then p.ARAVNB else s.BJA1NB end case,
p.ARBLCD,
CASE s.BJMPCD when '' then p.ARBOCD else s.BJMPCD end case,
s.BJCECD,
p.ARBHST,
MIN(pov.DHKYQT),
'0',
'ERPPND',
CURRENT TIMESTAMP,
CURRENT TIMESTAMP,
'UPDALL',
s.BJBBDT
FROM ProdChangesXRef pcx
INNER JOIN CWMUDTA.INITEM p
ON pcx.ITMNUMBER=p.ITMNUMBER
INNER JOIN CWMUDTA.INSKU s
ON s.BJBESK=pcx.SKUCODE and s.BJBECD=pcx.ITMNUMBER
LEFT JOIN CWMUDTA.POVNIT pov
ON pov.ITMNUMBER=pcx.ITMNUMBER AND pov.SKUCODE=pcx.SKUCODE
WHERE pcx.ProcessingStatus='ERPQUE' AND pcx.SKUCODE<>'' And pcx.DELETED='0'
GROUP BY pcx.ITMNUMBER, pcx.SKUCODE;
-- update inventory, upc, POs
DECLARE pc_cursor CURSOR FOR
select ITMNUMBER, SKUCODE from ProdChanges
where SKUCODE<>'' and DELETED='0'and processingStatus='ERPPND'
for update of SKUInventory,SKU_UPC1,SKU_UPC2,SKU_UPC3,SKU_UPC4,SKU_UPC5
,EXPECT_ON_001,EXPECT_ON_002,EXPECT_ON_003,EXPECT_ON_004,EXPECT_ON_005
,QUANTITY_001,QUANTITY_002,QUANTITY_003,QUANTITY_004,QUANTITY_005;
OPEN pc_cursor;
FETCH FROM pc_cursor INTO v_itemnumber, v_skucode;
WHILE(SQLSTATE = '00000') DO
-- update SKUInventory
UPDATE ProdChanges
SET SKUInventory=
(select sum(A7OHDQ – A7RESQ – A7PROQ – A7SHRQ) from CWMUDTA.INIWRE inner join CWMUDTA.INWRHS on INIWRE.A7WCDE=INWRHS.BOWCDE
where INWRHS.BOWALO='1' and INWRHS.BOWRTO='0' and INIWRE.A7BECD=v_itemnumber and INIWRE.A7BESK=v_skucode)
WHERE CURRENT OF pc_cursor;
-- update UPC
DELETE from session.tmp_UPCTable;
insert into session.tmp_UPCTable values
(select row_number() 0ver(), S0MONR, S0BECD, S0BESK from CWMUDTA.ITMUPC
where S0BECD=v_itemnumber and S0BESK=v_skucode and S0COMP=1 FETCH FIRST 5 ROWS ONLY);
update ProdChanges
set SKU_UPC1 = (select UPC from session.tmp_UPCTable where Order=1),
SKU_UPC2 = (select UPC from session.tmp_UPCTable where Order=2),
SKU_UPC3 = (select UPC from session.tmp_UPCTable where Order=3),
SKU_UPC4 = (select UPC from session.tmp_UPCTable where Order=4),
SKU_UPC5 = (select UPC from session.tmp_UPCTable where Order=5)
where CURRENT OF pc_cursor;
-- update POs
DELETE from session.tmp_POTable;
insert into session.tmp_POTable values
(select row_number() 0ver(), p.ENCWDT, SUM(p.enkiqt - p.enkjqt)
FROM CWMUDTA.PODETL p
WHERE p.ENBECD=v_itemnumber AND p.ENBESK=v_skucode
AND p.encomp = 1 AND p.enhvst = 'O' AND p.enbecd = 'CW259A08' AND p.enbesk = 'CHAR LRG'
GROUP BY p.encwdt
ORDER BY p.ENCWDT ASC
FETCH FIRST 5 ROWS ONLY);
update ProdChanges
set EXPECT_ON_001 = (select EXPECT_ON from session.tmp_POTable where Order=1),
QUANTITY_001 = (select QUANTITY from session.tmp_POTable where Order=1),
EXPECT_ON_002 = (select EXPECT_ON from session.tmp_POTable where Order=2),
QUANTITY_002 = (select QUANTITY from session.tmp_POTable where Order=2),
EXPECT_ON_003 = (select EXPECT_ON from session.tmp_POTable where Order=3),
QUANTITY_003 = (select QUANTITY from session.tmp_POTable where Order=3),
EXPECT_ON_004 = (select EXPECT_ON from session.tmp_POTable where Order=4),
QUANTITY_004 = (select QUANTITY from session.tmp_POTable where Order=4),
EXPECT_ON_005 = (select EXPECT_ON from session.tmp_POTable where Order=5),
QUANTITY_005 = (select QUANTITY from session.tmp_POTable where Order=5)
where CURRENT OF pc_cursor;
FETCH FROM pc_cursor INTO v_itemnumber, v_skucode;
END WHILE;
CLOSE pc_cursor;
DROP TABLE session.tmp_UPCTable;
DROP TABLE session.tmp_POTable;
DELETE FROM ProdChangesXRef x
where exists (select 1 from ProdChanges
where ProdChanges.ITMNUMBER=x.ITMNUMBER)
WHERE x.processingStatus='ERPQUE';
END
[解决办法]
DECLARE pc_cursor CURSOR FOR
select ITMNUMBER, SKUCODE from ProdChanges
where SKUCODE<>'' and DELETED='0'and processingStatus='ERPPND'
for update of SKUInventory,SKU_UPC1,SKU_UPC2,SKU_UPC3,SKU_UPC4,SKU_UPC5
,EXPECT_ON_001,EXPECT_ON_002,EXPECT_ON_003,EXPECT_ON_004,EXPECT_ON_005
,QUANTITY_001,QUANTITY_002,QUANTITY_003,QUANTITY_004,QUANTITY_005;
这句有问题，检查一下
DELETE FROM ProdChangesXRef x
where exists (select 1 from ProdChanges
where ProdChanges.ITMNUMBER=x.ITMNUMBER)
WHERE x.processingStatus='ERPQUE';->
DELETE FROM ProdChangesXRef x
where exists (select 1 from ProdChanges
where ProdChanges.ITMNUMBER=x.ITMNUMBER)
and x.processingStatus='ERPQUE';
[解决办法]
我喜欢这么写：
/****************************************************************
* Name: PR_income_check
* Description: 涉外收入申报单校验存储过程。
* Creator: icss 赵坚密
* Creation date: 20071023
* Parameters:
* Input Output
* ---------------------------------------------
* Return:
* Version History
* Date:Author:Description:
* -------------------------
***************************************************************/
-- db2 connect to inte user db2admin using db2admin
--编译过程 db2 -td@ -vf PR_income_check.db2
--运行过程
--db2 call PR_income_check()
--删除过程
drop procedure PR_income_check()@
--创建PR_income_check过程
create procedure PR_income_check()
language sql
begin
--定义变量
declare V_RPTNO_DATE VARCHAR(10);
declare V_RPTNO_YEAR CHARACTER(2);
declare V_DATE DATE;
declare VAR_CUSTNAME VARCHAR(128);
--循环计数器
declare loopcount INT;
declare loopcontrol INT;
--校验日志相关变量
declare V_TBL_NAME CHARACTER(50);
declare V_PK_VALUE VARCHAR(255);
declare V_ERR_TYPE CHARACTER(4);
declare V_DESCRIPTION VARCHAR(255);
declare STR_TBL_CUSTOMERINFO CHARACTER(50) default 'TBL_CUSTOMERINFO';
declare STR_TBL_BRANCHINFO CHARACTER(50) default 'TBL_BRANCHINFO';
declare STR_TBL_BANKINFO CHARACTER(50) default 'TBL_BANKINFO';
declare STR_TBL_PAYMETHODCODE CHARACTER(50) default 'TBL_PAYMETHODCODE';
declare STR_TBL_CCYCODE CHARACTER(50) default 'TBL_CCYCODE';
declare STR_TBL_COUNTRY CHARACTER(50) default 'TBL_COUNTRY';
declare STR_TBL_TXCODE CHARACTER(50) default 'TBL_TXCODE';
declare V_PK VARCHAR(255);
--定义数据select错误的相关异常处理的参数
declare tblname char(50) default 'TBL_INCOME_TMP';
declare errLog VARCHAR(400);
declare sqlcode integer ;
declare sqlstate char(5) ;
declare rowcount int;
DECLARE not_found CONDITION FOR SQLSTATE '02000';
DECLARE CONTINUE HANDLER FOR not_found
set rowcount = 0;
DECLARE CONTINUE HANDLER FOR SQLSTATE '22007'
set rowcount = 6;
-- DECLARE CONTINUE HANDLER FOR SQLSTATE '23505'
-- set rowcount = 2;
declare exit handler for sqlexception
call logNote(tblname, errLog, sqlcode, sqlstate);
-------
call logNote(tblname, 'PR_income_check开始',0, '0');
-------
set errLog = '游标取数,TBL_INCOME_TMP';
set V_TBL_NAME = 'TBL_INCOME_TMP';
set loopcount = 0;
set loopcontrol = 1;
for checkLoop as check_cursor cursor with hold for
--游标取数TBL_INCOME_TMP，放入变量
select
RPTNOV_RPTNO,
DS_IDV_DS_ID,
--YEARV_YEAR,
--PAYDATEV_PAYDATE,
BRANCHCODESELFV_BRANCHCODESELF,
BRANCHCODEV_BRANCHCODE,
BANKCODESELFV_BANKCODESELF,
BANKCODEV_BANKCODE,
CUSTTYPEV_CUSTTYPE,
--IDCODEV_IDCODE,
CUSTCODEV_CUSTCODE,
CUSTNAMEV_CUSTNAME,
--OPPNAMEV_OPPNAME,
TXCCYV_TXCCY,
TXCCYSELFV_TXCCYSELF,
TXAMTV_TXAMT,
TXAMTUSDV_TXAMTUSD,
EXRATEV_EXRATE,
LCYAMTV_LCYAMT,
LCYAMTUSDV_LCYAMTUSD,
--LCYACCV_LCYACC,
FCYAMTV_FCYAMT,
FCYAMTUSDV_FCYAMTUSD,
--FCYACCV_FCYACC,
OTHAMTV_OTHAMT,
.............................................
CHECK_STATEV_CHECK_STATE
from TBL_INCOME_TMP
for update
do
set rowcount = 1;
set V_PK_VALUE = V_RPTNO;
update TBL_INCOME_TMP set CHECK_STATE = '1' where current of check_cursor;
--数据源校验
--Ds_Id为1、2、3或4
-- if V_DS_ID = 1 or V_DS_ID = 2 or V_DS_ID = 3 or V_DS_ID = 4 then
-- else
-- end if;
--对金额字段进行非空处理
--收款金额TxAmt
--结汇金额LcyAmt
--现汇金额FcyAmt
--其他金额OthAmt
update TBL_INCOME_TMP set TxAmt = coalesce(V_TXAMT, 0) where current of check_cursor;
update TBL_INCOME_TMP set LcyAmt = coalesce(V_LCYAMT, 0) where current of check_cursor;
update TBL_INCOME_TMP set FcyAmt = coalesce(V_FCYAMT, 0) where current of check_cursor;
update TBL_INCOME_TMP set OthAmt = coalesce(V_OTHAMT, 0) where current of check_cursor;
--对限额标记字段进行赋值
if V_TXAMT <= 2000 then
update TBL_INCOME_TMP set UNDERLIMIT = 'Y' where current of check_cursor;
else
update TBL_INCOME_TMP set UNDERLIMIT = 'N' where current of check_cursor;
end if;
--申报号里的时间相关校验
set V_RPTNO_YEAR = substr(V_RPTNO, 13, 2);
set rowcount = 1;
if V_RPTNO_YEAR > '60' then
set V_RPTNO_DATE = '19'
[解决办法]
V_RPTNO_YEAR
[解决办法]
'-'
[解决办法]
substr(V_RPTNO, 15 ,2)
------解决方案--------------------
'-'
[解决办法]
substr(V_RPTNO, 17 ,2);
else
set V_RPTNO_DATE = '20'
[解决办法]
V_RPTNO_YEAR
[解决办法]
'-'
[解决办法]
substr(V_RPTNO, 15 ,2)
[解决办法]
'-'
[解决办法]
substr(V_RPTNO, 17 ,2);
end if;
set V_DATE = cast(V_RPTNO_DATE as date);
if rowcount = 6 then
update TBL_INCOME_TMP set CHECK_STATE = '0' where current of check_cursor;
set V_ERR_TYPE = '0101';
set V_DESCRIPTION = '涉外收入申报单：申报单号中日期非法:'
[解决办法]
V_RPTNO_DATE;
call check_log(V_TBL_NAME, V_PK_VALUE, V_ERR_TYPE, V_DESCRIPTION);
end if;
。。。。。。。。。。。。。。。
--交易金额非空校验
set V_TXAMT1 = coalesce(V_TXAMT1, 0);
if V_TXAMT1 = 0 then
update TBL_INCOME_TMP set CHECK_STATE = '0' where current of check_cursor;
set V_ERR_TYPE = '0113';
set V_DESCRIPTION = '涉外收入申报单：交易金额1为空或为零';
call check_log(V_TBL_NAME, V_PK_VALUE, V_ERR_TYPE, V_DESCRIPTION);
end if;
--交易编码2校验,交易金额2校验
if not (V_TXCODE2 is null or ltrim(V_TXCODE2)= '') then
if V_TXCODE2SELF is null or ltrim(V_TXCODE2SELF)= '' then
update TBL_INCOME_TMP set CHECK_STATE = '0' where current of check_cursor;
set V_ERR_TYPE = '0114';
set V_DESCRIPTION = '涉外收入申报单：交易编码2非空，且非法';
call check_log(V_TBL_NAME, V_PK_VALUE, V_ERR_TYPE, V_DESCRIPTION);
set V_PK = V_TXCODE2;
set V_DESCRIPTION = '国际收支交易编码表：交易编码不存在';
call check_log(STR_TBL_TXCODE, V_PK, V_ERR_TYPE, V_DESCRIPTION);
end if;
set V_TXAMT2 = coalesce(V_TXAMT2, 0);
if V_TXAMT2 = 0 then
update TBL_INCOME_TMP set CHECK_STATE = '0' where current of check_cursor;
set V_ERR_TYPE = '0115';
set V_DESCRIPTION = '涉外收入申报单：交易编码2非空，但交易金额2为空或零';
call check_log(V_TBL_NAME, V_PK_VALUE, V_ERR_TYPE, V_DESCRIPTION);
end if;
end if;
--收付款性质的校验
--payType为A、R、或O
if V_PAYTYPE <> 'A' and V_PAYTYPE <> 'R' and V_PAYTYPE <> 'O' then
update TBL_INCOME_TMP set CHECK_STATE = '0' where current of check_cursor;
set V_ERR_TYPE = '0116';
set V_DESCRIPTION = '涉外收入申报单：收付款性质非法（不是“A”，“R”，“O”）';
call check_log(V_TBL_NAME, V_PK_VALUE, V_ERR_TYPE, V_DESCRIPTION);
end if;
if V_TXAMT1USD + V_TXAMT2USD <> V_TXAMTUSD then
update TBL_INCOME_TMP set CHECK_STATE = '0' where current of check_cursor;
set V_ERR_TYPE = '0119';
set V_DESCRIPTION = '涉外收入申报单：不符合折美元字段的平衡公式：相应金额1折美元+相应金额2折美元=收付款金额折美元';
call check_log(V_TBL_NAME, V_PK_VALUE, V_ERR_TYPE, V_DESCRIPTION);
end if;
--\交易编码重复校验
IF (V_TXCODE1 = V_TXCODE2) and (NOT (V_TXCODE2 is null or trim(V_TXCODE2) = '')) THEN
UPDATE TBL_INCOME_TMP SET CHECK_STATE = '0' WHERE CURRENT OF CHECK_CURSOR;
SET V_ERR_TYPE = '0120';
SET V_DESCRIPTION = '涉外收入申报单：交易编码1与交易编码2相同';
CALL CHECK_LOG(V_TBL_NAME, V_PK_VALUE, V_ERR_TYPE, V_DESCRIPTION);
END IF;
end if;
if V_TxAmt <> 0 and V_TxAmtUsd = 0 then
update TBL_INCOME_TMP set CHECK_STATE = '0' where current of check_cursor;
set V_ERR_TYPE = '0117';
set V_DESCRIPTION = '涉外收入申报单：交易金额不为0，但折美元为0';
call check_log(V_TBL_NAME, V_PK_VALUE, V_ERR_TYPE, V_DESCRIPTION);
end if;
if V_LCYAMTUSD + V_FCYAMTUSD + V_OTHAMTUSD > V_TXAMTUSD then
update TBL_INCOME_TMP set CHECK_STATE = '0' where current of check_cursor;
set V_ERR_TYPE = '0118';
set V_DESCRIPTION = '涉外收入申报单：不符合金额字段的平衡公式：结汇金额+现汇金额+其它金额<=收付款金额';
call check_log(V_TBL_NAME, V_PK_VALUE, V_ERR_TYPE, V_DESCRIPTION);
end if;
--——————————————————————————————————
--非删除记录才校验
end if;
--——————————————————————————————————
set loopcount = loopcount + 1;
if loopcount >= 8000 * loopcontrol then
set loopcontrol = loopcontrol + 1;
call logNote(tblname, 'PR_income_check处理了'
[解决办法]
char(loopcount)
[解决办法]
'条数据',0, '0');
commit;
end if;
end for;
-------
call logNote(tblname, 'PR_income_check结束',0, '0');
commit;
end@
