Mysql custom stored procedures and triggers

Welcome to the Linux community forum and interact with 2 million technical staff to enter the mysql custom stored procedure and trigger-stored procedure demonstration example; CREATEPROCEDUREPRO_TEST (INNUM_ININT, OUTNUM_OUTINT, INOUTNUM_INOUT) BEGINDOANYTHINGYOUWANTEN

Welcome to the Linux community forum and interact with 2 million technical staff> go to the 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 WANT EN

Welcome to the Linux community forum and interact with 2 million technicians>

Mysql custom stored procedures and triggers

-- 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 WANT

END;

Parameter description:

In: it is 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 the value.

Out: This is an output parameter. When it is a value in the stored procedure, even if it is previously passed in, its value is only NULL, this is equivalent to creating a new value in the stored procedure, paying the out parameter, and outputting

Inout: This is an input/output parameter. It can be understood as the sum of the first two.

How to call in the command line:

SET @ NM_IN = 1, @ NM_OUT = 2, @ NM_INOUT = 3;

Call PROC_TEST (@ NM_IN, @ NM_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 (1, 2, 3 )");

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,

The trigger time can be divided into: BEFORE, AFTER,

The OLD and NEW operations can be divided into OLD and NEW operations. The delete operation only has OLD operations, and the NEW operation only has NEW operations.

For example, if the user table is not modified once, data before and after modification is inserted into the user_back table:

Create trigger TRI_UPGRADE_USER BEFORE UPDATE ON USER FOR EACH ROW

BEGIN

Insert into USER_BACK (USERNAME, USERPWD) VALUES (OLD. USERNAME, OLD. USERPWD );

Insert into USER_BACK (USERNAME, USERPWD) VALUES (NEW. USERNAME, NEW. USERPWD );

END;

This is done.