Cursor within a cursor : Nested Cursor « Cursor « Oracle PL / SQL

Home
Oracle PL / SQL
1.Aggregate Functions
2.Analytical Functions
3.Char Functions
4.Constraints
5.Conversion Functions
6.Cursor
7.Data Type
8.Date Timezone
9.Hierarchical Query
10.Index
11.Insert Delete Update
12.Large Objects
13.Numeric Math Functions
14.Object Oriented Database
15.PL SQL
16.Regular Expressions
17.Report Column Page
18.Result Set
19.Select Query
20.Sequence
21.SQL Plus
22.Stored Procedure Function
23.Subquery
24.System Packages
25.System Tables Views
26.Table
27.Table Joins
28.Trigger
29.User Previliege
30.View
31.XML
Oracle PL / SQL » Cursor » Nested Cursor 
Cursor within a cursor
 
SQL> -- Cursor within a cursor
SQL>
SQL>     create or replace procedure MULTIPLE_CURSORS_PROC is
  2          v_owner varchar2(40);
  3          v_table_name varchar2(40);
  4          v_column_name varchar2(100);
  5
  6          
  7          cursor firstCursor is
  8          select distinct tbl.owner, tbl.table_name
  9          from all_tables tbl
 10          where tbl.owner = 'SYSTEM';
 11
 12          
 13          cursor secondCursor is
 14          select distinct col.column_name
 15          from all_tab_columns col
 16          where col.owner = v_owner
 17          and col.table_name = v_table_name;
 18
 19         begin
 20
 21            
 22            open firstCursor;
 23            loop
 24                fetch firstCursor into v_owner, v_table_name;
 25
 26                dbms_output.put_line('v_owner: '||v_owner);
 27                dbms_output.put_line('v_table_name: '||v_table_name);
 28
 29                open secondCursor;
 30                loop
 31                    fetch secondCursor into v_column_name;
 32                    dbms_output.put_line('v_column_name: '||v_column_name);
 33                end loop;
 34                close secondCursor;
 35
 36             end loop;
 37             close firstCursor;
 38
 39      EXCEPTION
 40      WHEN OTHERS THEN
 41            raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
 42      end MULTIPLE_CURSORS_PROC;
 43      /

Procedure created.

SQL>
SQL>     exec MULTIPLE_CURSORS_PROC();

           
         
  
Related examples in the same category
1.Nested cursor demo
java2s.com  | Contact Us | Privacy Policy
Copyright 2009 - 12 Demo Source and Support. All rights reserved.
All other trademarks are property of their respective owners.