MySQL 5.0 Stored Procedure
1. CreateStored Procedure
1. Basic Syntax:
Create procedure sp_name ()
Begin
.........
End
2. parameter transfer
Ii. CallStored Procedure
1. Basic Syntax: Call sp_name ()
Note:
Stored ProcedureThe name must be enclosed in parentheses, even if this
Stored ProcedureNo parameter passed
Iii. DeleteStored Procedure
1. Basic Syntax:
Drop procedure sp_name //
2. Notes
(1) cannot be in
Stored ProcedureTo delete another
Stored Procedure, 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
If condition then
Statement
Else
Statement
End if;
3. Loop statements
(1). While Loop
[Label:] While expression do
Statements
End while [label];
(2) loop
[Label:] 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
Display all stored
Stored ProcedureBasic information, including the database,
Stored ProcedureName, creation time, etc.
2. Show create procedure sp_name
Display
Stored ProcedureDetailed information
MySQLStored ProcedureOperators to be used in
- MySQLStored Procedure-Operators
Arithmetic Operators
+ Add set var1 = 2 + 2; 4
-Subtract set var2 = 3-2; 1
* Multiply by 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
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 is 5 between two values between 1 and 10 true
Not between not between two values 5 not between 1 and 10 false
In the set, 5 in (,) is false.
Not in is 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 simple pattern matching "guy Harrison" like "guy %" true
Regexp regular expression match "guy Harrison" Regexp "[Gg] Reg" false
Is null is null 0 is null false
Is not null 0 is not null true
Logical operators
And(And)
And |
True |
False |
Null |
True |
True |
False |
Null |
False |
False |
False |
Null |
Null |
Null |
Null |
Null |
Or (OR)
Or |
True |
False |
Null |
True |
True |
True |
True |
False |
True |
False |
Null |
Null |
True |
Null |
Null |
Exclusive or (XOR)
XOR |
True |
False |
Null |
True |
False |
True |
Null |
False |
True |
False |
Null |
Null |
Null |
Null |
Null |
Bitwise operators
| Bit or
& Bit and
<Left shift
> Right shift
~ Bitwise non (single object operation, bitwise inversion)
MysqStored ProcedureCommon functions, string operations, mathematics, and date and time.
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:
MySQLWhen processing strings, the default subscript of the first character is 1, that is, the position parameter 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. 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) // 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
Appendix: available types in interval
Day, day_hour, day_minute, day_second, hour, hour_minute, hour_second, minute, minute_second, month, second, year