如何存储将返回sysrefcursor的函数的结果? [英] How to store result of a function which will return sysrefcursor?
问题描述
需要帮助。
场景:我们有一个游标的程序。
我需要调用一个函数,该函数将接受来自该游标值的输入并返回 SYS_REFCURSOR
。
我需要将函数的结果存储在一个不同的变量/ cursor&需要从过程返回这个值作为输出参数。
我使用oracle 11g。
请帮忙!
PFB我的方法:
create or替换过程prc_test
(p_dept_id in number,
c_detail out sysrefcursor) - 需要添加额外的输出参数
作为
var1 varchar2(200):= null;
begin
打开c_detail为
从dual选择-1;
如果p_dept_id不为空然后
打开c_detail为
从emp $ b选择emp_no其中dept_id = p_dept_id;
--i需要将'get_emp_dtls'函数的值作为输出参数进行重新计算。
结束if;
结束程序;
/
要调用的函数
CREATE OR REPLACE FUNCTION get_emp_dtls
(p_emp_no IN EMP.EMP_NO%TYPE)
RETURN SYS_REFCURSOR
AS
o_cursor SYS_REFCURSOR;
BEGIN
OPEN o_cursor FOR
SELECT
ENAME,
JOB
FROM emp
WHERE EMP_NO = p_emp_no;
RETURN o_cursor;
- 异常部分
END;
/
一个varchar2变量并返回一个refcursor(弱类型)。
创建或替换FUNCTION fn_return_cur(v IN VARCHAR2)
RETURN SYS_REFCURSOR
IS
c1 SYS_REFCURSOR;
BEGIN
OPEN c1 FOR
SELECT'ABC'
FROM dual
WHERE'col1'= v;
RETURN c1;
END;
/
以下是将游标值作为参数传递的过程
创建或替换PROCEDURE Pr_pass_out_cur(v_2 OUT SYS_REFCURSOR)
IS
func_arg VARCHAR2(3);
other_arg VARCHAR2(3);
CURSOR c_2 IS
SELECT'ABC'col1,
'DEF'col2
FROM dual;
BEGIN
LOOP
FETCH c_2 INTO func_arg,other_arg;
退出c_2%NOTFOUND;
v_2:= Fn_return_cur(func_arg);
END LOOP;
EXCEPTION
当其他
NULL;
END;
/
让我知道您的反馈。
Need a help.
Scenario : There is an procedure inside which we have a cursor.
I need to call a function which will take an input from that cursor value and will return SYS_REFCURSOR
.
I need to store that result of function in a different variable/cursor & need to return this value from procedure as out parameter.
I am using oracle 11g.
Please help! How I will proceed?
PFB My Approach:
create or replace procedure prc_test
(p_dept_id in number,
c_detail out sysrefcursor)--need to add extra out parameter
as
var1 varchar2(200) :=null;
begin
open c_detail for
select -1 from dual;
if p_dept_id is not null then
open c_detail for
select emp_no from emp
where dept_id=p_dept_id;
--i need to retrn value of 'get_emp_dtls' function as out parameter.
end if;
end procedure;
/
Function to be called
CREATE OR REPLACE FUNCTION get_emp_dtls
(p_emp_no IN EMP.EMP_NO%TYPE)
RETURN SYS_REFCURSOR
AS
o_cursor SYS_REFCURSOR;
BEGIN
OPEN o_cursor FOR
SELECT
ENAME,
JOB
FROM emp
WHERE EMP_NO = p_emp_no;
RETURN o_cursor;
-- exception part
END;
/
Here is your function that takes a varchar2 variable and returns A refcursor( weakly typed).
CREATE OR replace FUNCTION fn_return_cur(v IN VARCHAR2)
RETURN SYS_REFCURSOR
IS
c1 SYS_REFCURSOR;
BEGIN
OPEN c1 FOR
SELECT 'ABC'
FROM dual
WHERE 'col1' = v;
RETURN c1;
END;
/
Here is the procedure that has a cursor value passed as argument to function and the returned cursor passed as OUT argument.
CREATE OR replace PROCEDURE Pr_pass_out_cur(v_2 OUT SYS_REFCURSOR)
IS
func_arg VARCHAR2(3);
other_arg VARCHAR2(3);
CURSOR c_2 IS
SELECT 'ABC' col1,
'DEF' col2
FROM dual;
BEGIN
LOOP
FETCH c_2 INTO func_arg, other_arg;
EXIT WHEN c_2%NOTFOUND;
v_2 := Fn_return_cur(func_arg);
END LOOP;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
/
Let me know your feedback.
这篇关于如何存储将返回sysrefcursor的函数的结果?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!