MySQL stored procedures and functions

I. syntax for stored procedures and functions

CREATE PROCEDURE sp_name ([proc_parameter[,...]])    [Characteristic ...] Routine_body CREATE FUNCTION sp_name ([func_parameter[,...])    RETURNS type    [characteristic ...] routine_body        proc_parameter: [In    | Out | INOUT] Param_name type        func_parameter:    param_name Type type: Any    valid MySQL data type characteristic:    LANGUAGE SQL  | [NOT] Deterministic  | {CONTAINS SQL | NO SQL | READS SQL DATA | Modifies SQL DATA}  | SQL SECURITY {definer | INVOKER}  | COMMENT ' string ' routine_body:    Valid SQL PROCEDURE statement or statements

　　

LANGUAGE sql: Used to illustrate that the statement part is an SQL statement that may support other types of statements in the future

[NOT] Deterministic: If a program or thread always produces the same result for the same input parameter, it is considered "deterministic", otherwise it is "not deterministic". If neither a given deterministic nor a not deterministic is given, the default is not deterministic (non-deterministic) CONTAINS SQL: A statement that the subroutine does not contain read or write data.

CONTAINS sql: Contains the subroutine containing the SQL statement
No SQL: Indicates that the subroutine does not contain SQL statements.
READS SQL Data: A statement that represents a subroutine that contains read data, but does not contain a statement that writes data.
Modifies SQL data: Represents the statement that the subroutine contains write data.

SQL SECURITY: Permission-related
SQL SECURITY Definer: Indicates that the program executing the stored procedure is executed by the permissions of the user who created the stored procedure. (who created the stored procedure and executed it as the user's permission) default
SQL SECURITY INVOKER: Indicates that the program executing the stored procedure is executed by the permission of the user who called the stored procedure. (see if the current caller has permission to execute the table involved in the stored procedure, if not, then an error)

COMMENT ' string ': Comment, same as the field comment that created the table.

Two. Differences between stored procedures and functions

1. The implementation of the stored procedure is more complicated, the function function is relatively single
2. The stored procedure has the In,out,inout parameter, does not support the default value setting, the function can only have the input parameter, and cannot take in
2. The stored procedure has no return value, as for out, it can be understood as a reference to an external variable; The function must have a return value
3. Stored procedure calls with call Proc_fun (); function with select Fun_fun ();

Three. Example

1. Function Example

DELIMITER $ $CREATE FUNCTION Test (a int (4)) RETURNS intlanguage sqlnot deterministicreads SQL datasql SECURITY definerbegin DECLARE b int DEFAULT 0; SELECT COUNT (*) into B from Test2 WHERE id>a; RETURN b; END $ $DELIMITER;

　　

Call: SELECT Test (0)

2. Stored Procedure Example:

The specified ID of the query test2 has no record, inserts a record without a record, sets the out variable A to 1, and if so, sets the out variable A to 0

int (oneint)) LANGUAGE sqlnot deterministiccontains sqlbeginselect ID from  test2 WHERE test2. ' id '=0  thenset a=0; Elseinsert into Test2 ("Time") VALUES (now ()); set a=-1; END IF; end$ $DELIMITER;

Call:
Call proc (@a, 2979);
Remove variable A
SELECT @a;

Ps:found_rows () Another trick
Paging can be done using sql_calc_found_rows + limit and found_rows
SELECT sql_calc_found_rows * FROM table_name LIMIT 10;
SELECT found_rows ();//This will return the total number of records queried above, not affected by limit

Four. Error handling in Stored procedures

If a statement block produces an error, what should I do with it? Is it quitting, or is it going?

1. Syntax:

DECLARE handler_action Handler          For Condition_value [, Condition_value] ...          Statement            handler_action:          CONTINUE        |  EXIT        | UNDO            condition_value:          mysql_error_code        |  SQLSTATE [VALUE] sqlstate_value        | Condition_name        | sqlwarning        | Not FOUND        | SQLEXCEPTION  

2. Example
DECLARE CONTINUE HANDLER for SQLEXCEPTION SET a=-1;
If an error is generated when executing the SQL statement, set the variable A to-1, or leave the original value if no error is generated

DELIMITER $ $CREATE PROCEDURE proc2 (out a int) LANGUAGE sqlnot deterministiccontains sqlbegindeclare CONTINUE HANDLER F OR SQLEXCEPTION SET a=-1; SET @x=0;insert into Test2 (' time ') VALUES (10); end$ $DELIMITER;

　　

Basic explanation: The MySQL stored procedure does not support assigning default values for out and in parameters, the default value is Null,null meaning is not very clear, so set a to 0;
Set a=0; On declare CONTINUE handler, an error will be raised. DECLARE CONTINUE Handler only the local variables defined by the DECLARE can be placed. Placing set @x=10, and other user variables, also throws an error.

Five. Cursors

1. Defining cursors
DECLARE cursor_name Cursor for select_statement

2. Open the cursor
OPEN cursor_name

3.FETCH Cursors
FETCH cursor_name into Var_name[,var_name]

4. Close the cursor
CLOSE cursor_name;

5. Example:

DELIMITER $ $CREATE PROCEDURE proc3 () LANGUAGE sqlnot deterministiccontains sqlbegindeclare myid int;declare my_cursor CURSOR for (SELECT ' id ' from test2 ORDER by ' id ' DESC);D eclare CONTINUE HANDLER for not FOUND CLOSE My_cursor;open my_curs Or FETCH my_cursor into myID; CLOSE My_cursor; SELECT myID; #打印结果END $ $DELIMITER;

PS: It is important to note that the variables in the process do not have the same name as the field names in the query statement, and the field names should be wrapped.

Since the above loop is not used, only one line is read out.

Six, Cycle

1.while Cycle

DELIMITER $ $CREATE PROCEDURE proc3 () LANGUAGE sqlnot deterministiccontains sqlbegindeclare myid INT; #定义变量DECLARE my_ Cursor cursor for (SELECT ' id ' from test2 ORDER by ' id ' DESC); #定义游标DECLARE CONTINUE HANDLER for not FOUND SET myID=
   
    0
    
    0 do
     #while循环开始SELECT myID, #打印结果FETCH my_cursor into myID;  #向下取一行数据END while; #结束循环标志CLOSE My_cursor; #关闭游标END $ $DELIMITER;
   

2.LOOP Loop:

DELIMITER $ $CREATE PROCEDURE proc5 () LANGUAGE sqlnot deterministiccontains sqlbegindeclare myID INT; #定义变量DECLARE my_cursor Cursor for (the SELECT ' id ' from test2 the ORDER by ' id ' DESC); #定义游标DECLARE CONTINUE HANDLER-not FOUND Set myid=0; #如果没有数据的时候, sets the Myis to 0OPEN My_cursor; #打开游标FETCH my_cursor into myID, #获取一行数据ins: LOOP #ins为循环体名字IF myid = 0 then #退出循环LEAVE ins; #类似breakEND if;if myID = 2980 theniterate ins; #跳过当前循环, directly executes the next loop end IF; SELECT myID; FETCH my_cursor into myID; END LOOP ins; CLOSE My_cursor; #关闭游标END $ $DELIMITER;

　　

3.repeat Cycle
Slightly

MySQL stored procedures and functions