不使用游标的动态表名 [英] dynamic table name without using cursors

问题描述

我想在 for 循环中使用动态表名.我知道使用游标可以解决这个问题.但是我想知道是否有没有游标的解决方案，因为我当前的代码具有通用的 for 循环(隐式游标)，我想知道是否可以在不使用显式游标的情况下以相同的方式保留它.

现在对我有用的是:

BEGIN对于 itr in(从 schedule_table 中选择 var1、var2、var3、var4)环形--使用itr.var1、itr.var2、itr.var3、itr.var4工作结束循环结尾

我想知道是否可以使用相同类型的 for 循环但动态地将表名传递给它.我研究了立即执行动态 sql 选项，但我认为我无法在 for 循环中使用它.在我开始重新设计整个过程以使用显式游标之前，我只是想在这里检查一下(不是很喜欢，因为与隐式相比，我担心性能).

谢谢.

解决方案

您可以使用 EXECUTE IMMEDIATE 来BULK COLLECT INTO 一个集合并通过它循环:

Oracle 12c

DECLARETYPE t_rec IS RECORD( fname VARCHAR2(100), lname VARCHAR2(100) );类型 t_tab 是 t_rec 表；t t_tab;v_table_name VARCHAR2(128) := 'HR.EMPLOYEES';开始EXECUTE IMMEDIATE 'SELECT first_name, last_name FROM ' ||v_table_name批量收集到 t;FOR i IN 1 .. t.COUNT 循环DBMS_OUTPUT.PUT_LINE( t(i).fname || ' ' || t(i).lname );结束循环；结尾;/

注意:在 Oracle 10/11 中，您需要在 SQL 范围内创建集合类型.在 Oracle 12 中，您可以在 PL/SQL 范围内声明它.

I want to use dynamic table name within a for loop. I understand that there are solutions to this problems by using cursors. But I was wondering if there is a solution without cursor as my current code has generic for loop (implicit cursors) and I wanted to know if I can retain it the same way without going for explicit cursors.

What works for me right now is:

BEGIN 
for itr in (select var1,var2,var3,var4 from schedule_table)
loop
--work using itr.var1, itr.var2, itr.var3, itr.var4 
end loop
END

and I want to know if there was anyway I can use the same kind of for loop but pass table name to it dynamically. I looked into execute immediate dynamic sql options, but I don't think I would be able to use it within for loop. I just thought of checking this here before I went about redesigning the entire procedure to work with explicit cursors (not a big fan as I fear performance compared to implicit).

Thank You.

解决方案

You can use EXECUTE IMMEDIATE to BULK COLLECT INTO a collection and the loop through that:

Oracle 12c

DECLARE
  TYPE t_rec IS RECORD( fname VARCHAR2(100), lname VARCHAR2(100) );
  TYPE t_tab IS TABLE OF t_rec;
  t t_tab;
  v_table_name VARCHAR2(128) := 'HR.EMPLOYEES';
BEGIN
  EXECUTE IMMEDIATE 'SELECT first_name, last_name FROM ' || v_table_name
    BULK COLLECT INTO t;

  FOR i IN 1 .. t.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE( t(i).fname || ' ' || t(i).lname );
  END LOOP;
END;
/

Note: In Oracle 10/11 you will need to create the collection type in the SQL scope. In Oracle 12, you can declare it in the PL/SQL scope.

这篇关于不使用游标的动态表名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！