09 04 2008
Download
Skip this Video
Download Presentation
09-04-2008

Loading in 2 Seconds...

play fullscreen
1 / 36

09-04-2008 - PowerPoint PPT Presentation


  • 118 Views
  • Uploaded on

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

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about '09-04-2008' - jared


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
slide4
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;

slide9
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)
asu web site
ASU Web-Site

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

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;

nested blocks
Nested Blocks
  • http://www.unix.org.ua/orelly/oracle/prog2/ch15_03.htm
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
example of use case
Example of Use Case

Use-case diagram for a university registration system

end of lecture
End of Lecture

End

Of

Today’s

Lecture.

ad