Shulou(Shulou.com)05/31 Report--

This article is about what mysql stored procedures are. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

I. definition

A stored procedure (Stored Procedure) is a set of SQL statements that are stored in the database in order to complete a specific function in a large database system. After the first compilation, the call does not need to be compiled again. The user executes it by specifying the name of the stored procedure and giving parameters (if the stored procedure has parameters). Stored procedure is an important object in database.

2. Characteristics of stored procedures

1. Be able to complete more complex judgments and operations

2. Strong programmability and flexibility

3. The code of SQL programming can be reused.

4. The execution speed is relatively fast.

5. Reduce data transmission between networks and save overhead

Third, create a simple stored procedure

1. Simple syntax for creating stored procedures

Create procedure name () begin.end

2. Create a simple stored procedure

Create procedure testa () begin select * from users; select * from orders;end

3. Call the stored procedure

Call testa ()

The running results are shown in figure (1) and figure (2):

Figure (1)

Figure (2)

IV. Variables of stored procedures

1. Learn the declaration and assignment of variables through a simple example.

Create procedure test2 () begin-- 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 username select name into username from users where id=1;-- return the variable select username;end

2. Generalization

(1) variables are declared using declare. Only one variable is declared in a declare sentence. Variables must be declared before they are used.

(2) variables have data types and lengths, which are consistent with mysql's SQL data types, so they can even set default values, character sets, collations, etc.

(3) variables can be assigned through set or select into

(4), variables need to be returned, you can use select statements, such as: select variable name.

The scope of variables

1. Variable scope description:

(1) the variable in the stored procedure has a scope, and the scope of action is between begin and end blocks, and the scope of the end ending variable ends.

(2) if you need to pass values between multiple blocks, you can use global variables, that is, before all code blocks

(3) passing parameters is global and can work among multiple blocks.

2. Verify the scope of variables through an example.

Requirements: create a stored procedure to count the number of rows in tables users and orders, and the maximum and minimum amounts in orders tables

Create procedure test3 () begin begin declare userscount int default 0;-- quantity in user table declare ordercount int default 0;-- quantity in order table select count (*) into userscount from users; select count (*) into ordercount from orders; select userscount,ordercount;-- returns quantity in user table, quantity in order table end; begin declare maxmoney int default 0;-- maximum amount declare minmoney int default 0 -- minimum amount select max (money) into maxmoney from orders; select min (money) into minmoney from orders; select maxmoney,minmoney;-- returns the minimum amount and minimum amount end;end

Call the above stored procedure and the result is shown in figure (3) and figure (4):

(3)

(4)

3. I changed the process test (3) to the following:

Create procedure test3 ()

Begin

Begin

Declare userscount int default 0;-- quantity in the user table

Declare ordercount int default 0;-- quantity in the order table

Select count (*) into userscount from users

Select count (*) into ordercount from orders

Select userscount,ordercount;-returns the quantity in the user table and the quantity in the order table

End

Begin

Declare maxmoney int default 0;-- maximum amount

Declare minmoney int default 0;-- minimum amount

Select max (money) into maxmoney from orders

Select min (money) into minmoney from orders

Select userscount,ordercount,maxmoney,minmoney;-returns the maximum and minimum amount

End

End

Call call test3 () again; an error will be reported as shown in figure 5:

Figure (5)

4. Change userscount,ordercount to a global variable and verify it again

Create procedure test3 ()

Begin

Declare userscount int default 0;-- quantity in the user table

Declare ordercount int default 0;-- quantity in the order table

Begin

Select count (*) into userscount from users

Select count (*) into ordercount from orders

Select userscount,ordercount;-returns the quantity in the user table and the quantity in the order table

End

Begin

Declare maxmoney int default 0;-- maximum amount

Declare minmoney int default 0;-- minimum amount

Select max (money) into maxmoney from orders

Select min (money) into minmoney from orders

Select userscount,ordercount,maxmoney,minmoney;-returns the maximum and minimum amount

End

End

Call call test3 () again; the error is shown in figure (6) and figure (7):

Figure (6)

Figure (7)

Therefore, the scope of the variable in the stored procedure is between the begin and end blocks, and the scope of the end ending variable ends.

VI. Stored procedure parameters

1. Basic grammar

Create procedure name ([IN | OUT | INOUT] Parameter name Parameter data Type) begin.end

The parameter types of stored procedures are: IN,OUT,INOUT, which are described below:

2. The outgoing parameter IN of the stored procedure

Description:

(1) input parameter: the type is in, which means that the value of the parameter must be specified in the calling stored procedure. If it is not specified as in, then the default is the in type.

(2). IN type parameters are generally only used for passing in, and are generally not modified and returned during the call.

(3) if you need to modify and return a value in calling a stored procedure, you can use the OUT type parameter

Demonstrate through an example:

Requirements: write stored procedures, input id, and return name according to id

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

Run as shown in figure (8)

Figure (8)

3. The outgoing parameter out of the stored procedure

Requirement: when calling the stored procedure, the input userId returns the user's name

Create procedure test5 (in userId int,out username varchar (32))

Begin

Select name into username from users where id=userId

End

The result of calling and running is shown in figure (9):

Figure (9)

Summary:

1. Outgoing parameters: when calling a stored procedure, you can change its value and return

2. Out is an outgoing parameter and cannot be used to pass in parameter values.

3. When calling a stored procedure, the out parameter also needs to be specified, but it must be a variable, not a constant.

4. If you need both input and output, you can use the INOUT type parameter

(3)。 Variable parameter INOUT of stored procedure

Requirements: when calling a stored procedure, userId and userName are passed in, and even if passed in, they are 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

The result of calling and running is shown in figure (10).

Figure (10)

Summary:

1. Variable INOUT: the value can be passed in during the call, and its value can be modified or returned at the same time.

2. The INOUT parameter integrates the parameter functions of IN and OUT types.

3. Variables are passed in when INOUT is called, not constants

Stored procedure conditional statement

1. Basic structure

(1) the basic structure of conditional statements:

If () then...else...end if

(2) Multi-conditional judgment sentence:

If () then...elseif () then...else... end if

2. Examples

Example 1: write a stored procedure that returns username if the user userId is even, or userId otherwise

Create procedure test7 (in userId int) begin declare username varchar (32) default''; if (userId%2=0) then select name into username from users where id=userId; select username; else select userId; end if;end

The call and run results are shown in figure (11) and figure (12):

Figure (11)

Figure (12)

2. An example of the application of multi-conditional statements in stored procedures

Requirements: judged by the uid parameters passed in by the user

(1) if the user status status is 1, add 10 points to the user score

(2) if the user status status is 2, add 20 points to the user score

(3) add 30 points for other situations

Create procedure test8 (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

The data of the users table before the call program is shown in figure (13). Call call test8 (1); and run the result diagram (14):

Figure (13)

Figure (14)

Stored procedure loop statement

1. While statement

(1). The basic structure of while statement

While (expression) do. End while

(2), example

Requirements: insert 10 consecutive records into the table test1 (id) using a circular statement

Create procedure test9 () begin declare i int default 0; while (iTunes 10-if I > = 10, then jump out of the loop end repeat;end

The result of calling and running is shown in figure (17) and figure (18).

Figure (17)

Figure (18)

Summary:

Until determines whether the return logic is true or false, and the expression can be any expression that returns true or false. Only if the until statement is true, the loop ends.

IX. The use of stored procedure cursors

1. What is a cursor

Cursors are temporary areas where query results are saved

2. Example

Requirements: write stored procedures and use cursors to update user names one by one for records with even id in the users table

Create procedure test11 () begin declare stopflag int default 0; declare username VARCHAR (32);-- create a cursor variable with the declare variable name cursor. Declare username_cur cursor for select name from users where id%2=0 -- the cursor is the temporary area where the query results are saved-- the cursor variable username_cur holds the temporary results of the query, which is actually the result set-- when the results saved in the cursor variables are queried once (traversing), the variable stopflag is set to 1 to determine whether to end the declare continue handler for not found set stopflag=1; open username_cur in the loop. -- fetch username_cur into username;-- the cursor takes a step forward, takes a record and puts it in the variable username (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 at the front door of the user name. " Fetch username_cur into username; end; end while;-- end loop close username_cur;-- close cursor end

The call result is shown in figure (19):

Figure (19)

10. Custom function

The biggest difference between a function and a stored procedure is that the function must have a return value, otherwise an error will be reported.

1. A simple function

Create function getusername (userid int) returns varchar (32) reads sql data-reads data from the database but does not modify the data begin declare username varchar (32) default'; select name into username from users where id=userid; return username; end

The result of calling and running is shown in figure (20):

Figure (20)

Summary:

1. Create a function that uses the create function function name (parameter) returns to return the type

two。 The function body is placed between begin and end

3.returns specifies the return value of the function

4. The function call uses select getusername ().

2. Example

Requirements: according to userid, obtain the accoutid,id,name combined into UUID as the unique identity of the user

Create function getuuid (userid int) returns varchar (64) reads sql data-reads data from the database but does not modify the data begin declare uuid varchar (64) default''; select concat (accontid,'_',id,'_',name) into uuid from users where id=userid; return uuid; end

The result of calling and running is shown in figure (21).

Figure (21)

Trigger 1. What is a trigger

Trigger, like function and stored procedure, trigger is a kind of object, it can trigger some actions according to the operation time of the table, these actions can be insert,update,delete and other modification operations.

2. Example 1

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

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

After successful creation, insert a record into the uses table:

Insert into users (id,name,age,status,score,accontid) values (6 'Xiao Zhou', 23'1', 23'1 "60" and "10001')

After the execution is successful, open the oplog table and see that a record is inserted in the oplog table as shown in figure 22.

Figure (22)

(2) Summary

1. Use the create trigger trigger name to create a trigger

2. When will it be triggered? After insert on users, besides after and before, triggers the action before (before) or after (after) the operation on the table.

3. What action events are triggered? After insert on users, action events include insert,update,delete and other modification actions

4. What table is triggered? After insert on users

5. What is the scope of influence? For each row

3. Example 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

Delete a record in the users table

Delete from users where id=6

After the execution is successful, open the oplog table and see that a record is inserted in the oplog table as shown in figure 23.

Figure (23)

XII. Process control

1. Case branch

(1) basic grammatical structure

Case... when... Then....when.... Then....else... End case

(2), example

In the users table, get the status value according to userid. If status is 1, modify score to 10; if status is 2, modify to 20; if status3, modify to 30; otherwise, modify to 40.

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

Call procedure call testcate (1);, and the execution result is shown in figure (24)

Figure (24)

Stored procedure + event (event)

1. A simple implementation of welfare lottery 3D lottery using stored procedures + event events.

Requirements: design the lottery opening process of a welfare lottery, once every 3 minutes.

The first step: first write a stored procedure open_lottery, generate three random numbers and generate a lottery record

Step 2: write a time scheduler that calls this process every 3 minutes

Create procedure open_lottery () begin insert into lottery (num1,num2,num3,ctime) select FLOOR (rand () * 9) + 1 Magi now (); end Create event if not exists lottery_event-- create an event on schedule every 3 minute-- when will on schedule execute, and on completion preserve do call open_lottery will be executed every three minutes?

The running result is shown in figure (25).

Figure (25)

Note that if one of the event is not running, follow these steps:

(1), show variables like'% event_scheduler%'

Set global event_scheduler=on

(2), alert event lottery_event enable

2. Parse the creation format of event

(1) basic grammar

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?

(2) description of execution time

1. Example of a single scheduled task

Once at 4: 00 on February 1, 2019.

On schedule at '2019-02-01 04GRV 0000'

two。 Repetitive plan execution

On schedule every 1 second executes once per second

On schedule every 1 minute executes every minute

On schedule every 1 day is executed every day.

3. Repetitive scheduled tasks for a specified time range

It is executed once a day at 20:00:00

On schedule every 1 day starts' 2019-02-01 20 purl 0000'

15. The tables used in this article

1. Lottery table

2. Oplog table

3. Orders table

4. Test1 table

5. User table

Thank you for reading! This is the end of this article on "what is a mysql stored procedure?". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it for more people to see!

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.