09 04 2008
This presentation is the property of its rightful owner.
Sponsored Links
1 / 36

09-04-2008 PowerPoint PPT Presentation


  • 95 Views
  • Uploaded on
  • Presentation posted in: General

CS8630 Database Administration Triggers, Stored Procedures, Functions Dr. Mario Guimaraes. 09-04-2008. Class Will Start Momentarily…. PL/SQL overview. Oracle’s Procedural SQL. Microsoft’s equivalent: Transact SQL Structure similar to PASCAL and ADA

Download Presentation

09-04-2008

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


09 04 2008

CS8630 Database Administration

Triggers, Stored Procedures, Functions

Dr. Mario Guimaraes

09-04-2008

  • Class

    • Will

      • Start

      • Momentarily…


Pl sql overview

PL/SQL overview

  • Oracle’s Procedural SQL. Microsoft’s equivalent: Transact SQL

  • Structure similar to PASCAL and ADA

  • PL/SQL module can be: Stored Procedure, Stored Function or Trigger (or Package or Anonymous Block).

  • Components of a module:

    • variable declaration section,

    • executable section, and the

    • exception handler


Pl sql block 3 sections

PL/SQL block: 3 sections

  • variable declaration section,

  • executable section, and the

  • exception handler


09 04 2008

Procedures, Functions, Triggers

adbc.kennesaw.edu

adbc.kennesaw.edu/adbclast


Calling stored procedures

Calling Stored Procedures

  • CREATE OR REPLACE PROCEDURE insertPerson ( id IN VARCHAR, DOB IN DATE, fname IN VARCHAR, lname IN VARCHAR) IS counter INTEGER;

  • BEGIN

  • SELECT COUNT(*) INTO counter FROM person p WHERE p.pid = id;

  • IF (counter > 0) THEN

  • -- person with the given pid already exists

  • DBMS_OUTPUT.PUT_LINE('WARNING Inserting person: person with pid '

  • || id || ' already exists!');

  • ELSE

  • INSERT INTO person VALUES (id, DOB, fname, lname);

  • DBMS_OUTPUT.PUT_LINE('Person with pid ' || id || ' is inserted.');

  • END IF;

  • END

    ---------------------------------------------------------------------------------------------------------------

    CREATE OR REPLACE PROCEDURE insertFaculty (pid IN VARCHAR, DOB IN DATE, fname IN VARCHAR, lname IN VARCHAR, rank IN VARCHAR, dept IN VARCHAR) IS BEGIN insertPerson(pid, DOB, fname, lname);

    insert into facultyEDB values(pid, rank, dept);

    DBMS_OUTPUT.PUT_LINE('Faculty with pid ' || pid || ' is inserted.');

    END insertFaculty;

    -----------------------------------------------------------------------------------------------------------------

    SQL>exec insertFaculty('121-11-1111', '21-OCT-1961', 'Susan', 'Urban', 'Emeritus', 'CSE'); -- from sql prompt or

    stmt.executeUpdate ("Insert into customers (cid, cname, city, discnt) values " +

    "('C011','IBM','Atlanta',30)"); -- from within a Java program


Procedures or functions

Procedures or Functions ?

R = max (a, b);

Swap (a,b);

R = cos (x);

Void insertRecord;


09 04 2008

Note that like Tables, stored procedures and stored functions are

  • stored in a particular user’s schema

  • A user may access a stored procedure owned by another user by typing the name of that user followed by a dot and the name of the procedure.

  • Example: user jones types in the following

  • SQL> exec Smith.insert faculty;

  • Note that for this to work, user smith needs to GRANT EXECUTE on this procedure to user Jones or user jones needs to have DBA privileges.

Stored Procedure & function Call


Triggers

Triggers

  • A series of PL/SQL statements attached to a

    database table that execute whenever a

    triggering event (select, update, insert, delete, etc.) occurs.

  • Unlike stored procedures and functions, they

    not explicitly called, but they are activated when

    a triggering event occurs.

    Obs.: main purpose is to implement the complex integrity constraints that can’t be done with the CREATE TABLE or ALTER TABLE command.


Example of a trigger

Example of a Trigger

  • CREATE or REPLACE TRIGGER IncreaseDiscount

    AFTER INSERT on OrdersFOR EACH ROW

    BEGIN UPDATE Customers SET discnt = discnt+.1

    WHERE Customers.cid=:new.cid;END;/

    Name of Trigger: IncreaseDiscount

    Triggering Event: After insert on orders

    Trigger Body or Trigger code that is fired: UPDATE customers …

    :new and :old represent reserved words for Oracle’s PL/SQL. In this example, :new represents the cid of the new row in the orders table that was just

    inserted.


Storing triggers

Storing Triggers

  • Where are Triggers, Stored Procedures and Functions Stored ?

  • What is the difference between (a Trigger) and a

    (Stored Prodedure/Function) ?

  • What is the difference between a Function and a Procedure ?


Trigger w insert

Trigger w/Insert

  • CREATE OR REPLACE TRIGGER faculty_before_insert_row

    BEFORE INSERT ON facultyEDB

    FOR EACH ROW

    DECLARE

    counter INTEGER; OverLap EXCEPTION; -- declare exception

    BEGIN

    SELECT COUNT(1) INTO counter FROM student s WHERE s.pid = :new.pid;

    IF (counter > 0) THEN RAISE Overlap; END IF;

    EXCEPTION WHEN Overlap THEN

    RAISE_APPLICATION_ERROR(-20001, 'ERROR: the person already exists as a student in the database! Insert aborted.');

    END;

    1) Name of Trigger ?

    2) Triggering Event ?

    3) What is the Trigger Doing ?


Trigger w update

Trigger w/ Update

CREATE OR REPLACE TRIGGER faculty_after_update_row

AFTER UPDATE ON facultyEDB

FOR EACH ROW

BEGIN

IF UPDATING ('dept') AND :old.dept <> :new.dept

THEN UPDATE department SET chair = NULL WHERE chair = :old.pid; END IF;

END; /

What is this trigger doing ?


Trigger w delete

Trigger w/ Delete

CREATE OR REPLACE TRIGGER faculty_after_delete_row

AFTER DELETE ON facultyEDB

FOR EACH ROW BEGIN

DELETE FROM person WHERE pid = :old.pid;

END; /


Trigger fails

Trigger Fails

When we do an INSERT that fires a Trigger and the insert is sucessful, but the trigger fails (for example, the trigger is trying to access a row

that is locked by another user), will the

INSERT be undone (ROLLBACK) ?

See trigger on adbc web-site for answer. Trigger with error program on the Transactions module


Anonymous block

Anonymous Block

Example:

DECLARE

percent_id agents.percent%TYPE;

BEGIN

SELECT percent INTO percent_id FROM agents WHERE aid = 'a02';

IF percent_id > 0 THEN

INSERT INTO agents (aid, aname, city) VALUES ('a07', 'John', 'Corpus');

END IF;

END;

/

What does agents.percent%TYPE mean ?

  • Unlike Triggers and Stored Procedures/Functions,

    They are not stored inside the database, but

    They are stored as a .sql file in an individual user’s area and executed from the sql prompt.


Jdbc and sqlj

JDBC and SQLJ

  • Major Alternative to PL/SQL

  • JDBC – Java Database Connectivity

    • A predefined set of classes and methods for accessing SQL databases

  • SQLJ – SQL for Java

  • The Oracle pre-compiler for JAVA. It takes simple Oracle calls and translates them into JAVA code prior to compilation with javac


Using jdbc

Using JDBC

Steps to use a JDBC

  • Download java from sun web-site and place it in proper directory

  • Download jdbc driver for oracle from oracle web-site

  • Change connection string (host computer, port number, database instance)


Pl sql versus java

PL/SQL versus JAVA


Asu web site

ASU Web-Site

Choose “9. Case Studies.. To se stored procedures, triggers, e.g.


Oracle code

Oracle Code


Asu oracle schema views

ASU – Oracle Schema - Views

  • CREATE VIEW faculty AS

    SELECT P.pid, P.DOB, P.firstName, P.lastName, F.rank, F.dept

    FROM person P,

    facultyEDB F

    WHERE P.pid = F.pid;

  • CREATE VIEW student AS

    SELECT P.pid, P.DOB, P.firstName, P.lastName, S.status, S.major

    FROM person P, studentEDB S

    WHERE P.pid = S.pid;


Http www unix org ua orelly oracle prog2

http://www.unix.org.ua/orelly/oracle/prog2/


Nested blocks

Nested Blocks

  • http://www.unix.org.ua/orelly/oracle/prog2/ch15_03.htm


Nested procedure example

Nested Procedure Example


Project core deliverables

Project – Core deliverables

  • 1) INTRODUCTION

  • 2) use-case diagram (optional)

  • 3) E-R DIAGRAM

  • 4) RELATIONAL SCHEMA

  • 5) MATRIX

  • 6) SQL Queries, Code

  • 7) Scheduling Chart (PERT or GANNT)

  • 8) Major difficulties encountered and how

  • they were resolved

  • 9) Conclusions


Additional items

Additional Items

  • 1) Database Security Plan with Procedures/Functions and Triggers Implementing Row and Column Level Security.Forms accessing the DB.

  • 2) or SQL Injection. OR

  • 3) Database Application that Runs over the Web.

  • User versus Objects Matrix

  • 4) If Datawarehouse, DD must include the mapping of the destination to the source as well as the time interval that data will be extracted.

  • 5) Tables, Roles, Views, Stored


Gantt chart

Gantt Chart


Pert chart

PERT chart


Example of use case

Example of Use Case

Use-case diagram for a university registration system


Many different notations

Many Different Notations


Crud matrix

CRUD Matrix


End of lecture

End of Lecture

End

Of

Today’s

Lecture.


  • Login