MySQL stored procedure Simple instance

Transferred from: http://www.cnblogs.com/zhuawang/p/4185302.html

*********************Create a table*****************************/delimiter//DROP TABLE if existsTest//CREATE TABLETest (IDint( One)NULL) ///********************** One of the simplest stored procedures **********************/Drop procedure if existsSp// CREATE PROCEDURESP ()Select 1 //Call sp ()// /********************* stored procedure with input parameters *******************/Drop procedure if existsSp1//Create procedureSP1 (inchPint) Comment'INSERT into a int value'begin  /*define an shaping variable*/  DeclareV1int; /*assigning the value of an input parameter to a variable*/  SetV1=p; /*Perform an insert operation*/  Insert  intoTest (ID)Values(v1);End///*call this stored procedure*/Call SP1 (1)///*go to the database to see the results after the call*/Select *  fromTest// /****************** stored procedure with output parameters ************************/Drop procedure if existssp2//Create procedureSP2 (out Pint)/*The deterministic clause here indicates that both the input and output values are deterministic and will not be changed. A colleague of mine said that MySQL does not implement this function at present, so the addition is not deterministic*/Deterministicbegin  Select Max(ID) intoP fromtest;End///*call this stored procedure, note: The output parameter must be a variable with the @ sign*/Call SP2 (@pv)///*querying variables that have just been used in a stored procedure*/Select @pv//                                                    /******************** stored procedure with input and output parameters ***********************/Drop procedure if existssp3//Create procedureSP3 (inchP1int, out P2int)begin  ifP1= 1  Then    /*define a variable with the @ symbol plus the variable name, similar to declare*/    Set @v = Ten; Else    Set @v =  -; End if; /*A statement body can execute multiple SQL, but must be separated by semicolons*/  Insert  intoTest (ID)Values(@v); Select Max(ID) intoP2 fromtest; End///*call this stored procedure, note: The input parameter is a value, and the output parameter must be a variable with the @ sign*/Call SP3 (1,@ret)//Select @ret///***************** A stored procedure that does both input and output parameters ***************************************/Drop procedure if existsSp4//Create procedureSP4 (InOut P4int)begin   ifP4= 4  Then      Set @pg =  -; Else      Set @pg =  -; End if; Select @pg; End//Call SP4 (@pp)///*you need to set an assigned variable before passing it as a parameter .*/Set @pp = 4//Call SP4 (@pp)///********************************************************/

MySQL stored procedure Simple instance