Methods for creating stored functions and setting triggers in MySQL

Stored functions are also one of the procedural objects, similar to stored procedures. These code snippets contain SQL and procedural statements that can be called from applications and SQL. However, they also have some differences:

1. The storage function has no output parameters, because the storage function itself is the output parameter.

2. The CALL statement cannot be used to call stored functions.

3. The stored function must contain a RETURN statement, and this special SQL statement is not allowed to be included in the stored procedure

1. Create a stored function

Use CREATE FUNCTION Statement to create a stored function

Syntax format:

CREATE FUNCTION Storage function name ([parameters[,...]])
RETURNS type
Function body

Note: Stored functions cannot have the same name as stored procedures. The stored function body must contain a RETURN value statement, and the value is the return value of the stored function.

Example: Create a stored function that returns the number of books in the Book table as the result

DELIMITER $$
CREATE FUNCTION num_book()
RETURNS INTEGER
BEGIN
RETURN(SELECT COUNT(*)FROM Book);
END$$
DELIMITER ;

When the RETURN clause contains a SELECT statement, the return result of the SELECT statement can only be one row and can only be There is a column of values. Even if the stored function does not require parameters, you need to use () when calling it, for example: num_book().

Example: Create a stored function to delete records that exist in the Sell table but not in the Book table

DELIMITER $$
CREATE FUNCTION del_sell(book_bh CHAR(20))
RETURNS BOOLEAN
BEGIN
DECLARE bh CHAR(20);
SELECT 图书编号 INTO bh FROM Book WHERE 图书编号=book_bh;
IF bh IS NULL THEN
DELETE FROM Sell WHERE 图书编号=book_bh;
RETURN TRUE;
ELSE
RETURN FALSE;
END IF;
END$$
DELIMITER ;

The stored function gives the book number as the input parameter, first press the given book The number is searched in the Book table to see if there is a book with the book number. If there is no book, return false. If there is, return true. At the same time, the book with this book number must be deleted from the Sell table. To list the stored procedures in the database, use the SHOW FUNCTION STATUS command.

2. Call the stored function

After the stored function is created, the method of calling the stored function is the same as using the built-in function provided by the system, using the SELECT keyword.

Syntax format:

SELECT storage function name ([parameters[,...]])

Example: Create a storage function publish_book, Obtain the author of the book by calling the storage function author_book, and determine whether the author's surname is "Zhang". If so, the publication time will be returned; if not, "unsatisfactory" will be returned.

DELIMITER $$
CREATE FUNCTION publish_book(b_name CHAR(20))
RETURNS CHAR(20)
BEGIN
DECLARE name CHAR(20);
SELECT author_book(b_name)INTO name;
IF name like'张%' THEN
RETURN(SELECT 出版时间 FROM Book WHERE 书名=b_name);
ELSE
RETURN'不合要求';
END IF;
END$$
DELIMITER ;

Call the stored function publish_book to view the results:

SELECT publish_book('Computer Network Technology');

Methods to delete stored functions and delete storage The method of the process is basically the same, using the DROP FUNCTION statement

Syntax format:

DROP FUNCTION [IF EXISTS] stores the function name

Note: IF EXISTS The clause is an extension of MySQL. If the function does not exist, it prevents errors from occurring.

Example: Delete stored function a

DROP FUNCTION IF EXISTS a;

3. Create a trigger

Use the CREATE TRIGGER statement Create a trigger

Syntax format:

CREATE TRIGGER trigger name trigger time trigger event
ON table name FOR EACH ROW trigger action

Triggers have two triggering options: BEFORE and AFTER, which respectively indicate that the trigger is triggered before or after the statement that activates it. Typically the AFTER option is used to execute the statement after activating the trigger. The BEFORE option is used to verify that the new data complies with usage restrictions.

Triggers containing SELECT statements will return results to the client. To avoid this situation, you should avoid using SELECT statements in trigger definitions. Likewise, stored procedures that return data to the client cannot be called.

Example: Create a table table1 with only one column a, create a trigger on the table, and set the value of the user variable str to TRIGGER IS WORKING during each insertion operation.

CREATE TABLE table1(a INTEGER);
CREATE TRIGGER table1_insert AFTER INSERT
ON table1 FOR EACH ROW
SET@str='TRIGGER IS WORKING';

To see which triggers are in the database, use the SHOW TRIGGERS command.

SQL statements in MySQL triggers can be associated with any column in the table. But you cannot directly use the name of the column to mark it, which will confuse the system, because the statement that activates the trigger may have modified, deleted, or added a new column name, while the old name of the column exists at the same time. Must be identified using this syntax: NEW.column_name or OLD.column_name. NEW.column_name is used to refer to a column of a new row, and OLD.column_name is used to refer to a column of an existing row before updating or deleting it.

For INSERT statements, only NEW is legal, and for DELETE statements, only OLD is legal. The UPDATE statement can be used simultaneously with NEW and OLD.

Create a trigger so that when the information about a book in the table "Book" is deleted, all data in the "Sell" table related to the book will also be deleted.

DELIMITER $$
CREATE TRIGGER book_del AFTER DELETE
ON Book FOR EACH ROW
BEGIN
DELETE FROM Sell WHERE 图书编号=OLD.图书编号;
END$$
DELIMITER ;

When the trigger wants to trigger the update operation of the table itself, only the BEFORE trigger can be used, and the AFTER trigger will not be allowed.

4. Call the stored procedure in the trigger

Example: Assume that there is a table member_b in the Bookstore database with the same structure as the Members table. Create a trigger and add data to the Members table. When , call the stored procedure to synchronize the data in the member_b table with the Members table.

1. Define the stored procedure: create a table member_b with the same structure as the Members table

DELIMITER $$
CREATE PROCEDURE data_copy()
BEGIN
REPLACE member_b SELECT * FROM Members;
END$$

2. Create a trigger: call the stored procedure data_copy()

DELIMITER $$
CREATE TRIGGER members_ins AFTER INSERT
ON Members FOR EACH ROW
CALL data_copy();
DELIMITER ;

5 , Delete trigger

Syntax format:

DROP TRIGGER Trigger name

Example: Delete trigger members_ins

DROP TRIGGER members_ins;

The above is the detailed content of Methods for creating stored functions and setting triggers in MySQL. For more information, please follow other related articles on the PHP Chinese website!