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

This article mainly explains "how to understand mysql stored procedures". The content of the explanation is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn how to understand mysql stored procedures.

1. Brief introduction of stored procedures:

Our commonly used database language SQL statements need to be compiled and then executed, and a stored procedure (Stored Procedure) is a set of SQL statements that are compiled and stored in the database in order to complete a specific function. The user calls and executes the stored procedure by specifying the name of the stored procedure and giving parameters (if the stored procedure has parameters).

A stored procedure is a programmable function that is created and saved in the database. It can consist of SQL statements and some special control structures. Stored procedures are useful when you want to execute the same function on different applications or platforms, or to encapsulate specific functions. The stored procedure in the database can be regarded as a simulation of the object-oriented method in programming. It allows you to control how data is accessed.

2. Some points of the stored procedure:

(1)。 Stored procedures enhance the functionality and flexibility of the SQL language. The stored procedure can be written with flow control statements, which has strong flexibility and can complete complex judgments and more complex operations.

(2)。 Stored procedures allow standard components to be programming. After a stored procedure is created, it can be called multiple times in the program without having to rewrite the SQL statement of the stored procedure. And database professionals can modify the stored procedure at any time, which has no effect on the application source code.

(3)。 Stored procedures can achieve faster execution speed. If an operation contains a large amount of Transaction-SQL code or is executed multiple times separately, the stored procedure executes much faster than batch processing. Because the stored procedure is precompiled. When a stored procedure is run for the first time, the optimizer analyzes and optimizes it, and gives the execution plan that is eventually stored in the system table. Batch Transaction-SQL statements are compiled and optimized each time they are run, which is relatively slow.

(4)。 Stored procedures can reduce network traffic. For the operation of the same database object (such as query, modification), if the Transaction-SQL statement involved in this operation is organized into a stored procedure, then when the stored procedure is called on the client computer, only the calling statement is transmitted in the network, thus greatly increasing the network traffic and reducing the network load.

(5)。 Stored procedures can be fully utilized as a security mechanism. By restricting the permissions of a stored procedure, the system administrator can restrict the access to the corresponding data, avoid the access of unauthorized users to the data, and ensure the security of the data.

3. Stored procedures for mysql:

Stored procedure is an important function of database storage, but MySQL did not support stored procedure before 5. 0, which greatly reduced the application of MySQL. Fortunately, MySQL 5.0 has finally begun to support stored procedures, which can not only greatly improve the processing speed of the database, but also improve the flexibility of database programming.

At the same time, to create subroutines above mysql5.1, you must have CREATE ROUTINE permissions, and ALTER ROUTINE and EXECUTE permissions are automatically granted to its creators

4. The creation of stored procedures:

(1) grammar:

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

Proc_parameter specifies 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 both input and output, param_name represents the parameter name, and type indicates the type of parameter. This type can be any type in the MYSQL database with the following values:

Characteristic:

LANGUAGE SQL

| | [NOT] DETERMINISTIC |

| | {CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA} |

| | SQL SECURITY {DEFINER | INVOKER} |

| | COMMENT 'string' |

Routine_body:

Valid SQL procedure statement or statements

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

[NOT] DETERMINISTIC: indicates whether the result of the stored procedure execution is correct. DETERMINISTIC indicates that the result is certain. Each time the stored procedure is executed, the same input gets the same output. [NOT] DETERMINISTIC indicates that the result is uncertain, and the same input may get different output. If no value is specified, it defaults to [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 the subroutine contains SQL statements, but does not contain statements that read and write data

NO SQL indicates that the subroutine does not contain SQL statements

READS SQL DATA: statements that indicate that subroutines contain read data

MODIFIES SQL DATA indicates that the subroutine contains statements to write data.

By default, the system is specified as CONTAINS 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. By default, the system is designated as DEFINER

COMMENT 'string': comment information that 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 SQL code.

(2) format

Format of MySQL stored procedure creation: CREATE PROCEDURE procedure name ([procedure parameters [,...]])

[features.] Process body

EG:

DELIMITER / /

DROP PROCEDURE IF EXISTS simpleproc

CREATE PROCEDURE simpleproc (OUT param1 INT)

BEGIN

SELECT COUNT (*) INTO param1 FROM t_user

END / /

DELIMITER

I am tapping on Navicat for MySQL, so I am not going to enter the mysql client to tap. Personally, I think this is more convenient and faster; if I am familiar with linux children's shoes, I probably don't think so.

All right, no more nonsense, first explain: (1) the purpose of DELIMITER is to tell mysql that the closing symbol of my stored procedure is / /, so it is used in your stored procedure; it will not be considered a closing symbol. Note: when using the delimiter command, you should avoid using the backslash ('\') character, because that is the escape character of MySQL (2) drop procedure if exists has the same meaning as the statement when we created the table, and delete it if this stored procedure exists (3) the meaning of create procedure simpleproc (out param1 int) is also very clear, that is, to create this process with an output parameter, Note: there can be return statements in function, but procedure is not available, but it is also possible to pass the results back, as above; (4) begin. Wrapped in the middle of end is the main program of procedure, that is, the main part; in short, just leave the sql that you want to work together here; (5) the statement DELIMITER; as the closing statement. The purpose is to tell mysql that I don't want to use / / as the closing character now, but I want to change it back; note: this is not only but / /, but also other symbols.

5. Variables

DECLARE statements are used to localize different projects to a subroutine: local variables, conditions and handlers, and cursors; DECLARE is only used in BEGIN. In the END compound statement, and must be at the beginning of the compound statement, before any other statement. The cursor must be declared before the handler is declared, and variables and conditions must be declared before the cursor or handler is declared.

Declare declares a local variable: DECLARE var_name [,...] Type [DEFAULT value]; this statement is used to declare local variables. To provide a default value for a variable, include a DEFAULT clause. Value can be specified as an expression and does not need to be a constant. If there is no DEFAULT clause, the initial value is NULL.

The set 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. The SET statement in the stored program is implemented as part of the pre-existing SET syntax. This allows SET aquix, Bevery,... Such an extended syntax. Different types of variables (locally declared variables and global and collective variables) can be mixed. This also allows you to combine local variables with options that are meaningful only to system variables. In that case, this option is recognized but ignored.

6. Define the handler

Sometimes a program can go wrong, but if you want your program to continue to execute in the event of an error, declare can help us solve this problem; it is defined as follows: specific conditions require specific processing. These conditions can be linked to errors, as well as general process control in subprograms. 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 can enhance the ability of the stored program to deal with problems and prevent the program from stopping running abnormally.

1. Define conditions

DECLARE condition_name CONDITION FOR[condition _ type]

[condition_type]:

SQLSTATE [VALUE] sqlstate_value | mysql_error_code

Condition_name: indicates the condition name

Condition_type: represents the type of condition

Both sqlstate_value and mysql_error_code can represent mysql errors

A string error code with a length of 5 sqlstate_value

Mysql_error_code is a numeric type error code. For example, in ERROR1142 (42000), the value of sqlstate_value is 42000.

The value of mysql_error_code is 1142

/ / method 1: use sqlstate_valueDECLARE command_not_allowed CONDITION FOR SQLSTATE '42000 swap / method 2: use mysql_error_codeDECLARE command_not_allowed CONDITION FOR SQLSTATE 1148

This statement specifies that special processing conditions are required. He associates a name with the specified error condition.

This name is then used in the DECLARE HANDLER statement that defines the handler

2. Define the handler

DECLARE handler_type HANDLER FOR condition_value [,...] Sp_statement

Handler_type:

| CONTINUE: for a CONTINUE processor, the execution of the previous subroutine continues after the handler statement is executed.

| EXIT: for EXIT processors, the execution of the current BEGIN...END compound statement is terminated.

| UNDO: UNDO handler type statement is not supported yet.

Condition_value:

SQLSTATE [VALUE] sqlstate_value

| | condition_name |

| | SQLWARNING:SQLWARNING is a shorthand for all SQLSTATE codes starting with 01 |

| | NOT FOUND:NOT FOUND is a shorthand for all SQLSTATE codes starting with 02 |

| | SQLEXCEPTION:SQLEXCEPTION is a shorthand of all SQLSTATE codes that are not captured by SQLWARNING or NOT FOUND |

| | mysql_error_code |

This statement specifies each handler that can handle one or more conditions. If one or more conditions are generated, the specified statement is executed.

/ / method 1: capture sqlstate_value DECLARE CONTINUE HANDLER FOR SQLSTATE '42000'SET @ info='CAN NOT FIND'; / / method 2: capture mysql_error_code DECLARE CONTINUE HANDLER FOR 1148SET @ info='CAN NOT FIND'; / / method 3: define the condition first, and then call DECLARE can_not_find CONDITION FOR 1146; DECLARE CONTINUE HANDLER FOR can_not_find SET set @ info='CAN NOT FIND'; / / method 4: use SQLWARNING DECLARE EXIT HANDLER FOR SQLWARNING SET @ info='ERROR' / / method 5: use NOT FOUND DECLARE EXIT HANDLER FOR NOT FOUND SET @ info='CAN NOT FIND'; / / method 6: use SQLEXCEPTION DECLARE EXIT HANDLER FOR SQLEXCEPTION SET @ info='ERROR';eg:delimiter / / DROP PROCEDURE IF EXISTS sp2;CREATE PROCEDURE sp2 () BEGIN DECLARE CONTINUE HANDLER for SQLSTATE '23000' set @ info= 23000; SET @ info= 1; INSERT INTO test.t_user VALUES. SET @ info = 2; INSERT INTO test.t_user VALUES (1); SET @ info = 3 info end / / delimiter;-- execute the following sp2call sp2 ();-- query the following @ info SELECT @ info;-- result analysis: if there is no error in the program, according to the execution purpose, the result @ info = 3 If '23000' is caught, the result is @ 23000; if continue is used as the execution handler_type, the exception is caught and execution continues So the result is 3; if you use exit as the execution handler_type, the exception is caught and ends directly, so the result is 23000

7. Cursor

Simple cursors are supported within stored programs and functions. The syntax is like being in an embedded SQL. The cursor is currently insensitive, read-only and non-scrolling. Insensitive means that the server can live and cannot copy its result table. The cursor must be declared before the handler is declared, and variables and conditions must be declared before the cursor or handler is declared.

1. Declaration cursor

DECLARE cursor_name CURSOR FOR select_statement

This statement declares a cursor. You can also define multiple cursors in a subroutine, but each cursor in a block must have a unique name. A SELECT statement cannot have an INTO clause.

2. Open the cursor

The statement OPEN cursor_name opens the previously declared cursor.

3. Use the cursor FETCH

FETCH cursor_name INTO var_name [, var_name]... This statement reads the next line (if any) with the specified open cursor and advances the cursor pointer

4. Close the cursor

The statement CLOSE cursor_name closes the cursor that was previously opened. If not explicitly closed, the cursor is closed at the end of the compound statement in which it is declared.

2. Process control structure

1.IF statement

The IF statement is used to determine the condition. Different statements are executed depending on whether the condition is met. The basic form of its grammar is as follows:

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

Among them, the search_condition parameter represents the conditional judgment statement; the statement_list parameter represents the execution statement with different conditions.

Note: MYSQL also has an IF () function, which is different from the IF statement described here.

The following is an example of an IF statement. The code is as follows:

Delimiter / / DROP PROCEDURE IF EXISTS sp3;CREATE PROCEDURE sp3 () BEGIN DECLARE age int DEFAULT 19; set @ age1= 0; SET @ age2= 0; set @ age3= 0; IF age > 20 THEN SET @ age1=age; ELSEIF age=20 THEN SET @ age2=age; ELSE SET @ age3=-1; END IF; END / / delimiter; call sp3 ()

This example executes different SET statements based on the size relationship between age and 20.

If the age value is greater than 20, add 1 to the count1 value; if the age value is equal to 20, add 1 to the count2 value

In other cases, add 1 to the value of count3. All IF statements need to end with END IF.

2.CASE statement

CASE statements are also used for conditional judgment, which can achieve more complex conditional judgments than IF statements. The basic form of the CASE statement is as follows:

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

Where the case_value parameter represents the variable of conditional judgment.

The when_value parameter represents the value of the variable

The statement_list parameter represents execution statements with different when_ value values.

There is another form of CASE statement. The syntax of this form is as follows:

CASE WHEN search_condition THEN statement_list [WHEN search_condition THEN statement_list]... [ELSE statement_list] END CASE

Where the search_condition parameter represents the conditional judgment statement

The statement_list parameter represents the execution statement under different conditions.

The following is an example of a CASE statement. The code is as follows:

Delimiter / / DROP PROCEDURE IF EXISTS sp4;CREATE PROCEDURE sp4 () BEGIN DECLARE age int DEFAULT 19; set @ age1 = 0; CASE age WHEN age > 20 THEN SET @ age1 = age; WHEN age = 20 THEN SET @ age1 = age-1; ELSE SET @ age1 =-1; END CASE;END / / delimiter; call sp4 (); select @ age1

Note: the CASE statement here is slightly different from the CASE statement of the SQL CASE expression described in the "Control flow function". The CASE statement here cannot have an ELSE NULL clause

And use END CASE instead of END to terminate!

3.LOOP statement

The LOOP statement can cause some specific statements to be executed repeatedly, implementing a simple loop.

But the LOOP statement itself does not have a statement to stop the loop, you must encounter a level statement and so on to stop the loop.

The basic syntax of the LOOP statement is as follows:

[begin_label:] LOOP statement_list END LOOP [end_label]

Where the begin_label parameter and the end_label parameter represent the start and end flags of the loop, respectively, which must be the same and can be omitted; (example is written in conjunction with leave)

4.LEAVE statement

The LEAVE statement is mainly used to jump out of loop control. Its grammatical form is as follows:

LEAVE label

Where the label parameter represents the flag of the loop.

The following is an example of a LEAVE statement. The code is as follows:

Delimiter / / DROP PROCEDURE IF EXISTS sp5;CREATE PROCEDURE sp5 () BEGIN set @ age = 0; add_age:LOOP SET @ age = @ age + 1; if @ age > 1000 THEN LEAVE add_age; end IF; END LOOP add_age;END / / delimiter;-- call call sp5 ();-- View the result select @ age

The example loop performs the operation of count plus 1. When the value of count is equal to 100, the LEAVE statement jumps out of the loop.

5.ITERATE statement

The ITERATE statement is also used to jump out of the loop. However, the ITERATE statement is to jump out of this loop and go straight to the next loop.

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

The basic syntax of the ITERATE statement is as follows:

ITERATE label

Where the label parameter represents the flag of the loop.

The following is an example of an ITERATE statement. The code is as follows:

Delimiter / / DROP PROCEDURE IF EXISTS sp6;CREATE PROCEDURE sp6 () BEGIN DECLARE age int DEFAULT 0; DECLARE EXIT HANDLER FOR SQLSTATE '23000' SET age = 23000; add_fun:LOOP SET age = age + 1; IF age > 10 THEN INSERT into t_user value (1,' lennon', 'lennon',' lennon'); ELSEIF age > 5 & & age

< 10 THEN ITERATE add_fun; ELSE select age; END IF; END LOOP add_fun;END //delimiter ;call sp6(); 说明：LEAVE语句和ITERATE语句都用来跳出循环语句，但两者的功能是不一样的。 LEAVE语句是跳出整个循环，然后执行循环后面的程序。而ITERATE语句是跳出本次循环，然后进入下一次循环。 使用这两个语句时一定要区分清楚。 6．REPEAT语句 REPEAT语句是有条件控制的循环语句。当满足特定条件时，就会跳出循环语句。REPEAT语句的基本语法形式如下： [begin_label:] REPEAT statement_list UNTIL search_condition END REPEAT [end_label] 其中，statement_list参数表示循环的执行语句；search_condition参数表示结束循环的条件，满足该条件时循环结束。 下面是一个REPEAT语句的示例。代码如下： delimiter //DROP PROCEDURE IF EXISTS sp7;CREATE PROCEDURE sp7()BEGIN set @age = 0; add_count:REPEAT set @age = @age + 1 ; UNTIL @age>

100 END REPEAT add_count;END / / delimiter; call sp7 (); select @ age

The example loop performs the operation of count plus 1, ending the loop when the count value is 100.

The REPEAT loop ends with END REPEAT.

7.WHILE statement

WHILE statements are also conditional loop statements. But the WHILE statement is different from the REPEAT statement.

A WHILE statement is a statement that executes within a loop when a condition is met.

The basic syntax of the WHILE statement is as follows:

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

Where the parameter search_condition represents the condition under which the loop executes, and when the condition is satisfied, the loop executes

The statement_list parameter represents the execution statement of the loop.

The following is an example of an ITERATE statement. The code is as follows:

Delimiter / / DROP PROCEDURE IF EXISTS sp8;CREATE PROCEDURE sp8 () BEGIN set @ age = 0; WHILE @ age < 100 DO set @ age = @ age + 1; END WHILE;END / / delimiter; call sp8 (); select @ age

The example loop performs the operation of count plus 1, and executes the loop when the count value is less than 100.

If the count value equals 100, then jump out of the loop. The WHILE loop needs to end with END WHILE.

8. View stored procedures

SHOW {PROCEDURE | FUNCTION} STATUS [LIKE 'pattern']; SHOW CREATE {PROCEDURE | FUNCTION} sp_name; SELECT * FROM information_schema.Routines WHERE ROUTINE_NAME=' sp_name'

Eg:

Show PROCEDURE STATUS like 'sp6';show CREATE PROCEDURE sp6;SELECT * FROM information_schema.Routines WHERE ROUTINE_NAME='sp6'; thank you for reading, the above is the content of "how to understand mysql stored procedures". After the study of this article, I believe you have a deeper understanding of how to understand mysql stored procedures, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

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.