Basic functions and triggers in MySQL stored procedures

Common functions of MySQL stored procedures

1. String class
CHARSET(str) // returns the string character set
CONCAT (string2 [,... ] ) // Connection string
INSTR (string,substring) // returns the location where substring first appeared in string. No return 0 exists
LCASE (string2) // converted to lowercase
LEFT (string2,length) // Takes length characters from the left side of string2
LENGTH (string) //string length
LOAD_FILE (file_name) // Reads contents from a file
LOCATE (substring, string [,start_position]) is the same as INSTR, but the starting position can be specified
LPAD (string2,length,pad) // Repeat pad at the beginning of string until the string length is length
LTRIM (string2) // Remove the front space
REPEAT (string2,count) // Repeat count times
REPLACE (str,search_str,replace_str) // Replace search_str with replace_str in str
RPAD (string2,length,pad) // pad is used after str until the length is length
RTRIM (string2) // Remove back-end Spaces
STRCMP (string1,string2) // Character-by-character comparison of two string sizes,
SUBSTRING (str, position [,length]) // Starting with position of str, take length characters,
Note: When dealing with strings in mysql, the default is the first character subscript 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) // removes the specified character at the specified position
UCASE (string2) // converted to uppercase
RIGHT(string2,length) // Takes the last length characters of string2
SPACE(count) // generates count Spaces

2. The math class

ABS (number2) // Absolute value
BIN (decimal_number) // Base 10 to base 2
CEILING (number2) // Round up
CONV(number2,from_base,to_base) // base conversion
FLOOR (number2) // Round down
FORMAT (number,decimal_places) // Reserve decimal places
HEX (DecimalNumber) // turn 106
Note: in HEX(), if a string can be passed in, its ASC-11 code is returned; for example, HEX('DEF') returns 4142143
You can also pass in a decimal integer and return its decimal code, such as HEX(25), which returns 19
LEAST (number , number2 [,..] ) // Find the minimum
MOD (numerator,denominator) // Remainder
POWER (number,power) // Index
RAND([seed]) // Random Numbers
ROUND (number [,decimals]) //4 rounded to 5,decimals is the decimal place]
Note: Return types are not all integers, such as:
(1) Becomes the plastic value by default

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) Can set the decimal number and 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) // returns the symbol, plus or minus or 0
SQRT(number2) // Square root

3. Date and time

ADDTIME (date2,time_interval) // Add time_interval to date2
CONVERT_TZ (datetime2,fromTZ,toTZ) // Change time zone
CURRENT_DATE () // Current date
CURRENT_TIME () // Current time
CURRENT_TIMESTAMP () // Current timestamp
DATE (datetime) // returns the date portion of datetime
DATE_ADD (date2, INTERVAL d_value d_type) // Add date or time to date2
DATE_FORMAT (datetime,FormatCodes) // Displays datetime using formatcodes format
DATE_SUB (date2, INTERVAL d_value d_type) // Subtract 1 time from date2
DATEDIFF (date1,date2) // Difference between the two dates
DAY (date) // Days of return date
DAYNAME (date) // English Week
DAYOFWEEK (date) // Week (1-7),1 for Sunday
DAYOFYEAR (date) // Day of the year
EXTRACT (interval_name FROM date) // Extracts the specified part of the date from date
MAKEDATE (year,day) // Gives the year and the day in the middle of the year, and generates the date string
MAKETIME (hour,minute,second) // Generate time string
MONTHNAME (date) // English month name
NOW () // Current time
SEC_TO_TIME (seconds) // Seconds converted into time
STR_TO_DATE (string,format) // string conversion time, displayed in format format
TIMEDIFF (datetime1,datetime2) // Two time differences
TIME_TO_SEC (time) // RPM]
WEEK (date_time [,start_of_week]) // Weeks
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) / / ms
MONTH (datetime) / / month
MINUTE (datetime) / / min

Attachment: Types available 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 the 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 ;

Calling a stored procedure:

CALL PROC_TEST('USER',20);

Delete stored procedure:

DROP PROCUDURE productpricing

2. Create triggers
~ ~ ~ ~ grammar
CREATE TRIGGER < Trigger name > The trigger must have a name, up to 64 characters, and may be followed by a delimiter. It is basically similar to the way other objects in MySQL are named.
{BEFORE | AFTER} -- triggers have a time set for execution: they can be set before or after the event.
INSERT | UPDATE | DELETE} -- you can also set triggered events: they can be triggered during the execution of insert, update, or delete.
ON < The name of the table > A trigger belongs to a table: an insert, update, or delete operation on this table causes the trigger to be activated. We cannot assign two triggers to the same event in the same table.
FOR EACH ROW -- Interval of trigger execution: the FOR EACH ROW clause notifies the trigger to perform an action once every other row, rather than once for the entire table.
< Trigger SQL statement > -- The trigger contains the SQL statement to be fired: The statement here can be any valid statement, including compound statements, but the statement here is restricted to one of the functions.

You must have considerable permissions to create triggers (CREATE TRIGGER). If you are already an Root user, that is enough. This is different from the SQL standard.

Create the trigger :t_afterinsert_on_tab1

Effect: Add tab1 table record to tab2 table automatically

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
Role: Automatically delete the corresponding record in tab2 table after deleting the record in tab1 table

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

0

Delete trigger:

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

1