how to Pass table name to PL SQL cursor dynamicall

问题:

I have written one SQL Procedure where I have written one cursor and every time i have to pass table name to cursor query dynamically .

create or replace
PROCEDURE Add_DEN as 


v_TableName VARCHAR2(4000) := 'BO_USER_DATA';

cursor c_DEN is select * from BO_USER_DATA; // Want to pass dynamically ,now hardcoded
r_DEN c_DEN%ROWTYPE;

fetch c_DEN into r_DEN; 
v_Name := r_DEN."Name";

Can i write something like this

cursor c_DEN is "select * from " || v_TableName; 

Any Help ?

回答1:

here an example:

declare
  TYPE curtype IS REF CURSOR;
  l_cursor curtype;
  l_param  number;
  l_key number;
  l_value number;
  l_sql varchar2(200);
begin
  /* build your sql... */
  l_sql   := 'with data as (select 1 key, 100 value from dual union select 2, 200 from dual union select 3, 300 from dual union select 3, 301 from dual)' ||
         ' select key, value from data where key = :1';
  l_param := 3;

  open l_cursor for l_sql
    using l_param;

  loop
    fetch l_cursor
      into l_key, l_value;
    exit when l_cursor%notfound;

    dbms_output.put_line(l_key||' = '||l_value);
  end loop;
  close l_cursor;
end;

Result:

3 = 300

3 = 301

回答2:

The basic answer is yes, you can and Given your example I would recommend you to use execute immediate to execute an arbitrary SQL string and bind the variables. I would nevertheless reconsider if you really needed to dynamically set the table as this is not very often really needed.

Example:

DECLARE
   sql_stmt    VARCHAR2(200);
   emp_id      NUMBER(4) := 7566;
   emp_rec     emp%ROWTYPE;
BEGIN
   sql_stmt := 'SELECT * FROM emp WHERE empno = :id';
   EXECUTE IMMEDIATE sql_stmt INTO emp_rec USING emp_id;
END;