CODEWITHSUNDEEP
oracle-databaseplsql
Luis perez
Luis perez

Reputation: 132

rowtype with more than one row?

I must do a pl/sql that shows me some rows of a sql. Sorry I'm newbie and I'm trying to do with rowtype but I see that only shows me the first line of the result. How is the best way to store the sql statement result into some variable if it has more than one row. Thanks and sorry for my English!

    BEGIN
FOR I IN (select xxxxxxxxxxxxx) LOOP    
    DECLARE
    CURSOR CURSORMINUS 
    IS
            select * xxxxxxx
            minus
            select * xxxxxxxxx;

            v_reg CURSORMINUS%rowtype;
      BEGIN
            OPEN CURSORMINUS;
            FETCH CURSORMINUS INTO V_REG;



            IF CURSORMINUS%NOTFOUND THEN DBMS_OUTPUT.PUT_LINE('no differences...'); 
            ELSE
            DBMS_OUTPUT.PUT_LINE('the differences are ....' || v_reg.APP_OBJECT );
            END IF;

      END;

END LOOP;
END;
/

Upvotes: 0

Views: 3738

Answers (3)

Guneli
Guneli

Reputation: 1731

Please, check this: 

    BEGIN
        FOR I IN (select xxxxxxxxxxxxx) LOOP    
            DECLARE
            has_records BOOLEAN:=FALSE;
            CURSOR CURSORMINUS 
            IS
                    select * xxxxxxx
                    minus
                    select * xxxxxxxxx;

                    v_reg CURSORMINUS%rowtype;
              BEGIN
                    OPEN CURSORMINUS;
                    LOOP 
                        FETCH CURSORMINUS INTO V_REG;
                        EXIT WHEN CURSORMINUS%NOTFOUND;
                        DBMS_OUTPUT.PUT_LINE('the differences are ....' || v_reg.APP_OBJECT );
                        has_records:=TRUE;


                    END LOOP;
                    CLOSE CURSORMINUS;

                    IF NOT has_records  THEN 
                        DBMS_OUTPUT.PUT_LINE('no differences...'); 
                    END IF;

              END;

        END LOOP;
        END;

    /

Upvotes: 0

Lalit Kumar B
Lalit Kumar B

Reputation: 49082

I'm trying to do with rowtype but I see that only shows me the first line of the result.

From documentation,

The %ROWTYPE attribute provides a record type that represents a row in a database table. The record can store an entire row of data selected from the table or fetched from a cursor or cursor variable.

Coming to your question,

How is the best way to store the sql statement result into some variable if it has more than one row.

You need a PL/SQL collection type.

For example,

SQL> set serveroutput on
SQL> DECLARE
  2  TYPE tbl_emp
  3  IS
  4    TABLE OF emp%ROWTYPE;
  5    l_tab tbl_emp;
  6  BEGIN
  7    SELECT * BULK COLLECT INTO l_tab FROM emp;
  8    FOR i IN 1..l_tab.count
  9    LOOP
 10      dbms_output.put_line('Empno = '||l_tab(i).empno);
 11    END LOOP;
 12  END;
 13  /
Empno = 7369
Empno = 7499
Empno = 7521
Empno = 7566
Empno = 7654
Empno = 7698
Empno = 7782
Empno = 7788
Empno = 7839
Empno = 7844
Empno = 7876
Empno = 7900
Empno = 7902
Empno = 7934

PL/SQL procedure successfully completed.

SQL>

Upvotes: 1

anudeepks
anudeepks

Reputation: 1132

is this what you are looking for ?

  SCOTT@research 16-APR-15> select * from test1;

  VAL1       VAL2       VAL3
  ---------- ---------- ----------
   555          2          4
     3          2          4
   123          2          3
    42          3

   declare
   begin
   for i in (select * from test1) loop
   dbms_output.put_line('output is' );
   dbms_output.put_line(i.val1);

   end loop;
   end;
   /

output is   
555
3
123
42

Upvotes: 0

Related Questions