英文:
When i call package.function within a procedure and function return value in cursor, how can i store these value in procedure variable in Oracle SQL?
问题
以下是要翻译的代码部分：
CREATE OR REPLACE PROCEDURE LIST_LOG(name VARCHAR2, id VARCHAR2, label VARCHAR2)
IS
v_query VARCHAR2(4000);
v_cursor SYS_REFCURSOR;
v_result VARCHAR2(4000);
BEGIN
CASE label
WHEN 'abc' THEN
v_query := 'SELECT PKG_LIST_LOG.abc('''||name||''','''||id||''','''||label||''') FROM DUAL';
OPEN v_cursor FOR v_query;
FETCH v_cursor INTO v_result;
-- EXECUTE IMMEDIATE v_query INTO v_result;
DBMS_OUTPUT.PUT_LINE(v_result);
ELSE
DBMS_OUTPUT.PUT_LINE('INVALID PARAMETERS');
END CASE;
END;
/
请注意，我只提供了代码部分的翻译。
英文:
The PKG_LIST_LOG.abc (package.function) return a value like {<PR=100: ORA-01403: no data ,SC=ORA-01403: no data found,PSC=sp_ls :LA,EXEC_DATE=04-MAY-23>,} so how can i store this information in the below procedure and print the result.
CREATE OR REPLACE PROCEDURE LIST_LOG(name VARCHAR2, id VARCHAR2, label VARCHAR2)
IS
v_query VARCHAR2(4000);
v_cursor SYS_REFCURSOR;
v_result VARCHAR2(4000);
BEGIN
CASE label
WHEN 'abc' THEN
v_query := 'SELECT PKG_LIST_LOG.abc('''||name||''','''||id||''','''||label||''') FROM DUAL';
OPEN v_cursor FOR v_query;
FETCH v_cursor INTO v_result;
-- EXECUTE IMMEDIATE v_query INTO v_result;
DBMS_OUTPUT.PUT_LINE(v_result);
ELSE
DBMS_OUTPUT.PUT_LINE('INVALID PARAMETERS');
END CASE;
END;
/
答案1
得分: 0
你应该只是将 abc 函数的结果放入 v_cursor 中。由于我没有你的表格或数据（你也没有发布任何额外信息），这里是基于 Scott 的示例模式的示例。
函数返回 ref cursor：
create or replace function abc(p_deptno in number, p_job in varchar2)
return sys_refcursor
is
rc sys_refcursor;
begin
open rc for select ename, sal
from emp
where deptno = p_deptno
and job = p_job;
return rc;
end;
/
过程调用该函数；请注意第 7 行 - 这是你应该做的；你的代码中没有 动态 部分，所以你不需要构建调用该函数的 select 语句，也不需要打开一个游标 - 只需使用函数返回的内容：
create or replace procedure list_log(p_deptno in number, p_job in varchar2)
is
v_cursor sys_refcursor;
v_ename emp.ename%type;
v_sal emp.sal%type;
begin
v_cursor := abc(p_deptno, p_job);
loop
fetch v_cursor into v_ename, v_sal;
exit when v_cursor%notfound;
dbms_output.put_line(v_ename ||' '|| v_sal);
end loop;
close v_cursor;
end;
/
测试：
set serveroutput on
exec list_log(20, 'CLERK');
输出是否正确？看起来是的：
select ename, sal from emp where deptno = 20 and job = 'CLERK';
英文:
The way I understood it, you should just put result of the abc function into v_cursor.
As I don't have your tables nor data (and you didn't post any additional info), here's an example based on Scott's sample schema.
Function returns ref cursor:
SQL> create or replace function abc(p_deptno in number, p_job in varchar2)
2 return sys_refcursor
3 is
4 rc sys_refcursor;
5 begin
6 open rc for select ename, sal
7 from emp
8 where deptno = p_deptno
9 and job = p_job;
10 return rc;
11 end;
12 /
Function created.
Procedure calls that function; note line #7 - that's what you should do; there's nothing dynamic in your code so you don't have / need to compose a select statement which calls the function, nor should you open a cursor - just use what function returned:
SQL> create or replace procedure list_log(p_deptno in number, p_job in varchar2)
2 is
3 v_cursor sys_refcursor;
4 v_ename emp.ename%type;
5 v_sal emp.sal%type;
6 begin
7 v_cursor := abc(p_deptno, p_job);
8 loop
9 fetch v_cursor into v_ename, v_sal;
10 exit when v_cursor%notfound;
11 dbms_output.put_line(v_ename ||' '|| v_sal);
12 end loop;
13 close v_cursor;
14 end;
15 /
Procedure created.
Testing:
SQL> set serveroutput on
SQL> exec list_log(20, 'CLERK');
SMITH 840
ADAMS 1100
PL/SQL procedure successfully completed.
Is that output correct? Looks like it is:
SQL> select ename, sal from emp where deptno = 20 and job = 'CLERK';
ENAME SAL
---------- ----------
SMITH 840
ADAMS 1100
SQL>
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论