Application of MySQL stored procedures and triggers

Storage routines:

A storage routine is a set of SQL statements stored in a database server that executes these SQL statement commands by invoking a specified name in the query.

Usage scenarios:

Our application is divided into two kinds: Web-based, desktop-based, and they interact with the database to complete the data access work. Assuming that there is one application that contains both, and now you want to modify one of the query SQL statements, then we may want to modify the corresponding SQL statements in them, and when our application is very large and load, the problem arises and is not easy to maintain! In addition, SQL queries are easily corrupted by SQL injection in our web programs or desktops. And the stored procedure just can help us solve this problem.

The stored routines in MySQL contain stored procedures and stored functions, collectively known as storage routines

The main completion of the stored procedure is to get a record or insert a record or update a record or delete a record, i.e. complete select,insert,delete,update, etc.

The store function only completes the query, accepts input parameters and returns a result

Querying all stored procedures for a database: Showprocedure STATUS

Stored procedure syntax:

Demiliter $$--Redefine separator

CREATE PROCEDURE Sp_name ()

Begin--Block definition begins

......

End--Zone definition ends

$$--defined delimiters

Delete stored procedure: Drop PROCEDURE procedure_name

Variable definition: Local variable declarations must be placed at the beginning of the body of the stored procedure

DECLARE variable_name [, variable_name ...] DATATYPE [Default value]

DataType data type for MySQL, such as Int,float,date,varchar (length)

Example: DECLARE l_int int unsighed default 40000;

Variable assignment:

SET variable name = expression [, Variable_name = "expression ...]

Trigger:

Instead of thinking that the stored procedure needs to be called in the program, it is triggered when the data table is modified by a DML statement such as Insert/pudate/delete.

Application scenarios: Important business logic, improved performance, monitoring table modification, etc.

Grammar:

DELIMITER $$

CREATE TRIGGER tri_stuname--Trigger name

Trigger_time--The trigger time value is Before/after

Trigger_event--Trigger time, value is insert/update/delete; On Tbl_name--Create a table name for the trigger

For each ROW

TRIGGER_STMT--Trigger program body, begin ... end

Instance:

           DELIMITER $
                CREATE trigger Tri_stuinsert after insert
                on student for each row
                begin
                declare c int;
               Set c = (select Stucount From class where classid=new.classid);
               Update class set Stucount = C + 1 where ClassID = New.classid;
               end$
           DELIMITER;

Application of MySQL stored procedures and triggers