Database Interface - Procedure - Function

Interfaces are procedure, functions or package that are used for interacting with user or other applications

We have tables, their records and we know how to access them. Imagine we have to calculate the total of 5 subjects of student which user asks for and display all the marks as well as total in a Report format.

Table of Contents

What would be the steps involved in it?

We will first retrieve marks of student name/id requested by the user. Then we would calculate the total of 5 subjects. Then we will display the results for student in the Report format. This can be done by firing individual queries. But user might not be aware of all these queries.

Imagine parents of the student want to see their son/daughters result. But they cannot fire database queries. They will just enter his/her name or ID and they want to see the result. What we would do in such case is group all these related transactions into a block and names it. When parents enter the ID of their son/daughter, we call this named block for that particular ID and get the result. This named block is called as procedure/function. This helps a developer to call each time, when there is a requirement to perform the same task repeatedly.

Procedure

A procedure is a named PL/SQL block which executes one or more related task. Standard syntax for a procedure is as below:

 
CREATE [OR REPLACE] PROCEDURE procedure_name
[ (parameter [,parameter]) ]
IS
[declaration_section]
BEGIN
executable_section
[EXCEPTION
exception_section]
END [procedure_name];

Where

Parameter is a one or more values input for the procedure, for which we need to perform the task or use them to perform the task. In our above example, STUDENT_ID is the input parameter. For the ID input to procedure, the marks and totals are displayed. Parameters being entered are of 3 types – IN OUT and INOUT

IN Parameters values cannot be modified by the procedure/function. It is like a read only value input to procedure/function.

OUT parameter act as a result parameter. It will not have any input value, but the result of the procedure/function will be passed back to the calling program.

INOUT parameters will input some value to the procedure/function and the result of the tasks within will be sent back to the calling program using the same parameter.

Note If parameter type is not mentioned explicitly, it will take IN as default parameter type.

Declaration_Section will have set of variables declared which are local to the procedure and are used to perform the various tasks.

Executable_Section is the actual section of the procedure where the one or more steps of tasks are performed to meet the goal. In our example above, in this section, we would fetch the marks of student, calculate his total and display the result in a report.

Exception_Section is the error handling section of the procedure. Suppose there is an error while calculating the tasks, say there was no data found for the entered Student, then no need to perform rest of the actions. It will throw errors and procedure will fail. But user will not be happy to see that there is some error on the page. If proper error message is displayed, it will please them. Hence, when there is a pre-defined error situation, we capture them and proper message/alternative set of action will be defined.

Let us put our example in a procedure.

CREATE OR REPLACE Procedure sp_getReport (STD_ID IN NUMBER)
IS
-- Declaration Section
ln_Subject1 NUMBER;
ln_Subject2 NUMBER;
ln_Subject3 NUMBER;
ln_Subject4 NUMBER;
ln_Subject5 NUMBER;
ln_total NUMBER;
lv_Std_name varchar2 (50);

-- Executable Section
BEGIN
	-- Retrieve the marks of a student
	SELECT s.STUDENT_NAEM, m.subject1, m.subject2, m.subject3, m.subject4, m.subject5
	INTO lv_Std_name,
		ln_Subject1,
		ln_Subject2,
		ln_Subject3,
		ln_Subject4,
		ln_Subject5
	FROM MARKS m, STUDENT s
	WHERE m.STUDENT_ID = s.STUDENT_ID
	AND m.STUDENT_ID = STD_ID; -- If no records are found by this query, it will jump to exception block below
	
	-- Calculate the Total Marks
	ln_total:= ln_Subject1 + ln_Subject2 + ln_Subject3 + ln_Subject4 + ln_Subject5;
	
	-- Display the Report
	dbms_output.put_line ('Student Report');
	dbms_output.put_line ('-----------------------');
	dbms_output.put_line ('Student name:'|| lv_std_name);
	dbms_output.put_line ('Student Id:'|| STD_ID);
	dbms_output.put_line ('SUBJECT1   SUBJECT2 SUBJECT3 SUBJECT4 SUBJECT5   TOTAL');
	dbms_output.put_line (ln_Subject1 || ' '||ln_Subject2 || ' '||ln_Subject3 || ' '||ln_Subject4 || ' '||ln_Subject5 || ||ln_total);

	-- Exception Section
	EXCEPTION
	WHEN NO_DATA_FOUND THEN
		dbms_output.put_line ('Student Report');
		dbms_output.put_line ('-----------------------');
		dbms_output.put_line ('No Records are found!');
END sp_getReport; -- End of the Procedure

Procedure can be executed in 2 ways:

From the SQL prompt

EXECUTE [or EXEC] procedure_name;

It can be called from an anonymous pl/sql block or any other procedure.

procedure_name;

If we need to remove the procedure from the database, we fire DROP command.

DROP PROCEDURE procedure_name;

Example:

DROP PROCEDURE sp_getReport;

Function

Function is similar to procedure but it will return the result to the calling program. It is different from procedure that it will always return some value to the calling program. Whereas procedure may or may not return value.

CREATE [OR REPLACE] FUNCTION function_name [parameters]
RETURN return_datatype; -- Datatype of result
IS
Declaration_section
BEGIN
Execution_section
Return return_variable;
EXCEPTION
Exception section
Return return_variable;
END;

Suppose we want get the first name and last name of employee.

CREATE OR REPLACE FUNCTION fn_emp_name (EMP_ID NUMBER)
RETURN VARCHAR (20);
IS
lv_emp_name VARCHAR (20);
BEGIN
SELECT e.EMP_FIRST_NAME || ' '||e.EMP_LAST_NAME

INTO lv_emp_name
FROM EMPLOYEE e WHERE e.EMPLOYEE_ID = EMP_ID;

RETURN lv_emp_name; -- Returns the employee name to calling program
END;
/

How do we execute these functions? We have 3 methods to execute this function.

We can directly assign it to a variable, if there is only one return value.

lv_emp_name:= fn_emp_name (103);

We can call it in a select statement directly

SELECT fn_emp_name (100) FROM DUAL;

We can call it inside the display function as below

dbms_output.put_line (fn_emp_name (100));

Functions can be removed from the database just like a procedure.

DROP FUNCTION function_name;

Example:

DROP FUNCTION fn_emp_name;

Package

We have set of different procedures, functions, variables and other database objects which are performing various tasks on STUDENT table. All the database objects are named properly, so that we can identify them easily. But all are scattered in the database. In order to group all of them under one single name, DBMS provides a feature called package. It can be considered as a Folder, which contains different types of file – .txt, .doc, .xls, .pdf etc.

Package is an object which groups procedures, functions, variables, constants, exception etc under one particular name. It has 2 parts – specification and body. The specification part has all the declarations about the objects inside it. Body part has detailed tasks about the objects.

Below is one simple example of a package.

-- Package Specification
CREATE PACKAGE package_name AS
FUNCTION function_name (p_arg IN NUMBER) RETURN NUMBER; -- Declares the function in the package
PROCEDURE procedure_name (p_parameter IN NUMBER) RETURN VARCHAR2; -- Declares the procedure in the package
END package_name;
 /
 
-- Package Body
CREATE PACKAGE BODY package_name AS
-- Detailed set of transactions inside the package function
FUNCTION function_name (p_arg IN NUMBER) RETURN NUMBER IS
BEGIN
RETURN p_arg*10; -- Returns the input parameter multiplied by 10
END function_name;
-- Detailed set of transactions inside the package procedure
PROCEDURE procedure_name (p_parameter IN NUMBER) IS
ld_date DATE;
BEGIN
-- Calculate current date and displays
SELECT SYSDATE into ld_date FROM DUAL;
dbms_output.put_line ('Today’s Date is: ’|| ld_date);
END procedure_name;
END package_name; -- End of the package body
/

Creating the package specification alone in the database creates a package in the database. But we will not be able to call the objects inside the package. We can call the procedures and functions inside the package similar to the way we call simple procedure or function. But we need to append package name to the procedure or function.

-- Below are the methods to call package function
lv_emp_name:= package_name.function_name (103);
SELECT package_name.function_name (100) FROM DUAL;
dbms_output.put_line (package_name.function_name (100));

-- Below are the methods to call package procedure
EXECUTE [or EXEC] package_name.procedure_name;
package_name.procedure_name;

In order to remove the package from the database, we can fire below query.

DROP package package_name; -- removes both specification and body from DB
DROP package BODY package_name; -- removes only package body from DB