MySQL 7-1-Stored Procedure

MySQL 7-1-Stored Procedure 1. advantages of using Stored Procedure: (1) the stored procedure runs on the server and runs fast. (2) After a stored procedure is executed once, the execution plan will reside in the cache. In future operations, you only need to call the compiled binary code for execution from the cache, improves system performance. (3) Ensure database security. You can use the stored procedure to complete all database operations and control the permissions of these operations on database information through programming. Www.2cto.com 2. You can use the create procedure statement to create a stored procedure. To CREATE a stored procedure in MySQL 5.1, you must have the CREATE routine permission. To view the stored procedures in the database, run the show procedure status Command. To view the specific information of a stored PROCEDURE, run the showcreate procedure sp_name command, where sp_name is the name of the stored PROCEDURE. Syntax format of create procedure: create procedure sp_name ([proc_parameter [,...]) [characteristic...] IN routine_body, The proc_parameter parameter is as follows: [IN | OUT | INOUT] param_name type characteristic features are as follows: language SQL | [NOT] DETERMINISTIC | {contains SQL | no SQL | reads SQL data | modifies SQL data} | SQL SECURITY {DEFINER | INVOKER} | COMMENT 'string' description: ● sp_name: name of the stored procedure, which is created in the current database by default. To create a stored procedure in a specific database, add the database name before the name in the format of db_name.sp_name. It is worth noting that this name should be used as far as possible to avoid the same name as the built-in functions of MySQL. Otherwise, an error will occur. ● Proc_parameter: The Stored Procedure Parameter. param_name is the parameter name and type is the parameter type. Multiple parameters are separated by commas. A stored procedure can have 0, 1, or more parameters. The MySQL Stored Procedure supports three types of parameters: input, output, and input/output. The keywords are IN, OUT, And INOUT. Input parameters to pass data to a stored procedure. When an answer or result is returned, the stored procedure uses the output parameter. The input/output parameters can be both input parameters and output parameters. Stored procedures can also be left blank, but the brackets behind the name cannot be omitted. Note: The parameter name should not be the same as the column name. Otherwise, although no error message is returned, the SQL statement in the stored procedure regards the parameter name as the column name, leading to unpredictable results. Characteristic: Some feature settings of a stored procedure. The following describes language SQL: indicates that the language used to write the stored procedure is an SQL language. Currently, mySQL stored procedures cannot be written in external programming languages, that is, this option can be left unspecified. It will be extended in the future. The most likely first supported language is PHP. Www.2cto.com deterministic: if it is set to DETERMINISTIC, the stored procedure produces the same results for the same input parameters. If it is set to not deterministic, the uncertain results will be generated. The default value is NOTDETERMINISTIC. Contains SQL: a statement that does not contain read or write data. No SQL indicates that the stored procedure does not contain SQL statements. Reads SQL DATA indicates that the Stored Procedure contains statements for reading DATA, but does not contain statements for writing DATA. Modifies SQL DATA indicates the statement that the Stored Procedure contains DATA writing. If these features are not explicitly specified, the default value is contains SQL. SQL security: the SQL SECURITY feature can be used to specify whether a stored procedure is executed using the user (DEFINER) license that created the stored procedure, or the caller (INVOKER) license. The default value is DEFINER. COMMENT 'string': Description of the stored procedure. string is the description. This information can be displayed using the showcreate procedure statement. ● Routine_body: This is the main part of the stored procedure, also known as the Stored Procedure body. It contains the statements that must be executed during the process call. This part always starts with begin and ends with end. Of course, the BEGIN-END flag can be omitted when there is only one SQL statement in the Stored Procedure body. 3. Before creating a stored procedure, introduce a very practical command, namely the delimiter command. In MySQL, the server processes statements with a semicolon ending sign. However, when creating a stored procedure, the stored procedure body may contain multiple SQL statements, each ending with a semicolon, in this case, when the server processing program encounters the first semicolon, it will think that the program is over. This is definitely not feasible. Therefore, use the DELIMITER command to change the end mark of the MySQL statement to another symbol. DELIMITER syntax format: DELIMITER $ Description: $ it is a user-defined Terminator. Generally, this symbol can be a special symbol, such as two "#", A "$", number, and letter can all be used. When using the DELIMITER command, do not use the backslash ("\") because it is an escape character of MySQL. For example, to create a stored procedure, you can delete the information of a specific student. DELIMITER $ create procedure DELETE_STUDENT (in xh char (6) begin delete from xs where student ID = XH; END $ DELIMITER; Description: When this stored PROCEDURE is called, mySQL deletes the data in the XS table based on the value of the provided XH parameter. The stored procedure body is specified between the keyword BEGIN and END. Of course, the BEGIN-END composite statement can also be nested. 4. Local variables can be declared during the stored procedure, and they can be used to store temporary results. To declare a local variable, you must use the declare statement. You can also assign an initial value to a local variable while declaring it. DECLARE syntax format: DECLARE var_name [,...] type [DEFAULT value] Description: var_name is the variable name; type is the variable type; default clause specifies a DEFAULT value for the variable, if not specified, the default value is NULL. Multiple local variables of the same type can be declared at the same time, separated by commas. For example, declare an integer variable and two character variables. DECLARE num INT (4); DECLARE str1, str2 VARCHAR (6); declare n char (10) default 'abcdef'; Note: local variables can only be in BEGIN... END statement block. The local variable must be declared at the beginning of the stored procedure. After the declaration, you can declare its BEGIN... This variable is used in the END statement block. It cannot be used in other statement blocks. Www.2cto.com can also declare user variables in the stored procedure, but do not confuse these two variables. The difference between a local variable and a user variable is that the @ symbol is not used before a local variable... The END statement block disappears after processing, and user variables exist throughout the session. 5. If you want to assign values to local variables using the SET statement, you can use the SET Statement, which is also part of the SQL statement. Syntax format: SET var_name = expr [, var_name = expr]... example: assign values to local variables during storage. SET num = 1, str1 = 'hello'; Note: unlike when declaring a user variable, there is no @ symbol before the variable name. Note: The statement in this example cannot be executed independently and can only be used in Stored Procedures and stored functions. 6. SELECT... the INTO Statement (important) uses this SELECT... The INTO syntax can store the selected column values directly to variables. Therefore, only one row can be returned. Syntax format: SELECT col_name [,...] INTO var_name [,...] table_expr Description: col_name is the column name, And var_name is the variable name to be assigned a value. Table_expr is the FROM clause in the SELECT statement and the following part. It is not described here. For example, in the Stored Procedure body, assign the values of the Student name and professional name whose student ID is 081101 In the XS table to the variable name and project respectively. SELECT name, professional name INTO name, project FROMXS; WHERE student ID = '000000'; 7. flow Control statements are in MySQL. common procedural SQL statements can be used in a stored procedure body. For example, IF statement, CASE statement, LOOP statement, WHILE statement, iterate statement, and LEAVE statement. (1) IF-THEN-ELSE statements can be used to perform different operations based on different conditions. Syntax format: IF judgment condition THEN one or more SQL statements [ELSEIF judgment condition THEN one or more SQL statements]... [ELSE one or more SQL statements] end if Description: when the condition is true, the corresponding SQL statement is executed. The IF statement is different from the system's built-in function IF (). The IF () function can only be used to determine two cases, so do not confuse them. For example, create a stored procedure for the XSCJ database to determine which of the two input parameters is larger. DELIMITER $ www.2cto.com create procedure xscj. COMPAR (IN K1INTEGER, IN K2 INTEGER, OUT K3 CHAR (6) BEGIN IFK1> K2 then set K3 = 'greater than '; ELSEIFK1 = K2 then set K3 = 'equal '; else set K3 = 'smaller than '; ENDIF; END $ DELIMITER; note: In the stored procedure, K1 and K2 are input parameters, and K3 are output parameters. (2) The CASE statement has been introduced before. Here we introduce the usage of the CASE statement in the stored procedure, which is slightly different from the previous one. Syntax format: CASE case_value WHEN when_value THEN statement_list [WHEN when_value THEN statement_list]... [ELSE statement_list] end case or: case when search_condition THEN statement_list [WHEN search_condition THEN statement_list]... [ELSE statement_list] www.2cto.com end case Description: a case statement can often act as an IF-THEN-ELSE statement. In the first format, case_value is the value or expression to be judged, followed by a series of WHEN-THEN blocks. The when_value parameter of each block specifies the value to be compared with case_value. If it is true, execute the SQL statement in statement_list. If each block does not match, the statement specified by the ELSE block is executed. The CASE statement ends with end case. In the second format, there is no parameter after the CASE keyword. In the WHEN-THEN block, search_condition specifies a comparison expression. WHEN the expression is true, the statement after THEN is executed. Compared with the first format, this format enables more complex condition judgment and is easier to use. For example, create a stored procedure and return different results for different parameters. DELIMITER $ create procedure xscj. RESULT (IN str VARCHAR (4), OUT sex VARCHAR (4) begin case str WHEN 'M' then set sex = 'male '; WHEN 'F' then set sex = 'femal'; else set sex = 'none'; ENDCASE; END $ DELIMITER; example: use the CASE statement in the second format to create the above stored procedure. The program snippet is as follows: CASE WHENstr = 'M' then set sex = 'male'; WHENstr = 'F' then set sex = 'female '; else set sex = 'none '; end case; (3) loop statement MySQL supports three statements used to create a loop: while, repeat, and loop statements. You can define 0, 1, or more loop statements in a stored procedure. ● WHILE statement syntax format: [begin_label:] WHILE search_condition DO statement_list www.2cto.com end while [end_label] Description: The statement first checks whether search_condition is true. If not, the statement in statement_list is executed, then judge again. If it is true, the loop continues. If it is not true, the loop ends. Begin_label and end_label are the labels of the WHILE statement. End_label cannot be provided unless begin_label exists. If both of them appear, their names must be the same. For example, create a stored procedure with a WHILE loop. DELIMITER $ create procedure dowhile () begin declare v1 INT DEFAULT5; WHILE v1> 0 do set v1 = v1-1; end while; END $ DELIMITER; ● repeat statement format is as follows: [begin_label:] REPEAT statement_list UNTIL search_condition end repeat [end_label] Description: The REPEAT statement first executes the statement in statement_list, and then judges whether search_condition is true. If it is true, the loop is stopped. REPEAT can also be labeled. Example: Use the REPEAT statement to create a stored procedure, such as example 7.9. The program fragment is as follows: REPEAT v1 = v1-1; UNTIL v1 <1; end repeat; Note: The difference between the REPEAT statement and the WHILE statement is that the REPEAT statement first executes the statement and then judges; the WHILE statement is executed only when the condition is true. ● LOOP statement syntax format: [begin_label:] LOOP www.2cto.com statement_list end loop [end_label] Description: LOOP allows repeated execution of a specific statement or statement group to implement a simple LOOP structure, statement_list is a statement that needs to be executed repeatedly. The statements in the loop are repeated until the loop is exited. When exiting, a LEAVE statement is always followed. The LEAVE statement is often used together with BEGIN... END or loop. The structure is as follows: LEAVE label; label is the name marked in the statement. This name is customized. The LEAVE keyword can be used to exit the labeled loop statement. For example, create a stored procedure with LOOP statements. DELIMITER $ create procedure doloop () begin set @ a = 10; Label: loop set @ a = @ A-1; IF @ a <0 THEN LEAVELabel; end if; END LOOPLabel; END $ DELIMITER; there is an iterate statement in the LOOP statement, which can only appear in the LOOP, REPEAT, and WHILE statements, meaning "LOOP again ". Its format is: ITERATE label description: The statement format is similar to that of LEAVE. The difference is that LEAVE statements exit a loop, while ITERATE statements start a loop again. 8. We call this stored procedure to view the final result. CALL doloop () to CALL the stored procedure, run the following command: CALL doloop (); then, view the user variable value: SELECT @ a; Syntax format: CALL sp_name ([parameter [,...]) note: sp_name is the name of the stored procedure. To call the stored procedure of a specific database, you must add the name of the database. Parameter is the parameter used to call the stored procedure. The number of parameters in this statement must always be equal to the number of parameters in the stored procedure. Example of www.2cto.com: Create a stored procedure with two input parameters: XH and KCM. If the score of a student in a certain course is less than 60, the credits are changed to zero, if the value is greater than or equal to 60 points, the credits are changed to the credits of this course. DELIMITER $ create procedure xscj. DO_UPDATE (in xhchar (6), in kcm char (16) begin declare kch char (3); declare xf tinyint; declare cj tinyint; SELECT course No., credits into kch, xffrom kc where Course name = KCM; SELECT score into cj from XS_KC WHERE student ID = xh and course number = KCH; if cj <60 then update XS_KC SET credits = 0 WHERE student ID = xh and course number = KCH; else update XS_KC SET credits = xf where student ID = xh and course number = KCH; end if; END $ DELIMITER; 9. when a stored procedure needs to be deleted after it is created Use the drop procedure statement. Before that, you must confirm that the stored procedure does not have any dependency; otherwise, other associated stored procedures may not run. Syntax format: DROPPROCEDURE [if exists] sp_name Description: sp_name is the name of the stored procedure to be deleted. The if exists clause is an extension of MySQL. IF a program or function does not exist, it prevents errors. For example, delete the Stored PROCEDURE dowhile: drop procedure if exists dowhile; 10. You can use the alter procedure statement to modify some features of the stored PROCEDURE. Syntax format: alter procedure sp_name [characteristic...] here, characteristic is: www.2cto.com {contains SQL | no SQL | reads sqldata | modifies SQL data} | SQL SECURITY {DEFINER | INVOKER} | COMMENT 'string' description: characteristic is a feature of stored PROCEDURE creation. It has been introduced in the create procedure statement. As long as the values are set, the features of the stored procedure change accordingly. If you want to modify the content of a stored procedure, you can delete it first and then redefine the stored procedure. Example: Use the delete method to modify the stored procedure in example 7.12. DELIMITER $ drop procedure if exists DO_QUERY; create procedure DO_QUERY () begin select * from xs; END $ DELIMITER; *** 11 .. Not important !! 11. An error message in an SQL statement may be returned when an SQL statement is processed during storage. For example, if a new row is inserted into a table and the primary key value already exists, this INSERT statement will cause an error message and MySQL immediately stops processing the stored procedure. Each error message has a unique code and a SQLSTATE code. For example, SQLSTATE 23000 belongs to the following Error code: Error 1022, "Can't write; duplicate (repeated) key intable" Error 1048, "Column cannot benull" Error 1052, "Column is ambiguous (ambiguous)" Error 1062, "Duplicate entry forkey" the "Error messages and code" chapter in the MySQL Manual lists all Error messages and their respective codes. To prevent MySQL from stopping processing when an error message is generated, use the DECLAREhandler statement. This statement declares a so-called handler for the error code, which specifies what will happen if an error message is generated when an SQL statement is processed. Declare handler syntax format: DECLARE handler_type handler for condition_value [,...] sp_statement where handler_type is: Continue | EXIT | UNDO condition_value is: SQLstate [VALUE] sqlstate_value www.2cto.com | condition_name | SQLwarning | not found | SQLexception | Description: ● handler_type: there are three main types of processing programs: CONTINUE, EXIT, and UNDO. For the CONTINUE processing program, MySQL does not interrupt the processing of the stored procedure. For the EXIT handler, the execution of the current BEGIN... END compound statement is terminated. UNDO handler type statements are not supported yet. ● Condition_value: indicates the SQLSTATE code. Condition_name is the name of the processing condition. SQLWARNING is a shorthand for all SQLSTATE codes starting with 01. Not found is a shorthand for all SQLSTATE codes starting with 02. SQLEXCEPTION is a shorthand for all SQLSTATE code NOT captured by SQLWARNING or not found. When you do not want to define a processing program for each possible error message, you can use the above three forms. Mysql_error_code is the specific SQLSTATE code. In addition to the SQLSTATE value, the MySQL ERROR code is also supported, in the form of: ERROR = 'xxxx '. ● Sp_statement: the action to be executed when the processing program is activated. For example, create a stored procedure and insert a row of data ('2015010', 'wangmin', 'computer ', 1, '2017-02-10', 50, NULL, NULL). It is known that student ID 081101 already exists in the XS table. If an error occurs, the program continues. Use xscj; DELIMITER $ create procedure MY_INSERT () begin declarecontinue handler for sqlstate '000000' SET @ x2 = 1; SET @ x = 2; insertinto xs values ('000000 ', 'wang min', 'computer ', 1, '1970-02-10', 50, NULL, NULL); SET @ x = 3; www.2cto.com END $ DELIMITER; description: after the stored procedure is called, the processing program is not activated when no error message is encountered. When an error message occurs when an INSERT statement is executed, MySQL checks whether the processing program is defined for this error code. If yes, activate the processing program. In this example, the error message caused by the INSERT statement is exactly one of the SQLSTATE codes. Next, execute the additional statement of the Processing Program (SET @ x2 = 1 ). After that, MySQL checks the type of the processing program, where the type is CONTINUE. Therefore, the stored procedure continues to process and assigns the user variable x to 3. If the INSERT statement can be executed here, the handler will not be activated, and the user variable x2 will not be assigned a value. Note: you cannot define two or more handlers for the same error message in the same BEGIN-END statement block. To improve readability, you can use the declare condition Statement to define a name for a SQLSTATE or error code and use this name in the handler. Declare condition syntax format: DECLARE condition_name CONDITION FORcondition_value where condition_value: SQLSTATE [VALUE] sqlstate_value | mysql_error_code Description: condition_name is the name of the processing CONDITION, condition_value is the SQLSTATE or error code to define the alias. Example: Modify the stored procedure in the previous example, define SQLSTATE '20160301' as NON_UNIQUE, and use this name in the handler. The program snippet is: begin declare NON_UNIQUE condition for sqlstate '200'; declare continue handler for NON_UNIQUE SET @ x2 = 1; SET @ x = 2; insert into xs values ('20140901 ', 'wang min', 'computer ', 1, '1970-02-10', 50, NULL, NULL); SET @ x = 3; www.2cto.com END; 12. cursors a SELECT... the INTO statement returns a row with a value, which reads data to the stored procedure. However, a conventional SELECT statement returns multiple rows of data. to process the data, you need to introduce the cursor concept. MySQL supports Simple cursors. In MySQL, the cursor must be used in the stored procedure or function, and cannot be used separately in the query. Four special statements are required to use a CURSOR: declare cursor (declared CURSOR), open cursor (open cursor), fetch cursor (read CURSOR), and close cursor (close cursor ). If the declare cursor statement is used to DECLARE a CURSOR, it is connected to a result set returned by the SELECT statement. OPEN the cursor using the open corsor statement. Then, you can use the fetch cursor statement to read the generated results one row by one to the stored procedure or stored function. A cursor is equivalent to a pointer pointing to the current row of data. You can use the fetch corsor statement to move the cursor to the next row. When all rows are processed, use the CLOSECURSOR statement to close the cursor. (1) declared cursor syntax format: DECLAREcursor_name cursor for select_statement Description: cursor_name is the name of the cursor and uses the same rules as the table name. Select_statement is a SELECT statement that returns data of one or more rows. This statement declares a cursor. You can also define multiple cursors In the stored procedure, but each cursor in a block must have a unique name. Note: The SELECT clause cannot have an INTO clause. The following definition complies with a CURSOR statement: DECLARE XS_CUR1 cursor for select student ID, name, gender, birth date, total credits from xs where professional name = 'compute'; note: the cursor can only be used in stored procedures or stored functions. In this example, the statements cannot be run independently. (2) After opening the cursor declaration cursor, to use the cursor to extract data from it, you must first open the cursor. In MySQL, use an OPEN statement to OPEN a cursor. The format is: OPEN cursor_name. In a program, a cursor can be opened multiple times. Because other users or programs have already updated the table, therefore, the result may be different each time you open it. Www.2cto.com (3) read data after the cursor is opened, you can use fetch... The into statement reads data from it. Syntax format: FETCH cursor_nameINTO var_name [, var_name]... description: FETCH... INTO statement and SELECT... the INTO statement has the same meaning. The FETCH statement assigns a row of data pointed to by the cursor to some variables. The number of variables in the clause must be equal to the number of columns in the SELECT clause when the cursor is declared. Var_name is the name of the variable that stores data. (4) Close the cursor in time after use. Use the CLOSE statement to CLOSE the cursor. The format is: the meaning of the parameters of the CLOSE cursor_name statement is the same as that of the OPEN statement. For example, CLOSE XS_CUR2 will CLOSE the cursor XS_CUR2. For example, create a stored procedure and calculate the number of rows in the XS table. DELIMITER $ create procedure compute (out numberinteger) begin declarexh char (6); declarefound boolean default true; DECLARENUMBER_XS cursor for select student from xs; declarecontinue handler for not found setfound = FALSE; SETNUMBER = 0; OPENNUMBER_XS; FETCHNUMBER_XS into xh; www.2cto.com whilefound do setnumber = NUMBER + 1; FETCHNUMBER_XS into xh; ENDWHILE; CLOSENUMBER_XS; END $ DELIMITER;

Author tianyazaiheruan