Stored Procedure
A stored procedure is a collection of SQL commands, or can be described as a subprogram with a specific name stored in the database. The process performed by a stored procedure involves receiving input parameters, returning values in the form of output parameters to the caller, and performing data manipulation operations on the database, executed by a program such as a trigger or another stored procedure. The use of stored procedures has several advantages:
- Improves application performance, because stored procedures run faster than uncompiled SQL commands sent by the application.
- Reduces network traffic between the database and application, because there is no need to send uncompiled SQL statements.
- Can be used repeatedly and transparently across all applications.
However, stored procedures also have some disadvantages:
- Debugging is not possible.
- Increases the load on the server hardware.
- Writing them is not easy and requires specific knowledge.
Syntax in Stored Procedure
The syntax found in stored procedures:
- Syntax for writing a stored procedure
DELIMITER // CREATE PROCEDURE procedure_name() BEGIN sql query END // DELIMITER ;
- Syntax for calling a stored procedure
CALL procedure_name()
- Syntax for declaring a variable in a stored procedure
DECLARE variable_name datatype(length) DEFAULT value;
- Syntax for stored procedure with parameters
There are three modes for parameters:
- IN, which is the default mode where any changes inside the stored procedure do not affect the parameter.
- OUT, a mode that changes the parameter value and sends it back to the caller.
- INOUT, a mode that combines 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:
text
CALL procedure_name([parameter_value])
Cursor
A cursor is an object in a database used to handle the processing of specific SQL statements, or can be described as a variable that holds the result of a query containing more than one row or record. A cursor can iterate over the number of rows in a table. There are several characteristics of a cursor:
- Cannot be updated (read only).
- Cursor moves only in one direction (non-scrollable).
- Updating the table referenced by the cursor is not recommended because it may produce unwanted results (asensitive).
Syntax in Stored Procedure
The syntax found in cursors:
There are several steps in creating a cursor:
- Declare, the step to declare the cursor, with the syntax:
DECLARE cursor_name CURSOR FOR SELECT_statement;
- Open, the step to open or activate the cursor, with the syntax:
OPEN cursor_name;
- Fetch, the step to retrieve data from the cursor and then store it in a variable, with the syntax:
FETCH cursor_name INTO list_of_variables
- Close, the step to deactivate the cursor, with the 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, whereas a cursor holds the query result and also acts as a pointer to that result. This allows the cursor to perform complex operations that cannot be done by a stored procedure. One such operation is iterating over records.
