Other Suggestions


  • 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.

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 00.00    con_discount_2000 CONSTANT NUMBER := .90;    -- discount rate for courses that cost between 01.00    -- and 00.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[s]SQL by Example
Oracle PL[s]SQL by Example
ISBN: 3642256902
EAN: N/A
Year: 2003
Pages: 289

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