写了个函数实现输入一个title_id参数,查询到另外两个参数v_btitle和v_pname放在游
标里.
编译通过,但运行出错.
请问哪里不对?着急啊,跪求高人指教.create or replace package types as
type mytype is ref cursor;
end;create or replace function get(title_id VARCHAR2)
return types.mytype is
cursor_bp types.mytype;
sqlstr varchar2(4096); v_btitle VARCHAR2(30);
v_pname VARCHAR2(30);begin
sqlstr :='SELECT title,name
FROM books JOIN publisher using(pubid)
WHERE isbn = :title_id'; -- open cursor_bp for sqlstr;
LOOP
FETCH cursor_bp INTO v_btitle,v_pname;
EXIT WHEN cursor_bp%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_btitle||' is published by '||v_pname);
END LOOP;
end;
标里.
编译通过,但运行出错.
请问哪里不对?着急啊,跪求高人指教.create or replace package types as
type mytype is ref cursor;
end;create or replace function get(title_id VARCHAR2)
return types.mytype is
cursor_bp types.mytype;
sqlstr varchar2(4096); v_btitle VARCHAR2(30);
v_pname VARCHAR2(30);begin
sqlstr :='SELECT title,name
FROM books JOIN publisher using(pubid)
WHERE isbn = :title_id'; -- open cursor_bp for sqlstr;
LOOP
FETCH cursor_bp INTO v_btitle,v_pname;
EXIT WHEN cursor_bp%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_btitle||' is published by '||v_pname);
END LOOP;
end;
FROM books JOIN publisher using(pubid)
WHERE isbn = :title_id'; --
改为
sqlstr :='SELECT title,name
FROM books JOIN publisher using(pubid)
WHERE isbn ='||title_id; --
2.function 里面open了游标没有关闭。
3.如果是function,你该最后 return cursor_bp,并且不应该在function里面把游标打开去轮循,否则你就算return了这个游标,它也到了游标末尾了,别的地方调用就会出错。可能还有其他地方错误,认真看下function的定义吧,不要一开始就用package之类的,就定义个function,调通了再去试package之类的。
对于第1点,改成||运行是可以的,但是性能不好,建议改成
sqlstr :='SELECT title,name
FROM books JOIN publisher using(pubid)
WHERE isbn = :title_id'; -- open cursor_bp for sqlstr using title_id ;