Document Sample
plsql Powered By Docstoc
                             II Semester 2008-09
                          CS C352 – Database Systems
                             Lab# Explicit Cursors, Procedures & Triggers

Explicit cursor attributes are -
cursorname%NOTFOUND : evaluates to true, if the last fetch has failed because no more rows were
cursorname %FOUND           : evalutes to true, if the last fetch suceed because a row was available.
Cursorname%ISOPEN           : evalutes to true, if explicit cursor is open.
Cursorname%ROWCOUNT: returns number of rows fetched until now. For eg. a piece of code could
look like     EXIT when empl%ROWCOUNT=10;

Example: Suppose you wanted to print the name and price of all menu items in descending order by
     c_price     menuitems.price%TYPE;
     CURSOR menuitemcur     IS SELECT name, price FROM menuitems ORDER BY
     price DESC;
     OPEN menuitemcur ;
           FETCH menuitemcur INTO c_name, c_price;
           DBMS_OUTPUT.PUT_LINE(to_char(c_name) ||' and ' || c_price);
           EXIT WHEN menuitemcur%NOTFOUND ;
     END LOOP;
     CLOSE menuitemcur;

A procedure is a subprogram that performs a specific action. You specify the name of the procedure, its
parameters, its local variables, and the BEGIN-END block that contains its code and handles any
exceptions. For information on the syntax of the PROCEDURE declaration, see "Procedure

Create Procedure Syntax

       The general syntax to create (or replace) a procedure is shown here. The reserved words and
clauses surrounded by square brackets are optional.
       CREATE [OR REPLACE] PROCEDURE <procedure_name> (<parameter1_name> <mode>
       <data type>, <parameter2_name> <mode> <data type>, ...) {AS|IS}
           <Variable declarations>
           Executable statements
           Exception handlers]
       END <optional procedure name>;

If an error occurs, then the procedure is created but it is an invalid procedure. It does not display the
errors. To view errors give:

SQL>Select * from user_errors;
       Procedure Parts

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 (or AS – both IS and AS work identically) 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 optional local declarations. Declarations are declared between the
keywords IS and BEGIN. The DECLARE keyword, which introduces declarations in an anonymous
PL/SQL block, is not used.
The executable part can hold one or more coding statements. Coding statements are located BEGIN
and END. If an exception-handling section is coded, then coding statements for the executable part are
located between the BEGIN and EXCEPTION keywords. At least one statement must appear in the
executable part of a procedure. The NULL statement meets this requirement.
The exception-handling part contains exception handlers. The code for these is located between the
keywords EXCEPTION and END just as you learned for an anonymous PL/SQL block.
Prior to executing a procedure, you must compile and load the procedure into a schema. In other words,
you have to make it “available” in your logon session. The syntax for compiling a schema is shown
here. You can use either the “@” symbol or the START SQL command to compile the file. The <SQL
filename> parameter is the .sql file that contains the procedure to be compiled.

               SQL>@<SQL filename>
               SQL>start <SQL filename>

This is the same command that you used to execute an anonymous PL/SQL block. It is important to
understand that the filename does not need to be the same as the procedure name. The .sql file only
contains the procedure code. If the procedure compiles correctly, it is then stored in the database, not
the .sql file. In order to execute a procedure at the SQL prompt, you need to execute the procedure, not
the .sql file. We will return to this topic later in the chapter.
Often a procedure will not compile correctly. In this situation, you will usually be prompted with a
warning message. In order to examine the warning, issue the command SHOW ERRORS at the SQL>

       SQL> show errors;

Procedures stored in an Oracle database as database objects are executed with the EXECUTE (EXEC
for short) command. The example command shown here executes a procedure named Insert_Employee.

       EXECUTE Insert_Employee


Both procedures and functions can take parameters. Values passed as parameters to a procedure as arguments
in a calling statement are termed actual parameters. The parameters in a procedure declaration are called
formal parameters. The difference between these two classifications is critical to understanding the use of the
parameter modes. The values stored in actual parameters are values passed to the formal parameters – the
formal parameters are like placeholders to store the incoming values. When a procedure completes, the actual
parameters are assigned the values of the formal parameters. A formal parameter can have one of three
possible modes: (1) IN, (2), OUT, or (3) IN OUT. These modes are outlined in Table

     Mode           Description
     IN             This type of parameter is passed to a 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         This type of parameter combines both IN and OUT; a parameter of this
                    mode is passed to a procedure, and its value can be changed within the

A formal parameter coded as mode IN receives an actual parameter value, and within the procedure, the
value of the formal parameter cannot be changed. It is read-only and acts like a constant value. Mode
IN is the default.
A formal parameters coded as OUT is not passed the value of the corresponding actual parameter.
Rather, it behaves like an un-initialized PL/SQL variables and is assigned a NULL value. These
parameters can have their value changed within a subprogram and the value of the formal parameter is
returned to the actual parameter in the calling procedure.
The IN OUT mode combines both IN and OUT. The value of an actual parameter is passed to the
corresponding formal parameter. In the subprogram, the formal parameter is like an initialized PL/SQL
variable and it can be modified within the subprogram. When the subprogram finishes, the value of the
formal parameter is passed back to the corresponding actual parameter. A good programming practice
is to use different names for actual and formal parameters.
When you call a procedure, the actual parameters are evaluated and the results are assigned to the
corresponding formal parameters. If necessary, before assigning the value of an actual parameter to a
formal parameter, PL/SQL converts the data type of the value. For example, if you pass a number when
the procedure expects a string, PL/SQL converts the parameter so that the procedure receives a string.
The actual parameter and its corresponding formal parameter must have compatible data types. For
instance, PL/SQL cannot convert between the DATE and NUMBER data types, or convert a string to a
number if the string contains extra characters such as dollar signs.
Procedures do not always return the values of OUT and IN OUT parameters. If a procedure raises an
exception, the formal parameter values are not copied back to their corresponding actual parameters.
Procedures do not allow specifying a constraint on the parameter data type. For example, the following
CREATE PROCEDURE statement is not allowed because of the specification that constrains the
v_Variable parameter to NUMBER(2). Instead use the general data type of NUMBER.

       /* Invalid constraint on parameter. */
       CREATE OR REPLACE PROCEDURE proSample (v_Variable NUMBER(2), ...)

       /* Valid parameter. */
       CREATE OR REPLACE PROCEDURE proSample(v_Variable NUMBER, ...)

Developing Procedures
Following are the main steps for developing a stored procedure. The next two pages provide more detail
about creating procedures.
       1. Write the syntax: Enter the code to create a procedure (CREATE PROCEDURE statement) in
       a system editor or word processor and save it as a SQL script file (.sql extension).
       2. Compile the code: Using iSQL*Plus, load and run the SQL script file. The source code is
       compiled into P code and the procedure is created. A script file with the CREATE
       PROCEDURE (or CREATE OR REPLACE PROCEDURE) statement enables you to change
       the statement if there are any compilation or run-time errors, or to make subsequent changes to
       the statement. You cannot successfully invoke a procedure that contains any compilation or run-
       time errors. In iSQL*Plus, use SHOW ERRORS to see any compilation errors. Running the
       CREATE PROCEDURE statement stores the source code in the data dictionary even if the
       procedure contains compilation errors. Fix the errors in the code using the editor and recompile
       the code.

       3. Execute the procedure to perform the desired action. After the source code is compiled and
       procedure is successfully created, the procedure can be executed any number of times using the
       EXECUTE command from iSQL*Plus. The PL/SQL compiler generates the pseudocode or P
       based on the parsed code. The PL/SQL engine executes this when the procedure is invoked.

Creates a stored procedure named AllergyMenu that takes a single parameter, allergen, and finds the
items that do not contain the specified allergen

SQL> REATE PROCEDURE AllergyMenu (allergen VARCHAR ) is
     namee number(20);
     pricee number(20);
           SELECT name, price into namee,pricee
           FROM items IT
           (SELECT *
           FROM madewith m JOIN ingredients ig ON (m.ingredientid =
           WHERE it.itemid = m.itemid AND = allergen);
     end AllergyMenu ;

Dropping a Procedure

        The SQL statement to drop a procedure is the straight-forward DROP PROCEDURE <procedureName>
command. Keep in mind that this is a data definition language (DDL) command, and so an implicit commit
executes prior to and immediately after the command.


Comparing Anonymous PL/SQL Blocks with Procedures

     In order to contrast anonymous PL/SQL blocks with procedures, let us examine the anonymous
PL/SQL block in PL/SQL Example 13.3.
            Temp_Salary NUMBER(10,2);
            SELECT Salary INTO temp_Salary
            FROM Employee
            WHERE EmployeeID = '01885';
            IF temp_Salary > 15000 THEN
                DBMS_OUTPUT.PUT_LINE('Salary > 15,000.');
                DBMS_OUTPUT.PUT_LINE('Salary < 15,000.');
            END IF;
                DBMS_OUTPUT.PUT_LINE('Employee not found.');

        The anonymous PL/SQL block in PL/SQL Example 13.3 stores a value from the Salary column
of the employee table to a temporary variable named tempSalary for the employee with identifier 01885.
Following this, a line of output is produced depending on the value of tempSalary.
        Every time the code of the anonymous PL/SQL block executes, PL/SQL must first parse the
code. Contrast this with the creation and execution of the procedure named DisplaySalary that is given
in PL/Example 13.4. Since the procedure is stored to the database, it is only parsed and compiled once.
Thereafter, the compiled version of the procedure is executed. The procedure only requires
recompilation if it is dropped and created again.

           -- create local variable with required constraint
           temp_Salary NUMBER(10,2);
            SELECT Salary INTO temp_Salary
            FROM Employee
            WHERE EmployeeID = '01885';
            IF temp_Salary > 15000 THEN
                DBMS_OUTPUT.PUT_LINE('Salary > 15,000.');
                DBMS_OUTPUT.PUT_LINE('Salary < 15,000.');
            END IF;
                DBMS_OUTPUT.PUT_LINE('Employee not found.');
       END DisplaySalary;

        You can create the procedure by issuing the appropriate command to run the file that stores the
code to create the procedure at the SQL prompt as shown here (either @ch13-4.sql or start ch13-4.sql).
Alternatively, you can copy/paste the entire CREATE PROCEDURE statement (including all code)
within SQL*Plus and execute the code. This will also create the procedure. A successful creation
results in the Procedure created message. The procedure can be executed with the exec <procedure
name> command. Here the procedure is executed and the output displayed is: Salary > 15,000.

       SQL> exec DisplaySalary
       Salary > 15,000.

Triggers provide a procedural technique to specify and maintain integrity constraints. Triggers even
allow users to specify more complex integrity constraints since a trigger essentially is a PL/SQL
procedure. Such a procedure is associated with a table and is automatically called by the database
system whenever a certain modification (event) occurs on that table. Modifications on a table may
include insert, update, and delete operations.

Basic Trigger Syntax
       CREATE TRIGGER <trigger name>
       {AFTER | BEFORE}
       {DELETE | INSERT | UPDATE [OF <column list>]}
       ON <table name>
       [REFERENCING <reference list>]
<triggered SQL statement>

 Some important points to note:
       You can create only BEFORE and AFTER triggers for tables. (INSTEAD OF triggers are only
available for views; typically they are used to implement view updates.)
      You may specify up to three triggering events using the keyword OR. Furthermore, UPDATE
can be optionally followed by the keyword OF and a list of attribute(s) in <table_name>. If present, the
OF clause defines the event to be only an update of the attribute(s) listed after OF. Here are some
       o ... INSERT ON R ...
       o ... UPDATE OF A, B OR INSERT ON R ...
      If FOR EACH ROW option is specified, the trigger is row-level; otherwise, the trigger is
      Only for row-level triggers:
            o The special variables NEW and OLD are available to refer to new and old tuples
                respectively. Note: In the trigger body, NEW and OLD must be preceded by a colon
                (":"), but in the WHEN clause, they do not have a preceding colon! See example below.
            o The REFERENCING clause can be used to assign aliases to the variables NEW and
            o A trigger restriction can be specified in the WHEN clause, enclosed by parentheses. The
                trigger restriction is a SQL condition that must be satisfied in order for Oracle to fire the
                trigger. This condition cannot contain subqueries. Without the WHEN clause, the trigger
                is fired for each row.
     <trigger_body> is a PL/SQL block, rather than sequence of SQL statements. Oracle has placed
       certain restrictions on what you can do in <trigger_body>, in order to avoid situations where one
       trigger performs an action that triggers a second trigger, which then triggers a third, and so on,
       which could potentially create an infinite loop. The restrictions on <trigger_body> include:
            o You cannot modify the same relation whose modification is the event triggering the
            o You cannot modify a relation connected to the triggering relation by another constraint
                such as a foreign-key constraint.

Trigger Example
We illustrate creating a trigger through an example based on the following two tables:

We create a trigger that may insert a tuple into T5 when a tuple is inserted into T4. Specifically, the
trigger checks whether the new tuple has a first component 10 or less, and if so inserts the reverse tuple
into T5:
        FOR EACH ROW
        WHEN (newRow.a <= 10)
        INSERT INTO T5 VALUES(:newRow.b, :newRow.a);
        END oninsert;


        Notice that we end the CREATE TRIGGER statement with a dot and run, as for all PL/SQL
statements in general. Running the CREATE TRIGGER statement only creates the trigger; it does not
execute the trigger. Only a triggering event, such as an insertion into T4 in this example, causes the
trigger to execute.

SQL>insert into T4 values (3,'mytigger');
Then see whether trigger executed or not by using following command.
SQL>select * from T5;

Displaying Trigger Definition Errors
        As for PL/SQL procedures, if you get a message “Warning: Trigger created with compilation
errors.” you can see the error messages by typing
SQL>show errors trigger <trigger_name>;

       Alternatively, you can type, SHO ERR (short for SHOW ERRORS) to see the most recent
compilation error. Note that the reported line numbers where the errors occur are not accurate.

Viewing Defined Triggers
      To view a list of all defined triggers, use:
SQL>select trigger_name from user_triggers;
        For more details on a particular trigger:

Dropping Triggers : The DROP TRIGGER statement drops a trigger from the database. Also, if you
drop a table, all associated table triggers are also dropped. The syntax is:
drop trigger <trigger_name>;

SQL>drop trigger oninsert ;

Enabling and Disabling Triggers

        It is useful to be able to enable and disable triggers. For example, if you need to run a script that does a
bulk load of the equipment table, you may not want to generate audit trail information regarding the bulk load.
Having a table’s triggers fire can seriously degrade the performance of a bulk load operation.
        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. The
syntax for enabling and disabling triggers is:

        -- Disable an individual trigger by name.
        ALTER TRIGGER trigger_name DISABLE;

        -- Disable all triggers associated with a table.

-- Enable a trigger that was disabled.

-- Enable all triggers associated with a table.

Shared By: