Infolinks

Wednesday 20 February 2013

PL/SQL CHAPTER 3



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:

  1. 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.

  1. 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.

  1. 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.
  1. 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.
  2. 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).
  3. 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;

PACKAGES

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.


Posted by at

1 comment:

Subscribe to: Post Comments (Atom)