Storing Select result and using loop to test in PL/SQL block

Question

I want to store a result set of a query into some temporary location(heard of cursors) and then use a loop to test each column against a value. I tried

Declare r_rec mytable%ROWTYPE;
BEGIN
select * into r_rec from mytable where column='20190103';
/*IF need to test certain condition for each column.
Then
V_C:=V_C+1;
end if; */

end;
/

I am sorry if I confused you. My requirement is to check whether any column of a set of records contains 0 if it is I need to increment to get the count of rows that has 0 in any column. I can do query it but I will have to type all 200 columns and I am looking for a alternative where I can test each record of a select query to check if any column in any record fetched has 0.

Sorry about not posting my question correctly.

Why don't you use an `UPDATE` statement with a `WHERE` clause that defines which rows should be updated? I don't understand the need for a cursor in this case — Martin, Jan 03 '19 at 11:49
If you're testing for a condition, why not do that as part of the query instead of inside a loop? What will you do with the rows that do and do not meet the condition? You might be looking for [collections](https://docs.oracle.com/en/database/oracle/oracle-database/12.2/lnpls/plsql-collections-and-records.html), but it isn't clear... if you're just counting how many rows match the condition then you might only need a simple aggregate `count(*)`, which doesn't need PL/SQL at all. — Alex Poole, Jan 03 '19 at 12:02

score 3 · Answer 1 · answered Jan 03 '19 at 12:26

A cursor doesn't store results, it's really a pointer to let you iterate over the results (as @Ted shows in action). If you want to store the results within your PL/SQL block then you can use a collection, which you can declare as a type matching your table to be close to your single-row query into a record type; and then bulk-collect into that:

declare
  type t_tab is table of mytable%ROWTYPE;
  v_tab t_tab;
  v_c pls_integer := 0;
begin
  select *
  bulk collect into v_tab
  from mytable
  where col1='20190103';

  for i in 1..v_tab.count loop
    if v_tab(i).col2 = 'Y' then -- whatever you need to test
      v_c := v_c + 1;
    end if;
  end loop;

  dbms_output.put_line(v_c);
end;
/

But unless you're doing something else with both the rows that match and those that don't match your condition, you could just add that as a test in the main query:

declare
  type t_tab is table of mytable%ROWTYPE;
  v_tab t_tab;
  v_c pls_integer := 0;
begin
  select *
  bulk collect into v_tab
  from mytable
  where col1='20190103'
  and col2='Y'; -- whatever you need to test

  for i in 1..v_tab.count loop
    v_c := v_c + 1;
  end loop;

  dbms_output.put_line(v_c);
end;
/

If you're only counting the matching rows then you don't need a cursor or a collection, just use the aggregate function:

declare
  v_c pls_integer;
begin
  select count(*)
  into v_c
  from mytable
  where col1='20190103'
  and col2='Y'; -- whatever you need to test

  dbms_output.put_line(v_c);
end;
/

or don't use PL/SQL at all:

select count(*)
from mytable
where col1='20190103'
and col2='Y'; -- whatever you need to test

Incidentally, your '20190103' value looks like you're storing a date as a string. You should use the correct data type - store dates as actual dates. (And if the column is a date then you're relying on implicit conversion, which is also not a good idea...)

I am sorry if I confused you. My requirement is to check whether any column of a set of records contains 0 if it is I need to increment to get the count of rows that has 0 in any column. I can do query it but I will have to type all 200 columns and I am looking for a alternative where I can test each record of a select query to check if any column in any record fetched has 0. Sorry about not posting my question correctly. — Rajni, Jan 04 '19 at 11:13
You still need to type all the column names so you can check them one-by-one inside the loop. Or you could use `dbms_sql` to automate that but that might be overcomplicating things. With either a static SQL query or PL/SQL block you could generate the list of columns via a query against the data dictionary, to save you physically typing them all, if that is your only objection. But you'd still somehow need to identify which columns to include - or maybe which to exclude, if that's a shorter list. — Alex Poole, Jan 04 '19 at 11:20
hi, can you please take a look at my question? it is similar to trimming last characters. thank you https://stackoverflow.com/questions/69834411/update-one-column-of-each-row-in-a-table-pl-sql-unix-scripting — dcdum2018, Nov 04 '21 at 05:33

score 2 · Answer 2 · answered Jan 03 '19 at 12:36

2

Here is a very simple way to loop through a query result:

BEGIN
  FOR rec IN (select col1, col2 from mytable where column = '20190103') LOOP
    IF rec.col1 > rec.col2 THEN
      ...
    END IF;
  END LOOP;
END;

answered Jan 03 '19 at 12:36

Thorsten Kettner

89,309
7
49
73

Ted at ORCL.Pro · Answer 3 · 2019-01-03T12:37:41.230

1

Here is a template which I think will help you:

DECLARE
   cursor c1 is
     select column1, column2 ... etc from mytable where column='20190103';

BEGIN

   FOR r_rec in c1
   LOOP
      if r_rec.column_XYZ = something then
       do_something;
      end if;
   END LOOP;
END;

edited Jan 03 '19 at 12:37

answered Jan 03 '19 at 12:00

Ted at ORCL.Pro

1,602
1
7
10

score 0 · Answer 4 · answered Jan 04 '19 at 20:57

I modified this answer from Search All Fields In All Tables For A Specific Value (Oracle)

to do counts. It will count of records for each field in a table that contains 0. Replace my table name with yours.

    SELECT count(*),
   SUBSTR (table_name, 1, 30) "Table",
      SUBSTR (column_name, 1, 30) "Column"
    FROM cols,
      TABLE (xmlsequence (dbms_xmlgen.getxmltype ('select '
    || column_name
      || ' from '
      || table_name
      || ' where upper('
     || column_name
     || ') like upper(''%'
     || 0
     || '%'')' ).extract ('ROWSET/ROW/*') ) ) t
     where table_name = 'INVENTORY_LINE'
     group by SUBSTR (table_name, 1, 30) ,
      SUBSTR (column_name, 1, 30) 
   ORDER BY "Table";

Storing Select result and using loop to test in PL/SQL block

4 Answers4