How are stored procedures different from functions?

Functions are forced to use the RETURNS and RETURN arguments, whereas it is not necessary for stored procedures. In other words, a stored procedure is more flexible to write any code that we want, whereas functions have a rigid structure and functionality.

Why should we use stored procedures?

A stored procedure provides an essential layer of security between the database and the user interface. Security is provided through data access controls because end users can change or enter data but can not write procedures. It improves productivity because statements in a stored procedure must be written only once.

What is the difference between stored procedure and view?

The view is simple and showcases data stored in the database tables, whereas a stored procedure is a group of statements that can be executed. A view is faster as it displays data from the tables referenced, whereas a store procedure executes SQL statements.

Table of contents

Introduction

Create Procedure

2.1.

Modes of Parameters

Execute Procedure

3.1.

Using the EXECUTE keyword

3.2.

Calling the name of the procedure from a PL/SQL block

Drop Procedure

Advantages

Disadvantages

FAQs

Key Takeaways

Last Updated: Mar 27, 2024

PL/SQL Procedures

Author Shivani Kumari

Introduction

The stored procedure in PL/SQL is a program block that performs specific tasks. It can be considered a function or a method. But the procedure does not return a value directly like functions. It can be invoked by triggers or other procedures etc. It reduces the network traffic and increases performance because the commands in the stored procedure are executed as a single batch of code.

It consists of two parts.

Header: The header includes the name of the procedure and the parameters passed to the procedure.
Body: Body includes declaration section, execution section, and exception section.

Create Procedure

Syntax

CREATE [OR REPLACE] PROCEDURE procedure_name [list of parameters]

IS | AS

//Declaration block

BEGIN

//Execution block

EXCEPTION

//Exception block

END;

Declaration Block: It is an optional block. It contains cursors, constants, variables, exceptions, and nested subprograms. These things are local to the subprogram and cease to exist when the subprogram completes execution.
Executable Block: This is a mandatory block and contains statements that perform the designated action.
Exception Block: This is again an optional block. It includes the code that handles run-time errors.

Modes of Parameters

IN-parameters: IN mode parameters are read-only parameters. The procedure cannot change the value of IN parameters.
OUT-parameters: OUT mode parameters are the write-only parameters used to return values to the calling program. The procedure can change the value of OUT parameters.
IN OUT-parameters: IN OUT mode parameters are both read and write parameters, i.e., a procedure can read and change the IN OUT parameter value and return it to the calling program.

Example

In this example, we create a procedure named add_employee to add new employee data in the Employee table. The employeeID and name are taken as IN mode parameters in the procedure.

CREATE OR REPLACE PROCEDURE add_employee(employeeID IN NUMBER, name IN VARCHAR2)

BEGIN

insert into Employee values(employeeID, name);

END;

Output

Recommended topics, procedure call in compiler design and Tcl Commands in SQL

Execute Procedure

The procedure can be executed in two ways:

Using the EXECUTE keyword

Syntax

EXECUTE procedure_name();

Example

EXECUTE add_employee();

Output

Calling the name of the procedure from a PL/SQL block

Syntax

BEGIN

procedure_name;

END;

Example

BEGIN

add_employee;

END;

Output

Drop Procedure

We use the DROP PROCEDURE keyword followed by the procedure name to drop a procedure.

Syntax

DROP PROCEDURE procedure_name;

Example

DROP PROCEDURE add_employee;

Output

Must Read SQL Clauses

Advantages

The advantages of using stored procedures are listed below:

Reduce network traffic: Stored procedures are precompiled, so we do not need to compile them every time. It reduces the network traffic because it executes in a single execution plan. A stored procedure is also cached on the server.
Security: When we call a stored procedure in the network, we can execute the stored procedure based on your parameter, but we can see the table and database object name.
Maintainability: Stored procedure scripts are in one location, so updating and tracking dependencies based on schema changes becomes more accessible. The stored procedure can be tested independent of the application.

Disadvantages

Restricted for complex business logic: Actually, stored procedure constructs are not designed for developing complex and flexible business logic.
Memory Usage: Store procedures use a lot of memory.
Debugging: Debugging in stored procedures will either not be possible at all or be highly clunky. Some relational databases, such as SQL Server, have some debugging capabilities and others none. There is nothing worse than using a database profiler to track down an application issue or debug your database via print statements.

FAQs

How are stored procedures different from functions?
Functions are forced to use the RETURNS and RETURN arguments, whereas it is not necessary for stored procedures. In other words, a stored procedure is more flexible to write any code that we want, whereas functions have a rigid structure and functionality.
Why should we use stored procedures?
A stored procedure provides an essential layer of security between the database and the user interface. Security is provided through data access controls because end users can change or enter data but can not write procedures. It improves productivity because statements in a stored procedure must be written only once.
What is the difference between stored procedure and view?
The view is simple and showcases data stored in the database tables, whereas a stored procedure is a group of statements that can be executed. A view is faster as it displays data from the tables referenced, whereas a store procedure executes SQL statements.

Key Takeaways

Stored procedures are a block of code that can perform a specific task. It promotes code reusability like functions in C/C++, Java, etc. We learned about creating procedures and how to execute them. We also discover the advantages and disadvantages of using stored procedures.