在DB2 Stored Proc中声明一个Condition处理程序 [英] Declaring a Condition handler in DB2 Stored Proc
问题描述
尝试为SQLEXCEPTION声明一个简单的处理程序。
简单存储过程（UDB 8.2）
----
创建过程TFBUDB .SMACF_SM_DI（IN POL_NBR CHAR（10）
，OUT ErrNo INTEGER
，OUT ErrMsg CHAR（80））
----- -------------------------------------------------- -----------------
- SQL存储过程
------------ -------------------------------------------------- ----------
P1：BEGIN
DECLARE SQLCODE INTEGER DEFAULT 0;
DECLARE NOTFOUND INTEGER DEFAULT 100;
DECLARE CONTINUE HANDLER for SQLEXCEPTION
SET ErrMsg =''休斯顿，我们遇到了问题'';
DECLARE D1 CURSOR WITH返回给调用者
选择DI_NME_TXT
，DI_OCCR_NBR
来自TFBUDB.SM_DI_NME_INFO
WHERE POL_NBR_TXT = POL_NBR;
OPEN D1;
END P1
---------------------- ----------------------------------
Attemp为了在DB2开发中心构建，我得到以下错误 -
它肯定不喜欢它。书籍处理程序的语法：
TFBUDB.SMACF_SM_DI - 构建开始。
DROP SPECIFIC PROCEDURE TFBUDB.SQL070413170923800
TFBUDB .SMACF_SM_DI - 删除存储过程完成。
创建存储过程返回-104。
TFBUDB.SMACF_SM_DI：20：[IBM] [CLI驱动程序] [DB2 / NT] SQL0104N
意外标记"< cursor declaration>"在"之后找到。
预期的令牌可能包括：< SQL statement>。 LINE NUMBER = 20。
SQLSTATE = 42601
TFBUDB.SMACF_SM_DI - 构建失败。
TFBUDB.SMACF_SM_DI - 回滚完成成功。
Trying to declare a simple handler for SQLEXCEPTION.
Simple stored proc (UDB 8.2)
----
CREATE PROCEDURE TFBUDB.SMACF_SM_DI (IN POL_NBR CHAR(10)
, OUT ErrNo INTEGER
, OUT ErrMsg CHAR(80) )
------------------------------------------------------------------------
-- SQL Stored Procedure
------------------------------------------------------------------------
P1: BEGIN
DECLARE SQLCODE INTEGER DEFAULT 0;
DECLARE NOTFOUND INTEGER DEFAULT 100;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
SET ErrMsg = ''Houston, we have a problem'';
DECLARE D1 CURSOR WITH RETURN TO CALLER FOR
SELECT DI_NME_TXT
,DI_OCCR_NBR
FROM TFBUDB.SM_DI_NME_INFO
WHERE POL_NBR_TXT = POL_NBR;
OPEN D1;
END P1
--------------------------------------------------------
Attempting to build in DB2 Development center I get the error below -
it certainly does not like it. Syntax for handler right out of book:
TFBUDB.SMACF_SM_DI - Build started.
DROP SPECIFIC PROCEDURE TFBUDB.SQL070413170923800
TFBUDB.SMACF_SM_DI - Drop stored procedure completed.
Create stored procedure returns -104.
TFBUDB.SMACF_SM_DI: 20: [IBM][CLI Driver][DB2/NT] SQL0104N An
unexpected token "<cursor declaration>" was found following "".
Expected tokens may include: "<SQL statement>". LINE NUMBER=20.
SQLSTATE=42601
TFBUDB.SMACF_SM_DI - Build failed.
TFBUDB.SMACF_SM_DI - Roll back completed successfully.
推荐答案
4月18日下午2:39，PJack ... @ txfb-ins.com写道：
On Apr 18, 2:39 pm, PJack...@txfb-ins.com wrote:
尝试为SQLEXCEPTION声明一个简单的处理程序。
简单存储过程（UDB 8.2）
----
CREATE PROCEDURE TFBUDB.SMACF_SM_DI（IN POL_NBR CHAR（10）
，OUT ErrNo INTEGER
，OUT ErrMsg CHAR（80））
-------------------------------------------- ----------------------------
- SQL存储过程
- -------------------------------------------------- ---------------------
P1：BEGIN
DECLARE SQLCODE INTEGER DEFAULT 0;
DECLARE NOTFOUND INTEGER DEFAULT 100;
DECLARE CONTINUE HANDLER for SQL EXCEPTION
SET ErrMsg =''休斯顿，我们遇到了问题'';
DECLARE D1 CURSOR返回给调用者
选择DI_NME_TXT
，DI_OCCR_NBR
来自TFBUDB.SM_DI_NME_INFO
WHERE POL_NBR_TXT = POL_NBR;
OPEN D1;
结束P1
--------------------------------- -----------------------
尝试在DB2开发中心构建我收到以下错误 -
肯定不喜欢它。书籍处理程序的语法：
TFBUDB.SMACF_SM_DI - 构建开始。
DROP SPECIFIC PROCEDURE TFBUDB.SQL070413170923800
TFBUDB .SMACF_SM_DI - 删除存储过程完成。
创建存储过程返回-104。
TFBUDB.SMACF_SM_DI：20：[IBM] [CLI驱动程序] [DB2 / NT] SQL0104N
意外标记"< cursor declaration>"在"之后找到。
预期的令牌可能包括：< SQL statement>。 LINE NUMBER = 20。
SQLSTATE = 42601
TFBUDB.SMACF_SM_DI - 构建失败。
TFBUDB.SMACF_SM_DI - 回滚完成成功。
Trying to declare a simple handler for SQLEXCEPTION.
Simple stored proc (UDB 8.2)
----
CREATE PROCEDURE TFBUDB.SMACF_SM_DI (IN POL_NBR CHAR(10)
, OUT ErrNo INTEGER
, OUT ErrMsg CHAR(80) )
------------------------------------------------------------------------
-- SQL Stored Procedure
------------------------------------------------------------------------
P1: BEGIN
DECLARE SQLCODE INTEGER DEFAULT 0;
DECLARE NOTFOUND INTEGER DEFAULT 100;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
SET ErrMsg = ''Houston, we have a problem'';
DECLARE D1 CURSOR WITH RETURN TO CALLER FOR
SELECT DI_NME_TXT
,DI_OCCR_NBR
FROM TFBUDB.SM_DI_NME_INFO
WHERE POL_NBR_TXT = POL_NBR;
OPEN D1;
END P1
--------------------------------------------------------
Attempting to build in DB2 Development center I get the error below -
it certainly does not like it. Syntax for handler right out of book:
TFBUDB.SMACF_SM_DI - Build started.
DROP SPECIFIC PROCEDURE TFBUDB.SQL070413170923800
TFBUDB.SMACF_SM_DI - Drop stored procedure completed.
Create stored procedure returns -104.
TFBUDB.SMACF_SM_DI: 20: [IBM][CLI Driver][DB2/NT] SQL0104N An
unexpected token "<cursor declaration>" was found following "".
Expected tokens may include: "<SQL statement>". LINE NUMBER=20.
SQLSTATE=42601
TFBUDB.SMACF_SM_DI - Build failed.
TFBUDB.SMACF_SM_DI - Roll back completed successfully.
有一个订单，游标，处理程序，DGTT和变量必须是
声明的。我不是在我的桌子上有我的手册和备忘单（所以
我不是100％就此而言），但是尝试在你的光标声明之后把你的处理程序声明放在
之前（但是之前）开放）。
--Jeff
There''s an order that cursors, handlers, DGTTs, and variables must be
declared in. I''m not at my desk with my manuals and cheat sheets (so
I''m not 100% on this), but try putting your handler declaration after
your cursor declaration (but before the open).
--Jeff
4月18日下午2:39，PJack .. 。@ txfb-ins.com写道：
On Apr 18, 2:39 pm, PJack...@txfb-ins.com wrote:
尝试为SQLEXCEPTION声明一个简单的处理程序。
简单存储过程（UDB 8.2）
----
CREATE PROCEDURE TFBUDB.SMACF_SM_DI（IN POL_NBR CHAR（10）
，OUT ErrNo INTEGER
，OUT ErrMsg CHAR（80））
--------------------------------- ---------------------------------------
- SQL存储过程
---------------------------------------- ---------------- ----------------
P1：BEGIN
DECLARE SQLCODE INTEGER DEFAULT 0;
DECLARE NOTFOUND INTEGER DEFAULT 100;
DECLARE CONTINUE HANDLER for SQLEXCEPTION
SET ErrMsg =''休斯顿，我们遇到了问题'';
DECLARE D1 CURSOR返回给来电者
SELECT DI_NME_TXT
，DI_OCCR_NBR
来自TFBUDB.SM_DI_NME_INFO
POL_NBR_TXT = POL_NBR;
OPEN D1;
END P1
---------------- ----------------------------------------
正在尝试在DB2开发中心构建我得到以下错误 -
它肯定不喜欢它。书籍处理程序的语法：
TFBUDB.SMACF_SM_DI - 构建开始。
DROP SPECIFIC PROCEDURE TFBUDB.SQL070413170923800
TFBUDB .SMACF_SM_DI - 删除存储过程完成。
创建存储过程返回-104。
TFBUDB.SMACF_SM_DI：20：[IBM] [CLI驱动程序] [DB2 / NT] SQL0104N
意外标记"< cursor declaration>"在"之后找到。
预期的令牌可能包括：< SQL statement>。 LINE NUMBER = 20。
SQLSTATE = 42601
TFBUDB.SMACF_SM_DI - 构建失败。
TFBUDB.SMACF_SM_DI - 回滚完成成功。
Trying to declare a simple handler for SQLEXCEPTION.
Simple stored proc (UDB 8.2)
----
CREATE PROCEDURE TFBUDB.SMACF_SM_DI (IN POL_NBR CHAR(10)
, OUT ErrNo INTEGER
, OUT ErrMsg CHAR(80) )
------------------------------------------------------------------------
-- SQL Stored Procedure
------------------------------------------------------------------------
P1: BEGIN
DECLARE SQLCODE INTEGER DEFAULT 0;
DECLARE NOTFOUND INTEGER DEFAULT 100;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
SET ErrMsg = ''Houston, we have a problem'';
DECLARE D1 CURSOR WITH RETURN TO CALLER FOR
SELECT DI_NME_TXT
,DI_OCCR_NBR
FROM TFBUDB.SM_DI_NME_INFO
WHERE POL_NBR_TXT = POL_NBR;
OPEN D1;
END P1
--------------------------------------------------------
Attempting to build in DB2 Development center I get the error below -
it certainly does not like it. Syntax for handler right out of book:
TFBUDB.SMACF_SM_DI - Build started.
DROP SPECIFIC PROCEDURE TFBUDB.SQL070413170923800
TFBUDB.SMACF_SM_DI - Drop stored procedure completed.
Create stored procedure returns -104.
TFBUDB.SMACF_SM_DI: 20: [IBM][CLI Driver][DB2/NT] SQL0104N An
unexpected token "<cursor declaration>" was found following "".
Expected tokens may include: "<SQL statement>". LINE NUMBER=20.
SQLSTATE=42601
TFBUDB.SMACF_SM_DI - Build failed.
TFBUDB.SMACF_SM_DI - Roll back completed successfully.
好的 - 回到我的办公桌前。这是宣布订单：
1.变量
2.条件
3.游标
4.处理程序
5. DGTT
- 杰夫
OK--back at my desk. Here''s the declaration order:
1. Variables
2. Conditions
3. Cursors
4. Handlers
5. DGTTs
--Jeff
jefftyzzer写道：
jefftyzzer wrote:
4月18日下午2:39，PJack ... @ txfb-ins.com写道：
On Apr 18, 2:39 pm, PJack...@txfb-ins.com wrote:
>尝试为SQLEXCEPTION声明一个简单的处理程序。
简单的存储过程（UDB 8.2）
----
CREATE PROCEDURE TFBUDB.SMACF_SM_DI（IN POL_NBR CHAR （10）
，OUT ErrNo INTEGER
，OUT ErrMsg CHAR（80））
----------------------- -------------------------------------------------
- SQL存储过程
-------------------------------------- ----------------------------------
P1：BEGIN
DECLARE SQLCODE INTEGER DEFAULT 0 ;
DECLARE NOTFOUND INTEGER DEFAULT 100;
宣布SQLEXC的继续处理器EPTION
SET ErrMsg =''休斯顿，我们遇到了问题'';
DECLARE D1 CURSOR返回给调用者选择DI_NME_TXT
，DI_OCCR_NBR 来自TFBUDB.SM_DI_NME_INFO
在哪里POL_NBR_TXT = POL_NBR;
OPEN D1;
END P1
------------------- -------------------------------------
试图在DB2开发中心构建我得到的下面的错误 -
它肯定不喜欢它。书籍处理程序的语法：
TFBUDB.SMACF_SM_DI - 构建开始。
DROP SPECIFIC PROCEDURE TFBUDB.SQL070413170923800
TFBUDB.SMACF_SM_DI - 删除存储过程完成。
创建存储过程返回-104。
TFBUDB.SMACF_SM_DI：20：[IBM] [CLI驱动程序] [DB2 / NT] SQL0104N
意外标记"< cursor declaration>"在"之后找到。
预期的令牌可能包括：< SQL statement>。 LINE NUMBER = 20。
SQLSTATE = 42601
TFBUDB.SMACF_SM_DI - 构建失败。
TFBUDB.SMACF_SM_DI - 回滚成功完成。
>Trying to declare a simple handler for SQLEXCEPTION.
Simple stored proc (UDB 8.2)
----
CREATE PROCEDURE TFBUDB.SMACF_SM_DI (IN POL_NBR CHAR(10)
, OUT ErrNo INTEGER
, OUT ErrMsg CHAR(80) )
------------------------------------------------------------------------
-- SQL Stored Procedure
------------------------------------------------------------------------
P1: BEGIN
DECLARE SQLCODE INTEGER DEFAULT 0;
DECLARE NOTFOUND INTEGER DEFAULT 100;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
SET ErrMsg = ''Houston, we have a problem'';
DECLARE D1 CURSOR WITH RETURN TO CALLER FOR
SELECT DI_NME_TXT
,DI_OCCR_NBR
FROM TFBUDB.SM_DI_NME_INFO
WHERE POL_NBR_TXT = POL_NBR;
OPEN D1;
END P1
--------------------------------------------------------
Attempting to build in DB2 Development center I get the error below -
it certainly does not like it. Syntax for handler right out of book:
TFBUDB.SMACF_SM_DI - Build started.
DROP SPECIFIC PROCEDURE TFBUDB.SQL070413170923800
TFBUDB.SMACF_SM_DI - Drop stored procedure completed.
Create stored procedure returns -104.
TFBUDB.SMACF_SM_DI: 20: [IBM][CLI Driver][DB2/NT] SQL0104N An
unexpected token "<cursor declaration>" was found following "".
Expected tokens may include: "<SQL statement>". LINE NUMBER=20.
SQLSTATE=42601
TFBUDB.SMACF_SM_DI - Build failed.
TFBUDB.SMACF_SM_DI - Roll back completed successfully.
好的 - 回到我的办公桌前。这是宣布订单：
1.变量
2.条件
3.游标
4.处理程序
5. DGTT
OK--back at my desk. Here''s the declaration order:
1. Variables
2. Conditions
3. Cursors
4. Handlers
5. DGTTs
实际上DGTT与它无关。它们只是常规的
语句，必须在所有真实之后出现。 DECLAREs
干杯
Serge
-
Serge Rielau
DB2解决方案开发
IBM多伦多实验室
Actually DGTT have nothing to do with it. They are just regular
statements which have to come after all the "real" DECLAREs
Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
这篇关于在DB2 Stored Proc中声明一个Condition处理程序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
