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;
/

------解决方案--------------------

SQL code

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>

------解决方案--------------------

SQL code

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