MySQL Stored Procedures

❮ Code Editor Shell Process Substitution ❯

MySQL Stored Procedures

Category Programming Techniques

MySQL has supported stored procedures since version 5.0.

A stored procedure is a database object that stores complex programs for external programs to call.

Stored procedures are collections of SQL statements designed to perform specific functions, compiled and created, and saved in the database. Users can call them for execution by specifying the name of the stored procedure and providing parameters (when necessary).

The concept of stored procedures is simple; it is essentially the encapsulation and reuse of code at the SQL language level in the database.

Advantages

Stored procedures can encapsulate and hide complex business logic.
Stored procedures can return values and accept parameters.
Stored procedures cannot be run using the SELECT statement because they are sub-programs, which is different from viewing tables, data tables, or user-defined functions.
Stored procedures can be used for data validation, enforcing business logic, etc.

Disadvantages

Stored procedures are often customized for specific databases because the supported programming languages vary. When switching to a database system from another vendor, the existing stored procedures need to be rewritten.
The performance tuning and writing of stored procedures are limited by various database systems.

I. Creating and Calling Stored Procedures

A stored procedure is a piece of code with a name, used to perform a specific function.
The created stored procedure is saved in the database's data dictionary.

Creating Stored Procedures

CREATE
    [DEFINER = { user | CURRENT_USER }]
　PROCEDURE sp_name ([proc_parameter[,...]])
    [characteristic ...] routine_body

proc_parameter:
    [ IN | OUT | INOUT ] param_name type

characteristic:
    COMMENT 'string'
  | LANGUAGE SQL
  | [NOT] DETERMINISTIC
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }

routine_body:
　　Valid SQL routine statement

[begin_label:] BEGIN
　　[statement_list]
　　　　……
END [end_label]

Key Syntax in MYSQL Stored Procedures

Statement terminator, can be customized:

DELIMITER $$
or
DELIMITER //

Declare a stored procedure:

CREATE PROCEDURE demo_in_parameter(IN p_in int)

Start and end symbols for stored procedures:

BEGIN .... END

Variable assignment:

SET @p_in=1

Variable definition:

DECLARE l_int int unsigned default 4000000;

Creating MySQL stored procedures, stored functions:

create procedure procedure_name(parameters)

Body of the stored procedure:

create function function_name(parameters)

Example

Create a database, backup data tables for example operations:

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 to delete all matches participated in by a given player:

mysql> delimiter $$　　# Temporarily change the statement terminator from semicolon ; to two $$ (can be customized)
mysql> CREATE PROCEDURE delete_matches(IN p_playerno INTEGER)
    -> BEGIN
    -> 　　DELETE FROM MATCHES
    ->    WHERE playerno = p_playerno;
    -> END$$
Query OK, 0 rows affected (0.01 sec)

mysql> delimiter;　　# Restore the statement terminator to semicolon

Analysis: By default, stored procedures are associated with the default database. If you want to specify that a stored procedure is created under a specific database, then prefix the procedure name with the database name.

When defining a procedure, use the DELIMITER $$ command to temporarily change the statement terminator from semicolon ; to two $$, so that the semicolons used in the procedure body are directly transmitted to the server and not interpreted by the client (such as mysql).

Call the stored procedure:

call sp_name[(arguments)];

mysql> select * from MATCHES;
+---------+--------+----------+-----+------+
| 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
This is a Chinese to English translation, please provide the English translation for this text. Do not provide any explanations or text apart from the translation.

Chinese: |       1 |
+---------+

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

mysql> select @p_inout;
+----------+
| @p_inout |
+----------+
|        2 |
+----------+
#Called the inout_param stored procedure, accepted the input parameter, also output the parameter, changed the variable

Note:

If the procedure has no parameters, you must still write parentheses after the procedure name, for example:
```
CREATE PROCEDURE sp_name ([proc_parameter[,...]]) ……
```
Ensure that the parameter name is not the same as the column name, otherwise, in the procedure body, the parameter name is treated as a column name.

Suggestions:

Use in parameters for input values.
Use out parameters for return values.
Minimize the use of inout parameters.

Three, Variables

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];

Here, datatype is a MySQL data type, such as: 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';

2. Variable Assignment

SET variable_name = expression value [,variable_name = expression ...]

3. User Variables

Use user variables in the MySQL client:

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 user variables with global scope 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_procedure) |  
+-----------------------------------------------+  
| Last procedure was p1                         |  
 +-----------------------------------------------+

Note:

1. User variable names generally start with @
1. Misuse of user variables can make the program difficult to understand and manage

Four, Comments

MySQL stored procedures can use two styles of comments

Two horizontal bars --: This style is generally used for single-line comments.

C style: Generally used for multi-line comments.

For example:

mysql > DELIMITER //  
mysql > CREATE PROCEDURE proc1 --name stored procedure name  
     -> (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 ;

MySQL Stored Procedure Invocation

Use call and your procedure name along with a parenthesis, inside the parenthesis according to the need, add parameters, including input parameters, output parameters, input and output parameters. The specific method of calling can refer to the above example.

MySQL Stored Procedure Query

We want to know what tables are under a database,


while condition do
    -- Loop body
endwhile

repeat... end repeat

It checks the result after performing the operation, while the while loop checks before execution.


mysql > DELIMITER //  
mysql > CREATE PROCEDURE proc5 ()  
     -> begin   
     -> declare v int;  
     -> set v=0;  
     -> repeat  
     -> insert into t values(v);  
     -> set v=v+1;  
     -> until v>=5  
     -> end repeat;  
     -> end;  
     -> //  
mysql > DELIMITER ;


repeat
    -- Loop body
until loop condition  
end repeat;

loop ... endloop

The loop does not require an initial condition, which is similar to the while loop, and like the repeat loop, it does not require a termination condition. The leave statement means to exit the loop.


mysql > DELIMITER //  
mysql > CREATE PROCEDURE proc6 ()  
     -> begin 
     -> declare v int;  
     -> set v=0;  
     -> LOOP_LABLE:loop  
     -> insert into t values(v);  
     -> set v=v+1;  
     -> if v >=5 then 
     -> leave LOOP_LABLE;  
     -> end if;  
     -> end loop;  
     -> end;  
     -> //  
mysql > DELIMITER ;

Labels:

Labels can be used in front of begin, repeat, while, or loop statements. Statement labels can only be used in front of valid statements. They can break out of the loop, causing the execution to reach the last step of the compound statement.

(4). ITERATE Iteration

ITERATE restarts the compound statement by referencing its label:


mysql > DELIMITER //  
mysql > CREATE PROCEDURE proc10 ()  
     -> begin 
     -> declare v int;  
     -> set v=0;  
     -> LOOP_LABLE:loop  
     -> if v=3 then   
     -> set v=v+1;  
     -> ITERATE LOOP_LABLE;  
     -> end if;  
     -> insert into t values(v);  
     -> set v=v+1;  
     -> if v>=5 then 
     -> leave LOOP_LABLE;  
     -> end if;  
     -> end loop;  
     -> end;  
     -> //  
mysql > DELIMITER ;

Reference articles:

https://www.cnblogs.com/geaozhang/p/6797357.html

http://blog.sina.com.cn/s/blog_86fe5b440100wdyt.html

**Click to share notes

Cancel

English:

❮ Code Editor Shell Process Substitution ❯