Stored procedures and triggers

Stored Procedure 5.1 What is a stored procedure

stored procedures, SQL statements with logic

The previous SQL has no condition to judge, no loops

Stored procedures with Process Control statements (if while)

5.2 Stored Procedure Features

1) execution efficiency very fast! The stored procedure is executed on the server side of the database!!!

2) Very poor transplant! Stored procedures for different databases are not portable.

                      5.3 Stored Procedure syntax

--Create stored procedure DELIMITER $      --Declaring a stored procedure Terminator CREATE PROCEDURE pro_test ()            --Stored procedure name (parameter list) begin             --Start         --can write multiple SQL statements;          --SQL statement + Process Control           SELECT * from employee; End $           --end Terminator   -Execute Stored procedure Call Pro_test ();         --The call stored procedure name (parameter);   Parameters: in:   represents input parameters and can carry data with stored procedures Out: Represents an output parameter that can be returned from a stored procedure INOUT: Represents an input-output parameter that can either be entered or output

--************** Three, stored procedure *******************- --declaration Terminator --Create a stored procedure DELIMITER $ CREATE PROCEDURE pro_test () BEGIN --can write multiple SQL statements; SELECT * from employee; END $ --Execute Stored procedure Call Pro_test (); --3.1 stored procedure with input parameters --Requirements: Pass in an employee ID, query employee information DELIMITER $ CREATE PROCEDURE Pro_findbyid (in Eid Int.)--In: Input parameters BEGIN SELECT * FROM employee WHERE Id=eid; END $ --Call Call Pro_findbyid (4); --3.2 stored procedure with output parameters DELIMITER $ CREATE PROCEDURE pro_testout (out str VARCHAR) – Out: Output parameters BEGIN --Assigning values to parameters SET str= ' Helljava '; END $ --Delete stored procedures DROP PROCEDURE pro_testout; --Call --How to accept the value of the return parameter?? --***mysql variable ****** --Global variables (built-in variables): MySQL database built-in variables (all connections work) --View All global variables: show variables --View a global variable: SELECT @@ variable Name --Modify global variables: Set Variable name = new value --Encoding of the received data of the CHARACTER_SET_CLIENT:MYSQL server --Encoding of CHARACTER_SET_RESULTS:MYSQL server output data --session variable: exists only in one connection between the current client and the database server side. If the connection is broken, then all session variables are lost! --Define Session variables: SET @ variable = value --View Session variables: SELECT @ variable --Local variables: variables used in stored procedures are called local variables. Local variables are lost as long as the stored procedure is executed!! --1) Define a Session variable name, 2) use the name session variable to receive the return value of the stored procedure Call Pro_testout (@NAME); --View variable values SELECT @NAME; --3.3 stored procedure 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; --3.4 stored procedures with conditional judgment --Demand: Enter an integer if 1, then return "Monday" if 2, return "Tuesday", if 3, return "Wednesday". Other numbers, return "error input"; 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= ' Wednesday '; ELSE SET str= ' input error '; END IF; END $ Call Pro_testif (4, @str); SELECT @str; --3.5 stored procedures with loop function --Requirements: Enter an integer and sum. For example, enter 100, statistic 1-100, and 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 $ DROP PROCEDURE Pro_testwhile; Call Pro_testwhile (@result); SELECT @result; Use DAY16; --3.6 Assigning a value to a variable using the query's result (into) DELIMITER $ CREATE PROCEDURE Pro_findbyid2 (in Eid int,out vname VARCHAR (20)) BEGIN SELECT EmpName to VName from employee WHERE Id=eid; END $ Call Pro_findbyid2 (1, @NAME); SELECT @NAME;

Trigger 6.1 Trigger action

When you manipulate a table, you want to trigger some action/behavior at the same time, you can use the trigger to complete!!

For example, when you insert a record into an employee table, you want to insert the data in the log table at the same time

--Requirements: when inserting a record into an employee table, I want MySQL to automatically insert data into the log table at the same time --Create a trigger (add) 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 insert a record '); --Inserting data INSERT into employee (Id,empname,deptid) VALUES (7, ' Zangus ', 1); INSERT into employee (Id,empname,deptid) VALUES (8, ' Staples 2 ', 1); --Create a trigger (modify) CREATE TRIGGER tri_empupd after UPDATE on employee for each ROW--when a record is modified to the employee table INSERT into Test_log (content) VALUES (' Employee table modified a record '); --Modification UPDATE employee SET empname= ' Eric ' WHERE id=7; --Create TRIGGER (delete) CREATE TRIGGER Tri_empdel After delete on employee for each ROW--when a record is deleted to the employee table INSERT into Test_log (content) VALUES (' Employee table delete a record '); --Delete DELETE from employee WHERE id=7;

Stored procedures and triggers