DECLARE TYPE v_cur IS REF CURSOR; v_cur_ref v_cur; BEGIN OPEN v_cur_ref FOR SELECT FUNCTION (a) FROM DUAL; LOOP EXIT WHEN v_cur_ref%NOTFOUND; END LOOP; CLOSE v_cur_ref; END; 用游标变量试试。 循环体里面还需要加上FETCH INTO 的语句,因为我不知道楼主的函数返回的值是什么,故没有写出来。
CREATE OR REPLACE package pkg_test AS type myrctype is ref cursor; function get return myrctype; end pkg_test; CREATE OR REPLACE package body pkg_test as function get return myrctype is rc myrctype; --定义ref cursor变量 sqlstr varchar2(500); BEGIN --静态测试,直接用select语句直接返回结果 open rc for SELECT JOINT_DRIVER_ID, DRIVER_ID, COMPANY_ID, GROUP_ID, DRIVER_NAME FROM DRIVER_MST; return rc; end get; end pkg_test;select pkg_test.get() from dual; ================================= 以上是函数的主题部分。 请问,我之前还创建了存储过程,能返回游标。也不晓得怎么在另一个过程活函数中调用,呵呵 谢谢了~~
DECLARE type cur is record( JOINT_DRIVER_ID NUMBER(8), DRIVER_ID VARCHAR2(8), COMPANY_ID VARCHAR2(10), GROUP_ID VARCHAR2(4), DRIVER_NAME VARCHAR2(20) ); type cur_result_type is ref cursor return cur; cur_refcur cur_result_type; cur_object cur;
BEGIN OPEN cur_refcur For select pkg_test.get() from dual; fetch cur_refcur into cur_object; while cur_refcur%found loop --begin for DBMS_OUTPUT.put_line(cur_object.DRIVER_NAME || cur_object.GROUP_ID); fetch cur_refcur into cur_object; end loop;--end for close cur_refcur;END; ============================ 我按照存储过程内的写法循环读出数据,但是好像这样也不行。求救~~
DECLARE TYPE cur IS RECORD ( joint_driver_id NUMBER (8), driver_id VARCHAR2 (8), company_id VARCHAR2 (10), GROUP_ID VARCHAR2 (4), driver_name VARCHAR2 (20) ); TYPE cur_result_type IS REF CURSOR; cur_refcur cur_result_type; cur_object cur; BEGIN OPEN cur_refcur FOR SELECT pkg_test.get () FROM DUAL; LOOP FETCH cur_refcur INTO cur_object; DBMS_OUTPUT.put_line (cur_object.driver_name || cur_object.GROUP_ID); EXIT WHEN cur_refcur%NOTFOUND; END LOOP; CLOSE cur_refcur; END;这样子试试,应该就可以了。
TYPE v_cur IS REF CURSOR; v_cur_ref v_cur;
BEGIN
OPEN v_cur_ref FOR
SELECT FUNCTION (a)
FROM DUAL; LOOP
EXIT WHEN v_cur_ref%NOTFOUND;
END LOOP; CLOSE v_cur_ref;
END;
用游标变量试试。
循环体里面还需要加上FETCH INTO 的语句,因为我不知道楼主的函数返回的值是什么,故没有写出来。
package pkg_test AS
type myrctype is ref cursor;
function get return myrctype;
end pkg_test;
CREATE OR REPLACE
package body pkg_test as
function get return myrctype is
rc myrctype; --定义ref cursor变量
sqlstr varchar2(500);
BEGIN
--静态测试,直接用select语句直接返回结果
open rc for SELECT JOINT_DRIVER_ID,
DRIVER_ID,
COMPANY_ID,
GROUP_ID,
DRIVER_NAME
FROM
DRIVER_MST;
return rc;
end get;
end pkg_test;select pkg_test.get() from dual;
=================================
以上是函数的主题部分。
请问,我之前还创建了存储过程,能返回游标。也不晓得怎么在另一个过程活函数中调用,呵呵
谢谢了~~
DECLARE
type cur is record(
JOINT_DRIVER_ID NUMBER(8),
DRIVER_ID VARCHAR2(8),
COMPANY_ID VARCHAR2(10),
GROUP_ID VARCHAR2(4),
DRIVER_NAME VARCHAR2(20)
);
type cur_result_type is ref cursor return cur;
cur_refcur cur_result_type;
cur_object cur;
BEGIN OPEN cur_refcur For
select pkg_test.get() from dual;
fetch cur_refcur into cur_object;
while cur_refcur%found loop --begin for
DBMS_OUTPUT.put_line(cur_object.DRIVER_NAME || cur_object.GROUP_ID);
fetch cur_refcur into cur_object;
end loop;--end for
close cur_refcur;END; ============================
我按照存储过程内的写法循环读出数据,但是好像这样也不行。求救~~
TYPE cur IS RECORD (
joint_driver_id NUMBER (8),
driver_id VARCHAR2 (8),
company_id VARCHAR2 (10),
GROUP_ID VARCHAR2 (4),
driver_name VARCHAR2 (20)
); TYPE cur_result_type IS REF CURSOR; cur_refcur cur_result_type;
cur_object cur;
BEGIN
OPEN cur_refcur FOR
SELECT pkg_test.get ()
FROM DUAL; LOOP
FETCH cur_refcur
INTO cur_object; DBMS_OUTPUT.put_line (cur_object.driver_name || cur_object.GROUP_ID);
EXIT WHEN cur_refcur%NOTFOUND;
END LOOP; CLOSE cur_refcur;
END;这样子试试,应该就可以了。