Create a simple stored procedure
Basic operations
1 /*querying a database for all stored procedures*/2Showprocedurestatus;3 4 /*queries are a stored procedure*/ 5ShowCreate proceduretes1; 6 7 /*create a stored procedure*/8 CREATE PROCEDUREtest1;9 Ten /*Delete a stored procedure "without parentheses ()"*/ One DROP PROCEDUREtest1; A - /*executes a stored procedure "must have parentheses, regardless of parameters "*/ -Call Test1 ();
Create
1 DROP PROCEDURE' Proc_test ' (2 inchAint, 3 inchBint, 4Outsum int5 )6 BEGIN7 DECLARECint;8 ifA is NULL Then SetA= 0; 9 End if;Ten One ifB is NULL Then SetB= 0; A End if; - - Set sum =A+b; the END;
Perform the above stored results to verify that the results are correct, such as the result OK:
Set @b = 5 ; -- Execute Call Proc_adder (2,@b,@s); Select @s as sum;
Control statements in a stored procedure
If statement:
1 DROP PROCEDURE IF EXISTSStutse;2 /*use of stored if*/3 CREATE PROCEDUREStutse (4 inchAxINT5 )6 BEGIN7 DECLARECVARCHAR( -);8 9 IFAx= 0 Then Ten SETC="I am c,ax for 0"; OneELSEIF Ax= 1 Then A SETC="I am c,ax for 1"; - ELSE - SETC="I am C"; the END if; - SELECTC; - - END; + - SET @ax=4; +Call Stutse (@ax);
Case statement:
1 DROP PROCEDURE IF EXISTStest2;2 /*use of stored case*/3 CREATE PROCEDURETest2 (4 inchAINT 5 )6 BEGIN 7 DECLAREBVARCHAR( -);8 9 CaseaTen when 0 Then One SETB=" the"; A when 1 Then - SetB="111"; - ELSE the SETB="BBBB"; - END Case; - - SELECTb; + END; - + SET @a=2; ACall Test2 (@a);
The drawbacks of stored procedures
Different database, syntax difference is very big, transplant difficulty, change the database, need to write again;
Bad management, put too much business logic in the stored procedure is not good maintenance, not conducive to hierarchical management, easy chaos, general stored procedures for individual performance requirements of the business, the other need is not very large;
...
MySQL preliminary understanding of stored procedures