Creating Procedures Functions and Packages in Oracle PL/SQL -

In this tutorial, you will see a detailed description of how to create and execute the named blocks (procedures and functions and packages).

What is a Procedure?

A Procedure in PL/SQL is a named subprogram unit that consists of a group of PL/SQL statements. There are two types of PL/SQL blocks: anonymous and named.

An anonymous block is a block of code that is compiled each time it is issued. It is not stored in the database and it cannot be invoked from another PL/SQL block of code.
A named block is the block of code that is compiled and stored in the database and can be called by another block of code.

Characteristics of Procedure block in PL/SQL:

PL/SQL procedure contains declaration part (optional), execution part, exception handling part (optional).
It can have three types of parameters – IN, OUT, or IN OUT.
A Procedure in SQL can have a RETURN statement to return the control to the calling block, but it cannot return any values through the RETURN statement.

Types of Parameters in PL/SQL code:

IN Parameter: It is used to pass input values to the program and the value of this parameter cannot be changed during its execution inside the program.
OUT parameter: It is used to pass output values to the program and the value of this parameter can be changed during its execution inside the program.
IN OUT parameter: It is used for both giving input and for getting output from the program and the value of this parameter can be changed during its execution inside the program.

Syntax of a procedure:

CREATE OR REPLACE PROCEDURE <procedure_name> (
	<param_name> IN/OUT/IN OUT <datatype>
	........
	) IS/AS
	/* Declarative section is here */
BEGIN
	/* Executable section is here */
EXCEPTION
	/* Exception section is here */
END [procedure_name];

The procedure is first created using CREATE keyword, if it already exists it is replaced using REPLACE keyword. Keyword IS will be used when the stored procedure in Oracle is nested into some other blocks. If the procedure is standalone then AS will be used. Once it is created, it is then compiled and stored in the database which can be used to run later. We can have one or more comma-separated parameters. Let’s say we have a table PERSONS with three columns person_id, first_name, and last_name. So let’s write a procedure AddNewPerson to insert a new record into this table.

CREATE OR REPLACE PROCEDURE AddNewPerson (
	p_ID persons.person_id%TYPE,
	p_First_Name persons.first_name%TYPE,
	p_Last_Name persons.last_name%TYPE) AS
BEGIN
	INSERT INTO persons (person_id, first_name, last_name) VALUES (p_ID, p_First_Name, p_Last_Name);
END AddNewPerson;

Once this procedure is created and compiled, we can invoke it via another procedure as below:

BEGIN
	AddNewPerson(100, 'Ted', 'Mosby');
END;

What is a Function?

A Function is a standalone program and is very similar to a procedure. Both take parameters, which can be of any mode. Both are different forms of PL/SQL blocks, with declarative, executable, and exception sections. Both can be stored in the database or declared within a block. However, a procedure call is a PL/SQL statement by itself, while a function call is called as part of an expression. In function, RETURN is mandatory, it either returns a value or raises an Exception. Syntax of a function:

CREATE OR REPLACE FUNCTION <function_name> (
	<param_name> IN/OUT <datatype>
	............
	) RETURN <datatype> [ IS | AS ]
	/* Declarative section is here */
BEGIN
	/* Executable section is here */
EXCEPTION
	/* Exception section is here */
END;

The function is created using CREATE FUNCTION keyword, if it already exists it is replaced using OR REPLACE Keyword with the current one. The name of the function has to be unique and the RETURN datatype should be mentioned. Keyword IS will be used, when the procedure is nested into some other blocks. If the procedure is standalone then AS will be used. Other than this coding standard, both have the same meaning. Considering the above example, let’s query if a person exists in the author’s table with the last name ‘Mosby’.

CREATE OR REPLACE FUNCTION QueryPerson (
	p_last_name IN persons.last_name%TYPE) RETURN BOOLEAN AS
    
	v_last_name persons.last_name%TYPE;
BEGIN
	SELECT first_name
	INTO v_last_name
	FROM persons
	WHERE last_name = 'Mosby';
	IF v_last_name IS NULL THEN
		RETURN FALSE;
	ELSE
		RETURN TRUE;
	END IF;
END QueryPerson;

set serveroutput on;
BEGIN
	FOR v_Rec IN (SELECT last_name, first_name FROM persons) LOOP
		IF QueryPerson(v_Rec.last_name) THEN
			DBMS_OUTPUT.PUT_LINE('Mr.' || v_Rec.last_name || ' is a valid Author');
		END IF;
	END LOOP;
END;

Apart from such custom functions, Oracle PL/SQL provides many build-in functions like TO_CHAR, TO_DATE, TO_NUMBER, INSTR, SUBSTR, LENGTH, UPPER, LOWER, INITCAP, LPAD, RPAD, LTRIM, RTRIM, ADD_MONTHS, SYSDATE, ROUND, TRUNC, MONTHS_BETWEEN.

Dropping Procedures and Functions: Just as a table can be dropped, procedures and functions can also be dropped. This removes the procedure or function from the data dictionary. The syntax for dropping a procedure and a function is

DROP PROCEDURE procedure_name;
DROP FUNCTION function_name;

where procedure_name is the name of an existing procedure, and function_name is the name of an existing function. For example, the following statement drops the AddNewPerson procedure:

DROP PROCEDURE AddNewPerson;

What is a Package?

A Package is a PL/SQL construct that allows related objects to be stored together. A package has two separate parts: the specification and the body. Each of them is stored separately in the data dictionary. Unlike procedures and functions, which can be contained locally in a block or stored in the database, a package can only be stored, it cannot be local. Anything that can go in the declarative part of a block can go in a package. This includes procedures, functions, cursors, types, and variables. One advantage of putting these objects into a package is the ability to reference them from other PL/SQL blocks.

Package Specification: The package specification (also known as the package header) contains information about the contents of the package. However, it does not contain the code for any subprograms. Consider the following example:

CREATE OR REPLACE PACKAGE Test_Pkg AS 

    PROCEDURE AddNewPerson ( p_ID persons.person_id%TYPE,
        p_First_Name persons.first_name%TYPE,
        p_Last_Name persons.last_name%TYPE
    );
    
    FUNCTION QueryPerson ( p_last_name IN persons.last_name%TYPE);

END TEST_PKG;

The Package is created using CREATE PACKAGE keyword, if it already exists it is replaced using OR REPLACE keyword with the current one. The name of the package has to be unique. Elements of the package can appear in any order. However, as in a declarative section, an object must be declared before it is referenced. All types of elements do not have to be present.

Package Body: The package body is a separate data dictionary object from the package header. It cannot be successfully compiled unless the package header has already been successfully compiled. The body contains the code for the forward subprogram declarations in the package header. It can also contain additional declarations that are global to the package body but are not visible in the specification.

CREATE OR REPLACE PACKAGE BODY Test_Pkg  AS
    <global_variables>
    
    PROCEDURE AddNewPerson (
        p_ID persons.person_id%TYPE,
        p_First_Name persons.first_name%TYPE,
        p_Last_Name persons.last_name%TYPE) AS
    BEGIN
        INSERT INTO persons (person_id, first_name, last_name) VALUES (p_ID, p_First_Name, p_Last_Name);
    END AddNewPerson;

    FUNCTION QueryPerson (
        p_last_name IN persons.last_name%TYPE) RETURN BOOLEAN AS
    
        v_last_name persons.last_name%TYPE;
    BEGIN
        SELECT first_name
        INTO v_last_name
        FROM persons
        WHERE last_name = 'Mosby';
        IF v_last_name IS NULL THEN
            RETURN FALSE;
        ELSE
            RETURN TRUE;
        END IF;
    END QueryPerson;
END Test_Pkg;

Any object declared in a package header is in scope and is visible outside the package, by qualifying the object with the package name.
Inside a package, procedures and functions can be overloaded. This means that there is more than one procedure or function with the same name, but with different parameters. This is a handy feature because it allows the same operation to be applied to objects of different types. You cannot overload two functions that differ only in their return type.

That’s all about this article!

Request you to subscribe to my YouTube channel for regular updates.

Enter your keyword

post

Leave a Reply Cancel reply