Mysql stored procedure-basic knowledge _ MySQL

Mysql stored procedure-Basic Knowledge: bitsCN.com

Mysql stored procedure-basic knowledge

Stored procedures, such as the same programming language, also contain data types, process control, input and output, and their own function libraries.

------------------ Basic syntax --------------------

1. create a stored procedure

Create procedure sp_name ()

Begin

.........

End

II. call the stored procedure

1. basic syntax: call sp_name ()

Note: the stored procedure name must be enclosed in parentheses, even if the stored procedure has no parameters

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. 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 mysql stored procedure.

-------------------- Data type and operator --------------------

I. basic data types:

Omitted

2. variables:

Custom variables: DECLARE a INT; SET a = 100; can be replaced by the following statement: DECLARE a int default 100;

Variables are divided into user variables and system variables. system variables are divided into session and full-local variables.

User variable: User variable names generally start with @. misuse of user variables will make the program hard to understand and manage.

1. use user variables on the 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. use user variables in stored procedures

Mysql> create procedure GreetWorld () select concat (@ greeting, 'World ');

Mysql> SET @ greeting = 'hello ';

Mysql> CALL GreetWorld ();

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

III. operators:

1. 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

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 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

3. logical operators

4. bitwise operators

| Or

&

<Left shift

> Right shift

~ Non (single object operation, bitwise inversion)

Note:

Mysql stored procedures can be annotated using two styles

Double horizontal bars :--

This style is generally used for single-line comments.

C style:/* comment content */generally used for multi-line comment

-------------------- Process control --------------------

I. ordered structure

II. branch structure

If

Case

III. loop structure

For loop

While loop

Loop

Repeat until Loop

Note:

Block definition, common

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.

Begin and end are similar to {and} in c }.

-------------------- Input and output --------------------

Mysql Stored Procedure parameters are used IN the definition of stored procedures. There are three parameter types: IN, OUT, and INOUT.

Create procedure | function ([[IN | OUT | INOUT] parameter name data class...])

IN input parameters

Indicates that the value of this parameter must be specified when the stored procedure is called. modifying the value of this parameter in the stored procedure cannot be returned, which is the default value.

OUT output parameters

This value can be changed within the stored procedure and can be returned

INOUT input and output parameters

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; -- Modify

Select p_in; -- view the modified value

END;

Execution result:

Mysql> set @ p_in = 1

Mysql> call sp_demo_in_parameter (@ p_in)

Omitted

Mysql> select @ p_in;

Omitted

As shown above, although p_in is modified in the stored procedure, it does not affect the value of @ p_id.

OUT parameter example

Create:

Mysql> create procedure sp_demo_out_parameter (OUT p_out INT)

BEGIN

SELECT p_out;/* View output parameters */

SET p_out = 2;/* modify the parameter value */

SELECT p_out;/* check for any changes */

END;

Execution result:

Mysql> SET @ p_out = 1

Mysql> CALL sp_demo_out_parameter (@ p_out)

Omitted

Mysql> SELECT @ p_out;

Omitted

Example of INOUT parameter:

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 )//

Omitted

Select @ p_inout;

Omitted

Appendix: Function Library

Mysql stored procedure basic functions include: string type, numerical type, date type

I. string

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 parameter position 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. numeric type

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 type

TO_DAYS () # SELECT TO_DAYS (now ()/365 The result is 2014.8822

YEARWEEK () # select yearweek ('2017-07-18 ') returns 2013

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

Note: available INTERVAL types: 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];

Datatype is the data type of mysql, such as INT, FLOAT, DATE, VARCHAR (length)

Example:

DECLARE Rochelle INT unsigned default 4000000;

DECLARE l_numeric NUMERIC (9.95) DEFAULT;

DECLARE l_date date default '2017-12-31 ';

DECLARE l_datetime datetime default '2017-12-31 23:59:59 ';

DECLARE l_varchar VARCHAR (255) DEFAULT 'this will not be padded ';

BitsCN.com