MySQL stored procedure functions and basic usage

Stored procedures are a method that almost all database systems support to improve performance. The following describes some functions and basic usage of mysql stored procedures for MySQL beginners, if you need to know the stored procedure, refer to this article.

Basic usage

The MySQL stored procedure gradually adds new functions from MySQL 5.0. Stored Procedures also have advantages and disadvantages in practical applications. However, the most important thing is the execution efficiency and SQL code encapsulation. In particular, the SQL code encapsulation function, if there is no stored procedure.

When an external program accesses a database (such as PHP), many SQL statements need to be organized.

Especially when the business logic is complex, a lot of SQL statements and conditions are mixed in PHP code, which is chilling. With the MySQL stored procedure, the business logic can encapsulate the stored procedure, which is not only easy to maintain, but also highly efficient in execution.

I. MySQL Stored Procedure

"Pr_add" is a simple MySQL stored procedure. This MySQL stored procedure has two int-type input parameters: "a" and "B", and returns the sum of the two parameters.

The Code is as follows:	Copy code
Drop procedure if exists pr_add;

Calculate the sum of two numbers

The Code is as follows:	Copy code
Create procedure pr_add ( A int, B int ) Begin Declare c int; If a is null then Set a = 0; End if; If B is null then Set B = 0; End if; Set c = a + B; Select c as sum; /* Return c; It cannot be used in MySQL stored procedures. Return can only appear in functions. */ End;

Ii. Call the MySQL Stored Procedure

Call pr_add (10, 20 );

Execute the MySQL stored procedure. The stored procedure parameter is a MySQL user variable.

The Code is as follows:	Copy code
Set @ a = 10; Set @ B = 20; Call pr_add (@ a, @ B );

Iii. MySQL stored procedure features

The simple syntax for creating a MySQL stored procedure is:

The Code is as follows:	Copy code
Create procedure stored procedure name () ( [In | out | inout] parameter datatype ) Begin MySQL statement; End;

If "in", "out", and "inout" are not explicitly specified for MySQL stored procedure parameters, the default value is "in ". Traditionally, we will not explicitly specify the "in" parameter.

1. The "()" after the MySQL stored procedure name is required. Even if there is no parameter, "()" is required.

2. For MySQL stored procedure parameters, you cannot add "@" before the parameter name, for example, "@ a int ". The following syntax for creating a stored procedure is incorrect in MySQL (correct in SQL Server ). You do not need to add "@" before the variable name in the MySQL stored procedure, although the MySQL client user variable requires "@".

The Code is as follows:	Copy code
Create procedure pr_add ( @ A int, -- Error B int -- correct )

3. The default value cannot be specified for MySQL stored procedure parameters.

4. You do not need to add "as" before procedure body to the MySQL stored procedure ". The SQL Server Stored Procedure must contain the "as" keyword.

The Code is as follows:	Copy code
Create procedure pr_add ( A int, B int ) As -- error. MySQL does not need "" Begin Mysql statement ...; End;

5. If the MySQL Stored Procedure contains multiple MySQL statements, the begin end keyword is required.

The Code is as follows:	Copy code
Create procedure pr_add ( A int, B int ) Begin Mysql statement 1 ...; Mysql statement 2 ...; End;

6. Add a semicolon (;) to the end of each statement in the MySQL stored procedure.

...

The Code is as follows:	Copy code
Declare c int; If a is null then Set a = 0; End if; ... End;

7. Notes in the MySQL stored procedure.

The Code is as follows:	Copy code
/* This is Comment on multiple MySQL lines. */ Declare c int; -- this is a single-line MySQL comment (Note that there must be at least one space after) If a is null then # this is also a single-row MySQL comment Set a = 0; End if; ... End;

8. The "return" keyword cannot be used in MySQL stored procedures.

The Code is as follows:	Copy code
Set c = a + B; Select c as sum; /* Return c; -- cannot be used in MySQL stored procedures. Return can only appear in functions. */ End;

9. when calling the MySQL stored procedure, you need to add "()" after the process name. Even if there is no parameter, you also need "()"

The Code is as follows:	Copy code
Call pr_no_param ();

10. Because there is no default value for the MySQL stored procedure parameters, you cannot omit the parameters when calling the MySQL stored procedure. It can be replaced by null.

Mysql stored procedure basic functions

I. 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.

The Code is as follows:

Copy code

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

Ii. 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.

The Code is as follows:	Copy code
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.

The Code is as follows:	Copy code
Mysql> select round (1.567, 2 ); + ---------------- + | Round (1.567, 2) | + ---------------- + | 1, 1.57 | + ---------------- + 1 row in set (0.00 sec) SIGN (number2) // return SIGN, positive and negative or 0 SQRT (number2) // Square

Iii. 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
LAST_DAY (date) // the last date of the Month of date
MICROSECOND (datetime) // MICROSECOND
MONTH (datetime) // MONTH
MINUTE (datetime) // MINUTE

Mysql stored procedure summary

Core tips: 1. Create a stored procedure 1. Basic Syntax:

The Code is as follows:	Copy code
Create procedure sp_name () Begin

.........
End2. parameter transfer 2. call Stored Procedure 1. Basic Syntax: call sp_name ()
Note: The stored procedure name must be enclosed in parentheses, even if

1. Create a stored procedure

1. Basic Syntax:

The Code is as follows:	Copy code
Create procedure sp_name () Begin ......... End

2. parameter transfer
Ii. Call the Stored Procedure

1. Basic Syntax: call sp_name ()
Note: The stored procedure name must be enclosed in parentheses, even if the stored procedure has no parameters
Iii. delete stored procedures

1. Basic Syntax:

The Code is as follows:	Copy code
Drop procedure sp_name //

2. Notes
(1) You cannot delete another stored procedure in one stored procedure. You can only call another stored procedure.
4. blocks, conditions, and loops

1. Block definition, commonly used
Begin
......
End;
You can also create an alias for the block, such:
Lable: begin
...........
End lable;
You can use leave lable to jump out of the block and execute code after the block.
2. conditional statements

The Code is as follows:	Copy code
If condition then Statement Else Statement End if;

3. Loop statements
(1). while Loop

The Code is as follows:	Copy code
[Label:] WHILE expression DO Statements End while [label];

(2) loop

[Label:]

The Code is as follows:	Copy code
LOOP Statements End loop [label]; (3). repeat until LOOP [Label:] REPEAT Statements UNTIL expression End repeat [label];

5. Other Common commands

1. show procedure status
Displays basic information about all stored procedures in the database, including the database, stored procedure name, and creation time.
2. show create procedure sp_name
Displays detailed information about a stored procedure.