MySQL syntax collation (5) -- functions, stored procedures and triggers

This chapter explains functions , Creation and basic use of stored procedures and triggers .

MySQL The function of is used to realize certain functions , Limited by the fact that the return parameter can only be a single , The functions realized are relatively limited .

/* A function that returns a single query value ,MySQL  Only this function can be used 
 Given the name of a department , Number of teachers returning to the Department */
USE university; 
SET GLOBAL log_bin_trust_function_creators = 1; --  Master slave replication is not enabled  
DELIMITER // --  Set the end symbol of the command segment to // 
CREATE FUNCTION dept_count( dept_name VARCHAR(20)) --  The parameters of the function 
	RETURNS INT /* return type */ 
	BEGIN	
		DECLARE d_count INT;  /* Declare parameters and their types */ 
		SELECT COUNT(*) 
		FROM instructor 
		WHERE instructor.dept_name = dept_name
		INTO d_count; /* Assign parameters */ 
		RETURN d_count; /* A function that returns a single query value ,MySQL  Only this function can be used */
	END;// 
DELIMITER ;

/* Call function */
SELECT dept_count('Comp. Sci.');

--  This is another way , Load the result of the function into @ss in 
SELECT dept_count('Comp. Sci.') INTO @ss; 
SELECT @ss

/* Here is an operation to put table records into a function 
 The number of returned teachers is greater than  1  Name and budget of all departments */
SELECT dept_name,budget 
FROM department 
WHERE dept_count(department.`dept_name`) > 1;

We were just writing a function , Mentioned the definition of variables , Next, let's talk about the definition of variables .declare： Defining local variables .set： Assign a value to a variable .

/*declare  Statement is used to define local variables , have access to  default  To describe the default values , Its role 
 The field is limited to this statement block , Commonly used in functions 、 In a stored procedure or trigger .*/
DECLARE age INT DEFAULT 0; --  Direct execution will report an error 
SET age = 18;

/*mysql  User variables ,mysql  User variables in do not need to be declared in advance , Direct use “@ Variable name ” Use 
 That's all right. , Its scope is the current connection .*/
SET @age=19;

/* Global variables affect the overall operation of the server . When the service starts , It initializes all global variables to 
 The default value is . To change global variables , Must possess  super  jurisdiction [email protected]@ Represents a global variable .*/
SET GLOBAL SQL_WARNINGS = ON; -- global  Don't omit  
--  Or another way of writing 
SET @@global.sql_warnings = OFF --  This variable exists once the machine is started 

delimiter： Redefine the Terminator .

/* Redefine command end symbol 
SQL  The default terminator is a semicolon ;*/
DELIMITER // --  Set the end symbol of the command segment to  //
DELIMITER ; --  Finally, remember to wrap the terminator back ;

And then again MySQL Common functions .

 String function ： CONCAT(), LOWER(),UPPER(),LEFT(),TRIM(),REPEAT()
 Numerical function ： ABS(),CEIL(),FLOOR(),MOD(),RAND() 
 Date time function ： CURDATE(),NOW(),YEAR()
 System variables ： DATABASE(),VERSION(),USER() ,MD5()

Next we're going to MySQL Stored procedures in , In fact, stored procedures are similar to functions , Let's talk about the difference between the two first .

One 、 Meaning is different

1、 stored procedure ： The stored procedure is SQL Statement and optional control flow statement , Store under a name and process as a unit .

2、 function ： By one or more SQL A subroutine consisting of statements , Can be used to encapsulate code for reuse . There are a lot of function restrictions , If you can't use a temporary table , You can only use table variables, etc

Two 、 Different conditions of use

1、 stored procedure ： A series of can be executed in a single stored procedure SQL sentence . And you can reference other stored procedures from your own stored procedures , This can simplify a series of complex statements .

2、 function ： Custom functions have many limitations , There are many statements that cannot be used , Many functions cannot be realized . Function can directly reference the return value , Use table variables to return recordsets . however , User defined functions cannot be used to perform a set of operations that modify the state of a global database .

3、 ... and 、 Different execution methods

1、 stored procedure ： Stored procedures can return parameters , Such as recordset , Functions can only return values or table objects . The parameters of the stored procedure are in,out,inout Three , The return type is not required when the stored procedure is declared .

2、 function ： Function parameters only have in, The function needs to describe the return type , And the function must contain a valid return sentence .

After understanding the stored procedure , We probably know , Functions can only perform simple operations , The stored procedure can complete the operation of multiple tables .

/* Given the name of a department , Number of teachers returning to the Department .*/
DROP PROCEDURE IF EXISTS dept_count_p; 
DELIMITER // 
CREATE PROCEDURE dept_count_p(IN dept_name VARCHAR(20),OUT d_count INT ) 
/*IN  Represents the input parameter ,OUT  Parameters representing the output , The output parameter name is d_count*/
BEGIN 
	SELECT COUNT(*) INTO d_count 
	FROM instructor 
	WHERE instructor.dept_name=dept_name ; 
END;// 
DELIMITER ;

/* Call the stored procedure directly ： Execute stored procedures on the database server side */
CALL dept_count_p('Comp. Sci.',@d_count); --  The output value after calling is d_count, Reference to be added @
SELECT @d_count

/* Calling stored procedure in middle note function */
DELIMITER // 
CREATE FUNCTION test(dept_name VARCHAR(20)) RETURNS VARCHAR(100) 
BEGIN 
	CALL dept_count_p(dept_name,@d_number); --  Calling stored procedure  
	IF @d_number >= 1 THEN 
		RETURN CONCAT('the number of this department teacher is: ',
		CAST(@d_number AS CHAR)); --  Connect two strings  
	ELSE 
		RETURN 'this department teacher is 0'; 
	END IF; 
END;// 
DELIMITER ;

/* Call function */
SELECT test('Music');

Stored procedures can operate on multiple tables , You can also use T-SQL Language （ Be similar to python） Implement some functions

/* use  T-SQL  Language completion  1+2+3……+n, And print out the results */
DELIMITER // 
CREATE PROCEDURE MY_Sum(IN n INT, OUT result INT) 
BEGIN 
	DECLARE i INT DEFAULT 1; 
	DECLARE SUM INT DEFAULT 0; 
	WHILE i <= n DO 
		SET SUM = SUM + i;  --  The assignment of each variable should use set
		SET i = i + 1; 
	END WHILE; 
	SET result = SUM; 
END;// 
DELIMITER ;

/* Calling stored procedure */
CALL my_sum(50,@result); 
SELECT @result;

/* Stored procedure compared to function , The biggest difference is that it cannot be called by others , And functions can .
 Generally, functions are used to realize a single function , Then use the storage structure to realize a section of business logic （ To sign up , Shift fee , Order warehousing ）*/

trigger ： It can be understood as a program that is executed after certain operations are performed , For example, when modifying the data of a table , I can create a trigger to record the modified data into another table .

Trigger involves multiple parameters ：

• about INSERT sentence , Only NEW It's legal.
• about DELETE sentence , Only OLD It's legal
• about UPDATE sentence ,NEW、OLD Can be used at the same time

/* Create two experiment tables for the trigger */
USE university; 
CREATE TABLE test1(id VARCHAR(5)PRIMARY KEY ); --  Create table  
CREATE TABLE test2(id VARCHAR(5)PRIMARY KEY ); --  Create table 

/* Create trigger  tr1, The trigger event is  INSERT, The function is to record tr1 Newly added id*/
DELIMITER // --  Set the end symbol of the command segment to //, If you don't add this, you may report an error  
CREATE TRIGGER tr1 
AFTER INSERT ON student --  The trigger time is inserted student After the table 
FOR EACH ROW -- mysql Statement triggers are not supported , So you have to write foreachrow
BEGIN 
	INSERT INTO test1 VALUES(New.ID); --  The newly recorded  id  Value insertion  test1  surface  
END;//

/* Create trigger  tr2, The trigger event is  update*/
DELIMITER //
CREATE TRIGGER tr2 
AFTER UPDATE ON student --  The trigger time is updated student After the table 
FOR EACH ROW 
BEGIN 
	INSERT INTO test2 VALUES(OLD.ID); --  The previous... Will be modified  id  Value insertion  test2  surface  
END;// 
DELIMITER ;

--  Test triggers tr1
INSERT INTO student VALUES('s0001','Jack','Music',NULL);
--  Test triggers tr2
UPDATE student SET NAME='Mary' WHERE id='s0001';

/* Set a trigger tr3, Enables the detection of new inputs section surface time_slot_id Must already exist , otherwise 
 Just report a mistake */
DELIMITER // 
CREATE TRIGGER tr3 
BEFORE INSERT ON section  --  The operation time here is before, That is, before insertion 
FOR EACH ROW 
BEGIN 
	DECLARE P_CNT INT; 
	SELECT COUNT(*) INTO P_CNT 
	FROM time_slot 
	WHERE time_slot_id = NEW.time_slot_id; 
	--  Condition not satisfied , Can't be in  section  Insert records in the table , New records need to be processed  
	IF (P_CNT <= 0) THEN 
		SET new.time_slot_id=''; --  Set to null  
		--  Deliberately execute the wrong statement , Let the system report an error , Automatically roll back new records  
		INSERT INTO xxxx VALUES (1); 
	END IF; 
END; // 
DELIMITER ;

Next, test trigger three

--  First look at the original table 
SELECT * FROM section; 
--  Insert an error record 
INSERT INTO section VALUES ('CS-321', '2', 'Spring', '2014', 'Taylor', '3128', 'J'); 
--  The record is not inserted after an error is found 
SELECT * 
FROM section 
WHERE course_id='CS-321';