PL-SQL Subprograms (Procedure and Function)


Before we talk about procedure, let's know some basic about subprogram.


What is Subprogram?

A PL/SQL subprogram is a named PL/SQL block that can be invoked with a set of parameters. A subprogram can be either a procedure or a function. Typically, you use a procedure to perform an action and a function to compute and return a value.


Advantages of using Subprograms

  • Subprograms let you extend the PL/SQL language.
  • Subprograms let you break a program into manageable, well-defined modules.
  • Subprograms promote re-usability.
  • Subprograms promote maintainability.
  • Subprograms can be grouped into PL/SQL packages.
  • You can hide the implementation details of subprograms by placing them in PL/SQL packages.
  • Dummy subprograms ("stubs") let you defer the definition of procedures and functions until after you have tested the main program.

What are the types of subprogram?

There are three types of subprogram or three ways to create subprogram.

  • Standalone Subprogram: A Subprogram created at Schema Level with CREATE PROCEDURE or CREATE FUNCTION statement. These programs are stored in database until you drop it with the DROP PROCEDURE or DROP FUNCTION statement.
  • Packaged Subprogram: A Subprogram created inside a Package. It is stored in the database until you drop the package with the DROP PACKAGE statement.
  • Nested Subprogram: A Subprogram created inside a PL/SQL block. It is stored in the database until you drop the package with the DROP PACKAGE statement. You can either declare and define it at the same time, or you can declare it first (forward declaration) and then define it later in the same block. A nested subprogram is stored in the database only if it is nested within a standalone or packaged subprogram.

What are the parts of Subprogram?

A subprogram always has a name, and can have a parameter list.

Like every PL/SQL block, a subprogram has an optional declarative part, a required executable part, and an optional exception-handling part as we discussed in the PL-SQL Introduction article.


Basic structure for creating Procedure and Function?

Structure for creating Procedure:

CREATE [OR REPLACE] FUNCTION function_name
[(parameter_name [IN | OUT | IN OUT] type [, ...])]
RETURN return_datatype
{IS | AS}
BEGIN
< function_body >
END [function_name];

Structure for creating Function:

CREATE [OR REPLACE] PROCEDURE procedure_name
[(parameter_name [IN | OUT | IN OUT] datatype [, ...])]
{IS | AS}
BEGIN
< procedure_body >
END procedure_name;

Structure explained:

  • procedure_name and function-name specifies the name of the procedure and function respectively.
  • [OR REPLACE] option allows modifying an existing procedure or function.
  • The optional parameter list contains name, mode and types of the parameters.
  • The function must contain a return statement. A procedure cannot have a return statement.
  • In RETURN clause, return_datatype specifies that data type you are going to return from the function.
  • procedure_body and function-body contains the executable part for procedure and function.
  • The AS keyword is used instead of the IS keyword for creating a standalone function.

What is difference between Procedure and Function?

  • A procedure and a function have the same structure, except that:
  • A function heading must include a RETURN clause that specifies the data type of the return value. A procedure heading cannot have a RETURN clause.
  • A function must have at least one RETURN statement in its executable part. In a procedure, the RETURN statement is optional.
  • A function always RETURN a value while procedure may or may not return a value(procedure uses OUT and IN OUT mode for returning value).
  • A function can be called from inside a procedure but procedure cannot be called from inside function.

What are the types of parameter modes in Subprogram?

There are three types of paramertes modes in Subprogram: IN , OUT and IN OUT

To know more in details about parameter, click here Declaring and Passing Subprogram Parameters.


Example of Standalone Subprogram (At Schema Level)?

Create Procedure:
CREATE OR REPLACE PROCEDURE GETSQUARE(NUM NUMBER) 
AS
NUM_SQUARE NUMBER;
BEGIN
  NUM_SQUARE := NUM * NUM;
  DBMS_OUTPUT.PUT_LINE(NUM_SQUARE);
END;
Execute Procedure:
SET SERVEROUTPUT ON
BEGIN
GETSQUARE(10);
-- Below line give error if you call procedure as function.
-- DBMS_OUTPUT.PUT_LINE('Executing Function:' || GETSQUARE(10)); 
END;
/
OR

EXECUTE GETCUBE(10);
Delete Procedure:
DROP PROCEDURE GETSQUARE;
Create Function:
CREATE OR REPLACE FUNCTION GETCUBE(NUM NUMBER) 
RETURN NUMBER
AS
NUM_CUBE NUMBER;
BEGIN
  NUM_CUBE := NUM * NUM * NUM;
  RETURN NUM_CUBE;
END;
Executie Function:
SET SERVEROUTPUT ON
BEGIN
DBMS_OUTPUT.PUT_LINE('Executing Function:' || GETCUBE(10));
-- Below line give error if you call function as procedure.
-- GETCUBE(10);
END;
/
Delete Function:
DROP FUNCTION GETCUBE;

Example of Nested Subprogram (At Block Level)?

In a block, you can create multiple nested subprograms. If they invoke each other, you need forward declaration, because a subprogram must be declared before it can be invoked. With forward declaration, you declare a subprogram, but do not define it until after you have defined the other subprograms that invoke it. A forward declaration and its corresponding definition must appear in the same block.

Creating Nested Subprograms that Invoke Each Other:
DECLARE
  -- Declare Procedure1 (forward declaration):
  PROCEDURE PROCEDURE1(NUMBER1 NUMBER);
  
  -- Declare and define Procedure2:
  PROCEDURE PROCEDURE2(NUMBER2 NUMBER) IS
  BEGIN
    PROCEDURE1(NUMBER2);
  END;

  -- Define proc 1:
  PROCEDURE PROCEDURE1(NUMBER1 NUMBER) IS
  BEGIN
    PROCEDURE2 (NUMBER1);
  END;

BEGIN
  NULL;
END;
/

Example of Packaged Subprogram (At Package Level)?

A package is a schema object that groups logically related PL/SQL types, variables, and subprograms. Packages usually have two parts, a specification ("spec") and a body; sometimes the body is unnecessary.

The specification is the interface to the package. It declares the types, variables, constants, exceptions, cursors, and subprograms that can be referenced from outside the package. The body defines the queries for the cursors and the code for the subprograms.

Click here to know more in detail about Packaged Subprogram: PL/SQL Package


Overloading PL/SQL Subprogram Names

PL/SQL lets you overload local subprograms, packaged subprograms, and type methods. You can use the same name for several different subprograms as long as their formal parameters differ in number, order, or data type family.

Example:

DECLARE
TYPE date_tab_typ IS TABLE OF DATE   INDEX BY PLS_INTEGER;
TYPE num_tab_typ  IS TABLE OF NUMBER INDEX BY PLS_INTEGER;

hiredate_tab  date_tab_typ;
sal_tab       num_tab_typ;

PROCEDURE initialize (tab OUT date_tab_typ, n INTEGER) IS
BEGIN
FOR i IN 1..n LOOP
tab(i) := SYSDATE;
END LOOP;
END initialize;

PROCEDURE initialize (tab OUT num_tab_typ, n INTEGER) IS
BEGIN
FOR i IN 1..n LOOP
tab(i) := 0.0;
END LOOP;
END initialize;

BEGIN
initialize(hiredate_tab, 50);  -- Invokes first (date_tab_typ) version
initialize(sal_tab, 100);      -- Invokes second (num_tab_typ) version
END;
/

Next Article: Declaring and Passing Subprogram Parameters


For any questions, suggestions or feedback, Please write to us. Thanks for Reading :)

No comments:

Post a Comment

Subscribe to: Posts (Atom)