通过动态替换变量值来执行存储在表列中的选择语句

发布于 2025-02-12 15:38:32 字数 526 浏览 0 评论 0原文

我有一个简单的PL/SQL块，下面的代码

declare 
rule1 varchar2(100 char);
begin
for i in (select table_name from all_tables where table_owner='EqEDI') loop
  execute immediate 'select rule_stmt from rulebook ' into rule1 ;
  execute immediate rule1 into result;
  dbms_output.put_line('Result is '||result);
end loop;
end;

在表格规则手册中存储的规则语句是：

"'select count(1) from '|| <tablename>"

我希望以上所有的表为给定所有者的所有表执行此上述语句但是在执行时，它不会用实际表中的查询中替换。如何使用简单的PL/SQL块实现它。

原文

I have simple PL/SQL block with below code

declare 
rule1 varchar2(100 char);
begin
for i in (select table_name from all_tables where table_owner='EqEDI') loop
  execute immediate 'select rule_stmt from rulebook ' into rule1 ;
  execute immediate rule1 into result;
  dbms_output.put_line('Result is '||result);
end loop;
end;

the rule statement stored in table rulebook is :

"'select count(1) from '|| <tablename>"

I want this above statement to be executed for all tables present for given owner
but while executing, it does not replace in query with actual tables.
How can I achieve it with simple PL/SQL block.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

嘴硬脾气大 2025-02-19 15:38:32

规则手册表的内容有点像错误。并不是说您无法按照存储选择语句进行操作 - 它只是不切实际的，因为您必须删除单个报价，删除tablename（因为您无法甚至可以绑定它，但会使光标返回的内容加入……太多不必要的工作要做。

另外，请检查all_tables及其列的名称 - 没有table_owner，只有所有者。

因此，我建议您存储这样的声明：

SQL> SELECT * FROM rulebook;

RULE_STMT
--------------------------------------------------------------------------------
select count(*) from

修复您的PL/SQL脚本：

SQL> SET SERVEROUTPUT ON
SQL>
SQL> DECLARE
  2     rule1   VARCHAR2 (100 CHAR);
  3     l_str   VARCHAR2 (100);
  4     result  NUMBER;
  5  BEGIN
  6     FOR i IN (SELECT table_name
  7                 FROM all_tables
  8                WHERE     owner = 'SCOTT'
  9                      AND table_name = 'EMP')
 10     LOOP
 11        EXECUTE IMMEDIATE 'select rule_stmt from rulebook '
 12           INTO rule1;
 13
 14        l_str := rule1 || i.table_name;
 15
 16        EXECUTE IMMEDIATE l_str
 17           INTO result;
 18
 19        DBMS_OUTPUT.put_line ('Result is ' || result);
 20     END LOOP;
 21  END;
 22  /
Result is 14

PL/SQL procedure successfully completed.

SQL>

rulebook table's contents is kind of wrong. Not that you can NOT do it the way you stored select statement into it - it is just impractical as you have to remove single quotes, remove tablename (as you can't even bind it, but concatenate what cursor returned) ... too much unnecessary jobs to be done.

Also, check all_tables and names of its columns - there's no table_owner, just owner.

Therefore, I'd suggest you to store such a statement:

SQL> SELECT * FROM rulebook;

RULE_STMT
--------------------------------------------------------------------------------
select count(*) from

Fix your PL/SQL script:

SQL> SET SERVEROUTPUT ON
SQL>
SQL> DECLARE
  2     rule1   VARCHAR2 (100 CHAR);
  3     l_str   VARCHAR2 (100);
  4     result  NUMBER;
  5  BEGIN
  6     FOR i IN (SELECT table_name
  7                 FROM all_tables
  8                WHERE     owner = 'SCOTT'
  9                      AND table_name = 'EMP')
 10     LOOP
 11        EXECUTE IMMEDIATE 'select rule_stmt from rulebook '
 12           INTO rule1;
 13
 14        l_str := rule1 || i.table_name;
 15
 16        EXECUTE IMMEDIATE l_str
 17           INTO result;
 18
 19        DBMS_OUTPUT.put_line ('Result is ' || result);
 20     END LOOP;
 21  END;
 22  /
Result is 14

PL/SQL procedure successfully completed.

SQL>

回复收藏 0 原文

~没有更多了~