Stored procedures for MySQL database

One, stored procedures
1, definition: A set of programmable functions, is to complete a specific function of the SQL statement set, compiled and saved in the database, the user can specify the name of the stored procedure and given parameters (when required) to invoke the execution

2, Advantages: ① A number of repetitive operations, encapsulated into a stored procedure, simplifying the call to these SQL
② batch processing: sql+ cycle, reduce the flow, that is, "Run the batch"
③ Unified interface to ensure data security

3. > Stored procedure is a piece of code with a name that is used to complete a specific function.
The stored procedure created by > is saved in the data dictionary of the database.

4. Basic Creation Format

1Delimiter//    --set the end tag of the SQL statement to//2 DROP PROCEDURE IF EXISTSTestprint;--If a stored procedure that has that name is deleted3 CREATE PROCEDURETestprint ()--create a stored procedure named Testprint,--the parentheses can be used to pass parameters: Create PROCEDURE mytest (in Sid int,in Suid INT)4 BEGIN    --Code section starts5 SELECT 'Hello, Chengdu'  asInfos;--the code portion of the stored procedure6 END;--end of code section7 //    --end Tag8Delimiter--restores the end tag of an SQL statement after writing the compilation

5. Call

-- calling a non-parametric stored procedure Call Testprint (); -- to invoke a stored procedure with parameters Set @stuid = 1 ; SET @subid = 1 ; Call MyTest (@stuid,@subid);    

6. Parameters of the stored procedure
A stored procedure can have 0 or more parameters for the definition of a stored procedure.
3 Types of parameters:
In input parameter: Indicates that the caller passed in a value to the procedure (the incoming value can be literal or variable)
Out output parameter: Indicates that the procedure has outgoing values to the caller (multiple values can be returned) (Outgoing values can only be variables)
InOut input/Output parameter: Indicates whether the caller passed in a value to the procedure, or that the procedure sent a value to the caller (the value can only be a variable)
It is recommended that:> input values use the in parameter;
> return value using out parameters;
The >inout parameter is used as little as possible.
Example:
1), incoming student and subject ID, get the student's achievement in this subject

1 delimiter $$2 DROP PROCEDURE IF EXISTSmytest;3 CREATE PROCEDUREMyTestinchSidINT,inchSuidINT)4 BEGIN5 UPDATEScoreSETScore.scscore=Scscore+5 6 WHEREScore.sid=Sid andScore.suid=suid;7 END;8 $$9 delimiter;Ten  One Set @stuid = 1; A SET @subid = 1; -Call MyTest (@stuid,@subid);

2) 、--incoming student serial number to get the name of the student

1 delimiter $$2 DROP PROCEDURE IF EXISTSGetstuname;3 CREATE PROCEDUREGetstuname (inchSidINT, Out StunameVARCHAR( -))4 BEGIN5 SELECTStudent.sname intoStuname fromStudentWHEREStudent.sid=SID;6 END;7 $$8 delimiter;9 Ten Set @stuid = 2; OneCall Getstuname (@stuid,@stuname); A SELECT @stuid  asSid@stuname  asStuname;

Stored procedures for MySQL database