17,377
社区成员
发帖
与我相关
我的任务
分享
CREATE OR REPLACE FUNCTION get_sql(p_table VARCHAR2) RETURN VARCHAR2 IS
l_str VARCHAR2(1000);
BEGIN
l_str:='select ename from '||p_table;
RETURN l_str;
END;
/
CREATE OR REPLACE PROCEDURE proc_test(p_table VARCHAR2,p_out OUT Sys_Refcursor) IS
l_str VARCHAR2(1000);
BEGIN
l_str:=get_sql(p_table);
dbms_output.put_line(l_str);
OPEN p_out FOR l_str;
END;
/
DECLARE
l_c SYS_REFCURSOR;
l_v VARCHAR2(100);
BEGIN
proc_test('scott.emp',l_c);
LOOP
FETCH l_c INTO l_v;
dbms_output.put_line(l_v);
EXIT WHEN l_c%NOTFOUND;
END LOOP;
END;
/
输出:
select ename from scott.emp
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILLER
MILLER
[Quote=引用 5 楼 zpos 的回复:]
SQL> create or replace function f1
2 return nvarchar2
3 is
4 begin
5 return 'select * from a';
6 end;
7 /
Function created
SQL>
SQL> create or replace procedure p1
2 as
3 strsql nvarchar2(200);
4 begin
5 select f1 into strsql from dual;
6 dbms_output.put_line(strsql);
7 end;
8 /
Procedure created
SQL> set serveroutput on;
SQL> exec p1;
select * from a
PL/SQL procedure successfully completed
SQL>
CREATE OR REPLACE FUNCTION get_sql(p_table VARCHAR2) RETURN VARCHAR2 IS
l_str VARCHAR2(1000);
BEGIN
l_str:='select ename from '||p_table;
RETURN l_str;
END;
/
CREATE OR REPLACE PROCEDURE proc_test(p_table VARCHAR2) IS
l_str VARCHAR2(1000);
BEGIN
l_str:=get_sql(p_table);
dbms_output.put_line(l_str);
END;
/
BEGIN
proc_test('scott.emp');
END;
/
[Quote=引用楼主 zpos 的帖子:]