MySQL stored procedures and functions

Lin Bingwen Evankaka Original works. Reprint please specify the source Http://blog.csdn.net/evankaka

Summary: 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.

First, stored procedure 1.1, basic syntax

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 separated by commas. 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), c8/>)    Engine=innodb DEFAULT Charset=utf8;

And then this is some data to insert:

1.3 In, out, inout parameters

(1), stored procedure with in

Create a stored procedure. cmd run create  PROCEDURE  sp_search (in P_name CHAR) beginif p_name is null or p_name= ' Thenselect * FR OM T_user; Elseselect * from T_user WHERE user_name like p_name; END IF; END

Because the chapter 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), stored procedure with out

Create  PROCEDURE  sp_search2 (in P_name CHAR) with out returned, out P_int int) beginif p_name is null or p_name= ' Thenselect * from T_user; Elseselect * 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 start of the forest

Called and outputs the result call SP_SEARCH2 (' Forest% ', @p_num); SELECT @p_num;

(3), stored procedure with inout

Stored procedure with inout create PROCEDURE sp_inout (inout p_num INT) Beginset p_num=p_num*10; end//called and outputs the result set @p_num =2;call sp_inout (@p_num); SELECT @p_num;

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.

DECLARE var_name[,...] Type [default value]var_name: Specifies the name of the local variable type: The data type used to declare the local variable the default clause: Used to specify a value for the local variable. If not specified, the default is null.

Such as:

Declare CID Int (10);

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=exprset cid=910;

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

Select col_name[,...] into var_name[,...] Table_exprcol_name: Used to specify the column name Var_name: Used to specify the variable name to assign to table_expr: Represents the FROM clause in the SELECT statement and the following syntax section

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.

DECLARE Handler_type handler for condition_value[,...] sp_statementhandler_type:continue | EXIT | undocondition_value:sqlwarning | Not Found | SqlException

5. Process Control Statements
(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 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)    )    Engine=innodb DEFAULT charset= UTF8;

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)    )    Engine=innodb DEFAULT Charset=utf8;

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

Stored procedure with multiple if create  PROCEDURE sp_scholarship_level (in P_level char (1)) beginif p_level = ' A '  thenselect * from T_ Grade WHERE Stu_score >=90; ELSEIF  p_level = ' B '  thenselect * from T_grade WHERE stu_score <90 and  stu_score>=80; ELSEIF  p_level = ' C '  thenselect * from T_grade WHERE stu_score <80 and  stu_score>=70; ELSEIF  p_level = ' D '  thenselect * from T_grade WHERE stu_score <60; Elseselect * from T_grade; END IF; END

Call procedure:

Call Sp_scholarship_level (' A ') called and outputs the result;

Case Statement
Expression Form 1

Case Case_valuewhen When_value then Statement_list[when when_value then Statement_list] ... [Else Statement_list] End case

Expression Form 2

Casewhen Search_condition then Statement_listend case

Usage examples

CREATE  PROCEDURE sp_scholarship_level3 (in P_level char (1)) begindeclare p_num int DEFAULT  0; Case P_levelwhen ' A '  thenset p_num=90; When ' B '  thenset p_num=80; When ' C '  thenset p_num=70; When ' D '  thenset p_num=60; Elseset 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 Sp_scholarship_level3 (' d ') called and outputs the result;

(2) Loop statement
While statements, repeat statements, and loop statements.
While statement

[Begin_label:]while search_condition Dostatement_listend 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; END while; end//calls and outputs the result call Sp_cal (5, @result); SELECT @result;

Output: Calculate 5!

Repeat statement syntax format

[Begin_label:]repeatstatement_listuntil Search_conditionend 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  END REPEAT; end//calls and outputs the result call Sp_cal2 (5, @result); SELECT @result;

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

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

Drop procedure [if exists] sp_name;

Second, function 2.1, definition

MySQL , the basic form of creating a storage function is as follows:

CREATE FUNCTION sp_name ([func_parameter[,...]) RETURNS type[characteristic ...] Routine_bodyreturn

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

Select sp_name ([func_parameter ...]) Select Fn_search (2);

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)

/** functions use **/create function Sp_cal_max (p_num1 int,p_num2 INT) RETURNS intbeginif p_num1 >= p_num2 thenreturn p_num1; Elsereturn p_num2; END IF; END

Call:

SET @p_num1 = 2; SET @p_num2 = 34; SELECT Sp_cal_max (@p_num1, @p_num2);

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 definition

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 declaring Cursors

Declare cursor_name cursor forselect_statement; cursor_name: Cursor name select_statement:select The contents of the statement declare cur_employee cursor forselect name,age from employee;

2 Open Cursor

Open Cursor_nameopen Cur_employee;

3 using the cursor

Mysql used in Fetch keyword to use the cursor, syntax form

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

4 Close Cursor

Close cursor_name; Close Cur_employee;

each cursor should be closed when it is no longer needed, using 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-by-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;//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  close mycur; END

Run:

Call Get_cur ()

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

Copyright NOTICE: This article for Bo Master Lin Bingwen Evankaka original article, without Bo Master permission not reproduced.

MySQL stored procedures and functions