Mysql custom stored procedure and trigger _ MySQL

Mysql custom stored procedures and triggers

Mysql custom stored PROCEDURE and trigger -- Stored PROCEDURE demonstration drop procedure if exists PRO_TEST; create procedure PRO_TEST (IN NUM_IN INT, OUT NUM_OUT INT, INOUT NUM_INOUT) BEGIN // do anything you wantend; parameter description: in: refers to the input parameter. the input parameter will be passed into the stored procedure for use as the parameter. changing its value will not change its original value, which is equivalent to passing out: this is an output parameter. if it is a value in the stored procedure, the value is NULL even if it is previously input, this is equivalent to creating a new value in the stored procedure, paying the out parameter, and outputting inout: this is the input and output parameter, which can be understood as the sum of the first two, the simplest thing is to understand how to call the command line by passing the object: SET @ NM_IN = 1, @ NM_OUT = 2, @ NM_INOUT = 3; call PROC_TEST (@ NM_IN, @ N M_OUT, @ NM_INOUT); how to call in java: find a way to get the connection, and set it to getSession () in hibernate (). connection (); use Java. SQL. callableStatemet call = connection (). prepareStatement ("call PROC_TEST (, 3)" implements callback call.executequery(future will return to the resultsetobject, and call.exe cuteUpdate () will return the affected number of rows. -- Trigger example triggers can be divided into INSERT, UPDATE, and DELETE triggers according to the trigger type, which can be divided into BEFORE and AFTER according to the trigger time. the trigger types can be divided into OLD, NEW, and OLD based on the content, here, the delete operation is only OLD, and the NEW operation is only NEW. The MODIFY operation has two operations: for example, if the user table is not modified once, insert the data before and after the modification to the user_back table: create trigger TRI_UPGRADE_USER before update on user for each rowbegininsert into USER_BACK (USERNAME, USERPWD) VALUES (OLD. USERNAME, OLD. USERPWD); insert into USER_BACK (USERNAME, USERPWD) VALUES (NEW. USERNAME, NEW. USERPWD); END;