CHAPTER 3
PROCEDURES, FUNCTIONS, TRIGGERS AND PACKAGES
------------------------------------------------------------------------------------------------------------
OBJECTIVES
In this chapter, we will briefly cover the following topics:
- Creating procedures
- Passing parameters and getting values out.
- Dropping procedures
- Creating Functions
- Return statement
- Dropping Functions
- Creating package specifiacations
- Creating package bodies
- Calling stored packages
- Cursors in packages
- Advantages of packages
- What is a trigger
- Parts of a trigger
- Before and After triggers
- Statement and Row triggers
- Enabling and Disabling Triggers
- Dropping Triggers
PROCEDURES AND FUNCTIONS
A procedure
or function is a named PL/SQL block. Procedures and functions are normally
stored in the database within package specifications (which is a wrapper for a
group of named blocks), although they may also be stored in the database
individually. A procedure or function is parsed or compiled at the time it is
stored. As a result, they tend to run faster compared to non-procedural SQL
scripts, which require extra time for compilation.
A procedure
or function can be invoked from most Oracle tools like SQL*Plus, or other
programming languages like C++ and JAVA.
Benefits of
using procedures and functions include:
- Improved data security and integrity.
·
Control
indirect access to objects from non-privileged users.
·
Ensure
that related actions are performed together, or not at all, by funnelling
actions for related tables through a single path.
- Improved performance.
·
Avoid
reparsing for multiple users by exploiting shared SQL.
·
Avoid
PL/SQL parsing at run time by parsing at compile time.
·
Reduce
the number of calls to the database and decrease network traffic by bundling
commands.
- Improved maintenance.
·
Modify
routines online without interfering with other users.
·
Modify
one routine to affect multiple applications.
·
Modify
one routine to eliminate duplicate testing.
PROCEDURES
Procedures
are simply named PL/SQL blocks. They are created and owned by a particular
schema. Like the DML functions, right to execute a procedure can be granted /
revoked.
Syntax
for creating a procedure:
CREATE OR
REPLACE PROCEDURE <procedure name> (<parameter1 name> <mode>
<data type>, <parameter1
name> <mode> <data type>,
…) IS
<Variable declarations>
BEGIN
Executable Commands
EXCEPTION
Exception handlers
END.
OR REPLACE
- This option re-creates the procedure, maintaining the privileges previously
granted.
Parameter
list - If a procedure contains more than one parameter, commas should be used
to separate them. You cannot specify a constraint on the data type.
For example, it is illegal to do the following:
CREATE
OR REPLACE PROCEDURE pro_sample(emp_ssn NUMBER(2), …..)
Instead,
you should do this:
CREATE
OR REPLACE PROCEDURE pro_sample(emp_ssn NUMBER, …..)
Parameters
can have three modes:
Mode |
Description |
|
IN
|
This type
of parameter is passed to the procedure as a read-only value that cannot be
changed within the procedure.
|
|
OUT
|
This type
of parameter is write-only, and can only appear on the left side of an
assignment statement in the procedure.
|
|
IN OUT
|
Combination
of IN and OUT; the parameter of this mode is passed to the procedure, and its
value can be changed within the procedure.
|
Variable
declarations - Constants, variables, other procedures and local functions are
declared in this section. Unlike the parameter list, you must specify
the constraint of the data type of a local variable.
Executable
commands - The commands to be executed by the procedure are placed here.
Exception
handlers - Any error handling statements that should be caught while executing
the procedure are placed here.
A procedure has
two parts: the specification and the body. The procedure
specification begins with the keyword PROCEDURE and ends with the procedure
name or a parameter list. Parameter declarations are optional. Procedures that
take no parameters are written without parentheses.
The procedure
body begins with the keyword IS and ends with the keyword END
followed by an optional procedure name. The procedure body has three parts: a declarative
part, an executable part, and an optional exception-handling
part.
The declarative
part contains some local declarations, which are placed between the keywords IS
and BEGIN. The keyword DECLARE, which introduces declarations in an anonymous
PL/SQL block, is not used.
The executable
part can hold one or more statements, which are placed between the keywords
BEGIN and EXCEPTION (or END). At least one statement must appear in the executable
part of a procedure. The NULL statement meets this requirement.
The
exception-handling part contains the exception handlers, which should be placed
between the keywords EXCEPTION and END.
Before executing a procedure, you need to compile and load it to a
schema. In other words, you have to make it “available” in your session. To do
so, run the following statement at the SQL prompt:
Syntax for compiling a procedure:
SQL>@<SQL filename>
or
SQL>start <SQL filename>
in which <SQL filename> is an .sql file which contains your
procedure.
Notice that it is also the same command to run an unnamed block. Bear in
mind that the filename needs NOT be the same as the procedure name. An .sql
file ONLY contains the code for the procedure. If it compiles correctly, it is
the procedure that is loaded to the database, not the .sql file. Therefore, in
order to run a procedure at a SQL prompt, you need to execute the procedure,
not the .sql file (We will discuss further about later).
If you are prompted with a warning message, type the following command to
check the details about the error:
SQL> show errors;
After you compile a procedure, you can execute it.
Syntax for executing a procedure:
SQL>
EXECUTE <procedure name> or EXEC <procedure name>
Let’s look
at the following unnamed block:
|
SET
SERVEROUTPUT ON
DECLARE
temp_emp_sal NUMBER(10,2);
BEGIN
SELECT emp_salary
INTO temp_emp_sal
FROM employee
WHERE emp_ssn = '999666666';
IF temp_emp_sal > 4000 THEN
DBMS_OUTPUT.PUT_LINE('Salary >
4000');
ELSE
DBMS_OUTPUT.PUT_LINE('Salary <
4000');
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Employee not
found ');
END;
/
|
Every time
it is executed, it has to be parsed first. If we store this block in the
database, we only need to parse or compile it once (the first time), unless we
make some changes, or drop it. The following is an example of a procedure:
|
--
pro1.sql
-- Note
that the file name and the procedure name are different.
SET
SERVEROUTPUT ON
CREATE OR
REPLACE PROCEDURE myproc1 IS
temp_emp_sal NUMBER(10,2); -- local variable, and its constraint is
required..
BEGIN
SELECT emp_salary
INTO temp_emp_sal
FROM employee
WHERE emp_ssn = '999666666';
IF temp_emp_sal > 4000 THEN
DBMS_OUTPUT.PUT_LINE('Salary >
4000');
ELSE
DBMS_OUTPUT.PUT_LINE('Salary <
4000');
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Employee not
found ');
END myproc1;
/
|
The keyword
CREATE OR REPLACE directs the Oracle to create a new procedure, or replace it
if a procedure with same name already exists in the same schema. OR REPLACE is
optional. If the OR REPLACE is omitted, you have to drop the procedure and load
it back again every time you change it.
The
following gives you an example of how to compile and execute the above
procedure, followed by its output:
|
SQL>
@pro1
Procedure
created.
SQL>
exec myproc1
Salary
> 4000
PL/SQL
procedure successfully completed.
|
The next
thing to cover here is how to get values into and out of the procedure, we can
do this by defining variables in the implicit declaration section as IN, OUT or
IN OUT. As explained above, an IN variable is an input variable. An OUT
variable is an output variable, or a variable used to hold the value that will
be returned to the user when the program completes. An IN OUT variable serves
as both. The following is an example with IN and OUT variables:
|
--
pro2.sql
SET
SERVEROUTPUT ON
CREATE OR
REPLACE PROCEDURE myproc2
(temp_emp_ssn
IN VARCHAR2,
temp_emp_sal
OUT NUMBER)
IS
temp_sal number(10,2);
BEGIN
SELECT emp_salary
INTO temp_sal
FROM employee
WHERE emp_ssn = temp_emp_ssn;
IF temp_sal > 4000 THEN
DBMS_OUTPUT.PUT_LINE('Salary >
4000');
ELSE
DBMS_OUTPUT.PUT_LINE('Salary <
4000');
END IF;
temp_emp_sal := temp_sal;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Employee not
found ');
END
myproc2;
/
|
The following
gives you an example of how to compile and execute the above procedure,
followed by its output:
|
SQL>
@pro2
Procedure
created.
SQL>
var par_sal number;
SQL>
exec myproc2('999666666', :par_sal);
Salary
> 4000
PL/SQL
procedure successfully completed.
SQL>
print par_sal;
par_sal
----------
55000
|
USING BIND VARIABLES TO CHECK THE RESULT
In the
above example, notice that a variable called par_sal is created after
the procedure is compiled. Remember the program we created above takes two
parameters, one IN variable and one OUT variable? The variable par_sal
is created to hold the input passed to the procedure or output value returned
from the program. It is actually called a bind variable in Oracle.
A bind variable is a variable created in SQL*Plus and then
referenced in other PL/SQL subprograms. If you create a bind variable in
SQL*Plus, you can use the variable like a variable in your PL/SQL programs and
then access the variable from SQL*Plus. In the above example, variable par_sal
serves this purpose, which is storing the return value.
A bind
variable is prefixed with a “:” during the execution of the program, like
:par_sal
To check
the value of a bind variable, simply use:
SQL>PRINT
par_sal;
Or
SQL>SELECT
par_sal FROM dual;
Bear in mind
that a procedure can have zero to many parameters, and can also return zero to
many values.
DROPPING A PROCEDURE
As
mentioned above, a procedure needs to be loaded before it can be executed. Once
it is loaded, it can also be unloaded, or dropped.
The
syntax for dropping a procedure:
SQL>DROP
PROCEDURE <procedurename>;
FUNCTIONS
A function
is similar to a procedure, except it returns only one value. A function can
accept zero to many parameters, and it must return one and only one value. The
data type of the return value must be declared in the header of the function.
Syntax
for creating a function:
CREATE OR
REPLACE FUNCTION <function name> (<parameter1 name> <mode>
<data type>, <parameter1 name> <mode> <data type>,…)
RETURN <function return value
data type> IS
<Variable declarations>
BEGIN
Executable Commands
RETURN (return value);
…
EXCEPTION
Exception handlers
END.
It is not
necessary for the RETURN statement to appear in the last line of the execution
section, and there may also be more than one RETURN statement. Like procedure,
OR REPLACE is optional when creating a function.
An example
follows:
|
--
func1.sql
CREATE OR
REPLACE FUNCTION myfunc1
RETURN NUMBER
IS
ret_sal NUMBER(10,2);
BEGIN
SELECT emp_salary INTO ret_sal
FROM employee
WHERE emp_ssn = '999666666';
RETURN (ret_sal);
END
myfunc1;
/
|
The output
of the above example is:
|
SQL>
@func1
Function
created.
SQL> var par_sal varchar2(20);
SQL>
EXECUTE :par_sal := myfunc2;
PL/SQL procedure successfully completed.
SQL>
print par_sal;
par_sal
----------
55000
|
Like the
procedure, we need to create a bind variable to hold the value returned from a
function. In the above example, a variable par_sal is created for that
purpose.
The following
is an example of a function that accepts a parameter:
|
--
func2.sql
CREATE OR
REPLACE FUNCTION myfunc2(
i_dept_no NUMBER)
RETURN varchar2
IS
dept VARCHAR2(20);
BEGIN
SELECT dpt_name INTO dept
FROM department
WHERE dpt_no = i_dept_no;
RETURN (dept);
END
myfunc2;
/
|
The output
of the above example is:
|
SQL>
@func2
Function
created.
SQL> var par_dep varchar2(20);
SQL>
EXECUTE :par_dep := myfunc2(3);
PL/SQL procedure successfully completed.
SQL>
print par_dep
PAR_DEP
--------------------------------
Admin and Records
|
DROPPING A FUNCTION
Syntax
for dropping a function:
SQL>DROP
FUNCTION <functionname>;
Oracle has
some built-in functions, such as the numeric and aggregate functions. Please
refer to Chapter 10 of Oracle SQL for more information. Also
rememberthat some Oracle built-in functions, such as DECODE, cannot be used in
PL/SQL program.
Database Triggers
A database trigger is a stored PL/SQL program unit
associated with a specific database table. ORACLE executes (fires) a database
trigger automatically when a given SQL operation (like INSERT, UPDATE or
DELETE) affects the table. Unlike a procedure, or a function, which must be
invoked explicitly, database triggers are invoked implicitly.
Database triggers can be used to perform any of the
following:
·
Audit data modification
·
Log events transparently
·
Enforce complex business rules
·
Derive column values automatically
·
Implement complex security authorizations
·
Maintain replicate tables
You can associate up to 12 database triggers with a given
table. A database trigger has three parts: a triggering event, an optional
trigger constraint, and a trigger action. When an event occurs, a
database trigger is fired, and an predefined PL/SQL block will perform the necessary
action. The owner, not the current user, must have appropriate access to all
objects referenced by the trigger action.
You cannot
use COMMIT, ROLLBACK and SAVEPOINT statements within trigger blocks. You have
to be careful with using triggers as it may be executed thousands of times for
a large update, and therefore can seriously affect SQL execution performance.
The syntax
for creating a trigger (the reserved words and phrases surrounded by brackets
are optional):
CREATE [OR
REPLACE] TRIGGER trigger_name
{BEFORE|AFTER}
triggering_event ON table_name
[FOR EACH
ROW]
[WHEN
condition]
DECLARE
Declaration statements
BEGIN
Executable statements
EXCEPTION
Exception-handling statements
END;
The reserved word CREATE specifies
that you are creating a new trigger. The reserved word REPLACE specifies that
you are modifying an existing trigger. OR REPLACE is optional.
The trigger_name references the name
of the trigger.
BEFORE or AFTER specify when the
trigger is fired (before or after the triggering event).
The triggering_event references a
DML statement issued against the table.
The table_name is the name of the
table associated with the trigger.
The clause, FOR EACH ROW, specifies
a trigger is a row trigger and fires once for each modified row.
A WHEN clause specifies the
condition for a trigger to be fired.
Bear in mind that if you drop a
table, all the associated triggers for the table are dropped as well.
PARTS OF A TRIGGER
A database trigger has three parts,
namely, a trigger statement, a trigger body and a trigger restriction.
- Trigger statement: The trigger statement specifies the DML statements like UPDATE, DELETE and INSERT, and it executes the trigger body if the condition is met.
- Trigger Body: A trigger action is the procedure (PL/SQL block) that contains the SQL or PL/SQL code to be executed for a triggering statement. Like stored procedures, a trigger action can call SQL or PL/SQL statements, and define PL/SQL language constructs (variables, constants, cursors, exceptions, and so on).
- Trigger restriction: Restrictions on a trigger can be set using the WHEN clause, as shown in the syntax for creating triggers. It basically specifies the condition under which the trigger should be fired.
TYPES OF
TRIGGERS:
Triggers
may be called BEFORE or AFTER the following events:
INSERT,
UPDATE and DELETE.
The
before/after options can be used to specify when the trigger body should be
fired with respect to the triggering statement. If the user indicates a BEFORE
option, then Oracle fires the trigger before executing the triggering
statement. On the other hand, if an AFTER is used, Oracle fires the trigger
after executing the triggering statement.
A trigger
may be a ROW or STATEMENT type. If the statement FOR EACH ROW is present in the
CREATE TRIGGER clause of a trigger, the trigger is a row trigger. A row trigger
is fired for each row affected by an triggering statement.
A statement trigger, however, is fired only
once for the triggering statement, regardless of the number of rows affected by
the triggering statement.
The
following is an example of a statement trigger:
|
CREATE OR
REPLACE TRIGGER mytrig1
BEFORE
DELETE OR INSERT OR UPDATE ON employee
BEGIN
IF (TO_CHAR(SYSDATE, 'day') IN ('sat',
'sun')) OR
(TO_CHAR(SYSDATE,'hh:mi') NOT BETWEEN
'08:30' AND '18:30')
THEN
RAISE_APPLICATION_ERROR(-20500,
'table is secured');
END IF;
END;
/
|
The above
example shows a trigger that limits the DML actions to the employee table to
weekdays from 8.30am to 6.30pm. If a user tries to
insert/update/delete a row in the EMPLOYEE table, a warning message will be
prompted. As exemplified above, you can
customize error conditions via RAISE_APPLICATION_ERROR procedure to display an
error number (which must be between -2001 and -20999) and an appropriate error
message.
Self Note :
Give some more examples which use Exceptions (pp 542, Pl/SQL book)
The
following is an example of a row trigger:
|
CREATE OR
REPLACE TRIGGER mytrig2
AFTER
DELETE OR INSERT OR UPDATE ON employee
FOR EACH
ROW
BEGIN
IF DELETING THEN
INSERT INTO xemployee (emp_ssn,
emp_last_name,emp_first_name, deldate)
VALUES (:old.emp_ssn,
:old.emp_last_name,:old.emp_first_name, sysdate);
ELSIF INSERTING THEN
INSERT INTO nemployee (emp_ssn,
emp_last_name,emp_first_name, adddate)
VALUES (:new.emp_ssn,
:new.emp_last_name,:new.emp_first_name, sysdate);
ELSIF UPDATING('emp_salary') THEN
INSERT INTO cemployee (emp_ssn,
oldsalary, newsalary, up_date)
VALUES (:old.emp_ssn,:old.emp_salary,
:new.emp_salary, sysdate);
ELSE
INSERT INTO uemployee (emp_ssn,
emp_address, up_date)
VALUES (:old.emp_ssn,
:new.emp_address, sysdate);
END IF;
END;
/
|
The above
trigger is used to keep track of all the transactions performed on the employee
table. If any employee is deleted, a new row containing the details of this
employee is stored in a table called xemployee. Similarly, if a new employee is
inserted, a new row is created in another table called nemployee, and so on.
Note that
we can specify the old and new values of an updated row by prefixing the column
names with the :OLD and :NEW qualifiers.
The
following is the output if we try to delete a record from the employee table.
The details of the deleted employee are automatically inserted into the
xemployee table (You must create the xemployee table first).
|
SQL> DELETE FROM
employee WHERE emp_last_name = 'Joshi';
1 row
deleted.
SQL>
SELECT * FROM xemployee;
EMP_SSN EMP_LAST_NAME EMP_FIRST_NAME DELDATE
------------- ------------------------- --------------------------
-----------------
999333333 Joshi Dinesh 02-MAY-03
|
ENABLING AND DISABLING TRIGGERS
A trigger
can be enables or disabled. An enabled trigger executes the trigger body if the
triggering statement is issued. By default, triggers are enabled. A disabled
trigger does not execute the trigger body even if the triggering statement is
issued. We can disable a trigger using the following syntax:
SQL>ALTER
TRIGGER trigger_name DISABLE;
We can
issue the following syntax to disable all the triggers associated with the
table,
SQL>ALTER
TABLE table_name DISABLE ALL TRIGGERS;
To enable a
trigger, which is disabled, we can use the following syntax:
SQL>ALTER
TABLE table_name ENABLE trigger_name;
All
triggers can be enabled for a specific table by using the following command
SQL>
ALTER TABLE table_name ENABLE ALL TRIGGERS;
DROPPING TRIGGERS
The drop
trigger command is used to drop a trigger from the database. The syntax is
SQL>
DROP TRIGGER trigger_name;
A package is a collection of PL/SQL
objects grouped together under one package name. Packages include procedures,
functions, cursors, declarations, types, and variables.
All of the
variables that you have used so far are local variables, meaning they are
visible only in the program in which they are declared. As soon as the program
terminates, the memory used to store the variable is freed, and the variable
cannot be accessed again. Sometimes it is necessary to have global variables,
which can be shared among many PL/SQL programs. A global variable is declared
using the same syntax as is used for declaring a local variable. While local
variables are declared in the DECLARE section of an individual program, global
variables are declared in the DECLARE section of a package.
PACKAGE SPECIFICATION
A package
consists of a specification and a body. The package specification, also called
the package header, declares global variables, cursors, procedures and
functions that can be called or accessed by other program units.
Syntax:
PACKAGE <package name>
IS
<variable
declarations>;
<cursor
declarations>;
<procedure
and function declarations>;
END <package name>;
To declare a
procedure in a package, you must specify the procedure name, followed by the
parameters and variable types, using the following format:
PROCEDURE <procedure name> (param1
param1datatype, param2 param2datatype,…);
To declare a function in a package, you must
specify the function name, parameters and return variable type as follows:
FUNCTION <function name> (param1
param1datatype, param2 param2datatype,…)
RETURN <return data type>;
PACKAGE BODY
A package body contains the code for the
programs declared in the package specification. The package body is optional. A
package may have only package declarations and no programs.
Syntax:
PACKAGE BODY<package name>
IS
<cursor
specifications>
<module
bodies>
END <package name>;
To create a package specification we use a
variation on the CREATE command; all we need to put in the specification is
each PL/SQL block header that will be public within the package. Procedures,
functions, cursors, and variables that are declared in the package
specification are global. They can be called, or used by external users
that have the EXECUTE permission for the package, or that have EXECUTE ANY
PROCEDURE privileges.
An example follows:
|
CREATE OR REPLACE PACKAGE pac_mag_emp
AS
PROCEDURE pro_find_ename(
emp_id IN employee.emp_ssn%TYPE,
employee_first_name OUT employee.emp_first_name%TYPE,
employee_last_name OUT employee.emp_last_name%TYPE);
FUNCTION fun_id_is_good(
emp_id IN employee.emp_ssn%TYPE)
RETURN BOOLEAN;
END pac_mag_emp;
/
|
To create a package body we now specify each PL/SQL block that makes up
the package, note that we are not creating these blocks separately (no CREATE
OR REPLACE is required for the procedure and function definitions).
The body of a package can contain the procedures declared in the package
specification, functions declared in the package specification, definitions of
cursors declared in the package specification, local procedures and functions
not declared in the package specification and local variables.
An example follows:
|
CREATE OR REPLACE PACKAGE BODY pac_mag_emp
AS
PROCEDURE pro_find_ename(
emp_id IN employee.emp_ssn%TYPE,
employee_first_name OUT employee.emp_first_name%TYPE,
employee_last_name OUT employee.emp_last_name%TYPE)
IS
BEGIN
SELECT emp_first_name,emp_last_name
INTO employee_first_name, employee_last_name
FROM employee
WHERE emp_ssn = emp_id;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error in finding employee ssn: '||emp_id);
END pro_find_ename;
FUNCTION fun_id_is_good(
emp_id IN employee.emp_ssn%TYPE)
RETURN BOOLEAN
IS
v_id_cnt NUMBER;
BEGIN
SELECT COUNT(*)
INTO v_id_cnt
FROM employee
WHERE emp_ssn = emp_id;
RETURN (1 = v_id_cnt);
EXCEPTION
WHEN OTHERS THEN
RETURN FALSE;
END fun_id_is_good;
END pac_mag_emp;
/
|
CALLING STORED PACKAGES
To reference a variable, procedure or a
function declared in the package specification, uswe the following notation:
<PACKAGE NAME>.<SUBPROGRAM NAME>
The following is an example of calling a
procedure from another program:
|
-- findname.sql
SET SERVEROUTPUT ON
DECLARE
var_first_name
employee.emp_first_name%type;
var_last_name
employee.emp_last_name%type;
BEGIN
pac_mag_emp.pro_find_ename(‘999666666’,var_first_name,
var_last_name);
DBMS_OUTPUT.PUT_LINE('The value returned is ' || var_first_name ||
var_last_name);
END;
/
|
The output of the above example is:
|
SQL>
@findname
The value
returned is BijoyBordoloi
|
CURSORS IN PACKAGES
As we have learned in Chapter 2, a cursor is used to
populate the result set of a multi-row query. However, a cursor is static
because it is tied to a specific query inside a function or procedure. To make
a cursor more dynamic (i.e. reusable and sharable among different procedures
and functions), like placing a procedure or a function in a package, we can use
a cursor variable.
A cursor variable has datatype
REF CURSOR. It is
like a pointer in C language, and it points to a query work area
in which the result set is stored. Therefore, it can be passed freely as a
parameter to other subprograms.
To create a cursor variable, you have to define a
REF CURSOR
type, followed by a cursor variable of that type.
The syntax for defining a
REF CURSOR type:
TYPE ref_type_name IS
REF CURSOR [RETURN <return_type>];
where <
return_type>
represents a row in a database table.
An example of cursor variable:
DECLARE
TYPE item_val_cv_type IS REF CURSOR RETURN
equipment%ROWTYPE;
item_cv IN OUT item_val_cv_type;
This following example demonstrates the use of REF Cursors. It prints
the equipment description for the equipment number assigned by the user.
|
-- pac_item_data.sql
CREATE OR REPLACE PACKAGE item_data AS
TYPE item_val_cv_type IS
REF CURSOR RETURN equipment%ROWTYPE;
PROCEDURE open_item_cv (
item_cv IN OUT
item_val_cv_type,
item_number IN
varchar2);
--Note the cursor variable
is IN OUT parameter that means it has some one in it after
--the procedure gets
execited. It is this value which is inputed into the next preocedure.
PROCEDURE fetch_item_data(
item_cv IN
item_val_cv_type, item_row OUT
equipment%ROWTYPE);
END item_data;
/
CREATE OR REPLACE PACKAGE BODY item_data AS
PROCEDURE open_item_cv (
item_cv IN OUT
item_val_cv_type,
item_number IN
varchar2)
IS
BEGIN
-- here is where the
cursor gets populated.
OPEN item_cv FOR
SELECT * FROM
equipment WHERE eqp_no = item_number;
END open_item_cv;
PROCEDURE fetch_item_data (
item_cv IN
item_val_cv_type,item_row OUT equipment%ROWTYPE)
IS
BEGIN
FETCH item_cv INTO
item_row;
END fetch_item_data;
END item_data;
/
SET SERVEROUTPUT ON
DECLARE
-- declare a cursor variable
item_curs
item_data.item_val_cv_type;
item_number
equipment.eqp_no%TYPE;
item_row equipment%ROWTYPE;
BEGIN
item_number := '4321';
-- open the cursor using a
variable
item_data.open_item_cv(item_curs,item_number);
-- fetch the data and
display it
LOOP
item_data.fetch_item_data(item_curs,item_row);
EXIT WHEN
item_curs%NOTFOUND;
DBMS_OUTPUT.PUT(item_row.eqp_no || '
');
DBMS_OUTPUT.PUT_LINE(item_row.eqp_description);
END LOOP;
END;
/
|
The output of the above example is:
|
SQL>
@pac_item_data
Package
created.
Package
body created.
4321 Computer, PC
PL/SQL
procedure successfully completed.
|
ADVANTAGES OF PACKAGES
1. Modularity - A package allows us to
encapsulate related subprograms. As a result, each package will help us to
understand the concepts involved in an application in a better manner
2. Easier application handling - To
design the application, we must first specify the objects or subprograms in the
package specification. Once the specification has been compiled, stored
subprograms that refer to the package can be compiled. Therefore, we need not
define the package body completely, until we are ready with the specification
of the application. In other words, we can code and compile a package
specification without its body
3. Information hiding - We are aware of
private and public objects. These objects can be used to protect the integrity
of the package. For example, consider that out package consists of four
subprograms, three public and one private subprogram. The package hides the
definition of the private subprogram so that only the package (not out
application) is affected if the definition changes. Thus, the implementation
details are hidden from other users, thereby protecting the integrity of the
package.
4. Added functionality - Public objects
and cursors declared in the package specification can be shared by all
procedures that are executed in the environment because they persist for the
duration of a session.
5. Better performance - When we call a
packaged program for the first time, the whole package is loaded in the memory.
Therefore, subsequent calls require no disk input/output.
Summary: In this chapter, we have learned how to create,
replace and drop a procedure, function, triggers, and packages.
REVIEW EXERCISES
Learn
Theses Terms
1.
Procedure: Procedures are simply named PL/SQL blocks.
2. Functions: A function is similar to
a procedure, except that when it is called, it returns a single value that is
assigned to a variable.
3. IN:
Parameter is passed to the
procedure as a read-only value that cannot be changed with in the procedure.
4.
OUT: Parameter is a write-only value that can only appear on the left side
of an assignment statement in the procedure.
5.
IN
OUT: Combination of IN and OUT; the parameter is passed to the procedure, and
its value can be changed within the procedure.
6.
Package
body: The package body contains the code for the programs declared in the
package specification.
7.
Package
specification: The package specification, also called the package header,
declares global variables, cursors, procedures and functions that can be called
or accessed by other program units.
8.
Trigger:
A trigger is PL/SQL code block attached and executed by an event, which occurs
to a database table.
9.
Row
Trigger: A row trigger is fired as many times as there are rows affected by the
triggering statement.
10.
Statement
Trigger: A statement trigger is fired once for the triggering statement. In other
words, a statement trigger fires once, regardless of the number of rows
affected by the triggering statement.
Concepts
Quiz:
1.
What
is the distinguishing characteristic that makes functions different from
procedures? (check all that apply)
a. Functions require a PRAGMA RESTRICT
clause.
b. Functions only take IN parameters.
c. Functions are stored in the
database.
d. Functions require a return value.
e. None of the above.
2.
What
statement(s) will cause control to return to the calling environment in a
function?
a. The raising of an exception.
b. The initialization of an OUT
parameter.
c. Writing to a database table.
d. The RETURN statement.
e. None of the above.
3.
If
a procedure has an IN parameter, then it must have an OUT parameter.
a. True
b. False
4.
If
a function declares a user-defined exception but never explicitly raises the
exception, which of the following will be true?
a. The function will not be able to
compile.
b. The function will fail a purity
level check.
c. The exception will never be raised.
d. As long as the exception has a
RETURN clause, there is no error in having a user-defined exception and not
calling it.
5.
IN
OUT parameters are permissible in functions.
a. True
b. False
c. The function will compile with an IN
OUT parameter, but it is not advisable to use them.
6.
Assume
a trigger named mytrig1 already exists in the database. If you use the CREATE
clause to modify the existing trigger, what error message is generated? Explain
your answer.
7.
All
procedures and functions in a package body must be declared in the package
specification.
a. True
b. False
8.
The
main advantages to grouping procedures and functions into packages are as
follows (check all that apply):
a. It follows the trendy object method
of programming.
b. It is a more efficient way of
utilizing the processor memory.
c. It makes greater use of the security
privileges of various users.
d. It is a more efficient method to
maximize tablespace storage.
e. It keeps you on good terms with the
DBA.
9.
The
package specification is merely a formality for other programmers to let them
know what parameters are being passed in and out of the procedures and
functions. It hides the program logic but in actuality it is not necessary and
is incorporated into the package body.
a. True
b. False
10.
A
trigger can fire for which of the following?
a. Before a triggering event.
b. After a triggering event.
c. Before or after a triggering event.
11.
How
many times does a row trigger fire if a DML (INSERT, UPDATE, or DELETE)
operation is issued against a table?
a. As many times as there are rows
affected by the DML operation.
b. Once per DML operation.
12.
How
many times does a statement trigger fire if a DML (INSERT, UPDATE, or DELETE)
operation is issued against a table?
a. As many times as there are rows
affected by the DML operation.
b. Once per DML operation.
Coding
Exercises and Questions
1. [easy] Write a function incr_salary, which
takes two parameters, employee ssn and the percentage by which the salary needs
to be raised and returns the new salary.
2. [easy] Write a procedure Get_On_Hand that has
one in and one out parameter. This procedure should
take in equipment number and send the quantity on hand for this equipment
through the out parameter.
3.
[easy] Write
a procedure called emp_dept which has one IN and one OUT parameter. This
procedure should take in employee ssn and send the name of the department he is
working for by getting it from the department table.
4.
[easy] Write a trigger, which checks the quantity on hand in the equipment
table for every update and displays an error message if the quantity on hand
falls below 2.
5.
[easy] Write a trigger, which fires before the update of employee table for
each row and checks if the new salary is below 20000, if it is then it raises
an error.
6.
[moderate] Write a trigger on the department table which fires after deleting a row
and stores the information of this department into a new table ‘xdept’ which
has the same structure as that of the department and an additional field called
deldate which stores the date the department is deleted.
7. [moderate]
Write a package which has one
procedure and one function in it. The
procedure should take an employees ssn and return his name and department
number. The function is to take employees ssn and a percentage by which his
salary is to be raised and raise his salary accordingly and return the new
salary.
8. [easy] Create a procedure to find out the SSN
of the dept manager for an employee. In other words, write a procedure that
takes an employee’s SSN as the input variable, and return the SSN of his or her
dept manager using the same variable. Return ‘-1’ if no dept manager can be
found.
9. [easy] The employees’ salary is to be
classified as following:
Class A: <= 30000
Class B: > 30000 and <= 60000
Class C: > 60000
Create a function to return the
class of a salary for an employee, given his or her SSN.
10. [moderate]
Assuming each department is granted only 5000 dollar for purchasing PCs, create
a function to check if a new row can be added to the equipment table. In other
words, create a function that takes a dept_no, eqp_qty_on_hand
and eqp_value as the parameters, and returns a 0 if the total value
(after adding the row) does not exceed the allowance, and return –1 if it does.
11. [easy] Create a trigger to ensure that no
department manager or employee starts on Saturday and Sunday.
12. [moderate]
Create a trigger to limit 5 assignments to one employee.
13. [moderate]
Create two tables, LT_ASGN_HISTORY and ASGN_HISTORY, both with the following
columns:
|
Column Name
|
Datatype
|
Size
|
Comments
|
|
work_emp_ssn
|
CHAR
|
9
|
Primary Key. Employee
social security number. Also Composite Foreign Key link to assignment table
|
|
work_pro_number
|
NUMBER
|
2
|
Primary Key. Project
number. Also Composite Foreign Key link to assignment table.
|
|
work_hours_completed
|
NUMBER
|
(5,1)
|
Number of hours an employee has completed on a project.
|
|
date_last_updated
|
DATE
|
|
Primary Key.
Date when this row is changed.
|
Create a trigger that does the
following:
When the work_hours_planned
becomes 0, (not NULL, assuming that a work_hours_planned with NULL
values means it is not assigned yet, while work_hours_planned = 0 means the assignment has been completed),
a. If
the total work hours exceed 100, insert a new record to LT_ASGN_HISTORY (long
term assignment history).
b. Else
insert a new record to ASGN_HISTORY.
14. [easy] Create a procedure that
calculate the total work hours planned for an employee given his or her ssn.
will omit your great writing due to this problem.
ReplyDeleteosb online course
azure devops online course
scala online course