Chapter 11th stored Procedures and functions

Chapter 11th stored Procedures and functions



Section I: Introduction to Stored procedures and functions

Stored procedures and functions are a collection of SQL statements that are defined in the database, and then call these stored procedures and functions directly to perform the defined
of SQL statements. Stored procedures and functions can prevent developers from writing the same SQL statements repeatedly. Also, stored procedures and functions are

Mysql
The server is stored and executed, which can reduce the data transfer between client and server;



Section II: Creating Stored Procedures and functions

2.1 Creating a Stored procedure


CREATE PROCEDURE sp_name ([proc_parameter[,...]])
[Characteristic ...] Routine_body

The Sp_name parameter is the name of the stored procedure;
Proc_parameter represents a list of parameters for a stored procedure;
The characteristic parameter specifies the characteristics of the stored procedure;
The Routine_body parameter is the contents of the SQL code and can be used with BEGIN ... End to flag the start and end of the SQL code.
Each parameter in the Proc_parameter is made up of 3 parts. These 3 sections are input and output types, parameter names, and parameter types, respectively.
[In | Out | INOUT] Param_name Type
Where the in represents the input parameter; out represents the output parameter; The INOUT represents either an input or an output; the Param_name parameter is
The parameter name of the stored procedure; The type parameter specifies the parameter type of the stored procedure, which can be any data type of the MySQL database;
The characteristic parameter has multiple values. The values are described as follows:
LANGUAGE sql: Description The routine_body part is made up of SQL language statements, which are the default languages of the database system.
[NOT] Deterministic: Indicates whether the execution result of the stored procedure is deterministic. Deterministic indicates that the result is deterministic. Every
The same input will get the same output when executing the stored procedure.

Not deterministic indicates that the result is indeterminate, and the same input may get different output. By default, the result is non-deterministic.
{CONTAINS SQL | NO SQL | READS SQL DATA | Modifies SQL DATA}: Indicates the limitations of the use of SQL statements by sub-programs;
CONTAINS SQL indicates that a subroutine contains SQL statements, but does not contain statements that read or write data;

No SQL indicates that the subroutine does not contain SQL statements;

READS SQL data represents the statement in the subroutine that contains the read data;

Modifies SQL data represents a statement in a subroutine that contains write data. By default, the system is specified as CONTAINS SQL;
SQL SECURITY {definer | INVOKER}; Indicates who has permission to execute.

Definer that only the definition can be executed by himself;
INVOKER indicates that the caller can execute. By default, the system-specified permissions are definer.
COMMENT ' string ': Comment information;


2.2 Creating a storage function


CREATE FUNCTION sp_name ([func_parameter[,...])
RETURNS type
[Characteristic ...] Routine_body

The Sp_name parameter is the name of the stored function;

Func_parameter represents a list of parameters for a stored function; RETURNS type specifies the kind of return value;

The characteristic parameter specifies the property of the stored procedure, and the value of the parameter is the same as the value in the stored procedure;

The Routine_body parameter is the contents of the SQL code and can be used with BEGIN ... End to mark the beginning and end of the SQL code;
Func_parameter can consist of multiple parameters, each of which consists of a parameter name and a parameter type, in the form of the following:
Param_name type where the Param_name parameter is the parameter name of the stored function;

The type parameter specifies the parameter type of the stored function, which can be any data type of the MySQL database;


2.3 Use of variables
1, defining variables

DECLARE var_name [,...] Type [DEFAULT value]




2, assigning a value to a variable

SET var_name = expr [, var_name=expr] ...

SELECT col_name[,...] Into var_name[,...]
From table_name WHERE condition



2.4 Use of cursors


Query statements may query multiple records, using cursors in stored procedures and functions to read the records in the query result set one by one. The cursor
Use include declaring cursors, opening cursors, using cursors, and closing cursors. The cursor must be declared before the handler and declared in the variables and bars
After the item.



1, declaring cursors

DECLARE cursor_name cursor for select_statement;


2, open cursor


OPEN cursor_name;


3, using Cursors


FETCH cursor_name into Var_name [, var_name ...] ；


4, close the cursor

CLOSE cursor_name;




2.5 Use of Process Control


Process controls can be used in stored procedures and functions to control the execution of statements. In MySQL, you can use the IF statement, Case statement, LOOP
Statements, LEAVE statements, iterate statements, REPEAT statements, and while statements for process control.


1,if statements


IF Search_condition then Statement_list
[ELSEIF search_condition then statement_list] ...
[ELSE Statement_list]
END IF



2,case statements


Case Case_value
When When_value then Statement_list
[When When_value then Statement_list] ...
[ELSE Statement_list]
END case



3,loop,leave statements

The Loop statement enables certain statements to be executed repeatedly, implementing a simple loop. But the loop statement itself does not stop looping
Statement, you must encounter a LEAVE statement to stop the loop. The basic form of the LOOP statement syntax is as follows:
[Begin_label:] LOOP
Statement_list
END LOOP [End_label]
The LEAVE statement is primarily used to jump out of loop control. The syntax form is as follows:
LEAVE Label



4,iterate statements


The iterate statement is also a statement used to jump out of a loop. However, the iterate statement jumps out of the loop and then goes directly to the next
Cycle. Basic syntax:
Iterate label;



5,repeat statements


The REPEAT statement is a conditional-controlled loop statement. When certain conditions are met, the loop statement is jumped out. The basic of the REPEAT statement
The syntax form is as follows:
[Begin_label:] REPEAT
Statement_list
UNTIL search_condition
END REPEAT [End_label]



6,while statements


[Begin_label:] While search_condition do
Statement_list
END while [End_label]



section III: Calling stored procedures and functions

3.1 Calling a stored procedure

Call Sp_name ([parameter[,...])




3.2 Calling a storage function


Fun_name ([parameter[,...])



section Fourth: Viewing stored procedures and functions


4.1 Show status statement to view the state of stored procedures and functions



SHOW {PROCEDURE | FUNCTION} STATUS [like ' pattern '];


4.2 Show CREATE Statement view the definition of a stored procedure's function


SHOW CREATE {PROCEDURE | FUNCTION} sp_name;


4.3 from information_schema. View information about stored procedures and functions in the Routines table



section Fifth: Modifying stored procedures and functions


ALTER {PROCEDURE | FUNCTION} Sp_name [characteristic ...]
Characteristic:
{CONTAINS SQL} NO SQL | READS SQL DATA | Modifies SQL DATA}
| SQL SECURITY {definer | INVOKER}
| COMMENT ' String '

Where the Sp_name parameter represents the name of the stored procedure or function, and the characteristic parameter specifies the function's attributes.

CONTAINS SQL indicates that a subroutine contains SQL statements, but does not contain statements that read or write data;

No SQL indicates that the subroutine does not contain SQL statements;

READS SQL data represents a statement in a subroutine that contains data;

Modifies SQL data represents a statement in a subroutine that contains write data. Sql
security{Definer | Invoder} indicates who has permission to execute.

Definer that only the definition can be executed by himself;
Invoder indicates that the caller can execute.

COMMENT ' String ' is a comment message.



Section Sixth: Deleting stored procedures and functions

DROP {PROCEDURE | FUNCTION} sp_name;

Chapter 11th stored Procedures and functions