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

Editor to share with you how to create MySQL storage program, I believe that most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!

Simply put, a stored procedure is a combination of one or more SQL statements that can be considered a batch file, but its role is not limited to batch processing.

(1) create a stored procedure

(2) create a storage function

(3) the use of variables

(4) define conditions and handlers

(5) the use of the cursor

(6) the use of process control

(1) create a stored procedure

To create a stored procedure, you need to use create procedure statements. The basic syntax format is as follows:

Create procedure sp_name ([proc_parameter]) [characteristics...] Routine_body

Create procedure is the keyword used to create the stored function; sp_name is the name of the stored procedure; and proc_parameter is the parameter list of the stored procedure, which is in the form of:

[in | out | inout] param_name type

In represents input parameters

Out represents output parameters

Inout means you can either import or export

Param_name represents the parameter name; type represents the type of the parameter

Characteristics specifies the characteristics of the stored procedure, with the following values:

Language SQL: indicates that the routine_body part is made up of SQL statements. The current language supported by the system is that SQL,SQL is the only value of the language feature.

[not] deterministic: indicates whether the result of the stored procedure execution is correct. Deterministic means that the same input will get the same output each time the stored procedure is executed, while not deterministic means that the same input may get different output. The default is not deterministic.

{contains SQL | no SQL | reads SQL date | modifies SQL date}: specify the restrictions on the use of SQL statements by subroutines. Contains SQL indicates that the subroutine contains SQL statements; no SQL indicates that the subroutine does not contain statements that SQ;reads SQL data subroutines contain read data; and modifies SQL data that subroutines contain statements to write data. The default is contatins SQL.

SQL security {definer | invoker}: indicates who has permission to execute. Definer means that only the definer can execute; invoker means that callers with permissions can execute. The default is definer.

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

Routine_body is the content of SQL code, you can use begin... End to represent the beginning and end of the SQL code.

[example 1] create a stored procedure to view the fruits table. The code statement is as follows:

Create procedure proc () BEGIN select * from fruits; END

This code creates a stored procedure to view the fruits table, and the code execution process is as follows:

Mysql > delimiter / / mysql > create procedure Proc ()-> begin-> select * from fruits;-> end / / Query OK, 0 rows affected (0.36 sec) mysql > delimiter

Tip: the purpose of the "delimiter / /" statement is to set the MySQL Terminator to / /, because the MySQL default statement ending symbol is a semicolon ";" to avoid conflicts with the SQL statement Terminator in the stored procedure. Use "delimiter;" to restore the default Terminator after the stored procedure is defined. When using the delimiter command, you should avoid using the backslash "" because the backslash is an escape character in MySQL.

[example 2] create a stored procedure named CountProc with the following code:

Create procedure CountProc (OUT paraml int) beginselect count (*) into paraml from fruits;end

The above code creates a stored procedure that gets the number of records in the fruits table, named CountProc,count (*), calculates and puts the result into the parameter paraml. The execution result of the code is as follows:

Mysql > delimiter / / mysql > create procedure CountProc (OUT paraml int)-> begin-> select count (*) into paraml from fruits;-> end / / Query OK, 0 rows affected (0.08 sec) mysql > delimiter; (2) create storage function

To create a storage function, you need to use a create function statement. The basic syntax is as follows:

Create function func_name ([func_parameter]) returns type [characteristic...] Routine_body

Create function is the keyword used to create the storage function

Func_name represents the name of the storage function

Func_parameter is the parameter list of the stored procedure, which is in the form of [in | out | inout] param_name type.

Where in represents the input parameter, out represents the output parameter, inout indicates that you can input or output param_name to represent the parameter name, and type represents the type of parameter; the returns type statement indicates the type of data returned by the function; and characteristic specifies the characteristics of the stored function, which is the same as when creating the stored procedure.

[example 3] create a storage function named NameByZip, which returns the query result of the select statement. The value type is string, and the code is as follows:

Create function NameByZip () returns char (50) return (select s_name from suppliers where s_call = '48075')

The execution result of the code is as follows

Mysql > delimiter / / mysql > create function NameByZip ()-> returns char (50)-> return (select s_name from suppliers where s_call = '48075');-> / Query OK, 0 rows affected (0.06 sec) mysql > delimiter

If the ruturn statement in the stored function returns a value of a type different from that specified in the function's returns clause, the return value will be forced to the appropriate type.

Note: specifying a parameter of in, out, or inout is legal only for procedure. (the in parameter is always the default in function.) The returns clause can only specify function, which is mandatory for functions. It is used to specify the return type of the function, and the function body must contain a return value statement.

(3) the use of variables

Variables can be declared and used in subroutines, and the scope of these variables is in begin. In the end program.

1. Define variable

Use the declare statement to define variables in a stored procedure in the following syntax format:

Declare var_name [, varname]... Date_type [default value]

Var_name is the name of the local variable. The default value clause provides a default value for the variable. In addition to being declared as a constant, a value can be specified as an expression. If there is no default clause, the initial value is null.

[example 4] define a variable named myparam, the type is int, and the default value is 100. the code is as follows:

Declare myparam int default 100

two。 Assign a value to a variable

Set var_name = expr [, var_name = expr].

The set statement in the stored program is an extended version of the general set statement. The referenced variable may be a variable declared within a subroutine, or a global server variable, such as a system variable or a user variable.

[example 5] declare three variables, var1,var2 and var3, the data type is int, and assign values to the variables using set. The code is as follows:

Declare var1,var2,var3 int;set var1 = 10, var2 = 20 * * set var3 = var1 + var2

In MySQL, you can also use select. Into assigns values to one or more variables, with the following statement:

Select col_name [,...] Into var_name [,...] Table_expr

This select syntax stores the selected column directly to the variable in the corresponding location. Col_name represents the field name; var_name represents the defined variable name; and table_expr represents the query condition expression, including the table name and the where clause.

[example 6] declare the variables fruitname and fruitprice, through select... The into statement queries the specified record and assigns a value to the variable. The code is as follows:

Declare fruitname char (50); declare fruitprice decimal (8 Magazine 2); select fancinamethomanery frankprice into fruitname,fruitpricefrom fruits where fanciidhandles A1; (4) define conditions and handlers.

Specific conditions require specific processing. The definition condition is to define the problems encountered in the execution of the program in advance, and the handler defines the way to deal with these problems, and ensures that the stored procedure or function can continue to execute in the event of warnings or errors. This enhances the ability of the stored program to deal with problems and prevents the program from stopping abnormally.

1. Define condition

Define the condition using the declare statement, and the syntax format is as follows:

Declare conditon_name Condition for [condition_type] [condition_type]; SQLSTATE [value] sqlstate_value | mysql_error_code

Condition_name represents the name of the condition

Condition_type represents the type of condition

Both sqlstate_value and mysql_error_code can represent errors in MySQL

Sqlstate_value is a character type error code of length 5

Mysql_error_code is the numeric type error code

For example: in ERROR1142 (42000), the value of sqlstate_value is 42000, and the value of MySQL error error code is 1142.

This statement specifies the conditions that require special handling. It associates a name with the specified error condition. This name can then be used in the declare handler statement that defines the handler.

[example 7] error defining "error 1148 (42000)" with the name command_not_allowed. You can define it in two different ways, with the following code:

[method 1]: use sqlstate_valuedeclare command_not_allowed condition for sqlstate '42000' [] method 2]: use mysql_error_codedeclare command_not_allowed condition for 1148

two。 Define a handler

When defining a handler, the syntax for using the declare statement is as follows:

Declare handler_type handler for condition_value [,...] Sp_statementhandler_type: continue | exit | undocondition_value: sqlstate [value] sqlstate_value | condition_name | sqlwarning | not found | sqlexception | mysql_error_code

Among them

Handler_type is the error handling method, and the parameters take three values: continue, exit, and undo.

Continue indicates that if an error is encountered, it will not be handled and will continue to execute.

Exit quit as soon as it encountered an error.

Undo indicates that the previous operation is withdrawn after an error, which is not supported in MySQL for the time being.

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

Sqlstate [value] sqlstate_value contains 5 string error values

Condition_name represents the name of the error condition defined by declare condition

Sqlwarning matches all sqlstate error codes that start with 01

Notfound matches all sqlstate error codes that start 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

The sp_statement parameter is a program statement segment that represents the stored procedure or function that needs to be executed when a defined error is encountered.

[example 8] several ways to define a handler are as follows:

Method 1: capture sqlstate_valuedeclare continue handler for sqlstate '42S02' set @ info='No_SUCH_TABLE'; method 2: capture mysql_error_codedeclare continue handler for 1146 set @ info='No_SUCH_TABLE'; method 3: define the condition first, and then call the declare no_such_table condition for 1146 continue handler for NO_SUCH_TABLE SET @ info='NO_SUCH_TABLE'; method 4: use sqlwarningdeclare exit handler for sqlwarning set @ info='ERROR' Method 5: use not founddeclare exit handler for not found set @ info=' NO_SUCH_TABLE'; method 6: use sqlexceptiondeclare exit handler forsqlexception set @ info='ERROR'

The above code is six ways to define handlers.

The first is to capture the sqlstate_ value. If a sqlstate_ value of "42S02" is encountered, the continue operation is performed and the "NO_SUCH_TABLE" information is output.

Second, capture the mysql_error_ code value. If a mysql_error_ code value of 1146 is encountered, the continue operation is performed and the "NO_SUCH_TABLE" information is output.

Third, define the condition before invoking the condition. Here, the no_such_table condition is defined, and the continue operation is performed when a 1146 error is encountered.

Fourth, use sqlwarning. Sqlwarning captures all sqlstate_ values that begin with 01, then performs the exit operation, and outputs "ERROE" information.

Fifth, use not found. Not found captures all sqlstate_ values that start with 02, then performs the exit operation, and outputs "NO_SUCH_TABLE" information.

Sixth, use SQLEXCEPTION. Sqlexception captures all sqlstate_ values that are not captured by sqlwarning or not found, then performs an exit operation and outputs "ERROR" information.

[example 9] define conditions and handlers, and the specific implementation process is as follows:

Mysql > create table test.t (S1 int,primary key (S1)); Query OK, 0 rows affected (0.14 sec) mysql > delimiter / / mysql > create procedure handlerdemo ()-> begin-> declare continue handler for sqlstate '23000' set @ x2room1;-> set @ x = 1;-> insert into test.t values (1);-> set @ xroom2;-> insert into test.t values (1);-> set @ xroom3;-> end -> / / Query OK, 0 rows affected (0.06 sec) [call stored procedure] mysql > delimiter; mysql > call handlerdemo (); Query OK, 0 rows affected (0.08 sec) [View the result of the calling process] mysql > select @ x Tinci + | @ x | +-+ | 3 | +-+ 1 row in set (0.00 sec)

As you can see, @ x is a user variable, and the execution result @ x equals 3, indicating that MySQL is executed to the end of the program.

"var_name" represents a user variable and is assigned a value using a set statement. User variables are related to the connection, and a client-defined variable cannot be seen or used by other clients. When a client exits, all variables for that client connection are automatically released.

(5) the use of the cursor

Cursors in MySQL can only be used in stored procedures and functions.

Query statements may return multiple records, and if the amount of data is very large, you need to use the cursor in stored procedures and stored functions to read the records in the query result set one by one. The cursor must be declared before the handler is declared, and variables and conditions must also be declared before the cursor or handler is declared.

1. Declaration cursor

The declare keyword is used in MySQL to declare the cursor. The syntax is as follows:

Declare cursor_name cursor for select_statement

Where the cursor_name parameter represents the name of the cursor; select_statement represents the contents of the select statement and returns a result set used to create the cursor.

[example 10] declare the cursor named cursor_fruit, the code is as follows:

Declare cursor_fruit cursor for select f_name,f_price from fruits

In this code, the cursor name is the cursor_fruit,select statement section that summarizes and queries the values of the f_name and f_price fields from the fruits table.

two。 Open the cursor

Open cursor_name {cursor name}

This statement opens the previously declared cursor named cursor_name.

[example 11] Open the cursor named cursor_fruit with the following code:

Open cursor_fruit

3. Use the cursor

Use the syntax format of the cursor:

Fetch cursor_name into var_name [, var_name]... {Parameter name}

Where the cursor_name parameter represents the name of the cursor; var_name means that the information queried by the select statement in the cursor is stored in the parameter, and the var_name must be defined before the cursor is declared.

[example 12] use a cursor named cursor_fruit. Store the queried data in two variables, fruit_name and fruit_price, as follows:

Fetch cursor_fruit into fruit_name,fruit_price

4. Close the cursor

Turn off the syntax format of the cursor:

Close cursor_name (cursor name)

This statement closes the previously opened cursor.

If it is not explicitly closed, the cursor is closed at the end of the compound statement in which it is declared.

[example 13] close the cursor named cursor_fruit, the code is as follows:

Close cursor_fruit; (6) use of process control

Flow control statements are used to control the execution of statements according to conditions. The statements used to construct the control flow in MySQL are IF statement, case statement, loop statement, leave statement, iterate statement, repeat statement and while statement. Each process may contain a separate statement, or use begin... The conformance statement of the end construction, which can be nested.

1.if statement

The if statement contains multiple conditional judgments. According to the result of the judgment, the corresponding statement is executed for true or false. The syntax format is as follows:

If expr_condition then statement_list [elseif expr_condition then statement_list]... [else statement_list] end if

If the expr_condition evaluation is true, the corresponding list of SQL statements is executed; if there is no expr_condition match, the list of statements in the else clause is executed. The statement_list list can include one or more statements.

There is also an if () function in MySQL, which is different from the if statement described here.

[example 14] example of if statement

If val is null then select 'val is null'; else select' val is not null';end if

The example determines whether the vale value is empty, and if it is empty, the output string "val is null"; otherwise, the output string "val is not null". All if statements need to end with end if.

2.case statement

Case is another statement for conditional judgment. There are two statement formats, the first one:

Case case_expr when when_value then statement_list [when when_value then statement_list]... [else statement_list] end case

Case_expr represents an expression for conditional judgment, which determines which when statement will be executed

When_value represents the possible value of the expression.

If a when_value expression and an case_expr expression have the same result, the statement in statement_list after the corresponding then keyword is executed.

Statement_list represents execution statements with different when_ value values.

[example 15] use the first format of the case process control statement to determine that the value is equal to 1, 2, or both. The SQL statement is as follows:

Caseval when 1 then select 'val is 1; when 2 then select' val is 2; else select 'val is not 1 or 2

When value is 1, the string "val is 1" is output; when value is 2, the string "val is 2" is output; otherwise, the string "val is not 1 or 2" is output.

The second format of the case statement is as follows:

Case when expr_condition then statement_list [when expr_condition then statement_list] [else statement_list] end case

Expr_condition represents a conditional judgment statement

Statement_list represents execution statements under different conditions

In this statement, the when statement is executed one by one until an expr_condition expression is true, then the statement_list statement after the corresponding then keyword is executed. If there is no conditional match, the statement in the else clause is executed.

Note: the difference between the case statement in the stored program and the case control flow function:

The case statement in the stored program cannot have an else null clause and is terminated with end case instead of end.

[example 16] use the second format of the case flow control statement to determine whether the val is empty, less than 0, greater than 0 or equal to 0 SQL statement as follows:

Case when val is null then select 'val is null'; when val

< 0 then select 'val is less than 0'; when val >

0 then select 'val is greater than 0; else select' val is 0; end case

When the vale value is empty, the string "val is null" is output; when the vale value is less than 0, the string "val is less than 0" is output; when the val value is greater than 0, the string "val is greater than 0" is output; otherwise, the string "valu 0" is output.

3.loop statement

Loop loop statements are used to execute certain statements repeatedly. Compared with if and case statements, loop only creates a loop-operated process without making conditional judgments. The exit loop process uses the quit clause. The format of the loop syntax is as follows:

[loop_label:] loop statement_listend loop [loop_label]

Loop_label represents the annotation name of the loop statement, and this parameter can be omitted. The statement_list parameter represents the statement that needs to be executed in a loop.

[example 17] Loop statement is used for loop operation. Before the id value is less than or equal to 10, the loop process will be executed repeatedly. The SQL statement is as follows:

Declare id int default 10add_loop:loopset id = id + 1; if > = 10 then leave add_loop; end if;end loop add_loop

The example loop performs the operation of id plus 1. When the id value is less than 10:00, the loop executes repeatedly. When the id value is greater than or equal to 10:00, use the quit statement to exit the loop. The loop loop ends with end loop.

4.leave statement

The leave statement is used to exit any annotated flow control construct. The basic format of the leave statement is as follows:

Leave label

Where the label parameter represents the flag of the loop. Leave and begin... End or loops are used together.

[example 18] use the cancel statement to exit the loop, as follows:

Add_num:loopset @ count=@count+1;if @ count=50 then leave add_num;end loop add_num

The example loop performs the operation of count plus 1, and when the value of count is equal to 50, the circle statement is used to jump out of the loop.

5.iterate statement

The iterater label statement shifts the execution order to the beginning of the statement segment in the following syntax format:

Iterate label

Iterate can only appear within loop, repeat, and while statements. Iterate means "loop again", and the label parameter indicates the flag of the loop. The iterate statement must precede the loop flag.

[example 19] example of iterate statement:

Create procedure doiterate () begin declare p1 int default 0; declare p1 int default 0; my_loop:loop; set p1 = p1 + 1; if p1

< 10 then iterate my_loop; elseif p1 >

20 then leave my_loop;end if; select'p1 is between 10 and 20 investors end loop my_loop;end

First, define p1 is between 0, and repeat the p1 plus 1 operation when the value of p1 is less than 10:00; when p1 is greater than or equal to 10 and less than or equal to 20:00, print the message "p1 is between 10 and 20"; when p1 is greater than 20:00, exit the loop.

6.repeat statement

The repeat statement creates a loop with conditional judgment. Each time the statement is executed, the conditional expression is judged. If the expression is true, the loop ends; otherwise, the statement in the loop is repeated. The syntax format of the repeat statement is as follows:

[repeat_label:] repeat statement_listuntil expr_conditionend repeat [repeat_label]

Repeat_label is the name of the repeat statement, and this parameter can be omitted; statements or statement groups within the repeat statement are repeated until expr_condition is true.

[example 20] example of the repeat statement. Before the id value is equal to 10, the loop process will be executed repeatedly, as follows:

Declare id int default 0 is repeatset id = id + 1 Singapore id > = 10end repeat

The example loop performs the operation of id plus 1. The loop executes repeatedly when the id value is less than 10:00, and exits when the id value is greater than or equal to 10:00. The repeat loop ends with end repeat.

7.while statement

While statement creates a loop process with conditional judgment. Unlike repeat, when while executes the statement, it first judges the specified expression. If it is true, it executes the statement in the loop, otherwise it exits the loop. The basic format of the while statement is as follows:

[while_label:] while expr_condition do statement_listend while [while_label]

While_label is the callout name of the while statement

Expr_condition is the expression to judge. If the expression turns out to be true, the statement or statement group within the while statement is executed until the expr_condition is false and exits the loop.

[example 21] example of a while statement. If the I value is less than 10:00, the loop process will be executed repeatedly, as follows:

Declare i int default 0bot while I

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.