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

MySQL Database Advanced (4)-- stored procedure 1, introduction to stored procedure 1, introduction to stored procedure

A stored procedure is a programmable function composed of a set of SQL statements with specific functions, which is compiled and created and saved in the database. The user can call and execute it by specifying the name of the stored procedure and giving parameters.

Stored procedure is one of the commonly used technologies in database management, which can easily do some similar work such as data statistics and data analysis. SQL SERVER, ORACLE and MySQL all support stored procedures, but the syntax structures of different database environments are different.

2. Advantages of stored procedures

A. stored procedures enhance the function and flexibility of SQL language. The stored procedure can be written with flow control statements, which has strong flexibility and can complete complex judgments and more complex operations.

B. stored procedures allow standard component programming. After a stored procedure is created, it can be called multiple times in the program without having to rewrite the SQL statement of the stored procedure. And database professionals can modify the stored procedure at any time, which has no effect on the application source code.

C, stored procedures can achieve faster execution speed. If an operation contains a large amount of Transaction-SQL code or is executed multiple times separately, the stored procedure executes much faster than batch processing. Because the stored procedure is precompiled. When a stored procedure is run for the first time, the optimizer analyzes and optimizes it, and gives the execution plan that is eventually stored in the system table. Batch Transaction-SQL statements are compiled and optimized each time they are run, which is relatively slow.

D. stored procedures can reduce network traffic. For the operation of the same database object (such as query, modification), if the Transaction-SQL statement involved in the operation is organized by a stored procedure, then when the stored procedure is called on the client computer, only the calling statement is transmitted in the network, thus greatly increasing the network traffic and reducing the network load.

E, stored procedures can be used as a security mechanism to make full use of. By restricting the permissions of a stored procedure, the system administrator can restrict the access to the corresponding data, avoid the access of unauthorized users to the data, and ensure the security of the data.

2. The use of stored procedures 1. The creation of stored procedures

Syntax for creating a stored procedure:

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: indicates that the value of this parameter must be specified when the stored procedure is called. The value that modifies the parameter in the stored procedure cannot be returned and is the default value.

OUT output parameter: this value can be changed inside the stored procedure and returned.

INOUT input and output parameters: specified when called, and can be changed and returned.

A. creation of stored procedures with no parameters

Create a stored procedure to find the top three students with the highest average score

Create procedure getMax () BEGINselect a.sname as' name', AVG (b.mark) as' average score 'from TStudent a join TScore b on a.studentID=b.studentIDgroup by b.studentID order by' average score 'DESC limit 3ten end'

B. creation of stored procedures with input parameters

Find the top three students with the highest average scores in a given class

Create procedure getMaxByClass (in classname VARCHAR (10)) BEGINselect a.sname as' name', AVG (b.mark) as' average score 'from TStudent a join TScore b on a.studentID=b.studentID where a.class=classnamegroup by b.studentID order by' average score 'DESC limit 3ten end

C, the creation of stored procedures with input and output parameters

According to the input class, find the student with the largest student number, and store the student number into the output parameters.

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. Call of stored procedure

Call sp_name [(passing parameters)]

The stored procedure name must be followed by parentheses, even if the stored procedure has no parameters passed.

4. View the information of stored procedures

Show procedure status

Displays basic information about all stored procedures in the database, including the database, the name of the stored procedure, the creation time, and so on.

Show create procedure sp_name

Displays the details of a stored procedure.

5. Use stored procedures to insert data 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' Sun WuKong', 'male'); 6. Use stored procedures to delete data

Delete the student's grades first according to the student number provided, and then delete the student.

Create procedure deleteStudent (in sid CHAR (5)) BEGINdelete from TScore where studentID=sid;delete from TStudent where studentID=sid;END;7, using stored procedure backup to restore data

A. use 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 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 the table name

Create a stored procedure, construct a new table name using the current event of the system, and back up the records in the Tstudent table.

Create procedure backupStudentByDateTime () BEGINDECLARE tablename VARCHAR (20); set tablename = CONCAT ('Table', 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. Restore data using stored procedures

Create a stored procedure, according to the input student number to restore the record from the specified table, the stored procedure first deletes the TStudent table student record of the specified student number, and then inserts the student 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=' Sun WuKong 'where studentID='00997'

Restore data from the specified table

Call restoreStudent ('00997,' Table20180404215950')

View the results of the restore

Select * from TStudent where studentID='00997'

3. Stored procedure example 1. Add students to the database table create procedure addStudent (in num int) begindeclare I int;set item1 * delete from TStudent While num > = i doinsert TStudent values (LPAD (convert (iPowerchar (5)), 5djime (4)), CreateName (), if (ceil (rand () * 10)% 2.0) 'male', 'female'), RPAD (convert (ceil (rand () * 1000000000000000000), char (18)), 18pm (0')), Concat (convert (ceil (rand () * 10) + 1980 last char (4),'-', LPAD (convert (rand ()) * 12) Char (2)), 2 rand ()),'-', LPAD (convert (ceil (rand () * 28), char (2), 2)), Concat (PINYIN (sname),'@ hotmail.com'), case ceil (rand () * 3) when 1 then 'network and website development' when 2 then 'JAVA' ELSE' NET' END, NOW ()) Set iperformance 1: end while;select * from TStudent;end2, add grades create procedure fillScore () beginDECLARE St_Num INT;DECLARE Sb_Num INT;DECLARE i1 INT;DECLARE i2 INT;set i1 to students: 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 While Sb_Num > = i2 doinsert TScore values (LPAD (convert (i1jinchar (5)), 5memento 0'), LPAD (convert (i2jingchar (4)), 4memorial0'), ceil (50+rand () * 50)); set i2memi1eEND WHILE;set i1flagi1nd end WHILE;end

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.