MySQL Stored Procedure syntax Basics

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.