MySQL stored procedures and triggers

MySQL stored procedures and triggers

I. Stored Procedure

The basic format of the stored procedure is as follows:

-- Declare the Terminator
-- Create a stored procedure
DELIMITER $ -- declare the end of the stored procedure
Create procedure pro_test () -- stored PROCEDURE name (parameter list)
BEGIN
-- Multiple SQL statements can be written; -- SQL statements + Process Control
SELECT * FROM employee;
END $ -- END Terminator

-- Execute the Stored Procedure
CALL pro_test (); -- Name of the CALL Stored Procedure (parameter );

-- Delete a stored procedure
Drop procedure pro_test;

Parameters:
IN: indicates the input parameter, which can carry data IN the Stored Procedure
OUT: indicates the output parameter, which can return results from the stored procedure.
INOUT: indicates the input and output parameters. It can be either input or output.

1. Stored Procedures with input parameters

Requirement: input an employee id to query employee information.

DELIMITER $
Create procedure pro_findById (IN eid INT) -- IN: input parameter
BEGIN
SELECT * FROM employee WHERE id = eid;
END $

-- Call
CALL pro_findById (4 );

2. Stored Procedures with output parameters

DELIMITER $
Create procedure pro_testOut (OUT str VARCHAR (20) -- OUT: output parameter
BEGIN
-- Assign values to parameters
SET str = 'hellojava ';
END $

How can I accept the returned parameter values? MySQL variables are involved here.

There are three types of MySQL variables:

Global Variables

Global variables, also known as built-in variables, are built-in variables in the mysql database and work for all connections.

View all global variables: show variables

View a global variable: select @ variable name

Modify global variables: set variable name = New Value

Character_set_client: encoding of the data received by the mysql server

Character_set_results: encoding of mysql Server output data

Session variable

It only exists in one connection between the current client and the database server. If the connection is disconnected, all session variables are lost!

Define session variables: set @ variable = Value

View session variables: select @ Variables

Local variable

The variables used in the stored procedure are called local variables. Local variables are lost after the stored procedure is executed.

Back to the above stored procedure, how do I accept the returned parameter values?

Defines a session variable name and uses the name session variable to receive the returned values of stored procedures.

CALL pro_testOut (@ NAME );

View variable values

SELECT @ NAME;

3. Stored Procedures with Input and Output Parameters

DELIMITER $
Create procedure pro_testInOut (INOUT n INT) -- INOUT: Input and Output Parameters
BEGIN
-- View Variables
SELECT n;
SET n = 500;
END $

-- Call
SET @ n = 10;

CALL pro_testInOut (@ n );

SELECT @ n;

4. Stored Procedures with conditional judgment

Requirement: enter an integer. If 1, "Monday" is returned. If 2, "Tuesday" is returned. If 3, "Wednesday" is returned ". For other numbers, "incorrect input" is returned ";

DELIMITER $
Create procedure pro_testIf (IN num INT, OUT str VARCHAR (20 ))
BEGIN
IF num = 1 THEN
SET str = 'monday ';
ELSEIF num = 2 THEN
SET str = 'tuesday ';
ELSEIF num = 3 THEN
SET str = 'weday ';
ELSE
SET str = 'input error ';
End if;
END $

-- Call
CALL pro_testIf (4, @ str );
 
SELECT @ str;

5. Stored Procedures with cyclic Functions

Requirement: enter an integer and sum. For example, enter 100 to calculate the sum of 1-

DELIMITER $
Create procedure pro_testWhile (IN num INT, OUT result INT)
BEGIN
-- Define a local variable
DECLARE I INT DEFAULT 1;
DECLARE vsum int default 0;
WHILE I <= num DO
SET vsum = vsum + I;
SET I = I + 1;
End while;
SET result = vsum;
END $

-- Call
CALL pro_testWhile (100, @ result );

SELECT @ result;

6. assign a value to the variable (INTO) using the query result)

DELIMITER $
Create procedure pro_findById2 (IN eid INT, OUT vname VARCHAR (20 ))
BEGIN
SELECT empName INTO vname FROM employee WHERE id = eid;
END $

-- Call
CALL pro_findById2 (1, @ NAME );

SELECT @ NAME;

Ii. triggers

Trigger: when you operate a table, you can use the trigger to trigger some actions or actions at the same time.

Requirement: When you insert a record to the employee table, you want mysql to automatically insert data to the log table at the same time.

Create trigger tri_empAdd after insert on employee for each row -- when a record is inserted into the employee table
Insert into test_log (content) VALUES ('employee table inserted a record ');

The above triggers are based on insert, and the following two are based on update and delete.

-- Create a trigger (modify)
Create trigger tri_empUpd after update on employee for each row -- when modifying a record to the employee table
Insert into test_log (content) VALUES ('employee table modified record ');
 
-- Create a trigger (delete)
Create trigger tri_empDel after delete on employee for each row -- when a record is deleted from the employee table
Insert into test_log (content) VALUES ('employee table deleted record ');

Note:

1. MySQL uses semicolon (;) as the statement Separator by default. If this is the case, it is difficult to create a stored procedure normally because its BEGIN and END can be any number of SQL statements, and each SQL statement ends with a semicolon. Therefore, you must redefine the delimiter when creating a stored procedure. In the above cases, "$" is used as the new separator. Note that after the stored procedure is created, you must reset the original separator ";".

This article permanently updates the link address: