09-04-2008

1. CS8630 Database Administration Triggers, Stored Procedures, Functions Dr. Mario Guimaraes 09-04-2008 • Class • Will • Start • Momentarily…

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

3. PL/SQL block: 3 sections • variable declaration section, • executable section, and the • exception handler

4. Procedures, Functions, Triggers adbc.kennesaw.edu adbc.kennesaw.edu/adbclast

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

7. Procedures or Functions ? R = max (a, b); Swap (a,b); R = cos (x); Void insertRecord;

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

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

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

13. 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 ?

14. 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 ?

15. 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 ?

16. 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; /

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

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

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

20. 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)

21. PL/SQL versus JAVA

22. ASU Web-Site Choose “9. Case Studies.. To se stored procedures, triggers, e.g.

24. Oracle Code

25. 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;

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

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

28. Nested Procedure Example

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

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

31. Gantt Chart

32. PERT chart

33. Example of Use Case Use-case diagram for a university registration system

34. Many Different Notations

35. CRUD Matrix

36. End of Lecture End Of Today’s Lecture.