Create, delete, and call mysql stored procedures and other common commands _ MySQL

[Go] create, delete, and call mysql stored procedures and other common commands bitsCN.com

Http://hi.baidu.com/jsexp/blog/item/f1398946e2838f066a63e5fa.html

Create, delete, and call mysql stored procedures and other common commands

1. create a stored procedure

1. basic syntax: create procedure sp_name ()
Begin
.........
End

2. parameter transfer

II. call the stored procedure

1. basic syntax: call sp_name ()
Note: the name of a stored procedure must be enclosed by parentheses, even if no parameter is passed for the stored procedure (in fact, it can be left with no parentheses, maybe the reason why my mysql version is newer)

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. 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 statement if condition then
Statement
Else
Statement
End if;

3. loop statements
(1). while loop

[Label:] WHILE expression DO

Statements

End while [label];
(2) loop 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
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.

Operators used in mysql stored procedures

Mysql stored procedure Learning Summary-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
P divisible SET var5 = 10 p 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 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 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)

Common functions in mysq stored procedures, 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: when processing strings in mysql, 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 ('ABC );
+ -------- +
| 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 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 the link below is official and useful http://dev.mysql.com/doc/refman/5.1/zh/stored-procedures.html

BitsCN.com