Site icon Technology and Trends

Stored Procedure in Database

A stored procedure is defined as a group of SQL (Structured Query Language) statements or subroutines that are stored inside the relational database management system (RDBMS) that performs a particular task. They are stored in the database data dictionary and are used for operations such as deleting a database or indexing a table. In this blog post, we will read about how Stored Procedures can be used in the Database.

They are stored inside the database and form a logical unit and perform a particular task. For example, operations such as hire, fire, promote, and lookup on an employee database could be coded as stored procedures and executed by application codes (hire, fire, promote, lookup) could be coded as stored procedures executed by application code.

Many commercial-based applications use a stored procedure as an API (Application Programming Interface) for simplicity and security purposes. They don’t come by default as part of the relational database model, but can be included in many commercial applications.

Creating a Stored Procedure

The exact syntax of the stored procedure might be different for different databases. In general, the syntax would look like the below.

  CREATE PROCEDURE <owner>.<procedure name>

     <Param> <datatype>
  AS

     <Body>

We can take below example below.

CREATE PROCEDURE SAMPLE_STORED_PROCEDURE
AS
     SELECT name 
     FROM SAMPLE_TABLE;
EXEC SAMPLE_STORED_PROCEDURE;

Calling Stored Procedure through Java

We can use CallableStatement class in JDBC (Java Database Connectivity) API to call store procedure. Let’s take an example to demonstrate this approach.

CallableStatement cs = con.prepareCall("{call SAMPLE_STORED_PROCEDURE}");
ResultSet rs = cs.executeQuery();

Advantages of using Stored Procedures

There are many advantages to using Stored Procedures. Some of them are listed below.

Recursive Stored Procedure

A recursive stored procedure calls itself until a boundary condition is reached. This recursive functionality in the database is advantageous to programmers who want to deploy the same set of code several times when required. Improper use of this stored procedure might’ve caused slowness in the system’s em or infinite loop execution.

Exit mobile version