Shulou(Shulou.com)06/01 Report--

A stored procedure is a collection of one or more SQL statements that can be treated as batch files, but its role is not limited to batch processing.

9.1. Create stored procedures and functions

Stored procedures can be divided into stored procedures and functions. The statements used to create stored procedures and functions in MySQL are CREATE PROCEDURE and CREATE FUNCTION, respectively. Using a call statement to call a stored procedure can only return a value with an output variable. Functions can be called from outside the statement (that is, by referencing the function name), or they can return scalar values. Stored procedures can also call other stored procedures.

Create a stored procedure

To create a stored procedure, you need to use a CREATE PROCEDURE statement. The basic syntax is:

CREATE PROCEDURE sp_name ([proc_parameter]) [characteristic...] Routine_body

CREATE PROCEDURE: keyword for creating stored procedures

Sp_name: name of the stored procedure

Proc_parameter: parameter list in the form of [IN | OUT | INOUT] param_name type

IN for input parameters, OUT for output parameters, INOUT for both input and output

Name of the param_name parameter; type of the type parameter, which can be any type in MySQL

Characteristic: specify the properties of a stored procedure

LANGUAGE SQL: indicates that the routine_body part is made up of SQL statements, and SQL is the only value of the LANGUAGE feature.

[NOT] DETERMINISTIC: indicates whether the result of the stored procedure execution is determined, DETERMINISTIC indicates that the result is determined, and each time the stored procedure is executed, the same input gets the same output; NOT DETERMINISTIC indicates uncertainty.

{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 the subroutine contains SQL statements, but does not contain statements to read and write data; NO SQL indicates that the subroutine does not contain SQL statements; READS SQL DATA indicates that the subroutine contains statements to read data; and MODIFIES SQL DATA indicates that the subroutine contains statements to write data.

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.

COMMENT 'string': comment information.

The content of the routine_body:SQL code, you can use BEGIN...END to represent the beginning and end of the code.

Create a stored procedure to view the fruits table

Mysql > DELIMITER / / mysql > CREATE PROCEDURE Proc ()-> BEGIN-> SELECT * FROM fruits;-> END / / Query OK, 0 rows affected (0.00 sec) mysql > DELIMITER

Create a stored procedure named CountProc

Mysql > DELIMITER / / mysql > CREATE PROCEDURE CountProc (OUT param1 INT)-> BEGIN-> SELECT COUNT (*) INTO param1 FROM fruits;-> END / / Query OK, 0 rows affected (0.00 sec) mysql > DELIMITER

The function of 'DELIMITER / /' is to set the Terminator of MySQL to / /, and the backslash ('\') character should be avoided when using the DELIMITER command, because the backslash is the escape character of MySQL.

Create a 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_bodyCREATE FUNCTION: the keyword func_name for creating a storage function indicates the name of the storage function

Func_parameter stores the parameter list of the function in the form of [IN | OUT | INOUT] param_name type

IN for input parameters, OUT for output parameters, INOUT for both input and output

Name of the param_name parameter; type of the type parameter, which can be any type in MySQL

RETURNS type indicates that the function returns the data type characteristic specifies the characteristics of the stored function, and the values are the same as those used to create the stored procedure

Create a storage function named NameByZip, which returns the query result of the SELECT statement. The numeric type is string.

Mysql > DELIMITER / / mysql > CREATE FUNCTION NameByZip ()-> RETURNS CHAR (50)-> RETURN (SELECT s_name FROM suppliers WHERE slots call = '48075');-> / / Query OK, 0 rows affected (0.00 sec) mysql > DELIMITER

The use of variables

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

DECLARE var_name [, varname]... Date_type [DEFAULT value]

Define a variable with the name myparam, the type is INT, and the default is 100

DECLARE myparam INT DEFAULT 100

After defining a variable, assigning a value to the variable can change the default information of the variable. MySQL uses the set statement to assign a value to the variable. The syntax format is:

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

Declare three variables, var1, var2 and var3, with the data type INT, and assign values to the variables using SET

DECLARE var1, var2, var3 INT;SET var1 = 10, var2 = 20 * * set var3 = var1 + var2

MySQLhankeyishiyong SELECT...INTO assigns values to one or more variables, and the syntax is:

SELECT col_name [,...] INTO var_name [,...] Table_expr

Declare the variables fruitname and fruitprice, through SELECT. The INTO statement queries the specified record and assigns values to the variable

DECLARE fruitname CHAR (50); DECLARE fruitprice DECIMAL (8Magne2); SELECT fancinamedfond price INTO fruitname, fruitpriceFROM fruits WHERE f_id = 'a1'

Define conditions and handlers

Specific conditions require specific processing. These conditions can be linked to errors, as well as general flow control of subroutines. 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. Use the DECLARE keyword to define conditions and handlers. The syntax format for defining a condition is:

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

Condition_name: condition name

Condition_type: type of condition

Both sqlstate_value and mysql_error_code can represent MySQL errors

String type error code with a sqlstate_value of length 5

Mysql_error_code is the numeric type error code

Error defining "ERROR 1148 (42000)", named command_not_allowed

/ / method 1: use sqlstate_value DECLARE command_not_allowed CONDITION FOR SQLSTATE '42000 strategies / method 2: use mysql_error_code DECLARE command_not_allowed CONDITION FOR 1148

When defining a handler, the syntax for using the DECLARE statement is:

DECLARE handler_action HANDLER FOR condition_value statement handler_action: CONTINUE | EXIT | UNDO condition_value: mysql_error_code | SQLSTATE [VALUE] sqlstate_value | condition_name | SQLWARNING | NOT FOUND | SQLEXCEPTION

Handler_action: handle the error. The parameter has three values: CONTINUE,EXIT,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.

When UNDO encounters an error, it withdraws its previous operation.

Condition_value indicates the type of error

SQLSTATE [VALUE] sqlstate_value: string error value containing 5 characters

Error condition name defined by condition_name: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: match numeric type error code

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

Several ways to define handlers

/ / method 1: capture sqlstate_value DECLARE 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 DECLARE no_such_table CONDITION FOR 1146 switch declare 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 FOR SQLEXCEPTION SET @ info='ERROR'

Define conditions and handlers

Mysql > CREATE TABLE test.t (S1 int,primary key (S1)); Query OK, 0 rows affected (0.05sec) mysql > DELIMITER / / mysql > CREATE PROCEDURE handlerdemo ()-> BEGIN-> DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @ x2 = 1;-> SET @ x = 1;-> INSERT INTO test.t VALUES (1);-> SET @ x = 2;-> INSERT INTO test.t VALUES (1) -> SET @ x = 3;-> END;-> / / Query OK, 0 rows affected (0.00 sec) mysql > DELIMITER; mysql > CALL handlerdemo (); / * call stored procedure * / Query OK, 0 rows affected, 1 warning (0.02 sec) mysql > SELECT @ x / * View the result of the calling process * / +-+ | @ x | +-+ | 3 | +-+ 1 row in set (0.00 sec)

'@ var_name' represents a user variable, which is assigned using a set statement, the user is related to the connection, and a client-defined variable cannot be seen or used by other clients. When a client exits, all variables of the client connection are automatically released.

Use of the cursor

Query statements may return multiple records, and if the 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. Cursors in MySQL can only be used in stored procedures and functions.

The cursor is declared using the DECLARE keyword in MySQL, and the basic syntax is as follows:

DECLARE cursor_name CURSOR FOR select_statement

Cursor_name represents the cursor name

Select_statement represents the contents of the SELECT statement

Returns a result set of user-created cursors

Declare a cursor named cursor_fruit

DECLARE cursor_fruit CURSOR FOR SELECT f_name, f_price FROM fruits

The syntax for opening the cursor is:

OPEN cursor_name [cursor name]

Open the cursor named cursor_fruit

OPEN cursor_fruit

The syntax for using the cursor is:

FETCH cursor_name INTO var_name [, var_name]... [parameter name]

Use the cursor named cursor_fruit. Store the queried data into the variables fruit_name and fruit_price

FETCH cursor_fruit INTO fruit_name, fruit_price

Close the cursor named cursor_fruit

CLOSE cursor_fruit

Use of process control

Flow control statements are used to control the execution of conditional statements. MySQL finally controls the flow with IF, CASE, LOOP, LEAVE, ITERATE, REPEAT, and WHERE statements.

IF

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:

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

An example of an IF statement

IF val IS NULL THEN SELECT 'val is NULL'; ELSE SELECT' val is not NULL';END IF

CASE

CASE is another statement for conditional judgment, which has two formats:

CASE case_value WHEN when_value THEN statement_list [WHEN when_value THEN statement_list]... [ELSE statement_list] END CASEOr:CASE WHEN search_condition THEN statement_list [WHEN search_condition THEN statement_list]... [ELSE statement_list] END CASE

Use the first format of the CASE process control statement to determine that the value is equal to 1, 2, or both

CASEval WHEN 1 THEN SELECT 'val is 1; WHEN 2 THEN SELECT' val is 2; ELSE SELECT 'val is not 1 or 2 output the string "val is 1" when valu is 1; when vale is 2, output string "val is 2"; otherwise, output string "val is not 1 or 2".

Use the second format of the CASE process control statement to determine whether the val is empty, less than 0, greater than 0, or equal to 0

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 output the string "val is NULL" when the value is empty; when the 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 "val is 0" is output.

LOOP

LOOP loop statements are used to execute certain statements repeatedly. Compared with IF and CASE, LOOP is just the process of creating a loop and does not make conditional judgments. The statements in LOOP are repeated all the time. Only I know that the loop is exited and the process of jumping out of the loop uses the LEAVE clause. The LOOP syntax format is as follows:

[begin_label:] LOOP statement_listEND LOOP [end_label]

Loop is performed using the lock statement, and the loop process is repeated before the id value is less than or equal to 10

DECLARE id INT DEFAULT 0 + addenda loop: LOOP SET id = id + 1; IF id > = 10 THEN LEAVE add_loop; END IF;END LOOP add_loop

LEAVE

The LEAVE statement is used to exit any annotated flow control construct in the syntax format:

LEAVE label

Exit the loop using the level statement

Add_num: LOOP SET @ count=@count+1;IF @ count=50 THEN LEAVE add_num; END LOOP add_num

ITERATE

The ITERATE statement changes the execution order to the beginning of the statement segment, with the syntax format:

ITERATE label

ITERATE can only appear within LOOP, REPEAT, WHERE statements.

Example of ITERATE statement

CREATE PROCEDURE doiterate () BEGINDECLARE p1 INT DEFAULT 0 / myopia 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

REPEAT

The REPEAT statement creates a loop with conditional judgment. After each execution of the statement, the conditional expression is judged. If true, the loop ends with the syntax format:

[begin_label:] REPEAT statement_listUNTIL search_conditionEND REPEAT [end_label]

REPEAT statement example, the loop process will be repeated before the id value is less than or equal to 10

DECLARE id INT DEFAULT 0 domestic REPEATSET id = id + 1 telluntil id > = 10END REPEAT

WHILE

The WHILE statement creates a loop process with conditional judgment. Unlike REPEAT, when WHERE executes the statement, it first judges the specified expression. If it is true, the statement that executes the loop has the following syntax format:

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

WHILE statement example, the loop process will be repeated before the id value is less than or equal to 10

DECLARE i INT DEFAULT 0While I

< 10 DOSET i = i + 1;END WHILE; 9.2、调用存储过程和函数 调用存储过程 存储过程通过CALL语句进行调用，语法格式为： CALL sp_name([parameter[,...]]) 定义名为CountProc1的存储过程，然后调用这个存储过程 mysql>

DELIMITER / / mysql > CREATE PROCEDURE CountProc1 (IN sid INT, OUT num INT)-> BEGIN-> SELECT COUNT (*) INTO num FROM fruits WHERE s_id = sid;-> END / / Query OK, 0 rows affected (0.00 sec) mysql > DELIMITER; mysql > CALL CountProc1 (101, @ num); Query OK, 1 row affected (0.00 sec) mysql > select @ num;+-+ | @ num | +-+ | 3 | +-+ 1 row in set (0.02 sec)

Call storage function

Define the storage function CountProc2, and then call this function

Mysql > DELIMITER / / mysql > CREATE FUNCTION CountProc2 (sid INT)-> RETURNS INT-> BEGIN-> RETURN (SELECT COUNT (*) FROM fruits WHERE s_id = sid);-> END / / Query OK, 0 rows affected (0.00 sec) mysql > DELIMITER; mysql > SELECT CountProc2 +-+ | Countproc (101) | +-+ | 3 | +-+

9.3. View stored procedures and functions

MySQL stores the state information of stored procedures and functions, which users can view using SHOW STATUS statements or SHOW CREATE statements, or directly from the system information_schema database.

Using the SHOW STATUS statement, you can view the status of stored procedures and functions. The basic syntax is:

SHOW [PROCEDURE | FUNCTION] STATUS [LIKE 'pattern']

Example of SHOW STATUS statement

Mysql > SHOW PROCEDURE STATUS LIKE'C%'\ gateway * 1. Row * * Db: test Name: CountProc Type: PROCEDURE Definer: root@localhost Modified: 2017- 08-04 11:32:08 Created: 2017-08-04 11:32:08 Security_type: DEFINER Comment: character_set_client: utf8collation_connection: utf8_general_ci Database Collation: utf8_general_ci1 row in set (2017 sec)

Using the SHOW CREATE statement, you can view the status of stored procedures and functions. The basic syntax is:

SHOW CREATE [PROCEDURE | FUNCTION] sp_name

Example of SHOW CREATE statement

Mysql > SHOW CREATE PROCEDURE test.CountProc\ G * * 1. Row * * Procedure: CountProc sql_mode: Create Procedure: CREATE DEFINER= `root` @ `localhost` PROCEDURE `CountProc` (OUT param1 INT) BEGIN SELECT COUNT (*) INTO param1 FROM fruits ENDcharacter_set_client: utf8collation_connection: utf8_general_ci Database Collation: utf8_general_ci1 row in set (0.00 sec)

The information about stored procedures and functions in MySQL is stored in the Routines table under the information_schema database. You can query stored procedures and functions by querying the records of the table. The basic syntax is as follows:

SELECT * FROM information_schema.Routines WHERE ROUTINE_NAME= 'sp_name'

Query the information of a stored procedure named CountProc from the Routines table

Mysql > SELECT * FROM information_schema.Routines-> WHERE ROUTINE_NAME='CountProc' AND ROUTINE_TYPE = 'PROCEDURE'\ G * * 1. Row * * SPECIFIC_NAME: CountProc ROUTINE_CATALOG: def ROUTINE_SCHEMA: Test ROUTINE_NAME: CountProc ROUTINE_TYPE: PROCEDURE DATA_TYPE: CHARACTER_MAXIMUM_LENGTH: NULL CHARACTER_OCTET_LENGTH: NULL NUMERIC_PRECISION: NULL NUMERIC_SCALE: NULL CHARACTER_SET_NAME: NULL COLLATION_NAME: NULL DTD_IDENTIFIER: NULL ROUTINE_BODY: SQL ROUTINE_DEFINITION: BEGIN SELECT COUNT (*) INTO param1 FROM fruits END EXTERNAL_NAME: NULL EXTERNAL_LANGUAGE: NULL PARAMETER_STYLE: SQL IS_DETERMINISTIC: NO SQL_DATA_ACCESS: CONTAINS SQL SQL_PATH: NULL SECURITY_TYPE: DEFINER CREATED: 2017-08-04 11:32:08 LAST_ALTERED: 2017-08-04 11:32:08 SQL_MODE : ROUTINE_COMMENT: DEFINER: root@localhost CHARACTER_SET_CLIENT: utf8 COLLATION_CONNECTION: utf8_general_ci DATABASE_COLLATION: utf8_general_ci1 row in set (0.00 sec)

9.4. Modify stored procedures and functions

You can use the alter statement to modify the properties of a stored procedure or function. The basic syntax is:

ALTER [PROCEDURE | FUNCTION] sp_name [characteristic...]

Modify the definition of the stored procedure CountProc. Change the read and write permissions to MODIFIES SQL DATA and indicate that the caller can execute

Mysql > ALTER PROCEDURE CountProc-> MODIFIES SQL DATA-> SQL SECURITY INVOKER; Query OK, 0 rows affected (0.00 sec) mysql > SELECT SPECIFIC_NAME,SQL_DATA_ACCESS,SECURITY_TYPE-> FROM information_schema.Routines-> WHERE ROUTINE_NAME='CountProc' AND ROUTINE_TYPE='PROCEDURE' +-+ | SPECIFIC_NAME | SQL_DATA_ACCESS | SECURITY_TYPE | +-+ | CountProc | MODIFIES SQL DATA | INVOKER | +-+ 1 row in set (0.00 sec)

9.5. Delete stored procedures and functions

To delete stored procedures and functions, you can use drop statements in the syntax format:

DROP [PROCEDURE | FUNCTION] [IF EXISTS] sp_name

Delete stored procedures and stored functions

Mysql > DROP PROCEDURE CountProc;Query OK, 0 rows affected (0.00 sec) mysql > DROP FUNCTION CountProc;Query OK, 0 rows affected (0.00 sec)

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.