Stored Procedure syntax Basics
1. Variable
DECLARE declaration, SET Value assignment
Variable type: Code
INT, INTEGER
BIGINT
FLOAT
DOUBLE
DECIMAL (precision, scale), NUMERIC (precision, scale)
DATE
DATETIME
CHAR (length)
VARCHAR (length)
BLOB, TEXT
LONGBLOB, LONGTEXT
You can use DEFAULT to set the DEFAULT value when setting the DECLARE variable. If it is not set, it is NULL.
2. Parameters
The parameter types include IN, OUT, And INOUT.
3. User Variables
User variables starting with @ and sessions in the scope can be used as global variables.
4. Comment
// Single line comment
/*
|
| Multi-line comment
|
*/
5. Operators
Arithmetic Operators
+
-
*
/# The result may be decimal.
Div # The result is an integer.
%
Comparison operator
Code
>
<
<=
> =
Between
Not
In
Not in
=
<> ,! = # Non-equal sign
<=> # Null safe equal (returns true if both arguments are null)
Like
REGEXP
IS NULL
IS NOT NULL
Logical operators
AND
OR
XOR
Bitwise operators
|
&
<
>
~
6. built-in functions
It can be divided into string functions, arithmetic functions, date and time functions, and other functions.
Common MySQL Functions
Code
ABS
CEILING
CONCAT
CURDATE
DATE_ADD
DATE_SUB
FORMAT
GREATEST
IF
IFNULL
INSERT
INSTR
ISNULL
LEAST
LEFT
LENGTH
LOCATE
LOWER
LPAD
LTRIM
MOD
NOW
POWER
RAND
REPEAT
REPLACE
ROUND
RPAD
RTRIM
SIGN
SQRT
STRCMP
SUBSTRING
UPPER
VERSION
7. Data Type
All the variables in MySQL are single elements and there is no array concept.
String type
CHAR: Fixed Length. Space is used for the remaining part. The excess part is truncated. The maximum length is 255 bytes.
Varchar: variable length. The excess part is truncated, and the maximum value is 65532 bytes.
Enum type
Set Type
Set is similar to Enum, but it can have multiple values.
Code
Create procedure sp_set (in_option SET ('yes', 'no', 'maybe '))
BEGIN
SELECT in_option;
END
--------------
CALL sp_set ('yes ')
--------------
+ ----------- +
| In_option |
+ ----------- +
| Yes |
+ ----------- +
1 row in set (0.01 sec)
Query OK, 0 rows affected (0.01 sec)
--------------
CALL sp_set ('yes, No, Maybe ')
--------------
+ -------------- +
| In_option |
+ -------------- +
| Yes, No, Maybe |
+ -------------- +
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
--------------
CALL sp_set ('yes, No, Go away ')
--------------
ERROR 1265 (01000): Data truncated for column 'in _ option' at row 1
Numeric type
Date and datetime types
Date is used to store the date, and datetime is used to store the date and time
Text and blob types
Text can be stored 64 K, longtext can be stored 4G
Blob and longblob are similar, but they can also store binary data.
8, SQL _mode
'Strict _ trans_tables '(default): strictly restrict the Data Type of the transactional table.
'Strict _ all_tables ': imposes strict data type restrictions on all tables.
In strict mode, if a data truncate error occurs (for example, assign a string value to an integer), a raise error occurs, in no strict mode, unexpected behavior occurs (for example, the value of string to integer is changed to 0) and can only be seen in "show warnings.
Code
Mysql> show warnings;
+ --------- + ------ + ------------------------------------------ +
| Level | Code | Message |
+ --------- + ------ + ------------------------------------------ +
| Warning | 1265 | Data truncated for column 'B' at row 1 |
| Warning | 1265 | Data truncated for column 'C' at row 1 |
+ --------- + ------ + ------------------------------------------ +
Stored Procedures should always work in strict mode to avoid unexpected errors.