3 Very Simple PLSQL Programs to Explain Procedures, Functions and Packages

Here in this tutorial, Procdures, Functions and Packages are fully explored through simple programs. Procedure is a subprogram that performs a given task while Function is same as Procedure but it returns the value. Packages group up Procedures, Functions, Variable, Constants, Cursors and Exceptions.

1. Program to illustrate the use of Procedure. The program is to multiply two numbers. This program is stored in a file name myprocedure.sql

CREATE OR REPLACE PROCEDURE product(a number, b number) AS
c number;
BEGIN
c:=a*b;
DBMS_OUTPUT.PUT_LINE(c);
END product;

Now use SQL>@ myprocedure to create this procedure.
Now use SQL> SHOW ERRORS; to find out if there is any error. This is the optional step.
Now use SQL> EXEC product(3,4); It will give output 12.

You can also call the above procedure through a program below:

SQL> ED CALLPRO
DECLARE
a number;
b number;
BEGIN
a:= &a;
b:= &b;
product(a,b);
END

SQL> @ CALLPRO;

2. Program to illustrate the use of Functions. The program is to multiply two numbers. This program is stored in a file name myfunction.sql

CREATE OR REPLACE FUNCTION product2(a number, b number) RETURN number AS
c number;
BEGIN
c:=a*b;
RETURN c;
END product2;

Now use SQL>@ myfunction to create this function.
Now use SQL> SHOW ERRORS; to find out if there is any error. This is the optional step.
Now use SQL> SELECT product2(3,4) FROM DUAL; It will give output 12.

You can also call the above function through a program below:

SQL> ED CALLFUN
DECLARE
a number;
b number;
result number;
BEGIN
a:= &a;
b:= &b;
result:= product2(a,b);
DBMS_OUTPUT.PUT_LINE(result);
END

SQL> @ CALLFUN;

3. Program to illustrate the use of Packages. This package name is combine and package specification is stored in file pack and package body is stored in file pack_body.

SQL> ED pack
CREATE OR REPLACE PACKAGE combine AS
PROCEDURE product(a number, b number);
FUNCTION product2(a number, b number) RETURN number;
END combine;

Now use SQL>@ pack to create this package.
Now use SQL> SHOW ERRORS; to find out if there is any error. This is the optional step.

Now we will make package body in file pack_body.

SQL> ED pack_body
CREATE OR REPLACE PACKAGE BODY combine AS

PROCEDURE product(a number, b number) AS
c number;
BEGIN
c:=a*b;
DBMS_OUTPUT.PUT_LINE(c);
END product;

FUNCTION product2(a number, b number) RETURN number AS
c number;
BEGIN
c:=a*b;
RETURN c;
END product2;

END combine;

Now use SQL>@ pack_body to create this package body.
Now use SQL> SHOW ERRORS; to find out if there is any error. This is the optional step.

Now we will make call to this package as

SQL> EXEC combine.product(3,4);
SQL> SELECT combine.product(3,4) FROM DUAL;