SQL stored procedure 3

 

4. Notes

MySQL stored procedures can be annotated using two styles

Dual-mode bar: // This style is generally used for single-line comment

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

5. query MySQL stored procedures

As we know the tables under a database, we generally use show tables; for viewing. So can we check whether the stored procedures under a database can be used as well? The answer is: we can view the stored procedures under a database, but it takes only one minute.

We can use

Select name from mysql. proc where db = 'database name ';

Or

Select routine_name from information_schema.routines where routine_schema = 'database name ';

Or

Show procedure status where db = 'database name ';

.

If we want to know the details of a stored procedure, what should we do? Can I use the describe table name for viewing like an operation table?

The answer is: we can view the details of the stored procedure, but we need to use another method:

Show create procedure database. Name of the stored PROCEDURE;

You can view the details of the current stored procedure.

 

6. Modify the MySQL Stored Procedure

ALTER PROCEDURE

Change the pre-specified stored PROCEDURE created with create procedure without affecting the stored PROCEDURE or function.

 

7. Delete the MySQL Stored Procedure

Deleting a stored procedure is simple, just like deleting a table:

DROP PROCEDURE

Delete one or more stored procedures from a MySQL table.

 

8. MySQL stored procedure control statements

(1). variable scope

Internal variables have a higher priority within the scope of their scope, when executed to the end. The internal variable disappears and is out of its scope, and the variable is no longer visible.

The declarative variable can no longer be found outside the process, but you can use the out parameter or assign its value

Session variables to save their values.

(2). Condition Statement

I. if-then-else statement

Ii. case statement:

(3). Loop statement

I. while... end while:

Ii. repeat · end repeat:

Iii. loop · end loop:

The loop does not require the initial conditions. This is similar to the while loop, and does not require the end condition like the repeat loop. The leave statement is used to exit the loop.

Iv. LABLES labels:

The label can be used before the begin repeat while or loop statement. The statement label can only be used before a valid statement. You can jump out of the loop to make the running command the last step of the compound statement.

(4). ITERATE Iteration

I. ITERATE:

1. You can refer to the compound statement label to start a compound statement.

9. basic functions of the MySQL Stored Procedure

 

(1). 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 parameter position must be greater than or equal to 1.

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

(2). 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]

Note: The return type is not an integer, for example:

(1) The default value is integer.

(3). 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

SQRT (number2) // Square

 

 

From song lixing's column