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

In this article Xiaobian for you to introduce in detail "Mysql stored procedures, triggers, event scheduler how to use", detailed content, clear steps, details handled properly, I hope that this "Mysql stored procedures, triggers, event scheduler how to use" article can help you solve doubts, following the editor's ideas slowly in-depth, together to learn new knowledge.

A stored procedure (Stored Procedure) is a database object that stores complex programs in a database. In order to complete the set of SQL statements for specific functions, it is compiled and created and saved in the database.

I. simple use of stored procedures

Create a stored procedure

Create procedure test () begin select * from users;end

Call stored procedure

Call test (); second, the variable create procedure test () begin in the stored procedure-- declare a variable declare username varchar (32) default''using the declare statement;-- assign the variable set username='xiaoxiao'; using the set statement-- assign the name of the id=1 in the users table to the variable username select name into username from users where id=1;-- return the variable select username;end

Note:

Variables can be assigned either by set or by select into

Variables need to be returned, you can use select statements, such as: select variable name.

Third, the scope of variables

The scope of the stored procedure is between begin and end blocks, and variables are declared outside begin and can be used as global variables:

Create procedure test () begin declare userscount int default 0;-- quantity begin select count (*) into userscount from users; select userscount; in the user table-- returns the quantity end; begin declare maxmoney int default 0 in the user table;-- maximum amount select max (money) into maxmoney from orders; select userscount,maxmoney -- returns the quantity in the user table, the maximum amount end; end; 4, the stored procedure parameter create procedure name ([IN | OUT | INOUT] parameter name parameter data type) begin.end

IN: pass in a parameter (if not specified, the default is IN type)

Create procedure test (userId int) begin declare username varchar (32) default'; select name into username from users where id=userId; select username; end

OUT: outgoing parameter

Create procedure test (in userId int,out username varchar (32)) begin select name into username from users where id=userId; end

INOUT: both incoming and outgoing parameters

Create procedure test6 (inout userId int,inout username varchar (32)) begin set userId=2; set username=''; select id,name into userId,username from users where id=userId;end; V, logic control statement 1, conditional statement if () then...elseif () then...else... end if;create procedure test (in userid int) begin declare my_status int default 0; select status into my_status from users where id=userid; if (my_status=1) then update users set score=score+10 where id=userid Elseif (my_status=2) then update users set score=score+20 where id=userid; else update users set score=score+30 where id=userid; end if;end;2, Loop statement

(1) while

While (expression) do. End while;create procedure test () begin declare i int default 0; while (iTun10-if I > = 10, jump out of the loop end repeat;end;3, case branch case... when. Then....when.... Then....else... End case; create procedure testcate (userid int) begin declare my_status int default 0; select status into my_status from users where id=userid; case my_status when 1 then update users set score=10 where id=userid; when 2 then update users set score=20 where id=userid; when 3 then update users set score=30 where id=userid; else update users set score=40 where id=userid; end case; end; 6, cursor

The cursor holds the temporary area of the query results

Declare variable name cursor...-- create a cursor variable close variable name;-- close the cursor create procedure test () begin declare stopflag int default 0; declare username VARCHAR (32); declare username_cur cursor for select name from users where id%2=0 -- the cursor variable username_cur holds the temporary result of the query, that is, the result set-- at the end of the data in the cursor variable, set the variable stopflag to 1 to determine whether to end the declare continue handler for not found set stopflag=1; open username_cur; in the loop-- sign-in cursor fetch username_cur into username -- the cursor takes a step forward, takes a record and puts it in the variable username while (stopflag=0) do-- if the cursor does not have an end, continue begin-- concatenate the'_ cur' string update users set name=CONCAT (username,'_cur') where name=username; fetch username_cur into username at the front door of the user name -- Vernier step forward, take a record and put it in variable username end; end while;-- end loop close username_cur;-- close cursor end; 7. Custom function-- create function create function function name (parameter) returns return type -- function body begin. End;-- specifies the return value of the function. The returns-- function calls the select function name (). Create function getusername (userid int) returns varchar (32) reads sql data-read data from the database but do not modify the data begin declare username varchar (32) default''; select name into username from users where id=userid; return username; end; VIII, trigger

A trigger is also a database object that fires when a definition condition is met and executes a collection of statements defined in the trigger.

Create trigger: create trigger trigger name

After, before: triggers an action before (before) or after (after) the operation on the table.

Action events: modification actions such as insert,update,delete

Scope of influence: for each row

1. Requirements: for audit purposes, when someone inserts a record into the table users, the inserted userid,username, insertion action and operation time are recorded.

Create trigger tr_users_insert after insert on users for each row begin insert into oplog (userid,username,action,optime) values (NEW.userid,NEW.name,'insert',now ()); end

2. Requirements: for audit purposes, when the users table is deleted, the main field values of the record before the record is deleted

Create trigger tr_users_delete before delete on users for each row begin insert into oplog (userid,username,action,optime) values (OLD.id,OLD.name,'delete',now ()); end; IX.

Triggers simply execute statements against events generated by a table, while the event scheduler executes statements at a certain (interval) time.

You must make sure that the event scheduler event_scheduler is turned on before using this feature:

SET GLOBAL event_scheduler = 1Mutual-or SET GLOBAL event_scheduler = on;-- to check whether show variables like'% event_scheduler%' is open. Create event [IF NOT EXISTS] event_name-- when to create and use create event ON SCHEDULE schedule-- when to execute [ON COMPLETION [NOT] PRESERVE]-- whether to retain [ENABLE | DISABLE] after the scheduling plan is executed-- whether to enable events, and whether to enable [COMMENT 'comment']-- Note DO sql_statement; for events-- what does this scheduling plan do?

Requirements: design a lottery opening process for welfare lottery tickets, which will be opened every 3 minutes

-- stored procedure create procedure test () begin insert into lottery (num1,num2,num3,ctime) select FLOOR (rand () * 9) + 1MagneFLOOR (rand () * 9) + 1MagneFLOOR (rand () * 9) + 1JEI now (); end -- event create event if not exists test_event-- create an event on schedule every 3 minute-- on schedule executes on completion preserve do call test every three minutes -- call stored procedures to read here. The article "how to use Mysql stored procedures, triggers and event Scheduler" has been introduced. If you want to master the knowledge points of this article, you still need to practice and use it. If you want to know more about related articles, please follow the industry information channel.

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.