Mysql stored procedure details

1. Introduction to stored procedures

The commonly used operating database language SQL statements must be compiled and then executed before execution. The Stored Procedure (Stored Procedure) is a set of SQL statements for specific functions, after compilation, the stored procedure is stored in the database. You can call and execute the stored procedure by specifying the name of the stored procedure and specifying parameters (if the stored procedure has parameters.

A stored procedure is a Programmable function that is created and saved in a database. It can contain SQL statements and some special control structures. Stored procedures are useful when you want to execute the same functions on different applications or platforms or encapsulate specific functions. Stored Procedures in databases can be seen as simulation of object-oriented methods in programming. It allows you to control how data is accessed.

Stored procedures generally have the following advantages:

(1) stored procedures enhance the functionality and flexibility of the SQL language. Stored procedures can be written using flow control statements. With great flexibility, they can complete complicated judgment and computation.

(2) 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 of the stored procedure. Database professionals can modify the stored procedure at any time without affecting the application source code.

(3). the stored procedure can achieve fast execution speed. If an operation contains a large number of Transaction-SQL code or is executed multiple times, the stored procedure is much faster than the batch processing. Because the stored procedure is pre-compiled. When a stored procedure is run for the first time, the optimizer analyzes and optimizes it and provides an execution plan that is finally stored in the system table. The Transaction-SQL statement of batch processing needs to be compiled and optimized each time it is run, which is relatively slow.

(4). stored procedures can reduce network traffic. For operations (such as queries and modifications) on the same database object, if the Transaction-SQL statement involved in this operation is organized by the stored procedure, when the stored procedure is called on the client's computer, the call statement is transmitted on the network, which greatly increases network traffic and reduces network load.

(5) stored procedures can be fully utilized as a security mechanism. By limiting the permissions of a stored procedure, the system administrator can restrict the data access permissions to avoid unauthorized users accessing data, this ensures data security.

2. MySQL stored procedures

Stored procedures are an important function of database storage. However, MySQL did not support stored procedures before MySQL 5.0, which greatly compromises the application of MySQL. Fortunately, MySQL 5.0 has finally started to support stored procedures, which can greatly improve the processing speed of the database and the flexibility of database programming.

3. Create a MySQL Stored Procedure

(1). Format

Format of MySQL stored PROCEDURE creation: create procedure process name ([process parameter [,...])
[Features...] process body

Here is an example:

Mysql> DELIMITER //
Mysql> create procedure proc1 (OUT s int)
-> BEGIN
-> Select count (*) INTO s FROM user;
-> END
-> //
Mysql> DELIMITER;

Note:

(1) Here we need to note that DELIMITER // and DELIMITER; the DELIMITER means DELIMITER, because MySQL uses ";" as the Separator by default, if we do not declare a DELIMITER, the compiler treats the stored procedure as an SQL statement, and the compilation process of the stored procedure reports an error. Therefore, we must first use the DELIMITER keyword to declare the DELIMITER of the current segment, in this way, MySQL regards ";" as the code in the stored procedure and does not execute the code. After the code is used up, the separator should be restored.

(2) The stored procedure may have input, output, and input and output parameters as needed. Here there is an output parameter s in the type of int. If there are multiple parameters, use ", "Split.

(3) start and END of the Process body are identified by BEGIN and END.

In this way, a MySQL stored procedure is completed, isn't it easy? It doesn't matter if you don't understand it. Next, Let's explain it in detail.

(2). Declare the delimiter

As a matter of fact, the above annotations have been clearly written about the Declaration delimiter. You don't need to say much, but note that if you use the MySQL Administrator management tool, it can be created directly without declaration.

(3). Parameters

MySQL stored procedure parameters are used IN the definition of stored procedures. There are three parameter types: IN, OUT, And INOUT. The format is as follows:

Create procedure ([[IN | OUT | INOUT] Parameter Name Data class...])

IN input parameter: indicates that the value of this parameter must be specified when the stored procedure is called. Modifying the value of this parameter IN the stored procedure cannot be returned, which is the default value.

OUT output parameter: The value can be changed within the stored procedure and can be returned.

INOUT input and output parameters: this parameter is specified during the call and can be changed or returned.

I. IN parameter example

Create:

Mysql> DELIMITER //
Mysql> create procedure demo_in_parameter (IN p_in int)
-> BEGIN
-> SELECT p_in;
-> SET p_in = 2;
-> SELECT p_in;
-> END;
-> //
Mysql> DELIMITER;

Execution result:

Mysql> SET @ p_in = 1;
Mysql> CALL demo_in_parameter (@ p_in );
+ ------ +
| P_in |
+ ------ +
| 1 |
+ ------ +

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

Mysql> SELECT @ p_in;
+ ------- +
| @ P_in |
+ ------- +
| 1 |
+ ------- +

As shown above, although p_in is modified in the stored procedure, it does not affect the value of @ p_id.

Example of the II. OUT Parameter

Create:

Mysql> DELIMITER //
Mysql> create procedure demo_out_parameter (OUT p_out int)
-> BEGIN
-> SELECT p_out;
-> SET p_out = 2;
-> SELECT p_out;
-> END;
-> //
Mysql> DELIMITER;

Execution result:

Mysql> SET @ p_out = 1;
Mysql> CALL sp_demo_out_parameter (@ p_out );
+ ------- +
| P_out |
+ ------- +
| NULL |
+ ------- +

+ ------- +
| P_out |
+ ------- +
| 2 |
+ ------- +

Mysql> SELECT @ p_out;
+ ------- +
| P_out |
+ ------- +
| 2 |
+ ------- +

Iii. Example of INOUT Parameters

Create:

Mysql> DELIMITER //
Mysql> create procedure demo_inout_parameter (INOUT p_inout int)
-> BEGIN
-> SELECT p_inout;
-> SET p_inout = 2;
-> SELECT p_inout;
-> END;
-> //
Mysql> DELIMITER;

Execution result:

Mysql> SET @ p_inout = 1;
Mysql> CALL demo_inout_parameter (@ p_inout );
+ --------- +
| P_inout |
+ --------- +
| 1 |
+ --------- +

+ --------- +
| P_inout |
+ --------- +
| 2 |
+ --------- +

Mysql> SELECT @ p_inout;
+ ---------- +
| @ P_inout |
+ ---------- +
| 2 |
+ ---------- +

(4). Variables

I. variable definition

DECLARE variable_name [, variable_name...] datatype [DEFAULT value];

Datatype is the data type of MySQL, such as int, float, date, varchar (length)

For example:

DECLARE Rochelle int unsigned default 4000000;
DECLARE l_numeric number (9.95) DEFAULT;
DECLARE l_date date DEFAULT '2017-12-31 ';
DECLARE l_datetime datetime DEFAULT '2017-12-31 23:59:59 ';
DECLARE l_varchar varchar (255) DEFAULT 'this will not be padded ';

Ii. Variable assignment

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

Iii. User Variables

I. Use user variables on 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 |
+ ------ +
Ii. 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 |
+ ---------------------------- +

Iii. Pass global user variables between stored procedures

Mysql> create procedure p1 () SET @ last_procedure = 'p1 ';
Mysql> create procedure p2 () select concat ('Last procedure was', @ last_proc );
Mysql> CALL p1 ();
Mysql> CALL p2 ();
+ ----------------------------------------------- +
| CONCAT ('Last procedure was', @ last_proc |
+ ----------------------------------------------- +
| Last procedure was p1 |
+ ----------------------------------------------- +

Note:

① User variable names generally start @
② Misuse of user variables will make the program hard to understand and manage

(5). Comment

MySQL stored procedures can be annotated using two styles

Dual-mode bar :--

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;

4. MySQL stored procedure call

Use the call and your process name and a bracket. Add parameters in the brackets as needed, including input parameters, output parameters, and input and output parameters. For details about the call method, refer to the example above.

5. query MySQL stored procedures

As we know the tables under a database, we generally use show tables; for viewing. So can we check whether the stored procedures under a database can be used as well? The answer is: we can view the stored procedures under a database, but it takes only one minute.

We can use

Select name from mysql. proc where db = 'database name ';

Or

Select routine_name from information_schema.routines where routine_schema = 'database name ';

Or

Show procedure status where db = 'database name ';

.

If we want to know the details of a stored procedure, what should we do? Can I use the describe table name for viewing like an operation table?

The answer is: we can view the details of the stored procedure, but we need to use another method:

Show create procedure database. Name of the stored PROCEDURE;

You can view the details of the current stored procedure.

6. Modify the MySQL Stored Procedure

ALTER PROCEDURE

Change the pre-specified stored PROCEDURE created with create procedure without affecting the stored PROCEDURE or function.

7. Delete the MySQL Stored Procedure

Deleting a stored procedure is simple, just like deleting a table:

DROP PROCEDURE

Delete one or more stored procedures from a MySQL table.

8. MySQL stored procedure control statements

(1). variable scope

Internal variables have a higher priority within the scope of their scope, when executed to the end. The internal variable disappears and is out of its scope, and the variable is no longer visible.
The declarative variable can no longer be found outside the process, but you can use the out parameter or assign its value
Session variables to save their values.

Mysql> DELIMITER //
Mysql> create procedure proc3 ()
-> Begin
-> Declare x1 varchar (5) default 'outer ';
-> Begin
-> Declare x1 varchar (5) default 'inner ';
-> Select x1;
-> End;
-> Select x1;
-> End;
-> //
Mysql> DELIMITER;

(2). Condition Statement

I. if-then-else statement

Mysql> DELIMITER //
Mysql> create procedure proc2 (IN parameter int)
-> Begin
-> Declare var int;
-> Set var = parameter + 1;
-> If var = 0 then
-> Insert into t values (17 );
-> End if;
-> If parameter = 0 then
-> Update t set s1 = s1 + 1;
-> Else
-> Update t set s1 = s1 + 2;
-> End if;
-> End;
-> //
Mysql> DELIMITER;

Ii. case statement:

Mysql> DELIMITER //
Mysql> create procedure proc3 (in parameter int)
-> Begin
-> Declare var int;
-> Set var = parameter + 1;
-> Case var
-> When 0 then
-> Insert into t values (17 );
-> When 1 then
-> Insert into t values (18 );
-> Else
-> Insert into t values (19 );
-> End case;
-> End;
-> //
Mysql> DELIMITER;

(3). Loop statement

I. while... end while:

Mysql> DELIMITER //
Mysql> create procedure proc4 ()
-> Begin
-> Declare var int;
-> Set var = 0;
-> While var <6 do
-> Insert into t values (var );
-> Set var = var + 1;
-> End while;
-> End;
-> //
Mysql> DELIMITER;

Ii. repeat · end repeat:

It checks the result after the operation is executed, while it checks before the 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;

Iii. loop · end loop:

The loop does not require the initial conditions. This is similar to the while loop, and does not require the end condition like the repeat loop. The leave statement is used 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;

Iv. LABLES labels:

The label can be used before the begin repeat while or loop statement. The statement label can only be used before a valid statement. You can jump out of the loop to make the running command the last step of the compound statement.

(4). ITERATE Iteration

I. ITERATE:

Reference the compound statement label to start a compound statement.

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;

9. basic functions of the MySQL Stored Procedure

(1). string type

CHARSET (str) // returns the string Character Set
CONCAT (string2 [,...]) // connection string
INSTR (string, substring) // returns the position of the first occurrence of the substring in the string. If no position exists, 0 is returned.
LCASE (string2) // converts it to lowercase
LEFT (string2, length) // take the length from the LEFT of string2
LENGTH (string) // string LENGTH
LOAD_FILE (file_name) // read content from the file
LOCATE (substring, string [, start_position]) is the same as INSTR, but the start position can be specified.
LPAD (string2, length, pad) // repeat pad to start with string until the string length is length
LTRIM (string2) // remove leading Spaces
REPEAT (string2, count) // REPEAT count times
REPLACE (str, search_str, replace_str) // REPLACE search_str with replace_str in str
RPAD (string2, length, pad) // use pad after str until the length is length.
RTRIM (string2) // remove backend Spaces
STRCMP (string1, string2) // compare the size of two strings by character,
SUBSTRING (str, position [, length]) // starts from the position of str and takes length characters,
Note: When processing strings in mysql, the default subscript of the first character is 1, that is, the parameter position must be greater than or equal to 1.

Mysql> select substring ('abcd );
+ ----------------------- +
| Substring ('abcd',) |
+ ----------------------- +

+ ----------------------- +
1 row in set (0.00 sec)

Mysql> select substring ('abcd', 1, 2 );
+ ----------------------- +
| Substring ('abcd', 1, 2) |
+ ----------------------- +
| AB |
+ ----------------------- +
1 row in set (0.02 sec)
TRIM ([[BOTH | LEADING | TRAILING] [padding] FROM] string2) // remove the specified character FROM the specified position
UCASE (string2) // converts to uppercase
RIGHT (string2, length) // gets the last length character of string2
SPACE (count) // generate count Spaces

(2). Mathematics

ABS (number2) // absolute value
BIN (decimal_number) // convert decimal to binary
CEILING (number2) // rounded up
CONV (number2, from_base, to_base) // hexadecimal conversion
FLOOR (number2) // round down
FORMAT (number, decimal_places) // number of reserved decimal places
HEX (DecimalNumber) // convert to hexadecimal
Note: HEX () can input a string, returns its ASC-11 code, such as HEX ('def ') returns 4142143
You can also input a decimal integer to return its hexadecimal encoding. For example, HEX (25) returns 19.
LEAST (number, number2 [,...]) // calculates the minimum value.
MOD (numerator, denominator) // evaluate the remainder
POWER (number, power) // Exponent
RAND ([seed]) // Random Number
ROUND (number [, decimals]) // rounding, decimals is the number of decimal places]

Note: The return type is not an integer, for example:
(1) The default value is integer.

Mysql> select round (1.23 );
+ ------------- +
| Round (1.23) |
+ ------------- +
| 1 |
+ ------------- +
1 row in set (0.00 sec)

Mysql> select round (1.56 );
+ ------------- +
| Round (1.56) |
+ ------------- +
| 2 |
+ ------------- +
1 row in set (0.00 sec)

(2) the number of decimal places can be set to return floating point data.

Mysql> select round (1.567, 2 );
+ ---------------- +
| Round (1.567, 2) |
+ ---------------- +
| 1, 1.57 |
+ ---------------- +
1 row in set (0.00 sec)
SIGN (number2 )//

(3). Date and Time

ADDTIME (date2, time_interval) // Add time_interval to date2
CONVERT_TZ (datetime2, fromTZ, toTZ) // convert the time zone
CURRENT_DATE () // current date
CURRENT_TIME () // current time
CURRENT_TIMESTAMP () // current Timestamp
DATE (datetime) // return the DATE part of datetime
DATE_ADD (date2, INTERVAL d_value d_type) // Add a date or time in date2
DATE_FORMAT (datetime, FormatCodes) // display datetime in formatcodes format
DATE_SUB (date2, INTERVAL d_value d_type) // subtract a time from date2
DATEDIFF (date1, date2) // two date differences
DAY (date) // returns the DAY of the date
DAYNAME (date) // english week
DAYOFWEEK (date) // Week (1-7), 1 is Sunday
DAYOFYEAR (date) // The day of the year
EXTRACT (interval_name FROM date) // EXTRACT the specified part of the date FROM date
MAKEDATE (year, day) // specifies the day of the year and year to generate a date string.
MAKETIME (hour, minute, second) // generate a time string
MONTHNAME (date) // name of the English month
NOW () // current time
SEC_TO_TIME (seconds) // converts seconds to time
STR_TO_DATE (string, format) // convert string to time, which is displayed in format
TIMEDIFF (datetime1, datetime2) // two time difference
TIME_TO_SEC (time) // time to seconds]
WEEK (date_time [, start_of_week]) // WEEK
YEAR (datetime) // YEAR
DAYOFMONTH (datetime) // The day of the month
HOUR (datetime) // HOUR