The current database has an ACount table
Create a stored procedure
(1) grammar: mysql > CREATE PROCEDURE created by the stored PROCEDURE name (OUT | | IN INOUT parameter data types,… ,…). Characteristic process body **; **
The analysis is as follows:
(2) Use:
Listing a detailed list of stored procedures: show procedure status;
Drop procedure Specifies the name of the stored procedure to drop.
To view information about creating a stored procedure, run the show create procedure command.
To view all stored procedures: show procedure status \G;
(3) Examples
Create a stored procedure pshow with no arguments:
Call the stored procedure pshow(); Note that parentheses are not used for calls without arguments
Create a stored procedure pfun with parameters:
Call the stored procedure pfun
call procedure(); / / no arguments
call procedure(@x,@y,****); // There are parameters (x and y are output parameters, **** are input parameters)
Select @x,@y; To query the values of x and y.
View all current stored procedures
Two, storage function:
(1) syntax: CREATE FUNCTION creates the storage FUNCTION name (parameter name parameter type,… ,…). RETURNS the body of the value type function;
(2) Use
Call storage function: select function name ([parameter]); // According to the parameters of the function defined, the parameters and arguments remain the same
Show create function function name /G;
To view all custom functions: show function status \G;
Drop function drop function name;
(3) Examples
Create a storage function fnameshow:
Call the storage function:
Create a storage function myDelete with arguments
Call:
Select * from table where id=5;
View storage functions:
Three, trigger:
(1) Grammar:
CREATE the TRIGGER to CREATE a TRIGGER name BEFFOR | AFTER INSERT | UPDATE | DELETE
ON table name FOR trigger execution interval trigger SQL statement
The analysis is as follows:
Note (from the network) :
The trigger is associated with the table to create the trigger. This table must reference persistent tables. A trigger cannot be associated with a temporary table table or view.
The action time BEFORE or AFTER of the triggering program to indicate whether the triggering program fired BEFORE or AFTER the statement that activated it.
The type of statement that activates the trigger. One of the following values:
· INSERT: The trigger is activated when a new row is inserted into a table, for example, through INSERT, LOAD DATA, and REPLACE statements.
· UPDATE: The trigger is activated when a row is changed, for example, through an UPDATE statement.
· DELETE: Activates the trigger when a row is deleted from a table, for example, by DELETE and REPLACE statements.
It is important to note that trigger_event is not very similar to an SQL statement that activates the trigger as a table operation.
For example, the BEFORE trigger for INSERT can be activated not only by INSERT statements, but also by LOAD DATA statements
(2) Use
View all triggers: show Triggers \G;
Drop trigger trigger name;
(3) Examples
Create an insert trigger
Update the table, check the table, find the balance of id=4 from 500 to 509
View trigger
Stored procedures, stored functions, and triggers:
(1) Basic differences:
(2) Comparison of advantages and disadvantages:
Stored procedure:
Advantages:
* There are if/else,case,while and other control statements, by writing stored procedures, you can achieve some logic more complex functions;
* Modular; Some functions are encapsulated, code reuse;
* Fast response, only in the first execution of the need to go through the compilation and optimization steps, after the call directly executed, eliminating the need to rewrite the code calculation steps.
* Reduce network traffic. Stored procedures run directly on the database server, and all data access takes place inside the server, with no need to transfer data to other terminals.
* Convenient for DBA optimization. All SQL in one place
Disadvantages:
* Complex business logic. There is no way to apply cache.
The trigger
Advantages:
* security. You can give the user some rights to manipulate the database based on its values. You can track user actions on the database.
* Triggers can reject or roll back changes that undermine the integrity of the relationship and cancel transactions that attempt to update data. This trigger works when a foreign key that does not match its primary key is inserted.
Disadvantages: * The trigger effect is very low when the data set is large (since the whole data set needs to be changed automatically)
* Triggers are not suitable for batch operations, and the business logic implemented with triggers is difficult to locate when problems occur.
Five, notes:
In MySQL, semicolons are used as delimiters by default. When writing stored procedures, stored functions, and triggers, the compiler will process them as SQL statements, and errors will occur during compilation. Therefore, delimiter @ (or other, as defined by the user) as the current segment delimiter. Tell the compiler to treat the content between the @ signs as code for stored procedures, stored functions, and triggers, and then “delimiter;” after writing the entire content. Restore the delimiter.