Getting started with MySQL stored procedures

This article mainly introduces the mysql stored procedures and functions in detail, the content is very detailed, the need for friends can refer to the following

Stored procedures and functions are collections of SQL statements that are defined in the database, and then call these stored procedures and functions directly to execute the SQL statements that have already been defined. Stored procedures and functions can prevent developers from writing the same SQL statements repeatedly. Furthermore, stored procedures and functions are stored and executed in the MySQL server, which can reduce the data transfer between the client and server side.
One, 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 parameter name, and type is the data type of the argument. Multiple parameters are used between each otherComma Delimited. Input parameters, output parameters, and input/output parameters, respectively, are identified by In/out/inout. The parameter should not be named the same as the column name of the data table.
Characteristic: Some feature settings for stored procedures, respectively
1 COMMENT ' string ': used to describe 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, it indicates an indeterminate result (default).
4 contains SQL | No SQL | Reads SQL Data | Modifies SQL data Contains SQL represents the statement that the stored procedure contains read or write data (default)
No SQL representation does not contain SQL statements
Reads SQL data means that the stored procedure contains only the statements that read the data
Modifies SQL data means that the stored procedure contains only statements that write data
5 SQL Security: This feature is used to specify whether the stored procedure executes with 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 the stored procedure that contains the SQL statement that must be executed at the time of the procedure call. Begin with begin and end 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 (TEN) NOT NULL,   User_email char (+) NOT NULL,   PRIMARY KEY (user_id),   INDEX idx_name (user_name), c9/>)  

And then this is some data to insert:

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

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

As follows:

Call:

Results

(2), stored procedure with out

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

(3), stored procedure with inout

Output Result:

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

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 the begin that declares it. End Statement block, the statement in the other statement block can not use it.
A local variable differs from a user variable in that it differs from a local variable declaration by not using the @ symbol in front of it, and it can only be at begin: End Statement block, while a user variable is declared with an @ sign preceded by its name, and the declared user variable exists throughout the session.
2. Set statement
Assigning values to local variables using SET statements

Set var_name=expr
Set cid=910;

3. Select ... into statement
Stores the value of the selected column directly in a local variable, with syntax formatting

Description: The result set returned by the Select...into statement in the stored procedure body can have only one row of data.
4, define the processing program
is to define in advance the problems that you may encounter during program execution. You can also define ways to resolve these problems in your handlers. This way you can anticipate potential problems in advance and propose solutions.

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

Search_condition parameter: Conditional judgment statement
Statement_list parameter: Execution statement with different conditions
Stored procedure instances with multiple if
Data preparation

Student table:

CREATE TABLE  t_student  (   stu_id int not null,   Stu_name CHAR (TEN) not NULL,   Stu_class int. not Null,
   stu_sex CHAR (2) NOT NULL,   stu_age INT not NULL,   PRIMARY KEY (stu_id)  )  

The data are as follows:

Score table (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 score level

Call Procedure:

Case statement
Expression Form 1

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

Expression Form 2

Usage examples

Call:

(2) Loop 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 the Statement_list, then make a judgment, if still true, continue the loop until the condition is not true when the loop ends.
Usage examples

Stored procedure with while the 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;  

Output: 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 the Statement_list, then determines whether the condition search_condition is true, and if true, ends the loop and, if not true, resumes the loop.
Repeat first after the judgment, while the first judgment after execution.
Examples of Use:

Stored procedure 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  

1.5. Call the stored procedure

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

1.6. Modifying stored procedures

Copy CodeThe code is 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 recreating
1.7. Delete stored Procedures

Copy CodeThe code is as follows: Drop procedure [if exists] sp_name;

Second, function
2.1. Definition

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

Delete Storage function drop
Modify the storage function alter to modify some related characteristics of the stored function.

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

Call:

2.3. Differences between stored procedures and functions
1) In general, the function of the stored procedure implementation is a little more complicated, and the function's realization function is relatively strong. Stored procedures, powerful, can perform a series of database operations, including modifying tables; user-defined functions cannot be used to perform a set of actions that modify the state of a global database.

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

3) Stored procedures, you can use the non-deterministic function, not allowed in the user-defined function body of the built-in non-deterministic function.

4) The stored procedure is typically executed as a separate part (EXECUTE statement execution), and the function can be invoked as part of a query statement (select Call), since the function can return a Table object, so it can be located in the query statement after the FROM keyword. Stored procedures are not available in SQL statements, and functions can be used.
Three, cursor (cursor)
3.1 Definitions
Query statements may query multiple records, using cursor markers in stored procedures and functions to read the records in the query result set one by one. The use of cursors 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 variable and the condition.
1 Declaration cursor

2 Open cursor

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

4 Close cursor

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, you can open it directly using the Open statement.
3.2. Use example
(Copy table Test_cur1 data to TEST_CUR2)

CREATE TABLE ' test_cur1 ' (  ' id ' int (one) 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 (one) not null auto_increment,  ' type ' char (one) ' Default NULL,  ' Order1 ' char ( ) Default NULL,  

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;//definition 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 TEMP variable IF not do then   INSERT into TEST_CUR2 VALUES (id,type,order1);//INSERT into another table  END IF;  UNTIL done End repeat;//when done=1 ends loop  //close cursor  

Run:

Let's take a look at two tables of data: this is table 2

This is table 1.

Indicates that the data has been successfully copied past.

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

Getting started with MySQL stored procedures