Shulou(Shulou.com)06/01 Report--

The main purpose of this article is to tell you briefly about the specific differences between mysql stored procedures and triggers. You can look up the relevant professional terms on the Internet or find some related books to supplement them. We will not dabble here, so let's go straight to the topic. I hope this article can give you some practical help. I hope there are any specific differences between mysql stored procedures and triggers.

Stored procedure:

In a large database system

A set of SQL statements to perform a specific function

Stored in the database and called again after the first compilation, there is no need to compile again

The user executes it by specifying the name of the stored procedure and giving parameters (if the stored procedure has parameters).

Stored procedure is an important object in database.

Advantages:

1 allows modular programming (create multiple uses at a time)

2 allow faster execution

3 reduce network traffic

4 better security mechanism

Format:

DELIMITER / / CREATE PROCEDURE storage name ([IN, OUT, INOUT]? Parameter name? Data type.) BEGIN SQL statement END / / DELIMITER

Calling procedure:

Use the call procedure name ()

View all stored procedure show procedure status

View the show create procedure procedure name created by the stored procedure

Delete procedure drop procedure procedure name

In indicates that parameters are passed from the outside to the inside for use (used internally in the process)

Out means that parameters save data to variables from within the process and give it to external use. All incoming variables must be variables. If the passed out variable itself has data outside, then the first thing to do after entering the process is to clear it and set it to null.

Inout data can be passed in from the outside to be used inside the procedure, and after the internal operation, the data will be returned to the outside.

Trigger:

Trigger is a special type of stored procedure, which is different from stored procedure.

Triggers are mainly triggered by events, while stored procedures can be called directly by the name of the stored procedure.

Function:

1. Data can be forcibly checked or converted before being written to the data table

two。 When an error occurs in the trigger, the result of the change will be undone.

Format

DELIMITER / / Create trigger-- trigger name trigger timing trigger event on table for each row Begin-- content of the operation End / / DELIMITER

Trigger object: on table for each row trigger binding is essentially all rows in the table, so when each row changes, the trigger will be triggered

Trigger time: the corresponding row in each table will have a different state, when the SQL instruction occurs

Will cause the data in the row to change, and each row will always have two states. Before operating data (before), after manipulating data (after)

Trigger event:

The goal of the trigger in Mysql is to change the data, and the corresponding operation only (add, delete, change) query does not change the data.

So the query did not trigger an event.

Note:

In a table, there can be only one trigger type corresponding to the trigger event bound by each trigger.

There can be only one after insert trigger in a table, so the maximum number of triggers in a table can be six.

Create a stored procedure

DELIMITER / / CREATE PROCEDURE addUser (IN uCode VARCHAR (50), IN uName VARCHAR (20), IN uRole INT,IN sex INT,IN tel VARCHAR (30)) BEGININSERT INTO smbms_user (userCode,userName,userRole,gender,phone) VALUES (uCode,uName,uRole,sex,tel); END//DELIMITER / / View stored procedure show procedure status CALL addUser (# {userCode}, # {userName}, # {userRole}, # {gender}, # {phone}) public int saveUser (@ Param ("userCode") String userCode,@Param ("userName") String userName,@Param ("userRole") Integer userRole,@Param ("gender") Integer gender,@Param ("phone" String phone); public List findUserListPage (String queryUserName, Integer queryUserRole, Integer currentPageNo, Integer pageSzie); public boolean saveUser (String userCode, String userName, Integer userRole,Integer gender, String phone) {SqlSession sqlSession = null;int row = 0 / / the number of rows affected try {sqlSession = MyBatisUtil.createSqlSession (); row = sqlSession.getMapper (UserMapper.class) .saveUser (userCode, userName, userRole, gender, phone); / / commit transaction sqlSession.commit ();} catch (Exception e) {if (sqlSession! = null) {sqlSession.rollback ();} row = 0 field e.printStackTrace ();} finally {MyBatisUtil.closeSqlSession (sqlSession);} if (row > 0) {return true;} return false } userService.saveUser ("zhangcuishan", "Yasso", 1,2, "15645678941")

Create trigger

Create two tables create table my_goods (id int primary key auto_increment,name varchar (20) not null,inv int) create table my_orders (id int primary key auto_increment,goods_id int not null,goods_num int not null) insert into my_goods values (null,' Mobile', 1000), (null,' computer', 500), (null,' Game console', 100) DELIMITER / / CREATE TRIGGER a_i_o_t AFTER INSERT ON my_orders FOR EACH ROWBEGINUPDATE my_goods SET inv = inv-new.goods_num WHERE id=new.goods_id;END//DELIMITER; DELIMITER / / CREATE TRIGGER b_i_o_t BEFORE INSERT ON my_orders FOR EACH ROWBEGIN SELECT inv FROM my_goods WHERE id=new.goods_id INTO @ inv;IF @ inv

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.