Tutorials on basic functions and triggers in the MySQL stored procedure, mysql Stored Procedure

Tutorials on basic functions and triggers in the MySQL stored procedure, mysql Stored Procedure

Common functions of MySQL stored procedures

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

mysql> select substring('abcd',0,2);

+-----------------------+| substring('abcd',0,2) |+-----------------------+| |+-----------------------+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. 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.

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.57 |+----------------+1 row in set (0.00 sec)

SIGN (number2) // return SIGN, positive and negative or 0
SQRT (number2) // Square

Iii. 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) // MINUTE

Appendix: available types in INTERVAL
DAY, DAY_HOUR, DAY_MINUTE, DAY_SECOND, HOUR, HOUR_MINUTE, HOUR_SECOND, MINUTE, MINUTE_SECOND, MONTH, SECOND, YEAR

MySql stored procedures and triggers
1. Create a stored procedure

DELIMITER //DROP PROCEDURE IF EXISTS `PROC_TEST`//CREATE PROCEDURE `PROC_TEST`(TABLE_NAME VARCHAR(20),NUM INT)BEGIN  SELECT * FROM TABLE_NAME LIMIT NUM;END//DELIMITER ;

Call the stored procedure:

CALL PROC_TEST('USER',20);

Delete stored procedure:

DROP PROCUDURE productpricing

2. Create a trigger
~~ Syntax ~~
Create trigger <TRIGGER Name> -- the TRIGGER must have a name of up to 64 characters, which may be followed by a separator. It is similar to the naming method of other objects in MySQL.
{BEFORE | AFTER} -- trigger has the execution time setting: it can be set to BEFORE or AFTER an event occurs.
{INSERT | UPDATE | DELETE} -- trigger events can also be set: they can be triggered during insert, update, or delete execution.
ON <Table Name> -- a trigger belongs to a table. When an insert, update, or delete operation is performed ON the table, the trigger is activated. we cannot schedule two triggers for the same event of the same table.
For each row -- trigger execution interval: The for each row clause notifies the trigger to execute an action every ROW, instead of executing an action FOR the entire table.
<Trigger SQL statement> -- the trigger contains the SQL statement to be triggered: The statement here can be any legal statement, including compound statements, but the statements here are subject to the same restrictions as those of functions.

-- You must have considerable permissions to CREATE a TRIGGER. If you are a Root user, this is enough. This is different from the SQL standard.
 
Create trigger: t_afterinsert_on_tab1
 
Purpose: automatically add a record to the tab2 table after adding a record to the tab1 table
 

DROP TRIGGER IF EXISTS `t_afterinsert_on_tab1`; CREATE TRIGGER t_afterinsert_on_tab1 AFTER INSERT ON `tab1` FOR EACH ROW BEGIN   insert into tab2(tab2_id) values(new.tab1_id); END;

Create trigger: t_afterdelete_on_tab1
Purpose: Delete the records in Table tab1 and automatically delete the records in Table tab2.

DROP TRIGGER IF EXISTS `t_afterdelete_on_tab1`; CREATE TRIGGER `t_afterdelete_on_tab1` AFTER DELETE ON `tab1` FOR EACH ROW BEGIN   delete from `tab2` where tab2_id=old.tab1_id; END;

Delete trigger:
 

DROP TRIGGER [schema_name.]trigger_name；