Formatted Version of PL/SQL Code

This page contains some Badly Formatted PL/SQL code, and its PL/SQL Formatted version. The formatted output was produced by our PL/SQL Formatter.

Badly Formatted PL/SQL Code

This code is a sample taken from a PL/SQL open source code web site. To be fair, the original code was neatly formatted; we've uglified it as if many careless programmers had been working on it. The nesting structure is essentially impossible to read; in spite of this, it is still a completely legal program. Most programmers's will have to reformat it manually to figure out what is going on. There goes half an hour (or more, if you make a typo while reformatting it) of productivity.


	DECLARE part_no
 NUMBER(4); in_stock BOOLEAN; my_sal REAL(7,2); credit_limit CONSTANT NUMBER
 := 5000.00;  my_title books.title%TYPE;  dept_rec dept%ROWTYPE; -- declare record variableCURSOR c1 IS
  emp_rec c1%ROWTYPE
; -- declare record variable that represents
                      -- a row fetched from the emp table
  acct_balance 
NUMBER
(
11  ,  2         )    ;            acct            CONSTANT           NUMBER      (
        4
          )
            :=
               3
                 ;
              debit_amt
     CONSTANT NUMBER
   (5
,
2) := 500.00; BEGIN
	  tax := price * tax_rate; valid_id := FALSE;
     bonus := current_salary * 0.10;
		  wages := gross_pay(emp_id, st_hrs, ot_hrs) - deductions;
		  SELECT ename, sal,
 hiredate, job FROM emp;  SELECT salary * 0.10 INTO bonus FROM employees
 WHERE employee_id = emp_id;  SELECT AVG(sal) INTO my_sal FROM emp;  adjust_salary(7788, my_sal); -- assigns a new value to my_sal
  FOR someone IN (SELECT * FROM employees)  LOOP   DBMS_OUTPUT.PUT_LINE('First name = ' || someone.first_name);
      DBMS_OUTPUT.PUT_LINE('Last name = ' || someone.last_name);   END LOOP;
my_deptno := dept_rec.deptno;  FETCH c1 INTO emp_rec;  SELECT bal INTO acct_balance FROM accounts
   WHERE account_id = acct  FOR UPDATE OF bal;  IF acct_balance >= debit_amt THEN
   UPDATE accounts SET bal = bal - debit_amt WHERE account_id = acct;
ELSE
INSERT INTO temp VALUES   (acct, acct_balance, 'Insufficient funds');
        -- insert account, current balance, and message
  END IF; COMMIT;
  -- This CASE statement performs different actions based
  -- on a set of conditional tests.
  CASE  WHEN shape = 'square' THEN area := side * side;  WHEN shape = 'circle' THEN BEGIN
 area := pi * (radius * 
radius);  DBMS_OUTPUT.PUT_LINE('Value is not exact because pi is irrational.');
END;   WHEN shape = 'rectangle' THEN area := length * width;
  ELSE    BEGIN
DBMS_OUTPUT.PUT_LINE('No formula to calculate area of a' || shape);
                                   RAISE PROGRAM_ERROR;   END; END CASE;
FOR num IN 1..500 LOOP INSERT INTO roots  VALUES
 (num, SQRT(num)); END LOOP;
END; DECLARE
salary emp.sal%TYPE := 0; mgr_num emp.mgr%TYPE; last_name emp.ename%TYPE; starting_empno emp.empno%TYPE
 := 7499;
BEGIN
SELECT mgr INTO mgr_num FROM emp WHERE empno = starting_empno;
WHILE salary <= 2500 LOOP
 	 SELECT sal, mgr, ename INTO salary, mgr_num, last_name FROM emp WHERE empno = mgr_num;
									END LOOP;
			INSERT INTO temp VALUES (NULL, salary, last_name);
									  COMMIT;
										EXCEPTION
WHEN NO_DATA_FOUND THEN
INSERT INTO temp VALUES (NULL, NULL, 'Not found'); COMMIT;
			END; DECLARE
PROCEDURE award_bonus (emp_id NUMBER) IS
						    bonus REAL;
    comm_missing EXCEPTION; BEGIN -- executable part starts here
SELECT comm * 0.15 INTO bonus FROM emp WHERE empno = emp_id;
IF bonus IS NULL THEN RAISE comm_missing; ELSE UPDATE payroll SET pay = pay + bonus
 WHERE empno = emp_id; END IF;
EXCEPTION -- exception-handling part starts here
    WHEN comm_missing THEN COMMIT; END award_bonus; BEGIN
  bonus := 1.0; END;
CREATE PACKAGE emp_actions AS -- package specification
PROCEDURE hire_employee (empno NUMBER, ename CHAR);
			PROCEDURE fire_employee (emp_id NUMBER);
END emp_actions; CREATE PACKAGE BODY emp_actions AS -- package body
PROCEDURE hire_employee (empno NUMBER, ename CHAR) IS
BEGIN INSERT INTO emp VALUES (empno, ename);
END hire_employee; PROCEDURE fire_employee (emp_id
 NUMBER) IS BEGIN
DELETE FROM emp WHERE empno = emp_id;
END fire_employee; END emp_actions; DECLARE
TYPE Staff IS TABLE OF Employee;  staffer Employee;
  FUNCTION new_hires (hiredate DATE) RETURN Staff IS
BEGIN NULL;  END; BEGIN
  staffer := new_hires('10-NOV-98')
(5);END;					DECLARE
			  TYPE TimeRec
   IS
    RECORD (hours SMALLINT, minutes SMALLINT);
TYPE MeetingTyp IS RECORD (
date_held DATE, duration TimeRec, -- nested record
  location VARCHAR2(20), purpose VARCHAR2(50));
BEGIN NULL; END; CREATE TYPE Bank_Account AS
OBJECT ( acct_number INTEGER(5), balance REAL, status VARCHAR2(10),  MEMBER PROCEDURE open (amount IN REAL), MEMBER PROCEDURE verify_acct (num IN INTEGER),  MEMBER PROCEDURE close (num IN INTEGER, amount
OUT REAL),  MEMBER PROCEDURE deposit (num IN INTEGER, amount IN REAL),  MEMBER PROCEDURE withdraw (num IN INTEGER, amount IN REAL),  MEMBER FUNCTION curr_bal (num IN INTEGER) RETURN REAL );

DECLARE  comm_missing EXCEPTION; -- declare exception
BEGIN  IF commission IS NULL THEN RAISE comm_missing; -- raise exception
 END IF;  bonus := (salary
 * 0.10) + (commission * 0.15); EXCEPTION  WHEN comm_missing THEN
    NULL; END; DECLARE  n NUMBER; BEGIN
n := 10E127; -- causes a 'numeric overflow or underflow' error
			END; DECLARE
  x BINARY_FLOAT := sqrt(2.0f); -- Single-precision floating-point number
y BINARY_DOUBLE := sqrt(2.0d); -- Double-precision floating-point number
BEGIN  NULL; END; BEGIN
str := 'Hello, world!'; str := 'XYZ Corporation'; str
 := '10-NOV-91'; str := 'He said "Life is like licking honey from a thorn."'; str := '$1,000,000';
 str := 'I''m a string, you''re a string.';  -- q'!...!' notation lets us use single quotes inside the literal.
  string_var := q'!I'm a string, you're a string.!';
  -- To use delimiters [, {, <, and (, pair them with ], }, >, and ).
  -- Here we pass a string literal representing a SQL statement
  -- to a subprogram, without doubling the quotation marks around
  -- 'INVALID'.
  func_call(q'[select index_name from user_indexes where status = 'INVALID']');
  -- For NCHAR and NVARCHAR2 literals, use the prefix nq instead of q.
  where_clause := nq'#where col_value like '%e'#';
END;
DECLARE d1 DATE := DATE '1998-12-25'; t1 TIMESTAMP := TIMESTAMP '1997-10-22 13:01:01';
t2 TIMESTAMP WITH TIME ZONE := TIMESTAMP '1997-01-31 09:26:56.66 +02:00';
  -- Three years and two months
  -- (For greater precision, we would use the day-to-second interval)
i1 INTERVAL YEAR TO MONTH := INTERVAL '3-2' YEAR TO MONTH;
  -- Five days, four hours, three minutes, two and 1/100 seconds
 i2 INTERVAL DAY TO SECOND := INTERVAL '5 04:03:02.01' DAY TO SECOND;BEGIN  NULL;
END; DECLARE
some_condition BOOLEAN; pi NUMBER := 3.1415926; radius NUMBER := 15; area NUMBER;
BEGIN
  /* Perform some simple tests and assignments */
  IF 2 + 2 = 4 THEN  some_condition := TRUE; /* We expect this THEN to always be done */
  END IF;
  /*
  The following line computes the area of a
  circle using pi, which is the ratio between
  the circumference and diameter.
  */
  area := pi * radius**2; END; DECLARE credit_limit CONSTANT REAL := 5000.00;  max_days_in_year CONSTANT INTEGER := 366; urban_legend CONSTANT BOOLEAN := FALSE;
 hours_worked INTEGER
 DEFAULT 40; employee_count INTEGER := 0; acct_id INTEGER(4) NOT NULL := 9999;  credit NUMBER(7,2);
  debit credit%TYPE; name VARCHAR2(20) := 'JoHn SmItH';
  -- If we increase the length of NAME, the other variables
  -- become longer too.
upper_name name%TYPE := UPPER(name);lower_name name%TYPE := LOWER(name);init_name name%TYPE := INITCAP(name);
  the_trigger user_triggers.trigger_name%TYPE; my_empno employees.employee_id%TYPE;
 -- %ROWTYPE can include all the columns in a table...
  emp_rec employees%ROWTYPE;
-- ...or a subset of the columns, based on a cursor.
  CURSOR c1 IS
    SELECT department_id, department_name FROM departments;
  dept_rec c1%ROWTYPE;
  					-- Could even make a %ROWTYPE with columns from multiple tables.
  CURSOR c2 IS SELECT employee_id, email, employees.manager_id, location_id FROM employees, departments WHERE employees.department_id = departments.department_id; join_rec c2%ROWTYPE; BEGIN
  -- We know EMP_REC can hold a row from the EMPLOYEES table.
SELECT * INTO emp_rec FROM employees WHERE ROWNUM < 2;
  -- We can refer to the fields of EMP_REC using column names
  -- from the EMPLOYEES table.
				  IF emp_rec.department_id = 20 AND emp_rec.last_name = 'JOHNSON' THEN
 emp_rec.salary := emp_rec.salary * 1.15;
  END IF;END;DECLARE
  dept_rec1 departments%ROWTYPE;  dept_rec2 departments%ROWTYPE; CURSOR c1 IS SELECT department_id, location_id FROM departments;
  dept_rec3 c1%ROWTYPE;
BEGIN
  dept_rec1 := dept_rec2; -- allowed
  -- dept_rec2 refers to a table, dept_rec3 refers to a cursor
  -- dept_rec2 := dept_rec3; -- not allowed
END;DECLARE
  dept_rec departments%ROWTYPE;
BEGIN
 SELECT
 * 
			INTO dept_rec FROM departments
    WHERE
 department_id = 30
 and ROWNUM < 2;
END;
BEGIN
-- We assign an alias (COMPLETE_NAME) to the expression value, because
  -- it has no column name.
FOR item IN  (  SELECT first_name || ' ' ||
                        last_name complete_name   FROM employees WHERE ROWNUM < 11   ) LOOP
    -- Now we can refer to the field in the record using this alias.
dbms_output.put_line('Employee name: ' || item.complete_name);  END LOOP;END;

-- CREATE TABLE employees2 AS SELECT last_name FROM employees;

<<MAIN>>
DECLARE last_name VARCHAR2(10) := 'King'; my_last_name
VARCHAR2(10) := 'King'; BEGIN
  -- Deletes everyone, because both LAST_NAMEs refer to the column
DELETE FROM employees2 WHERE last_name = last_name;  dbms_output.put_line('Deleted ' || SQL%ROWCOUNT || ' rows.');
ROLLBACK;
-- OK, column and variable have different names
		  DELETE FROM employees2 WHERE last_name = my_last_name; dbms_output.put_line('Deleted ' || SQL%ROWCOUNT || ' rows.');
	  ROLLBACK;
  -- OK, block name specifies that 2nd LAST_NAME is a variable
				  DELETE FROM employees2 WHERE last_name = main.last_name;
dbms_output.put_line('Deleted ' || SQL%ROWCOUNT || ' rows.');  ROLLBACK;END;
-- DROP TABLE employees2;
DECLARE  FUNCTION dept_name
 (department_id IN NUMBER)
RETURN departments.department_name%TYPE IS  department_name departments.department_name%TYPE;
  BEGIN
    -- DEPT_NAME.DEPARTMENT_NAME specifies the local variable
 -- instead of the table column
SELECT department_name INTO dept_name.department_name  FROM departments   WHERE department_id
 = dept_name.department_id;  RETURN department_name;  END; BEGIN
FOR item IN (SELECT department_id FROM departments)
				  LOOP  dbms_output.put_line('Department: ' ||
 dept_name(item.department_id)); END LOOP;END;DECLARE  a CHAR;  b REAL;BEGIN
  -- identifiers available here: a (CHAR), b
 DECLARE    a INTEGER;    c REAL;  BEGIN
    -- identifiers available here: a (INTEGER), b, c
			NULL;  END;  DECLARE d REAL;
BEGIN
-- identifiers available here: a (CHAR), b, d
    NULL;  END;
					  -- identifiers available here: a (CHAR), b
END; <<outer>> DECLARE   birthdate DATE; BEGIN   DECLARE    birthdate DATE;  BEGIN IF birthdate = outer.birthdate THEN
      NULL;    END IF; END;END;PROCEDURE check_credit (xxx REAL) IS rating NUMBER;  FUNCTION valid (
xxx REAL) RETURN BOOLEAN
 IS    rating NUMBER; BEGIN IF check_credit.rating < 3 THEN  NULL;  END IF;
					  END;
		BEGIN
				  NULL;
							END;

DECLARE
  counter INTEGER;
BEGIN
  -- COUNTER is initially NULL, so 'COUNTER + 1' is also null.
		  counter := counter + 1; IF counter IS NULL THEN  dbms_output.put_line('Sure enough, COUNTER is NULL not 1.'); END IF;
END; DECLARE   done BOOLEAN; -- DONE is initially NULL
counter NUMBER := 0;BEGIN  done := FALSE; -- Assign a literal value
WHILE done != TRUE -- Compare to a literal value
  LOOP    counter := counter + 1;    done := (counter > 500); -- If counter > 500, DONE = TRUE
END LOOP; END;
			DECLARE
					  emp_id employees.employee_id%TYPE := 100;
			  emp_name employees.last_name%TYPE;
		  wages NUMBER(7,2);
			BEGIN
				  SELECT last_name, salary + (salary * nvl(commission_pct,0))  INTO emp_name
, wages FROM employees  WHERE employee_id = emp_id; dbms_output.put_line('Employee ' || emp_name || ' might make ' || wages);
END; DECLARE
  on_hand INTEGER := 0;  on_order INTEGER :=
 100;BEGIN
  -- Does not cause divide-by-zero error; evaluation stops after 1st expr.
IF (on_hand = 0) OR ((on_order / on_hand) < 5) THEN  dbms_output
.put_line('There are no more widgets left!');  END
			IF; END;
DECLARE  PROCEDURE assert
(assertion VARCHAR2, truth BOOLEAN) IS BEGIN   IF truth IS
 NULL THEN   dbms_output.put_line('Assertion ' || assertion || ' is unknown (NULL)');
    ELSIF truth = TRUE THEN    dbms_output.put_line(
'Assertion ' || assertion || ' is TRUE');  ELSE    dbms_output.put_line('Assertion ' || assertion || ' is FALSE');
    END IF;  END; BEGIN  assert('2 + 2 = 4', 2 + 2 = 4);  assert('10 > 1', 10 > 1);  assert('10 <= 1', 10 <= 1);
 assert('5 BETWEEN 1 AND 10', 5 BETWEEN 1 AND 10);
			  assert('NULL != 0', NULL != 0);
		  assert('3 IN (1,3,5)',
 3 IN (1,3,5));  assert('''A''
 < ''Z''', 'A' < 'Z');  assert('''baseball'' LIKE ''%all%''', 'baseball' LIKE '%all%');
  assert('''suit'' || ''case'' = ''suitcase''',
 'suit' || 'case' = 'suitcase'); END; DECLARE
  fraction BINARY_FLOAT := 1/3;
BEGIN  IF fraction = 11/33 THEN   dbms_output.put_line(
'Fractions are equal (luckily!)')
; 
 END
 IF; END;

							DECLARE
						  done BOOLEAN ;
						BEGIN
					  -- Each WHILE loop is equivalent
				  done := FALSE;
			  WHILE done = FALSE
		  LOOP
	    done := TRUE;
  END LOOP;
	  done := FALSE;
	  WHILE NOT (done = TRUE)
		  LOOP
			    done := TRUE;
				  END LOOP;
					  done := FALSE;
						  WHILE NOT done
			  LOOP
			  done := TRUE;
		          END LOOP;
			END;

DECLARE grade CHAR(1) := 'B'; appraisal VARCHAR2(20); BEGIN  appraisal :=
 CASE grade  WHEN 'A' THEN 'Excellent' WHEN 'B' THEN 'Very Good' WHEN 'C' THEN 'Good' WHEN 'D' THEN 'Fair'  WHEN 'F' THEN 'Poor'
    ELSE 'No such grade'  END;  dbms_output.put_line('Grade ' || grade || ' is ' || appraisal); END; DECLARE
grade CHAR(1) := 'B';
appraisal VARCHAR2(120);
id NUMBER := 8429862;
attendance NUMBER := 150;  min_days
 CONSTANT NUMBER := 200; FUNCTION attends_this_school(id NUMBER) RETURN
 BOOLEAN IS BEGIN RETURN TRUE; END;BEGIN appraisal :=  CASE   WHEN attends_this_school(id) = FALSE THEN 'N/A - Student not enrolled'
      -- Have to put this condition early to detect
      -- good students with bad attendance
WHEN grade = 'F' OR attendance < min_days THEN 'Poor (poor performance or bad attendance)'
      WHEN grade = 'A' THEN 'Excellent'
		      WHEN grade = 'B' THEN 'Very Good'
      WHEN
 grade = 'C' 
THEN 'Good'
      WHEN grade = 'D'
 THEN 'Fair'  ELSE 'No such grade'    END;  dbms_output.put_line('Result for student ' || id || ' is ' || appraisal); END;
DECLARE  x NUMBER := 5; y NUMBER := NULL; BEGIN IF x != y THEN -- yields NULL, not TRUE 
 dbms_output.put_line('x != y'); -- not executed
  ELSIF x = y THEN -- also yields NULL
  dbms_output.put_line('x = y'); ELSE					   dbms_output.put_line('Can''t tell if x and y are equal or not...');  END IF;END;
DECLARE a NUMBER := NULL; b NUMBER := NULL;BEGIN IF a = b THEN -- yields NULL, not TRUE 
 dbms_output.put_line('a = b'); -- not executed
  			ELSIF a != b THEN -- yields NULL, not TRUE 
 dbms_output.put_line('a != b'); -- not executed
  ELSE dbms_output.put_line('Can''t tell if two NULLs are equal'); END IF;END; DECLARE null_string VARCHAR2(80) := TO_CHAR('');
  address
 VARCHAR2
(80); zip_code VARCHAR2(80) := SUBSTR(address
, 25, 0); name VARCHAR2(80);  valid BOOLEAN :=
 (name != ''); BEGIN  NULL; END; DECLARE  the_manager VARCHAR2(40);  name employees.last_name%TYPE; BEGIN
  -- NULL is a valid argument to DECODE. In this case, manager_id is null
  -- and the DECODE function returns 'nobody'.
SELECT DECODE(manager_id, NULL, 'nobody', 'somebody'), last_name  INTO
 the_manager, name FROM
 employees WHERE employee_id = 100;  dbms_output.put_line(name || ' is managed by ' || the_manager); END;
DECLARE
  string_type VARCHAR2(60);
             old_string string_type%TYPE := 'Apples and oranges';
          my_string string_type%TYPE := 'more apples';
  -- NULL is a valid argument to REPLACE, but does not match
  -- anything so no replacement is done.
                      new_string string_type%TYPE := REPLACE(old_string, NULL, my_string);
                      BEGIN
                      dbms_output
                      .
                      put_line
                      ('Old string = ' || old_string);
                      dbms_output.put_line('New string = ' || new_string);
                      END;
DECLARE string_type VARCHAR2(60); dashed string_type%TYPE := 'Gold-i-locks';
  -- When the substitution text for REPLACE is NULL,
  -- the text being replaced is deleted.
  name string_type%TYPE := REPLACE(dashed, '-', NULL); BEGIN  dbms_output.put_line('Dashed name = ' || dashed);  dbms_output.put_line('Dashes removed = ' || name); END;

PL/SQL Formatted Version

This is the result of using SD's PLSQL Formatter tool on the sample badly formatted PL/SQL, using just the default settings. You can see that the formatter has chosen very different line breaks, based on the language structure. The block structure is now clearly visible. Notice the commented out code; the formatter has left it alone, but aligned the comments, so even it is more readable. A programmer might actually be able to work on this version.


DECLARE
  part_no NUMBER(4);
  in_stock BOOLEAN;
  my_sal REAL(7, 2);
  credit_limit CONSTANT NUMBER := 5000.00;
  my_title books.title%TYPE;
  dept_rec dept%ROWTYPE; -- declare record variableCURSOR c1 IS
  emp_rec c1%ROWTYPE; -- declare record variable that represents
  -- a row fetched from the emp table
  acct_balance NUMBER(11, 2);
  acct CONSTANT NUMBER(4) := 3;
  debit_amt CONSTANT NUMBER(5, 2) := 500.00;
BEGIN
  tax := price * tax_rate;
  valid_id := FALSE;
  bonus := current_salary * 0.10;
  wages := gross_pay(emp_id, st_hrs, ot_hrs) - deductions;
  SELECT ename, sal, hiredate, job FROM emp;
  SELECT salary * 0.10 INTO bonus FROM employees
    WHERE employee_id = emp_id;
  SELECT AVG(sal) INTO my_sal FROM emp;
  adjust_salary(7788, my_sal); -- assigns a new value to my_sal
  FOR someone IN
    (SELECT * FROM employees)
  LOOP
    DBMS_OUTPUT.PUT_LINE('First name = ' || someone.first_name);
    DBMS_OUTPUT.PUT_LINE('Last name = ' || someone.last_name);
  END LOOP;
  my_deptno := dept_rec.deptno;
  FETCH c1 INTO emp_rec;
  SELECT bal INTO acct_balance FROM accounts
    WHERE account_id = acct
    FOR UPDATE OF bal;
  IF acct_balance >= debit_amt THEN
    UPDATE accounts
      SET bal = bal - debit_amt
      WHERE account_id = acct;
  ELSE
    INSERT INTO temp
      VALUES (acct, acct_balance, 'Insufficient funds');
  -- insert account, current balance, and message
  END IF;
  COMMIT;
  -- This CASE statement performs different actions based
  -- on a set of conditional tests.
  CASE
    WHEN shape = 'square' THEN
      area := side * side;
    WHEN shape = 'circle' THEN
      BEGIN
        area := pi * (radius * radius);
        DBMS_OUTPUT.PUT_LINE('Value is not exact because pi is irrational.');
      END;
    WHEN shape = 'rectangle' THEN
      area := LENGTH * width;
  ELSE
    BEGIN
      DBMS_OUTPUT.PUT_LINE('No formula to calculate area of a' || shape);
      RAISE PROGRAM_ERROR;
    END;
  END CASE;
  FOR num IN 1 .. 500 LOOP
    INSERT INTO roots
      VALUES (num, SQRT(num));
  END LOOP;
END;

DECLARE
  salary emp.sal%TYPE := 0;
  mgr_num emp.mgr%TYPE;
  last_name emp.ename%TYPE;
  starting_empno emp.empno%TYPE := 7499;
BEGIN
  SELECT mgr INTO mgr_num FROM emp
    WHERE empno = starting_empno;
  WHILE salary <= 2500 LOOP
    SELECT sal, mgr, ename INTO salary, mgr_num, last_name FROM emp
      WHERE empno = mgr_num;
  END LOOP;
  INSERT INTO temp
    VALUES (NULL, salary, last_name);
  COMMIT;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    INSERT INTO temp
      VALUES (NULL, NULL, 'Not found');
    COMMIT;
END;

DECLARE
  PROCEDURE award_bonus(emp_id NUMBER) IS
    bonus REAL;
    comm_missing EXCEPTION;
  BEGIN -- executable part starts here
    SELECT comm * 0.15 INTO bonus FROM emp
      WHERE empno = emp_id;
    IF bonus IS NULL THEN
      RAISE comm_missing;
    ELSE
      UPDATE payroll
        SET pay = pay + bonus
        WHERE empno = emp_id;
    END IF;
  EXCEPTION -- exception-handling part starts here
    WHEN comm_missing THEN
      COMMIT;
  END award_bonus;
BEGIN
  bonus := 1.0;
END;

CREATE PACKAGE emp_actions AS -- package specification
  PROCEDURE hire_employee(empno NUMBER,
                          ename CHAR);
  PROCEDURE fire_employee(emp_id NUMBER);
END emp_actions;

CREATE PACKAGE BODY emp_actions AS -- package body
  PROCEDURE hire_employee(empno NUMBER,
                          ename CHAR) IS
  BEGIN
    INSERT INTO emp
      VALUES (empno, ename);
  END hire_employee;
  PROCEDURE fire_employee(emp_id NUMBER) IS
  BEGIN
    DELETE FROM emp
      WHERE empno = emp_id;
  END fire_employee;
END emp_actions;

DECLARE
  TYPE Staff IS TABLE OF Employee;
  staffer Employee;
  FUNCTION new_hires(hiredate DATE)
                    RETURN Staff IS
  BEGIN
    NULL;
  END;
BEGIN
  staffer := new_hires('10-NOV-98') (5);
END;

DECLARE
  TYPE TimeRec IS
    RECORD (hours SMALLINT,
            minutes SMALLINT);
  TYPE MeetingTyp IS
    RECORD (date_held DATE,
            duration TimeRec, -- nested record
            location VARCHAR2(20),
            purpose VARCHAR2(50));
BEGIN
  NULL;
END;

CREATE TYPE Bank_Account AS
  OBJECT (acct_number INTEGER(5),
          balance REAL,
          status VARCHAR2(10),
          MEMBER PROCEDURE OPEN (amount IN REAL),
          MEMBER PROCEDURE verify_acct(num IN INTEGER),
          MEMBER PROCEDURE CLOSE (num IN INTEGER,
                                  amount OUT REAL),
          MEMBER PROCEDURE deposit(num IN INTEGER,
                                   amount IN REAL),
          MEMBER PROCEDURE withdraw(num IN INTEGER,
                                    amount IN REAL),
          MEMBER FUNCTION curr_bal(num IN INTEGER)
                                  RETURN REAL);

DECLARE
  comm_missing EXCEPTION; -- declare exception
BEGIN
  IF commission IS NULL THEN
    RAISE comm_missing; -- raise exception
  END IF;
  bonus := (salary * 0.10) + (commission * 0.15);
EXCEPTION
  WHEN comm_missing THEN
    NULL;
END;

DECLARE
  n NUMBER;
BEGIN
  n := 10E127; -- causes a 'numeric overflow or underflow' error
END;

DECLARE
  x BINARY_FLOAT := sqrt(2.0); -- Single-precision floating-point number
  y BINARY_DOUBLE := sqrt(2.0); -- Double-precision floating-point number
BEGIN
  NULL;
END;

BEGIN
  str := 'Hello, world!';
  str := 'XYZ Corporation';
  str := '10-NOV-91';
  str := 'He said "Life is like licking honey from a thorn."';
  str := '$1,000,000';
  str := q'{I'm a string, you're a string.}'; -- q'!...!' notation lets us use single quotes inside the literal.
  string_var := q'{I'm a string, you're a string.}';
  -- To use delimiters [, {, <, and (, pair them with ], }, >, and ).
  -- Here we pass a string literal representing a SQL statement
  -- to a subprogram, without doubling the quotation marks around
  -- 'INVALID'.
  func_call(q'{select index_name from user_indexes where status = 'INVALID'}');
  -- For NCHAR and NVARCHAR2 literals, use the prefix nq instead of q.
  where_clause := q'{where col_value like '%e'}';
END;

DECLARE
  d1 DATE := DATE '1998-12-25';
  t1 TIMESTAMP := TIMESTAMP '1997-10-22 13:01:01';
  t2 TIMESTAMP WITH TIME ZONE := TIMESTAMP '1997-01-31 09:26:56.66 +02:00';
  -- Three years and two months
  -- (For greater precision, we would use the day-to-second interval)
  i1 INTERVAL YEAR TO MONTH := INTERVAL '3-2' YEAR TO MONTH;
  -- Five days, four hours, three minutes, two and 1/100 seconds
  i2 INTERVAL DAY TO SECOND := INTERVAL '5 04:03:02.01' DAY TO SECOND;
BEGIN
  NULL;
END;

DECLARE
  some_condition BOOLEAN;
  pi NUMBER := 3.1415926;
  radius NUMBER := 15;
  area NUMBER;
BEGIN
  /* Perform some simple tests and assignments */
  IF 2 + 2 = 4 THEN
    some_condition := TRUE; /* We expect this THEN to always be done */
  END IF;
  /*
  The following line computes the area of a
  circle using pi, which is the ratio between
  the circumference and diameter.
  */
  area := pi * radius ** 2;
END;

DECLARE
  credit_limit CONSTANT REAL := 5000.00;
  max_days_in_year CONSTANT INTEGER := 366;
  urban_legend CONSTANT BOOLEAN := FALSE;
  hours_worked INTEGER DEFAULT 40;
  employee_count INTEGER := 0;
  acct_id INTEGER(4) NOT NULL := 9999;
  credit NUMBER(7, 2);
  debit credit%TYPE;
  NAME VARCHAR2(20) := 'JoHn SmItH';
  -- If we increase the length of NAME, the other variables
  -- become longer too.
  upper_name NAME%TYPE := UPPER(NAME);
  lower_name NAME%TYPE := LOWER(NAME);
  init_name NAME%TYPE := INITCAP(NAME);
  the_trigger user_triggers.trigger_name%TYPE;
  my_empno employees.employee_id%TYPE;
  -- %ROWTYPE can include all the columns in a table...
  emp_rec employees%ROWTYPE;
  -- ...or a subset of the columns, based on a cursor.
  CURSOR c1 IS
    SELECT department_id, department_name FROM departments;
  dept_rec c1%ROWTYPE;
  -- Could even make a %ROWTYPE with columns from multiple tables.
  CURSOR c2 IS
    SELECT employee_id, email, employees.manager_id, location_id FROM employees, departments
      WHERE employees.department_id = departments.department_id;
  join_rec c2%ROWTYPE;
BEGIN
  -- We know EMP_REC can hold a row from the EMPLOYEES table.
  SELECT * INTO emp_rec FROM employees
    WHERE ROWNUM < 2;
  -- We can refer to the fields of EMP_REC using column names
  -- from the EMPLOYEES table.
  IF emp_rec.department_id = 20 AND emp_rec.last_name = 'JOHNSON' THEN
    emp_rec.salary := emp_rec.salary * 1.15;
  END IF;
END;

DECLARE
  dept_rec1 departments%ROWTYPE;
  dept_rec2 departments%ROWTYPE;
  CURSOR c1 IS
    SELECT department_id, location_id FROM departments;
  dept_rec3 c1%ROWTYPE;
BEGIN
  dept_rec1 := dept_rec2; -- allowed
-- dept_rec2 refers to a table, dept_rec3 refers to a cursor
-- dept_rec2 := dept_rec3; -- not allowed
END;

DECLARE
  dept_rec departments%ROWTYPE;
BEGIN
  SELECT * INTO dept_rec FROM departments
    WHERE department_id = 30 AND ROWNUM < 2;
END;

BEGIN
  -- We assign an alias (COMPLETE_NAME) to the expression value, because
  -- it has no column name.
  FOR item IN
    (SELECT first_name || ' ' || last_name complete_name FROM employees
       WHERE ROWNUM < 11)
  LOOP
    -- Now we can refer to the field in the record using this alias.
    dbms_output.put_line('Employee name: ' || item.complete_name);
  END LOOP;
END;

-- CREATE TABLE employees2 AS SELECT last_name FROM employees;
<< MAIN >>
DECLARE
  last_name VARCHAR2(10) := 'King';
  my_last_name VARCHAR2(10) := 'King';
BEGIN
  -- Deletes everyone, because both LAST_NAMEs refer to the column
  DELETE FROM employees2
    WHERE last_name = last_name;
  dbms_output.put_line('Deleted ' || SQL%ROWCOUNT || ' rows.');
  ROLLBACK;
  -- OK, column and variable have different names
  DELETE FROM employees2
    WHERE last_name = my_last_name;
  dbms_output.put_line('Deleted ' || SQL%ROWCOUNT || ' rows.');
  ROLLBACK;
  -- OK, block name specifies that 2nd LAST_NAME is a variable
  DELETE FROM employees2
    WHERE last_name = main.last_name;
  dbms_output.put_line('Deleted ' || SQL%ROWCOUNT || ' rows.');
  ROLLBACK;
END;

-- DROP TABLE employees2;
DECLARE
  FUNCTION dept_name(department_id IN NUMBER)
                    RETURN departments.department_name%TYPE IS
    department_name departments.department_name%TYPE;
  BEGIN
    -- DEPT_NAME.DEPARTMENT_NAME specifies the local variable
    -- instead of the table column
    SELECT department_name INTO dept_name.department_name FROM departments
      WHERE department_id = dept_name.department_id;
    RETURN department_name;
  END;
BEGIN
  FOR item IN
    (SELECT department_id FROM departments)
  LOOP
    dbms_output.put_line('Department: ' || dept_name(item.department_id));
  END LOOP;
END;

DECLARE
  a CHAR;
  b REAL;
BEGIN
  -- identifiers available here: a (CHAR), b
  DECLARE
    a INTEGER;
    c REAL;
  BEGIN
    -- identifiers available here: a (INTEGER), b, c
    NULL;
  END;
  DECLARE
    d REAL;
  BEGIN
    -- identifiers available here: a (CHAR), b, d
    NULL;
  END;
-- identifiers available here: a (CHAR), b
END;

<< outer >>
DECLARE
  birthdate DATE;
BEGIN
  DECLARE
    birthdate DATE;
  BEGIN
    IF birthdate = outer.birthdate THEN
      NULL;
    END IF;
  END;
END;

PROCEDURE check_credit(xxx REAL) IS
  rating NUMBER;
  FUNCTION valid(xxx REAL)
                RETURN BOOLEAN IS
    rating NUMBER;
  BEGIN
    IF check_credit.rating < 3 THEN
      NULL;
    END IF;
  END;
BEGIN
  NULL;
END;

DECLARE
  counter INTEGER;
BEGIN
  -- COUNTER is initially NULL, so 'COUNTER + 1' is also null.
  counter := counter + 1;
  IF counter IS NULL THEN
    dbms_output.put_line('Sure enough, COUNTER is NULL not 1.');
  END IF;
END;

DECLARE
  done BOOLEAN; -- DONE is initially NULL
  counter NUMBER := 0;
BEGIN
  done := FALSE; -- Assign a literal value
  WHILE done != TRUE -- Compare to a literal value
  LOOP
    counter := counter + 1;
    done := (counter > 500); -- If counter > 500, DONE = TRUE
  END LOOP;
END;

DECLARE
  emp_id employees.employee_id%TYPE := 100;
  emp_name employees.last_name%TYPE;
  wages NUMBER(7, 2);
BEGIN
  SELECT last_name, salary + (salary * nvl(commission_pct, 0)) INTO emp_name, wages FROM employees
    WHERE employee_id = emp_id;
  dbms_output.put_line('Employee ' || emp_name || ' might make ' || wages);
END;

DECLARE
  on_hand INTEGER := 0;
  on_order INTEGER := 100;
BEGIN
  -- Does not cause divide-by-zero error; evaluation stops after 1st expr.
  IF (on_hand = 0) OR ( (on_order / on_hand) < 5) THEN
    dbms_output.put_line('There are no more widgets left!');
  END IF;
END;

DECLARE
  PROCEDURE assert(assertion VARCHAR2,
                   truth BOOLEAN) IS
  BEGIN
    IF truth IS NULL THEN
      dbms_output.put_line('Assertion ' || assertion || ' is unknown (NULL)');
    ELSIF truth = TRUE THEN
      dbms_output.put_line('Assertion ' || assertion || ' is TRUE');
    ELSE
      dbms_output.put_line('Assertion ' || assertion || ' is FALSE');
    END IF;
  END;
BEGIN
  assert('2 + 2 = 4', 2 + 2 = 4);
  assert('10 > 1', 10 > 1);
  assert('10 <= 1', 10 <= 1);
  assert('5 BETWEEN 1 AND 10', 5 BETWEEN 1 AND 10);
  assert('NULL != 0', NULL != 0);
  assert('3 IN (1,3,5)', 3 IN (1, 3, 5));
  assert(q'{'A'\r\n < 'Z'}', 'A' < 'Z');
  assert(q'{'baseball' LIKE '%all%'}', 'baseball' LIKE '%all%');
  assert(q'{'suit' || 'case' = 'suitcase'}', 'suit' || 'case' = 'suitcase');
END;

DECLARE
  fraction BINARY_FLOAT := 1 / 3;
BEGIN
  IF fraction = 11 / 33 THEN
    dbms_output.put_line('Fractions are equal (luckily!)');
  END IF;
END;

DECLARE
  done BOOLEAN;
BEGIN
  -- Each WHILE loop is equivalent
  done := FALSE;
  WHILE done = FALSE LOOP
    done := TRUE;
  END LOOP;
  done := FALSE;
  WHILE NOT (done = TRUE) LOOP
    done := TRUE;
  END LOOP;
  done := FALSE;
  WHILE NOT done LOOP
    done := TRUE;
  END LOOP;
END;

DECLARE
  grade CHAR(1) := 'B';
  appraisal VARCHAR2(20);
BEGIN
  appraisal := CASE grade
                 WHEN 'A' THEN 'Excellent'
                 WHEN 'B' THEN 'Very Good'
                 WHEN 'C' THEN 'Good'
                 WHEN 'D' THEN 'Fair'
                 WHEN 'F' THEN 'Poor'
               ELSE 'No such grade'
               END;
  dbms_output.put_line('Grade ' || grade || ' is ' || appraisal);
END;

DECLARE
  grade CHAR(1) := 'B';
  appraisal VARCHAR2(120);
  id NUMBER := 8429862;
  attendance NUMBER := 150;
  min_days CONSTANT NUMBER := 200;
  FUNCTION attends_this_school(id NUMBER)
                              RETURN BOOLEAN IS
  BEGIN
    RETURN TRUE;
  END;
BEGIN
  appraisal := CASE
                 WHEN attends_this_school(id) = FALSE THEN 'N/A - Student not enrolled'
                 -- Have to put this condition early to detect
                 -- good students with bad attendance
                 WHEN grade = 'F' OR attendance < min_days THEN 'Poor (poor performance or bad attendance)'
                 WHEN grade = 'A' THEN 'Excellent'
                 WHEN grade = 'B' THEN 'Very Good'
                 WHEN grade = 'C' THEN 'Good'
                 WHEN grade = 'D' THEN 'Fair'
               ELSE 'No such grade'
               END;
  dbms_output.put_line('Result for student ' || id || ' is ' || appraisal);
END;

DECLARE
  x NUMBER := 5;
  y NUMBER := NULL;
BEGIN
  IF x != y THEN -- yields NULL, not TRUE 
    dbms_output.put_line('x != y'); -- not executed
  ELSIF x = y THEN -- also yields NULL
    dbms_output.put_line('x = y');
  ELSE
    dbms_output.put_line(q'{Can't tell if x and y are equal or not...}');
  END IF;
END;

DECLARE
  a NUMBER := NULL;
  b NUMBER := NULL;
BEGIN
  IF a = b THEN -- yields NULL, not TRUE 
    dbms_output.put_line('a = b'); -- not executed
  ELSIF a != b THEN -- yields NULL, not TRUE 
    dbms_output.put_line('a != b'); -- not executed
  ELSE
    dbms_output.put_line(q'{Can't tell if two NULLs are equal}');
  END IF;
END;

DECLARE
  null_string VARCHAR2(80) := TO_CHAR('');
  address VARCHAR2(80);
  zip_code VARCHAR2(80) := SUBSTR(address, 25, 0);
  NAME VARCHAR2(80);
  valid BOOLEAN := (NAME != '');
BEGIN
  NULL;
END;

DECLARE
  the_manager VARCHAR2(40);
  NAME employees.last_name%TYPE;
BEGIN
  -- NULL is a valid argument to DECODE. In this case, manager_id is null
  -- and the DECODE function returns 'nobody'.
  SELECT DECODE (manager_id, NULL, 'nobody', 'somebody'), last_name INTO the_manager, NAME FROM employees
    WHERE employee_id = 100;
  dbms_output.put_line(NAME || ' is managed by ' || the_manager);
END;

DECLARE
  string_type VARCHAR2(60);
  old_string string_type%TYPE := 'Apples and oranges';
  my_string string_type%TYPE := 'more apples';
  -- NULL is a valid argument to REPLACE, but does not match
  -- anything so no replacement is done.
  new_string string_type%TYPE := REPLACE (old_string, NULL, my_string);
BEGIN
  dbms_output.put_line('Old string = ' || old_string);
  dbms_output.put_line('New string = ' || new_string);
END;

DECLARE
  string_type VARCHAR2(60);
  dashed string_type%TYPE := 'Gold-i-locks';
  -- When the substitution text for REPLACE is NULL,
  -- the text being replaced is deleted.
  NAME string_type%TYPE := REPLACE (dashed, '-', NULL);
BEGIN
  dbms_output.put_line('Dashed name = ' || dashed);
  dbms_output.put_line('Dashes removed = ' || NAME);
END;
For more information: info@semanticdesigns.com    Follow us at Twitter: @SemanticDesigns

PL/SQL Source Code
Formatter Example