MySQL database Advanced (quad)--Stored procedures

MySQL database Advanced (iv)--stored procedure one, introduction to Stored Procedures 1, Introduction to Stored Procedures

A stored procedure is a set of programmable functions that have a specific set of SQL statements that are compiled and saved in the database, and can be invoked by the user by specifying the name of the stored procedure and given a parameter.
Stored procedure is one of the commonly used techniques in database management, it can be easily done like data statistics, data analysis and so on, SQL SERVER, ORACLE, MySQL all support stored procedure, but different database environment syntax structure differ.

2, the advantages of stored procedures

A, stored procedures enhance the functionality and flexibility of the SQL language. Stored procedures can be written with flow control statements, with a strong flexibility to complete complex judgments and more complex operations.
B, stored procedures allow standard component-type programming. After a stored procedure is created, it can be called multiple times in the program without having to rewrite the SQL statement for the stored procedure. and database professionals can modify stored procedures at any time, without affecting the source code of the application.
C, the stored procedure can achieve a faster execution speed. If an operation contains a large number of Transaction-sql code or is executed more than once, the stored procedure is much faster than the batch execution. Because the stored procedure is precompiled. When you run a stored procedure for the first time, the optimizer optimizes it for analysis and gives the execution plan that is ultimately stored in the system table. The batch TRANSACTION-SQL statements are compiled and optimized each time they are run, relatively slowly.
D, stored procedures can reduce network traffic. For operations of the same database object (such as queries, modifications), if the Transaction-sql statement involved in the operation is organized by a stored procedure, when the stored procedure is called on the client computer, only the calling statement is transmitted on the network, which greatly increases network traffic and reduces network load.
E, stored procedures can be used as a security mechanism to make full use of. The system administrator restricts the access to the corresponding data by executing the permission of a stored procedure, avoids the unauthorized user's access to the data, and ensures the security of the data.

Second, the use of stored procedures 1, the creation of stored procedures

syntax for creating stored procedures:

CREATE    [DEFINER = { user | CURRENT_USER }]　PROCEDURE sp_name ([proc_parameter[,...]])    [characteristic ...] routine_bodyproc_parameter:    [ IN | OUT | INOUT ] param_name typecharacteristic:    COMMENT ‘string‘  | LANGUAGE SQL  | [NOT] DETERMINISTIC  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }  | SQL SECURITY { DEFINER | INVOKER }routine_body:　　Valid SQL routine statement[begin_label:] BEGIN　　[statement_list]　　　　END [end_label]

In input parameter: The value that represents the parameter must be specified when the stored procedure is called, and the value that modifies the parameter in the stored procedure cannot be returned as the default value.
Out output parameter: This value can be changed inside the stored procedure and can be returned.
InOut input and OUTPUT parameters: specified on invocation, and can be changed and returned.
A, parameter-Free stored procedure creation
Create a stored procedure that finds the top three students with the highest average score

create procedure getMax()BEGINselect a.sname as ‘姓名‘, AVG(b.mark) as ‘平均分‘ from TStudent a join TScore b on a.studentID=b.studentIDgroup by b.studentID order by ‘平均分‘ DESC limit 3;END;

B, stored procedure creation with input parameters
Find the top three students with the highest average score for a given class

create procedure getMaxByClass(in classname VARCHAR(10))BEGINselect a.sname as ‘姓名‘, AVG(b.mark) as ‘平均分‘ from TStudent a join TScore b on a.studentID=b.studentID where a.class=classnamegroup by b.studentID order by ‘平均分‘ DESC limit 3;END

C, stored procedure creation with input parameters and output parameters
According to the input class, find the student who has the largest number, and store the study number in the output parameter.

create procedure getMaxSIDByClass(IN classname VARCHAR(20), out maxid int)BEGINselect MAX(studentID) into maxid from TStudent where class=classname;END;

2. Deletion of stored procedures

drop procedure sp_name;
You cannot delete another stored procedure in one stored procedure, only another stored procedure can be called.

3, the call of the stored procedure

call sp_name[(传参)];
The stored procedure name must be appended with parentheses, even if the stored procedure has no parameter passed.

4. View of stored procedure information

show procedure status;
Displays basic information about all stored procedures in the database, including the owning database, stored procedure name, creation time, and so on.
show create procedure sp_name;
Displays detailed information about a stored procedure.

5. Inserting data using stored procedures

create procedure insertTStudent(in sid CHAR(5), name CHAR(10), ssex CHAR(1))BEGINinsert into TStudent (studentID, sname, sex)VALUES(sid, name, ssex);select * from TStudent where studentID=sid;END;call insertTStudent(‘01020‘,‘孙悟空‘,‘男‘);

6. Using stored procedures to delete data

Delete Students ' grades and delete students based on the number of students they provide.

create procedure deleteStudent(in sid CHAR(5))BEGINdelete from TScore where studentID=sid;delete from TStudent where studentID=sid;END;

7. Restore data using stored procedure backup

A. Using stored procedures to back up data
Create a stored procedure to back up the student table, create a new table based on the specified table name, and import the records from the Tstudent table into the new table.

create procedure backupStudent(in tablename CHAR(10))BEGINset @sql1=CONCAT(‘create table ‘,tablename,‘(studentID VARCHAR(5),sname VARCHAR(10),sex CHAR(1),cardID VARCHAR(20),Birthday DATETIME,email VARCHAR(20),class VARCHAR(10),enterTime DATETIME)‘);prepare CT1 from @sql1;EXECUTE CT1;set @sql2=CONCAT(‘insert into ‘, tablename, ‘(studentID,sname,sex,cardID,Birthday,email,class,enterTime)select studentID,sname,sex,cardID,Birthday,email,class,enterTime from TStudent‘);PREPARE CT2 from @sql2;EXECUTE CT2;END;call backupStudent(‘table2019‘);

B. Back up data using the current time as a table name
Create a stored procedure, use the system current event to construct a new table name, and back up the records in the Tstudent table.

create procedure backupStudentByDateTime()BEGINDECLARE tablename VARCHAR(20);set tablename = CONCAT(‘Table‘, REPLACE(REPLACE(REPLACE(now(),‘ ‘,‘‘),‘:‘,‘‘),‘-‘,‘‘));set @sql1=CONCAT(‘create table ‘,tablename,‘(studentID VARCHAR(5),sname VARCHAR(10),sex CHAR(1),cardID VARCHAR(20),Birthday DATETIME,email VARCHAR(20),class VARCHAR(10),enterTime DATETIME)‘);prepare CT1 from @sql1;EXECUTE CT1;set @sql2=CONCAT(‘insert into ‘, tablename, ‘(studentID,sname,sex,cardID,Birthday,email,class,enterTime)select studentID,sname,sex,cardID,Birthday,email,class,enterTime from TStudent‘);PREPARE CT2 from @sql2;EXECUTE CT2;ENDcall backupStudentByDateTime();

C. Using stored procedures to restore data
To create a stored procedure that restores a record from a specified table according to the input number, the stored procedure first deletes the student record of the tstudent table of the specified school number, and then inserts the pupil into the Tstudent table from the specified table.

create procedure restoreStudent(in sid VARCHAR(5), in tablename VARCHAR(20))BEGINset @sql1=concat(‘delete from TStudent where studentid=‘,sid);prepare CT1 from @sql1;EXECUTE CT1;set @sql2=concat(‘insert into TStudent (Studentid,sname,sex,cardID,Birthday,Email,Class,enterTime)  select Studentid,sname,sex,cardID,Birthday,Email,Class,enterTime  from ‘,tablename,‘ where studentid=‘,sid);prepare CT2 from @sql2;EXECUTE CT2;END;

Modify a student's record
update TStudent set sname=‘孙悟空‘ where studentID=‘00997‘;
Recovering data from a specified table
call restoreStudent(‘00997‘, ‘Table20180404215950‘);
View the results of a restore
select * from TStudent where studentID=‘00997‘;

Third, the stored procedure instance 1, adds the student to the database table

create procedure addStudent(in num int)begindeclare i int;set i=1;delete from TStudent;while num>=i doinsert TStudent values (       LPAD(convert(i,char(5)),5,‘0‘),       CreateName(),       if(ceil(rand()*10)%2=0,‘男‘,‘女‘),       RPAD(convert(ceil(rand()*1000000000000000000),char(18)),18,‘0‘),       Concat(convert(ceil(rand()*10)+1980,char(4)),‘-‘,LPAD(convert(ceil(rand()*12),char(2)),2,‘0‘),‘-‘,LPAD(convert(ceil(rand()*28),char(2)),2,‘0‘)),       Concat(PINYIN(sname),‘@hotmail.com‘),       case ceil(rand()*3) when 1 then ‘网络与网站开发‘ when 2 then ‘JAVA‘ ELSE ‘NET‘ END,       NOW());set i=i+1;end while;select * from TStudent;end

2. Add grades to students

create procedure fillScore()beginDECLARE St_Num INT;DECLARE Sb_Num INT;DECLARE i1 INT;DECLARE i2 INT;set i1=1;set i2=1;delete from TScore;select count(*) into St_Num from TStudent;select count(*) into Sb_Num from TSubject;while St_Num>=i1 doset i2=1;while Sb_Num>=i2 doinsert TScore values (LPAD(convert(i1,char(5)),5,‘0‘),LPAD(convert(i2,char(4)),4,‘0‘),ceil(50+rand()*50));set i2=i2+1;END WHILE;set i1=i1+1;END WHILE;end

MySQL database Advanced (quad)--Stored procedures