What is a stored procedure, and what does a stored procedure do? This blog will take you through the stored procedures and put them into action. (Suggested collection)
What is a stored procedure?
The MySQL5.0 version starts with supported stored procedures.
Stored Procedure is a database object that stores complex programs in a database for external programs to call.
A stored procedure is a set of SQL statements designed to perform a specific function. It is created by compilation and stored in a database. The user can invoke the execution of the stored procedure by specifying its name and giving parameters (if necessary).
Stored procedure is very simple in idea, which is code encapsulation and reuse at database SQL language level.
2. Advantages and disadvantages of stored procedures
2.1 the advantages
Stored procedures can encapsulate and hide complex business logic.
Stored procedures can send back values and can accept parameters.
The stored procedure cannot be used
A stored procedure cannot be run using the SELECT specification because it is a subroutine, unlike lookup tables, data tables, or function definitions.
Stored procedures can be used to validate data, enforce business logic, and so on.
2.2 disadvantages
Stored procedures are often customized on a particular database because of the different programming languages supported. When switching to another vendor’s data system, the existing stored procedures need to be rewritten.
Performance tuning writing of stored procedures is limited to a variety of database systems.
What are the features of stored procedures
There are input and output parameters, variables can be declared, if/else, case,while and other control statements, by writing stored procedures, you can achieve complex logic functions.
Common features of functions: modularization, encapsulation, code reuse.
Fast, only the first execution needs to go through the compilation and optimization steps, subsequent calls can be directly executed, skip the above steps.
Four, actual combat operation
Generating tens of millions of data volumes
First we create 2 tables, one table is to store real data, the other table to create a memory table for temporary storage of data. We can be very efficient with stored procedures.
4.1 Creating memory tables and common tables
Create a normal table
CREATETABLEuser(
Idint (11) NOTNULLAUTO_INCREMENTprimarykeycomment ‘primary key id,
User_idvarchar (36) NOTNULLcomment ‘user id’,
User_namevarchar (30)NOTNULLcomment’ user name ‘,
Phonevarchar (20)NOTNULLcomment’ mobile phone number ‘,
Lan_idint (9)NOTNULLcomment’ local ‘,
Region_idint (9) NOTNULLcomment ‘area’,
Create_timedatetimeNOTNULLcomment ‘creation time,
KEYidx_user_id(user_id)
)ENGINE=InnoDBDEFAULTCHARSET=utf8mb4;
Create a memory table
CREATETABLEuser_memory(
Idint (11) NOTNULLPRIMARYKEYAUTO_INCREMENTcomment ‘primary key id,
User_idvarchar (36) NOTNULLcomment ‘user id’,
User_namevarchar (30)NOTNULLcomment’ user name ‘,
Phonevarchar (20)NOTNULLcomment’ mobile phone number ‘,
Lan_idint (9)NOTNULLcomment’ local ‘,
Region_idint (9) NOTNULLcomment ‘area’,
Create_timedatetimeNOTNULLcomment ‘creation time,
KEYidx_user_id(user_id)
)ENGINE=MEMORYDEFAULTCHARSET=utf8mb4;
4.2 Creating a Stored Procedure
4.2.1 Creating and generating N random number functions
# Generate N random numbers
DELIMITER$$
CREATEFUNCTIONrandNumber(Nint)RETURNSVARCHAR(255)
begin
Define a default value of 0-9
declarechars_strvarchar(20)default’0123456789′;
# store the random value in return_str
DECLAREreturn_strvarchar(255)DEFAULT”;
Defines a variable to determine the number of arguments in a loop
DECLAREiINTDEFAULT0;
WHILEi<n
DO
Use a random function to add chars_str to a random number
SETreturn_str=concat(return_str,substring(chars_str,FLOOR(1+RAND()*10),1));
Add 1 to the variable
SETi=i+1;
ENDWHILE;
The final result is returned
RETURNreturn_str;
END$$
DELIMITER;
4.2.2 Generating mobile Phone Numbers
Generate random phone numbers
DELIMITER$$
CREATEFUNCTIONgenePhone()RETURNSvarchar(20)
BEGIN
Define a variable to hold
DECLAREheadchar(3);
# define a variable to hold the last generated phone number
DECLAREphonevarchar(20);
# Define a variable to store the beginning of a common phone number
DECLAREbodysvarchar(100)default”130 131 132 133 134 135 136 137 138 139 186 187 189 151 157″;
# Define a variable to store the start of the phone to capture
DECLAREstartsint;
Get a random index starting with a phone number
SETstarts=1+floor(rand()*15)*4;
Use substring to capture the beginning of the phone
SEThead=trim(substring(bodys,starts,3));
Concatenate head with the stored procedure just defined and send the final result to the phone
SETphone=trim(concat(head,randNumber(8)));
# data return
RETURNphone;
END$$
DELIMITER;
4.2.3 Generating random Strings
# create random string and random time function
DELIMITER$$
CREATEFUNCTIONrandString(NINT)RETURNSvarchar(255)CHARSETutf8mb4
DETERMINISTIC
BEGIN
# Define a string to store common characters, letters and numbers
DECLAREchars_strvarchar(100)DEFAULT’abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789! @ # $% ^ & * () _ +} {” 😕 <,. / >? <? ‘;
# Define a string to return
DECLAREreturn_strvarchar(255)DEFAULT”;
# define a number of times to hold each loop
DECLAREiINTDEFAULT0;
WHILEi<nDO
Append a random string from chars_str to return_str
SETreturn_str=concat(return_str,substring(chars_str,FLOOR(1+RAND()*79),1));
SETi=i+1;
ENDWHILE;
The final result is returned
RETURNreturn_str;
END$$
DELIMITER;
4.2.4 Inserting N Entries into a MEMORY table
Create insert memory table Data stored procedure insert as many rows of data as the input parameter N is
DELIMITER$$
CREATEPROCEDUREadd_user_memory(INNint)
BEGIN
DECLAREiINTDEFAULT1;
WHILE(i<=n)DO
INSERTINTOuser_memory(user_id,user_name,phone,lan_id,region_id,create_time)VALUES(uuid(),randString(20),genePhone(),FLOO R(RAND()*1000),FLOOR(RAND()*100),NOW());
SETi=i+1;
ENDWHILE;
END$$
DELIMITER;
# loop to get data from memory table and insert it into regular table
N indicates how many times the loop is called; Count indicates the amount of data that is inserted into the memory table and the regular table each time
DELIMITER$$
CREATEPROCEDUREadd_user_memory_to_outside(INnint,INcountint)
BEGIN
DECLAREiINTDEFAULT1;
WHILE(i<=n)DO
CALLadd_user_memory(count);
INSERTINTOuserSELECT*FROMtest_user_memory;
deletefromser_memory;
SETi=i+1;
ENDWHILE;
END$$
DELIMITER;
4.3 Inserting Data for testing
Insert 10000 data from memory table into normal table
CALLadd_user_memory(10000);
Insert table data into regular table at one time, this process is very fast
INSERTINTOuserSELECT*FROMuser_memory;
Empty memory table data
deletefromuser_memory;
selectcount(1)fromuser
# 10000
Generate tens of millions of bytes using add_user_memory_to_outside
00 calladd_user_memory_to_outside (1000100)
# 10010000
Summary: This blog explains what a stored procedure is and what it can do. If MySQL stores a lot of data, it is very slow to query based on conditions. In tens of millions of data volume we query a piece of query data will need more than 3 seconds of time, often in the enterprise query a data need several seconds of time is certainly not, the next chapter to explain to you how to make a SQL statement in 0.0 milliseconds to query the data we want.