======
PROGRAM WITH IN PARAMETER
-- including OR REPLACE is more convenient when updating a subprogram
-- IN is the default for parameter declarations so it could be omitted
CREATE OR REPLACE PROCEDURE award_bonus (emp_id IN NUMBER, bonus_rate IN NUMBER)
AS
-- declare variables to hold values from table columns, use %TYPE attribute
emp_comm employees.commission_pct%TYPE;
emp_sal employees.salary%TYPE;
-- declare an exception to catch when the salary is NULL
salary_missing EXCEPTION;
BEGIN -- executable part starts here
-- select the column values into the local variables
SELECT salary, commission_pct INTO emp_sal, emp_comm FROM employees
WHERE employee_id = emp_id;
-- check whether the salary for the employee is null, if so, raise an exception
IF emp_sal IS NULL THEN
RAISE salary_missing;
ELSE
IF emp_comm IS NULL THEN
-- if this is not a commissioned employee, increase the salary by the bonus rate
-- for this example, do not make the actual update to the salary
-- UPDATE employees SET salary = salary + salary * bonus_rate
-- WHERE employee_id = emp_id;
DBMS_OUTPUT.PUT_LINE('Employee ' || emp_id || ' receives a bonus: '
|| TO_CHAR(emp_sal * bonus_rate) );
ELSE
DBMS_OUTPUT.PUT_LINE('Employee ' || emp_id
|| ' receives a commission. No bonus allowed.');
END IF;
END IF;
EXCEPTION -- exception-handling part starts here
WHEN salary_missing THEN
DBMS_OUTPUT.PUT_LINE('Employee ' || emp_id ||
' does not have a value for salary. No update.');
WHEN OTHERS THEN
NULL; -- for other exceptions do nothing
END award_bonus;
/
-- the following BEGIN..END block calls, or executes, the award_bonus procedure
-- using employee IDs 123 and 179 with the bonus rate 0.05 (5%)
BEGIN
award_bonus(123, 0.05);
award_bonus(179, 0.05);
END;
/
=================
Example 4-1 Using a Simple PL/SQL Block
-- the following is an optional declarative part
DECLARE
monthly_salary NUMBER(6);
number_of_days_worked NUMBER(2);
pay_per_day NUMBER(6,2);
-- the following is the executable part, from BEGIN to END
BEGIN
monthly_salary := 2290;
number_of_days_worked := 21;
pay_per_day := monthly_salary/number_of_days_worked;
-- the following displays output from the PL/SQL block
DBMS_OUTPUT.PUT_LINE('The pay per day is ' || TO_CHAR(pay_per_day));
-- the following is an optional exception part that handles errors
EXCEPTION
WHEN ZERO_DIVIDE THEN
pay_per_day := 0;
END;
/
=================
Example 4-2 Using DBMS_OUTPUT.PUT_LINE to Display PL/SQL Output
-- enable SERVEROUTPUT in SQL Command Line (SQL*Plus) to display output with
-- DBMS_OUTPUT.PUT_LINE, this enables SERVEROUTPUT for this SQL*Plus session only
SET SERVEROUTPUT ON
DECLARE
answer VARCHAR2(20); -- declare a variable
BEGIN
-- assign a value to a variable
answer := 'Maybe';
-- use PUT_LINE to display data from the PL/SQL block
DBMS_OUTPUT.PUT_LINE( 'The answer is: ' || answer );
END;
/
============
Example 4-3 Using Comments in PL/SQL
DECLARE -- Declare variables here.
monthly_salary NUMBER(6); -- This is the monthly salary.
number_of_days_worked NUMBER(2); -- This is the days in one month.
pay_per_day NUMBER(6,2); -- Calculate this value.
BEGIN
-- First assign values to the variables.
monthly_salary := 2290;
number_of_days_worked := 21;
-- Now calculate the value on the following line.
pay_per_day := monthly_salary/number_of_days_worked;
-- the following displays output from the PL/SQL block
DBMS_OUTPUT.PUT_LINE('The pay per day is ' || TO_CHAR(pay_per_day));
EXCEPTION
/* This is a simple example of an exeception handler to trap division by zero.
In actual practice, it would be best to check whether a variable is
zero before using it as a divisor. */
WHEN ZERO_DIVIDE THEN
pay_per_day := 0; -- set to 0 if divisor equals 0
END;
/
=============
Example 4-4 Declaring Variables in PL/SQL
DECLARE -- declare the variables in this section
last_name VARCHAR2(30);
first_name VARCHAR2(25);
employee_id NUMBER(6);
active_employee BOOLEAN;
monthly_salary NUMBER(6);
number_of_days_worked NUMBER(2);
pay_per_day NUMBER(6,2);
avg_days_worked_month CONSTANT NUMBER(2) := 21; -- a constant variable
BEGIN
NULL; -- NULL statement does nothing, allows this block to executed and tested
END;
/
================
Example 4-5 Using Identifiers for Variables in PL/SQL
DECLARE
lastname VARCHAR2(30); -- valid identifier
last_name VARCHAR2(30); -- valid identifier, _ allowed
last$name VARCHAR2(30); -- valid identifier, $ allowed
last#name VARCHAR2(30); -- valid identifier, # allowed
-- last-name is invalid, hypen not allowed
-- last/name is invalid, slash not allowed
-- last name is invalid, space not allowed
-- LASTNAME is invalid, same as lastname and LastName
-- LastName is invalid, same as lastname and LASTNAME
BEGIN
NULL; -- NULL statement does nothing, allows this block to executed and tested
END;
/
===============
Example 4-6 Assigning Values to Variables With the PL/SQL Assignment Operator
DECLARE -- declare and assiging variables
wages NUMBER(6,2);
hours_worked NUMBER := 40;
hourly_salary NUMBER := 22.50;
bonus NUMBER := 150;
country VARCHAR2(128);
counter NUMBER := 0;
done BOOLEAN := FALSE;
valid_id BOOLEAN;
BEGIN
wages := (hours_worked * hourly_salary) + bonus; -- compute wages
country := 'France'; -- assign a string literal
country := UPPER('Canada'); -- assign an uppercase string literal
done := (counter > 100); -- assign a BOOLEAN, in this case FALSE
valid_id := TRUE; -- assign a BOOLEAN
END;
/
=================
Example 4-7 Using Numeric Literals in PL/SQL
DECLARE -- declare and assign variables
number1 PLS_INTEGER := 32000; -- numeric literal
number2 NUMBER(8,3);
BEGIN
number2 := 3.125346e3; -- numeric literal
number2 := -8300.00; -- numeric literal
number2 := -14; -- numeric literal
END;
/
Example 4-8 Using Character Literals in PL/SQL
DECLARE -- declare and assign variables
char1 VARCHAR2(1) := 'x'; -- character literal
char2 VARCHAR2(1);
BEGIN
char2 := '5'; -- character literal
END;
/
=============
Example 4-9 Using String Literals in PL/SQL
DECLARE -- declare and assign variables
string1 VARCHAR2(1000);
string2 VARCHAR2(32767);
BEGIN
string1 := '555-111-2323';
-- the following needs two single quotation marks to represent one in the string
string2 := 'Here''s an example of two single quotation marks used in a string.';
END;
/
============
Example 4-10 Using BOOLEAN Literals in PL/SQL
DECLARE -- declare and assign variables
finished BOOLEAN := TRUE; -- BOOLEAN literal
complete BOOLEAN; -- BOOLEAN literal
true_or_false BOOLEAN;
BEGIN
finished := FALSE; -- BOOLEAN literal set to FALSE
complete := NULL; -- BOOLEAN literal with unknown value
true_or_false := (3 = 4); -- BOOLEAN literal set to FALSE
true_or_false := (3 < 4); -- BOOLEAN literal set to TRUE
END;
/
==============
Example 4-11 Using Date-time Literals in PL/SQL
DECLARE -- declare and assign variables
date1 DATE := '11-AUG-2005'; -- DATE literal
time1 TIMESTAMP;
time2 TIMESTAMP WITH TIME ZONE;
BEGIN
time1 := '11-AUG-2005 11:01:01 PM'; -- TIMESTAMP literal
time2 := '11-AUG-2005 09:26:56.66 PM +02:00'; -- TIMESTAMP WITH TIME ZONE
END;
/
=============
Example 4-12 Using DEFAULT and NOT NULL in PL/SQL
DECLARE -- declare and assign variables
last_name VARCHAR2(30);
first_name VARCHAR2(25);
employee_id NUMBER(6);
active_employee BOOLEAN NOT NULL := TRUE; -- value cannot be NULL
monthly_salary NUMBER(6) NOT NULL := 2000; -- value cannot be NULL
number_of_days_worked NUMBER(2);
pay_per_day NUMBER(6,2);
employee_count NUMBER(6) := 0;
avg_days_worked_month NUMBER(2) DEFAULT 21; -- assign a default value
BEGIN
NULL; -- NULL statement does nothing, allows this block to executed and tested
END;
/
===============
============================
Example 4-13 Assigning Values to Variables Using PL/SQL SELECT INTO
DECLARE -- declare and assign values
bonus_rate CONSTANT NUMBER(2,3) := 0.05;
bonus NUMBER(8,2);
emp_id NUMBER(6) := 120; -- assign a test value for employee ID
BEGIN
-- retreive a salary from the employees table, then calculate the bonus and
-- assign the value to the bonus variable
SELECT salary * bonus_rate INTO bonus FROM employees
WHERE employee_id = emp_id;
-- display the employee_id, bonus amount, and bonus rate
DBMS_OUTPUT.PUT_LINE ( 'Employee: ' || TO_CHAR(emp_id)
|| ' Bonus: ' || TO_CHAR(bonus) || ' Bonus Rate: ' || TO_CHAR(bonus_rate));
END;
/
============
Example 4-14 Using %TYPE With Table Columns in PL/SQL
DECLARE -- declare variables using %TYPE attribute
empid employees.employee_id%TYPE; -- employee_id datatype is NUMBER(6)
emplname employees.last_name%TYPE; -- last_name datatype is VARCHAR2(25)
BEGIN
empid := 100301; -- this is OK because it fits in NUMBER(6)
-- empid := 3018907; -- this is too large and will cause an overflow
emplname := 'Patel'; -- this is OK because it fits in VARCHAR2(25)
DBMS_OUTPUT.PUT_LINE('Employee ID: ' || empid); -- display data
DBMS_OUTPUT.PUT_LINE('Employee name: ' || emplname); -- display data
END;
/
============
Example 4-15 Using %ROWTYPE with a PL/SQL Record
DECLARE -- declare variables
-- declare record variable that represents a row fetched from the employees table
emp_rec employees%ROWTYPE; -- declare variable with %ROWTYPE attribute
BEGIN
SELECT * INTO emp_rec FROM EMPLOYEES WHERE employee_id = 120; -- retrieve record
DBMS_OUTPUT.PUT_LINE('Employee name: ' || emp_rec.first_name || ' '
|| emp_rec.last_name); -- display
END;
/
================
Example 4-16 Using a Simple IF-THEN Statement in PL/SQL
DECLARE
sal NUMBER(8,2);
bonus NUMBER(6,2);
hiredate DATE;
empid NUMBER(6) := 128; -- use employee 120 for testing
BEGIN
-- retrieve the salary and the date that employee was hired, the date is checked
-- to calculate the amount of the bonus for the employee
SELECT salary, hire_date INTO sal, hiredate FROM employees
WHERE employee_id = empid;
IF hiredate > TO_DATE('01-JAN-00') THEN
bonus := sal/20;
DBMS_OUTPUT.PUT_LINE('Bonus for employee: ' || empid || ' is: ' || bonus );
END IF;
END;
/
==============
Example 4-17 Using the IF-THEN-ELSEIF Statement in PL/SQL
DECLARE
bonus NUMBER(6,2);
empid NUMBER(6) := 120;
hiredate DATE;
BEGIN
-- retrieve the date that employee was hired, the date is checked
-- to determine the amount of the bonus for the employee
SELECT hire_date INTO hiredate FROM employees WHERE employee_id = empid;
IF hiredate > TO_DATE('01-JAN-98') THEN
bonus := 500;
ELSIF hiredate > TO_DATE('01-JAN-96') THEN
bonus := 1000;
ELSE
bonus := 1500;
END IF;
DBMS_OUTPUT.PUT_LINE('Bonus for employee: ' || empid || ' is: ' || bonus );
END;
/
============
Example 4-18 Using the CASE-WHEN Statement in PL/SQL
DECLARE
grade CHAR(1);
BEGIN
grade := 'B';
CASE grade
WHEN 'A' THEN DBMS_OUTPUT.PUT_LINE('Excellent');
WHEN 'B' THEN DBMS_OUTPUT.PUT_LINE('Very Good');
WHEN 'C' THEN DBMS_OUTPUT.PUT_LINE('Good');
WHEN 'D' THEN DBMS_OUTPUT.PUT_LINE('Fair');
WHEN 'F' THEN DBMS_OUTPUT.PUT_LINE('Poor');
ELSE DBMS_OUTPUT.PUT_LINE('No such grade');
END CASE;
END;
/
============
Example 4-19 Using the IF-THEN_ELSE and CASE Statement in PL/SQL
DECLARE -- declare variables
empid NUMBER(6) := 115;
jobid VARCHAR2(10);
sal NUMBER(8,2);
sal_raise NUMBER(3,2); -- this is the rate of increase for the raise
BEGIN
-- retrieve the job ID and salary for the employee and
-- assign the values to variables jobid and sal
SELECT job_id, salary INTO jobid, sal from employees WHERE employee_id = empid;
CASE -- determine the salary raise rate based on employee job ID
WHEN jobid = 'PU_CLERK' THEN
IF sal < 3000 THEN sal_raise := .08;
ELSE sal_raise := .07;
END IF;
WHEN jobid = 'SH_CLERK' THEN
IF sal < 4000 THEN sal_raise := .06;
ELSE sal_raise := .05;
END IF;
WHEN jobid = 'ST_CLERK' THEN
IF sal < 3500 THEN sal_raise := .04;
ELSE sal_raise := .03;
END IF;
ELSE
BEGIN
-- if no conditions met, then the following
DBMS_OUTPUT.PUT_LINE('No raise for this job: ' || jobid);
END;
END CASE;
-- display the percent raise for the employee
DBMS_OUTPUT.PUT_LINE('Percent salary raise for employee: ' || empid || ' is: '
|| sal_raise );
END;
/
=============
Example 4-20 Using the FOR-LOOP in PL/SQL
BEGIN
-- use a FOR loop to process a series of numbers
FOR loop_counter IN 1..10 LOOP
DBMS_OUTPUT.PUT_LINE('Number: ' || TO_CHAR(loop_counter)
|| ' Square: ' || TO_CHAR(loop_counter**2));
END LOOP;
END;
/
============
Example 4-21 Using WHILE-LOOP for Control in PL/SQL
DECLARE -- declare variables
i NUMBER := 1; -- loop counter, initialize to one
i_cubed NUMBER;
BEGIN
-- use WHILE LOOP to process data
WHILE i <= 10 LOOP
i_cubed := i**3;
DBMS_OUTPUT.PUT_LINE('Number: ' || TO_CHAR(i)
|| ' Cube: ' || TO_CHAR(i_cubed));
i := i + 1;
END LOOP;
END;
/
==============
Example 4-22 Using the EXIT-WHEN Statement in PL/SQL
DECLARE -- declare and assign values to variables
total NUMBER(9) := 0;
counter NUMBER(6) := 0;
BEGIN
LOOP
counter := counter + 1; -- increment counter variable
total := total + counter * counter; -- compute total
-- exit loop when condition is true
EXIT WHEN total > 25000; -- LOOP until condition is met
END LOOP;
DBMS_OUTPUT.PUT_LINE('Counter: ' || TO_CHAR(counter)
|| ' Total: ' || TO_CHAR(total)); -- display results
END;
/
============
Example 4-23 Using the GOTO Statement in PL/SQL
DECLARE -- declare variables
p VARCHAR2(30);
n PLS_INTEGER := 37; -- test any integer > 2 for prime, here 37
BEGIN
-- loop through divisors to determine if a prime number
FOR j in 2..ROUND(SQRT(n))
LOOP
IF n MOD j = 0 THEN -- test for prime
p := ' is NOT a prime number'; -- not a prime number
GOTO print_now;
END IF;
END LOOP;
p := ' is a prime number';
<<print_now>>
DBMS_OUTPUT.PUT_LINE(TO_CHAR(n) || p); -- display results
END;
/
============
===========
Example 4-24 Declaring a Local PL/SQL Procedure With IN OUT Parameters
DECLARE -- declare variables and subprograms
fname VARCHAR2(20) := 'randall';
lname VARCHAR2(25) := 'dexter';
-- declare a local procedure which can only be used in this block
PROCEDURE upper_name ( v1 IN OUT VARCHAR2, v2 IN OUT VARCHAR2) AS
BEGIN
v1 := UPPER(v1); -- change the string to uppercase
v2 := UPPER(v2); -- change the string to uppercase
END upper_name;
-- start of executable part of block
BEGIN
DBMS_OUTPUT.PUT_LINE(fname || ' ' || lname ); -- display initial values
upper_name (fname, lname); -- call the procedure with parameters
DBMS_OUTPUT.PUT_LINE(fname || ' ' || lname ); -- display new values
END;
/
============
Example 4-25 Declaring a Local PL/SQL Function With IN Parameters
DECLARE -- declare variables and subprograms
fname VARCHAR2(20) := 'randall';
lname VARCHAR2(25) := 'dexter';
-- declare local function which can only be used in this block
FUNCTION upper_name ( v1 IN VARCHAR2, v2 IN VARCHAR2)
RETURN VARCHAR2 AS
v3 VARCHAR2(45); -- this variable is local to the function
BEGIN
-- build a string that will be returned as the function value
v3 := v1 || ' + ' || v2 || ' = ' || UPPER(v1) || ' ' || UPPER(v2);
RETURN v3; -- return the value of v3
END upper_name;
-- start of executable part of block
BEGIN
-- call the function and display results
DBMS_OUTPUT.PUT_LINE(upper_name (fname, lname));
END;
/
========
Example 4-26 Declaring a Complex Local Procedure in a PL/SQL Block
DECLARE -- declare variables and subprograms
empid NUMBER;
-- declare local procedure for this block
PROCEDURE avg_min_max_sal (empid IN NUMBER) IS
jobid VARCHAR2(10);
avg_sal NUMBER;
min_sal NUMBER;
max_sal NUMBER;
BEGIN
-- determine the job ID for the employee
SELECT job_id INTO jobid FROM employees WHERE employee_id = empid;
-- calculate the average, minimum, and maximum salaries for that job ID
SELECT AVG(salary), MIN(salary), MAX(salary) INTO avg_sal, min_sal, max_sal
FROM employees WHERE job_id = jobid;
-- display data
DBMS_OUTPUT.PUT_LINE ('Employee ID: ' || empid || ' Job ID: ' || jobid);
DBMS_OUTPUT.PUT_LINE ('The average salary for job ID: ' || jobid
|| ' is ' || TO_CHAR(avg_sal));
DBMS_OUTPUT.PUT_LINE ('The minimum salary for job ID: ' || jobid
|| ' is ' || TO_CHAR(min_sal));
DBMS_OUTPUT.PUT_LINE ('The maximum salary for job ID: ' || jobid
|| ' is ' || TO_CHAR(max_sal));
END avg_min_max_sal;
-- end of local procedure
-- start executable part of block
BEGIN
-- call the procedure with several employee IDs
empid := 125;
avg_min_max_sal(empid);
avg_min_max_sal(112);
END;
/
================
Example 4-27 Fetching a Single Row With a Cursor in PL/SQL
DECLARE
-- declare variables for first_name and last_name fetched from the employees table
firstname employees.first_name%TYPE; -- variable for first_name
lastname employees.last_name%TYPE; -- variable for last_name
-- declare a cursor to fetch data from a row (employee 120) in the employees table
CURSOR cursor1 IS
SELECT first_name, last_name FROM employees WHERE employee_id = 120;
BEGIN
OPEN cursor1; -- open the cursor
FETCH cursor1 INTO firstname, lastname; -- fetch data into local variables
DBMS_OUTPUT.PUT_LINE('Employee name: ' || firstname || ' ' || lastname);
CLOSE cursor1; -- close the cursor
END;
/
==========
Example 4-28 Fetching Multiple Rows With a Cursor in PL/SQL
DECLARE
-- declare variables for data fetched from cursors
empid employees.employee_id%TYPE; -- variable for employee_id
jobid employees.job_id%TYPE; -- variable for job_id
lastname employees.last_name%TYPE; -- variable for last_name
rowcount NUMBER;
-- declare the cursors
CURSOR cursor1 IS SELECT last_name, job_id FROM employees
WHERE job_id LIKE '%CLERK';
CURSOR cursor2 is SELECT employee_id, last_name, job_id FROM employees
WHERE job_id LIKE '%MAN' OR job_id LIKE '%MGR';
BEGIN
-- start the processing with cursor1
OPEN cursor1; -- open cursor1 before fetching
DBMS_OUTPUT.PUT_LINE( '---------- cursor 1-----------------' );
LOOP
FETCH cursor1 INTO lastname, jobid; -- fetches 2 columns into variables
-- check the cursor attribute NOTFOUND for the end of data
EXIT WHEN cursor1%NOTFOUND;
-- display the last name and job ID for each record (row) fetched
DBMS_OUTPUT.PUT_LINE( RPAD(lastname, 25, ' ') || jobid );
END LOOP;
rowcount := cursor1%ROWCOUNT;
DBMS_OUTPUT.PUT_LINE('The number of rows fetched is ' || rowcount );
CLOSE cursor1;
-- start the processing with cursor2
OPEN cursor2;
DBMS_OUTPUT.PUT_LINE( '---------- cursor 2-----------------' );
LOOP
-- fetch 3 columns into the variables
FETCH cursor2 INTO empid, lastname, jobid;
EXIT WHEN cursor2%NOTFOUND;
-- display the employee ID, last name, and job ID for each record (row) fetched
DBMS_OUTPUT.PUT_LINE( empid || ': ' || RPAD(lastname, 25, ' ') || jobid );
END LOOP;
rowcount := cursor2%ROWCOUNT;
DBMS_OUTPUT.PUT_LINE('The number of rows fetched is ' || rowcount );
CLOSE cursor2;
END;
/
================
Example 4-29 Passing Parameters to a Cursor in PL/SQL
DECLARE
-- declare variables for data fetched from cursor
empid employees.employee_id%TYPE; -- variable for employee_id
hiredate employees.hire_date%TYPE; -- variable for hire_date
firstname employees.first_name%TYPE; -- variable for first_name
lastname employees.last_name%TYPE; -- variable for last_name
rowcount NUMBER;
bonusamount NUMBER;
yearsworked NUMBER;
-- declare the cursor with a parameter,
CURSOR cursor1 (thismonth NUMBER)IS
SELECT employee_id, first_name, last_name, hire_date FROM employees
WHERE EXTRACT(MONTH FROM hire_date) = thismonth;
BEGIN
-- open and pass a parameter to cursor1, select employees hired on this month
OPEN cursor1(EXTRACT(MONTH FROM SYSDATE));
DBMS_OUTPUT.PUT_LINE('----- Today is ' || TO_CHAR(SYSDATE, 'DL') || ' -----');
DBMS_OUTPUT.PUT_LINE('Employees with yearly bonus amounts:');
LOOP
-- fetches 4 columns into variables
FETCH cursor1 INTO empid, firstname, lastname, hiredate;
-- check the cursor attribute NOTFOUND for the end of data
EXIT WHEN cursor1%NOTFOUND;
-- calculate the yearly bonus amount based on months (years) worked
yearsworked := ROUND( (MONTHS_BETWEEN(SYSDATE, hiredate)/12) );
IF yearsworked > 10 THEN bonusamount := 2000;
ELSIF yearsworked > 8 THEN bonusamount := 1600;
ELSIF yearsworked > 6 THEN bonusamount := 1200;
ELSIF yearsworked > 4 THEN bonusamount := 800;
ELSIF yearsworked > 2 THEN bonusamount := 400;
ELSIF yearsworked > 0 THEN bonusamount := 100;
END IF;
-- display the employee Id, first name, last name, hire date, and bonus
-- for each record (row) fetched
DBMS_OUTPUT.PUT_LINE( empid || ' ' || RPAD(firstname, 21, ' ') ||
RPAD(lastname, 26, ' ') || hiredate || TO_CHAR(bonusamount, '$9,999'));
END LOOP;
rowcount := cursor1%ROWCOUNT;
DBMS_OUTPUT.PUT_LINE('The number of rows fetched is ' || rowcount );
CLOSE cursor1;
END;
/
==============
Example 4-30 Using a Cursor Variable (REF CURSOR)
DECLARE
-- declare a REF CURSOR that returns employees%ROWTYPE (strongly typed)
TYPE emp_refcur_typ IS REF CURSOR RETURN employees%ROWTYPE;
emp_cursor emp_refcur_typ;
-- use the following local procedure to process all the rows after
-- the result set is built, rather than calling a procedure for each row
PROCEDURE process_emp_cv (emp_cv IN emp_refcur_typ) IS
person employees%ROWTYPE;
BEGIN
DBMS_OUTPUT.PUT_LINE('-- Here are the names from the result set --');
LOOP
FETCH emp_cv INTO person;
EXIT WHEN emp_cv%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(person.last_name || ', ' || person.first_name);
END LOOP;
END;
BEGIN
-- find employees whose employee ID is less than 108
OPEN emp_cursor FOR SELECT * FROM employees WHERE employee_id < 108;
process_emp_cv(emp_cursor); -- pass emp_cursor to the procedure for processing
CLOSE emp_cursor;
-- find employees whose last name starts with R
OPEN emp_cursor FOR SELECT * FROM employees WHERE last_name LIKE 'R%';
process_emp_cv(emp_cursor); -- pass emp_cursor to the procedure for processing
CLOSE emp_cursor;
END;
/
=================
Cursor Attributes
Cursor attributes return information about the execution of DML and DDL statements, such INSERT, UPDATE, DELETE, SELECT INTO, COMMIT, or ROLLBACK statements. The cursor attributes are %FOUND, %ISOPEN, %NOTFOUND, and %ROWCOUNT. These attributes return useful information about the most recently executed SQL statement. When using an explicit cursor, add the explicit cursor or cursor variable name to the beginning of the attribute, such as cursor1%FOUND, to return information for the most recently executed SQL statement for that cursor.
The attributes provide the following information:
%FOUND Attribute: Has a Row Been Fetched?
After a cursor or cursor variable is opened but before the first fetch, %FOUND returns NULL. After any fetches, it returns TRUE if the last fetch returned a row, or FALSE if the last fetch did not return a row.
%ISOPEN Attribute: Is the Cursor Open?
If a cursor or cursor variable is open, then %ISOPEN returns TRUE ; otherwise, %ISOPEN returns FALSE.
Note that implicit cursors are automatically opened before and closed after executing the associated SQL statement so %ISOPEN always returns FALSE.
%NOTFOUND Attribute: Has a Fetch Failed?
If the last fetch returned a row, then %NOTFOUND returns FALSE. If the last fetch failed to return a row, then %NOTFOUND returns TRUE. %NOTFOUND is the logical opposite of %FOUND.
%ROWCOUNT Attribute: How Many Rows Fetched So Far?
After a cursor or cursor variable is opened, %ROWCOUNT returns 0 before the first fetch. Thereafter, it returns the number of rows fetched so far. The number is incremented if the last fetch returned a row.
====================
Example 4-31 Declaring and Initializing a PL/SQL Record Type
DECLARE -- declare RECORD type variables
-- the following is a RECORD declaration to hold address information
TYPE location_rec IS RECORD (
room_number NUMBER(4),
building VARCHAR2(25)
);
-- you use the %TYPE attribute to declare the datatype of a table column
-- you can include (nest) a record inside of another record
TYPE person_rec IS RECORD (
employee_id employees.employee_id%TYPE,
first_name employees.first_name%TYPE,
last_name employees.last_name%TYPE,
location location_rec
);
person person_rec; -- declare a person variable of type person_rec
BEGIN
-- insert data in a record, one field at a time
person.employee_id := 20;
person.first_name := 'James';
person.last_name := 'Boynton';
person.location.room_number := 100;
person.location.building:= 'School of Education';
-- display data in a record
DBMS_OUTPUT.PUT_LINE( person.last_name || ', ' || person.first_name );
DBMS_OUTPUT.PUT_LINE( TO_CHAR(person.location.room_number) || ' '
|| person.location.building );
END;
/
=============
Example 4-32 Using %ROWTYPE With a Cursor When Declaring a PL/SQL Record
DECLARE -- declare variables
CURSOR cursor1 IS
SELECT * FROM employees
WHERE department_id = 60; -- declare cursor
-- declare record variable that represents a row fetched from the employees table
-- do not need to use TYPE .. IS RECORD with %ROWTYPE attribute
employee_rec cursor1%ROWTYPE;
BEGIN
-- open the explicit cursor c1 and use it to fetch data into employee_rec
OPEN cursor1;
LOOP
FETCH cursor1 INTO employee_rec; -- retrieve entire row into record
EXIT WHEN cursor1%NOTFOUND;
-- the record contains all the fields for a row in the employees table
-- the following displays the data from the row fetched into the record
DBMS_OUTPUT.PUT_LINE( ' Department ' || employee_rec.department_id
|| ', Employee: ' || employee_rec.employee_id || ' - '
|| employee_rec.last_name || ', ' || employee_rec.first_name );
END LOOP;
CLOSE cursor1;
END;
/
==============
Example 4-33 Using a PL/SQL VARRAY Type With Character Elements
DECLARE -- declare variables
TYPE jobids_array IS VARRAY(20) OF VARCHAR2(10); -- declare VARRAY
jobids jobids_array; -- declare a variable of type jobids_array
howmany NUMBER; -- declare a variable to hold employee count
BEGIN
-- initialize the arrary with some job ID values
jobids := jobids_array('AC_ACCOUNT', 'AC_MGR', 'AD_ASST', 'AD_PRES', 'AD_VP',
'FI_ACCOUNT', 'FI_MGR', 'HR_REP', 'IT_PROG', 'PU_MAN',
'SH_CLERK', 'ST_CLERK', 'ST_MAN');
-- display the current size of the array with COUNT
DBMS_OUTPUT.PUT_LINE('The number of elements (current size) in the array is '
|| jobids.COUNT);
-- display the maximum number of elements for the array LIMIT
DBMS_OUTPUT.PUT_LINE('The maximum number (limit) of elements in the array is '
|| jobids.LIMIT);
-- check whether another element can be added to the array
IF jobids.LIMIT - jobids.COUNT >= 1 THEN
jobids.EXTEND(1); -- add one more element
jobids(14) := 'PU_CLERK'; -- assign a value to the element
END IF;
-- loop through all the varray values, starting
-- with the FIRST and ending with the LAST element
FOR i IN jobids.FIRST..jobids.LAST LOOP
-- determine the number of employees for each job ID in the array
SELECT COUNT(*) INTO howmany FROM employees WHERE job_id = jobids(i);
DBMS_OUTPUT.PUT_LINE ( 'Job ID: ' || RPAD(jobids(i), 10, ' ') ||
' Number of employees: ' || TO_CHAR(howmany));
END LOOP;
-- display the current size of the array with COUNT
DBMS_OUTPUT.PUT_LINE('The number of elements (current size) in the array is '
|| jobids.COUNT);
END;
/
============
Example 4-34 Using a PL/SQL VARRAY Type With Record Type Elements
DECLARE -- declare variables
CURSOR cursor1 IS SELECT * FROM jobs; -- create a cursor for fetching the rows
jobs_rec cursor1%ROWTYPE; -- create a record to hold the row data
-- declare VARRAY with enough elements to hold all the rows in the jobs table
TYPE jobs_array IS VARRAY(25) OF cursor1%ROWTYPE;
jobs_arr jobs_array; -- declare a variable of type jobids_array
howmany NUMBER; -- declare a variable to hold employee count
i NUMBER := 1; -- counter for the number of elements in the array
BEGIN
jobs_arr := jobs_array(); -- initialize the array before using
OPEN cursor1; -- open the cursor before using
LOOP
FETCH cursor1 INTO jobs_rec; -- retrieve a row from the jobs table
EXIT WHEN cursor1%NOTFOUND; -- exit when no data is retrieved
jobs_arr.EXTEND(1); -- add another element to the varray with EXTEND
jobs_arr(i) := jobs_rec; -- assign the fetched row to an element the array
i := i + 1; -- increment the element count
END LOOP;
CLOSE cursor1; -- close the cursor when finished with it
FOR j IN jobs_arr.FIRST..jobs_arr.LAST LOOP -- loop through the varray elements
-- determine the number of employees for each job ID in the array
SELECT COUNT(*) INTO howmany FROM employees WHERE job_id = jobs_arr(j).job_id;
DBMS_OUTPUT.PUT_LINE ( 'Job ID: ' || RPAD(jobs_arr(j).job_id, 11, ' ') ||
RPAD(jobs_arr(j).job_title, 36, ' ') ||
' Number of employees: ' || TO_CHAR(howmany));
END LOOP;
END;
/
==============
Example 4-35 Using Dynamic SQL to Manipulate Data in PL/SQL
DECLARE
sql_stmt VARCHAR2(200); -- variable to hold SQL statement
column_name VARCHAR2(30); -- variable for column name
dept_id NUMBER(4);
dept_name VARCHAR2(30);
mgr_id NUMBER(6);
loc_id NUMBER(4);
BEGIN
-- create a SQL statement (sql_stmt) to execute with EXECUTE IMMEDIATE
-- the statement INSERTs a row into the departments table using bind variables
-- note that there is no semi-colon (;) inside the quotation marks '...'
sql_stmt := 'INSERT INTO departments VALUES (:dptid, :dptname, :mgrid, :locid)';
dept_id := 46;
dept_name := 'Special Projects';
mgr_id := 200;
loc_id := 1700;
-- execute the sql_stmt using the values of the variables in the USING clause
-- for the bind variables
EXECUTE IMMEDIATE sql_stmt USING dept_id, dept_name, mgr_id, loc_id;
-- use EXECUTE IMMEDIATE to delete the row that was previously inserted,
-- substituting for the column name and using a bind variable
column_name := 'DEPARTMENT_ID';
EXECUTE IMMEDIATE 'DELETE FROM departments WHERE ' || column_name || ' = :num'
USING dept_id;
END;
/
===============
Example 4-36 Using Dynamic SQL to Create a Table in PL/SQL
DECLARE
tabname VARCHAR2(30); -- variable for table name
current_date VARCHAR2(8); -- varible for current date
BEGIN
-- extract, format, and insert the year, month, and day from SYSDATE into
-- the current_date variable
SELECT TO_CHAR(EXTRACT(YEAR FROM SYSDATE)) ||
TO_CHAR(EXTRACT(MONTH FROM SYSDATE),'FM09') ||
TO_CHAR(EXTRACT(DAY FROM SYSDATE),'FM09') INTO current_date FROM DUAL;
-- construct the table name with the current date as a suffix
tabname := 'log_table_' || current_date;
-- use EXECUTE IMMEDIATE to create a table with tabname as the table name
EXECUTE IMMEDIATE 'CREATE TABLE ' || tabname ||
'(op_time VARCHAR2(10), operation VARCHAR2(50))' ;
DBMS_OUTPUT.PUT_LINE(tabname || ' has been created');
-- now drop the table
EXECUTE IMMEDIATE 'DROP TABLE ' || tabname;
END;
/
================
Example 4-37 Managing Multiple Errors With a Single PL/SQL Exception Handler
DECLARE -- declare variables
emp_column VARCHAR2(30) := 'last_name';
table_name VARCHAR2(30) := 'emp'; -- set value to raise error
temp_var VARCHAR2(30);
BEGIN
temp_var := emp_column;
SELECT COLUMN_NAME INTO temp_var FROM USER_TAB_COLS
WHERE TABLE_NAME = 'EMPLOYEES' AND COLUMN_NAME = UPPER(emp_column);
-- processing here
temp_var := table_name;
SELECT OBJECT_NAME INTO temp_var FROM USER_OBJECTS
WHERE OBJECT_NAME = UPPER(table_name) AND OBJECT_TYPE = 'TABLE';
-- processing here
EXCEPTION
WHEN NO_DATA_FOUND THEN -- catches all 'no data found' errors
DBMS_OUTPUT.PUT_LINE ('No Data found for SELECT on ' || temp_var);
END;
/
===========
Example 4-38 Determining the Scope of PL/SQL Exceptions
DECLARE
past_due EXCEPTION;
acct_num NUMBER;
BEGIN
DECLARE ---------- subblock begins
past_due EXCEPTION; -- this declaration prevails
acct_num NUMBER;
due_date DATE := SYSDATE - 1; -- set on purpose to raise exception
todays_date DATE := SYSDATE;
BEGIN
IF due_date < todays_date THEN
RAISE past_due; -- this is not handled
END IF;
END; ------------- subblock ends
EXCEPTION
WHEN past_due THEN -- does not handle raised exception
DBMS_OUTPUT.PUT_LINE('Handling PAST_DUE exception.');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Could not recognize PAST_DUE_EXCEPTION in this scope.');
END;
/
============
Example 4-39 Continuing After an Exception in PL/SQL
-- create a temporary table for this example
CREATE TABLE employees_temp AS
SELECT employee_id, salary, commission_pct FROM employees;
DECLARE
sal_calc NUMBER(8,2);
BEGIN
INSERT INTO employees_temp VALUES (303, 2500, 0);
BEGIN -- subblock begins
SELECT salary / commission_pct INTO sal_calc FROM employees_temp
WHERE employee_id = 303;
EXCEPTION
WHEN ZERO_DIVIDE THEN
sal_calc := 2500;
END; -- subblock ends
INSERT INTO employees_temp VALUES (304, sal_calc/100, .1);
EXCEPTION
WHEN ZERO_DIVIDE THEN
NULL;
END;
/
-- view the results
SELECT * FROM employees_temp WHERE employee_id = 303 OR employee_id = 304;
-- drop the temporary table
DROP TABLE employees_temp;
===============
PROGRAM WITH IN PARAMETER
-- including OR REPLACE is more convenient when updating a subprogram
-- IN is the default for parameter declarations so it could be omitted
CREATE OR REPLACE PROCEDURE award_bonus (emp_id IN NUMBER, bonus_rate IN NUMBER)
AS
-- declare variables to hold values from table columns, use %TYPE attribute
emp_comm employees.commission_pct%TYPE;
emp_sal employees.salary%TYPE;
-- declare an exception to catch when the salary is NULL
salary_missing EXCEPTION;
BEGIN -- executable part starts here
-- select the column values into the local variables
SELECT salary, commission_pct INTO emp_sal, emp_comm FROM employees
WHERE employee_id = emp_id;
-- check whether the salary for the employee is null, if so, raise an exception
IF emp_sal IS NULL THEN
RAISE salary_missing;
ELSE
IF emp_comm IS NULL THEN
-- if this is not a commissioned employee, increase the salary by the bonus rate
-- for this example, do not make the actual update to the salary
-- UPDATE employees SET salary = salary + salary * bonus_rate
-- WHERE employee_id = emp_id;
DBMS_OUTPUT.PUT_LINE('Employee ' || emp_id || ' receives a bonus: '
|| TO_CHAR(emp_sal * bonus_rate) );
ELSE
DBMS_OUTPUT.PUT_LINE('Employee ' || emp_id
|| ' receives a commission. No bonus allowed.');
END IF;
END IF;
EXCEPTION -- exception-handling part starts here
WHEN salary_missing THEN
DBMS_OUTPUT.PUT_LINE('Employee ' || emp_id ||
' does not have a value for salary. No update.');
WHEN OTHERS THEN
NULL; -- for other exceptions do nothing
END award_bonus;
/
-- the following BEGIN..END block calls, or executes, the award_bonus procedure
-- using employee IDs 123 and 179 with the bonus rate 0.05 (5%)
BEGIN
award_bonus(123, 0.05);
award_bonus(179, 0.05);
END;
/
=================
Example 4-1 Using a Simple PL/SQL Block
-- the following is an optional declarative part
DECLARE
monthly_salary NUMBER(6);
number_of_days_worked NUMBER(2);
pay_per_day NUMBER(6,2);
-- the following is the executable part, from BEGIN to END
BEGIN
monthly_salary := 2290;
number_of_days_worked := 21;
pay_per_day := monthly_salary/number_of_days_worked;
-- the following displays output from the PL/SQL block
DBMS_OUTPUT.PUT_LINE('The pay per day is ' || TO_CHAR(pay_per_day));
-- the following is an optional exception part that handles errors
EXCEPTION
WHEN ZERO_DIVIDE THEN
pay_per_day := 0;
END;
/
=================
Example 4-2 Using DBMS_OUTPUT.PUT_LINE to Display PL/SQL Output
-- enable SERVEROUTPUT in SQL Command Line (SQL*Plus) to display output with
-- DBMS_OUTPUT.PUT_LINE, this enables SERVEROUTPUT for this SQL*Plus session only
SET SERVEROUTPUT ON
DECLARE
answer VARCHAR2(20); -- declare a variable
BEGIN
-- assign a value to a variable
answer := 'Maybe';
-- use PUT_LINE to display data from the PL/SQL block
DBMS_OUTPUT.PUT_LINE( 'The answer is: ' || answer );
END;
/
============
Example 4-3 Using Comments in PL/SQL
DECLARE -- Declare variables here.
monthly_salary NUMBER(6); -- This is the monthly salary.
number_of_days_worked NUMBER(2); -- This is the days in one month.
pay_per_day NUMBER(6,2); -- Calculate this value.
BEGIN
-- First assign values to the variables.
monthly_salary := 2290;
number_of_days_worked := 21;
-- Now calculate the value on the following line.
pay_per_day := monthly_salary/number_of_days_worked;
-- the following displays output from the PL/SQL block
DBMS_OUTPUT.PUT_LINE('The pay per day is ' || TO_CHAR(pay_per_day));
EXCEPTION
/* This is a simple example of an exeception handler to trap division by zero.
In actual practice, it would be best to check whether a variable is
zero before using it as a divisor. */
WHEN ZERO_DIVIDE THEN
pay_per_day := 0; -- set to 0 if divisor equals 0
END;
/
=============
Example 4-4 Declaring Variables in PL/SQL
DECLARE -- declare the variables in this section
last_name VARCHAR2(30);
first_name VARCHAR2(25);
employee_id NUMBER(6);
active_employee BOOLEAN;
monthly_salary NUMBER(6);
number_of_days_worked NUMBER(2);
pay_per_day NUMBER(6,2);
avg_days_worked_month CONSTANT NUMBER(2) := 21; -- a constant variable
BEGIN
NULL; -- NULL statement does nothing, allows this block to executed and tested
END;
/
================
Example 4-5 Using Identifiers for Variables in PL/SQL
DECLARE
lastname VARCHAR2(30); -- valid identifier
last_name VARCHAR2(30); -- valid identifier, _ allowed
last$name VARCHAR2(30); -- valid identifier, $ allowed
last#name VARCHAR2(30); -- valid identifier, # allowed
-- last-name is invalid, hypen not allowed
-- last/name is invalid, slash not allowed
-- last name is invalid, space not allowed
-- LASTNAME is invalid, same as lastname and LastName
-- LastName is invalid, same as lastname and LASTNAME
BEGIN
NULL; -- NULL statement does nothing, allows this block to executed and tested
END;
/
===============
Example 4-6 Assigning Values to Variables With the PL/SQL Assignment Operator
DECLARE -- declare and assiging variables
wages NUMBER(6,2);
hours_worked NUMBER := 40;
hourly_salary NUMBER := 22.50;
bonus NUMBER := 150;
country VARCHAR2(128);
counter NUMBER := 0;
done BOOLEAN := FALSE;
valid_id BOOLEAN;
BEGIN
wages := (hours_worked * hourly_salary) + bonus; -- compute wages
country := 'France'; -- assign a string literal
country := UPPER('Canada'); -- assign an uppercase string literal
done := (counter > 100); -- assign a BOOLEAN, in this case FALSE
valid_id := TRUE; -- assign a BOOLEAN
END;
/
=================
Example 4-7 Using Numeric Literals in PL/SQL
DECLARE -- declare and assign variables
number1 PLS_INTEGER := 32000; -- numeric literal
number2 NUMBER(8,3);
BEGIN
number2 := 3.125346e3; -- numeric literal
number2 := -8300.00; -- numeric literal
number2 := -14; -- numeric literal
END;
/
Example 4-8 Using Character Literals in PL/SQL
DECLARE -- declare and assign variables
char1 VARCHAR2(1) := 'x'; -- character literal
char2 VARCHAR2(1);
BEGIN
char2 := '5'; -- character literal
END;
/
=============
Example 4-9 Using String Literals in PL/SQL
DECLARE -- declare and assign variables
string1 VARCHAR2(1000);
string2 VARCHAR2(32767);
BEGIN
string1 := '555-111-2323';
-- the following needs two single quotation marks to represent one in the string
string2 := 'Here''s an example of two single quotation marks used in a string.';
END;
/
============
Example 4-10 Using BOOLEAN Literals in PL/SQL
DECLARE -- declare and assign variables
finished BOOLEAN := TRUE; -- BOOLEAN literal
complete BOOLEAN; -- BOOLEAN literal
true_or_false BOOLEAN;
BEGIN
finished := FALSE; -- BOOLEAN literal set to FALSE
complete := NULL; -- BOOLEAN literal with unknown value
true_or_false := (3 = 4); -- BOOLEAN literal set to FALSE
true_or_false := (3 < 4); -- BOOLEAN literal set to TRUE
END;
/
==============
Example 4-11 Using Date-time Literals in PL/SQL
DECLARE -- declare and assign variables
date1 DATE := '11-AUG-2005'; -- DATE literal
time1 TIMESTAMP;
time2 TIMESTAMP WITH TIME ZONE;
BEGIN
time1 := '11-AUG-2005 11:01:01 PM'; -- TIMESTAMP literal
time2 := '11-AUG-2005 09:26:56.66 PM +02:00'; -- TIMESTAMP WITH TIME ZONE
END;
/
=============
Example 4-12 Using DEFAULT and NOT NULL in PL/SQL
DECLARE -- declare and assign variables
last_name VARCHAR2(30);
first_name VARCHAR2(25);
employee_id NUMBER(6);
active_employee BOOLEAN NOT NULL := TRUE; -- value cannot be NULL
monthly_salary NUMBER(6) NOT NULL := 2000; -- value cannot be NULL
number_of_days_worked NUMBER(2);
pay_per_day NUMBER(6,2);
employee_count NUMBER(6) := 0;
avg_days_worked_month NUMBER(2) DEFAULT 21; -- assign a default value
BEGIN
NULL; -- NULL statement does nothing, allows this block to executed and tested
END;
/
===============
============================
Example 4-13 Assigning Values to Variables Using PL/SQL SELECT INTO
DECLARE -- declare and assign values
bonus_rate CONSTANT NUMBER(2,3) := 0.05;
bonus NUMBER(8,2);
emp_id NUMBER(6) := 120; -- assign a test value for employee ID
BEGIN
-- retreive a salary from the employees table, then calculate the bonus and
-- assign the value to the bonus variable
SELECT salary * bonus_rate INTO bonus FROM employees
WHERE employee_id = emp_id;
-- display the employee_id, bonus amount, and bonus rate
DBMS_OUTPUT.PUT_LINE ( 'Employee: ' || TO_CHAR(emp_id)
|| ' Bonus: ' || TO_CHAR(bonus) || ' Bonus Rate: ' || TO_CHAR(bonus_rate));
END;
/
============
Example 4-14 Using %TYPE With Table Columns in PL/SQL
DECLARE -- declare variables using %TYPE attribute
empid employees.employee_id%TYPE; -- employee_id datatype is NUMBER(6)
emplname employees.last_name%TYPE; -- last_name datatype is VARCHAR2(25)
BEGIN
empid := 100301; -- this is OK because it fits in NUMBER(6)
-- empid := 3018907; -- this is too large and will cause an overflow
emplname := 'Patel'; -- this is OK because it fits in VARCHAR2(25)
DBMS_OUTPUT.PUT_LINE('Employee ID: ' || empid); -- display data
DBMS_OUTPUT.PUT_LINE('Employee name: ' || emplname); -- display data
END;
/
============
Example 4-15 Using %ROWTYPE with a PL/SQL Record
DECLARE -- declare variables
-- declare record variable that represents a row fetched from the employees table
emp_rec employees%ROWTYPE; -- declare variable with %ROWTYPE attribute
BEGIN
SELECT * INTO emp_rec FROM EMPLOYEES WHERE employee_id = 120; -- retrieve record
DBMS_OUTPUT.PUT_LINE('Employee name: ' || emp_rec.first_name || ' '
|| emp_rec.last_name); -- display
END;
/
================
Example 4-16 Using a Simple IF-THEN Statement in PL/SQL
DECLARE
sal NUMBER(8,2);
bonus NUMBER(6,2);
hiredate DATE;
empid NUMBER(6) := 128; -- use employee 120 for testing
BEGIN
-- retrieve the salary and the date that employee was hired, the date is checked
-- to calculate the amount of the bonus for the employee
SELECT salary, hire_date INTO sal, hiredate FROM employees
WHERE employee_id = empid;
IF hiredate > TO_DATE('01-JAN-00') THEN
bonus := sal/20;
DBMS_OUTPUT.PUT_LINE('Bonus for employee: ' || empid || ' is: ' || bonus );
END IF;
END;
/
==============
Example 4-17 Using the IF-THEN-ELSEIF Statement in PL/SQL
DECLARE
bonus NUMBER(6,2);
empid NUMBER(6) := 120;
hiredate DATE;
BEGIN
-- retrieve the date that employee was hired, the date is checked
-- to determine the amount of the bonus for the employee
SELECT hire_date INTO hiredate FROM employees WHERE employee_id = empid;
IF hiredate > TO_DATE('01-JAN-98') THEN
bonus := 500;
ELSIF hiredate > TO_DATE('01-JAN-96') THEN
bonus := 1000;
ELSE
bonus := 1500;
END IF;
DBMS_OUTPUT.PUT_LINE('Bonus for employee: ' || empid || ' is: ' || bonus );
END;
/
============
Example 4-18 Using the CASE-WHEN Statement in PL/SQL
DECLARE
grade CHAR(1);
BEGIN
grade := 'B';
CASE grade
WHEN 'A' THEN DBMS_OUTPUT.PUT_LINE('Excellent');
WHEN 'B' THEN DBMS_OUTPUT.PUT_LINE('Very Good');
WHEN 'C' THEN DBMS_OUTPUT.PUT_LINE('Good');
WHEN 'D' THEN DBMS_OUTPUT.PUT_LINE('Fair');
WHEN 'F' THEN DBMS_OUTPUT.PUT_LINE('Poor');
ELSE DBMS_OUTPUT.PUT_LINE('No such grade');
END CASE;
END;
/
============
Example 4-19 Using the IF-THEN_ELSE and CASE Statement in PL/SQL
DECLARE -- declare variables
empid NUMBER(6) := 115;
jobid VARCHAR2(10);
sal NUMBER(8,2);
sal_raise NUMBER(3,2); -- this is the rate of increase for the raise
BEGIN
-- retrieve the job ID and salary for the employee and
-- assign the values to variables jobid and sal
SELECT job_id, salary INTO jobid, sal from employees WHERE employee_id = empid;
CASE -- determine the salary raise rate based on employee job ID
WHEN jobid = 'PU_CLERK' THEN
IF sal < 3000 THEN sal_raise := .08;
ELSE sal_raise := .07;
END IF;
WHEN jobid = 'SH_CLERK' THEN
IF sal < 4000 THEN sal_raise := .06;
ELSE sal_raise := .05;
END IF;
WHEN jobid = 'ST_CLERK' THEN
IF sal < 3500 THEN sal_raise := .04;
ELSE sal_raise := .03;
END IF;
ELSE
BEGIN
-- if no conditions met, then the following
DBMS_OUTPUT.PUT_LINE('No raise for this job: ' || jobid);
END;
END CASE;
-- display the percent raise for the employee
DBMS_OUTPUT.PUT_LINE('Percent salary raise for employee: ' || empid || ' is: '
|| sal_raise );
END;
/
=============
Example 4-20 Using the FOR-LOOP in PL/SQL
BEGIN
-- use a FOR loop to process a series of numbers
FOR loop_counter IN 1..10 LOOP
DBMS_OUTPUT.PUT_LINE('Number: ' || TO_CHAR(loop_counter)
|| ' Square: ' || TO_CHAR(loop_counter**2));
END LOOP;
END;
/
============
Example 4-21 Using WHILE-LOOP for Control in PL/SQL
DECLARE -- declare variables
i NUMBER := 1; -- loop counter, initialize to one
i_cubed NUMBER;
BEGIN
-- use WHILE LOOP to process data
WHILE i <= 10 LOOP
i_cubed := i**3;
DBMS_OUTPUT.PUT_LINE('Number: ' || TO_CHAR(i)
|| ' Cube: ' || TO_CHAR(i_cubed));
i := i + 1;
END LOOP;
END;
/
==============
Example 4-22 Using the EXIT-WHEN Statement in PL/SQL
DECLARE -- declare and assign values to variables
total NUMBER(9) := 0;
counter NUMBER(6) := 0;
BEGIN
LOOP
counter := counter + 1; -- increment counter variable
total := total + counter * counter; -- compute total
-- exit loop when condition is true
EXIT WHEN total > 25000; -- LOOP until condition is met
END LOOP;
DBMS_OUTPUT.PUT_LINE('Counter: ' || TO_CHAR(counter)
|| ' Total: ' || TO_CHAR(total)); -- display results
END;
/
============
Example 4-23 Using the GOTO Statement in PL/SQL
DECLARE -- declare variables
p VARCHAR2(30);
n PLS_INTEGER := 37; -- test any integer > 2 for prime, here 37
BEGIN
-- loop through divisors to determine if a prime number
FOR j in 2..ROUND(SQRT(n))
LOOP
IF n MOD j = 0 THEN -- test for prime
p := ' is NOT a prime number'; -- not a prime number
GOTO print_now;
END IF;
END LOOP;
p := ' is a prime number';
<<print_now>>
DBMS_OUTPUT.PUT_LINE(TO_CHAR(n) || p); -- display results
END;
/
============
===========
Example 4-24 Declaring a Local PL/SQL Procedure With IN OUT Parameters
DECLARE -- declare variables and subprograms
fname VARCHAR2(20) := 'randall';
lname VARCHAR2(25) := 'dexter';
-- declare a local procedure which can only be used in this block
PROCEDURE upper_name ( v1 IN OUT VARCHAR2, v2 IN OUT VARCHAR2) AS
BEGIN
v1 := UPPER(v1); -- change the string to uppercase
v2 := UPPER(v2); -- change the string to uppercase
END upper_name;
-- start of executable part of block
BEGIN
DBMS_OUTPUT.PUT_LINE(fname || ' ' || lname ); -- display initial values
upper_name (fname, lname); -- call the procedure with parameters
DBMS_OUTPUT.PUT_LINE(fname || ' ' || lname ); -- display new values
END;
/
============
Example 4-25 Declaring a Local PL/SQL Function With IN Parameters
DECLARE -- declare variables and subprograms
fname VARCHAR2(20) := 'randall';
lname VARCHAR2(25) := 'dexter';
-- declare local function which can only be used in this block
FUNCTION upper_name ( v1 IN VARCHAR2, v2 IN VARCHAR2)
RETURN VARCHAR2 AS
v3 VARCHAR2(45); -- this variable is local to the function
BEGIN
-- build a string that will be returned as the function value
v3 := v1 || ' + ' || v2 || ' = ' || UPPER(v1) || ' ' || UPPER(v2);
RETURN v3; -- return the value of v3
END upper_name;
-- start of executable part of block
BEGIN
-- call the function and display results
DBMS_OUTPUT.PUT_LINE(upper_name (fname, lname));
END;
/
========
Example 4-26 Declaring a Complex Local Procedure in a PL/SQL Block
DECLARE -- declare variables and subprograms
empid NUMBER;
-- declare local procedure for this block
PROCEDURE avg_min_max_sal (empid IN NUMBER) IS
jobid VARCHAR2(10);
avg_sal NUMBER;
min_sal NUMBER;
max_sal NUMBER;
BEGIN
-- determine the job ID for the employee
SELECT job_id INTO jobid FROM employees WHERE employee_id = empid;
-- calculate the average, minimum, and maximum salaries for that job ID
SELECT AVG(salary), MIN(salary), MAX(salary) INTO avg_sal, min_sal, max_sal
FROM employees WHERE job_id = jobid;
-- display data
DBMS_OUTPUT.PUT_LINE ('Employee ID: ' || empid || ' Job ID: ' || jobid);
DBMS_OUTPUT.PUT_LINE ('The average salary for job ID: ' || jobid
|| ' is ' || TO_CHAR(avg_sal));
DBMS_OUTPUT.PUT_LINE ('The minimum salary for job ID: ' || jobid
|| ' is ' || TO_CHAR(min_sal));
DBMS_OUTPUT.PUT_LINE ('The maximum salary for job ID: ' || jobid
|| ' is ' || TO_CHAR(max_sal));
END avg_min_max_sal;
-- end of local procedure
-- start executable part of block
BEGIN
-- call the procedure with several employee IDs
empid := 125;
avg_min_max_sal(empid);
avg_min_max_sal(112);
END;
/
================
Example 4-27 Fetching a Single Row With a Cursor in PL/SQL
DECLARE
-- declare variables for first_name and last_name fetched from the employees table
firstname employees.first_name%TYPE; -- variable for first_name
lastname employees.last_name%TYPE; -- variable for last_name
-- declare a cursor to fetch data from a row (employee 120) in the employees table
CURSOR cursor1 IS
SELECT first_name, last_name FROM employees WHERE employee_id = 120;
BEGIN
OPEN cursor1; -- open the cursor
FETCH cursor1 INTO firstname, lastname; -- fetch data into local variables
DBMS_OUTPUT.PUT_LINE('Employee name: ' || firstname || ' ' || lastname);
CLOSE cursor1; -- close the cursor
END;
/
==========
Example 4-28 Fetching Multiple Rows With a Cursor in PL/SQL
DECLARE
-- declare variables for data fetched from cursors
empid employees.employee_id%TYPE; -- variable for employee_id
jobid employees.job_id%TYPE; -- variable for job_id
lastname employees.last_name%TYPE; -- variable for last_name
rowcount NUMBER;
-- declare the cursors
CURSOR cursor1 IS SELECT last_name, job_id FROM employees
WHERE job_id LIKE '%CLERK';
CURSOR cursor2 is SELECT employee_id, last_name, job_id FROM employees
WHERE job_id LIKE '%MAN' OR job_id LIKE '%MGR';
BEGIN
-- start the processing with cursor1
OPEN cursor1; -- open cursor1 before fetching
DBMS_OUTPUT.PUT_LINE( '---------- cursor 1-----------------' );
LOOP
FETCH cursor1 INTO lastname, jobid; -- fetches 2 columns into variables
-- check the cursor attribute NOTFOUND for the end of data
EXIT WHEN cursor1%NOTFOUND;
-- display the last name and job ID for each record (row) fetched
DBMS_OUTPUT.PUT_LINE( RPAD(lastname, 25, ' ') || jobid );
END LOOP;
rowcount := cursor1%ROWCOUNT;
DBMS_OUTPUT.PUT_LINE('The number of rows fetched is ' || rowcount );
CLOSE cursor1;
-- start the processing with cursor2
OPEN cursor2;
DBMS_OUTPUT.PUT_LINE( '---------- cursor 2-----------------' );
LOOP
-- fetch 3 columns into the variables
FETCH cursor2 INTO empid, lastname, jobid;
EXIT WHEN cursor2%NOTFOUND;
-- display the employee ID, last name, and job ID for each record (row) fetched
DBMS_OUTPUT.PUT_LINE( empid || ': ' || RPAD(lastname, 25, ' ') || jobid );
END LOOP;
rowcount := cursor2%ROWCOUNT;
DBMS_OUTPUT.PUT_LINE('The number of rows fetched is ' || rowcount );
CLOSE cursor2;
END;
/
================
Example 4-29 Passing Parameters to a Cursor in PL/SQL
DECLARE
-- declare variables for data fetched from cursor
empid employees.employee_id%TYPE; -- variable for employee_id
hiredate employees.hire_date%TYPE; -- variable for hire_date
firstname employees.first_name%TYPE; -- variable for first_name
lastname employees.last_name%TYPE; -- variable for last_name
rowcount NUMBER;
bonusamount NUMBER;
yearsworked NUMBER;
-- declare the cursor with a parameter,
CURSOR cursor1 (thismonth NUMBER)IS
SELECT employee_id, first_name, last_name, hire_date FROM employees
WHERE EXTRACT(MONTH FROM hire_date) = thismonth;
BEGIN
-- open and pass a parameter to cursor1, select employees hired on this month
OPEN cursor1(EXTRACT(MONTH FROM SYSDATE));
DBMS_OUTPUT.PUT_LINE('----- Today is ' || TO_CHAR(SYSDATE, 'DL') || ' -----');
DBMS_OUTPUT.PUT_LINE('Employees with yearly bonus amounts:');
LOOP
-- fetches 4 columns into variables
FETCH cursor1 INTO empid, firstname, lastname, hiredate;
-- check the cursor attribute NOTFOUND for the end of data
EXIT WHEN cursor1%NOTFOUND;
-- calculate the yearly bonus amount based on months (years) worked
yearsworked := ROUND( (MONTHS_BETWEEN(SYSDATE, hiredate)/12) );
IF yearsworked > 10 THEN bonusamount := 2000;
ELSIF yearsworked > 8 THEN bonusamount := 1600;
ELSIF yearsworked > 6 THEN bonusamount := 1200;
ELSIF yearsworked > 4 THEN bonusamount := 800;
ELSIF yearsworked > 2 THEN bonusamount := 400;
ELSIF yearsworked > 0 THEN bonusamount := 100;
END IF;
-- display the employee Id, first name, last name, hire date, and bonus
-- for each record (row) fetched
DBMS_OUTPUT.PUT_LINE( empid || ' ' || RPAD(firstname, 21, ' ') ||
RPAD(lastname, 26, ' ') || hiredate || TO_CHAR(bonusamount, '$9,999'));
END LOOP;
rowcount := cursor1%ROWCOUNT;
DBMS_OUTPUT.PUT_LINE('The number of rows fetched is ' || rowcount );
CLOSE cursor1;
END;
/
==============
Example 4-30 Using a Cursor Variable (REF CURSOR)
DECLARE
-- declare a REF CURSOR that returns employees%ROWTYPE (strongly typed)
TYPE emp_refcur_typ IS REF CURSOR RETURN employees%ROWTYPE;
emp_cursor emp_refcur_typ;
-- use the following local procedure to process all the rows after
-- the result set is built, rather than calling a procedure for each row
PROCEDURE process_emp_cv (emp_cv IN emp_refcur_typ) IS
person employees%ROWTYPE;
BEGIN
DBMS_OUTPUT.PUT_LINE('-- Here are the names from the result set --');
LOOP
FETCH emp_cv INTO person;
EXIT WHEN emp_cv%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(person.last_name || ', ' || person.first_name);
END LOOP;
END;
BEGIN
-- find employees whose employee ID is less than 108
OPEN emp_cursor FOR SELECT * FROM employees WHERE employee_id < 108;
process_emp_cv(emp_cursor); -- pass emp_cursor to the procedure for processing
CLOSE emp_cursor;
-- find employees whose last name starts with R
OPEN emp_cursor FOR SELECT * FROM employees WHERE last_name LIKE 'R%';
process_emp_cv(emp_cursor); -- pass emp_cursor to the procedure for processing
CLOSE emp_cursor;
END;
/
=================
Cursor Attributes
Cursor attributes return information about the execution of DML and DDL statements, such INSERT, UPDATE, DELETE, SELECT INTO, COMMIT, or ROLLBACK statements. The cursor attributes are %FOUND, %ISOPEN, %NOTFOUND, and %ROWCOUNT. These attributes return useful information about the most recently executed SQL statement. When using an explicit cursor, add the explicit cursor or cursor variable name to the beginning of the attribute, such as cursor1%FOUND, to return information for the most recently executed SQL statement for that cursor.
The attributes provide the following information:
%FOUND Attribute: Has a Row Been Fetched?
After a cursor or cursor variable is opened but before the first fetch, %FOUND returns NULL. After any fetches, it returns TRUE if the last fetch returned a row, or FALSE if the last fetch did not return a row.
%ISOPEN Attribute: Is the Cursor Open?
If a cursor or cursor variable is open, then %ISOPEN returns TRUE ; otherwise, %ISOPEN returns FALSE.
Note that implicit cursors are automatically opened before and closed after executing the associated SQL statement so %ISOPEN always returns FALSE.
%NOTFOUND Attribute: Has a Fetch Failed?
If the last fetch returned a row, then %NOTFOUND returns FALSE. If the last fetch failed to return a row, then %NOTFOUND returns TRUE. %NOTFOUND is the logical opposite of %FOUND.
%ROWCOUNT Attribute: How Many Rows Fetched So Far?
After a cursor or cursor variable is opened, %ROWCOUNT returns 0 before the first fetch. Thereafter, it returns the number of rows fetched so far. The number is incremented if the last fetch returned a row.
====================
Example 4-31 Declaring and Initializing a PL/SQL Record Type
DECLARE -- declare RECORD type variables
-- the following is a RECORD declaration to hold address information
TYPE location_rec IS RECORD (
room_number NUMBER(4),
building VARCHAR2(25)
);
-- you use the %TYPE attribute to declare the datatype of a table column
-- you can include (nest) a record inside of another record
TYPE person_rec IS RECORD (
employee_id employees.employee_id%TYPE,
first_name employees.first_name%TYPE,
last_name employees.last_name%TYPE,
location location_rec
);
person person_rec; -- declare a person variable of type person_rec
BEGIN
-- insert data in a record, one field at a time
person.employee_id := 20;
person.first_name := 'James';
person.last_name := 'Boynton';
person.location.room_number := 100;
person.location.building:= 'School of Education';
-- display data in a record
DBMS_OUTPUT.PUT_LINE( person.last_name || ', ' || person.first_name );
DBMS_OUTPUT.PUT_LINE( TO_CHAR(person.location.room_number) || ' '
|| person.location.building );
END;
/
=============
Example 4-32 Using %ROWTYPE With a Cursor When Declaring a PL/SQL Record
DECLARE -- declare variables
CURSOR cursor1 IS
SELECT * FROM employees
WHERE department_id = 60; -- declare cursor
-- declare record variable that represents a row fetched from the employees table
-- do not need to use TYPE .. IS RECORD with %ROWTYPE attribute
employee_rec cursor1%ROWTYPE;
BEGIN
-- open the explicit cursor c1 and use it to fetch data into employee_rec
OPEN cursor1;
LOOP
FETCH cursor1 INTO employee_rec; -- retrieve entire row into record
EXIT WHEN cursor1%NOTFOUND;
-- the record contains all the fields for a row in the employees table
-- the following displays the data from the row fetched into the record
DBMS_OUTPUT.PUT_LINE( ' Department ' || employee_rec.department_id
|| ', Employee: ' || employee_rec.employee_id || ' - '
|| employee_rec.last_name || ', ' || employee_rec.first_name );
END LOOP;
CLOSE cursor1;
END;
/
==============
Example 4-33 Using a PL/SQL VARRAY Type With Character Elements
DECLARE -- declare variables
TYPE jobids_array IS VARRAY(20) OF VARCHAR2(10); -- declare VARRAY
jobids jobids_array; -- declare a variable of type jobids_array
howmany NUMBER; -- declare a variable to hold employee count
BEGIN
-- initialize the arrary with some job ID values
jobids := jobids_array('AC_ACCOUNT', 'AC_MGR', 'AD_ASST', 'AD_PRES', 'AD_VP',
'FI_ACCOUNT', 'FI_MGR', 'HR_REP', 'IT_PROG', 'PU_MAN',
'SH_CLERK', 'ST_CLERK', 'ST_MAN');
-- display the current size of the array with COUNT
DBMS_OUTPUT.PUT_LINE('The number of elements (current size) in the array is '
|| jobids.COUNT);
-- display the maximum number of elements for the array LIMIT
DBMS_OUTPUT.PUT_LINE('The maximum number (limit) of elements in the array is '
|| jobids.LIMIT);
-- check whether another element can be added to the array
IF jobids.LIMIT - jobids.COUNT >= 1 THEN
jobids.EXTEND(1); -- add one more element
jobids(14) := 'PU_CLERK'; -- assign a value to the element
END IF;
-- loop through all the varray values, starting
-- with the FIRST and ending with the LAST element
FOR i IN jobids.FIRST..jobids.LAST LOOP
-- determine the number of employees for each job ID in the array
SELECT COUNT(*) INTO howmany FROM employees WHERE job_id = jobids(i);
DBMS_OUTPUT.PUT_LINE ( 'Job ID: ' || RPAD(jobids(i), 10, ' ') ||
' Number of employees: ' || TO_CHAR(howmany));
END LOOP;
-- display the current size of the array with COUNT
DBMS_OUTPUT.PUT_LINE('The number of elements (current size) in the array is '
|| jobids.COUNT);
END;
/
============
Example 4-34 Using a PL/SQL VARRAY Type With Record Type Elements
DECLARE -- declare variables
CURSOR cursor1 IS SELECT * FROM jobs; -- create a cursor for fetching the rows
jobs_rec cursor1%ROWTYPE; -- create a record to hold the row data
-- declare VARRAY with enough elements to hold all the rows in the jobs table
TYPE jobs_array IS VARRAY(25) OF cursor1%ROWTYPE;
jobs_arr jobs_array; -- declare a variable of type jobids_array
howmany NUMBER; -- declare a variable to hold employee count
i NUMBER := 1; -- counter for the number of elements in the array
BEGIN
jobs_arr := jobs_array(); -- initialize the array before using
OPEN cursor1; -- open the cursor before using
LOOP
FETCH cursor1 INTO jobs_rec; -- retrieve a row from the jobs table
EXIT WHEN cursor1%NOTFOUND; -- exit when no data is retrieved
jobs_arr.EXTEND(1); -- add another element to the varray with EXTEND
jobs_arr(i) := jobs_rec; -- assign the fetched row to an element the array
i := i + 1; -- increment the element count
END LOOP;
CLOSE cursor1; -- close the cursor when finished with it
FOR j IN jobs_arr.FIRST..jobs_arr.LAST LOOP -- loop through the varray elements
-- determine the number of employees for each job ID in the array
SELECT COUNT(*) INTO howmany FROM employees WHERE job_id = jobs_arr(j).job_id;
DBMS_OUTPUT.PUT_LINE ( 'Job ID: ' || RPAD(jobs_arr(j).job_id, 11, ' ') ||
RPAD(jobs_arr(j).job_title, 36, ' ') ||
' Number of employees: ' || TO_CHAR(howmany));
END LOOP;
END;
/
==============
Example 4-35 Using Dynamic SQL to Manipulate Data in PL/SQL
DECLARE
sql_stmt VARCHAR2(200); -- variable to hold SQL statement
column_name VARCHAR2(30); -- variable for column name
dept_id NUMBER(4);
dept_name VARCHAR2(30);
mgr_id NUMBER(6);
loc_id NUMBER(4);
BEGIN
-- create a SQL statement (sql_stmt) to execute with EXECUTE IMMEDIATE
-- the statement INSERTs a row into the departments table using bind variables
-- note that there is no semi-colon (;) inside the quotation marks '...'
sql_stmt := 'INSERT INTO departments VALUES (:dptid, :dptname, :mgrid, :locid)';
dept_id := 46;
dept_name := 'Special Projects';
mgr_id := 200;
loc_id := 1700;
-- execute the sql_stmt using the values of the variables in the USING clause
-- for the bind variables
EXECUTE IMMEDIATE sql_stmt USING dept_id, dept_name, mgr_id, loc_id;
-- use EXECUTE IMMEDIATE to delete the row that was previously inserted,
-- substituting for the column name and using a bind variable
column_name := 'DEPARTMENT_ID';
EXECUTE IMMEDIATE 'DELETE FROM departments WHERE ' || column_name || ' = :num'
USING dept_id;
END;
/
===============
Example 4-36 Using Dynamic SQL to Create a Table in PL/SQL
DECLARE
tabname VARCHAR2(30); -- variable for table name
current_date VARCHAR2(8); -- varible for current date
BEGIN
-- extract, format, and insert the year, month, and day from SYSDATE into
-- the current_date variable
SELECT TO_CHAR(EXTRACT(YEAR FROM SYSDATE)) ||
TO_CHAR(EXTRACT(MONTH FROM SYSDATE),'FM09') ||
TO_CHAR(EXTRACT(DAY FROM SYSDATE),'FM09') INTO current_date FROM DUAL;
-- construct the table name with the current date as a suffix
tabname := 'log_table_' || current_date;
-- use EXECUTE IMMEDIATE to create a table with tabname as the table name
EXECUTE IMMEDIATE 'CREATE TABLE ' || tabname ||
'(op_time VARCHAR2(10), operation VARCHAR2(50))' ;
DBMS_OUTPUT.PUT_LINE(tabname || ' has been created');
-- now drop the table
EXECUTE IMMEDIATE 'DROP TABLE ' || tabname;
END;
/
================
Example 4-37 Managing Multiple Errors With a Single PL/SQL Exception Handler
DECLARE -- declare variables
emp_column VARCHAR2(30) := 'last_name';
table_name VARCHAR2(30) := 'emp'; -- set value to raise error
temp_var VARCHAR2(30);
BEGIN
temp_var := emp_column;
SELECT COLUMN_NAME INTO temp_var FROM USER_TAB_COLS
WHERE TABLE_NAME = 'EMPLOYEES' AND COLUMN_NAME = UPPER(emp_column);
-- processing here
temp_var := table_name;
SELECT OBJECT_NAME INTO temp_var FROM USER_OBJECTS
WHERE OBJECT_NAME = UPPER(table_name) AND OBJECT_TYPE = 'TABLE';
-- processing here
EXCEPTION
WHEN NO_DATA_FOUND THEN -- catches all 'no data found' errors
DBMS_OUTPUT.PUT_LINE ('No Data found for SELECT on ' || temp_var);
END;
/
===========
Example 4-38 Determining the Scope of PL/SQL Exceptions
DECLARE
past_due EXCEPTION;
acct_num NUMBER;
BEGIN
DECLARE ---------- subblock begins
past_due EXCEPTION; -- this declaration prevails
acct_num NUMBER;
due_date DATE := SYSDATE - 1; -- set on purpose to raise exception
todays_date DATE := SYSDATE;
BEGIN
IF due_date < todays_date THEN
RAISE past_due; -- this is not handled
END IF;
END; ------------- subblock ends
EXCEPTION
WHEN past_due THEN -- does not handle raised exception
DBMS_OUTPUT.PUT_LINE('Handling PAST_DUE exception.');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Could not recognize PAST_DUE_EXCEPTION in this scope.');
END;
/
============
Example 4-39 Continuing After an Exception in PL/SQL
-- create a temporary table for this example
CREATE TABLE employees_temp AS
SELECT employee_id, salary, commission_pct FROM employees;
DECLARE
sal_calc NUMBER(8,2);
BEGIN
INSERT INTO employees_temp VALUES (303, 2500, 0);
BEGIN -- subblock begins
SELECT salary / commission_pct INTO sal_calc FROM employees_temp
WHERE employee_id = 303;
EXCEPTION
WHEN ZERO_DIVIDE THEN
sal_calc := 2500;
END; -- subblock ends
INSERT INTO employees_temp VALUES (304, sal_calc/100, .1);
EXCEPTION
WHEN ZERO_DIVIDE THEN
NULL;
END;
/
-- view the results
SELECT * FROM employees_temp WHERE employee_id = 303 OR employee_id = 304;
-- drop the temporary table
DROP TABLE employees_temp;
===============
No comments:
Post a Comment