Mysql stored procedure and storage function _ MySQL

Mysql stored procedure and storage function bitsCN.com

Advantages:

V improves security

V simplify mysql Query

V reduces bandwidth burden

Disadvantages:

V increases server load

V is more difficult for our developers

Functions in PHP

Function funname (parameter ){

// Function body

Return

}

For mysql, if there is a stored program with a returned value, we call it a storage function,

A stored program without return values is called a stored procedure.

The following describes how to use stored procedures.

1.0 quick case:

Delimiter $

// Code block

End $

# Call a stored procedure

Call show_times ();

# Viewing stored procedures

Show procedure status

# View the stored procedure

Show create procedure show_times;

# Deleting a stored procedure

Drop procedure show_times;

1.1 declare separators

DELIMITER is the DELIMITER, because MySQL uses the separator ";" by default. if we do not declare the DELIMITER, the compiler treats the stored procedure as an SQL statement, the compilation process of the stored procedure reports an error. Therefore, you must use the DELIMITER keyword to declare the current segment separator so that MySQL will use ";" as the code in the stored procedure and will not execute the code, after it is used up, the separator should be restored. $, $ ,//

1.2 Parameters

The parameter definition of the MySQL stored procedure. There are three parameter types: IN, OUT, and INOUT. the format is procedure (process)

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

IN input parameter: 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.

IN n int <=> n int

OUT output parameter: the value can be changed within the stored procedure and can be returned.

INOUT input and output parameters: this parameter is specified during the call and can be changed or returned.

I storage program variables:

DECLARE variable_name [, variable_name...] datatype [DEFAULT value];

Datatype is the data type of MySQL, such as int, float, date, varchar (length)

For example:

DECLARE Rochelle int unsigned default 4000000;

DECLARE l_numeric number (9.95) DEFAULT;

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

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

II. variable assignment

SET variable name = expression value [, variable_name = expression...]

Set cannot be forgotten

1.3 # write a stored procedure for producing random strings

Requirement: output a random string based on the given number.

What is the use of learning stored procedures?

1. understand the storage program mechanism.

2. understand the stored programs written by others.

3. write basic storage programs.

4. introduce the built-in functions of mysql.

Mysql has encapsulated a large number of storage programs, which can be used directly during development.

Mysql string functions

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

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]

SQRT (number2) // Square

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) // return the MINUTE sign, positive or negative or 0

BitsCN.com