A stored procedure (Stored Procedure) is a set of SQL statements that are compiled and stored in a database inorder to accomplish a specific function. The user invokes execution by specifying the name of the stored procedure and the given parameter (if the stored procedure has parameters). The MySQL stored procedure is a new feature that has been added since MySQL 5.0 . Greatly improve the database processing speed, but also can improve the flexibility of database programming.
Process: Encapsulates several of the statements that are invoked when these wrappers are executed. Storing this procedure in a database is a stored procedure .
Function: is a "procedure" with a return value.
Procedure: A function that has no return value.
Advantages of stored procedures: 1.stored procedures enhance theSQLlanguage functionality and flexibility (SQL programming, variables, expressions, control structures)2.stored procedures can achieve a faster execution speed. (stored procedures are much faster than batch execution,because the stored procedure is precompiled. When you run a stored procedure for the first time, the optimizer optimizes it for analysis and gives the execution plan that is ultimately stored in the system table. While batch-processingTransaction-sqlstatements are compiled and optimized each time they are run, with relatively slower speeds. )3.stored procedures can reduce network traffic. actions for the same database object (such as queries, modifications), if this operation involves aTransaction-sqlstatement is an organized stored procedure, when the stored procedure is called on a client computer, only the calling statement is transmitted in the network, which greatly increases network traffic and reduces network load. 4.stored procedures can be used as a security mechanism to make full use of them. The system administrator restricts the access to the corresponding data by executing the permission of a stored procedure, avoids the unauthorized user's access to the data, and ensures the security of the data. 5. Stored procedures allow standard components to be programmed. After a stored procedure is created, it can be called multiple times in the program without having to rewrite the SQL statement for the stored procedure . and database professionals can modify stored procedures at any time, without affecting the source code of the application. creation of stored procedures:
DELIMITER $$ //define delimiter create PROCEDURE p () //p for procedure name begin --sql statement//Encapsulate statement body End $$
view existing stored procedures: Show procedure status \g;Calling stored procedure: Call stored procedure name ();Delete Stored procedure: Drop procedure stored procedure name;
Variable declaration: Declaring a variable with declare in a stored procedureformat:declare variable name variable type [default value]in stored procedures, variables can be legitimately operated in SQL statements, such as +-*/How the result of an operation assigns a set variable : = expressionstored procedure arguments: in parentheses in the stored procedure, you can declare parameters . The syntax is CREATE PROCEDURE P ([in/out/inout] Parameter Name argument type:)inch outinout detailed analysis see next blog post (link afternoon release)
instance One: Create an operation assignment
CREATE PROCEDURE p1 () BEGIN DECLARE age INT DEFAULT; DECLARE height INT DEFAULT; SET Age: = Age +20; SELECT CONCAT (' Age is ', ages, ' height is ', height '); end$$
Example two: If/else control structure
CREATE PROCEDURE p2 () BEGIN DECLARE age INT DEFAULT; DECLARE height INT DEFAULT; IF age >=, then select "Adult"; ELSE Select "Minor"; END IF; end$$
Example three: WHILE/DO control structure for 1-100 and
CREATE PROCEDURE P3 () BEGIN DECLARE total INT DEFAULT 0; DECLARE num INT DEFAULT 0; While num<=100 does SET total: = num + total; SET num: = num + 1; END while; SELECT Total; end$$
Example four: Case control structure
CREATE PROCEDURE P4 () BEGIN DECLARE num INT DEFAULT 0; SET num: = Floor (4*rand ()); Case NUM When 1 then select "Cat"; When 2 then select "Dog"; When 3 then select "Sheep"; ELSE select "Pig"; END case; end$$
Example five: Repeat cycle
CREATE PROCEDURE P5 () BEGIN DECLARE num INT DEFAULT 0; DECLARE Total INT DEFAULT 0; REPEAT SET total = num + total; SET num: = num + 1; UNTIL num>100 END REPEAT; SELECT Total; end$$
First knowledge of MySQL stored procedures
