Tuesday, September 29, 2015
Oracle PLSQL Practical Questions
Download
Oracle PLSQL Essentials
Hands-on Guide
Practice for the PLSQL Lessons
EX 02:Declaring PL/SQL Variables……………………………………………….4
EX 03:Writing Executable Statements……………………………………………..6
EX 04:Interacting with the Oracle
Database Server………………………………..11
EX 05:Writing Control Structures………………………………………………….13
EX 06:Working with Composite Data Types………………………………………14
EX 07:Using Explicit Cursors ……………………………………………………..16
EX 08:Handling Exceptions………………………………………………………..20
EX 09:Creating Stored Procedures and
Functions………………………………….22
EX 10:Oracle-Packages…………………………………………………………….22
EX 11:Creating Triggers……………………………………………………………23
Practices
for Lesson 1
Introduction
to PL/SQL
1) Which of the
following PL/SQL blocks execute successfully?
a) BEGIN
END;
b) DECLARE
v_amount INTEGER(10);
END;
c) DECLARE
BEGIN
END;
d) DECLARE
v_amount INTEGER(10);
BEGIN
DBMS_OUTPUT.PUT_LINE(amount);
END;
2) Create and execute
a simple anonymous block that outputs “Hello World.” Execute and save this
script as lab_01_02_soln.sql.
Practices for Lesson 2
1) Identify valid and
invalid identifiers:
a) today
b) last_name
c) today’s_date
d) Number_of_days_in_February_this_year
e) Isleap$year
f) #number
g) NUMBER#
h) number1to7
2) Identify valid and
invalid variable declaration and initialization:
a) number_of_copies
PLS_INTEGER;
b) PRINTER_NAME
constant VARCHAR2(10);
c) deliver_to
VARCHAR2(10):=Johnson;
d) by_when DATE:=
CURRENT_DATE
3) Examine the
following anonymous block, and then select a statement from the following that is true.
DECLARE
v_fname VARCHAR2(20);
v_lname VARCHAR2(15) DEFAULT 'fernandez';
BEGIN
DBMS_OUTPUT.PUT_LINE(v_fname ||' '
||v_lname);
END;
a) The block executes
successfully and prints “fernandez.”
b) The block produces
an error because the fname variable is used without
initializing.
c) The block executes
successfully and prints “null fernandez.”
d) The block produces
an error because you cannot use the DEFAULT keyword to initialize a variable of
type VARCHAR2.
e) The block produces
an error because the v_fname variable is not declared.
4) Modify an existing
anonymous block and save it as a new script.
a) Open the
lab_01_02_soln.sql script, which you created in Practice 1.
b) In the PL/SQL
block, declare the following variables:
1. Variable v_today
of type DATE. Initialize today with SYSDATE.
2. Variable
v_tomorrow of type today. Use the %TYPE attribute to declare
this variable.
c) In the executable
section:
1. Initialize the
v_tomorrow variable with an expression, which calculates
tomorrow’s date (add
one to the value in v_today)
2. Print the value of
v_today and v_tomorrow after printing “Hello World”
d) Save your script
as lab_02_04_soln.sql, and then execute.
The sample output is
as follows (the values of v_today and v_tomorrow will
be different to
reflect your current today’s and tomorrow’s date):
5) Edit the
lab_02_04_soln.sql script.
Add the code to
create two bind variables, named a) b_basic_percent
and
b_pf_percent. Both
bind variables are of type NUMBER.
b) In the executable
section of the PL/SQL block, assign the values 45 and 12 to
b_basic_percent and
b_pf_percent, respectively.
c) Terminate the
PL/SQL block with “/” and display the value of the bind variables
by using the PRINT
command.
d) Execute and save
your script as lab_02_05_soln.sql. The sample output is
as follows:
Practices
for Lesson 3
Writing
Executable Statements
In this practice, you
examine and write executable statements.
DECLARE
v_weight NUMBER(3) :=
600;
v_message
VARCHAR2(255) := 'Product 10012';
BEGIN
DECLARE
v_weight NUMBER(3) :=
1;
v_message
VARCHAR2(255) := 'Product 11001';
v_new_locn
VARCHAR2(50) := 'Europe';
BEGIN
v_weight := v_weight
+ 1;
v_new_locn :=
'Western ' || v_new_locn;
1
END;
v_weight := v_weight
+ 1;
v_message :=
v_message || ' is in stock';
v_new_locn :=
'Western ' || v_new_locn;
2
END;
/
1) Evaluate the
preceding PL/SQL block and determine the data type and value of each
of the following
variables, according to the rules of scoping.
a) The value of
v_weight at position 1 is:
b) The value of
v_new_locn at position 1 is:
c) The value of
v_weight at position 2 is:
d) The value of
v_message at position 2 is:
e) The value of
v_new_locn at position 2 is:
DECLARE
v_customer
VARCHAR2(50) := 'Womansport';
v_credit_rating
VARCHAR2(50) := 'EXCELLENT';
BEGIN
DECLARE
v_customer NUMBER(7)
:= 201;
v_name VARCHAR2(25)
:= 'Unisports';
BEGIN
v_credit_rating
:='GOOD';
…
END;
…
END;
2) In the preceding
PL/SQL block, determine the values and data types for each of the following
cases:
a) The value of v_customer
in the nested block is:
b) The value of
v_name in the nested block is:
c) The value of
v_credit_rating in the nested block is:
d) The value of
v_customer in the main block is:
e) The value of
v_name in the main block is:
f) The value of
v_credit_rating in the main block is:
3) Use the same
session that you used to execute the practices in the lesson titled “Declaring
PL/SQL Variables.” If you have opened a new session, execute lab_02_05_soln.sql.
Then, edit lab_02_05_soln.sql as follows:
a) Use single-line
comment syntax to comment the lines that create the bind
variables, and turn
on SERVEROUTPUT.
b) Use multiple-line
comments in the executable section to comment the lines that assign values to
the bind variables.
c) In the declaration
section:
1. Declare and
initialize two temporary variables to replace the commented
out bind variables
2. Declare two
additional variables: v_fname of type VARCHAR2 and size
15, and v_emp_sal of
type NUMBER and size 10
d) Include the
following SQL statement in the executable section:
SELECT first_name,
salary INTO v_fname, v_emp_sal
FROM employees WHERE
employee_id=110;
e) Change the line
that prints “Hello World” to print “Hello” and the first name.Then, comment the
lines that display the dates and print the bind variables.
f) Calculate the
contribution of the employee towards provident fund (PF).
PF is 12% of the
basic salary, and the basic salary is 45% of the salary. Use local
variables for the
calculation. Try to use only one expression to calculate the PF.Print the
employee’s salary and his or her contribution toward PF.
In this practice, you
examine and write executable statements.
DECLARE
v_weight NUMBER(3) :=
600;
v_message
VARCHAR2(255) := 'Product 10012';
BEGIN
DECLARE
v_weight NUMBER(3) :=
1;
v_message VARCHAR2(255)
:= 'Product 11001';
v_new_locn
VARCHAR2(50) := 'Europe';
BEGIN
v_weight := v_weight
+ 1;
v_new_locn :=
'Western ' || v_new_locn;
1
END;
v_weight := v_weight
+ 1;
v_message :=
v_message || ' is in stock';
v_new_locn :=
'Western ' || v_new_locn;
2
END;
/
1) Evaluate the
preceding PL/SQL block and determine the data type and value of each of the
following variables, according to the rules of scoping.
a) The value of
v_weight at position 1 is:
b) The value of
v_new_locn at position 1 is:
c) The value of
v_weight at position 2 is:
d) The value of
v_message at position 2 is:
e) The value of
v_new_locn at position 2 is:
DECLARE
v_customer
VARCHAR2(50) := 'Womansport';
v_credit_rating
VARCHAR2(50) := 'EXCELLENT';
BEGIN
DECLARE
v_customer NUMBER(7)
:= 201;
v_name VARCHAR2(25)
:= 'Unisports';
BEGIN
v_credit_rating
:='GOOD';
…
END;
…
END;
2) In the preceding
PL/SQL block, determine the values and data types for each of the
following cases:
a) The value of
v_customer in the nested block is:
b) The value of
v_name in the nested block is:
c) The value of
v_credit_rating in the nested block is:
d) The value of
v_customer in the main block is:
e) The value of
v_name in the main block is:
f) The value of
v_credit_rating in the main block is:
3) Use the same session
that you used to execute the practices in the lesson titled
“Declaring PL/SQL
Variables.” If you have opened a new session, execute
lab_02_05_soln.sql.
Then, edit lab_02_05_soln.sql as follows:
a) Use single-line
comment syntax to comment the lines that create the bind
variables, and turn
on SERVEROUTPUT.
b) Use multiple-line
comments in the executable section to comment the lines that
assign values to the
bind variables.
c) In the declaration
section:
1. Declare and
initialize two temporary variables to replace the commented
out bind variables
2. Declare two
additional variables: v_fname of type VARCHAR2 and size
15, and v_emp_sal of
type NUMBER and size 10
d) Include the
following SQL statement in the executable section:
e) Change the line
that prints “Hello World” to print “Hello” and the first name.
Then, comment the
lines that display the dates and print the bind variables.
DBMS_OUTPUT.PUT_LINE('
Hello '|| v_fname);
/*
DBMS_OUTPUT.PUT_LINE('TODAY IS : '|| v_today);
DBMS_OUTPUT.PUT_LINE('TOMORROW
IS : ' || v_tomorrow);*/
...
...
/
--PRINT
b_basic_percent
--PRINT
b_basic_percent
f) Calculate the
contribution of the employee towards provident fund (PF).
PF is 12% of the
basic salary, and the basic salary is 45% of the salary. Use local
variables for the
calculation. Try to use only one expression to calculate the PF.
Print the employee’s
salary and his or her contribution toward PF.
g) Execute and save
your script as lab_03_03_soln.sql. The sample output is
as follows:
Practices
for Lesson 4
Interacting
with the Oracle Server
In this practice, you
use PL/SQL code to interact with the Oracle Server.
1) Create a PL/SQL
block that selects the maximum department ID in the
departments table and
stores it in the v_max_deptno variable. Display the
maximum department
ID.
a) Declare a variable
v_max_deptno of type NUMBER in the declarative section.
b) Start the
executable section with the BEGIN keyword and include a SELECT
statement to retrieve
the maximum department_id from the departments
table.
c) Display
v_max_deptno and end the executable block.
d) Execute and save
your script as lab_04_01_soln.sql. The sample output is
as follows:
2) Modify the PL/SQL
block that you created in step 1 to insert a new department into the
departments table.
a) Load the
lab_04_01_soln.sql script. Declare two variables:
v_dept_name of type
departments.department_name and
v_dept_id of type
NUMBER
Assign 'Education' to
v_dept_name in the declarative section.
b) You have already
retrieved the current maximum department number from the departments table. Add
10 to it and assign the result to v_dept_id.
c) Include an INSERT
statement to insert data into the department_name,
department_id, and
location_id columns of the departments table.
Use values in
dept_name and dept_id for department_name and
department_id,
respectively, and use NULL for location_id.
d) Use the SQL
attribute SQL%ROWCOUNT to display the number of rows that are affected.
e) Execute a SELECT
statement to check whether the new department is inserted.
You can terminate the
PL/SQL block with “/” and include the SELECT statement in your script.
f) Execute and save
your script as lab_04_02_soln.sql. The sample output is
as follows:
3) In step 2, you set
location_id to NULL. Create a PL/SQL block that updates the location_id to 3000
for the new department.
Note:
If you successfully completed step 2, continue with step 3a. If
not, first execute
the solution script
/soln/sol_04_02.sql.
a) Start the
executable block with the BEGIN keyword. Include the UPDATE
statement to set the
location_id to 3000 for the new department (dept_id
=280).
b) End the executable
block with the END keyword. Terminate the PL/SQL block with “/” and include a
SELECT statement to display the department that you updated.
c) Include a DELETE
statement to delete the department that you added.
d) Execute and save
your script as lab_04_03_soln.sql. The sample output is
as follows:
Practices for Lesson 5
Writing Control Structures
In this practice, you create
PL/SQL blocks that incorporate loops and conditional control structures. This
practice tests your understanding of various IF statements and LOOP constructs.
1) Execute the
command in the lab_05_01.sql file to create the messages table.
Write a PL/SQL block
to insert numbers into the messages table.
a) Insert the numbers
1 through 10, excluding 6 and 8.
b) Commit before the
end of the block.
c) Execute a SELECT
statement to verify that your PL/SQL block worked.
2) Execute the
lab_05_02.sql script. This script creates an emp table that is a
replica of the
employees table. It alters the emp table to add a new column,
stars, of VARCHAR2
data type and size 50. Create a PL/SQL block that inserts an
asterisk in the stars
column for every $1000 of an employee’s salary. Save your
script as lab_05_02_soln.sql.
a) In the declarative
section of the block, declare a variable v_empno of type
emp.employee_id and
initialize it to 176. Declare a variable v_asterisk
of type emp.stars and
initialize it to NULL. Create a variable v_sal of type
emp.salary.
b) In the executable
section, write logic to append an asterisk (*) to the string for every $1,000
of the salary. For example, if the employee earns $8,000, the string of
asterisks should contain eight asterisks. If the employee earns $12,500, the string
of asterisks should contain 13 asterisks.
c) Update the stars
column for the employee with the string of asterisks. Commit before the end of
the block.
d) Display the row
from the emp table to verify whether your PL/SQL block has executed
successfully.
e) Execute and save
your script as lab_05_02_soln.sql.
Practices
for Lesson 6
Working with 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)
Declare a variable v_countryid. Assign CA to v_countryid.
c) In the declarative
section, use the %ROWTYPE attribute and declare the
v_country_record
variable of type countries.
d) In the executable
section, get all the information from the countries table by
using v_countryid.
Display selected information about the country. The
sample output is as
follows:
e) You may want to
execute and test the PL/SQL block for countries with the IDs
DE, UK, and US.
2) Create a PL/SQL
block to retrieve the names of some departments from the
departments table and
print each department name on the screen, incorporating an
associative array.
Save the script as lab_06_02_soln.sql.
a) Declare an INDEX
BY table dept_table_type of type
departments.department_name.
Declare a variable my_dept_table
of type
dept_table_type to temporarily store the names of the departments.
b) Declare two
variables: f_loop_count and v_deptno of type NUMBER.
Assign 10 to
f_loop_count and 0 to v_deptno.
c) Using a loop,
retrieve the names of 10 departments and store the names in the
associative array.
Start with department_id 10. Increase v_deptno by 10
for every loop
iteration. The following table shows the department_id for
which you should
retrieve the department_name.
DEPARTMENT_ID
DEPARTMENT_NAME
10 Administration
20 Marketing
30 Purchasing
40 Human Resources
50 Shipping
60 IT
70 Public Relations
80 Sales
90 Executive
100 Finance
d) Using another
loop, retrieve the department names from the associative array and
display them.
e) Execute and save
your script as lab_06_02_soln.sql. The output is as
follows:
3) Modify the block
that you created in Practice 2 to retrieve all information about each department
from the departments table and display the infor mation. Use an
associative array
with the INDEX BY table of records method.
a) Load the
lab_06_02_soln.sql script.
b) You have declared
the associative array to be of type
departments.department_name.
Modify the declaration of the associative array to temporarily store the
number, name, and location of all the Departments. Use the %ROWTYPE attribute.
c) Modify the SELECT
statement to retrieve all department information currently in the departments
table and store it in the associative array.
d) Using another
loop, retrieve the department information from the associative array and
display the information.
Practices
for Lesson 7
Using
Explicit Cursors
In
this practice, you perform two exercises:
•
First, you use an explicit cursor to process a number of rows from a table and
populate
another table with the results using a cursor FOR loop.
•
Second, you write a PL/SQL block that processes information with two cursors,
including
one that uses a parameter.
1)
Create a PL/SQL block to perform the following:
a)
In the declarative section, declare and initialize a variable named v_deptno of
type
NUMBER. Assign a valid department ID value (see table in step d for values).
b)
Declare a cursor named c_emp_cursor, which retrieves the last_name,
salary,
and manager_id of employees working in the depar tment specified
in
v_deptno.
c)
In the executable section, use the cursor FOR loop to operate on the data
retrieved.
If
the salary of the employee is less than 5,000 and if the manager ID is either
101
or
124, display the message “<< last_name>>
Due for a raise.” Otherwise, display
the
message “<< last_name>> Not Due for a raise.”
d)
Test the PL/SQL block for the following cases:
Department ID Message
10
Whalen Due for a raise
20
Hartstein Not Due for a raise
Fay
Not Due for a raise
50
Weiss Not Due for a raise
Fripp
Not Due for a raise
Kaufling
Not Due for a raise
Vollman
Not Due for a raise. . .
.
. .
OConnell
Due for a raise
Grant
Due for a raise
80
Russell Not Due for a raise
Partners
Not Due for a raise
Errazuriz
Not Due for a raise
Cambrault
Not Due for a raise
.
. .
Livingston
Not Due for a raise
Johnson
Not Due for a raise
2)
Next, write a PL/SQL block that declares and uses two cursors—one without a
parameter
and one with a parameter. The first cursor retrieves the department number
and
the department name from the departments table for all departments whose
ID
number is less than 100. The second cursor receives the department number as a
parameter,
and retrieves employee details for those who work in that department and
whose
employee_id is less than 120.
a)
Declare a cursor c_dept_cursor to retrieve department_id and
department_name
for those departments with department_id less than
100.
Order by department_id.
b)
Declare another cursor c_emp_cursor that takes the department number as
parameter
and retrieves the following data from the employees table:
last_name,
job_id, hire_date, and salary of those employees who
work
in that department, with employee_id less than 120.
c)
Declare variables to hold the values retrieved from each cursor. Use the %TYPE
attribute
while declaring variables.
d)
Open c_dept_cursor and use a simple loop to fetch values into the variables
declared.
Display the department number and department name. Use the
appropriate
cursor attribute to exit the loop.
e)
Open c_emp_cursor by passing the current department number as a parameter.
Start
another loop and fetch the values of emp_cursor into variables, and print
all
the details retrieved from the employees table.
Note
•
Check whether c_emp_cursor is already open before opening the cursor.
•
Use the appropriate cursor attribute for the exit condition.
•
When the loop completes, print a line after you have displayed the details of
each
department, and close c_emp_cursor.
f)
End the first loop and close c_dept_cursor. Then end the executable section.
g)
Execute the script. The sample output is as follows:
Practice
7-2: Using Explicit Cursors – Optional
If
you have time, complete the following optional practice. Here, create a PL/SQL
block
that
uses an explicit cursor to determine the top n salaries
of employees.
1)
Run the lab_07-2.sql script to create the top_salaries table for storing the
salaries
of the employees.
2)
In the declarative section, declare the v_num variable of the NUMBER type that
holds
a
number n, representing the number of top n earners
from the employees table.
For
example, to view the top five salaries, enter 5. Declare another variable sal
of
type
employees.salary. Declare a cursor, c_emp_cursor, which retrieves the
salaries
of employees in descending order. Remember that the salaries should not be
duplicated.
3)
In the executable section, open the loop and fetch the top nsalaries, and then insert
them
into the top_salaries table. You can use a simple loop to operate on the
data.
Also, try and use the %ROWCOUNT and %FOUND attributes for the exit condition.
Note: Make
sure that you add an exit condition to avoid having an infinite loop.
4)
After inserting data into the top_salaries table, display the rows with a
SELECT
statement.
The output shown represents the five highest salaries in the employees
table.
5)
Test a variety of special cases such as v_num = 0 or where v_num is greater
than the
number
of employees in the employees table. Empty the top_salaries table
after
each test.
Practices for Lesson 8
Practice
8-1: Handling Predefined Exceptions
In
this practice, you write a PL/SQL block that applies a predefined exception in
order to
process
only one record at a time. The PL/SQL block selects the name of the employee
with
a given salary value.
1)
Execute the command in the lab_05_01.sql file to re-create the messages
table.
2)
In the declarative section, declare two variables: v_ename of type
employees.last_name
and v_emp_sal of type employees.salary.
Initialize
the latter to 6000.
3)
In the executable section, retrieve the last names of employees whose salaries
are
equal
to the value in v_emp_sal. If the salary entered returns only one row, insert
into
the messages table the employee’s name and the salary amount.
Note: Do
not use explicit cursors.
4)
If the salary entered does not return any rows, handle the exception with an
appropriate
exception handler and insert into the messages table the message “No
employee
with a salary of < salary>.”
5)
If the salary entered returns multiple rows, handle the exception with an
appropriate
exception
handler and insert into the messages table the message “More than one
employee
with a salary of < salary>.”
6)
Handle any other exception with an appropriate exception handler and insert
into the
messages
table the message “Some other error occurred.”
7)
Display the rows from the messages table to check whether the PL/SQL block has
executed
successfully. The output is as follows:
8)
Change the initialized value of v_emp_sal to 2000 and re-execute. Output is as
follows:
Practice
8-2: Handling Standard Oracle Server Exceptions
In
this practice, you write a PL/SQL block that declares an exception for the
Oracle
Server
error ORA-02292 (integrity constraint violated – child
record
found). The block tests for the exception and outputs the error message.
1)
In the declarative section, declare an exception e_childrecord_exists.
Associate
the declared exception with the standard Oracle Server error –02292.
2)
In the executable section, display “Deleting department 40....” Include a
DELETE
statement
to delete the department with the department_id 40.
3)
Include an exception section to handle the e_childrecord_exists exception
and
display the appropriate message.
Practices for Lesson 9
Practice 9:
Creating and Using Stored Procedures
In
this practice, you modify existing scripts to create and use stored procedures.
1)
Load the sol_02_04.sql script from the /home/oracle/plsf/soln/ folder.
a)
Modify the script to convert the anonymous block to a procedure called greet.
(Hint: Also
remove the SET SERVEROUTPUT ON command.)
b)
Execute the script to create the procedure. The output results should be as
follows:
c)
Save this script as lab_09_01_soln.sql.
d)
Click the Clear button to clear the workspace.
e)
Create and execute an anonymous block to invoke the greet procedure. ( Hint:
Ensure
that you enable SERVEROUTPUT at the beginning of the block.)
The
output should be similar to the following:
2)
Modify the lab_09_01_soln.sql script as follows:
a)
Drop the greet procedure by issuing the following command:
DROP
PROCEDURE greet;
b)
Modify the procedure to accept an argument of type VARCHAR2. Call the
argument
p_name.
c)
Print Hello < name > (that is, the contents of
the argument) instead of printing
Hello
World.
d)
Save your script as lab_09_02_soln.sql.
e)
Execute the script to create the procedure. The output results should be as
follows:
f)
Create and execute an anonymous block to invoke the greet procedure with a
parameter
value. The block should also produce the output.
Practices for Lesson 11
In this
practice, you create statement and row triggers. You also create procedures
that are invoked from within the triggers.
In this
practice, you create statement and row triggers. You also create procedures
that
are
invoked from within the triggers.
1) The
rows in the JOBS table store a minimum and maximum salary allowed for different
JOB_ID values. You are asked to write code to ensure that employees’salaries
fall in the range allowed for their job type, for insert and update operations.
a) Create
a p rocedure called CHECK_SALARYas follows:
i) The
procedure accepts two parameters, one for an employee’s job ID string and the
other for the salary.
ii) The
procedure uses the job ID to determine the minimum and maximum salary for the
specified job.
iii) If
the salary parameter does not fall within the salar y range of the job,
inclusive of the minimum and maximum, then it should raise an application
exception, with the message “ Invalid salary <sal>. Salaries for job
<jobid> must be between <min> and <max> .”
Replace
the various items in the message with values supplied by parameters and
variables populated by queries. Save the file.
b) Create
a trigger called CHECK_SALARY_TRGon the EMPLOYEESta ble that fires befo re an
INSERT or UPDATEoperation on each row:
i) The
trigger must call the CHECK_SALARYprocedure to carry out the business logic.
ii) The
trigger should pass the new job ID and salary to the procedur e
parameters.
2) Test
the CHECK_SAL_TRGtrigger using the following cases:
a) Using
your EMP_PKG.ADD_EMPLOYEEp rocedure, add employee Eleanor Beh to department 30.
What happens and why?
b) Update
the salary of employee 115 to $2,000. In a separate update operation, change
the employee job ID to HR_REP. What happens in each case?
c) Update
the salary of employee 115 to $2,800. What happens?
3) Update
the CHECK_SALARY_TRGtrigger to fire only when the job ID or salary values have
actually ch anged.
a)
Implement the business rule using a WHENcl ause to check whether the JOB_ID or
SALARYvalues have changed.
Note:Make sure that
the condition handles the NULLin the
OLD.column_name
values if an INSERT operation is performed; otherwise, an insert operation will
fail.
b) Test
the trigger by ex ecuting the EMP_PKG.ADD_EMPLOYEEp rocedure with the
following
parameter values:
-
p_first_name: 'Eleanor'
- p_last
name: 'Beh'
- p_Email:
'EBEH'
- p_Job:
'IT_PROG'
- p_Sal:
5000
c) Update
employees with the IT_PROG job by incrementing their salary by $2,000. What
happens?
d) Update
the salary to $9,000 for Eleanor Beh .
Hint:Use an
UPDATEstatement with a subquery in the WHEREclause. What happens?
e) Change
the job of Eleanor Beh to ST_MANusing another UPDATEstatement with a subquery.
What happens?
4) You are
asked to prevent employees from being deleted during business hours.
a) Write a
statement trigger called DELETE_EMP_TRGon the EMPLOYEESta ble to prevent rows
from being deleted during weekday business hours, which are from 9:00 AM to
6:00 PM.
b) Attempt
to delete employees with JOB_ID of SA_REP who are not assigned to a department.
Hint:This is employee
Grant with ID 178.
Practices for Lesson 11
In this
practice, you create statement and row triggers. You also create procedures
that
are
invoked from within the triggers.
1) The
rows in the JOBS table store a minimum and maximum salary allowed for different
JOB_ID values. You are asked to write code to ensure that employees’salaries
fall in the range allowed for their job type, for insert and update operations.
a) Create
a p rocedure called CHECK_SALARYas follows:
i) The
procedure accepts two parameters, one for an employee’s job ID string and the
other for the salary.
ii) The
procedure uses the job ID to determine the minimum and maximum salary for the
specified job.
iii) If
the salary parameter does not fall within the salar y range of the job, inclusive
of the minimum and maximum, then it should raise an application exception, with
the message “ Invalid salary <sal>. Salaries for job <jobid> must
be between <min> and <max> .”
Replace
the various items in the message with values supplied by parameters and
variables populated by queries. Save the file.
b) Create
a trigger called CHECK_SALARY_TRGon the EMPLOYEESta ble that fires befo re an
INSERT or UPDATEoperation on each row:
i) The
trigger must call the CHECK_SALARYprocedure to carry out the business logic.
ii) The
trigger should pass the new job ID and salary to the procedur e
parameters.
2) Test
the CHECK_SAL_TRGtrigger using the following cases:
a) Using
your EMP_PKG.ADD_EMPLOYEEp rocedure, add employee Eleanor Beh to department 30.
What happens and why?
b) Update
the salary of employee 115 to $2,000. In a separate update operation, change
the employee job ID to HR_REP. What happens in each case?
c) Update
the salary of employee 115 to $2,800. What happens?
3) Update
the CHECK_SALARY_TRGtrigger to fire only when the job ID or salary values have
actually ch anged.
a)
Implement the business rule using a WHENcl ause to check whether the JOB_ID or
SALARYvalues have changed.
Note:Make sure that
the condition handles the NULLin the
OLD.column_name
values if an INSERT operation is performed; otherwise, an insert operation will
fail.
b) Test
the trigger by ex ecuting the EMP_PKG.ADD_EMPLOYEE procedure with the
following
parameter values:
-
p_first_name: 'Eleanor'
- p_last
name: 'Beh'
- p_Email:
'EBEH'
- p_Job:
'IT_PROG'
- p_Sal:
5000
c) Update
employees with the IT_PROG job by incrementing their salary by $2,000. What
happens?
d) Update
the salary to $9,000 for Eleanor Beh .
Hint:Use an
UPDATEstatement with a subquery in the WHEREclause. What happens?
e) Change
the job of Eleanor Beh to ST_MANusing another UPDATEstatement with a subquery.
What happens?
4) You are
asked to prevent employees from being deleted during business hours.
a) Write a
statement trigger called DELETE_EMP_TRGon the EMPLOYEESta ble to prevent rows
from being deleted during weekday business hours, which are from 9:00 AM to
6:00 PM.
b) Attempt
to delete employees with JOB_ID of SA_REP who are not assigned to a department.
Hint:This is employee
Grant with ID 178.
Subscribe to:
Posts (Atom)