如何动态地将表名传递给PL SQL游标? [英] how to Pass table name to PL SQL cursor dynamically?

问题描述

我编写了一个SQL过程，其中我编写了一个游标，每次我必须动态地将表名传递给游标查询时.

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

我可以这样写吗

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

有帮助吗?

推荐答案

此处为示例:

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;

结果:

3 = 300

3 = 301

这篇关于如何动态地将表名传递给PL SQL游标?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！