PLSQL dynamic query

问题:

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.

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;

回答1:

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;

回答2:

There is some miss match in veriables that you had used i.e.

1. declared as "c_table" but accessing as "c_table2"
2. Each table common column name is "C" but accessing as "object_ref"
3. In dynamic query use INTO keyword to store the value to your varibale

Suggestions

1. Use concat() function to prepare the query dynamically i.e. something like:

   SET @SQL := CONCAT('SELECT max(c) INTO ', c_obj, ' FROM ',c_table);

2. Steps of implementing dynamic query is:

   SET @SQL = <your dynamic query>
   PREPARE stmt FROM @SQL;
   EXECUTE stmt;
   

Sample code:

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_table;      
      EXIT WHEN cursor_a%notfound; 
      SET @SQL := CONCAT('SELECT max(object_ref) AS c_obj INTO ', c_obj, ' FROM ',c_table);
    PREPARE stmt FROM @SQL;         
    EXECUTE stmt;    
    dbms_output.put_line('Maximum value: '|| c_table || c_obj);
   END LOOP;
CLOSE cursor_a;
END;