SET ECHO ON

--
-- plsql02.txt
--

-- Simple PL/SQL demo

-- Create an empty table which will help the demo.
DROP   TABLE dropmePlsql02;
CREATE TABLE dropmePlsql02 AS SELECT table_name, comments FROM dict WHERE 1=2;

DECLARE

   -- This is the main idea for this demo.
   -- A cursor is a memory structure which I may fill with rows returned
   -- from a SELECT statement.

   CURSOR dictCursor IS           
      SELECT table_name,comments
      FROM dictionary
      ORDER BY table_name;

   -- These variables will be used to demonstrate how to pull data out of
   -- a table into a cursor and then into variables.
   -- Once a variable contains some data, the data may be copied into
   -- a table with a simple INSERT command.

   myTableName VARCHAR2(30);
   myComments  VARCHAR2(4000);

BEGIN

   OPEN dictCursor; -- This command runs the SELECT statement in the cursor
                    -- and thus 'fills' the cursor.

   -- Here I demonstrate how to get the first 4 rows in the cursor and ignore the rest.
   FOR i IN 1..4 LOOP  
      FETCH dictCursor INTO myTableName, myComments; -- xfr data into variables from cursor
      EXIT WHEN dictCursor%NOTFOUND; -- demo of how to deal with data at end of cursor

      -- Demo how to copy data from variable into a table with a simple INSERT command.
      INSERT INTO dropmePlsql02 VALUES (myTableName, myComments);
      COMMIT;
   END LOOP;
   -- Notice that the loop is sandwiched between OPEN and CLOSE dictCursor
   CLOSE dictCursor; 
END;
/

-- Check if data really did get transfered from variables to the table
SELECT table_name, comments FROM dropmePlsql02;

-- End of demo