SET ECHO         ON

--
-- plsql04.txt
--

-- Simple PL/SQL demo
-- This demo makes use of the key words:
-- FOR UPDATE OF
-- and
-- WHERE CURRENT OF
-- When DBAs make use of the term: 'Current of cursor'
-- they are talking about this technique.
 
-- Create a table for demonstration purposes

DROP   TABLE dropmePlsql04;
CREATE TABLE dropmePlsql04 as
SELECT table_name,comments
FROM dictionary
WHERE table_name like '%TABLES';


DECLARE

   CURSOR dictCursor IS           
      SELECT table_name,comments
      FROM dropmePlsql04
      FOR UPDATE OF table_name,comments; -- The main point of this demo.
   -- I may use a cursor to give me access to specific rows which I might want to update.

BEGIN

   FOR aRecord IN dictCursor LOOP
     
     -- Notice the syntax I use here to access a field inside of aRecord which
     -- corresponds to a column in the SELECT  statement.
     IF aRecord.table_name = 'ALL_TABLES' THEN 
       UPDATE dropmePlsql04 SET table_name  = 'Bikle',  comments = 'was here.'
       WHERE CURRENT OF dictCursor; -- The main point of this demo.
     END IF;

   END LOOP;

END;
/

-- Check that the UPDATE happened
SELECT * FROM dropmePlsql04 ORDER BY table_name;

-- End of demo