Shulou(Shulou.com)05/31 Report--

This article mainly introduces the relevant knowledge of "how to create MySQL stored procedures and functions". The editor shows you the operation process through an actual case. The operation method is simple, fast and practical. I hope this article "how to create MySQL stored procedures and functions" can help you solve the problem.

1.0 create stored procedures and functions

To create stored procedures and functions is to combine a set of frequently used SQL statements and store these SQL statements as a whole on the MySQL server

1. Create a stored procedure CREATE PROCEDURE procedure_name ([proc_param [,...]]) [characteristic...] Routine_body

Procedure_name represents the name of the stored procedure to be created

Proc_param represents the parameters of a stored procedure

Characteristic represents the characteristics of a stored procedure

Routine_body represents the SQL statement of a stored procedure

Procedure_name cannot have a duplicate name.

The syntax of each parameter in proc_param is as follows, and each parameter consists of three parts, namely input / output type, parameter name, and parameter type. There are three types of input / output types, namely IN (input type), OUT (output type), and INOUT (input / output type). Param_name represents the parameter name, and type represents the parameter type

[IN | OUT | INOUT] param_name type

Characteristic specifies the properties of the stored procedure, with the following values:

LANGUAGE SQL: indicates that the stored procedure is written in SQL language

[NOT] DETERMINISTIC: indicates whether the result of the stored procedure execution is correct. DETERMINISTIC indicates that the result is certain, that is, each time the stored procedure is executed, the same input will get the same output; NOT DETERMINISTIC indicates that the result is uncertain, and the same input may get different output. The default is NOT DETERMINISTIC.

{CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA}: specify the restrictions on the use of SQL statements by subroutines. CONTAINS SQL indicates that subroutines do not contain statements to read or write data. NO SQL indicates that subroutines do not contain statements that read data, but does not contain statements that write data. MODIFIES SQL DATA indicates that subroutines contain statements that write data.

SQL SECURITY {DEFINER | INVOKER}: indicates who has the permission to execute. DEFINER means that only the definer can execute. INVOKER means the caller with permission can execute. Default is DEFINER.

COMMENT 'string': comment information, which can be used to describe stored procedures or functions

Example: create a stored procedure that queries the salary of all employees in the table t_employee

DELIMITER $$CREATE PROCEDURE proc_employee () COMMENT 'query the salary of employees' BEGIN SELECT salary FROM employees

The semicolon is used by default in MySQL; as the statement Terminator, the SQL statement in the stored procedure needs a semicolon to end. To avoid conflicts, use DELIMITER $$to set the MySQL Terminator to $$, then use DELIMITER; restore the Terminator to semicolon

two。 Create the storage function CREATE FUNCTION fun_name ([func_param, [,...]]) [characteristic...] Routine_body

Fun_name represents the name of the function

Func_param represents the function from the parameter

Characteristic represents the property of the function, and the value is the same as that of the stored procedure.

Routine_body represents the SQL statement of a function

The syntax of each parameter in func_param is as follows: parameter name and parameter type.

Param_type type

It's still 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; 2 | 0 variable

In MySQL, you can define and use variables whose scope is in BEGIN... In the END program segment

1. Define the variable DECLARE var_name [,...] Type [DEFAULT value]

The keyword DECLARE is used to declare variables

The parameter var_name is the name of the variable, and multiple variables can be defined at the same time

The parameter type is used to specify the type of variable

The DEFAULT value clause sets the default value of the variable to value, and when the DEFAULT clause is not used, the default value is NULL

Example: define the variable sql_test, the data type is INT, and the default is 10

DECLARE test_sql INT DEFAULT 10 is full of 2. Variable assignment SET var_name=expr [, var_name=expr]...

The keyword SET is used to assign values to variables.

Parameter var_name is the name of the variable

Parameter expr is an assignment expression

Example: assign the variable sql_test to 30

SET test_sql = 30t3 | 0 define conditions and handlers

Defining conditions and handlers is a pre-defined problem that may be encountered during program execution, and the ability to solve these problems can be defined in the handler.

1. Define condition DECLARE condition_name CONDITION FOR condition_valuecondition_value: SQLSTATE [value] sqlstate_value | mysql_error_code

The parameter condition_name represents the condition name

The parameter condition_value indicates the condition type

Both parameter sqlstate_value and parameter mysql_error_code can indicate the error of MySQL.

Example: define ERROR 1146 (42S02) error, named can_not_find, which can be defined in two different ways

/ / method 1: use sqlstate_valueDECLARE can_not_find CONDITION FOR SQLSTATE '42S02 strategy strategy / method 2: use mysql_error_codeDECLARE can_not_find CONDITION FOR 1146 position 2. Define the handler DECLARE handler_type HANDLER FOR condition_value [,...] Proc_statementhandler_type:CONTINUE | EXIT | UNDOcondition_value: SQLSTATE [value] sqlstate_value | condition_name | SQLWARNING | NOT FOUND | SQLEXCEPTION | mysql_error_code

Handler_type indicates how to handle the error: this parameter has three values:

CONTINUE: indicates that the error is not handled, and the execution continues.

EXIT: means to quit immediately if you encounter an error

UNDO: indicates that the previous operation is withdrawn after an error is encountered

Condition_value represents the error type and can have the following values:

SQLSTATE [value] sqlstate_value contains a string error value of 5 characters

Condition_name represents the name of the error condition defined by DECLARE CONDITION

SQLWARNING matches all SQLSTATE error codes starting with 01

NOT FOUND matches all SQLSTATE error codes starting with 02

SQLEXCEPTION matches all SQLSTATE error codes that are not captured by SQLWARNING or NOT FOUND

Mysql_error_code matching numeric type error code

Proc_statement matching numeric type error code

Here are several examples of how to define handlers:

/ / method 1: capture sqlstate_valueDECLARE CONTINUE HANDLER FOR SQLSTATE '42S02'SET @ info='NOT FOUND';// method 2: use mysql_error_code DECLARE CONTINUE HANDLER FOR 1146SET @ info='NOT FOUND';// method 3: define the condition first, and then call ECLARE can_not_find CONDITION FOR 1146 scatter CONTINUE HANDLER FOR can_not_findSET @ info='NOT FOUND';// method 4: use SQLWARNINGDECLARE EXIT HANDLER FOR SQLWARNING SET @ info='ERROR' / / method 5: use NOT FOUNDDECLARE EXIT HANDLER FOR NOT FOUNDSET @ info='NOT FOUND';// method 6: use SQLEXCEPTION DECLARE EXIT HANDLER FOR use SQLEXCEPTIONSET @ info='ERROR';4 | 0 cursor

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

1. Declaration cursor DECLARE cursor_name CURSORFOR select_statement

The parameter cursor_name represents the cursor name

The parameter select_statement represents the SELECT statement

Declare a cursor named cur_employee below

DECLARE cur_employee CURSORFOR SELECT name,age FROM tasking employee. Open the cursor OPEN cursor_name

The parameter cursor_name represents the name of the cursor

Next, open a cursor named cur_employee

OPEN cur_employee;3. Use the cursor FETCH cursor_nameINTO var_name [, var_name...]

Cursor_name represents the name of the cursor

Var_name means to store the information queried by the SELECT statement in the cursor into the parameter, which must be defined before the cursor is declared

Next, open a cursor named cur_employee and store the query data in the variables emp_name and emp_age

FETCH cur_employee INTO emp_name,emp_age;4. Close the cursor CLOSE cursor_name

Cursor_name represents the name of the cursor

5 | 0 flow control 1. IF statement IF search_condition THEN statement_list [ELSEIF search_condition THEN statement_list]... [ELSE statement_list] END IF

The parameter search_condition represents a conditional judgment statement

The parameter statement_list represents the execution statement under different conditions

Here is an example of an IF statement

IF age > 20 THEN SET @ count1=@conut1+1; ELSEIF age=20 THEN @ count2=@conut2+1; ELSE @ count3=@count3+1;END IF;2. CASE statement CASE case_value WHEN when_value THEN statement_list [WHEN when_value THEN statement_list]. [ELSE statement_list] END CASE

The parameter case_value represents the variable of conditional judgment.

The parameter when_value represents the value of the variable

The parameter statement_list represents the execution statement of different when_value

The following is an example of a CASE statement

CASE age WHEN 20 THEN SET @ count1=@count1+1; ELSE SET @ count2=@count2+1;END CASE;3. LOOP statement [begin_label:] LOOP statement_listEND LOOP [end_label]

Parameter begin_label and parameter end_label indicate the start and end flags of the loop, respectively, which must be the same and can be omitted.

The parameter statement_list indicates the statement that needs to be executed in a loop

Here is an example of a LOOP statement

Add_num:LOOP SELECT @ count1=@count1+1;END LOOP add_num4. LEAVE statement

Mainly used to jump out of the cycle

LEAVE label

The parameter label represents the flag of the loop

Here is an example of a LEAVE statement

Add_num:LOOP SELECT @ count1=@count1+1; IF @ count1=100 THEN LEAVE add_num;END LOOP add_num5. ITERATE statement

It is also used to jump out of the loop, but out of this loop and directly into the next loop, similar to Java's continue.

ITERATE label

ITERATE label

The parameter label represents the flag of the loop

Here is an example of an ITERATE statement

Add_num:LOOP SELECT @ count1=@count1+1; IF @ count1=100 THEN LEAVE add_num; ELSE IF MOD (@ count1,3) = 0 THEN ITERATE add_num; SELECT * FROM employee;END LOOP add_num6. REPEAT statement

A REPEAT statement is a loop statement controlled by a condition, which exits when a specific condition is met.

[begin_label:] REPEAT statement_list UNTIL search_conditionEND REPEAT [end_label]

The parameter statement_list indicates the statement that needs to be executed in a loop

The parameter search_condition indicates the condition for ending the loop. If this condition is met, the loop ends.

Here is an example of a REPEAT statement

Add_num:REPEAT SELECT @ count1=@count1+1; UNTIL @ count1=100END REPEAT add_num7. WHILE statement

WHILE statements are also conditional loop statements. Unlike REPEAT statements, WHILE statements execute statements within the loop when the conditions are met.

[begin_label:] WHILE search_condition DO statement_listEND WHILE [end_label]

The parameter statement_list indicates the statement that needs to be executed in a loop

The parameter search_condition indicates the condition for ending the loop. If this condition is met, the loop ends.

Here is an example of a WHILE statement

WHILE @ count

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.