PL/SQL Subprograms (Procedures, Functions and Packages)

[THE ADMIN]

Learning Objectives 
After completing this session, you will be able to: 

• Write Procedures
• Write Functions
• Write Packages

Understand Subprograms 

• Is a named PL/SQL block that can accept parameters and be invoked from a calling environment
• Is of two type:
• A procedure that performs an action
• A function that computes a value.
• Is based on standard PL/SQL block structure.
• Provides modularity, reusability, extensibility and maintainability.

Block structure for PL/SQL subprograms 

The header is relevant for named blocks only and determines the way that the program unit is called or invoked. 


The header determines: 

• The PL/SQL subprogram type, that is, either a procedure or a function
• The name of the subprogram
• The parameter list, if one exists
• The RETURN clause, which applies only to functions

The IS or AS keyword is mandatory.

Benefits of Subprograms 

• Easy maintenance
• Improved data security and integrity
• Improved performance
• Improved code clarity

Procedures 
What is a Procedure? 
It is a sub program in the PL/SQL block, which can perform a specific task when invoked explicitly with or without parameter. A Procedure has two parts: Specification and Body 
Specification: This section begins with the keyword Procedure followed by its name and Parameter list (optional) 
Body: Procedure Body begins with the keyword IS/AS and ends with the keyword END followed by the procedure name (optional) 
Syntax: 

Code:

CREATE OR REPLACE PROCEDURE <PROCEDURE_NAME> 
(<PARAMETER> [MODE] <DATA TYPE>,) 
IS/AS 
[LOCAL VARIABLE DECLARATION] 
BEGIN 
PL/SQL EXECUTABLE STATEMENT 
[EXCEPTION] 
[EXCEPTION HANDLERS] 
END [PROGRAM UNIT NAME];

In Syntax: 

• [REPLACE]: Option indicates that if the procedure exists, it will be dropped and replaced with the new version created by the statement.
• <parameter>: Name of a PL/SQL variable whose value is passed to or populated by the calling environment, or both, depending on the mode being used
• [Mode ]: Type of argument: IN (default), OUT, IN OUT
• <Data type>: Data type of the argument–can be any SQL / PLSQL data type.

PL/SQL block starts with either BEGIN or the declaration of local variables and ends with either END or END procedure_name.

Example: 

Code:

CREATE OR REPLACE PROCEDURE Addproduct( 
V_prdouct_code Varchar2(100); 
V_prdouct_name Varchar2(100);) AS 
BRGIN 
INSERT INTO product_dim (product_key, 
product_code, 
product_name) 
VALUES (product_sequence.nextval, 
v_product_code, 
v_product_name); 
COMMIT; 
END Addproduct;

Formal parameters can have three modes: IN, OUT, or INOUT. Their features are described as follows: 

IN: 
Default mode 
Takes the value inside the program 
Cannot be changed inside the subprogram 

OUT: 
Takes the value out of the subprogram 
Can be changed inside 

INOUT: Takes the value inside a subprogram and brings the value out of the subprogram 

Example with in parameter: 

Code:

CREATE OR REPLACE PROCEDURE sp_addproduct( 
IN V_prdouct_code Varchar2(100); 
IN V_prdouct_name Varchar2(100);) AS 
BRGIN 
INSERT INTO product_dim (product_key, 
product_code, 
product_name) 
VALUES (product_sequence.nextval, 
v_product_code, 
v_product_name); 
COMMIT; 
END sp_addproduct;

Example with OUT parameter: 

Code:

CREATE OR REPLACE PROCEDURE sp_calc_loanamount( 
pv_loan_no IN NUMBER, 
pv_loan_amount OUT NUMBER) IS 
BEGIN 
SELECT loan_amount*interest_rate INTO pv_loan_amount 
FROM daily_pipeline_loan_fact 
END sp_calc_loanamount;

Example of procedure with INOUT parameter: 

Code:

CREATE OR REPLACE PROCEDURE FORMAT_PHONE_NO ( V_PHONE IN OUT VARCHAR2) 
IS 
BEGIN 
V_PHONE := ‘(‘ || SUBSTR(V_PHONE,1,3) || ‘)’ || SUBSTR (V_PHONE,4,3) || ‘-’ || SUBSTR(V_PHONE,7); 
END FORMAT_PHONE_NO; 
/

Executing the procedure: 

Code:

DECLARE 
loan_id NUMBER; 
loan_amount NUMBER; 
BEGIN 
loan_id := &loan_no; 
Sp_calc_loanamount(loan_id,loan_amount); 
DBMS_OUTPUT.PUT_LINE(‘The Total loan amount for loan number’ || loan_id || ‘ is’ ||loan_amount); 
END; 
/

Removing a Procedure: 

Code:

DROP PROCEDURE <procedure_name> 
DROP PROCEDURE sp_calc_loanamount;

Functions 

• Function is a named PL/SQL block that returns a value.
• A function can be stored in the database as a schema object for repeated execution.
• A function is called as part of an expression.
• Functions can be used in a SELECT statement.

Syntax: 

Code:

CREATE OR REPLACE FUNCTION <FUNCTION NAME> 
[(parameter1 [mode1] datatype1, 
parameter2 [mode2] datatype2, 
. . .)] 
RETURN DATATYPE IS[<LOCAL DECLARATION>] 
BEGIN 
EXECUTABLE STATEMENTS 
[EXCEPTION] [EXCEPTION HANDLERS] 
END [FUNCTION_NAME];

In syntax: 

• [REPLACE]: Option indicates that if the function exists, it will be dropped and replaced with the new version created by the statement.
• function_name: Name of the function.
• Parameter: Name of a PL/SQL variable whose value is passed into the function
• Mode: The type of the parameter; only IN parameters should be declared
• Datatype: Data type of the parameter
• RETURN datatype: Data type of the RETURN value that must be output by the function

Example: 

Code:

CREATE OR REPLACE FUNCTION fn_get_loan_amount ( pv_loan_id IN number) 
RETURN NUMBER 
IS 
v_loanamount NUMBER; 
BEGIN 
SELECT loan_amount into V_loanamount 
FROM daily_pipeline_loan_fact 
WHERE loan_no = pv_loan_id ; 
RETURN v_loanamount; 
END fn_get_loan_amount;

Executing a Function: 

Code:

DECLARE 
loan_id NUMBER; 
loan_amount NUMBER; 
BEGIN 
loan_id := &loan_no; 
loan_amount:=fn_get_loan_amount(loan_id); 
DBMS_OUTPUT.PUT_LINE(‘The Total loan amount for loan number’ || loan_id || ‘ is’ ||loan_amount); 
END;

Locations to Call User-Defined Function:

• Select list of a SELECT command
• Condition of the WHERE and HAVING clauses
• CONNECT BY, START WITH, ORDER BY, and GROUP BY clauses
• VALUES clause of the INSERT command
• SET clause of the UPDATE command

Example: 

Code:

CREATE OR REPLACE FUNCTION fn_service_tax ( pv_loan_amount IN number) 
RETURN NUMBER 
IS 
v_tax NUMBER; 
BEGIN 
RETURN (v_loanamount*0.12); 
END fn_service_tax; 
SELECT loan_no , fn_service_tax(loan_amount) 
FROM daily_pipeline_loan_fact

To be callable from SQL expressions, a user-defined function must: 

• Be a stored function
• Accept only IN parameters
• Accept only valid SQL data types, not PL/SQL
• Specific types, as parameters Return data types that are valid SQL data types,
• Not PL/SQL specific types

Removing a Function: 

Code:

DROP FUNCTION <function_name> 
DROP FUNCTION fn_service_tax;

All the privileges granted on a function are revoked when the function is dropped. 

The CREATE OR REPLACE syntax is equivalent to dropping a function and recreating it. Privileges granted on the function remain the same when this syntax is used.


Procedure VS Function 

Procedures	Functions
Execute as a PL/SQL statement	Invoke as part of an expression
Do not contain RETURN clause in the header	Must contain a RETURN clause in the header
Can return none, one, or many values	Must return a single value
Can contain a RETURN statement	Must contain at least one RETURN statement

Packages 
What is a Package? 
A package is a collection of Functions, Procedures, Global variables and cursors stored in server in compiled form. A package consists of 2 parts: 
Package Specification: This acts as an interface to the user applications. This part declares the, PL/SQL types, variables, constants, exception, cursor and sub-programs (Functions and procedures). This part is created using CREATE PACKAGE command. 
Package Body: It implements the specifications by defining the cursors and sub-programs. This part is created using CREATE PACKAGE BODY command. 

Oracle stores package specification and body separately in the data dictionary. A package specification can exists without a package body but not vice versa. 

An element of a package, whether it is a variable or a module, can either be 
Public 
When defined in the specification a public element can be referenced from other programs and PL/SQL blocks 
Private 
When defined only in the body of the package, but does not appear in the specification. A private element cannot be referenced outside of the package. It can only be referenced by other elements within the package 


The sub-programs that are present inside a package cannot exist separately as database objects. A package cannot be called by itself. Only the procedures and functions from within the package can be called with reference to the package using the dot (.) operator. When one sub-program is called, then all other sub-programs are also loaded into the memory, hence the subsequent call for any other modules becomes fast.


Package specification Syntax: 

Code:

CREATE [OR REPLACE] PACKAGE package name 
IS|AS 
public type and item declarations 
subprogram specifications 
END package_name;

• The REPLACE option drops and recreates the package specification.
• Variables declared in the package specification are initialized to NULL by default.

Example package specification: 

Code:

CREATE PACKAGE pkg_total_amount 
AS 
PROCEDURE PROCEDURE sp_calc_loanamount 
(pv_loan_no IN NUMBER , pv_loan_amount OUT NUMBER) ; 
FUNTION fn_service_tax ( pv_loan_amount IN number); 
END PK1;

Creating package body Syntax: 

Code:

CREATE [OR REPLACE] PACKAGE BODY package_name 
IS|AS 
private type and item declarations 
subprogram bodies 
END package_name;

• The REPLACE option drops and recreates the package body.
• Identifiers defined only in the package body are private constructs. These are not visible outside the package body.

Example: 

Code:

CREATE PACKAGE BODY pkg_total_amount AS 
PROCEDURE sp_calc_loanamount(pv_loan_no IN NUMBER, pv_loan_amount OUT NUMBER) IS 
BEGIN 
SELECT loan_amount*interest_rate INTO pv_loan_amount 
FROM daily_pipeline_loan_fact 
END sp_calc_loanamount; 
FUNCTION fn_service_tax( pv_loan_amount IN number) 
RETURN NUMBER 
IS 
v_tax NUMBER; 
BEGIN 
RETURN (v_loanamount*0.12); 
END fn_service_tax; 
END pkg_total_amount;

Removing a package: 

To remove the package specification and the body, use the following syntax: 

Code:

DROP PACKAGE package_name;

To remove the package body, use the following syntax: 

Code:

DROP PACKAGE BODY package_name;

Advantage: 
Better performance: 

• The entire package is loaded into memory when the package is first referenced.
• There is only one copy in memory for all users.

Overloading: 
With packages you can overload procedures and functions, which means you can create multiple subprograms with the same name in the same package, each taking parameters of different number or data type. 

Oracle Supplied Packages 

Some Oracle server-supplied packages are: 

• DBMS_DDL 
  
• DBMS_JOB 
  
• DBMS_OUTPUT 
  
• UTL_FILE

The DBMS_OUTPUT package enables you to output messages from PL/SQL blocks. Available procedures include: 

• PUT 
  
• NEW_LINE
• PUT_LINE
• GET_LINE
• GET_LINES
• ENABLE/DISABLE

Function or Procedure	Description
PUT	Appends text from the procedure to the current line of the line output buffer
NEW_LINE	Places an end_of_line marker in the output buffer
PUT_LINE	Combines the action of PUT and NEW_LINE
GET_LINE	Retrieves the current line from the output buffer into the procedure
GET_LINES	Retrieves an array of lines from the output buffer into the procedure
ENABLE/DISABLE	Enables or disables calls to the DBMS_OUTPUT procedures

Summary 

• A procedure is a subprogram that performs an action.
• You can compile and save a procedure in the database.
• Parameters are used to pass data from the calling environment to the procedure.
• There are three parameter modes: IN, OUT, and IN OUT.
• A function is a named PL/SQL block that must return a value.
• A function is invoked as part of an expression.
• A function stored in the database can be called SQL statements.
• Generally, you use a procedure to perform action and a function to compute a value.
• A package is a compiled database object that logically groups PL/SQL types and subprograms. It consists of two parts: specification and body.
• The specification is the interface to your applications; it declares the types, variables, constants, exceptions, cursors, and subprograms available for use. The body fully defines cursors and subprograms, and so implements the specification.

Test Your Understanding 

1. Create a procedure that will accept a percentage and JOB, and will show the reflection on the total salary. Calculate the total salary before the increment for the particular JOB and show the difference. 
   
2. Create a procedure that will accept the LOAN amount and EMPNO as input and display the installment amount and number of installments to be paid. (Max loan amount will be his current salary * total number of years of service. Loan will be paid in equal monthly installment. Installment amount will be 1/10th of monthly salary) 
   
3. What is a Subprogram? 
   
4. What is the use of REPLACE keyword in creating procedure syntax?
5. What is the different mode of parameters?
6. What is difference between a procedure and a function?
7. Function received which type of parameter.
8. What is a package?
9. What are the advantages of package?
10. Give some example of Oracle supplied packages.