How to customize functions and triggers in MySQL

1. Stored function (custom function)

• Custom function is a way to extend MySQL, and its usage is the same as the built-in function.

• Two necessary conditions for defining a function: parameters and return value. Functions can return values ​​of any type and can also receive parameters of these types.

About the function body:

• The function body is composed of legal SQL statements.

• The function body can be a simple SELECT or INSERT statement.

• If the function body conforms to the structure, it is wrapped with the BEGIN....AND statement.

• Compound structures can contain declarations, loops, control structures, etc.

Important: Custom functions cannot have the same name, just like defining a global variable, the variable names cannot be consistent.

1.1 Define stored function

Syntax format:

create function 函数名(参数列表)
returns type(返回值类型)
begin
    --SQL语句
end;

Requirements: Define a function of a stored procedure, Get the total number of records that meet the conditions

Implementation:

delimiter $
create function fun(countryId int)
returns int 
begin
    # 定义一个存储总数据条数的变量
    declare cum int default 0;
    # 查询等于传递参数的全部的数据数，然后将其赋值给定义的变量
    select count(*) into cum from city where country_id = countryId;
    # 返回结果值。存储函数必须有返回值
    return cum;
end $

delimiter ;

1.2 Call the storage function

Syntax format:

select 函数名(参数列表);

Note: The call keyword is used when calling a stored procedure, but you can directly use select when calling a stored function, just like calling an ordinary aggregate function in MySQL.

select fun(1);
# 这里和存储过程一样，调用的时候需要加小括号和参数，但是在删除的时候指定函数名即可

1.3 Delete stored function

Syntax format

drop [if exists] function fun;

2. Trigger

2.1 Trigger introduction

• A trigger is a database object related to a table, which refers to triggering and executing the SQL set defined in the trigger before or after insert/update/delete. This feature of triggers can help applications ensure data integrity, logging, and data verification on the database side.

• Triggers can only be used when adding, deleting, or modifying, but not when querying.

• In triggers, use the OLD and NEW aliases to reference the changed parts of the record, similar to other databases. MySQL triggers only support row-level triggering and do not support statement-level triggering. Oracle supports both row-level and statement-level triggers.

• The two variables OLD and NEW are also called row record variables.

  You can use these two variables to obtain the data in the data table to be operated.

##2.2 Create trigger

Syntax format:

create trigger(触发器) trigger_name(触发器名称)
before/after insert/update/delete
on tab_name(表名)
[for each row](行级触发器)
begin
    trigger_stmt;(触发器的逻辑)
end;

Requirements: Record the data change log emp_logs of the emp table through triggers, which includes addition, modification, and deletion

Implementation:

Analysis:

A trigger can only operate one type of data operation, and cannot complete the operations of adding, modifying, and deleting at the same time. Therefore, multiple triggers need to be defined at this time to complete this logging task.

Because MySQL is a trigger for row-level operations, new and old store an entire row of data.

Create a trigger to execute insert:

• In insert mode , use the keyword new to get the data to be inserted

• The after is used, and this trigger is executed to record the log after the addition of the table emp is completed.

• When this trigger is executed depends on two points:

Must operate It is the emp table, which is the table declared after on.
Must perform insert operation.

Create a trigger to execute update:

At this time the old variable The data before modification is stored in the new variable, and the data after modification is stored in the new variable.

Create a trigger to execute delete:

The data to be deleted stored in the old variable at this time

Test: The test must be the emp table of the operation, so that the above will be triggered Defined trigger.

2.3 Delete trigger

Syntax structure:

drop trigger [schema_name.](数据库名)trigger_name(触发器名);

If not specified schema_name (database name), defaults to the current database.

2.4 View triggers

You can view the status, syntax and other information of triggers by executing the show triggers command.

Grammar structure:

show triggers;

The above is the detailed content of How to customize functions and triggers in MySQL. For more information, please follow other related articles on the PHP Chinese website!