CODEWITHSUNDEEP
sqloracle-databasefor-loopplsqlcursor
joe_t
joe_t

Reputation: 1

ORACLE SQL CURSOR / FOR LOOP

I need to verify converted data, distinct values and records counts. I would like to write statements so that I can enter a table name, then retrieve it's columns and use them in a query to get its distinct values (the actual values, not just a count of how many distinct) and their count.

I think I need to a CURSOR or CURSOR FOR LOOP and create something like this:

declare 
    cursor field_name
is 
  select COLUMN_NAME
     from user_tab_cols
     where table_name='TABLE1'
c_field_name    field_name%ROWTYPE;


BEGIN
    OPEN field_name
    loop 
        fetch field_name INTO c_field_name;
        exit when field_name%NOTFOUND;
    end loop;
    CLOSE field_name;
end;

Then run a query using that above in something like

select field_name, count(*)
from table1
group by field_name

Do I need to create 2 loop statements? I've not yet created one and can't quite get the context to get my results so far.

Upvotes: 0

Views: 1131

Answers (2)

Pawan Rawat
Pawan Rawat

Reputation: 525

Considering you will be giving the table name as parameter below code will print all the values of all the columns one by one along with the count of the values 

create or replace PROCEDURE PR_PREP(
P_TABLE_NAME IN VARCHAR2)
IS
  CURSOR CUR_COLUMNS (PA_TABLE_NAME VARCHAR2)
  IS
    SELECT COLUMN_NAME FROM ALL_TAB_COLUMNS WHERE TABLE_NAME = PA_TABLE_NAME;
  COL_NAMES CUR_COLUMNS%ROWTYPE;
TYPE TYP_RECORD
 IS
 RECORD
 (
   FIELD_NAME VARCHAR2(255),
  CNT        INT);
TYPE TYP_OP_TABLE
IS
 TABLE OF TYP_RECORD;
  OP_TABLE TYP_OP_TABLE;
 I     INT;
 V_SQL VARCHAR2(2000);
BEGIN
  FOR COL_NAMES IN CUR_COLUMNS(P_TABLE_NAME)
   LOOP
     V_SQL := 'SELECT ' || COL_NAMES.COLUMN_NAME || ' AS FIELD_NAME , 
 COUNT(*) AS CNT FROM ' || 
         P_TABLE_NAME || ' GROUP BY ' || COL_NAMES.COLUMN_NAME ;
  --    DBMS_OUTPUT.PUT_LINE (V_SQL);
     EXECUTE IMMEDIATE V_SQL BULK COLLECT INTO OP_TABLE;
  dbms_output.put_line('columna name = ' ||COL_NAMES.COLUMN_NAME);
     FOR I IN OP_TABLE.FIRST .. OP_TABLE.LAST
     LOOP
       DBMS_OUTPUT.PUT_LINE('FIELD VALUE '||OP_TABLE(I).FIELD_NAME || ' COUNT = ' || OP_TABLE(I).CNT);
     END LOOP;
      DBMS_OUTPUT.PUT_LINE('ONE FILED ENDED , NEXT STARTED');
    END LOOP;
 END;

Upvotes: 0

Cyrille MODIANO
Cyrille MODIANO

Reputation: 2376

BEGIN
    FOR myrow in (select field_name, count(*) as "count" from table1 group by field_name)
    loop 
        dbms_output.put_line(myrow.field_name);
        dbms_output.put_line(myrow.count);
    end loop;
end;

Upvotes: 1

Related Questions