MySQL stored procedures Getting Started tutorial

Introduction to Stored Procedures

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 difference between stored procedures and functions

　　Functions are often used as formulas, and stored procedures are used as a function of accomplishing certain functions.

Functions are divided into table-valued functions and scalar functions. A table-valued function returns a table after some SQL statement methods, and the scalar function returns a value after some SQL statement method.

The function can be used directly in the SELECT statement, and the procedure cannot.

　　Advantages of stored Procedures

As a stored procedure, there are 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 encapsulate stored procedures so that it is not only easy to maintain, but also highly efficient to execute

Of course, stored procedures also have some drawbacks, 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, define the terminator of the stored procedure

In a stored procedure, you typically enter a lot of SQL statements, and each statement in the SQL statement ends with a semicolon, so tell the stored procedure where it means the entire stored procedure ends, so before we write the stored procedure, we define the delimiter, which 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 take a look at a simple example, the following code:

DELIMITER//
Createprocedure ' P2 ' ()
LANGUAGE SQL
Deterministic
SQL SECURITY Definer
COMMENT ' A procedure '
BEGIN
SELECT ' Hello world! ';
end//

Below is a brief explanation of the components of the 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. Methods for calling stored procedures

The method of calling the stored procedure is simple, just use the call command, followed by the name of the stored procedure to invoke and the list of variables entered, such as:

Call Stored_procedure_name (param1, param2, ...)
Call Procedure1 (, ' string parameter ', @parameter_var);

　　4. Modifying and deleting stored procedures

You can use Alter's syntax to modify the main characteristics and parameters of a stored procedure, and to modify the main part of its 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. As follows

DROP PROCEDURE IF EXISTS p2;

　　5. 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 () This stored procedure is 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

CREATE PROCEDURE Proc1 (out varname data-type) varname as output parameter in this stored procedure

CREATE PROCEDURE proc1 (INOUT varname data-type) In this stored procedure, varname is both an input parameter and an output parameter

Here is an example of an in input parameter, as follows:

DELIMITER//
CREATE PROCEDURE ' proc_in ' (in Var1 INT)
BEGIN
    SELECT var1 + 2 as result;
end//

Examples of output out parameters are as follows:

DELIMITER//
CREATE PROCEDURE ' Proc_out ' (out var1 VARCHAR (100))
BEGIN
SET var1 = ' This is a test ';
END//

Examples of in-out:

DELIMITER//
CREATE PROCEDURE ' Proc_inout ' (out var1 INT)
BEGIN
SET var1 = var1 * 2;
END//

　　6. How to define Variables

Here's how to define a variable 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

To illustrate:

DECLARE 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;  
  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. Syntax structure of MySQL stored procedure

Syntax structures and statements such as If,case,iterate,leave Loop,while and repeat are supported in MySQL stored procedures, and in this article, the if,case and while syntax are highlighted, as they are most widely used.

IF statement

The IF statement uses the syntax structure of the if...then end If, as an example:

DELIMITER//
CREATE PROCEDURE ' proc_if ' (in param1 INT)
BEGIN
DECLARE variable1 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 many if statements, you should consider using a case statement, which is a multi-branch selection statement, which is written in two ways:

The first type of notation:

DELIMITER//
CREATE PROCEDURE ' proc_case ' (in param1 INT)
BEGIN
DECLARE variable1 INT;
SET variable1 = param1 + 1;
Case Variable1
When 0 then
INSERT into table1 VALUES (param1);
When 1 Then
INSERT into table1 VALUES (variable1);
ELSE
INSERT into table1 VALUES (in);
END case;
END//

Another way to do this:

DELIMITER//
CREATE PROCEDURE ' proc_case ' (in param1 INT)
BEGIN
DECLARE variable1 INT;
SET variable1 = param1 + 1;
 Case
When variable1 = 0 Then
INSERT into table1 VALUES (param1);
When variable1 = 1 Then
INSERT into table1 VALUES (variable1);
ELSE
INSERT into table1 VALUES (in);
END case;
END//

While statement

The while statement is similar to the while statement in a common programming language, as in the following example:

 delimiter// 
 create PROCEDURE ' Proc_while ' (in param1 INT)  
 begin  
  DECLARE Variable1, variable2 INT;  
  
  while variable1  
  INSERT into table1 VALUES (param1);  
  select COUNT (*) into variable2 from table1;  
  
  END while;  
 end// 

　　8. Cursors in MySQL stored procedure

Cursors in MySQL are a very important concept. Cursors provide a flexible means of manipulating data retrieved from a table, essentially, a cursor is essentially a mechanism for extracting 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/* Specifies how the cursor continues to process after the result set has been traversed */
OPEN Cursor-name; /* OPEN CURSOR */
FETCH cursor-name into variable [, variable]; /* Assign a variable to a cursor */
CLOSE Cursor-name; /* Close cursor after use */

A concrete 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 do
FETCH cur1 into A;
IF B = 0 Then
SET C = C + A;
END IF;
END while;
CLOSE Cur1;
SET param1 = c;
END//

Wherein, 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.

MySQL stored procedures Getting Started tutorial