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;
