Practice Exercise for PL/SQL Block
1. Create a PL/SQL block that computes the commission amount for a given employee based
on the employee’s salary.
a. Use the DEFINE command to provide the employee ID.
DEFINE p_empno = 100
b. If the employee’s salary is less than $5,000, display the bonus amount for the employee
as 10% of the salary.
c. If the employee’s salary is between $5,000 and $10,000, display the bonus amount for the
employee as 15% of the salary.
d. If the employee’s salary exceeds $10,000, display the bonus amount for the employee as
20% of the salary.
e. If the employee’s salary is NULL, display the bonus amount for the employee as 0.
f. Test the PL/SQL block for each case using the following test cases, and check each bonus
amount.
DECLARE
v_empno employees.employee_id%TYPE := &p_empno;
v_sal [Link]%TYPE;
v_bonus_per NUMBER(7,2);
v_bonus NUMBER(7,2);
BEGIN
SELECT salary
INTO v_sal
FROM employees
WHERE employee_id = v_empno;
IF v_sal < 5000 THEN
v_bonus_per := .10;
ELSIF v_sal BETWEEN 5000 and 10000 THEN
v_bonus_per := .15;
ELSIF v_sal > 10000 THEN
v_bonus_per := .20;
ELSE
v_bonus_per := 0;
END IF;
v_bonus := v_sal * v_bonus_per;
DBMS_OUTPUT.PUT_LINE ('The bonus for the employee with employee_id '
|| v_empno || ' and salary ' || v_sal || ' is ' || v_bonus);
END;
/
2. Create a PL/SQL block to retrieve the name of each department from the DEPARTMENTS table and
print each department name on the screen, incorporating an INDEX BY table. Save the code in a
file called [Link] by clicking the Save Script button. Save the script with a .sql extension.
a. Declare an INDEX BY table, MY_DEPT_TABLE, to temporarily store the name of the
departments.
b. Using a loop, retrieve the name of all departments currently in the DEPARTMENTS table
and store them in the INDEX BY table. Use the following table to assign the value for
DEPARTMENT_ID based on the value of the counter used in the loop.
COUNTER DEPARTMENT_ID
1 10
2 20
3 50
4 60
5 70
6 80
7 90
c. Using another loop, retrieve the department names from the PL/SQL table and print them
to the screen, using DBMS_OUTPUT.PUT_LINE
DECLARE
TYPE DEPT_TABLE_TYPE IS
TABLE OF departments.department_name%TYPE
INDEX BY BINARY_INTEGER;
my_dept_table dept_table_type;
v_count NUMBER (2);
v_deptno departments.department_id%TYPE;
BEGIN
SELECT COUNT(*) INTO v_count FROM departments;
FOR i IN 1..v_count
LOOP
IF i = 1 THEN
v_deptno := 10;
ELSIF i = 2 THEN
v_deptno := 20;
ELSIF i = 3 THEN
v_deptno := 50;
ELSIF i = 4 THEN
v_deptno := 60;
ELSIF i = 5 THEN
v_deptno := 80;
ELSIF i = 6 THEN
v_deptno := 90;
ELSIF i = 7 THEN
v_deptno := 110;
END IF;
Practice Exercise for Composite Data types
1. Write a PL/SQL block to print information about a given country.
a. Declare a PL/SQL record based on the structure of the COUNTRIES table.
b. Use the DEFINE command to provide the country ID.
DEFINE p_countryid = CA
c. Use DBMS_OUTPUT.PUT_LINE to print selected information about the country. A sample output is
shown below.
DECLARE
country_record countries%ROWTYPE;
BEGIN
SELECT *
INTO country_record
FROM countries
WHERE country_id = UPPER('&p_countryid');
DBMS_OUTPUT.PUT_LINE ('Country Id: ' ||
country_record.country_id || ' Country Name: ' ||
country_record.country_name || ' Region: ' ||
country_record.region_id);
END;
/
d. Execute and test the PL/SQL block for the countries with the IDs CA, DE, UK, US.
2. Create a PL/SQL block to retrieve the name of each department from the DEPARTMENTS table
and print each department name on the screen, incorporating an INDEX BY table. Save the code
in a file called [Link] by clicking the Save Script button. Save the script with a .sql
extension.
a) Declare an INDEX BY table, MY_DEPT_TABLE, to temporarily store the name of the departments.
b) Using a loop, retrieve the name of all departments currently in the DEPARTMENTS table and store
them in the INDEX BY table. Use the following table to assign the value for DEPARTMENT_ID based
on the value of the counter used in the loop.
COUNTER DEPARTMENT_ID
1 10
2 20
3 50
4 60
5 80
6 90
7 110
c. Using another loop, retrieve the department names from the INDEX BY table and print them to the
screen, using DBMS_OUTPUT.PUT_LINE. The output from the program is shown on the next page
DECLARE
TYPE DEPT_TABLE_TYPE IS
TABLE OF departments.department_name%TYPE
INDEX BY BINARY_INTEGER;
my_dept_table dept_table_type;
v_count NUMBER (2);
v_deptno departments.department_id%TYPE;
BEGIN
SELECT COUNT(*) INTO v_count FROM departments;
FOR i IN 1..v_count
LOOP
IF i = 1 THEN
v_deptno := 10;
ELSIF i = 2 THEN
v_deptno := 20;
ELSIF i = 3 THEN
v_deptno := 50;
ELSIF i = 4 THEN
v_deptno := 60;
ELSIF i = 5 THEN
v_deptno := 80;
ELSIF i = 6 THEN
v_deptno := 90;
ELSIF i = 7 THEN
v_deptno := 110;
END IF;
SELECT department_name INTO my_dept_table(i) FROM departments
WHERE department_id = v_deptno;
END LOOP;
FOR i IN 1..v_count
LOOP
DBMS_OUTPUT.PUT_LINE (my_dept_table(i));
END LOOP;
END;
Practice Exercise for Cursors
[Link] a new table for storing the salaries of the employees.
CREATE TABLE top_dogs
( salary NUMBER(8,2));
2. Create a PL/SQL block that determines the top employees with respect to salaries.
a. Accept a number n from the user where n represents the number of top n earners from the
EMPLOYEES table. For example, to view the top five earners, enter 5.
Note: Use the DEFINE command to provide the value for n.
DELETE FROM top_dogs;
DEFINE p_num = 5
b. Gather the salaries of the top n people from the EMPLOYEES table. There should be no duplication
in the salaries. If two employees earn the same salary, the salary should be picked up
only once.
c. Store the salaries in the TOP_DOGS table.
d. Test a variety of special cases, such as n = 0 or where n is greater than the number
of employees in the EMPLOYEES table. Empty the TOP_DOGS table after each test. The output shown
represents the five highest salaries in the EMPLOYEES table
DECLARE
v_num NUMBER(3) := &p_num;
v_sal [Link]%TYPE;
CURSOR emp_cursor IS
SELECT distinct salary
FROM employees
ORDER BY salary DESC;
BEGIN
OPEN emp_cursor;
FETCH emp_cursor INTO v_sal;
WHILE emp_cursor%ROWCOUNT <= v_num AND emp_cursor%FOUND LOOP
INSERT INTO top_dogs (salary)
VALUES (v_sal);
FETCH emp_cursor INTO v_sal;
END LOOP;
CLOSE emp_cursor;
COMMIT;
END;
/
SELECT * FROM top_dogs:
Practice Exercise for Procedure
1. Create and invoke the ADD_JOB procedure and consider the results.
a. Create a procedure called ADD_JOB to insert a new job into the JOBS table. Provide
the ID and title of the job, using two parameters.
CREATE OR REPLACE PROCEDURE add_job
(p_jobid IN jobs.job_id%TYPE,
p_jobtitle IN jobs.job_title%TYPE)
IS
BEGIN
INSERT INTO jobs (job_id, job_title)
VALUES (p_jobid, p_jobtitle);
COMMIT;
END add_job;
b. Compile the code, and invoke the procedure with IT_DBA as job ID and
Database Administratoras job title. Query the JOBStable to view the
results.
EXECUTE add_job ('IT_DBA', 'Database Administrator')
SELECT * FROM jobs WHERE job_id = 'IT_DBA';
c. Invoke your procedure again, passing a job ID of ST_MAN and a job title of Stock
Manager. What happens and why?
EXECUTE add_job ('ST_MAN', 'Stock Manager')
There is a primary key integrity constraint on the JOB_ID column.
2. Create a procedure called UPD_JOB to modify a job in the JOBS table.
a. Create a procedure called UPD_JOB to update the job title. Provide the job ID and
a new title, using two parameters. Include the necessary exception handling if no
update occurs.
CREATE OR REPLACE PROCEDURE upd_job
(p_jobid IN jobs.job_id%TYPE,
p_jobtitle IN jobs.job_title%TYPE)
IS
BEGIN
UPDATE jobs
SET job_title = p_jobtitle
WHERE job_id = p_jobid;
IF SQL%NOTFOUND THEN
RAISE_APPLICATION_ERROR(-20202,'No job updated.');
END IF;
END upd_job;
/
b. Compile the code; invoke the procedure to change the job title of the job ID IT_DBA
to Data Administrator. Query the JOBS table to view the results. Also check the
exception handling by trying to update a job that does not exist (you can use job ID
IT_WEB and job title Web Master).
EXECUTE upd_job ('IT_DBA', 'Data Administrator')
SELECT * FROM jobs WHERE job_id = 'IT_DBA';
EXECUTE upd_job ('IT_WEB', 'Web Master')
3. Create a procedure called DEL_JOB to delete a job from the JOBS table.
a. Create a procedure called DEL_JOB to delete a job from the JOBStable.
Include the necessary exception handling if no job is deleted.
CREATE OR REPLACE PROCEDURE del_job
(p_jobid IN jobs.job_id%TYPE)
IS
BEGIN
DELETE FROM jobs
WHERE job_id = p_jobid;
IF SQL%NOTFOUND THEN
RAISE_APPLICATION_ERROR(-20203,'No jobs deleted.');
END IF;
END DEL_JOB;
/
b. Compile the code; invoke the procedure using job ID IT_DBA. Query the
JOBStable to view the results.
EXECUTE del_job ('IT_DBA')
SELECT * FROM jobs WHERE job_id = 'IT_DBA';
4. Create a procedure called QUERY_EMPto query the EMPLOYEEStable, retrieving the
salary and job ID for an employee when provided with the employee ID.
a. Create a procedure that returns a value from the SALARYand JOB_ID
columns for a specified employee ID. Use host variables for the two
OUTparameters salary and job ID.
CREATE OR REPLACE PROCEDURE query_emp
(p_empid IN employees.employee_id%TYPE,
p_sal OUT [Link]%TYPE,
p_job OUT employees.job_id%TYPE)
IS
BEGIN
SELECT salary, job_id
INTO p_sal, p_job
FROM employees
WHERE employee_id = p_empid;
END query_emp;
/
b. Compile the code, invoke the procedure to display the salary and job ID for employee
ID 120.
VARIABLE g_title VARCHAR2(30)
EXECUTE :g_title := q_job ('SA_REP')
PRINT g_title
Practice Exercise for Function
1. Create and invoke the Q_JOB function to return a job title.
a. Create a function called Q_JOB to return a job title to a host variable.
CREATE OR REPLACE FUNCTION q_job
(p_jobid IN jobs.job_id%TYPE)
RETURN VARCHAR2
IS
v_jobtitle jobs.job_title%TYPE;
BEGIN
SELECT job_title
INTO v_jobtitle
FROM jobs
WHERE job_id = p_jobid;
RETURN (v_jobtitle);
END q_job;
/
b. Compile the code; create a host variable G_TITLE and invoke the function with job ID
SA_REP. Query the host variable to view the result.
VARIABLE g_title VARCHAR2(30)
EXECUTE :g_title := q_job ('SA_REP')
PRINT g_title
2. Create a function called ANNUAL_COMP to return the annual salary by accepting two
parameters: an employee’s monthly salary and commission. The function should address
NULL values.
a. Create and invoke the function ANNUAL_COMP, passing in values for monthly
salary and commission. Either or both values passed can be NULL, but the function
should still return an annual salary, which is not NULL. The annual salary is defined
by the basic formula:
(salary*12) + (commission_pct*salary*12)
CREATE OR REPLACE FUNCTION annual_comp
(p_sal IN [Link]%TYPE,
p_comm IN employees.commission_pct%TYPE)
RETURN NUMBER
IS
BEGIN
RETURN (NVL(p_sal,0) * 12 + (NVL(p_comm,0)* p_sal * 12));
END annual_comp;
/
b. Use the function in a SELECT statement against the EMPLOYEES table for
department 80.
SELECT employee_id, last_name, annual_comp(salary,commission_pct)
"Annual Compensation"
FROM employees
WHERE department_id=80;
3. Create a procedure NEW_EMP, to insert a new employee into the EMPLOYEES table. The
procedure should contain a call to the VALID_DEPTID function to check whether the
department ID specified for the new employee exists in the DEPARTMENTS table.
a. Create the function VALID_DEPTID to validate a specified department ID. The
function should return a BOOLEAN value.
CREATE OR REPLACE FUNCTION valid_deptid
(p_deptid IN departments.department_id%TYPE)
RETURN BOOLEAN
IS
v_dummy VARCHAR2(1);
BEGIN
SELECT 'x'
INTO v_dummy
FROM departments
WHERE department_id = p_deptid;
RETURN (TRUE);
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN (FALSE);
END valid_deptid;
/
b. Create the procedure NEW_EMP to add an employee to the EMPLOYEES table. A new
row should be added to the EMPLOYEES table if the function returns TRUE. If the
function returns FALSE, the procedure should alert the user with an appropriate message.
Define default values for most parameters. The default commission is 0, the default salary is
1000, the default department number is 30, the default job is SA_REP, and the default
manager ID is 145. For the employee’s ID, use the sequence EMPLOYEES_SEQ. Provide
the last name, first name, and e-mail address of the employee
CREATE OR REPLACE PROCEDURE new_emp
(p_lname employees.last_name%TYPE,
p_fname employees.first_name%TYPE,
p_email [Link]%TYPE,
p_job employees.job_id%TYPE DEFAULT 'SA_REP',
p_mgr employees.manager_id%TYPE DEFAULT 145,
p_sal [Link]%TYPE DEFAULT 1000,
p_comm employees.commission_pct%TYPE DEFAULT 0,
p_deptid employees.department_id%TYPE DEFAULT 30)
IS
BEGIN
IF valid_deptid(p_deptid) THEN
INSERT INTO employees(employee_id, last_name, first_name,
email, job_id, manager_id, hire_date,
salary, commission_pct, department_id)
VALUES (employees_seq.NEXTVAL, p_lname, p_fname, p_email,
p_job, p_mgr, TRUNC (SYSDATE, 'DD'), p_sal,
p_comm, p_deptid);
ELSE
RAISE_APPLICATION_ERROR (-20204,
'Invalid department ID. Try again.');
END IF;
END new_emp;
/
c. Test your NEW_EMP procedure by adding a new employee named Jane Harris to
department 15. Allow all other parameters to default. What was the result?
EXECUTE new_emp(p_lname=>'Harris', p_fname=>'Jane',
p_email=>'JAHARRIS', p_deptid => 15)
Practice for Package:
1. Create a package specification and body called JOB_PACK. (You can save the package body and
specification in two separate files.) This package contains your ADD_JOB, UPD_JOB, and DEL_JOB
procedures, as well as your Q_JOB function.
Note: Use the code in your previously saved script files when creating the package.
a. Make all the constructs public.
Note: Consider whether you still need the stand-alone procedures and functions you just packaged.
CREATE OR REPLACE PACKAGE job_pack IS
PROCEDURE add_job
(p_jobid IN jobs.job_id%TYPE,
p_jobtitle IN jobs.job_title%TYPE);
PROCEDURE upd_job
(p_jobid IN jobs.job_id%TYPE,
p_jobtitle IN jobs.job_title%TYPE);
PROCEDURE del_job
(p_jobid IN jobs.job_id%TYPE);
FUNCTION q_job
(p_jobid IN jobs.job_id%TYPE)
RETURN VARCHAR2;
END job_pack;
CREATE OR REPLACE PACKAGE BODY job_pack IS
PROCEDURE add_job
(p_jobid IN jobs.job_id%TYPE,
p_jobtitle IN jobs.job_title%TYPE)
IS
BEGIN
INSERT INTO jobs (job_id, job_title)
VALUES (p_jobid, p_jobtitle);
END add_job;
PROCEDURE upd_job
(p_jobid IN jobs.job_id%TYPE,
p_jobtitle IN jobs.job_title%TYPE)
IS
BEGIN
UPDATE jobs
SET job_title = p_jobtitle
WHERE job_id = p_jobid;
IF SQL%NOTFOUND THEN
RAISE_APPLICATION_ERROR(-20202,'No job updated.');
END IF;
END upd_job;
PROCEDURE del_job
(p_jobid IN jobs.job_id%TYPE)
IS
BEGIN
DELETE FROM jobs
WHERE job_id = p_jobid;
IF SQL%NOTFOUND THEN
RAISE_APPLICATION_ERROR (-20203,'No job deleted.');
END IF;
END del_job;
FUNCTION q_job
(p_jobid IN jobs.job_id%TYPE)
RETURN VARCHAR2
IS
v_jobtitle jobs.job_title%TYPE;
BEGIN
SELECT job_title
INTO v_jobtitle
FROM jobs
WHERE job_id = p_jobid;
RETURN (v_jobtitle);
END q_job;
END job_pack;
/
b. Invoke your ADD_JOB procedure by passing values IT_SYSAN and SYSTEMS ANALYST as
parameters.
EXECUTE job_pack.add_job('IT_SYSAN', 'Systems Analyst')
SELECT * FROM jobs
WHERE job_id = 'IT_SYSAN';
2. Create and invoke a package that contains private and public constructs.
a. Create a package specification and package body called EMP_PACK that contains your
NEW_EMP procedure as a public construct, and your VALID_DEPTID function as a private
construct. (You can save the specification and body into separate files.)
CREATE OR REPLACE PACKAGE emp_pack IS
PROCEDURE new_emp
(p_lname employees.last_name%TYPE,
p_fname employees.first_name%TYPE,
p_email [Link]%TYPE,
p_job employees.job_id%TYPE DEFAULT 'SA_REP',
p_mgr employees.manager_id%TYPE DEFAULT 145,
p_sal [Link]%TYPE DEFAULT 1000,
p_comm employees.commission_pct%TYPE DEFAULT 0,
p_deptid employees.department_id%TYPE DEFAULT 80);
END emp_pack;
CREATE OR REPLACE PACKAGE BODY emp_pack IS
FUNCTION valid_deptid
(p_deptid IN departments.department_id%TYPE)
RETURN BOOLEAN
IS
v_dummy VARCHAR2(1);
BEGIN
SELECT 'x'
INTO v_dummy
FROM departments
WHERE department_id = p_deptid;
RETURN (TRUE);
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN(FALSE);
END valid_deptid;
PROCEDURE new_emp
(p_lname employees.last_name%TYPE,
p_fname employees.first_name%TYPE,
p_email [Link]%TYPE,
p_job employees.job_id%TYPE DEFAULT 'SA_REP',
p_mgr employees.manager_id%TYPE DEFAULT 145,
p_sal [Link]%TYPE DEFAULT 1000,
p_comm employees.commission_pct%TYPE DEFAULT 0,
p_deptid employees.department_id%TYPE DEFAULT 80)
IS
BEGIN
IF valid_deptid(p_deptid) THEN
INSERT INTO employees (employee_id, last_name, first_name,
email, job_id, manager_id, hire_date, salary, commission_pct,
department_id)
VALUES (employees_seq.NEXTVAL, p_lname, p_fname, p_email,
p_job, p_mgr, TRUNC (SYSDATE, 'DD'), p_sal, p_comm,
p_deptid);
ELSE
RAISE_APPLICATION_ERROR (-20205,
'Invalid department number. Try again.');
END IF;
END new_emp;
END emp_pack;
/
b. Invoke the NEW_EMP procedure, using 15 as a department number. Because the
department ID 15 does not exist in the DEPARTMENTS table, you should get an error
message as specified in the exception handler of your procedure.
Invoke the NEW_EMP procedure, using an existing department ID 80.
EXECUTE emp_pack.new_emp(p_lname=>'Harris',p_fname=>'Jane',
p_email=>'JAHARRIS', p_deptid => 15)
Practice Exercise for Trigger
1. Changes to data are allowed on tables only during normal office hours of 8:45 a.m. until 5:30
p.m., Monday through Friday.
Create a stored procedure called SECURE_DML that prevents the DML statement from
executing outside of normal office hours, returning the message, “You may only make
changes during normal office hours.”
CREATE OR REPLACE PROCEDURE secure_dml
IS
BEGIN
IF TO_CHAR (SYSDATE, 'HH24:MI') NOT BETWEEN '08:45' AND '17:30'
OR TO_CHAR (SYSDATE, 'DY') IN ('SAT', 'SUN')
THEN RAISE_APPLICATION_ERROR (-20205,
'You may make changes only during normal office hours');
END IF;
END secure_dml;
/
a. Create a statement trigger on the JOBS table that calls the above procedure.
CREATE OR REPLACE TRIGGER secure_prod
BEFORE INSERT OR UPDATE OR DELETE ON jobs
BEGIN
secure_dml;
END secure_prod;
b. Test the procedure by temporarily modifying the hours in the procedure and attempting to
insert a new record into the JOBS table. (Example: replace 08:45 with 16:45; This attempt
results in an error message)
After testing, reset the procedure hours asspecified in question 1 and recreate the
procedure as in question 1 above.
INSERT INTO jobs (job_id, job_title)
VALUES ('HR_MAN', 'Human Resources Manager');
If you have time:
3. Employees should receive an automatic increase in salary if the minimum salary for a job is
increased. Implement this requirement through a trigger on the JOBS table.
a. Create a stored procedure named UPD_EMP_SAL to update the salary amount. This procedure accepts
two parameters: the job ID for which salary has to be updated, and the
new minimum salary for this job ID. This procedure is executed from the trigger on the
JOBS table.
CREATE OR REPLACE PROCEDURE upd_emp_sal
(p_jobid IN employees.job_id%TYPE,
p_minsal IN [Link]%TYPE)
IS
BEGIN
UPDATE employees
SET salary = p_minsal
WHERE job_id = p_jobid
AND SALARY < p_minsal;
END upd_emp_sal
b. Create a row trigger named UPDATE_EMP_SALARY on the JOBS table that invokes the
procedure UPD_EMP_SAL, when the minimum salary in the JOBS table is updated for a
specified job ID.
CREATE OR REPLACE TRIGGER update_emp_salary
AFTER UPDATE OF min_salary ON jobs
FOR EACH ROW
BEGIN
upd_emp_sal(:NEW.job_id, :NEW.min_salary);
END;
c. Query the EMPLOYEES table to see the current salary for employees who are
programmers
SELECT last_name, first_name, salary
FROM employees
WHERE job_id = 'IT_PROG';
d. Increase the minimum salary for the Programmer job from 4,000 to 5,000.
UPDATE jobs
SET min_salary = 5000
WHERE job_id = 'IT_PROG';
e. Employee Lorentz (employee ID 107) had a salary of less than 4,500. Verify that her
salary has been increased to the new minimum of 5,000.
SELECT last_name, first_name, salary
FROM employees
WHERE employee_id = 107;