Shulou(Shulou.com)05/31 Report--

This article mainly introduces the example analysis of storage functions and triggers in MySQL, which has a certain reference value, and interested friends can refer to it. I hope you will gain a lot after reading this article.

Create a function:

1. The stored function is also one of the procedural objects, similar to the stored procedure.

They are code snippets made up of SQL and procedure statements and can be called from applications and SQL. However, there are some differences:

(1) the stored function cannot have output parameters, because the stored function itself is the output parameter.

Www.2cto.com

(2) you cannot call a storage function with a call statement.

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

two。 Create a stored function using the create function statement.

To see which stored functions are in, you can use the show function satus command (similar to stored procedures). CREATE function syntax format:

CREATE FUNCTION sp_name ([func_parameter [,...]])

Returns type

[characteristic...] Routine_body

Description: the definition format of the stored function is not much different from the stored procedure.

● sp_name is the name of the storage function. Stored functions cannot have the same name as stored procedures.

● func_parameter is a parameter to the storage function, which has only the name and type, and cannot specify IN, OUT, and INOUT. The RETURNS type clause declares the data type of the value returned by the function.

● routine_body is the body of the stored function, also known as the body of the stored function. All the SQL statements used in the stored procedure are also applicable in the stored function, including flow control statements, cursors and so on. However, the body of the storage function must contain a RETURN value statement, and value is the return value of the storage function. This is not in the body of the stored procedure.

Example 1: create a stored function that returns the number of students in the XS table as a result.

Www.2cto.com

DELIMITER $$

CREATE FUNCTION NUM_OF_XS ()

RETURNS INTEGER

BEGIN

RETURN (SELECT COUNT (*) FROM XS)

END$$

DELIMITER

Example 2: create a storage function to delete student numbers that exist in the XS_ KC table but not in the XS table.

DELIMITER $$

CREATE FUNCTION DELETE_STU (XH CHAR (6))

RETURNS BOOLEAN

BEGIN

DECLARE STU CHAR (6)

SELECT name INTO STU FROM XS WHERE student number = XH

IF STU IS NULL THEN

DELETE FROM XS_KCWHERE student number = XH

RETURN TRUE

ELSE

RETURN FALSE

Www.2cto.com

END IF

END$$

DELIMITER

3. Call the created function

After the storage function is created, it is just like the built-in function provided by the system (such as version ()), so the method of calling the storage function is similar, using the SELECT keyword.

The syntax format is: SELECT sp_name ([func_parameter [,...]])

Example: call the storage function in example 1: SELECT NUM_OF_XS ()

Another stored function or stored procedure can be called in a stored function.

Example: create a storage function, call the storage function NAME_OF_STU to get the name of the student number, determine whether the name is "Wang Lin", return Wang Lin's date of birth if not, return "FALSE" if not.

DELIMITER $$

CREATE FUNCTION IS_STU (XH CHAR (6))

RETURNS CHAR (10)

BEGIN

DECLARE NAME CHAR (8)

SELECT NAME_OF_STU (XH) INTO NAME

IF NAME= 'Wang Lin' THEN

RETURN (SELECT date of birth FROM XS WHERE student number = XH)

ELSE

RETURN 'FALSE'

END IF; www.2cto.com

END$$

DELIMITER

4. Delete and modify created functions

The method of deleting a stored function is basically the same as deleting a stored procedure, using DROP FUNCTION statements.

The syntax format is: DROPFUNCTION [IF EXISTS] sp_name

Example: delete the storage function NUM_OF_XS in example 1.

DROP FUNCTION IF EXISTS NUM_OF_XS

It is also possible to modify the characteristics of the storage function using the ALTER FUNCTION statement.

The syntax format is: ALTER FUNCTION sp_name [characteristic...]

Of course, to modify the contents of the storage function, you have to delete it first and define it later.

Trigger

1. Create trigger

Create triggers using the create statement to see which triggers in the database can use the show triggers command.

CREATE TRIGGER syntax format:

CREATE TRIGGERtrigger_name trigger_time trigger_event

ON tbl_nameFOR EACH ROW trigger_stmt

Description:

● trigger_name: name of the trigger, which must have a unique name in the current database. If you want to create it in a particular database, the name should be preceded by the name of the database.

● trigger_time: when a trigger fires, there are two options: AFTER and BEFORE, to indicate that the trigger is fired before or after the statement that activates it. The AFTER option is usually used if you want to make several or more changes after the statement that activates the trigger is executed, or if you want to verify that the new data meets the usage limits, use the BEFORE option. There is no obvious difference in MySQL. Before is similar to after. Www.2cto.com

● trigger_event: trigger event that indicates the type of statement that activates the trigger. Trigger_event can be one of the following values:

INSERT: activates the trigger when a new row is inserted into the table. For example, through INSERT, LOAD DATA, and REPLACE statements.

UPDATE: activates the trigger when a row is changed. For example, through the UPDATE statement.

DELETE: activates the trigger when a row is deleted from the table. For example, through DELETE and REPLACE statements.

● tbl_name: the name of the table associated with the trigger on which a trigger event occurs before the trigger is activated. The same table cannot have two triggers with the same trigger time and event. For example, for a table, you cannot have two BEFORE UPDATE triggers, but you can have 1 BEFORE UPDATE trigger and 1 BEFOREINSERT trigger, or 1 BEFORE UPDATE trigger and 1 AFTER UPDATE trigger.

● FOR EACH ROW: this declaration is used to specify that the trigger action should be activated for each line affected by the triggered event. For example, if you use a statement to add a set of rows to a table, the trigger performs the corresponding trigger action on each row.

● trigger_stmt: trigger action that contains the statement that will be executed when the trigger is activated. If you want to execute multiple statements, you can use BEGIN... END compound statement structure. In this way, you can use the same statements allowed in the stored procedure.

Note: triggers cannot return any results to the client. To prevent results from being returned from triggers, do not include SELECT statements in the trigger definition. Similarly, stored procedures that return data to the client cannot be called.

2. New. List, old. List usage

The SQL statement in a MySQL trigger can associate any column in the table. However, you cannot directly use the name of the column to flag, which can confuse the system, because the statement that activates the trigger may have modified, deleted, or added a new column name, and the old column name exists at the same time. Therefore, it must be marked with this syntax: "NEW.column_name" or "OLD.column_name". NEW.column_name is used to reference a column of a new row, and OLD.column_name is used to refer to a column of an existing row before it is updated or deleted.

For INSERT statements, only NEW is legal; for DELETE statements, only OLD is legal; and UPDATE statements can be used with NEW or OLD.

Example: create a trigger that deletes all data related to a student in the XS_ KC table when deleting the information of a student in the table XS. Www.2cto.com

DELIMITER $$

CREATE TRIGGERXS_DELETE AFTER DELETE

ON XS FOR EACH ROW

BEGIN

DELETE FROM XS_KC WHERE student number = OLD. Student number

END$$

DELIMITER

Now verify the function of the trigger: DELETE FROM XS WHERE student number = '081101'

Use the select statement to view the situation in the XS_ KC table: SELECT * FROM XS_KC

Note: when triggers involve updating the trigger itself, only BEFORE,AFTER triggers will not be allowed.

3. Delete trigger www.2cto.com

Like other database objects, triggers can be removed from the database using the drop statement. Syntax format: DROP TRIGGER [schema _ name.] trigger_name

Description: trigger_name: refers to the name of the trigger to delete. Schema_name is the name of the database in which it is located, which can be omitted if it is in the current database.

Example: delete trigger XS_DELETE: DROP TRIGGERXS_DELETE

Thank you for reading this article carefully. I hope the article "sample Analysis of Storage functions and triggers in MySQL" shared by the editor will be helpful to you. At the same time, I also hope you will support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.