嵌套SQL过程中出错 [英] Error in nested SQL Procedure
问题描述
我在Windows上的DB2 V8.2.1上遇到嵌套SQL过程的问题。
当我尝试构建时，这个看起来很简单的proc会给我这个错误消息开发中心的
：
RHINO.CALLED01：18：[IBM] [CLI驱动程序] [DB2 / NT] SQL0104N意外令牌
"< cursor declaration>"发现在之后。预期的令牌可能包括：
"< SQL statement>" ;. LINE NUMBER = 18 SQLSTATE = 42601
这是proc的源代码;第18行是开始的行
声明游标2光标。
---------------- -------
CREATE PROCEDURE RHINO.CALLED01（IN in_sex char（1），IN in_edlevel smallint）
语言SQL
动态结果集2
-------------------------------------- ----------------------------------
- SQL存储过程
--------------------------------------------- ---------------------------
BEGIN
声明cursor1返回调用者的光标
选择empno，lastname，salary，bonus，comm
来自emp
where sex = in_sex
和edlevel = in_edlevel
按empno订购;
打开cursor1;
声明cursor2游标返回给来电者
选择计数（*）
来自emp
其中sex = in_sex
和edlevel = in_edlevel;
打开游标2;
结束
- ---------------------
这个proc在我添加第二个游标之前编译并运行正常。我不能确定这是多么的错误。这个原因的两个最佳候选者是：
1.在SQL proc中不允许有两个结果集。这似乎不太可能因为动态结果设置而不可能。从我手册中可以看出，子句允许任何正的
整数。
2.我的结构以某种方式关闭：也许我需要进行干预语句两个游标之间的某种形式的
。如果是这样，我错过了什么？
谁能告诉我这个程序有什么问题？我试过谷歌搜索
并没有找到任何有用的东西，并且没有看到任何有用的
手册。
-
犀牛
---
rhino1 AT sympatico DOT ca
"有两种构建软件设计的方法。一种方法是使它变得如此简单，以至于显然没有任何缺陷。而另一种方式是使b / b $ b变得如此复杂以至于没有明显的缺陷。 - CAR
Hoare
Rhino写道：我遇到了问题Windows上的DB2 V8.2.1上的嵌套SQL过程。
当我尝试在开发中心构建它时，这个简单的过程会给我这个错误消息：
RHINO.CALLED01：18：[IBM] [CLI驱动程序] [DB2 / NT] SQL0104N意外的令牌
"< cursor declaration>"发现在之后。预期的令牌可能包括：
"< SQL statement>"。 LINE NUMBER = 18 SQLSTATE = 42601
< snip>谁能告诉我这个程序有什么问题？我试过谷歌搜索
并没有找到任何有用的东西，也没有看到任何有用的手册。
嗯。仔细查看复合语句的定义。
它声明DECLARE游标必须出现在开头。 proc
正文以你的例子中的OPEN开头。
你必须选择：将DECLARE移到OPEN的前面，或推送
这样的新化合物：
DECLARE
OPEN
BEGIN
DECLARE
开放......
...
关闭
....
结束
关闭
....
-
Serge Rielau
DB2 SQL编译器开发
IBM多伦多实验室
Serge Rielau写道：Rhino写道：
我在Windows上的DB2 V8.2.1上遇到嵌套SQL过程的问题。
这个看起来很简单的过程在我尝试时给出了这个错误消息在开发中心构建
RHINO.CALLED01：18：[IBM] [CLI驱动程序] [DB2 / NT] SQL0104N意外的令牌
"< cursor declaration>"发现在之后。预期的令牌可能包括：
"< SQL statement>"。 LINE NUMBER = 18 SQLSTATE = 42601
< snip>
有谁能告诉我这个程序有什么问题？我已经尝试了谷歌搜索
并没有找到任何有用的东西，在
手册中没有看到任何帮助
。
嗯。仔细研究复合语句的定义。
它声明DECLARE游标必须出现在开头。 proc
主体以你的例子中的OPEN开始。
你必须选择：将DECLARE移到OPEN的前面，或推送一个像这样的新化合物：
DECLARE
OPEN
BEGIN
DECLARE
OPEN ...
...
关闭
...
END 关闭
...
PS：这些规则是SQL / PSM标准规定的，试图保持语言结构。我想知道这是否太苛刻了......也许
他们应该放松以支持消费品。
干杯
Serge
-
Serge Rielau
DB2 SQL编译器开发
IBM多伦多实验室
" Serge Rielau" < SR ***** @ ca.ibm.com>在消息中写道
news：3l ************* @ individual.net ...Rhino写道：我在
Windows上的DB2 V8.2.1上遇到了嵌套SQL过程的问题。
当我尝试
构建它时，这个简单的过程会给我这个错误消息在开发中心：
RHINO.CALLED01：18：[IBM] [CLI驱动程序] [DB2 / NT] SQL0104N意外的
令牌"<游标声明>"发现在之后。预期的代币可能
包括："< SQL statement>"。 LINE NUMBER = 18 SQLSTATE = 42601
< snip>有谁能告诉我这个程序有什么问题？我已经尝试了
谷歌搜索并没有找到任何有用的东西，并且没有看到任何有助于
手册的内容。
嗯。仔细研究复合语句的定义。
它声明DECLARE游标必须出现在开头。 proc
主体以你的例子中的OPEN开始。
你必须选择：将DECLARE移到OPEN的前面，或推送一个像这样的新化合物：
DECLARE
OPEN
BEGIN
DECLARE
OPEN ...
...
关闭
...
END 关闭
...
- Serge Rielau
DB2 SQL编译器开发
IBM多伦多实验室
你是绝对正确的Serge;一旦我确定两个DECLARE
CURSOR语句都在两个OPEN语句之前，程序就编译了
并且执行得很好。因此，我的代码结构不正确。
Rhino
I am having a problem with a nested SQL Procedure on DB2 V8.2.1 on Windows.
This simple-looking proc is giving me this error message when I try to build
it in the Development Center:
RHINO.CALLED01: 18: [IBM][CLI Driver][DB2/NT] SQL0104N An unexpected token
"<cursor declaration>" was found following "". Expected tokens may include:
"<SQL statement>". LINE NUMBER=18 SQLSTATE=42601
Here is the source code for the proc; line 18 is the line that begins
"declare cursor2 cursor".
-----------------------
CREATE PROCEDURE RHINO.CALLED01 (IN in_sex char(1), IN in_edlevel smallint)
language SQL
dynamic result sets 2
------------------------------------------------------------------------
-- SQL Stored Procedure
------------------------------------------------------------------------
BEGIN
declare cursor1 cursor with return to caller for
select empno, lastname, salary, bonus, comm
from emp
where sex = in_sex
and edlevel = in_edlevel
order by empno;
open cursor1;
declare cursor2 cursor with return to caller for
select count(*)
from emp
where sex = in_sex
and edlevel = in_edlevel;
open cursor2;
END
-----------------------
This proc compiled and ran fine before I added the second cursor. I can''t
figure out what''s wrong. The two top candidates for the cause are:
1. It is not permissible to have two result sets in an SQL proc. That seems
unlikely because the "dynamic result sets" clause permits any positive
integer, as far as I can tell from the manual.
2. My structure is off somehow: maybe I need to have intervening statements
of some kind between the two cursors. If so, what am I missing?
Can anyone tell me what''s wrong with this procedure? I have tried googling
and didn''t find anything useful and haven''t seen anything that helped in the
manuals.
--
Rhino
---
rhino1 AT sympatico DOT ca
"There are two ways of constructing a software design. One way is to make it
so simple that there are obviously no deficiencies. And the other way is to
make it so complicated that there are no obvious deficiencies." - C.A.R.
Hoare
Rhino wrote:I am having a problem with a nested SQL Procedure on DB2 V8.2.1 on Windows.
This simple-looking proc is giving me this error message when I try to build
it in the Development Center:
RHINO.CALLED01: 18: [IBM][CLI Driver][DB2/NT] SQL0104N An unexpected token
"<cursor declaration>" was found following "". Expected tokens may include:
"<SQL statement>". LINE NUMBER=18 SQLSTATE=42601
<snip> Can anyone tell me what''s wrong with this procedure? I have tried googling
and didn''t find anything useful and haven''t seen anything that helped in the
manuals.
Hmmm. look closely at the definition of compound statement.
It states that DECLARE cursor has to appear at teh beginning. The proc
body starts with the OPEN in your example.
You have to choices: Move the DECLARE up infront of the OPEN, or push a
new compound like this:
DECLARE
OPEN
BEGIN
DECLARE
OPEN...
...
CLOSE
....
END
CLOSE
....
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Serge Rielau wrote:Rhino wrote:I am having a problem with a nested SQL Procedure on DB2 V8.2.1 on
Windows.
This simple-looking proc is giving me this error message when I try to
build
it in the Development Center:
RHINO.CALLED01: 18: [IBM][CLI Driver][DB2/NT] SQL0104N An unexpected
token
"<cursor declaration>" was found following "". Expected tokens may
include:
"<SQL statement>". LINE NUMBER=18 SQLSTATE=42601
<snip>Can anyone tell me what''s wrong with this procedure? I have tried
googling
and didn''t find anything useful and haven''t seen anything that helped
in the
manuals.
Hmmm. look closely at the definition of compound statement.
It states that DECLARE cursor has to appear at teh beginning. The proc
body starts with the OPEN in your example.
You have to choices: Move the DECLARE up infront of the OPEN, or push a
new compound like this:
DECLARE
OPEN
BEGIN
DECLARE
OPEN...
...
CLOSE
...
END
CLOSE
...
PS: These rules are mandated by the SQL/PSM standard in an attempt to
keep teh language structure. I wonder whether that was too harsh.. maybe
they ought to be relaxed in favor of consumability.
Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
"Serge Rielau" <sr*****@ca.ibm.com> wrote in message
news:3l*************@individual.net...Rhino wrote:I am having a problem with a nested SQL Procedure on DB2 V8.2.1 on Windows.
This simple-looking proc is giving me this error message when I try to build it in the Development Center:
RHINO.CALLED01: 18: [IBM][CLI Driver][DB2/NT] SQL0104N An unexpected token "<cursor declaration>" was found following "". Expected tokens may include: "<SQL statement>". LINE NUMBER=18 SQLSTATE=42601
<snip>Can anyone tell me what''s wrong with this procedure? I have tried googling and didn''t find anything useful and haven''t seen anything that helped in the manuals.
Hmmm. look closely at the definition of compound statement.
It states that DECLARE cursor has to appear at teh beginning. The proc
body starts with the OPEN in your example.
You have to choices: Move the DECLARE up infront of the OPEN, or push a
new compound like this:
DECLARE
OPEN
BEGIN
DECLARE
OPEN...
...
CLOSE
...
END
CLOSE
...
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
You''re absolutely right Serge; as soon as I made sure that both DECLARE
CURSOR statements preceded the two OPEN statements, the procedure compiled
and executed fine. Therefore, the structure of my code was incorrect.
Rhino
这篇关于嵌套SQL过程中出错的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
