定义了一个存储过程,其中要用到一个游标
cursor c1
is
select * from xxx
where xx in ('ds','sdf');
存储过程中传入的是一个字符串参数='ds','sdf'
如何在游标中直接使用这个参数?用过动态sql处理,可是oracle报错
存储过程如下:
create or replace procedure XXXXXX
(p_countrycode IN varchar2)
is
SqlText varchar2(500);
type c_type is ref cursor;
c1 c_type;
BEGIN
SqlText := '
SELECT
SERIAL_NO
FROM
XXXX
WHERE
ATTR in(' ||p_countrycode||') and DATETIME >= SYSDATE-10 and ATTRIBUTE_95 IS NULL';
StartDate:=sysdate;
delete tmp_
commit;
open c1 for SqlText;
/*open c1 for SqlText;*/
for rec in c1 loop
insert into tmp_
oracle每次执行到for rec in c1 loop就会报错,
pls-00221 C1 is not a procedure or is undefined
cursor c1
is
select * from xxx
where xx in ('ds','sdf');
存储过程中传入的是一个字符串参数='ds','sdf'
如何在游标中直接使用这个参数?用过动态sql处理,可是oracle报错
存储过程如下:
create or replace procedure XXXXXX
(p_countrycode IN varchar2)
is
SqlText varchar2(500);
type c_type is ref cursor;
c1 c_type;
BEGIN
SqlText := '
SELECT
SERIAL_NO
FROM
XXXX
WHERE
ATTR in(' ||p_countrycode||') and DATETIME >= SYSDATE-10 and ATTRIBUTE_95 IS NULL';
StartDate:=sysdate;
delete tmp_
commit;
open c1 for SqlText;
/*open c1 for SqlText;*/
for rec in c1 loop
insert into tmp_
oracle每次执行到for rec in c1 loop就会报错,
pls-00221 C1 is not a procedure or is undefined
不知道for的方法是否有什么限制
你的方法错了使用游标有两种方法 1 open fetch close
2 for loop
用for时,前面不能用open cursor
(p_str IN varchar2)
is
SqlText varchar2(500);
v_xx_id varchar2(20);
type c_type is ref cursor;
c1 c_type;
BEGIN
SqlText := ' SELECT XX_id FROM XXXX WHERE attr in (' ||p_str||') ';
dbms_output.put_line(sqltext);
open c1 for SqlText;
loop
fetch c1 into v_xx_id;
exit when c1%notfound;
dbms_output.put_line(v_xx_id);
end loop;
close c1;
end;