Other Suggestions


Oracle® PL/SQL® Interactive Workbook, Second Edition
By Benjamin Rosenzweig, Elena Silvestrova
Table of Contents
Appendix B.  PL/SQL Formatting Guide

  • For SQL statements embedded in PL/SQL, use the same formatting guidelines to determine how the statements should appear in a block.

  • Provide a comment header that explains the intent of the block, lists the creation date and author's name, and have a line for each revision with the author's name, date, and the description of the revision.

graphics/intfig03.gif FOR EXAMPLE

The following example shows the aforementioned suggestions. Notice that it also uses a monospaced font (Courier) that makes the formatting easier. Proportional spaced fonts can hide spaces and make lining up clauses difficult. Most text and programming editors by default use a monospace font.

 REM ********************************************************  REM * filename: coursediscount01.sql              version: 1  REM * purpose:  To give discounts to courses that have at  REM *           least one section with an enrollment of more  REM *           than 10 students.  REM * args:     none  REM *  REM * created by:  s.tashi            date: January 1, 2000  REM * modified by: y.sonam            date: February 1, 2000  REM * description: Fixed cursor, added indentation and  REM *              comments.  REM ********************************************************  DECLARE     -- C_DISCOUNT_COURSE finds a list of courses that have     -- at least one section with an enrollment of at least 10     -- students.     CURSOR c_discount_course IS        SELECT DISTINCT course_no          FROM section sect         WHERE 10 <= (SELECT COUNT(*)                        FROM enrollment enr                       WHERE enr.section_id = sect.section_id                      );     -- discount rate for courses that cost more than $2000.00     con_discount_2000 CONSTANT NUMBER := .90;     -- discount rate for courses that cost between $1001.00     -- and $2000.00     con_discount_other CONSTANT NUMBER := .95;     v_current_course_cost course.cost%TYPE;     v_discount_all NUMBER;     e_update_is_problematic EXCEPTION;  BEGIN     -- For courses to be discounted, determine the current     -- and new cost values     FOR r_discount_course in c_discount_course LOOP        SELECT cost          INTO v_current_course_cost          FROM course         WHERE course_no = r_discount_course.course_no;        IF v_current_course_cost > 2000 THEN           v_discount_all := con_discount_2000;        ELSE           IF v_current_course_cost > 1000 THEN              v_discount_all :=  con_discount_other;           ELSE              v_discount_all := 1;           END IF;        END IF;        BEGIN           UPDATE course              SET cost = cost * v_discount_all            WHERE course_no = r_discount_course.course_no;        EXCEPTION           WHEN OTHERS THEN              RAISE e_update_is_problematic;        END;   -- end of sub-block to update record     END LOOP; -- end of main LOOP     COMMIT;  EXCEPTION     WHEN e_update_is_problematic THEN        -- Undo all transactions in this run of the program        ROLLBACK;        DBMS_OUTPUT.PUT_LINE           ('There was a problem updating a course cost.');     WHEN OTHERS THEN        NULL;  END;  / 


    Oracle PL. SQL Interactive Workbook
    Oracle PL/SQL Interactive Workbook (2nd Edition)
    ISBN: 0130473200
    EAN: 2147483647
    Year: 2002
    Pages: 146

    Similar book on Amazon

    flylib.com © 2008-2017.
    If you may any questions please contact us: flylib@qtcs.net