Packages and Exception

1. CPIT 340 Database 2 Packages and Exception

2. CPIT 340 Database 2 • Outline: • Package • Creating a package specification. • Creating package body. • Calling functions & procedures in a package. • Dropping a package.

3. CPIT 340 Database 2 • Outline: • Exception • ZERO_DIVIDE Exception • DUP_VAL_ON_INDEX Exception • INVALIDE_NUMBER Exception • OTHERS Exception

4. CPIT 340 Database 2 Remember • There are three levels of subprograms: • At schema level: • which is standalone subprogram • Inside a package: • It is stored in the package inside database and can be deleted only when the package is deleted with the DROP PACKAGE statement.  • Inside a PL/SQL block

5. CPIT 340 Database 2 Package What is a package ? • It is the place where you group procedures and functions together into it. • It allows you to encapsulate related functionality into one self-contained unit.

6. CPIT 340 Database 2 Package • Packages are typically made up of two components: • Specification • Body

7. CPIT 340 Database 2 Package Specification • It lists the available procedures, functions, types, and objects. • It doesn’t contain the code that makes up the procedures and functions; the code is contained in the package body.

8. CPIT 340 Database 2 Creating a Package Specification • You create a package specification using the CREATE PACKAGE statement. • The syntax is: CREATE [OR REPLACE] PACKAGE package_name IS | AS package_specification END package_name;

9. CPIT 340 Database 2 Creating a Package Specification • Where: • package_nameis the name of the package. • package_specificationlists the public procedures, functions, types, and objects available to your package’s users.

10. CPIT 340 Database 2 Example 1 • In the text file

11. CPIT 340 Database 2 Creating a Package Body • You create a package body using the CREATE PACKAGE BODY statement. • The syntax is: CREATE [OR REPLACE] PACKAGE BODY package_name IS | AS package_body END package_name;

12. CPIT 340 Database 2 Creating a Package Body • Where: • package_name is the name of the package, • Remember: it must match the package name in the specification. • package_body contains the code for the procedures and functions.

13. CPIT 340 Database 2 Example 2 • In the text file

14. CPIT 340 Database 2 Calling Functions and Procedures • When calling functions and procedures in a package, you must include the package name in the call. • Example of call function: VariableName:= employee_package.totalEmployees; • Note: you must define variable to store the return value returned by function inside it.

15. CPIT 340 Database 2 Calling Functions and Procedures • The output is:

16. CPIT 340 Database 2 Calling Functions and Procedures Example of call procedure: set serveroutput on DECLARE Z INTEGER; BEGIN employee_package.findmin(3, 1.25,z); DBMS_OUTPUT.PUT_LINE(z); END; /

17. CPIT 340 Database 2 Dropping a Package • To drop a package specification using DROP PACKAGE. • To drop a package body using DROP PACKAGE BODY. • Example: • DROP PACKAGE employee_package; • DROP PACKAGE BODY employee_package;

18. CPIT 340 Database 2 Exception • What is an Exception ? It’s the third part in the block structure, that used to handle (catch) any run time exceptions (errors) that is raised from BEGIN part

19. CPIT 340 Database 2 Predefined Exceptions

20. CPIT 340 Database 2 Predefined Exceptions

21. CPIT 340 Database 2 Examples • CASE_NOT_FOUND DECLARE n number := 7; BEGIN CASE n WHEN 1 THEN dbms_output.put_line('n = 1'); WHEN 2 THEN dbms_output.put_line('n = 2'); dbms_output.put_line('That implies n > 1'); WHEN 2+2 THEN dbms_output.put_line('n = 4'); END CASE; END; /

22. CPIT 340 Database 2 Example • CURSOR_ALREADY_OPEN DECLARE  2    myLecturerIDNUMBER;  3  4    CURSOR AllEmoNos IS  5      SELECT empnoFROM Emp;  6  BEGIN  7    OPEN AllEmpNos;  8  9    OPEN AllEmpNos; 10  END; 11  /

23. CPIT 340 Database 2 Example • NO_DATA_FOUND Declare  2    v_name VARCHAR2(20);  3  begin  4    select ename  5    into   v_name  6    from   emp  7    where  empno = 16;  8    dbms_output.put_line(v_name);  9* end;/

24. CPIT 340 Database 2 To handle Exception EXCEPTION WHEN Exception name THEN Put your action;

25. CPIT 340 Database 2 ZERO_DIVIDE Exception • The ZERO_DIVIDE exception is raised when an attempt is made to divide a number by zero. • Example 1: BEGIN DBMS_OUTPUT.PUT_LINE(1 / 0); EXCEPTION WHEN ZERO_DIVIDE THEN DBMS_OUTPUT.PUT_LINE('Division by zero'); END; /

26. CPIT 340 Database 2 ZERO_DIVIDE Exception • Example 2: BEGIN DBMS_OUTPUT.PUT_LINE(1 / 0); END;

27. CPIT 340 Database 2 DUP_VAL_ON_INDEX Exception • The DUP_VAL_ON_INDEX exception is raised when an attempt is made to store duplicate values in a column that is constrained by a unique index.

28. CPIT 340 Database 2 DUP_VAL_ON_INDEX Exception • Example: BEGIN INSERT INTO Employee ( ID, First_name, last_name, Salary ) VALUES ( 1, ‘Ola', ‘Hussein‘, 2000 (; EXCEPTION WHEN DUP_VAL_ON_INDEX THEN DBMS_OUTPUT.PUT_LINE('Duplicate value on an index'); END; /

29. CPIT 340 Database 2 INVALID_NUMBER Exception • The INVALID_NUMBER exception is raised when an attempt is made to convert an invalid character string into a number

30. CPIT 340 Database 2 INVALID_NUMBER Exception • Exercise 1 • in the txt file

31. CPIT 340 Database 2 Other Exception • You can use the OTHERS exception to handle all exceptions, as shown here: BEGIN DBMS_OUTPUT.PUT_LINE(1 / 0); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('An exception occurred'); END; /

32. CPIT 340 Database 2 Other Exception • Because OTHERS matches all exceptions, you must list it after any specific exceptions in your EXCEPTION block. • If you attempt to list OTHERS elsewhere, the database returns the error PLS-00370