Creating Stored Procedures and Functions

1. Creating Stored Procedures and Functions

2. Objectives • After completing this lesson, you should be able to do the following: • Differentiate between anonymous blocks and subprograms • Create a simple procedure and invoke it from an anonymous block • Create a simple function • Create a simple function that accepts a parameter • Differentiate between procedures and functions

3. Procedures and Functions • Are named PL/SQL blocks • Are called PL/SQL subprograms • Have block structures similar to anonymous blocks: • Optional declarative section (without DECLARE keyword) • Mandatory executable section • Optional section to handle exceptions

4. Differences Between Anonymous Blocks and Subprograms

5. Procedure: Syntax CREATE [OR REPLACE] PROCEDURE procedure_name [(argument1 [mode1] datatype1, argument2 [mode2] datatype2, . . .)] IS|AS procedure_body;

6. Procedure: Example ... CREATE TABLE dept AS SELECT * FROM departments; CREATE PROCEDURE add_dept IS dept_iddept.department_id%TYPE; dept_namedept.department_name%TYPE; BEGIN dept_id:=280; dept_name:='ST-Curriculum'; INSERT INTO dept(department_id,department_name) VALUES(dept_id,dept_name); DBMS_OUTPUT.PUT_LINE(' Inserted '|| SQL%ROWCOUNT ||' row '); END; /

7. Invoking the Procedure BEGIN add_dept; END; / SELECT department_id, department_name FROM dept WHERE department_id=280;

8. Function: Syntax CREATE [OR REPLACE] FUNCTION function_name [(argument1 [mode1] datatype1, argument2 [mode2] datatype2, . . .)] RETURN datatype IS|AS function_body;

9. Function: Example CREATE FUNCTIONcheck_salRETURN Boolean IS dept_idemployees.department_id%TYPE; empnoemployees.employee_id%TYPE; salemployees.salary%TYPE; avg_salemployees.salary%TYPE; BEGIN empno:=205; SELECT salary,department_id INTO sal,dept_id FROM employees WHERE employee_id= empno; SELECT avg(salary) INTO avg_sal FROM employees WHERE department_id=dept_id; IF sal > avg_sal THEN RETURN TRUE; ELSE RETURN FALSE; END IF; EXCEPTION WHEN NO_DATA_FOUND THEN RETURN NULL; END;/

10. Invoking the Function SET SERVEROUTPUT ON BEGIN IF (check_sal IS NULL) THEN DBMS_OUTPUT.PUT_LINE('The function returned NULL due to exception'); ELSIF (check_sal) THEN DBMS_OUTPUT.PUT_LINE('Salary > average'); ELSE DBMS_OUTPUT.PUT_LINE('Salary < average'); END IF; END; /

11. Passing a Parameter to the Function DROP FUNCTION check_sal; CREATE FUNCTION check_sal(empnoemployees.employee_id%TYPE)RETURN Boolean IS dept_idemployees.department_id%TYPE; salemployees.salary%TYPE; avg_salemployees.salary%TYPE; BEGIN SELECT salary,department_id INTO sal,dept_id FROM employees WHERE employee_id=empno; SELECT avg(salary) INTO avg_sal FROM employees WHERE department_id=dept_id; IF sal > avg_sal THEN RETURN TRUE; ELSE RETURN FALSE; END IF; EXCEPTION ... ...

12. Invoking the Function with a Parameter BEGIN DBMS_OUTPUT.PUT_LINE('Checking for employee with id 205'); IF (check_sal(205) IS NULL) THEN DBMS_OUTPUT.PUT_LINE('The function returned NULL due to exception'); ELSIF (check_sal(205)) THEN DBMS_OUTPUT.PUT_LINE('Salary > average'); ELSE DBMS_OUTPUT.PUT_LINE('Salary < average'); END IF; DBMS_OUTPUT.PUT_LINE('Checking for employee with id 70'); IF (check_sal(70) IS NULL) THEN DBMS_OUTPUT.PUT_LINE('The function returned NULL due to exception'); ELSIF (check_sal(70)) THEN ... END IF; END; /

13. Creating Stored Procedures

14. Objectives After completing this lesson, you should be able to do the following: • Describe and create a procedure • Create procedures with parameters • Differentiate between formal and actual parameters • Use different parameter-passing modes • Invoke a procedure • Handle exceptions in procedures • Remove a procedure

15. What Is a Procedure? A procedure: • Is a type of subprogram that performs an action • Can be stored in the database as a schema object • Promotes reusability and maintainability

16. Syntax for Creating Procedures • Use CREATEPROCEDURE followed by the name, optional parameters, and keyword IS or AS. • Add the ORREPLACE option to overwrite an existing procedure. • Write a PL/SQL block containing local variables,a BEGIN statement, and an END statement (or ENDprocedure_name). CREATE [OR REPLACE] PROCEDURE procedure_name [(parameter1 [mode] datatype1, parameter2 [mode] datatype2, ...)] IS|AS [local_variable_declarations; …] BEGIN -- actions; END [procedure_name]; PL/SQL Block

17. Developing Procedures 1 2 Edit Load file.sql 3 Create (compile and store) 4 Execute Use SHOWERRORSfor compilation errors

18. What Are Parameters? Parameters: • Are declared after the subprogram name in the PL/SQL header • Pass or communicate data between the caller and the subprogram • Are used like local variables but are dependent on their parameter-passing mode: • An IN parameter (the default) provides values for a subprogram to process. • An OUT parameter returns a value to the caller. • An INOUT parameter supplies an input value, which may be returned (output) as a modified value.

19. Formal and Actual Parameters • Formal parameters: Local variables declared in the parameter list of a subprogram specification Example: • Actual parameters: Literal values, variables, and expressions used in the parameter list of the called subprogram Example: CREATE PROCEDURE raise_sal(id NUMBER,sal NUMBER) ISBEGIN ... END raise_sal; emp_id := 100; raise_sal(emp_id, 2000)

20. Procedural Parameter Modes • Parameter modes are specified in the formal parameter declaration, after the parameter name and before its data type. • The IN mode is the default if no mode is specified. CREATE PROCEDURE procedure(param [mode] datatype) ... Modes Callingenvironment IN (default) OUT IN OUT Procedure

21. Using IN Parameters: Example CREATE OR REPLACE PROCEDURE raise_salary (id IN employees.employee_id%TYPE, percent IN NUMBER) IS BEGIN UPDATE employees SET salary = salary * (1 + percent/100) WHERE employee_id = id; END raise_salary; / EXECUTE raise_salary(176,10)

22. Using OUT Parameters: Example CREATE OR REPLACE PROCEDURE query_emp (id IN employees.employee_id%TYPE, name OUT employees.last_name%TYPE, salary OUT employees.salary%TYPE) IS BEGIN SELECT last_name, salary INTO name, salary FROM employees WHERE employee_id = id; END query_emp; DECLARE emp_name employees.last_name%TYPE; emp_sal employees.salary%TYPE; BEGIN query_emp(171, emp_name, emp_sal); ... END;

23. Viewing OUT Parameters with iSQL*Plus • Use PL/SQL variables that are printed with calls to the DBMS_OUTPUT.PUT_LINE procedure. • Use iSQL*Plus host variables, execute QUERY_EMP using host variables, and print the host variables. SET SERVEROUTPUT ON DECLARE emp_name employees.last_name%TYPE; emp_sal employees.salary%TYPE; BEGIN query_emp(171, emp_name, emp_sal); DBMS_OUTPUT.PUT_LINE('Name: ' || emp_name); DBMS_OUTPUT.PUT_LINE('Salary: ' || emp_sal); END; VARIABLE name VARCHAR2(25) VARIABLE sal NUMBER EXECUTE query_emp(171, :name, :sal) PRINT name sal

24. Calling PL/SQL Using Host Variables A host variable (also known as a bind or a global variable): • Is declared and exists externally to the PL/SQL subprogram. A host variable can be created in: • iSQL*Plus by using the VARIABLE command • Oracle Forms internal and UI variables • Java variables • Is preceded by a colon (:) when referenced in PL/SQL code • Can be referenced in an anonymous block but not in a stored subprogram • Provides a value to a PL/SQL block and receives a value from a PL/SQL block

25. Using INOUT Parameters: Example Calling environment phone_no (before the call) phone_no (after the call) '8006330575' '(800)633-0575' CREATE OR REPLACE PROCEDURE format_phone (phone_no IN OUT VARCHAR2) IS BEGIN phone_no := '(' || SUBSTR(phone_no,1,3) || ')' || SUBSTR(phone_no,4,3) || '-' || SUBSTR(phone_no,7); END format_phone; /

26. Syntax for Passing Parameters • Positional: • Lists the actual parameters in the same order as the formal parameters • Named: • Lists the actual parameters in arbitrary order and uses the association operator (=>) to associate a named formal parameter with its actual parameter • Combination: • Lists some of the actual parameters as positional and some as named

27. Parameter Passing: Examples • Passing by positional notation: • Passing by named notation: CREATE OR REPLACE PROCEDURE add_dept( name IN departments.department_name%TYPE, loc IN departments.location_id%TYPE) IS BEGIN INSERT INTO departments(department_id, department_name, location_id) VALUES (departments_seq.NEXTVAL, name, loc); END add_dept; / EXECUTE add_dept ('TRAINING', 2500) EXECUTE add_dept (loc=>2400, name=>'EDUCATION')

28. Using the DEFAULT Option for Parameters • Defines default values for parameters: • Provides flexibility by combining the positional and named parameter-passing syntax: CREATE OR REPLACE PROCEDURE add_dept( name departments.department_name%TYPE:='Unknown', loc departments.location_id%TYPE DEFAULT 1700) IS BEGIN INSERT INTO departments (...) VALUES (departments_seq.NEXTVAL, name, loc); END add_dept; EXECUTE add_dept EXECUTE add_dept ('ADVERTISING', loc => 1200) EXECUTE add_dept (loc => 1200)

29. IN OUT IN OUT Default mode Must be specified Must be specified Value is passed into subprogram Returned to calling environment Passed into subprogram; returned to calling environment Formal parameter acts as a constant Uninitialized variable Initialized variable Actual parameter can be a literal, expression, constant, or initialized variable Must be a variable Must be a variable Can be assigned a default value Cannot be assigned a default value Cannot be assigned a default value Summary of Parameter Modes

30. Invoking Procedures You can invoke procedures by: • Using anonymous blocks • Using another procedure, as in the following example: CREATE OR REPLACE PROCEDURE process_employees IS CURSOR emp_cursor IS SELECT employee_id FROM employees; BEGIN FOR emp_rec IN emp_cursor LOOP raise_salary(emp_rec.employee_id, 10); END LOOP; COMMIT; END process_employees; /

31. Handled Exceptions Calling procedure Called procedure PROCEDURE PROC2 ... IS ... BEGIN ... EXCEPTION ... END PROC2; PROCEDURE PROC1 ... IS ... BEGIN ... PROC2(arg1); ... EXCEPTION ... END PROC1; Exception raised Exception handled Control returnsto calling procedure

32. Handled Exceptions: Example CREATE PROCEDURE add_department( name VARCHAR2, mgr NUMBER, loc NUMBER) IS BEGIN INSERT INTO DEPARTMENTS (department_id, department_name, manager_id, location_id) VALUES (DEPARTMENTS_SEQ.NEXTVAL, name, mgr, loc); DBMS_OUTPUT.PUT_LINE('Added Dept: '||name); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Err: adding dept: '||name); END; CREATE PROCEDURE create_departments IS BEGIN add_department('Media', 100, 1800); add_department('Editing', 99, 1800); add_department('Advertising', 101, 1800); END;

33. Exceptions Not Handled Calling procedure Called procedure PROCEDURE PROC2 ... IS ... BEGIN ... EXCEPTION ... END PROC2; PROCEDURE PROC1 ... IS ... BEGIN ... PROC2(arg1); ... EXCEPTION ... END PROC1; Exception raised Exception not handled Control returnedto exception section of calling procedure

34. Exceptions Not Handled: Example SET SERVEROUTPUT ON CREATE PROCEDURE add_department_noex( name VARCHAR2, mgr NUMBER, loc NUMBER) IS BEGIN INSERT INTO DEPARTMENTS (department_id, department_name, manager_id, location_id) VALUES (DEPARTMENTS_SEQ.NEXTVAL, name, mgr, loc); DBMS_OUTPUT.PUT_LINE('Added Dept: '||name); END; CREATE PROCEDURE create_departments_noex IS BEGIN add_department_noex('Media', 100, 1800); add_department_noex('Editing', 99, 1800); add_department_noex('Advertising', 101, 1800); END;

35. Removing Procedures You can remove a procedure that is stored in the database. • Syntax: • Example: DROP PROCEDURE procedure_name DROP PROCEDURE raise_salary;

36. Viewing Procedures in the Data Dictionary Information for PL/SQL procedures is saved in the following data dictionary views: • View source code in the USER_SOURCE table to view the subprograms that you own, or the ALL_SOURCE table for procedures that are owned by others who have granted you the EXECUTE privilege. • View the names of procedures in USER_OBJECTS. SELECT text FROM user_source WHERE name='ADD_DEPARTMENT' and type='PROCEDURE' ORDER BY line; SELECT object_name FROM user_objects WHERE object_type = 'PROCEDURE';

37. Benefits of Subprograms • Easy maintenance • Improved data security and integrity • Improved performance • Improved code clarity

38. Creating Stored Functions

39. Objectives After completing this lesson, you should be able to do the following: • Describe the uses of functions • Create stored functions • Invoke a function • Remove a function • Differentiate between a procedure and a function

40. Overview of Stored Functions A function: • Is a named PL/SQL block that returns a value • Can be stored in the database as a schema object for repeated execution • Is called as part of an expression or is used to provide a parameter value

41. Syntax for Creating Functions The PL/SQL block must have at least one RETURN statement. CREATE [OR REPLACE] FUNCTION function_name[(parameter1 [mode1] datatype1, ...)] RETURN datatype IS|AS [local_variable_declarations; …] BEGIN -- actions; RETURN expression; END [function_name]; PL/SQL Block

42. Developing Functions 1 2 Edit Load func.sql 3 Create (compile and store) 4 Execute Use SHOWERRORSfor compilation errors.

43. Stored Function: Example • Create the function: • Invoke the function as an expression or as a parameter value: CREATE OR REPLACE FUNCTION get_sal (id employees.employee_id%TYPE) RETURN NUMBER IS sal employees.salary%TYPE := 0; BEGIN SELECT salary INTO sal FROM employees WHERE employee_id = id; RETURN sal; END get_sal; / EXECUTE dbms_output.put_line(get_sal(100))

44. Ways to Execute Functions • Invoke as part of a PL/SQL expression • Using a host variable to obtain the result: • Using a local variable to obtain the result: • Use as a parameter to another subprogram • Use in a SQL statement (subject to restrictions) VARIABLE salary NUMBER EXECUTE :salary := get_sal(100) DECLARE sal employees.salary%type; BEGIN sal := get_sal(100); ... END; EXECUTE dbms_output.put_line(get_sal(100)) SELECT job_id, get_sal(employee_id) FROM employees;

45. Advantages of User-Defined Functions in SQL Statements • Can extend SQL where activities are too complex, too awkward, or unavailable with SQL • Can increase efficiency when used in the WHERE clause to filter data, as opposed to filtering the data in the application • Can manipulate data values

46. Function in SQL Expressions: Example CREATE OR REPLACE FUNCTION tax(value IN NUMBER) RETURN NUMBER IS BEGIN RETURN (value * 0.08); END tax; / SELECT employee_id, last_name, salary, tax(salary) FROM employees WHERE department_id = 100; Function created. 6 rows selected.

47. Locations to Call User-Defined Functions User-defined functions act like built-in single-row functions and can be used in: • The SELECT list or clause of a query • Conditional expressions of the WHERE and HAVING clauses • The CONNECTBY, STARTWITH, ORDERBY, and GROUPBY clauses of a query • The VALUES clause of the INSERT statement • The SET clause of the UPDATE statement

48. Restrictions on Calling Functions from SQL Expression • User-defined functions that are callable from SQL expressions must: • Be stored in the database • Accept only IN parameters with valid SQL data types, not PL/SQL-specific types • Return valid SQL data types, not PL/SQL-specific types • When calling functions in SQL statements: • Parameters must be specified with positional notation • You must own the function or have the EXECUTE privilege

49. Controlling Side Effects When Calling Functions from SQL Expressions Functions called from: • A SELECT statement cannot contain DML statements • An UPDATE or DELETE statement on a table T cannot query or contain DML on the same table T • SQL statements cannot end transactions (that is, cannot execute COMMIT or ROLLBACK operations) Note: Calls to subprograms that break these restrictions are also not allowed in the function.

50. Restrictions on Calling Functions from SQL: Example CREATE OR REPLACE FUNCTION dml_call_sql(sal NUMBER) RETURN NUMBER IS BEGIN INSERT INTO employees(employee_id, last_name, email, hire_date, job_id, salary) VALUES(1, 'Frost', 'jfrost@company.com', SYSDATE, 'SA_MAN', sal); RETURN (sal + 100); END; UPDATE employees SET salary = dml_call_sql(2000) WHERE employee_id = 170; UPDATE employees SET salary = dml_call_sql(2000) *ERROR at line 1:ORA-04091: table PLSQL.EMPLOYEES is mutating, trigger/function may not see itORA-06512: at "PLSQL.DML_CALL_SQL", line 4