Explanation of Stored Procedures and Cursors in Database

Stored Procedure

A stored procedure is a collection of SQL statements, or it can be said as a subprogram with a specific name stored in the database.

The process carried out by a stored procedure is to receive input parameters, then return values in the form of output parameters to the caller, and perform data processing operations on the database run by a program such as a trigger or other stored procedures.

Using stored procedures has several advantages, namely:

• Improves application performance, because the stored procedure that has been created runs faster than the SQL commands sent by the application that have not been compiled.
• Reduces network traffic between database and application, because there is no need to send uncompiled SQL.
• Can be used repeatedly and transparently across all applications.

However, using stored procedures also has several drawbacks, namely:

• Cannot be debugged.
• Increases load on server hardware.
• Writing is not easy and requires specific knowledge.

---

Stored Procedure Syntax

The syntax found in stored procedures is as follows:

Stored Procedure Writing Syntax

DELIMITER //

CREATE PROCEDURE procedure_name()
BEGIN
    sql query
END //

DELIMITER ;

Stored Procedure Calling Syntax

CALL procedure_name();

Stored Procedure Variable Declaration Syntax

DECLARE variable_name datatype(length) DEFAULT value;

Stored Procedure with Parameters Syntax

There are three modes for parameters, namely:

• IN
  The default form of parameter, where all changes in the stored procedure will not affect the parameter.

• OUT
  Mode that changes the parameter value, then sends it back to the caller.

• INOUT
  Combination mode of IN and OUT.

Example Syntax for Defining a Stored Procedure with Parameters

DELIMITER //

CREATE PROCEDURE procedure_name(
    [mode] [parameter] [data_type]
)
BEGIN
    sql query
END //

DELIMITER ;

Example Syntax for Calling a Stored Procedure with Parameters

CALL procedure_name([parameter_value]);

---

Cursor

A cursor is an object in the database used to hold the processing of specific SQL statements, or it can be said as a variable that holds query results containing more than one row or record.

A cursor can iterate as many times as the number of rows of data in the table. There are several characteristics of cursors, namely:

• Cannot perform updates (read only).
• Cursor only moves in one direction (non-scrollable).
• It is not recommended to update the table referenced by the cursor because it may produce unwanted results (asensitive).

---

Cursor Syntax

There are several steps in creating a cursor:

1. Declare

Step to declare a cursor, with syntax:

DECLARE cursor_name CURSOR FOR
SELECT_statement;

2. Open

Step to open or activate the cursor, with syntax:

OPEN cursor_name;

3. Fetch

Step to retrieve data from the cursor and then store it in a variable, with syntax:

FETCH cursor_name INTO variable_list;

4. Close

Step to deactivate the cursor, with syntax:

CLOSE cursor_name;

---

Differences Between Stored Procedure and Cursor

The difference between a stored procedure and a cursor is that a stored procedure only holds declaration commands from MySQL, while a cursor holds query results and also acts as a pointer to the query results.

This allows a cursor to perform complex commands that cannot be done by a stored procedure. One of them is iterating over records.