Definition: A stored procedure is a set of SQL commands that can be stored on the server. A stored procedure is a program that is physically saved in a database. Its implementation varies from one database management system to another. This program is written in a language specific to each DBMS and is compiled, so its execution speed is very fast.
Advantages: The DBMS is capable of working faster with data than any external program, since it has direct access to the data being manipulated and only needs to send the final result to the user. We only make one connection to the server, and it is then capable of performing all checks without having to re-establish a connection. We can reuse the procedure, and it can be called from different applications and languages. We only program it once.
Disadvantages: Stored procedures are saved in the DB, so if it becomes corrupted we will lose all stored procedures.
Usefulness of stored procedures: When multiple client applications are written in different languages or run on different platforms, but need to perform the same operation on the database. When security is very important. Banks, for example, use stored procedures for all common operations. This provides a secure and consistent environment, and procedures can ensure that every operation is properly logged. In such an environment, applications and users would not get any direct access to the database tables; they can only execute certain stored procedures.
Stored procedures: Stored procedures can improve performance since less information needs to be sent between the server and the client. The trade-off is that it increases the load on the database server, as most of the work is performed on the server side rather than the client.
What a Stored Procedure Contains: A name. It can have a list of parameters. It has content (also called the procedure definition). This content can consist of SQL statements, control structures, local variable declarations, error handling, etc.
Stored procedure syntax: Stored procedures and routines are created with CREATE PROCEDURE and CREATE FUNCTION commands. A routine is a procedure or a function. A procedure is invoked using a CALL command, and can only pass values using output variables. A function can be called from within a command like any other function (that is, by invoking the function name), and can return a scalar value. Stored routines can call other stored routines.
Structure:
CREATE PROCEDURE sp_name ([parameter[,...]]) [characteristic ...] routine_body
DROP {PROCEDURE | FUNCTION} [IF EXISTS] sp_name
Parameters:
- IN: passes a value into a procedure. The procedure might modify the value, but the modification is not visible to the caller.
- OUT: Its initial value is NULL in the procedure, and its value is visible to the caller.
- INOUT: is initialized by the caller, can be modified by the procedure, and any changes made by the procedure are visible to the caller when the procedure returns.
Example
delimiter //
CREATE PROCEDURE simpleproc (OUT param1 INT)
BEGIN
SELECT COUNT(*) INTO param1 FROM t;
END//
SHOW CREATE {PROCEDURE | FUNCTION} sp_name
The 'SHOW CREATE PROCEDURE or FUNCTION' statement returns the exact string that can be used to recreate the named routine.
SHOW {PROCEDURE | FUNCTION} STATUS [LIKE 'pattern']
Returns routine characteristics, such as the database name, name, type, creator, and creation and modification dates. If a pattern is not specified, it lists information for all stored procedures, depending on the command used.
The CALL command invokes a procedure previously defined with CREATE PROCEDURE.
CALL can pass values to the caller using parameters declared as OUT or INOUT.
The BEGIN ... END statement is used to delimit compound statements that appear in procedures. Each statement within the BEGIN ... END must end with the delimiter ';'. There can be a BEGIN ... END statement with no sub-statements.
FUNCTIONS
What is
CREATE PROCEDURE and CREATE FUNCTION?
These commands create a stored routine. Since MySQL 5.0.3, to create a routine, it is necessary to have the CREATE ROUTINE privilege, and the ALTER ROUTINE and EXECUTE privileges are automatically assigned to its creator.
The CREATE FUNCTION command is used in earlier versions of MySQL to support UDFs (User Defined Functions).
ALTER PROCEDURE AND ALTER FUNCTION
This command can be used to change the characteristics of a stored procedure or function.
DROP PROCEDURE AND DROP FUNCTION
This command is used to drop a stored procedure or function. That is, the specified routine is removed from the server. You must have the ALTER ROUTINE privilege for routines up to MySQL 5.0.3. This privilege is automatically granted to the routine's creator.
The IF EXISTS clause is a MySQL extension. It prevents an error from occurring if the function or procedure does not exist. A warning is generated that can be viewed with SHOW WARNINGS.
SHOW CREATE PROCEDURE AND SHOW CREATE FUNCTION
This command is a MySQL extension. Similar to SHOW CREATE TABLE, it returns the exact string that can be used to recreate the named routine.
SHOW PROCEDURE STATUS AND SHOW FUNCTION STATUS
This command is a MySQL extension. It returns routine characteristics, such as database name, name, type, creator, and creation and modification dates. If a pattern is not specified, it lists information for all stored procedures, depending on the command used.
THE CALL STATEMENT
The CALL command invokes a procedure previously defined with CREATE PROCEDURE.
CALL can pass values to the caller using parameters declared as OUT or INOUT.
It also "returns" the number of affected records, which in a client program can be obtained at the SQL level by calling the ROW_COUNT() function and from C by calling the C API function mysql_affected_rows().
BEGIN...END
Begin SQL is a keyword that allows indicating in the method editor the start of a sequence of SQL commands to be interpreted by the process's current data source.
A sequence of SQL commands starts with Begin SQL and must end with the keyword End SQL.
These keywords work as follows:
• You can place one or more Begin SQL/End SQL tag blocks in the same method.
• You can write several SQL statements on the same line or on different lines separated by a semicolon ";".
TO CREATE A FUNCTION
CREATE FUNCTION sp_name ([parameter[,...]])
RETURNS type
[characteristic ...] routine_body
parameter:
[ IN | OUT | INOUT ] param_name type
type:
Any valid MySQL data type
features:
LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'
routine_body:
valid stored procedures or SQL commands
ALTER FUNCTION
function_name [characteristic ...]
characteristic:
{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'
DROP FUNCTION
DROP {PROCEDURE | FUNCTION} [IF EXISTS] sp_name
SHOW CREATE FUNCTION
SHOW CREATE {PROCEDURE | FUNCTION} sp_name
[start_label:] BEGIN
[statement_list]
END [end_label]
HOW IT WORKS
These commands allow storing functions on the MySQL server:
CREATE FUNCTION
This command adds a user-defined function, usually associated with the database the user is working in.
ALTER FUNCTION
The "ALTER" command is used to change the characteristics of a stored function.
DROP FUNCTION
The "DROP FUNCTION" command is used to drop a stored function (it is deleted from the server). The user performing this action must have the "ALTER ROUTINE" privilege, which is automatically granted to the routine's creator (a routine is a term that encompasses procedures and functions).
The "IF EXISTS" clause is a MySQL extension that prevents an error from occurring if the function does not exist. A warning is generated instead, which can be viewed with "SHOW WARNINGS".
SHOW CREATE FUNCTION
This command is a MySQL extension, similar to "SHOW CREATE TABLE", which returns the exact string that can be used to recreate the named function.
SHOW FUNCTION STATUS
This command is a MySQL extension that returns function characteristics, such as the database name, the function name itself, the type, the creator, and the creation and modification dates. If a pattern is not specified, it lists information for all functions, depending on the command used.