MySQL 7-2-storage functions and triggers-MySQL

MySQL 7-2-storage functions and trigger bitsCN.com creation functions: 1. storage functions are also a procedural object, similar to stored procedures. They are code snippets composed of SQL and procedural statements and can be called from applications and SQL. However, they also have some differences: (1) the storage function cannot have output parameters, because the storage function itself is the output parameter; (2) The CALL statement cannot be used to CALL the storage function; (3) a stored function must contain a RETURN statement, but this special SQL statement cannot be included in the stored procedure. 2. create a storage function using the CREATEFUNCTION statement. You can run the show function satus command (similar to the stored procedure) to view the stored functions in the database ). Syntax format of CREATE function: create function sp_name ([func_parameter [,...]) returns type [characteristic...] routine_body description: the definition format of the stored function is not much different from that of the stored procedure. ● Sp_name is the name of the storage function. A stored function cannot have the same name as a stored procedure. ● Func_parameter is a parameter of the stored function. the parameter has only the name and type, and the IN, OUT, and INOUT parameters cannot be specified. The RETURNS type clause declares the data type returned by the function. ● Routine_body is the subject of the stored function, also called the storage function body. all SQL statements used in the stored procedure are also applicable to the stored function, including flow control statements and cursors. However, the stored function body must contain a RETURN value statement, and value is the RETURN value of the stored function. This is not in the stored procedure body. Example 1: create a storage function that returns the number of students in the XS table as the result. DELIMITER $ create function NUM_OF_XS () returns integer begin return (select count (*) from xs); END $ DELIMITER; Example 2: create a storage function to delete the student ID that exists in the XS_KC table but does not exist 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 ID = XH; if stu is null then delete from XS_KCWHERE student ID = XH; return true; else return false; end if; END $ DELIMITER; 3. after the created function storage function is called, it is like a built-in function (such as version () provided by the system. Therefore, the method for calling the storage function is similar, and the SELECT keyword is used. Syntax format: SELECT sp_name ([func_parameter [,...]) example: call the storage function in Example 1: SELECT NUM_OF_XS (); you can also call another storage function or stored procedure in the storage function. For example, create a storage function and call the storage function NAME_OF_STU to obtain the student ID and determine whether the name is "Wang Lin". if yes, return the birth date of Wang Lin. if not, return "FALSE ". 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 line' then return (SELECT date of birth from xs where student ID = XH); else return 'false'; end if; END $ DELIMITER; 4. the method for deleting and modifying a created FUNCTION to delete a stored FUNCTION is basically the same as that for deleting a stored procedure. the drop function statement is used. Syntax format: DROPFUNCTION [if exists] sp_name example: delete the storage function NUM_OF_XS in example 1. Drop function if exists NUM_OF_XS; also, the feature of the stored FUNCTION can be modified using the alter function statement. Syntax format: alter function sp_name [characteristic...] of course, to modify the content of a stored FUNCTION, you must first delete it and then define it. Trigger 1. create a trigger and create a trigger using the CREATEtrigger statement. you need to check which triggers in the database can use the show triggers command. Syntax format: CREATE TRIGGERtrigger_name trigger_time trigger_event ON tbl_nameFOR each row trigger_stmt description: ● trigger_name: name of the TRIGGER. the TRIGGER must have a unique name in the current database. If you want to create a database, add the database name before the name. ● Trigger_time: the trigger time. There are two options: AFTER and BEFORE, which indicate that the trigger is triggered BEFORE or AFTER the statement is activated. If you want to execute several or more changes AFTER the statement that activates the trigger is executed, the "AFTER" option is usually used. if you want to verify that the new data meets the restrictions, use the "BEFORE" option. In MySQL, the difference is not obvious. before is similar to after. ● Trigger_event: trigger event, indicating the type of statements used to activate the trigger program. Trigger_event can be one of the following values: INSERT: the trigger is activated when a new row is inserted into the table. For example, INSERT, load data, and REPLACE statements are used. UPDATE: The trigger is activated when a row is changed. For example, use the UPDATE statement. DELETE: the trigger is activated when a row is deleted from the table. For example, the DELETE and REPLACE statements are used. ● Tbl_name: name of the table related to the trigger. a trigger event occurs on the table to activate the trigger. The same table cannot have two triggers with the same trigger time and event. For example, a table cannot have two before update triggers, but one before update trigger and one BEFOREINSERT trigger, or one before update trigger and one after update trigger. ● For each row: This statement is used to specify that the trigger action must be activated for each row affected by the trigger event. For example, if you use a statement to add a group of rows to a table, the trigger will execute the corresponding trigger action on each row. ● Trigger_stmt: trigger action, including the statement to be executed when the trigger is activated. If you want to execute multiple statements, you can use the in... END compound statement structure. In this way, the same statements allowed in the stored procedure can be used. Note: The Trigger cannot return any results to the client. to prevent the returned results from the trigger, do not include the SELECT statement in the trigger definition. Similarly, you cannot call the stored procedure that returns data to the client. 2. new. column name and old. column name usage SQL statements in MySQL triggers can be associated with any column in the table. However, you cannot directly use the column name as a flag, which will confuse the system, because the statement for activating the trigger may have modified, deleted, or added a new column name, and the old column name also exists. Therefore, this syntax must be used to indicate "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 reference updating or deleting a column of an existing row. For INSERT statements, only NEW statements are valid; for DELETE statements, only OLD statements are valid; and the UPDATE statement can be used together with NEW or OLD statements. For example, create a trigger. when you delete the information of a student in table XS, delete all data related to the student in table XS_KC. DELIMITER $ CREATE TRIGGERXS_DELETE after delete on xs for each row begin delete from XS_KC WHERE student ID = OLD. student ID; END $ DELIMITER; now verify the trigger function: delete from xs where student ID = '000000'; use the SELECT statement to view the situation in the XS_KC table: SELECT * FROM XS_KC; note: when a trigger involves updating the trigger itself, only BEFORE can be used, and the AFTER trigger is not allowed. 3. Like other database objects, the delete trigger can be deleted from the database using the DROP statement. Syntax format: drop trigger [schema_name.] trigger_name description: trigger_name: name of the TRIGGER to be deleted. Schema_name is the name of the database in which it is located. if it is in the current database, it can be omitted. Example: delete trigger XS_DELETE: DROP TRIGGERXS_DELETE;
Author tianyazaiheruanbitsCN.com