How to get started with the new MySQL database features stored procedures Tutorial

How to get started with the new MySQL database features stored procedures Tutorial

In MySQL 5, the new feature of stored procedures is finally introduced, which will greatly enhance the database processing capability of MySQL. 　　In this article, you will be instructed to quickly master the basics of MySQL 5 stored procedures and lead users to get started. Stored procedure describes a stored procedure is a set of SQL statements that are compiled and stored in a database in order to accomplish a specific function. The user executes it by specifying the name of the stored procedure and giving the parameter (if the stored procedure has parameters). Stored procedures can be executed by the application through a call, and the user is allowed to declare variables. 　　At the same time, a stored procedure can receive and output parameters, return a state value that executes a stored procedure, or nest calls. The advantages of stored procedures as stored procedures have the following advantages: (1) Reduce network traffic. 　　Calling a stored procedure with a low number of rows may not be very different from the network traffic that calls the SQL statement directly, but if the stored procedure contains hundreds of rows of SQL statements, the performance is definitely much higher than a single call to the SQL statement. (2) Execution speed is faster. When the stored procedure is created, the database has been parsed and optimized once. 　　Second, once the stored procedure is executed, a copy of the stored procedure is kept in memory so that the next time the same stored procedure is executed, it can be read directly from memory. (3) Stronger security. 　　Stored procedures provide access to specific data and improve code security, such as preventing SQL injection, by granting permissions to users, rather than table-based. 　　(4) Business logic can be encapsulated in stored procedures, so that not only easy to maintain, but also the implementation of high efficiency of course there are also some shortcomings of the stored procedures, such as: (1) Portability: When migrating from one database to another, many of the stored procedures are written to be partially modified. 　　(2) The stored procedure takes some learning time to learn, such as learning its grammar. MySQL Query browswer (http://dev.mysql.com/doc/query-browser/en/) is the recommended tool for the development and management of stored procedures in MySQL. 　　Follow the steps below to learn about stored procedures in MySQL. 1, the definition of the stored procedure terminator in the stored procedure, usually to enter a lot of SQL statements, and the SQL statement to end each statement with a semicolon, so to tell the stored procedure, where is meant to end the entire stored procedure, so before we write the stored procedure, we define the delimiter, we define//as a delimiter, 　　We can define the terminator by using delimiter//Such syntax, and of course you can define other favorite symbols yourself. 2. How to create a stored procedure let's look at a simple example below, the code is as follows: DelimiteR//Createprocedure ' P2 ' () LANGUAGE SQL deterministic SQL SECURITY definer COMMENT ' A procedure ' BEGIN SELECT ' Hello worl D! '; 　　end//below explains the components of a stored procedure: 1) First, after defining the terminator, create a stored procedure using the creation Procedure+ stored procedure name method, the language option specifies the language used, and SQL is used by default. 　　2) The role of the deterministic keyword is to use this keyword when determining the input and output of each stored procedure is the same, otherwise the default is not deterministic. 3) The SQL security keyword, which indicates that the user's permissions are checked when invoked. 　　When the value is Invoker, it is checked when the user calls the stored procedure, which defaults to Definer, which is when the stored procedure is created. 　　4) The comment section is the comment Description section of the stored procedure. 　　5) In the Begin End section, is the body part of the stored procedure. 3. The method of calling the stored procedure calls the stored procedure in a simple way, using only the call command, followed by the name of the stored procedure to invoke and a list of the variables entered, such as: called Stored_procedure_name (param1, param2, ....) 　　Call Procedure1 (, ' string parameter ', @parameter_var); 4, modify and delete stored procedures can use ALTER's syntax to modify the main characteristics and parameters of the stored procedure, to modify the main part of the stored procedure, you must first delete and then rebuild. For example, modify the definition of stored procedure num_from_employee below. Change the Read and write permissions to modifies SQL DATA and indicate that the caller can execute them. 　　The code executes as follows: ALTER PROCEDURE num_from_employee modifies SQL DATA SQL SECURITY INVOKER; The syntax for deleting stored procedures is to use the drop keyword. 　　DROP PROCEDURE IF EXISTS p2 as follows; 5, the parameters of the stored procedure below to learn the parameters in the stored procedure, first look at the parameters in the stored procedure form, as follows: CREATE PROCEDURE Proc1 () in this stored procedureis an empty argument list CREATE PROCEDURE proc1 (in varname data-type) This stored procedure has an output parameter named VarName, followed by the data type Data-type,in parameter is the default, so you can omit not to write CR Eate PROCEDURE Proc1 (out varname data-type) This stored procedure varname as output parameter CREATE PROCEDURE proc1 (INOUT varname data-type) This stored procedure , VarName is both an input parameter and an output parameter below, the first is an example of an in input parameter, as follows: DELIMITER//CREATE PROCEDURE ' proc_in ' (in var1 INT) BEGIN   &NBSP ; SELECT var1 + 2 as result; end//output out parameters examples are as follows: DELIMITER//CREATE PROCEDURE ' proc_out ' (out var1 VARCHAR) BEGIN SET var1 = ' This is a test '; END//In-out Example: DELIMITER//CREATE PROCEDURE ' proc_inout ' (out var1 INT) BEGIN SET var1 = var1 * 2; END//6, how to define variables below to explain how to define variables in the MySQL 5 stored procedure. You must explicitly declare variables at the beginning of a stored procedure and indicate their data type, but once you declare a variable, you can use it in a stored procedure, and the syntax for defining the variable is as follows: DECLARE varname data-type DEFAULT defaultvalue illustration: DE CLARE A, b INT DEFAULT 5; DECLARE str VARCHAR (50); DECLARE today TIMESTAMP DEFAULT current_date; 　　DECLARE v1, v2, v3 TINYINT; Once a variable is defined, it can be assigned an initial value in the stored procedure, and various related operations such as: DELIMITER//CREATE PROCEDURE ' Var_proc ' (IN paramstr VARCHAR) BEGIN DECLARE A, b INT DEFAULT 5; 　　DECLARE str VARCHAR (50); 　　DECLARE today TIMESTAMP DEFAULT current_date; 　　DECLARE v1, v2, v3 TINYINT; 　　INSERT into Table1 VALUES (a); 　　SET str = ' I am a string '; SELECT CONCAT (STR,PARAMSTR), today from Table2 WHERE b>=5; END//7, MySQL stored procedure syntax structure MySQL stored procedures support If,case,iterate,leave loop,while and repeat syntax structures and statements, in this article, focus on if,case and while syntax, 　　Because they are the most widely used. The IF statement uses the syntax structure of If...then End If, as an example: DELIMITER//CREATE PROCEDURE ' proc_if ' (in param1 INT) BEGIN DECLARE variable 　　1 INT; 　　SET variable1 = param1 + 1; 　　IF variable1 = 0 then    select variable1; 　　END IF; 　　IF param1 = 0 Then    select ' Parameter value = 0 '; 　　ELSE    select ' Parameter value <= 0 '; END IF; END//Case statement when there are a lot of if statements, you should consider the use of the statement, which is a multi-branch SELECT statement, there are two ways: the first way: DELIMITER//CREATE PROCEDURE ' proc_case ' (in param1 　　int) BEGIN DECLARE variable1 int; 　　SET variable1 = param1 + 1; 　Case Variable1 If 0 then　    insert into table1 VALUES (param1); 　　When 1 then    insert into table1 VALUES (variable1); 　　ELSE    insert into table1 VALUES (99); END case; 　　END//Another notation: DELIMITER//CREATE PROCEDURE ' proc_case ' (in param1 int) BEGIN DECLARE variable1 int; 　　SET variable1 = param1 + 1; 　　case if Variable1 = 0 then    insert into table1 VALUES (param1); 　　When variable1 = 1 then    insert into table1 VALUES (variable1); 　　ELSE    insert into table1 VALUES (99); END case; The END//While statement is similar to the while statement in a normal programming language, as in the following example: DELIMITER//CREATE PROCEDURE ' Proc_while ' (in param1 INT) BEGIN DEC 　　Lare variable1, Variable2 INT; 　　SET variable1 = 0; 　　 While Variable1    insert to table1 VALUES (param1); 　　    select COUNT (*) into variable2 from table1; 　　   set variable1 = variable1 + 1; END while; END//8, the cursor in MySQL stored procedure is a very important concept. Cursors provide a flexible means of manipulating data retrieved from a table, in essence,A cursor is actually a mechanism that extracts one record at a time from a result set that includes multiple data records. The syntax for cursors in MySQL is as follows: DECLARE cursor-name cursor for SELECT ...; /* Declare a cursor with the name Cursor-name and use the cursor for select*/DECLARE CONTINUE HANDLER for not FOUND/* To specify how the cursor will continue to process after the result set has been traversed */OPEN cursor- Name /* OPEN cursor */FETCH cursor-name into variable [, variable]; /* Assign the variable to the cursor */CLOSE cursor-name; 　　/* Close the cursor after use */A specific example is as follows: DELIMITER//CREATE PROCEDURE ' Proc_cursor ' (out param1 INT) BEGIN DECLARE A, b, C int; 　　DECLARE cur1 CURSOR for SELECT col1 from table1; 　　DECLARE CONTINUE HANDLER for not FOUND SET B = 1; 　　OPEN Cur1; 　　SET B = 0; 　　SET c = 0; 　　 While B = 0 does    fetch cur1 into A; 　　    if B = 0 Then        set C = C + A; 　　   end IF; 　　END while; 　　CLOSE Cur1; SET param1 = c; 　　END//Where, DECLARE cur1 CURSOR for SELECT col1 from table1; Indicates that the contents of the Col1 column will be selected from the Table1 table to be placed in the cursor curl, that is, the result of each cursor traversal is placed in curl, noting that the cursor can only traverse forward, not backwards, and that the cursor cannot be updated, and the cursor is finally closed.