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.
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; /
