09-02-2009PL/SQL overviewPL/SQL block: 3 sectionsSlide 4Slide 5Calling Stored ProceduresProcedures or Functions ?Slide 8Slide 9TriggersExample of a TriggerStoring TriggersTrigger w/InsertTrigger w/ UpdateTrigger w/ DeleteTrigger FailsAnonymous BlockJDBC and SQLJUsing JDBCPL/SQL versus JAVAASU Web-SiteSlide 22Oracle CodeASU – Oracle Schema - Viewshttp://www.unix.org.ua/orelly/oracle/prog2/Nested BlocksNested Procedure ExampleProject – Core deliverablesAdditional ItemsGantt ChartPERT chartExample of Use CaseMany Different NotationsCRUD MatrixEnd of LectureCS 8630 Database Administration, Dr. Guimaraes09-02-2009ClassWill Start Momentarily…CS8630 Database AdministrationDr. Mario GuimaraesCS 8630 Database Administration, Dr. GuimaraesPL/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 handlerCS 8630 Database Administration, Dr. Guimaraesvariable declaration section, executable section, and theexception handler PL/SQL block: 3 sectionsCS 8630 Database Administration, Dr. GuimaraesProcedures, Functions, Triggersadbc.kennesaw.eduCS 8630 Database Administration, Dr. GuimaraesCS 8630 Database Administration, Dr. GuimaraesCalling 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 programCS 8630 Database Administration, Dr. GuimaraesProcedures or Functions ?R = max (a, b);Swap (a,b);R = cos (x);Void insertRecord;CS 8630 Database Administration, Dr. GuimaraesCS 8630 Database Administration, Dr. GuimaraesCS 8630 Database Administration, Dr. GuimaraesTriggers•A series of PL/SQL statements attached to a database table that execute whenever atriggering event (select, update, insert, delete) occurs.•Unlike stored procedures and functions, they not explicitly called, but they are activated whena 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.CS 8630 Database Administration, Dr. GuimaraesExample of a Trigger•CREATE or REPLACE TRIGGER IncreaseDiscountAFTER INSERT on OrdersFOR EACH ROW BEGIN UPDATE Customers SET discnt = discnt+.1 WHERE Customers.cid=:new.cid;END;/Name of Trigger: IncreaseDiscountTriggering Event: After insert on ordersTrigger 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.CS 8630 Database Administration, Dr. GuimaraesStoring 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 ?CS 8630 Database Administration, Dr. GuimaraesTrigger 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 ?CS 8630 Database Administration, Dr. GuimaraesTrigger w/ UpdateCREATE 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 ?CS 8630 Database Administration, Dr. GuimaraesTrigger w/ DeleteCREATE OR REPLACE TRIGGER faculty_after_delete_row AFTER DELETE ON facultyEDB FOR EACH ROW BEGIN DELETE FROM person WHERE pid = :old.pid; END; /CS 8630 Database Administration, Dr. GuimaraesTrigger FailsWhen 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 rowthat is locked by another user), will the INSERT be undone (ROLLBACK) ?See trigger on coffee web-site for answer.CS 8630 Database Administration, Dr. GuimaraesAnonymous BlockExample: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, butThey are stored as a .sql file in an individual user’s area and executed from the sql prompt.CS 8630 Database Administration, Dr. GuimaraesJDBC and SQLJMajor Alternative to PL/SQL•JDBC – Java Database Connectivity–A predefined set of classes and methods for accessing SQL databases•SQLJ – SQL for JavaThe Oracle pre-compiler for JAVA. It takes simple Oracle calls and translates them into JAVA code