CODEWITHSUNDEEP
oracle-databaseplsql
Guillaume
Guillaume

Reputation: 3

How to define for each table, the maximum value of one field of a list?

I have a list of Oracle table and fields and I would like to define for each table, the maximum value of the field of the list.

Input:

+------+--------+ 
| TAB  | FIELDS |
+------+--------+ 
| tab1 | field1 |
+------+--------+ 
| tab2 | field2 |
+------+--------+

Output:

+------+--------+-----------+ 
|  TAB | FIELDS | Max value |
+------+--------+-----------+     
| tab1 | field1 |    10     |
+------+--------+-----------+     
| tab2 | field2 |    15     |
+------+--------+-----------+

I want to write a PL / SQL function to create the loop but I have very little knowledge in this language. Do you have any examples to show me? The input table is dynamic, which is why I want to use a loop. thanks in advance

Upvotes: 0

Views: 55

Answers (1)

Kaushik Nayak
Kaushik Nayak

Reputation: 31676

The input is build with system table like all_column_tab The output must be store in a table.

It is indeed not a great design to store and retrieve data, but I presume something like this should work for you. I've used a VARCHAR2 variable for storing max value instead of a Numeric because to handle MAX for non-numeric fields. Your table that stores the max val should be defined as VARCHAR2 for it to work normally for such cases.

DECLARE
    v_maxVal VARCHAR2(400);
    begin
        FOR rec IN
       (   SELECT table_name,column_name
                        FROM user_tab_columns where table_name IN ('TAB1','TAB2')
         ) 
       LOOP
         EXECUTE IMMEDIATE 
              'SELECT MAX('||rec.column_name||') FROM '||rec.table_name
                    INTO  v_maxVal ;                   

         INSERT INTO fieldstab(tab,fields,max_val) VALUES
             ( rec.table_name,rec.column_name,v_maxVal);

       END LOOP;                   
  END;
  /

DEMO

Upvotes: 2

Related Questions