如何调用以SYS_REFCURSOR作为OUT参数的Oracle函数 [英] How to call Oracle function which has SYS_REFCURSOR as OUT Parameter
问题描述
create or replace
FUNCTION test_fun (
p_ref_cur OUT SYS_REFCURSOR,
p_a_code IN NUMBER DEFAULT 0,
p_category IN package.category%TYPE DEFAULT NULL,
p_name IN package.name%TYPE DEFAULT NULL,
p_display_name IN package.display_name%TYPE DEFAULT NULL,
p_rowid IN package."rowid"%TYPE DEFAULT NULL,
p_flg IN package.flg%TYPE DEFAULT '1',
p_mod_dat IN package.mod_dat%TYPE DEFAULT SYSTIMESTAMP,
p_mod_usr IN package.mod_usr%TYPE DEFAULT NULL
)
RETURN NUMBER
AS
如何在以SYS_REFCURSOR作为OUT参数的oracle中调用此函数?
How to call this function in oracle which has SYS_REFCURSOR as OUT parameter?
更新:
** test_fun **将通过另一个函数调用,例如 caller()哪里完成将执行的功能将从数据库中获取,并使用execute立即命令执行。
Update: **test_fun** will be called from another function say caller() where the complete function to be executed will be taken from the database and execute using execute immediate command.
SELECT command into cmd from data_table where id=p_id;
execute immediate (cmd);
cmd 的值将类似于
test_fun(v_cv1,0,'pp','np123','np',NULL,1,NULL,'testuser');
注意:我们可以控制 caller()函数和 cmd 来执行但无法控制 test_fun()
NOTE: We have control over the caller() function and cmd to be execute but no control over test_fun()
我们非常感谢您的帮助。
Any help is much appreciated.
推荐答案
此过程起到了神奇的作用
This procedure did the magic
PROCEDURE executecmd(cmd_in IN VARCHAR2)
AS
v_cv1 SYS_REFCURSOR;
BEGIN
dbms_output.put_line('Executing cmd: '|| cmd);
execute immediate 'declare
result number;
BEGIN
result:='||cmd_in||';'||
'END;'
USING v_cv1;
END;
这篇关于如何调用以SYS_REFCURSOR作为OUT参数的Oracle函数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!