MySQL stored procedures and functions

Create stored procedures and functions

Creating stored procedures and functions is a group that will be used frequently SQL Sentences together , And these SQL The statement is stored as a whole in MySQL The server

1. Create stored procedure

CREATE PROCEDURE procedure_name ([proc_param[,...]])
    [characteristic ...] routine_body

• procedure_name Indicates the name of the stored procedure to be created
• proc_param Parameters representing stored procedures
• characteristic Represents the characteristics of a stored procedure
• routine_body That represents a stored procedure SQL sentence

procedure_name No duplicate names

proc_param The syntax of each parameter in is as follows , Each parameter consists of three parts , Input respectively / The output type 、 Parameter name and parameter type . Input / There are three types of output , Namely IN（ Input type ）、OUT（ The output type ）、INOUT（ Input / The output type ）.param_name Indicates the parameter name ,type Indicates the type of parameter

[ IN | OUT | INOUT ] param_name type

characteristic Specify the characteristics of the stored procedure , There are the following values ：

• LANGUAGE SQL： Explain that... Is used in the stored procedure SQL language-written
• [NOT] DETERMINISTIC： Explain whether the result of the execution of the stored procedure is correct ,DETERMINISTIC Indicates that the result is certain , That is, every time a stored procedure is executed , The same input gets the same output ;NOT DETERMINISTIC Indicates that the result is uncertain , The same input may get different outputs , The default is NOT DETERMINISTIC
• {CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA}： Indicates that the subroutine uses SQL Sentence restrictions ,CONTAINS SQL Indicates that the subroutine does not contain statements to read or write data ,NO SQL Indicates that the subroutine does not contain SQL,READS SQL DATA Indicates that the subroutine contains statements that read data , But it doesn't include statements that write data ,MODIFIES SQL DATA Indicates that the subroutine contains statements that write data
• SQL SECURITY {DEFINER | INVOKER}： Specifies who has permission to execute ,DEFINER Indicates that only the definer can execute ,INVOKER Indicates that the caller with permission can execute , The default is DEFINER
• COMMENT 'string'： Annotation information , Can be used to describe stored procedures or functions

Example ： Create stored procedure , Query table t_employee Stored procedure for the salaries of all employees in the

DELIMITER $$
CREATE PROCEDURE proc_employee()
COMMENT ' Query employee salary '
BEGIN
	SELECT salary FROM t_employee
END;
$$
DELIMITER ;

MySQL The default is semicolon ; As a statement Terminator , In a stored procedure SQL The statement needs a semicolon to end , In order to avoid conflict , First use DELIMITER $$ take MySQL Is set to $$, Reuse DELIMITER ; Restore the terminator to a semicolon

2. Create a storage function

CREATE FUNCTION fun_name([func_param,[,...]])
	[characteristic ...] routine_body

• fun_name Indicates the name of the function
• func_param Indicates that the function starts from the parameter
• characteristic Represents a function property , The value is the same as that of the stored procedure
• routine_body Of a function SQL sentence

func_param The syntax of each parameter in is as follows , Parameter name and parameter type respectively

param_type type

Again, the previous example

DELIMITER $$
	CREATE FUNCTION func_employee(id INT(4))
		RETURNS INT(6)
	COMMENT ' Query employee salary '
	BEGIN
		SELECT salary FROM t_employee
	END;
	$$
DELIMITER ;

Variable

stay MySQL in , Variables can be defined and used , The scope of these variables is BEGIN ... END In the program section

1. Defining variables

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

• keyword DECLARE Used to declare variables
• Parameters var_name Is the name of the variable , You can define multiple variables at the same time
• Parameters type Used to specify the type of variable
• DEFAULT value Clause sets the default value of the variable to value, Not used DEFAULT When clause , The default value is NULL

Example ： Defining variables sql_test, The data type is INT type , The default value is 10

DECLARE test_sql INT DEFAULT 10;

2. Variable assignment

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

• keyword SET Used to assign values to variables
• Parameters var_name Is the name of the variable
• Parameters expr It's an assignment expression

Example ： Put the variable sql_test The assignment is 30

SET test_sql = 30;

Define conditions and handlers

Defining conditions and processing procedures is to define the problems that may be encountered in the process of program execution in advance , And the ability to solve these problems can be defined in the handler

1. Defined conditions

DECLARE condition_name CONDITION FOR condition_value
condition_value:
SQLSTATE[VALUE] sqlstate_value|mysql_error_code

• Parameters condition_name Indicates the condition name
• Parameters condition_value Represents the type of condition
• Parameters sqlstate_value and Parameters mysql_error_code All of them can express MySQL Error of

Example ： Definition ERROR 1146(42S02) error , The name is can_not_find, There are two different ways to define

//  Method 1 : Use sqlstate_value
DECLARE can_not_find CONDITION FOR SQLSTATE '42S02';
//  Method 2 : Use mysql_error_code
DECLARE can_not_find CONDITION FOR 1146;

2. Define handler

DECLARE handler_type HANDLER FOR condition_value[,...] proc_statement
handler_type:
CONTINUE|EXIT|UNDO
condition_value:
SQLSTATE[VALUE]sqlstate_value|condition_name|SQLWARNING|NOT FOUND|SQLEXCEPTION|mysql_error_code

• handler_type Indicate how the error is handled ： This parameter has three values ：

  • CONTINUE： Indicates that an error is encountered and will not be processed , Keep going down
  • EXIT： Exit immediately in case of an error
  • UNDO： Indicates that the previous operation is withdrawn after an error is encountered

• condition_value Indicates the error type , You can take the following values ：

  • SQLSTATE[VALUE]sqlstate_value contain 5 String error value of characters
  • condition_name Express DECLARE CONDITION Defined error condition name
  • SQLWARNING Match all 01 At the beginning SQLSTATE Error code
  • NOT FOUND Match all 02 At the beginning SQLSTATE Error code
  • SQLEXCEPTION Match all that have not been SQLWARNING or NOT FOUND The captured SQLSTATE Error code
  • mysql_error_code Match numeric type error code

• proc_statement Match numeric type error code

Here are some example ways to define handlers ：

//  Method 1 : Capture sqlstate_value
DECLARE CONTINUE HANDLER FOR SQLSTATE '42S02'
SET @info='NOT FOUND';
//  Method 2 : Use mysql_error_code 
DECLARE CONTINUE HANDLER FOR 1146
SET @info='NOT FOUND';
//  Method 3 : Define the conditions first , And then call 
ECLARE can_not_find CONDITION FOR 1146;
DECLARE CONTINUE HANDLER FOR can_not_find
SET @info='NOT FOUND';
//  Method four : Use SQLWARNING
DECLARE EXIT HANDLER FOR SQLWARNING 
SET @info='ERROR';
//  Method five : Use NOT FOUND
DECLARE EXIT HANDLER FOR NOT FOUND
SET @info='NOT FOUND';
//  Methods six : Use SQLEXCEPTION 
DECLARE EXIT HANDLER FOR  Use SQLEXCEPTION
SET @info='ERROR';

cursor

The query statement may query multiple records , Use the cursor in stored procedures and functions to read the records in the query result set one by one . The cursor must be declared before the handler , Declare after variables and conditions

1. Declaration cursor

DECLARE cursor_name CURSOR
FOR select_statement;

• Parameters cursor_name Indicates the cursor name
• Parameters select_statement Express SELECT sentence

The following statement is called cur_employee Cursor for

DECLARE cur_employee CURSOR
FOR SELECT name,age FROM t_employee;

2. Open the cursor

OPEN cursor_name;

• Parameters cursor_name Indicates the name of the cursor

Now open a file named cur_employee Cursor for

OPEN cur_employee;

3. Use the cursor

FETCH cursor_name
INTO var_name[,var_name...]

• cursor_name Indicates the name of the cursor
• var_name Indicates that... Will be in the cursor SELECT The information queried by the statement is stored in the parameter , This parameter must be defined before declaring the cursor

Now open a file named cur_employee Cursor for , Store the query data into emp_name and emp_age Of these two variables

FETCH cur_employee INTO emp_name,emp_age;

4. Close the cursor

CLOSE cursor_name

• cursor_name Indicates the name of the cursor

Process control

1. IF sentence

IF search_condition THEN statement_list
 [ELSEIF search_condition THEN statement_list]...
 [ELSE statement_list]
END IF

• Parameters search_condition Indicates a conditional judgment statement
• Parameters statement_list Execution statements representing different conditions

Here's a IF Examples of statements

IF age>20 THEN SET @[email protected]+1;
	ELSEIF age=20 THEN @[email protected]+1;
	ELSE @[email protected]+1;
END IF;

2. CASE sentence

CASE case_value
	WHEN when_value THEN statement_list
	[WHEN when_value THEN statement_list]...
	[ELSE statement_list]
END CASE

• Parameters case_value Variables representing conditional judgment
• Parameters when_value Indicates the value of the variable
• Parameters statement_list Represent different when_value The execution statement of

Here's a CASE Examples of statements

CASE age
	WHEN 20 THEN SET @[email protected]+1;
	ELSE SET @[email protected]+1;
END CASE;

3. LOOP sentence

[begin_label:]LOOP
	statement_list
END LOOP [end_label]

• Parameters begin_label And parameters end_label Marks indicating the beginning and end of the cycle respectively , It has to be the same , And you can omit
• Parameters statement_list Represents a statement that needs to be executed in a loop

Here's a LOOP Examples of statements

add_num:LOOP
	SELECT @[email protected]+1;
END LOOP add_num

4. LEAVE sentence

Mainly used to jump out of the loop

LEAVE label

• Parameters label A flag that represents a loop

Here's a LEAVE Examples of statements

add_num:LOOP
	SELECT @[email protected]+1;
	IF @count1=100 THEN
		LEAVE add_num;
END LOOP add_num

5. ITERATE sentence

Also used to jump out of the loop , Just jump out of this cycle , Go straight to the next cycle , similar Java Of continue

ITERATE label

• Parameters label A flag that represents a loop

Here's a ITERATE Examples of statements

add_num:LOOP
	SELECT @[email protected]+1;
	IF @count1=100 THEN
		LEAVE add_num;
	ELSE IF MOD(@count1,3)=0 THEN
		ITERATE add_num;
	SELECT * FROM employee;
END LOOP add_num

6. REPEAT sentence

REPEAT Statements are loop statements controlled by conditions , When certain conditions are met, the loop statement will exit

[begin_label:]REPEAT
	statement_list
	UNTIL search_condition
END REPEAT [end_label]

• Parameters statement_list Represents a statement that needs to be executed in a loop
• Parameters search_condition Indicates the condition for ending the loop , When this condition is met, the cycle ends

Here's a REPEAT Examples of statements

add_num:REPEAT
	SELECT @[email protected]+1;
	UNTIL @count1=100
END REPEAT add_num

7. WHILE sentence

WHILE Statements are also conditionally controlled loop statements , And REPEAT The difference is ,WHILE A statement is a statement that executes in a loop when a condition is met

[begin_label:]WHILE search_condition DO
	statement_list
END WHILE [end_label]

• Parameters statement_list Represents a statement that needs to be executed in a loop
• Parameters search_condition Indicates the condition for ending the loop , When this condition is met, the cycle ends

Here's a WHILE Examples of statements

WHILE @count<100 DO
	SET @[email protected]+1;
END WHILE;

Manipulate stored procedures and functions

1. Calling stored procedure

stay MySQL in , Use CALL Statement to call the stored procedure

CALL proc_name([parameter[,...]])

• proc_name Is the name of the stored procedure
• paramter Refers to the parameters of the stored procedure

2. Calling the storage function

stay MySQL in , How to use the storage function and MySQL The internal functions are the same

3. Use SHOW STATUS Statement to view the status of stored procedures and functions

SHOW {PROCEDURE|FUNCTION} STATUS{LIKE 'pattern'}

• Parameters PROCEDURE Represents a query stored procedure ,FUNCTION Represents a storage function
• Parameters LIKE 'pattern' Used to match the name of a stored procedure or function

4. Use SHOW CREATE Statement to see the definition of stored procedures and functions

SHOW CREATE {PROCEDURE|FUNCTION} proc_name

5. from information_schema.Routine View the information of stored procedures and functions in the table

SELECT * FROM information_schema.Routine
WHERE ROUTINE_NAME='proc_name'

6. Modify stored procedures and functions

ALTER {PROCEDURE|FUNCTION} proc_name[characteristic...];
characteristic:
	{CONTAINS SQL|NO SQL|READS SQL DATA|MODIFIES SQL DATA}
|SQL SECURITY{DEFINER|INVOKER}
|COMMENT 'string'

• Parameters proc_name Represents the name of a stored procedure or function
• Parameters characteristic Specify the characteristics of the stored procedure ：
  • CONTAINS SQL Indicates that the subroutine contains SQL sentence , But does not contain statements that read or write data
  • NO SQL Indicates that the subroutine does not contain SQL sentence
  • READS SQL DATA A statement that contains read data in a subroutine
  • MODIFIES SQL DATA Represents a statement in a subroutine that contains data to be written
• SQL SECURITY{DEFINER|INVOKER} Specifies who has permission to execute ：
  • DEFINER Indicates that only the definer can execute
  • INVOKER Indicates that the caller can execute
• COMMENT 'string' It's annotating information

7. Deletes stored procedures and functions

adopt DROP Statement delete stored procedure

DROP PROCEDURE proc_name;

adopt DROP FUNCTION Statement delete store function

DROP FUNCTION func_name;