MySQL stored procedure syntax and examples

Stored procedures, like a programming language, also contain data types, process controls, inputs and outputs, and its own library of functions.

--------------------Basic Syntax--------------------

I. Creating a stored Procedure
CREATE PROCEDURE Sp_name ()
Begin
.........
End

Two. Calling a stored procedure
1. Basic syntax: Call Sp_name ()
Note: The stored procedure name must be appended with parentheses, even if the stored procedure has no parameters passed

Three. Delete a stored procedure
1. Basic syntax:
drop procedure sp_name//

2. Precautions
(1) cannot delete another stored procedure in one stored procedure, only another stored procedure can be called

Four. Other common commands

1.show Procedure Status
Displays basic information about all stored procedures in the database, including the owning database, stored procedure name, creation time, etc.

2.show CREATE PROCEDURE Sp_name
Show details of a MySQL stored procedure

--------------------data types and operators--------------------
First, the basic data type:
Slightly

Second, variable:

Custom variable: DECLARE a INT;    SET a=100; The following statements can be substituted: DECLARE a INT DEFAULT 100;

Variables are divided into user variables and system variables , and system variables are divided into session and global level variables.

User variables: User variable names usually begin with @, abusing user variables can cause the program to be difficult to understand and manage

1. Use user variables in 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. Using user variables in stored procedures

Mysql> CREATE PROCEDURE greetworld () SELECT CONCAT (@greeting, ' world ');
mysql> SET @greeting = ' Hello ';
Mysql> call Greetworld ();

3. User variables that pass 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 ();

Third, Operator:
1. Arithmetic operators
+ Add SET var1=2+2; 4
-Minus SET var2=3-2; 1
* Multiply SET var3=3*2; 6
/except SET VAR4=10/3; 3.3333
div divisible SET var5=10 Div 3; 3
% modulus SET var6=10%3; 1

2. Comparison operators
> Greater than 1>2 False
< less than 2<1 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 True
Not between is not between two values 5 not between 1 and False
In Set 5 in (1,2,3,4) False
Not in the collection 5 not in (1,2,3,4) True
= equals 2=3 False
<>! = is not equal to 2<>3 False
<=> strictly compares two NULL values for equality Null<=>null True
Like simple pattern matches "guy Harrison" like "guy%" True
REGEXP regular Match "Guy Harrison" REGEXP "[Gg]reg" False
Is null 0 is null False
Is isn't null 0 is not NULL True

3. Logical operators

4. Bitwise operators
| Or
& and
<< left Shift
>> Right Shift
~ Non (single-mesh operation, bitwise negation)

Comments:

MySQL stored procedures can use two styles of annotations
Double horizontal bar:--

This style is typically used for single-line annotations
C-style:/* Comment content */General for multi-line comments

--------------------Process Control--------------------
First, sequential structure
Two, branch structure
If
Case

Third, the cycle structure
For loop
While loop
Loop loop
Repeat until cycle

Note:
Block definitions, commonly used
Begin
......
End
You can also alias chunks, such as:
Lable:begin
...........
End lable;
You can use leave lable, jump out of chunks, execute code after chunk

Begin and end are like {and} in the C language.

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

The parameters of the MySQL stored procedure are used in the definition of stored procedure, there are three kinds of parameter types, in,out,inout
Create procedure|function ([[In | Out | INOUT] Parameter name Data class ...])

In input parameters
The value that represents the parameter must be specified when the stored procedure is called, and the value of the parameter that is modified in the stored procedure cannot be returned as the default value

Out output parameters
This value can be changed inside the stored procedure and can be returned

INOUT Input/Output parameters
Called when specified, and 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; --Modification
Select p_in;--View the modified values
END;

Execution Result:
Mysql> Set @p_in =1
Mysql> call Sp_demo_in_parameter (@p_in)
Slightly
Mysql> Select @p_in;
Slightly
As you can see, p_in is modified in the stored procedure, but does not affect the value of @p_id

Examples of Out parameters
Create:
Mysql> CREATE PROCEDURE sp_demo_out_parameter (out P_out INT)
BEGIN
SELECT p_out;/* View OUTPUT parameters */
SET p_out=2;/* Modify parameter values */
SELECT p_out;/* See if there are any changes */
END;

Execution Result:
mysql> SET @p_out =1
Mysql> call Sp_demo_out_parameter (@p_out)
Slightly

Mysql> SELECT @p_out;
Slightly

InOut parameter Examples:
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)//
Slightly
Select @p_inout;
Slightly

Attached: Library of functions
MySQL stored procedure basic functions include: String type, numeric type, date type

first, the String class
CHARSET (str)//return string character set
CONCAT (string2 [,...])//connection string
INSTR (string, substring)//returns the position where substring first appeared in string, no return 0
LCASE (string2)//Convert to lowercase
Left (string2, length)//The length of the string from string2
Length (String)//string
Load_file (file_name)//read content from File
LOCATE (substring, string [, Start_position]) same as InStr, but can specify start position
Lpad (string2, length, pad)//repeat pad to start with string until string length
LTRIM (string2)//Remove front-end spaces
REPEAT (string2, count)//Repeat Count times
Replace (str, SEARCH_STR, REPLACE_STR)//replaces SEARCH_STR with REPLACE_STR in str
Rpad (string2, length, pad)//after STR with pad supplement until length
RTRIM (string2)//Remove back-end spaces
STRCMP (string1, string2)//character comparison two string size,
SUBSTRING (str, position [, length])//starting with the position of STR, taking a length character,
Note: When working with strings in MySQL, the default first character subscript is 1, that is, the parameter position must be greater than or equal to 1
mysql> Select substring (' ABCD ', 0,2);
+ ——————— –+
| SUBSTRING (' ABCD ', 0,2) |
+ ——————— –+
| |
+ ——————— –+
1 row in Set (0.00 sec)

mysql> Select substring (' ABCD ',);
+ ——————— –+
| SUBSTRING (' ABCD ', up) |
+ ——————— –+
| AB |
+ ——————— –+
1 row in Set (0.02 sec)

TRIM ([[[Both| Leading| TRAILING] [padding] from]string2)//remove specified characters from the specified position
UCASE (string2)//Convert to uppercase
Right (String2,length)//Take string2 last length character
Space (count)//Generate Count of spaces

Second, the value type

ABS (NUMBER2)//Absolute value
BIN (Decimal_number)//decimal into binary
CEILING (NUMBER2)//Up rounding
CONV (number2,from_base,to_base)//Binary conversion
Floor (NUMBER2)//Down rounding
FORMAT (number,decimal_places)//number of decimal digits reserved
Hex (Decimalnumber)//Turn hex
Note: Hex () can pass in a string, then return its ASC-11 code, such as Hex (' DEF ') return 4142143
You can also pass in a decimal integer, returning its hexadecimal encoding, such as Hex (25) to return 19
LEAST (number, number2 [,..])//Find minimum
MOD (numerator, denominator)//redundancy
Power (number, Power)//Index
RAND ([seed])//random number
ROUND (number [, decimals])//rounded, decimals to decimal place]

Note: Return types are not all integers, such as:

(1) The default becomes an integer value
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) You can set the number of decimal digits, return the 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 symbol, plus or minus 0
SQRT (NUMBER2)//Open Square

Iii. Type of date

Addtime (Date2, Time_interval)//Add Time_interval to Date2
Convert_tz (DateTime2, Fromtz, Totz)//Convert time zone
Current_date ()//Current date
Current_time ()//Current time
Current_timestamp ()//current timestamp
Date (datetime)//Return datetime part
Date_add (Date2, INTERVAL d_value d_type)//Add date or time to Date2
Date_format (datetime, Formatcodes)//Use formatcodes format to display datetime
Date_sub (Date2, INTERVAL d_value d_type)//Subtract one time from Date2
DATEDIFF (Date1, Date2)//Two date difference
Day (date)/days of return date
Dayname (date)//English Week
DAYOFWEEK (date)//week (1-7), 1 for Sunday
DayOfYear (date)//day of the year
EXTRACT (interval_name from date)//Extract the specified part of the date
Makedate (year, day)//gives the first days of the years and years, generating a date string
Maketime (hour, minute, second)//Generate time string
MONTHNAME (date)//English month name
Now ()//Current time
Sec_to_time (seconds)//seconds turn into time
Str_to_date (string, format)//string turns into time, displayed in format
Timediff (datetime1, datetime2)//Two time difference
Time_to_sec (time)//times to seconds]
WEEK (Date_time [, Start_of_week])//weeks
Year (DateTime)//Years
DayOfMonth (DateTime)/day of the month
HOUR (DateTime)//hour
Last_day (date)//date The last date of the month
Microsecond (DateTime)//microseconds
Month (datetime)//month
MINUTE (DateTime)//min

Note: Types available in interval: 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];
Where datatype is the data type of MySQL, such as: INT, FLOAT, DATE, VARCHAR (length)

Cases:

DECLARE l_int int unsigned default 4000000;
DECLARE l_numeric Numeric (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 is not being padded ';

MySQL stored procedure syntax and examples