Linux Nine Yin canon of nine yin Bones claw fragment 8 (storage functions, stored procedures, triggers)

Storage functions

Note: Parameters can have more than one parameter, and there must be only one return value.

1. System functions

Reference Official Document: Https://dev.mysql.com/doc/refman/5.7/en/func-op-summary-ref.html

2. Custom Function (user-defined function:udf)

The custom function is saved in the Mysql.proc table

• View the UDF list show Functioin STATUS;

• View UDF definition show CREATE FUNCTION function_name

• Delete UDF drop FUNCTION function_name

• Calling custom Function Syntax SELECT function_name (parameter_value,...)
• Assign a value to a variable SET parameter_name = Value[,parameter_name = value ...]

• View variables SELECT into parameter_name

MariaDB [testdb]>DELIMITER//#修改结束符为//MariaDB [testdb]>CREATE FUNCTION addtwonumber (x SMALLINT UNSIGNED, Y SMALLINT UNSIGNED), RETURNS SMALLINT, BEGIN-& Gt    DECLARE A, b SMALLINT UNSIGNED DEFAULT 10;    SET a = x, b = y;    --RETURN a+b; end//Query OK,0Rows Affected (0.01sec) MariaDB [TestDB]> DELIMITER; #定义完函数后再修改回来MariaDB [TestDB]>SELECT addtwonumber (8,9); #调用UDF求和+-------------------+| Addtwonumber (8,9) |+-------------------+| -|+-------------------+

Third, stored procedures

Stored procedures encapsulate frequently used SQL statements or business logic, and precompilation is saved in the database and called directly from the database when needed, eliminating the need for a compilation process. Increased operating speed while reducing network data transfer volume

Stored procedures: Stored procedures are saved in the Mysql.proc table

Create a stored procedure

CREATE PROCEDURE Sp_name ([Proc_parameter [, Proc_parameter ...]) routime_body

Proc_parameter: [in| Out| INOUT] Parameter_name Type

Where in represents an input parameter, out represents an output parameter, inout means that it can be either input or output; Param_name represents the name of the parameter; type

View the list of stored procedures show PROCEDURE STATUS

View stored procedure definitions show CREATE PROCEDURE sp_name

Calling the stored procedure: Call Sp_name ([Proc_parameter [, Proc_parameter ...])

Note: When there is no parameter, "()" can be omitted and "()" should not be omitted when there are parameters.

Delete stored procedure: Drop PROCEDURE [IF EXISTS] Sp_name

The ALTER statement modifies the stored procedure only to modify the stored procedure's annotations and other insignificant things, cannot modify the stored procedure body, so to modify the stored procedure, the method is to delete the rebuild

Process Control

Process control can be used in stored procedures and functions to control the execution of statements

• IF: Used to make conditional judgments. Executes different statements depending on whether the condition is met
• Case: Used for conditional judgment, can be more complex than if statement condition judgment
• Loop: Executes a specific statement repeatedly, implementing a simple loop
• LEAVE: For jumping out of loop control
• Iterate: Jump out of the loop and then go straight to the next loop
• REPEAT: A conditional-controlled loop statement. When a specific condition is met, the loop statement is jumped out
• While: Conditional control of a loop statement

Trigger

The execution of a trigger does not have a program call, nor is it initiated manually, but there is time to trigger and activate for execution.

Create a Trigger

MySQL CREATE [definer = {User | Current_User}] TRIGGER trigger_name trigger_time trigger_event on tbl_name for each ROW trigger_body

Trigger_name: Name of the trigger

trigger_time:{before | After}, which indicates a trigger before or after an event

trigger_event::{INSERT | UPDATE | DELETE}, the specific event that is triggered

Tbl_name: The trigger acts on the table name

View trigger show TRIGGERS;

Delete trigger drop TRIGGER trigger_name;

Example: Creating a trigger, when you want to INSERT data into a student table, the number of students increases, and the number of students is reduced when the student information is deleted.

MariaDB [laobai]> CREATE TABLE students_info (ID TINYINT (2) notNULL Auto_increment,name VARCHAR (30) DEFAULT null,primary KEY(id)); #创建一张学生信息表 Query OK,0Rows Affected (0.00sec) MariaDB [Laobai]>CREATE TABLE Students_count (Stu_count tinyint (2) default 0); #创建一张学生数量表Query OK,0Rows Affected (0.00sec) MariaDB [Laobai]> Insert intoStudents_countValues(0);#给个初始值为0Query OK,1Row affected (0.00SEC) MariaDB [laobai]>Create Trigger Trigger_students_count_insert, after insert,  on Students_info fo R each row, update Students_count set stu_count=stu_count+1;Query OK,0Rows Affected (0.01sec) MariaDB [Laobai]>CREATE trigger Trigger_students_count_delete, after delete,  on students_info for each row, update Students_count set stu_count=stu_count-1; Query OK,0Rows Affected (0.01Sec

MariaDB [laobai]> Insert Students_info (id,name) VALUES (1,'Tom'),(2,'Maria'); Query OK,2Rows Affected (0.01sec) Records:2Duplicates:0Warnings:0MariaDB [Laobai]>Select* fromStudents_info;+----+-------+| ID | Name |+----+-------+|1| Tom | |2| Maria |+----+-------+2Rowsinch Set(0.00sec) MariaDB [Laobai]>Select* fromStudents_count; #插入记录, triggering events with an increase of 2+-----------+| Stu_count |+-----------+|2|+-----------+1Rowinch Set(0.00sec) MariaDB [Laobai]> Delete fromStudents_infowhereId=1; Query OK,1Row affected (0.01sec) MariaDB [Laobai]>Select* fromStudents_info;+----+-------+| ID | Name |+----+-------+|2| Maria |+----+-------+1Rowinch Set(0.00sec) MariaDB [Laobai]>Select* fromStudents_count; #删除记录, number minus 1.+-----------+| Stu_count |+-----------+|1|+-----------+1Rowinch Set(0.00Sec

Linux Nine Yin canon of nine yin Bones claw fragment 8 (stored functions, stored procedures, triggers)