Introduction to PL/SQL Part II: Procedures, Functions, Packages

1. Introduction to PL/SQL Part II

2. Objectives • Procedures and Functions • Packages • Trigger

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

4. 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];

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

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

7. 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’);

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

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

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

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

12. Named Procedure within a Procedure • The name procedure must be the last object listed in the declaration section

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

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

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

16. Calling the Procedure declare howmany mylog.logon_num%TYPE; begin num_logged(‘John',howmany); dbms_output.put_line(howmany); end; /

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

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

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

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

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

22. Calling the function declare paulRate:=9; Begin dbms_output.put_line(ratingMessage(paulRate)); end; /

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

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

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

26. Creating a Package Body CREATE [OR REPLACE] PACKAGE BODY package_name   {IS | AS}  package_bodyEND package_name;

27. 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);

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

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

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

31. 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]; /

32. 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)  );

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

34. 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);

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

36. 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 );

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