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;