Mysql Stored Procedure syntax and example

Mysql Stored Procedure syntax and instance stored procedure, such as the same programming language, also contains the data type, process control, input and output, and its own function library. -------------------- Basic syntax ------------------ 1. Create the Stored Procedure createproceduresp_name () begin ...... end 2. Call the Stored Procedure

Mysql Stored Procedure syntax and instance stored procedure, such as the same programming language, also contains the data type, process control, input and output, and its own function library. -------------------- Basic syntax ------------------ 1. create procedure sp_name () begin ...... end 2. Call save

Mysql Stored Procedure syntax and Examples

Stored procedures, such as the same programming language, also contain data types, process control, input and output, and their own function libraries.

------------------ Basic syntax --------------------

1. Create a stored procedure
Create procedure sp_name ()
Begin
.........
End

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:
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. 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 mysql stored procedure.

-------------------- Data type and operator --------------------
I. Basic data types:
Omitted

2. variables:

Custom variable: DECLARE ?? A INT; SET a = 100 ;??? Use the following statement instead: DECLARE a int default 100;

Variable is dividedUser variableAndSystem VariablesSystem variables are further divided into sessions and full-site variables.

User variable: User variable names generally start with @. Misuse of user variables will make the program hard to understand and manage.

1. Use user variables on the mysql client
Mysql> SELECT 'Hello world' into @ x;
Mysql> SELECT @ x;

Mysql> SET @ y = 'Goodbye Cruel world ';
Mysql> select @ y;

Mysql> SET @ z = 1 + 2 + 3;
Mysql> select @ z;

2. Use user variables in Stored Procedures

Mysql> create procedure GreetWorld () select concat (@ greeting, 'World ');
Mysql> SET @ greeting = 'hello ';
Mysql> CALL GreetWorld ();

3. 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_procedure );
Mysql> CALL p1 ();
Mysql> CALL p2 ();

?

Iii. operators:
1. Arithmetic Operators
+ ???? Jia ?? SET var1 = 2 + 2 ;?????? 4
-???? Minus ?? SET var2 = 3-2 ;?????? 1
*????? Multiplication ?? SET var3 = 3*2 ;?????? 6
/???? Except ?? SET var4 = 10/3 ;????? 3.3333
DIV ?? Division SET var5 = 10 DIV 3; 3
% ???? Modulo SET var6 = 10% 3 ;???? 1

2. Comparison Operators
> ??????????? Greater than 1> 2 False
<= ?????????? Less than or equal to 2 <= 2 True
>= ?????????? Greater than or equal to 3> = 2 True
BETWEEN ????? BETWEEN two values 5 BETWEEN 1 AND 10 True
Not between two values 5 not between 1 AND 10 False
IN ?????????? 5 IN (,) False IN the Set
Not in ?????? Not in the set 5 not in (,) True
= ???????????? Equal to 2 = 3 False
<> ,! = ?????? Not equal to 2 <> 3 False
<=> ????????? Strictly compare whether two NULL values are equal NULL <=> NULL True
LIKE ????????? Match "Guy Harrison" LIKE "Guy %" True in simple mode
REGEXP ?????? Regular Expression match "Guy Harrison" REGEXP "[Gg] reg" False
Is null ????? NULL 0 is null False
Is not null 0 is not null True

3. logical operators

4. bitwise operators
| ?? Or
&?? And
<Left shift
> Right shift
~?? Non (single object operation, bitwise inversion)

Note:

Mysql stored procedures can be annotated using two styles
Double horizontal bars :--

This style is generally used for single-line comments.
C style:/* Comment content */generally used for multi-line comment

-------------------- Process Control --------------------
I. Ordered Structure
Ii. Branch Structure
If
Case

Iii. Loop Structure
For Loop
While Loop
Loop
Repeat until Loop

Note:
Block definition, common
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.

Begin and end are similar to {And} in C }.

-------------------- Input and Output --------------------

Mysql stored procedure parameters are used IN the definition of stored procedures. There are three parameter types: IN, OUT, And INOUT.
Create procedure | function ([[IN | OUT | INOUT] Parameter Name Data class...])

IN input parameters
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 parameters
This value can be changed within the stored procedure and can be returned

INOUT Input and Output Parameters
Can be changed and returned.

IN parameter example:
Create procedure sp_demo_in_parameter (IN p_in INT)
BEGIN
SELECT p_in; -- Query input parameters
SET p_in = 2; -- modify
Select p_in; -- view the modified value
END;

Execution result:
Mysql> set @ p_in = 1
Mysql> call sp_demo_in_parameter (@ p_in)
Omitted
Mysql> select @ p_in;
Omitted
As shown above, although p_in is modified in the stored procedure, it does not affect the value of @ p_id.

OUT parameter example
Create:
Mysql> create procedure sp_demo_out_parameter (OUT p_out INT)
BEGIN
SELECT p_out;/* view output parameters */
SET p_out = 2;/* modify the parameter value */
SELECT p_out;/* check for any changes */
END;

Execution result:
Mysql> SET @ p_out = 1
Mysql> CALL sp_demo_out_parameter (@ p_out)
Omitted

Mysql> SELECT @ p_out;
Omitted

Example of INOUT parameter:
Mysql> create procedure sp_demo_inout_parameter (INOUT p_inout INT)
BEGIN
SELECT p_inout;
SET p_inout = 2;
SELECT p_inout;
END;

Execution result:
Set @ p_inout = 1
Call sp_demo_inout_parameter (@ p_inout )//
Omitted
Select @ p_inout;
Omitted

?

?

Appendix: function library
Mysql stored procedure basic functions include: string type, numerical type, date type

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

Ii. numeric type

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.57 |
+ ------ +
1 row in set (0.00 sec)

SIGN (number2) // return SIGN, positive and negative or 0
SQRT (number2) // Square

Iii. date type

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

Note: available INTERVAL types: DAY, DAY_HOUR, DAY_MINUTE, DAY_SECOND, HOUR, HOUR_MINUTE, HOUR_SECOND, MINUTE, MINUTE_SECOND, MONTH, SECOND, YEAR
DECLARE variable_name [, variable_name...] datatype [DEFAULT value];?
Datatype is the data type of mysql, such as INT, FLOAT, DATE, VARCHAR (length)

Example:

DECLARE l_int INT unsigned default 4000000 ;?
DECLARE l_numeric NUMERIC (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 ';