PL/SQL Subprograms

1. CPIT 340 Database 2 PL/SQL Subprograms

2. CPIT 340 Database 2 • Outline: • What is Subprogram ? • What are the kinds of subprogram ? • Procedure • Create a procedure. • Call a procedure. • Drop a procedure. • View errors in a procedure • Functions • Create a function. • Call a function. • Drop a function.

3. CPIT 340 Database 2 What is Subprogram ? • It is a program unit/module that performs a particular task. • These subprograms are combined to form larger programs. • A subprogram can be invoked by another subprogram or program which is called the calling program.

4. CPIT 340 Database 2 What is Subprogram ? • 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 • We will cover it in more details

5. CPIT 340 Database 2 Parts of a PL/SQL Subprogram

6. CPIT 340 Database 2 PL/SQL subprograms • There are two kinds of PL/SQL subprograms: • Procedures: these subprograms do not return a value directly, mainly used to perform an action. • Functions: these subprograms return a single value, mainly used to compute and return a value.

7. CPIT 340 Database 2 What is a Procedures ? • A procedure is a PL/SQL block that performs one or more specific tasks. • And, can be used (accessed / called) by any program that access the database.

8. CPIT 340 Database 2 Creating a Procedure • We can create a Procedure using the CREATE PROCEDURE statement. • The simplified syntax for creating the procedure is as follows: CREATE [OR REPLACE] PROCEDUREprocedure_name ( parameter_nameIN | OUT | IN OUT type [, ...] ) IS | AS BEGIN procedure_body END; /

9. CPIT 340 Database 2 Creating a Procedure (Cont.) • REPLACE: means that we create a procedure to replace an existing procedure. • Parameters Mode (IN | OUT | IN OUT) : • IN: indicate that you must supply a value for the argument when calling the procedure and the value of an IN parameter cannot be changed in the procedure body. • OUT:  indicate that the procedure passes a value for this argument back to its caller after execution. • IN OUT: indicate that you must supply a value for the argument when calling the procedure and that the procedure passes a value back to its caller after execution. • Type: the type of the parameters • Procedure_body: the actual code of the procedure

10. CPIT 340 Database 2 Exercise 1: • Write a simple procedure that only show ‘Hello World’ message on the screen

11. CPIT 340 Database 2 Calling / Executing a Procedure We can run (or call) a procedure using either: • EXECUTE keyword in case when the procedure doesn’t contain parameters. • Or, calling the name of the procedure from a PL/SQL block using the name of the procedure There are three ways to call the procedure that contains parameters:

12. CPIT 340 Database 2 Calling a Procedure (cont.) Example: 1- Positional notation: update_product_price (1,1.5); We only pass the value of the parameters 2- Named notation: update_product_price (p_product_id => 2, p_factore => 1.3); We pass the name and value of the parameters 3- Mixed notation: update_product_price (3, p_factor => 1.7) We use both positional and named notation

13. CPIT 340 Database 2 Exercise 2: • Write a procedure that  finds the minimum number of two values and return the result to the caller and show it to the screen

14. CPIT 340 Database 2 Exercise 3: • Write a procedure that compute the square of the passed value and then return the result to the caller and show it to the screen

15. CPIT 340 Database 2 Dropping a Procedure We drop a procedure using DROP PROCEDURE. Example: DROP PROCEDURE update_product_price;

16. CPIT 340 Database 2 Viewing Errors in a Procedure If the database reports an error when you create a procedure, you can view the errors using the SHOW ERRORS command.

17. CPIT 340 Database 2 Example1: SQL> CREATE PROCEDURE update_customer_dob ( • p_customer_id INTEGER, • p_dob DATE 3 ) AS 4 BEGIN 5 UPDATE customers 6 SET dob = p_dobs 7 WHERE customer_id = p_customer_id; 8 ENDupdate_customer_dob; • / Warning: Procedure created with compilation errors.

18. CPIT 340 Database 2 Example1 (cont.): To view the errors, you use SHOW ERRORS SQL> SHOW ERRORS Errors for PROCEDURE UPDATE_CUSTOMER_DOB: LINE/COL ERROR -------- --------------------------------------------- 5/3 PL/SQL: SQL Statement ignored 6/13 PL/SQL: ORA-00904: invalid column name

19. CPIT 340 Database 2 What is a Function ? • A function is similar to the procedure, except that a function must return a value but procedure not.

20. CPIT 340 Database 2 Creating a Function • We can create a Function using the CREATE FUNCTION statement. • The syntax for creating the function is as follows: CREATE [OR REPLACE] FUNCTION function_name ( parameter_nameIN | OUT | IN OUT type [, ...] ) RETURN type IS | AS BEGIN function_body END; /

21. CPIT 340 Database 2 Example2: • In the text file. “Example2_Creat a function”

22. CPIT 340 Database 2 Exercise 4 • Write a function that count the number of Employees from Emp table • Using the query: • Select count(*) into variableName from tableName;

23. CPIT 340 Database 2 Calling a Function We can call the function by: • Calling the name of the function from a PL/SQL block • There are three ways to call the function:

24. CPIT 340 Database 2 Calling a Function (cont.) Example: 1- Positional notation: circle_area (2); 2- Named notation: circle_area (p_radius => 4);

25. CPIT 340 Database 2 Dropping a Function We drop a function using DROP FUNCTION. Example: DROP FUNCTION circle_area;