370 likes | 480 Views
Introduction to PL/SQL. Part II. Objectives. Procedures and Functions Packages Trigger. Procedures and Functions. Up until now, our code was in an anonymous block It was run immediately It is useful to put code in a function or procedure so it can be called several times
E N D
Introduction to PL/SQL Part II
Objectives • Procedures and Functions • Packages • Trigger
Procedures and Functions • Up until now, our code was in an anonymous block • It was run immediately • It is useful to put code in a function or procedure so it can be called several times • Once we create a procedure or function in a Database, it will remain until deleted (like a table). • A procedure can be executed as a statement of its own, and does not have to return a value • A function is always used as an argument within a statement, and always returns a value.
Creating Procedures CREATE [OR REPLACE] PROCEDURE procedure_name [(parameter1 [mode1] datatype1, parameter2 [mode2] datatype2, . . .)] IS [variable declarations] BEGIN executable statements [EXCEPTION exception handlers] END [name];
Creating Procedures • Modes: • IN: procedure must be called with a value for the parameter. Value cannot be changed • OUT: procedure must be called with a variable for the parameter. Changes to the parameter are seen by the user (i.e., call by reference) • IN OUT: value can be sent, and changes to the parameter are seen by the user • Default Mode is: IN
Procedures • CREATE or REPLACE PROCEDURE goodday IS BEGIN dbms_output.put_line(‘Good day to you!’); END; / • SQL> execute goodday; Good day to you! • SQL> drop procedure goodday; Procedure dropped.
Procedures CREATE or REPLACE PROCEDURE list_dept_employees(department in varchar2 default ‘IT’) IS BEGIN for a in (select * from employee where fk_department = department) loop dbms_output.put_line(a.last_name); end loop; END; / • SQL> exec list_dept_employee(‘HR’);
CREATE or REPLACE PROCEDURE find_oldest_employee (department in varhcar2, fname out employee.first_name%TYPE, lname out employee.last_name%TYPE) IS CURSOR b IS select first_name, last_name from employee where birth_date= (select max(birth_date) from employee where fk_department=department); BEGIN open b; fetch b into fname, lname; close b; END find_oldest_employee; /
DECLARE cursor a is select * from department; a_var a%ROWTYPE; last_name employee.last_name%TYPE; first_name employee.first_name%TYPE; BEGIN open a; fetch a into a_var; while a%FOUND loop find_oldest_employee(a_var.department, first_name, last_name); dbms_output.put_line(a_var.department || ‘ ‘|| first_name || ‘ ‘|| last_name); fetch a into a_var; end loop; close a; END; /
Procedure apply_discount (company_id_in IN company.company_id%TYPE, discount_in IN NUMBER) IS min_discount CONSTANT NUMBER := .05; max_discount CONSTANT NUMBER := .25; invalid_discount EXCEPTION; BEGIN IF discount_in BETWEEN min_discount AND max_discount then update item set item_amount := item_amount * (1-discount_in) where exists (select * from order where order.order_id=item.order_id and order.company_id=company_id_in); IF SQL%ROWCOUNT=0 THEN RAISE NO_DATA_FOUND; END IF;
ELSE RAISE invalid_discount; END IF; EXCEPTION WHEN invalid_discount THEN dbms_output.put_line(‘The specified discount is invalid.’); WHEN NO_DATA_FOUND THEN dbms_output.put_line(‘No orders in the system for company:’ || TO_CHAR(company_id_in)); END apply_discount;
Named Procedure within a Procedure • The name procedure must be the last object listed in the declaration section
DECLARE cursor a is select * from department; a_var a%ROWTYPE; last_name employee.last_name%TYPE; first_name employee.first_name%TYPE; PROCEDURE find_oldest_employee (department in varhcar2, fname out employee.first_name%TYPE, lname out employee.last_name%TYPE) IS CURSOR b IS select first_name, last_name from employee where birth_date= (select max(birth_date) from employee where fk_department=department); BEGIN open b; fetch b into fname, lname; close b; END find_oldest_employee;
BEGIN open a; fetch a into a_var; while a%FOUND loop find_oldest_employee(a_var.department, first_name, last_name); dbms_output.put_line(a_var.department || ‘ ‘|| first_name || ‘ ‘|| last_name); fetch a into a_var; end loop; close a; END; /
Example- what does this do? Table mylog create or replace procedure num_logged (person IN mylog.who%TYPE, num OUT mylog.logon_num%TYPE) IS BEGIN select logon_num into num from mylog where who = person; END; /
Calling the Procedure declare howmany mylog.logon_num%TYPE; begin num_logged(‘John',howmany); dbms_output.put_line(howmany); end; /
Overloading • Overloading is a technique that allows the developer to have objects with the same name that have different behariors. • Pl/SQL allows the developers to define two or more procedures with the same name within a procedure. • The combination of procedure name and parameters determines uniqueness.
DECLEAR PROCEDURE list_employees(department in varchar2) IS BEGIN for a in (select emp_lname, emp_date from employee where emp_dept=department) loop dbms_out.put_line(a.emp_lname||’ ‘|| to_char(a.emp_date)); end loop; END; PROCEDURE list_employees(department in varchar2, hired in date) IS BEGIN for a in (select emp_lname, emp_date from employee where emp_dept=department and emp_date > hired) loop dbms_out.put_line(a.emp_lname||’ ‘|| to_char(a.emp_date)); end loop; END;
BEGIN dbms_output.put_line(‘Results of the first procedure’); list_employees(‘IT’); dbms_output.put_line(‘--------------’); dbms_output.put_line(Results of the second procedure’); list_empolyees(‘IT’, ’01-JAN-08’); END; /
Creating a Function • Almost exactly like creating a procedure, but you supply a return type CREATE [OR REPLACE] PROCEDURE function_name [(parameter1 [mode1] datatype1, parameter2 [mode2] datatype2, . . .)] RETURN return_datatype IS [variable declarations] BEGIN executable statements [EXCEPTION exception handlers] END [function_name];
A Function create or replace function rating_message(rating IN NUMBER) return VARCHAR2 IS BEGIN IF rating > 7 THEN return 'You are great'; ELSIF rating >= 5 THEN return 'Not bad'; ELSE return 'Pretty bad'; END IF; END; / NOTE THAT YOU DON'T SPECIFY THE SIZE
Calling the function declare paulRate:=9; Begin dbms_output.put_line(ratingMessage(paulRate)); end; /
Packages • Functions, Procedures, Variables can be put together in a package • In a package, you can allow some of the members to be "public" and some to be "private" • There are also many predefined Oracle packages
Package • Enhance and maintain applications more easily • Improve overall application performance • Minimize the need to recompile code • A package consists of two pieces of code: the specification and body. • The specification defines how a developer can use the package, such as which programs can be called and what cursors be opened. • The body contains the implementation of the programs.
Creating a Package Specification CREATE [OR REPLACE] PACKAGE package_name{IS | AS} package_specificationEND package_name; CREATE OR REPLACE PACKAGE inv_pck_spec as FUNCTION inv_count(qty integer) RETURN integer;PROCEDURE inv_adjust(qty integer);END inv_pck_spec; /
Creating a Package Body CREATE [OR REPLACE] PACKAGE BODY package_name {IS | AS} package_bodyEND package_name;
CREATE OR REPLACE PACKAGE BODY inv_pck_spec is FUNCTION inv_count(qty integer)RETURN integer is new_qty integer;BEGIN new_qty:= qty*6;INSERT into employee (id,salary) values ('01',new_qty); RETURN(new_qty);END inv_count;PROCEDURE inv_adjust(qty integer) isBEGINDELETE from employee WHERE salary<qty;END inv_adjust; BEGIN -- package initialization begins hereINSERT into employee (id, first_name) values('01', 'new'); END inv_pck_spec; / call inv_pck_spec.inv_count(2);call inv_pck_spec.inv_adjust(2000);
create or replace package pkg_test1 asfunction getArea (i_rad NUMBER) return NUMBER;procedure p_print (i_str1 VARCHAR2 :='hello', i_str2 VARCHAR2 :='world', i_end VARCHAR2 :='!' );end; /create or replace package body pkg_test1 asfunction getArea (i_rad NUMBER)return NUMBERis v_pi NUMBER:=3.14;beginreturn v_pi * (i_rad ** 2);end;procedure p_print(i_str1 VARCHAR2 :='hello', i_str2 VARCHAR2 :='world', i_end VARCHAR2 :='!' ) isbegin DBMS_OUTPUT.put_line(i_str1||','||i_str2||i_end);end;end; /
Triggers • Triggers are special procedures which we want activated when someone has performed some action on the DB. • For example, we might define a trigger that is executed when someone attempts to insert a row into a table, and the trigger checks that the inserted data is valid.
Triggers • There are four types of database triggers: • Table-level triggers can initiate activity before or after an INSERT, UPDATE, or DELETE event. • View-level triggers defines what can be done to the view. • Database-level triggers can be activated at startup and shutdown of a database. • Session-level triggers can be used to store specific information.
DML Triggers CREATE [OR REPLACE] TRIGGER trigger_name {BEFORE | AFTER} {INSERT | DELETE | UPDATE | UPDATE OF column_list} ON table_name [FOR EACH ROW] [WHEN (…)] [DECLARE…] BEGIN … executable statements… [EXCEPTION…] END [trigger_name]; /
create table Employee( ID VARCHAR2(4) PRIMARY KEY, First_Name VARCHAR2(10), Last_Name VARCHAR2(10), Start_Date DATE, End_Date DATE, Salary Number(8,2), City VARCHAR2(10), Description VARCHAR2(15) );
CREATE OR REPLACE TRIGGER LimitSalary BEFORE INSERT OR UPDATE OF salary ON employee FOR EACH ROW DECLARE v_MaxSalary CONSTANT NUMBER := 2000; v_CurrentSalary NUMBER;BEGINSELECT salary INTO v_CurrentSalaryFROM employeeWHERE id = :new.id; IF v_CurrentSalary > v_MaxSalary THEN RAISE_APPLICATION_ERROR(-20000, 'Too high in salary ' || :new.id); END IF;END LimitSalary; / To run the trigger: (1) Save as LimitSalary.sql (2) SQL>start LimitSalary.sql
Trigger for auditing CREATE TABLE DEPT( DEPTNO NUMBER(2), DNAME VARCHAR2(14), LOC VARCHAR2(13)); CREATE TABLE DEPT$AUDIT ( DEPTNO NUMBER, DNAME VARCHAR2(14 byte), LOC VARCHAR2(13 byte), CHANGE_TYPE VARCHAR2(1 byte), CHANGED_BY VARCHAR2(30 byte), CHANGED_TIME DATE);
CREATE OR REPLACE TRIGGER auditDEPTAR AFTERINSERT OR UPDATE OR DELETE ON DEPT FOR EACH ROW declare my DEPT$audit%ROWTYPE;beginif inserting then my.change_type := 'I'; elsif updating then my.change_type :='U';else my.change_type := 'D'; end if; my.changed_by := user; my.changed_time := sysdate;case my.change_type when 'I' then my.DEPTNO := :new.DEPTNO; my.DNAME := :new.DNAME; my.LOC := :new.LOC;else my.DEPTNO := :old.DEPTNO; my.DNAME := :old.DNAME; my.LOC := :old.LOC;end case;insert into DEPT$audit values my;end; /
Trigger with a REFERENCING clause create table company( product_id number(4) not null, company_id NUMBER(8) not null, company_short_name varchar2(30) not null, company_long_name varchar2(60) ); create table product_audit( product_id number(4) not null, num_rows number(8) not null );
CREATE OR REPLACE TRIGGER myTrigger AFTER INSERT ON company REFERENCING NEW AS new_org FOR EACH ROWBEGINUPDATE product_audit SET num_rows =num_rows+1WHERE product_id =:new_org.product_id; IF (SQL%NOTFOUND) THENINSERT INTO product_audit VALUES (:new_org.product_id,1);END IF;END; /