Mysql stored procedures and functions detailed _mysql

Stored procedures and functions define a collection of SQL statements in a database, and then call those stored procedures and functions directly to execute a defined SQL statement. Stored procedures and functions can prevent developers from writing the same SQL statements repeatedly. Furthermore, stored procedures and functions are stored and executed on the MySQL server to reduce data transfer at the client and server side.
First, stored procedures
1.1. Basic grammar

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

[Characteristic ...] Routine_body

Sp_name: The name of the stored procedure, which is created by default in the current database. This name should try to avoid the same name as MySQL's built-in function

Proc_parameter: Parameter list for stored procedures
format [in| Out| Inout]param_name type
Param_name is the name of the parameter and type is the data type of the parameter. Multiple parameters are separated by commas. Input parameters, output parameters and input/output parameters, respectively, with in/out/inout identification. The name of the parameter is not the same as the column names of the data table.
Characteristic: Some feature settings for stored procedures, introduced separately
1 COMMENT ' string ': A description of the stored procedure, where string is the description and COMMENT is the keyword.
2 LANGUAGE sql: Indicates that the language in which this stored procedure is written is the SQL language. This option may not be specified.
3 Deterministic: Indicates that the stored procedure produces the same result for the same input parameter; Not deterministic indicates an indeterminate result (default).
4 contains SQL | No SQL | Reads SQL Data | Modifies SQL data Contains SQL represents a statement in which a stored procedure contains read or write data (default)
No SQL means no SQL statements
Reads SQL data indicates that a stored procedure contains only statements that read data
Modifies SQL data indicates that a stored procedure contains only statements that write data
5 SQL Security: This feature is used to specify whether a stored procedure is executed using the permission of the user who created the stored procedure (definer), or with the permission of the caller (invoker). Default is Definer
Routine_body: The body part of a stored procedure that contains the SQL statements that must be executed when the procedure is invoked. Begins with begin and ends with end. If there is only one SQL statement in the stored procedure body, you can omit the Begin-end flag.
1.2. Data preparation

CREATE TABLE 
 t_user 
 ( 
  user_id INT not NULL auto_increment, 
  user_name CHAR (=) NOT null, 
  User_ PASSWORD Char (a) not NULL, 
  user_email char (= NOT null, 
  PRIMARY KEY (user_id), 
  INDEX idx_name (user_name) c10/>) 
 Engine=innodb DEFAULT Charset=utf8; 

Then this is some of the data inserted:

1.3 In, out, inout parameters
(1), stored procedure with in

Create a stored procedure. cmd runs the Create 
PROCEDURE sp_search (in P_name CHAR ()) 
BEGIN 
IF p_name is null or p_name= ' then< C19/>select * from T_user; 
ELSE 
SELECT * from T_user WHERE user_name like p_name; 
End IF; 
End 

Because the branch conflict, so to add delimiter//. Set//To end run symbol

As follows:

Call:

Invoke and output the result call 
sp_search (' Lin Bingwen ') 

Results

(2), with out stored procedures

 
CREATE PROCEDURE Sp_search2 with out return (in P_name CHAR (m), out P_int int) 
BEGIN 
IF p_name is null or p_name= ' T HEN 
SELECT * from T_user; 
ELSE 
SELECT * from T_user WHERE user_name like p_name; 
End IF; 
SELECT found_rows () into P_int; 
End 

Call Output: Count the number of people with the beginning of the forest

Invoke and output the result call 
sp_search2 (' Forest% ', @p_num); 

(3), with inout stored procedures

Stored procedures with inout 
CREATE PROCEDURE sp_inout (inout p_num INT) 
BEGIN 
SET p_num=p_num*10; 
End//Call and output the result 
SET @p_num =2; 
Call Sp_inout (@p_num); 
SELECT @p_num; 

Output results:

1.4. Stored Procedure Body
In the stored procedure body, a combination of various SQL statements and procedural statements can be used to encapsulate the complex business logic and processing rules in database application, so as to realize the flexible programming of database application. Here are a few common syntax elements that are used to construct a stored procedure body.
1. Local Variables
Local variables can be declared in the stored procedure body to store temporary results in the stored procedure body.

DECLARE var_name[,...] Type [DEFAULT value] 
var_name: Specifies the name type of a local variable 
: The data type used to declare a local variable 

Such as:

Instructions for use:
Local variables can only be declared in the Begin...end statement block of the stored procedure body.
Local variables must be declared at the beginning of the stored procedure body.
The scope of a local variable is limited to declaring its begin. End Statement block, which is not available in statements in other statement blocks.
Local variables differ from user variables: When a local variable is declared, the @ symbol is not used before it, and it can only begin at the beginning. End Statement block, and the user variable, when declared, uses the @ symbol before its name, while the declared user variable exists throughout the session.
2, Set statement
Assigning a local variable using the SET statement

Set var_name=expr
Set cid=910;

3. The select ... into statement
Store the value of the selected column directly into a local variable, syntax format

Select col_name[,...] into var_name[,...] table_expr 
col_name: Used to specify column name 
var_name: Specifies the variable name to assign to 
table_expr: Represents the FROM clause in a SELECT statement and the following grammatical part 

Description: The Select...into statement in the stored procedure body Returns a result set that can only have one row of data.
4. Define the processing procedure
is to define the problems that may be encountered during program execution. And you can define solutions to these problems in your handlers. This method can predict the possible problems in advance and propose solutions.

DECLARE Handler_type handler for condition_value[,...] sp_statement 
handler_type:continue | EXIT | UNDO 

5. Process Control Statement
(1) Conditional judgment statement
If statement

If search_condition then statement_list 
[ElseIf search_condition then statement_list] ... 
[Else Statement_list] 
End If 

Search_condition parameter: Conditional judgment statement
Statement_list parameters: Executing statements with different conditions
Instances of stored procedures with multiple if
Data preparation

Student table:

CREATE TABLE 
 t_student 
 ( 
  stu_id int not null, 
  Stu_name CHAR (a) not NULL, 
  Stu_class int not null,
   stu_sex CHAR (2) NOT NULL, 
  stu_age INT is not null, 
  PRIMARY KEY (stu_id) 
 ) 
 Engine=innodb DEFAULT charset= UTF8; 

The data are as follows:

Score Sheet (stu_id is the student table is a foreign key relationship):

CREATE TABLE 
 t_grade 
 ( 
  stu_id int not NULL, 
  Stu_score int not null, 
  FOREIGN KEY (stu_id) REFERENCES t_student (stu_id), 
  INDEX stu_id (stu_id) 
 ) 
 

Then write a stored procedure: the person who returns each fraction level

Stored procedure with multiple IF 
CREATE PROCEDURE sp_scholarship_level (in P_level char (1)) 
BEGIN 
IF p_level = ' A ' THEN 
SELECT * from T_grade WHERE stu_score >=90; 
ELSEIF p_level = ' B ' THEN 
SELECT * from T_grade WHERE stu_score <90 and stu_score>=80; 
ELSEIF p_level = ' C ' THEN 
SELECT * from T_grade WHERE stu_score <80 and stu_score>=70; 
ELSEIF p_level = ' D ' THEN 
SELECT * from T_grade WHERE stu_score <60; 
ELSE 
SELECT * from T_grade; 
End IF; 

Call Procedure:

Call and output the result 

Case statement
expression Form 1

Case Case_value when 
When_value then statement_list 
[when When_value then Statement_list] ... 
[Else Statement_list] 
End case

Expression Form 2

Case when 
search_condition then statement_list 

Usage examples

CREATE PROCEDURE sp_scholarship_level3 (in P_level char (1)) 
BEGIN 
DECLARE p_num int DEFAULT 0; 
Case P_level when 
' A ' THEN 
SET p_num=90; 
When ' B ' THEN 
SET p_num=80; 
When ' C ' THEN 
SET p_num=70; 
When ' D ' THEN 
SET p_num=60; 
ELSE 
SET p_num=0; 
End case; 
SELECT * from T_grade G, t_student s WHERE g.stu_id=s.stu_id and G.stu_score >= p_num; 
End 

Call:

Call and output the result 

(2) Circular statement
while statements, repeat statements, and loop statements.
While statement

[Begin_label:] 
While Search_condition does 
statement_list end while 
[End_label]  

Judge whether the condition search_condition is true, if true, then execute the statement in Statement_list, and then make a judgment, if still true then continue to circulate until the condition is judged not true when the cycle ends.
usage Examples

The stored procedure with while is 
CREATE PROCEDURE sp_cal (in P_num int,out p_result INT) 
BEGIN 
 SET p_result=1; 
 While P_num > 1 do 
 SET p_result = p_num * P_result; 
 SET p_num = p_num-1; 
 End While; 
End//calling and output result call 
sp_cal (5, @result); 
SELECT @result; 

Output result: Calculate 5!

Repeat statement syntax format

[Begin_label:] 
Repeat 
statement_list 
Until search_condition end 
repeat 
[End_label]   

The repeat statement first executes the statement in Statement_list and then determines whether the condition search_condition true, and if true, ends the loop, if not true, to continue the loop.
Repeat the first execution, while the first judge after the execution.
Use Example:

Stored procedures with repeat 
CREATE PROCEDURE sp_cal2 (in P_num int,out p_result INT) 
BEGIN 
 SET p_result=1; 
 REPEAT 
  SET p_result = p_num * P_result; 
  SET p_num = p_num-1; 
  UNTIL p_num<=1 end 
 REPEAT; 
End//calling and output result call 
Sp_cal2 (5, @result); 
SELECT @result; 

1.5. Calling stored procedures

Call Sp_name ([parameter[,...]]); 
Sp_name the name of the called stored procedure 
Parameter: Specifies the parameter to use to invoke the stored procedure.

1.6. Modify the stored procedure

Copy Code code as follows:

Alter procedure proc_name[characteristic ...]

You can only modify the characteristics of a stored procedure, and if you want to modify the contents of a stored procedure, delete the stored procedure before you recreate the
1.7. Delete stored procedure

Copy Code code as follows:

Drop procedure [if exists] sp_name;

Second, function
2.1. Definition

In MySQL, the basic form of creating a stored function is as follows: Create
function Sp_name ([func_parameter[,...]]) 
RETURNS type 
[characteristic ...] routine_body return 
 

Clause is used to declare the data type of a stored function return value. A stored procedure is a collection of user-defined sets of SQL statements that involve tasks for a particular table or other object, and a user can invoke a stored procedure, which is usually a method defined by the database, receives parameters and returns a value of some type and does not involve a particular user table.
Calling a stored function

Select sp_name ([func_parameter ...]) 

Remove a storage function drop
Modify the stored function alter to modify some of the related characteristics of the stored function.

2.2. Examples of function usage
(compare size, return a large number)

The/** function uses the **/ 
CREATE function Sp_cal_max (p_num1 int,p_num2 int) 
RETURNS int 
BEGIN 
IF p_num1 >= p_num2 THEN return 
p_num1; 
ELSE return 
p_num2; 
End IF; 
End 

Call:

SET @p_num1 =2; 
SET @p_num2 = 34; 

2.3, stored procedures and function differences
1 in general, the functions of the stored procedures to achieve a bit more complex, and the function of the implementation of a relatively strong target. Stored procedures, powerful, can perform a series of database operations including modifying tables, and user-defined functions cannot be used to perform a set of actions that modify the state of the global database.

2 for stored procedures, you can return parameters, such as recordsets, and functions can only return values or table objects. A function can return only one variable, and a stored procedure may return multiple. The parameters of a stored procedure can have in,out,inout three types, and a function can only have an in class ~ ~ The stored procedure declaration does not need a return type, and the function declaration needs to describe the return type, and the function body must contain a valid returns statement.

3 stored procedures, you can use an indeterminate function and do not allow built-in indeterminate functions in the body of a user-defined function.

4 The stored procedure is typically executed as a separate part (execute execution), and a function can be invoked as part of a query statement (select invocation), because the function can return a Table object, so it can be located after the FROM keyword in the query statement. Stored procedures are not available in SQL statements, but functions can be used.
third, cursor (cursor)
3.1 Definition
the query statement may query multiple records, and use cursor labels in stored procedures and functions to read the records in the query result set one after the other. The use of the cursor includes declaring the cursor, opening the cursor, using the cursor, and closing the cursor. The cursor must declare the cursor, open the cursor, use the cursor, and close the cursor. The cursor must be declared before the handler, and declared after the variables and conditions.
1 Declaration cursor

Declare cursor_name cursor forselect_statement; 
Cursor_name: 
The contents of the cursor name Select_statement:select statement 

2 Open cursor

Open cursor_name 

3 using the cursor
Use the FETCH keyword in MySQL to use the cursor, syntax form

Fetch cur_name intovar_name[,var_name ...]; 
Cur_name represents the name of the cursor 
var_name indicates that the information that is queried by the SELECT statement in the cursor is stored in the parameter. Var_name must be well defined before the cursor is declared. 
Fetch Cur_employee intoemp_name,emp_age; 

4 Close the cursor

Close cursor_name; 

Each cursor should be closed when it is no longer needed, and using the close statement will release all resources used by the cursor. After a cursor is closed, it cannot be used if it is not reopened. For a declared cursor, you do not need to declare it again, and you can open it directly using the Open statement.
3.2, use the example
(copy table test_cur1 data to TEST_CUR2)

CREATE TABLE ' test_cur1 ' ( 
 ' id ' int ') NOT NULL auto_increment, 
 ' type ' char (one) default null, 
 ' Order1 ' char ( One) default NULL, 
 PRIMARY KEY (' id ') 
INSERT into ' Test_cur1 ' VALUES (1, ' 145 ', ' D1 '); 
INSERT into ' Test_cur1 ' VALUES (2, ' 134 ', ' 1d '); 
INSERT into ' Test_cur1 ' VALUES (3, ' 123 ', ' 1AD '); 
INSERT into ' Test_cur1 ' VALUES (4, ' 121 ', ' 1as '); 
 
CREATE TABLE ' test_cur2 ' ( 
 ' id ' int () NOT NULL auto_increment, 
 ' type ' char (one) default null, 
 ' Order1 ' ch AR (one) default NULL, 
 PRIMARY KEY (' id ') 
) 

Then write the cursor:

CREATE PROCEDURE Get_cur () 
BEGIN 
 DECLARE done INT DEFAULT 0; 
 DECLARE ID Int (one); 
 DECLARE type char (one); 
 DECLARE Order1 char (one); 
 DECLARE mycur CURSOR for SELECT * from test_cur1;//define cursor DECLARE CONTINUE HANDLER for 
 SQLSTATE ' 02000 ' SET done = 1;
   //opens the cursor open 
 mycur; 
 Start loop 
 REPEAT 
 FETCH mycur into id,type,order1;//take out the contents of the cursor to the temporary variable IF not do 
 THEN 
  INSERT into Test_ CUR2 VALUES (id,type,order1);//insert to another table end 
 IF; 
 UNTIL do end repeat;//when done=1 ends loop 
 //closes cursor close 
 mycur; 
End 

Run:

Take a look at the data from two tables: this is table 2

This is table 1

Indicates that the data has been successfully replicated.

The above is the entire content of this article, I hope to learn from the MySQL stored procedures and functions to help