1/ What are stored procedures

Is a collection of one or more SQL statements saved for later use. Stored procedure concept is the database SQL language level code encapsulation and reuse. You can think of it as a function in Python. MySQL version 5.0 is starting to support stored procedures. Stored Procedure is a database object that stores complex programs in a database for external programs to call. A stored procedure is a set of SQL statements designed to perform a specific function. It is created by compilation and stored in a database. The user can invoke the execution of the stored procedure by specifying its name and giving parameters (if necessary). Stored procedure is very simple in idea, which is code encapsulation and reuse at database SQL language level. You can think of it as a function in Python.Copy the code

2/ Strengths and weaknesses of stored procedures

< 1 >

Stored procedures can encapsulate and hide complex business logic. Stored procedures can send back values and can accept parameters. A stored procedure cannot be run using the SELECT instruction because it is a subroutine, unlike viewing a table, data table, or user-defined function. Stored procedures can be used to validate data, enforce business logic, and so on.Copy the code

< 2 >

Stored procedures are often customized to specific databases because of the supported programming languages. When switching to another vendor's database system, the existing stored procedures need to be rewritten. Performance tuning and authoring of stored procedures is limited to a variety of database systems.Copy the code

3/ Creation of stored procedures

A stored procedure is a piece of code with a name that performs a specific function. The created stored procedures are stored in the database's data dictionary. <1> Declare the statement terminator, which can be customized: DELIMITER $$or DELIMITER. CREATE PROCEDURE demo_in_parameter(IN p_in int) Stored procedures can have or have no parameters, but must be followed by () parentheses. There are three types of parameters, IN /out/inout: Input values use the in parameter. The return value uses the OUT argument. Use the inout parameter as little as possible. <3> Stored procedure start and end symbols: BEGIN.... END <4> Assign to a variable: SET @p_in=1 <5> DECLARE l_int int unsigned default 4000000; <7> Body of the stored procedure: The stored procedure body contains statements that must be executed during a procedure call, such as DML and DDL statements, if-then-else and while-do statements, declare statements that declare variables, and so on: Start with begin and end with end (nested) begin begin begin statements; Note: Each nested block and each statement within it must END with a semicolon. Begin-end blocks (also known as compound statements) that indicate the END of a procedure body do not need a semicolon.Copy the code

   - the sample
   Create a new database db1 and copy data from other database tables to new tables PLAYERS and MATCHES
   mysql> create database db1;
   mysql> use db1;    
   mysql> create table PLAYERS as select * from TENNIS.PLAYERS;
   mysql> create table MATCHES  as select * from TENNIS.MATCHES;
   
   Here is an example of a stored procedure that deletes all matches played by a given player:
   mysql>delimiter $$-- Changes the end of a statement from a semicolon; Temporarily change to two $$(can be custom)
   mysql> create procedure delete_matches(IN p_playerno INTEGER) -- Input variable p_playerno
       -> BEGIN
       -> 　　DELETE FROM MATCHES
       ->    WHERE playerno = p_playerno;  SQL > select * from 'and';
       -> END $$
   Query OK, 0 rows affected (0.01 sec)

   mysql>delimiter ;Restore statement terminator to semicolon
Copy the code

By default, a stored procedure is associated with a default database. If you want to specify that the stored procedure is created under a particular database, prefix the stored procedure name with the database name. When defining the stored procedure, use the DELIMITER $$command to change the end of the statement from a semicolon; Temporarily change it to two $$and don't forget to change it back at the end.Copy the code

4/ Calls to stored procedures

Syntax: call sp_name[(pass parameter)]; Use call with the procedure name and a parentheses with input parameters, output parameters, and input/output parameters as needed.Copy the code

    mysql> select * from MATCHES;  Look at all the data in the table first
    +---------+--------+----------+-----+------+
    | MATCHNO | TEAMNO | PLAYERNO | WON | LOST |
    +---------+--------+----------+-----+------+
    |       1 |      1 |        6 |   3 |    1 |
    |       7 |      1 |       57 |   3 |    0 |
    |       8 |      1 |        8 |   0 |    3 |
    |       9 |      2 |       27 |   3 |    2 |
    |      11 |      2 |      112 |   2 |    3 |
    +---------+--------+----------+-----+------+
    5 rows in set (0.00 sec)

    mysql> call delete_matches(57); -- Call the stored procedure delete_matches() with parameter 57
    Query OK, 1 row affected (0.03 sec)

    mysql> select * from MATCHES;
    +---------+--------+----------+-----+------+
    | MATCHNO | TEAMNO | PLAYERNO | WON | LOST |
    +---------+--------+----------+-----+------+
    |       1 |      1 |        6 |   3 |    1 |
    |       8 |      1 |        8 |   0 |    3 |
    |       9 |      2 |       27 |   3 |    2 |
    |      11 |      2 |      112 |   2 |    3 |
    +---------+--------+--------
    P_playerno = p_playerno = p_playerno
    P_playerno = 57; p_playerno = 57; p_playerno = 57; p_playerno = 57;
Copy the code

5/ Parameters of the stored procedure

MySQL stored procedure parameters used IN the definition of the stored procedure, a total of three parameter types, IN and OUT, and INOUT, forms such as: CREATEPROCEDURE stored procedure name ([[IN | OUT | INOUT] parameter name data type... ) <1>IN Input parameters: indicates that the caller passes a value to the stored procedure (which can be a literal or a variable) <2>OUT Output parameters: indicates that the stored procedure passes a value to the caller (which can return multiple values) (the outgoing value can only be a variable) <3>INOUT Input and output parameters: Indicates that the caller passes a value to the procedure and that the procedure passes a value to the caller (the value can only be a variable)Copy the code

<1>in Input parameter

    mysql> delimiter $$ -- Modify the end symbol
    mysql> create procedure in_param(in p_in int)
        -> begin
        -> 　　select p_in;
        -> 　　set p_in=2;
        ->    select P_in;
        -> end$$
    mysql> delimiter ;

    mysql> set @p_in=1;

    mysql> call in_param(@p_in);
            +------+
            | p_in |
            +------+
            |    1 |
            +------+

            +------+
            | P_in |
            +------+
            |    2 |
            +------+

            mysql> select @p_in;
            +-------+
            | @p_in |
            +-------+
            |     1 |
            +-------+
   P_in = p_in; p_in = p_in; p_in = p_in; p_in = p_in;
Copy the code

<2>out Output parameters

    mysql> delimiter //  -- Modify the end symbol
    mysql> create procedure out_param(out p_out int)
        ->   begin
        ->     select p_out;
        ->     set p_out=2;
        ->     select p_out;
        ->   end
        -> //
    mysql> delimiter ;

    mysql> set @p_out=1;

    mysql> call out_param(@p_out);
        +-------+
        | p_out |
        +-------+
        |  NULL |
        +-------+# becauseoutP_out is output to the caller and does not receive input arguments, so p_out in the stored procedure isnull
        +-------+
        | p_out |
        +-------+
        |     2 |
        +-------+

    mysql> select @p_out;
        +--------+
        | @p_out |
        +--------+
        |      2 |
        +--------+# call the out_param stored procedure, print the parameter, and change the value of p_out variableCopy the code

<3>inout Input parameter

    mysql> delimiter $$
    mysql> create procedure inout_param(inout p_inout int)
        ->   begin
        ->     select p_inout;
        ->     set p_inout=2;
        ->     select p_inout;
        ->   end$$
    mysql> delimiter ;

    mysql> set @p_inout=1;

    mysql> call inout_param(@p_inout);
        +---------+
        | p_inout |
        +---------+
        |       1 |
        +---------+

        +---------+
        | p_inout |
        +---------+
        |       2 |
        +---------+

    mysql> select @p_inout;
        +----------+
        | @p_inout |
        +----------+
        |        2 |
        +----------+# call the inout_param stored procedure, take the input parameters, also output parameters, change the variableCopy the code

6/ Query stored procedures

We want to know what tables are under a database, we usually use show tables; Take a look. So if we want to look at a stored procedure under a database, can we also use it? The answer is that we can look at stored procedures under a database, but in a different way.Copy the code

   We can use the following statement to query:
   selectname from mysql.proc where db='database name';
   或者
   selectroutine_name from information_schema.routines where routine_schema='database name';
   或者
   showprocedure status where db='database name';
   
   What if we want to know the details of a stored procedure?
   -- Is it possible to view the describe table name as well as manipulate tables?
   The answer is: we can view the details of the stored procedure, but we need to do it in a different way:
        SHOWCREATE PROCEDUREDatabase. Stored procedure name;Copy the code

7/ Changes to stored procedures

ALTER PROCEDURE Changes a pre-specified stored PROCEDURE created with CREATE PROCEDURE without affecting the associated stored PROCEDURE or storage function.Copy the code

8/ Deletion of stored procedures

Dropping a stored PROCEDURE is as simple as dropping a table: DROP PROCEDURECopy the code

9 / variable

<1> Variable definition

Local variable declarations must be placed at the beginning of the stored procedure body: DECLARE variable_name [,variable_name...]  datatype [DEFAULT value]; Datatype specifies the datatype of MySQL, for example, int, float, date,varchar(length). For example, DECLARE l_int int unsigned default 4000000. DECLARE l_numeric number(8,2) DEFAULT 9.95; DECLARE l_date date DEFAULT '1999-12-31'; DECLARE l_datetime datetime DEFAULT '1999-12-31 23:59:59'; DECLARE l_varchar varchar(255) DEFAULT 'This will not be padded';Copy the code

<2> Variable assignment

SET variable name = expression value [,variable_name = expression...Copy the code

<3> User variable

Use user variables in MySQL client:Copy the code

    mysql > SELECT 'Hello World' into @x;  
    mysql > SELECT @x;  
            +-------------+  
            |   @x        |  
            +-------------+  
            | Hello World |  
            +-------------+  
    mysql > SET @y='Goodbye Cruel World';  
    mysql > SELECT @y;  
            +---------------------+  
            |     @y              |  
            +---------------------+  
            | Goodbye Cruel World |  
            +---------------------+  

    mysql > SET @z=1+2+3;  
    mysql > SELECT @z;  
            +------+  
            | @z   |  
            +------+  
            |  6   |  
            +------+
    Use user variables in stored procedures

    mysql > CREATE PROCEDURE GreetWorld( ) SELECT CONCAT(@greeting.' World');  
    mysql > SET @greeting='Hello';  
    mysql > CALL GreetWorld( );  
            +----------------------------+  
            | CONCAT(@greeting.' World') |  
            +----------------------------+  
            |  Hello World               |  
            +----------------------------+
    Pass global-scoped user variables between stored procedures

    mysql> CREATE PROCEDURE p1()   SET @last_procedure='p1';  
    mysql> CREATE PROCEDURE p2() SELECT CONCAT('Last procedure was '.@last_procedure);  
    mysql> CALL p1( );  
    mysql> CALL p2( );  
            +-----------------------------------------------+  
            | CONCAT('Last procedure was '.@last_proc       |  
            +-----------------------------------------------+  
            | Last procedure was p1                         |  
             +-----------------------------------------------+
    - note:
    User variable names usually start with @
    2. Misuse of user variables can make the program difficult to understand and manage
Copy the code

10 / comment

MySQL stored procedures can use two styles of comment: two hyphens -- : This style is typically used for single-line comments. C-style: Generally used for multi-line comments.Copy the code

    , such as:
    mysql > DELIMITER //  
    mysql > CREATE PROCEDURE proc1 --name Name of the stored procedure
         -> (IN parameter1 INTEGER)   
         -> BEGIN   
         -> DECLARE variable1 CHAR(10);   
         -> IF parameter1 = 17 THEN   
         -> SET variable1 = 'birds';   
         -> ELSE 
         -> SET variable1 = 'beasts';   
        -> END IF;   
        -> INSERT INTO table1 VALUES (variable1);  
        -> END   
        -> //  
    mysql > DELIMITER ;
Copy the code