PL/SQL Programming Log
Sample Templates
Bibliography

Saturday, 29 April, 2000

Although I purchased PL/SQL Programming online over a month ago, I only began reading it a few days ago.  I'm up to Chapter 6 now, on Database Interaction and Cursors.  I puchased Oracle Built-in Packages earlier this morning.  It is my goal to complete my first reading of the former before this latter book is delivered

I have already written a few templates - skeletons, if you will, for future programs.

package_template.txt    for packages
cursor_template.txt   for cursors
cursor_var_template.txt  for cursor variables
 
 

Sunday, 30 April, 2000

Finished up through Chapter 7, on Loops.

cursor_for_loop_template.txt   for cusrsor FOR loops (among Feurstein's favorite PL/SQL features, and I see why)

package_template.txt    for packages (ch. 3)
cursor_template.txt   for cursors  (ch. 6)
cursor_var_template.txt  for cursor variables (ch. 6)
cursor_for_loop_template.txt  for cusrsor FOR loops (ch. 7)
 

PACKAGE
/*
|| Author: John K. Joachim
||
|| Overview:
||
|| Major Modifications:
|| Date:
||
*/
IS
   /* -------------- Modules to define the list -----------------*/
 

   /* -------------- Modules to manage Item Selections ----- */

   /* -------------- Package Variables ----------------------------- */
 /*... declarations placed here ...*/

   /* -------------- Private Modules ------------------------------- */
 /* FUNCTION... */
 /* PROCEDURE ...*/

   /* -------------- Public Modules -------------------------------- */
 /* FUNCTION... */
 /* PROCEDURE ...*/

   END

Back to 29 April, 2000

cursor_template.txt

DECLARE
 /*
|| Author: John K. Joachim
||         Telamon Corporation
|| Overview: ________ Cursor
||
|| Major Modifications:
|| Date:
||
*/
      CURSOR cursor_name IS
 SELECT  .... FROM ;
         [record] cursor_name%ROWTYPE;
BEGIN
      /* Open only if cursor is not yet open */
      IF NOT cursor_name%ISOPEN
      THEN
            OPEN cursor_name;
       END IF;

       FETCH cursor_name INTO [record]  ;

       /* Keep fetching until no more records are FOUND */
       WHILE cursor_name%FOUND
        LOOP
 DBMS_OUTPUT.PUT_LINE
 ('Just fetched record number ' ||
 TO_CAHR  (cursor_name%ROWCOUNT));
 FETCH cursor_name INTO ......  ;
         END LOOP;
         CLOSE cursor_name;
END;

Back to 29 April, 2000
 

cursor_var_template.txt

DECLARE
         /* Create the cursor type.  */
        TYPE  company_curtype IS REF CURSOR RETURNcompany%ROWTYPE;

        /* Decalre a cursor variable of that type  */
       company_curvar company_curtype;

        /* Declare a record with same structure as cursor variable.  */
       company_rec company%ROWTYPE;

BEGIN
        /* Open the cursor variable, associating with it a SQL statement.  */
       OPEN company_curvar FOR SELECT * FROM company;

        /* Fetch from the cursor variable.  */
       FETCH company_cuvar INTO company_rec;

       /* Close the cursor object associated with variable.  */
      CLOSE company_curvar;

END;

Back to 29 April, 2000
 

cursor_for_loop_template.txt

DECLARE
        CURSOR  _CUR
/*
|| Author: John K. Joachim
||
|| Overview:
||
|| Major Modifications:
|| Date:
||
*/
is
 SELECT ......
 FROM
 WHERE
         _rec _cur%ROWTYPE;
BEGIN
        OPEN _cur;
        LOOP
 FETCH _cur INTO _rec;
 EXIT WHEN _cur%NOTFOUND;
 update_
         (  _rec._, _rec._);
 END LOOP;
 CLOSE _cur;
END;

Back to 30 April, 2000.

The following log was established to keep the notes I have from treading the following 2 books by Steven Feuerstrin:
 
             
           Oracle PL/SQL                     Oracle Built-in
             Programming                          Packages

The purpose of reading these two books is to prepare for the first of five exams in Oracle's OCP certification track.  It is my belief that studing these two books will not only prepare me for this exam, but will also help me retain what I learn (also a purpose of this build log).

Other books I have purchased which I suspect will also be helpful, and will be referenced occasionally in this log:

       
  Oracle 8i Internals          Oracle Design             Oracle SQL*Plus:
  for Waits, Latches,                                            The Definitive Guide
 Locks, and Memory