1 / 35

09-02-2009

CS8630 Database Administration Dr. Mario Guimaraes. 09-02-2009. Class Will Start Momentarily…. PL/SQL overview. Oracle’s Procedural SQL. Microsoft’s equivalent: Transact SQL Structure similar to PASCAL and ADA

kieran-bowman
Télécharger la présentation

09-02-2009

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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. CS8630 Database Administration Dr. Mario Guimaraes 09-02-2009 • 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

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

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

  7. Triggers • A series of PL/SQL statements attached to a database table that execute whenever a triggering event (select, update, insert, delete) 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.

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

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

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

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

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

  13. 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 coffee web-site for answer.

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

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

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

  17. PL/SQL versus JAVA

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

  19. Oracle Code

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

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

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

  23. Nested Procedure Example

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

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

  26. Gantt Chart

  27. PERT chart

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

  29. Many Different Notations

  30. CRUD Matrix

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

More Related