In PSQL, I want to declare a cursor within cursor. The second cursor should use a value from the first cursor in the "where clause". How can I do this?

Below is an example of how to declare a cursor within a cursor .

In this example, we have a cursor called get_tables that retrieves the owner and table_name values. These values are then used in a second cursor called get_columns.

create or replace procedure MULTIPLE_CURSORS_PROC is
    v_owner varchar2(40);
    v_table_name varchar2(40);
    v_column_name varchar2(100);

    /* First cursor */
    cursor get_tables is
    select distinct tbl.owner, tbl.table_name
    from all_tables tbl
    where tbl.owner = 'SYSTEM';

    /* Second cursor */
    cursor get_columns is
    select distinct col.column_name
    from all_tab_columns col
    where col.owner = v_owner
    and col.table_name = v_table_name;

   begin

      -- Open first cursor
      open get_tables;
      loop
          fetch get_tables into v_owner, v_table_name;

          open get_columns;
          loop
              fetch get_columns into v_column_name;

          end loop;
          close get_columns;

       end loop;
       close get_tables;

EXCEPTION
WHEN OTHERS THEN
      raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
end MULTIPLE_CURSORS_PROC;

The trick to declaring a cursor within a cursor is that you need to continue to open and close the second cursor each time a new record is retrieved from the first cursor . That way, the second cursor will use the new variable values from the first cursor .

list of dbms

sql joins

sql server replication

dbms wiki

free sql training

sql server substring

sql server 2008 r2 standard

windows database software

website database

types of database

database software

sql server management studio download

database management

dbms systems

database designers

access database templates

dbms stands for

sql server

database server

sql training