Sub Programs

1. Sub Programs Functions & Procedures Oracle Short Term Course TIET

2. Subprograms are named PL/SQL blocks that can take parameters and can be invoked. Subprograms allow decomposition of a program into logical units. These logical units can be used as building blocks to create complete application programs. Oracle Short Term Course TIET

3. Types of Subprograms PL/SQL has two types of subprograms: ¨Procedures ¨Functions Generally, we use a procedure to perform an action and a function to compute a value. Like unnamed oranonymous PL/SQL blocks, subprograms have a declarative part, an executable part, and an optional exception-handling part. Anonymous bocks provide specific operation but cannot accept or return values as in procedures or functions. Subprograms may be further classified as local and stored type. Oracle Short Term Course TIET

4. Local Subprograms Such procedures and functions are local to the PL/SQL module, which contain it. They can be created in the declarative section of PL/SQL module, local to the module. The local module can be called anywhere in the module’s execution section. Oracle Short Term Course TIET

5. Stored Subprograms A stored procedure or function is a named PL/SQL code block that have been compiled and stored in one of the Oracle engine’s system tables. Stored Procedures and Functions are stored in the Oracle database. They are invoked or called by any the PL/SQL block that appear within an application. Before the procedure or function is stored, the Oracle engine parses and compiles the procedure or function. The Oracle engine compiles the PL/SQL code block. Oracle Short Term Course TIET

6. If an error occurs during compilation of the procedure or function an invalid procedure or function is created. The Oracle engine displays a message after creation that the procedure or function was created with compilation errors. It does not display the errors. These errors can be viewed using the select statement: SELECT *FROM USER_ERRORS; Or Show errors; Oracle Short Term Course TIET

7. Advantages of Subprograms ¨Extensibility: Allows creation of new program modules without affecting existing program modules. ¨ Modularity: Allows breaking a program down into manageable, well-defined logic modules. Each unit provides specific services in a program. This supports top-down design and the stepwise refinement approach to problem solving. ¨Reusability: Allows creation of subprograms that can be used by many applications. Oracle Short Term Course TIET

8. ¨Maintainability: Modularity and reusability lead to easier maintenance and enhancement. ¨Abstraction: Subprograms provides abstraction, because during use of subprograms, we must know what they do, not how they work. Therefore, we can design applications from the top down without worrying about implementation details. Dummy Subprograms (Stubs): Itallows deferring the definition of procedures and functions until we test and debug the main program. Oracle Short Term Course TIET

9. Oracle Short Term Course TIET

10. Anonymous PL/SQL Blocks versus Stored Procedures A stored procedure is created and stored in the database as a schema object. Once created and compiled, it is a named object that can be executed without recompiling. Additionally, dependency information is stored in the data dictionary to guarantee the validity of each stored procedure. As an alternative to a stored procedure, you can create an anonymous PL/SQL block by sending an unnamed PL/SQL block to the Oracle server from an Oracle tool or an application. Oracle compiles the PL/SQL block and places the compiled version in the shared pool of the SGA, but does not store the source code or compiled version in the database for reuse beyond the current instance. Shared SQL allows anonymous PL/SQL blocks in the shared pool to be reused and shared until they are flushed out of the shared pool. Oracle Short Term Course TIET

11. Difference between Procedure and Function Oracle Short Term Course TIET

12. Parts of Functions and Procedures Procedures and Functions are made up of:  A declarative part. ¨An executable part, and ¨An optional exception-handling part. Declarative part The declarative part is used to declare constants, variables, exceptions and subprograms. These objects are local to the procedure or function. The objects become invalid once the user exits from the procedure or the function. Oracle Short Term Course TIET

13. Executable part It is a compulsory part, which is used to perform actions. Variables declared are put to use in this block. The executable part is a PL/SQL block consisting of SQL and PL/SQL statements that assign values control execution and manipulate data. The data that is to be returned back to the calling environment is also returned from here. Exception handling part It is optional part to handle the errors raised during the execution of code in the executable part. We cannot transfer the flow or execution from the Exception Handling part to the Executable part. Oracle Short Term Course TIET

14. Procedures  A procedure is a subprogram that performs a specific action. Procedures can be created within a PL/SQL module, if there is a repetitive code, which could be better-designed using procedures. There are two types of Procedures: ¨Local procedure ¨Stored procedure Oracle Short Term Course TIET

15. Local Procedure Such procedure is local to the PL/SQL module, which contain it. Procedures can be created in the declarative section of PL/SQL module, local to the module. The local module can be called anywhere in the module’s execution section. Oracle Short Term Course TIET

16. Oracle Short Term Course TIET

17. procedure_name It is the procedure name used to identify the procedures when it is called in a program. Declarations It is the optional declaration section where local variables, constant, exception or cursor declaration is placed. Exception It is the optional exception-handing section for procedure. Note  We cannot impose the NOTNULL constraint on a parameter. Also, we cannot specify a constraint on the data type. Oracle Short Term Course TIET

18. For example, the following declaration of emp_id is illegal because it imposes a size constraint:  PROCEDURE raise_salary (emp_id NUMBER(4)) IS /*--- illegal; should be NUMBER*/ Oracle Short Term Course TIET

19. Argument Modes Argument modes are used to define the behavior of formal parameters. There are three argument modes IN, OUT and IN OUT to be used with any subprograms. Oracle Short Term Course TIET

20. Oracle Short Term Course TIET

21. Oracle Short Term Course TIET

22. Consider a procedure that accepts two numbers and return addition, subtraction, multiplication and division of two numbers or in other words a procedure to return multiple values through arguments. Oracle Short Term Course TIET

23. Oracle Short Term Course TIET

24. Consider the procedure raise_salary, which accepts two parameters empid and amount to be added to salary of employee. It will increase the salary and update it to the database. Oracle Short Term Course TIET

25. Oracle Short Term Course TIET

26. Oracle Short Term Course TIET

27. A Stored procedure that accepts two numbers and return addition, subtraction, multiplication and division of two numbers or in other words a stored procedure to return multiple values through arguments. Oracle Short Term Course TIET

28. Oracle Short Term Course TIET

29. A PL/SQL code to call the procedure PROCESS created in example 20.3. Oracle Short Term Course TIET

30. A stored procedure fire_employee to delete employee on the basis of employee number. CREATE PROCEDURE fire_employee (emp_id NUMBER) AS BEGIN DELETE FROM emp WHERE empno = emp_id; END; Oracle Short Term Course TIET

31. A PL/SQL code to call the procedure fire_employee created in example 20.5. DECLARE e number; BEGIN e:=&empno; fire_employee(e); END; Oracle Short Term Course TIET

32. Actual versus Formal Parameters Subprograms pass information using parameters. There are two types of parameters. ¨Formal Parameters ¨Actual Parameters Formal Parameters are declared in the parameter list of procedure or function definition. Oracle Short Term Course TIET

33. For example, the following procedure declares two formal parameters named emp_id and increase: PROCEDURE raise_salary (emp_id NUMBER, increase NUMBER) IS Actual Parameters are referenced in a procedure or function call. For example, the following procedure call lists two actual parameters named emp_num and amount: raise_salary(emp_num, amount); Oracle Short Term Course TIET

34. Functions A function is a subprogram that computes a value. Functions and procedures are structured alike, except that functions have a RETURN clause. There are two types of functions: ¨Local function ¨Stored function Oracle Short Term Course TIET

35. Oracle Short Term Course TIET

36. Here:function_name is the function name to identify the function when it is called in the program  return datatype is used to declare the datatype of the return value of the function which can be any valid PL/SQL datatype. declarations is optional declaration section where local variable, constant or cursor are placed. Note We cannot impose the NOTNULL constraint on a parameter, and we cannot specify a constraint on the data type. Oracle Short Term Course TIET

37. RETURN Statement  The RETURN statement immediately completes the execution of a subprogram and returns control to the caller. Execution then resumes with the statement following the subprogram call. (Do not confuse the RETURN statement with the RETURN clause, which specifies the datatype of the result value in a function specification.)  A subprogram can contain several RETURN statements. Executing any of them completes the subprogram immediately. In procedures, a RETURN statement cannot contain an expression. The statement simply returns control to the caller before the normal end of the procedure is reached. However, in functions, a RETURN statement must contain an expression, which is evaluated when the RETURN statement is executed. The resulting value is assigned to the function identifier, which acts like a variable of the type specified in the RETURN clause. A function must contain at least one RETURN statement. Otherwise, PL/SQL raises the predefined exception PROGRAM_ERROR at run time. Oracle Short Term Course TIET

38. Write a PL/SQL code that calls a function to add two numbers. Oracle Short Term Course TIET

39. A PL/SQL code, which call a function balance to return the balance of a specified bank account. Oracle Short Term Course TIET

40. Consider a function which accepts a code and find the rate for the code from item table, quantity from sale table and return the product of rate*quantity. The PL/SQL block calls the function by passing codes from 1 to 5 and adds up the total values returned by the function and display it. Oracle Short Term Course TIET

41. Oracle Short Term Course TIET

42. Oracle Short Term Course TIET

43. Create a Stored function that accepts two numbers and return addition of passed values. Oracle Short Term Course TIET

44. A PL/SQL code to call the function ADDN created in example 20.10. Oracle Short Term Course TIET

45. A Stored function that accepts department number and return total salary of that department. Oracle Short Term Course TIET

46. A PL/SQL code to call the function SALARY created in example 20.12. Oracle Short Term Course TIET

47. Dropping a Procedure/ Function To drop a procedure, DROP PROCEDURE command is used. For this user must either own the procedure or have DROP ANY PROCEDURE system privilege.  Syntax:  DROP PROCEDURE procedure_name;  Example: DROP PROCEDURE process; Oracle Short Term Course TIET

48. To drop a function, DROP FUNCTION command is used. For this user must either own the function or have DROP ANY FUNCTION system privilege. Syntax: DROP FUNCTION function_name; Example: DROP FUNCTION addn; Oracle Short Term Course TIET

49. References Simplified Approach To Oracle By Kalyani Publishers Oracle Short Term Course TIET