问题一:p_cur_trainsch OUT type_cur_trainsch 这语句中,过程与游标是否在包已字义?问题二: sql1 := 'SELECT ELECT_ID,SESS_ID FROM TRAIN_SCH WHERE ELECT_ID ='''||p_elect_id||''''; OPEN p_cur_trainsch FOR sql1;
答复beckhambobo(beckham) 问题一:p_cur_trainsch OUT type_cur_trainsch ,这个我已经在包里已经定义,而且我改成静态执行是完全正确的。游标没问题,数据可以取出来。第二个问题是:我已经这样改了,不过报的错误是: 游标不能在动态sql open语句中使用 请问,是否还需要主意什么问题
记错,修改如下:sql1 := 'SELECT ELECT_ID,SESS_ID FROM TRAIN_SCH WHERE ELECT_ID =:m'; OPEN p_cur_trainsch FOR sql1 using p_elect_id;
我的代码如下: 先是包: REATE OR REPLACE PACKAGE nears_trainsch_pg IS TYPE type_cur_trainsch IS REF CURSOR RETURN TRAIN_SCH%ROWTYPE; PROCEDURE nears_listall_trainsch_sp ( p_elect_id IN Varchar2, p_cur_trainsch OUT type_cur_trainsch ); END nears_trainsch_pg;体如下: CREATE OR REPLACE PACKAGE BODY nears_trainsch_pg ISPROCEDURE nears_listall_trainsch_sp ( p_elect_id IN Varchar2, p_cur_trainsch OUT type_cur_trainsch ) IS sql1 varchar2(255); BEGIN sql1 := ' OPEN p_cur_trainsch FOR SELECT ELECT_ID,SESS_ID WHERE ELECT_ID ='|| p_elect_id; execute immediate sql1; END nears_listall_trainsch_sp;END nears_trainsch_pg;
我不用动态,我下面的代码是可以的,不过我有一个存储过程是需要动态执行,这个是我的测试存储过程,没问题的静态体如下: PROCEDURE nears_listall_trainsch_sp ( p_elect_id IN Varchar2, p_cur_trainsch OUT type_cur_trainsch ) IS
BEGIN OPEN p_cur_trainsch FOR SELECT ELECT_ID,SESS_ID FROM TRAIN_SCH WHERE ELECT_ID = p_elect_id;
END nears_listall_trainsch_sp;
先把游标设为弱类型: TYPE type_cur_trainsch IS REF CURSOR; ....... sql1 := 'SELECT ELECT_ID,SESS_ID FROM TRAIN_SCH WHERE ELECT_ID =:m'; OPEN p_cur_trainsch FOR sql1 using p_elect_id;记得用完游标后关闲.
sql1 := 'SELECT ELECT_ID,SESS_ID FROM TRAIN_SCH WHERE ELECT_ID ='''||p_elect_id||'''';
OPEN p_cur_trainsch FOR sql1;
问题一:p_cur_trainsch OUT type_cur_trainsch ,这个我已经在包里已经定义,而且我改成静态执行是完全正确的。游标没问题,数据可以取出来。第二个问题是:我已经这样改了,不过报的错误是:
游标不能在动态sql open语句中使用
请问,是否还需要主意什么问题
OPEN p_cur_trainsch FOR sql1 using p_elect_id;
还是要再请问一下beckhambobo(beckham),
这个sql语句里的“m”是什么意思,谢谢
其次,open的语法很简单:open 游标变量 for 查询语句.
先是包:
REATE OR REPLACE PACKAGE nears_trainsch_pg IS
TYPE type_cur_trainsch IS REF CURSOR RETURN TRAIN_SCH%ROWTYPE;
PROCEDURE nears_listall_trainsch_sp
(
p_elect_id IN Varchar2,
p_cur_trainsch OUT type_cur_trainsch
);
END nears_trainsch_pg;体如下:
CREATE OR REPLACE PACKAGE BODY nears_trainsch_pg ISPROCEDURE nears_listall_trainsch_sp
(
p_elect_id IN Varchar2,
p_cur_trainsch OUT type_cur_trainsch
)
IS
sql1 varchar2(255);
BEGIN
sql1 := '
OPEN p_cur_trainsch FOR
SELECT ELECT_ID,SESS_ID
WHERE ELECT_ID ='|| p_elect_id;
execute immediate sql1;
END nears_listall_trainsch_sp;END nears_trainsch_pg;
PROCEDURE nears_listall_trainsch_sp
(
p_elect_id IN Varchar2,
p_cur_trainsch OUT type_cur_trainsch
)
IS
BEGIN
OPEN p_cur_trainsch FOR
SELECT ELECT_ID,SESS_ID
FROM TRAIN_SCH
WHERE ELECT_ID = p_elect_id;
END nears_listall_trainsch_sp;
TYPE type_cur_trainsch IS REF CURSOR;
.......
sql1 := 'SELECT ELECT_ID,SESS_ID FROM TRAIN_SCH WHERE ELECT_ID =:m';
OPEN p_cur_trainsch FOR sql1 using p_elect_id;记得用完游标后关闲.