Step by step MySQL (4) triggers and stored procedures

Document directory

• 1. Syntax
• 2. Description
• 3. Instance
• 4. Problems
• 1. Introduction
• 2. Advantages
• 3. Syntax
• 3. Instance
• 5. Use stored procedures in C Language

Trigger

Like the stored procedure, MySQL triggers are embedded into a MySQL program. The trigger is a new function of mysql5. Currently, the online fengchao system, Beidou system, and Columbus system use the database of mysql5.0.45. Many programs such as FC-star Management Terminal, SFRD (DAS ), all Dorado uses the trigger program to associate the events caused by database addition, deletion, and modification.

1. Syntax

CREATE TRIGGER trigger_name trigger_time trigger_event    ON tbl_name FOR EACH ROW trigger_stmt

A trigger is a table-related named database object. This object is activated when a specific event occurs on the table.

The trigger program is related to the table named tbl_name. Tbl_name must reference a permanent table. You cannot associate the trigger program with the temporary table or view.
Trigger_time is the time when the program is triggered. It can be before or after to indicate that the trigger program is triggered before or after its statement is activated.
Trigger_event indicates the type of statements used to activate the trigger program. Trigger_event can be one of the following values:
· Insert: the trigger program is activated when a new row is inserted into the table, for example, through insert, load data, and replace statements.
· Update: the trigger program is activated when a row is changed, for example, through the update statement.
· Delete: the trigger program is activated when a row is deleted from the table, for example, through the Delete and replace statements.
Note that trigger_event is not very similar to the SQL statement used to activate the trigger program in the form of table operations, which is very important. For example, the before trigger program for insert can be activated by both the insert statement and the load data statement.

2. Description

Now, I have noticed the trigger_time and trigger_event again. As mentioned above, trigger_time can be replaced by before and after, indicating whether the trigger program is executed before or after the SQL statement is executed; trigger_event can be insert, update, delete replacement, which indicates the type of SQL that triggers the trigger program.

A maximum of six triggers can be created on a table, namely, 1) Before insert, 2) Before update, 3) before Delete, 4) after insert, 5) After update, 6) After Delete type.

The MySQL engine used in the above experiment is InnoDB, And the InnoDB engine is also used by the online fengchao system, Beidou system, and Columbus system, the table created on InnoDB is a transactional table, that is, transaction security. "For a transaction table, if the triggering program fails (and the entire statement fails as a result), all changes executed by the statement will be rolled back. This type of rollback is not allowed for non-transactional tables (from the MySQL user manual ). Therefore, any changes made before the statement fails are valid even if the statement fails. That is to say, if the SQL statement in the trigger or the SQL statement that triggers the trigger fails to be executed for the data table on the InnoDB engine, the transaction is rolled back, and all operations will become invalid.

3. Instance

Reference: http://hi.baidu.com/avauntage/item/023ac310391b1a011894ecd2

4. Problems

Stored Procedure 1. Introduction

The commonly used operating database language SQL statements must be compiled and then executed before execution. The Stored Procedure (Stored Procedure) is a set of SQL statements for specific functions, after compilation, the stored procedure is stored in the database. You can call and execute the stored procedure by specifying the name of the stored procedure and specifying parameters (if the stored procedure has parameters.
A stored procedure is a Programmable function that is created and saved in a database. It can contain SQL statements and some special control structures. Stored procedures are useful when you want to execute the same functions on different applications or platforms or encapsulate specific functions. Stored Procedures in databases can be seen as simulation of object-oriented methods in programming. It allows you to control how data is accessed.

2. Advantages

(1) stored procedures enhance the functionality and flexibility of the SQL language. Stored procedures can be written using flow control statements. With great flexibility, they can complete complicated judgment and computation.
(2) stored procedures allow standard components to be programmed. After a stored procedure is created, it can be called multiple times in the program without having to rewrite the SQL statement of the stored procedure. Database professionals can modify the stored procedure at any time without affecting the application source code.
(3). the stored procedure can achieve fast execution speed. If an operation contains a large number of transaction-SQL code or is executed multiple times, the stored procedure is much faster than the batch processing. Because the stored procedure is pre-compiled. When a stored procedure is run for the first time, the optimizer analyzes and optimizes it and provides an execution plan that is finally stored in the system table. The transaction-SQL statement of batch processing needs to be compiled and optimized each time it is run, which is relatively slow.
(4). stored procedures can reduce network traffic. For operations (such as queries and modifications) on the same database object, if the transaction-SQL statement involved in this operation is organized by the stored procedure, when the stored procedure is called on the client's computer, the call statement is transmitted on the network, which greatly increases network traffic and reduces network load.
(5) stored procedures can be fully utilized as a security mechanism. By limiting the permissions of a stored procedure, the system administrator can restrict the data access permissions to avoid unauthorized users accessing data, this ensures data security.

3. Syntax

CREATE PROCEDURE sp_name ([proc_parameter[,...]])    [characteristic ...] routine_body

Or specific:

Create procedure stored procedure name (parameter list) Begin SQL statement code block end
Eg:
delimiter |create procedure  proce_name(INOUT testpi int )beginset testpi=90 ;end ;| delimiter ;
Delimiter | it is the statement delimiter. When using the delimiter command, you should avoid using the backslash ('"') character, because it is an escape character of MySQL.
A. Alter procedure (modify)
Alter procedure stored procedure name SQL statement code block
This statement can be used to change the features of a stored program.
B. Drop procedure (delete)
Drop procedure if exists stored procedure name
Eg: Drop procedure if exists proc_employee (proc_employee stored procedure name)
This statement is used to remove a stored program. You cannot delete another stored procedure in one stored procedure. You can only call another stored procedure.
C. Show create procedure
(Similar to show create table, view an existing Stored Procedure)
Show create procedure stored procedure name
D. Show procedure status
(List all stored procedures)
Show procedure status
E. Call Statement (Stored Procedure Call)
Call stored procedure name (parameter list)
The call statement calls a previously created program using CREATE procedure.
The call statement can return a value to its caller using the inout parameter declared as out or.
The stored procedure name must be enclosed in parentheses, even if the stored procedure has no parameters passed
F begin... end (compound statement)
[Begin_label:]
Begin
[Statement_list]
End
[End_label]
You can use the in... end compound statement to store subprograms to contain multiple statements.
Statement_list indicates the list of one or more statements. Each statement in statement_list must end with a semicolon.
Compound statements can be marked. Unless begin_label exists, end_label cannot be given, and if both exist, they must be the same.
G declare Statement (used to declare local variables)
The declare statement is used to place different projects to a sub-program: local variables
Declare is only used in the in... end compound statement and must begin with the compound statement before any other statement.
Http://database.51cto.com/art/201011/235405.htm for more information
H. variables in the Stored Procedure
A. Declare local variables
Declare var_name [,...] type [default value]
This statement is used to declare local variables.
To provide a default value for a variable, include a default clause.
The value can be specified as an expression and does not need to be a constant.
If no default clause exists, the initial value is null.
The scope of a local variable is within the declared begin... end block.
It can be used in nested blocks, except those that declare variables with the same name.
B. Variable set statement
Set var_name = expr [, var_name = expr]
The Set statement in the storage Program is an extended version of the general set statement.
The referenced variable may be a variable declared in the subroutine or a global server variable.
The Set statement in the stored program is implemented as part of the pre-existing set syntax. This allows the set a = X, B = Y,... extension syntax.
Different variable types (local declaration variables and global and collective variables) can be mixed.
This also allows you to combine local variables with some options that only make sense to system variables.
C. Select... into statement
Select col_name [,...] into var_name [,...] table_expr
This select syntax stores the selected columns directly to variables.
Therefore, only a single row can be retrieved.
Select ID, data into X, Y from test. T1 limit 1;
Note that user variable names are case-insensitive in MySQL 5.1.
Important: the SQL variable name cannot be the same as the column name. If an SQL statement such as select... into contains a reference to a column and a local variable with the same name as the column, MySQL interprets the reference as the name of a variable.
D. MySQL stored procedure parameter types (In, out, inout)
The content of this section is from:
See: http://www.blogjava.net/nonels/archive/2009/04/22/233324.html
MySQL stored procedure parameters (in)
MySQL stored procedure "in" parameter: similar to the value passing of function parameters in C language, MySQL stored procedure may modify this parameter internally, but modify the in type parameter, not visible for callers ).
MySQL stored procedure parameters (out)
MySQL Stored Procedure "Out" parameter: transfers a value from the stored procedure to the caller. In a stored procedure, the initial value of this parameter is null, regardless of whether the caller sets a value for the stored procedure parameter.
MySQL stored procedure parameters (inout)
The inout parameter of the MySQL stored procedure is similar to the out parameter, and can be passed to the caller from the stored procedure. The difference is that the caller can also pass the value to the stored procedure through the inout parameter.
I Summary
If you only want to pass data to the MySQL stored procedure, use the "in" type parameter. If you only return values from the MySQL stored procedure, use the "out" type parameter; if you need to pass the data to the MySQL stored procedure, you need to pass it back to us after some computation. In this case, you need to use the "inout" type parameter.
3. Instance
A. Create a stored procedure
Stored Procedure with (output parameter) return value:
-- Delete a stored procedure
Drop procedure if exists proc_employee_getcount
-- Create a stored procedure
Create procedure proc_employee_getcount (out n INT)
Begin
Select count (*) from employee;
End
-- MySQL calls the Stored Procedure
Call proc_employee_getcount (@ n );
Stored Procedure with input parameters:
-- Delete a stored procedure
Drop procedure if exists proc_employee_findbyid;
-- Create a stored procedure
Create procedure proc_employee_findbyid (in n int)
Begin
Select * from employee where id = N;
End
-- Define variables
Set @ n = 1;
-- Call a stored procedure
Call proc_employee_findbyid (@ n );
Note the following when operating the stored procedure:
A. When deleting a stored procedure, you only need to specify the name of the stored procedure without parentheses;
B. When creating a stored procedure, brackets are required no matter whether the stored procedure has any parameters;
C. Use the set syntax rules to define variables;
Set @ variable name = initial value;
When defining the Stored Procedure Parameter List, note that the parameter name is different from the field name in the database. Otherwise, unexpected results may occur.
5. Use stored procedures in C Language
Refer:
Http://www.blogjava.net/sxyx2008/archive/2009/11/24/303497.html
Http://database.51cto.com/art/201011/235405.htm
Http://www.cnblogs.com/wxb-km/archive/2012/09/10/2678376.html
Http://blog.sina.com.cn/s/blog_52d20fbf0100ofd5.html
Http://hi.baidu.com/avauntage/item/023ac310391b1a011894ecd2
Http://www.58blo.com /? P = 163