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

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

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.

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

2.3 Delete trigger

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

2.4 View triggers

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

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!