PLSQL动态查询 [英] PLSQL dynamic query

问题描述

我有一个表A，其中的列A将表名作为值保存. 所有这些表都有一个公共列C.我需要每个表的此列的最大值.

I have a table A which has column A which holds table names as values. All these tables have a common column C. I need maximum value of this column for each table.

我使用动态SQL尝试过此操作，但出现错误.请提出建议.

I tried this using dynamic SQL but I'm getting errors. Please suggest.

DECLARE    
 query1 VARCHAR2(100);
 c_table VARCHAR2(40);
 c_obj VARCHAR2(20);
 Cursor cursor_a IS
 SELECT a FROM A;  
BEGIN
Open cursor_a;
   LOOP
      Fetch cursor_a INTO c_table2;      
      EXIT WHEN cursor_a%notfound;     
      query1 := 'SELECT max(object_ref) AS "c_obj" FROM c_table' ;
      EXECUTE IMMEDIATE query1;
      dbms_output.put_line('Maximum value: '|| c_table || c_obj);
   END LOOP;
Close cursor_a;
END;

推荐答案

动态SQL无法看到您的PL/SQL变量:您需要向其传递一个可以在SQL引擎范围内执行的字符串.因此，您需要将表名与该语句的样板文本连接起来:

Dynamic SQL can't see your PL/SQL variable: you need to pass it a string which can be executed in the scope of the SQL engine. So you need to concatenate the table name with the statement's boilerplate text:

query1 := 'SELECT max(c)  FROM ' || variable_name;

您还需要将查询结果返回到变量中.

You also need to return the result of the query into a variable.

这是它的工作方式(我从您的示例中删除了一些不必要的代码):

Here is how it works (I've stripped out some of the unnecessary code from your example):

DECLARE    
 c_table VARCHAR2(40);
 c_obj VARCHAR2(20);
BEGIN
   for lrec in ( select a as tab_name from A )
   LOOP
      EXECUTE IMMEDIATE 'SELECT max(object_ref)  FROM ' || lrec.tab_name 
           into c_obj ;
      dbms_output.put_line('Maximum value: '|| lrec.tab_name
           || '='|| c_obj);
   END LOOP;
END;

这篇关于PLSQL动态查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！