MySQL stored procedure creation and considerations

MySQL tutorial stored procedure creation and considerations
The first MySQL stored procedure is a little different from the stored procedure that wrote SQL Server 2005 before;

Second, the use of MySQL third-party Enterprise Manager sqlyogent.exe debugging, write stored procedures and general query statements to separate, I was later realized, angry (probably also with SQL Server 2005 Enterprise Manager editor of the habit caused). Needless to say, write stored procedures, to note the following points:

The first is to define delimiter//, it means to use//as a terminator of a sentence (this is very important, I write the stored procedure syntax is not wrong, but, is not pass, the original semicolon:; The delimiter is to notify the MySQL client has entered the completion of the symbol, and my stored procedures inside a lot have semicolons; As a result, it's time to run a stored procedure into sections.

Second, if you want me to be "unfortunate" selected third party MySQL client sqlyogent.exe, be sure to right-click the stored procedure in the Database Tutorial directory to select the new stored procedure, and then type your stored procedure statement in the pop-up window on the right, and the input in the normal Query Analyzer is invalid. It's a bitter lesson.

Third, if the parameters of the stored procedure are entered in Chinese, it is necessary to add the character set GBK code after the definition of the stored procedure, otherwise the calling stored procedure will have an error when it uses Chinese parameters, such as:

CREATE PROCEDURE Countpro (out a_out int,in b_date date, in Unit_name varchar () Character Set GBK)
And four, if you need a fuzzy query inside your stored procedure, use the like '% content% ' to not write '% ' in the SELECT statement, and define a parameter with: set wherestr = '% ' +wherestr+ '% '; Splicing statement V Finally, to restore the end character: delimiter; As a terminator of a statement

Here's a look at the MySQL stored procedure instance

1. Create an instance database
Create DATABASE DB5;
Use DB5;

2. Create a simple worksheet and insert the data
CREATE TABLE T (s1 int);
INSERT into T values (5);

3. Create a program instance procedure example
CREATE PROCEDURE p1 () select * from T;
The first part of the SQL statement stored procedure is the CREATE PROCEDURE
The second part is procedure name: the name of the new stored procedure above is P1.
The third part of the book parameter list (), part Four is the main body of the program, "SELECT * from T"

What kind of MySQL statement is legal in the stored procedure body?
You can include all legitimate SQL database definition languages, insert,update,delete,drop,create,replace, and so on in the stored procedure body
Include (Set,commit,rollback) However, if you include the MySQL extensions in your code, the code will not be ported.

4. To invoke the stored procedure, you need to enter the call and your procedure name and a bracket.
Call P1 ();

5. The characteristic clause in the process
CREATE PROCEDURE P2 () language SQL
Not deterministic
SQL Security Definer
Comment '
Select Current_date,rand () from T;

6.parameters parameters
CREATE PROCEDURE P5 ()------; The argument list is empty
CREATE PROCEDURE P5 ([in] name Data-type)----//input parameter in optional, default to parameter in
Create PROCEDURE P5 (out name Data-type)-----//Output parameter out
Create PROCEDURE P5 (inout so data-type)-----//That can be done input parameters can also do output parameters

----input parameter in example.
CREATE PROCEDURE P5 (p int) set @x=p;
Call P5 (12345);
Select @x;

----OUTPUT Parameter out example
CREATE PROCEDURE P6 (out P int) set p=-5;
Call P6 (@y);
Select @y;

7. Compound statement: If you have more than one statement in the process, then you need to begin/end block. Here you can define the variables and control the flow.
First execute command delimiter//
CREATE PROCEDURE P7 ()
Begin
Set @a=6;
Set @b=5;
INSERT into T values (@a);
Select S1 * @a from T where s1>= @b;
end;//

  -----Declare variables in compound statements
       CREATE PROCEDURE P8 ()
             BEGIN
       declare a int ;
       declare b int;
       set a=5;
       set b=5;
       insert INTO T values (a);
       Select S1*a from T where s1>=b;
            end;//

       examples with default statement-tongue-setting statements
       Create Procedure P9 ()
       begin
           Declare a, b int default 5;
   insert INTO T values (a);
   Select S1*a from T where s1>=b;
       end;//

8. Scope scope Issues: Internal variables enjoy higher precedence within their scope, and when execution to end
        variable, internal variables disappear, At this point, the variable is no longer visible outside of its scope, and should not be found beyond the storage
  procedure, but you can save its value by using the out parameter or assigning its value
  to the session variable.
   CREATE PROCEDURE P11 ()
   begin
      declare x1 char (5) def Ault ' outer ';
      begin
        declare x1 char (5) Default ' Inner ';
 select X1;
      end;
      Select x1;
   end;//
  
Conditional statements in/*********************************  stored procedures   *********** ****************************/

1. If-then-else Statement
    CREATE PROCEDURE p12 (in parameter int)
    begin
&nbs p;     declare var int;
      set var=parameter+1;
      If var=0 then
        insert into t values ( 17);
      End If;
      If parameter=0 then
        update t set s1= s1+1;
      Else
        update T set s1=s1+2
      End If;
    end;//

2. Case instruction: If more conditions are needed to determine the true and false, we can use the statement
   CREATE PROCEDURE p13 (in parameter int)
     BEGIN
       declare var int;
       Set var=parameter+1;
       case Var
         0 Then INSERT into T values (17);
  When 1 then inserts into T values (18);
  Else insert into T values (19);
       End case;
    end;//

/***************************** Loop Statement ************************************/
1. While End while; Loop statement
CREATE PROCEDURE P14 ()
Begin
declare var int;
Set var=0;
While var<6 do
INSERT into T values (VAR);
Set var=var+1;
End while;
end;//

2. Repeat End repeat, which checks the result after the action is performed while the while is checked before execution
CREATE PROCEDURE P15 ()
Begin
declare v int;
Set v=0;
Repeat
INSERT into T values (v);
Set v=v+1;
Until V>=5
End repeat;
end;//

3. Loop End Loop; The loop loop does not require an initial condition, which is similar to the while loop, and the repeat
Loop does not require an end condition, the meaning of the leave statement is to leave the loop,
CREATE PROCEDURE p16 ()
Begin
declare v int;
Set v=0;
Loop_lable:loop
INSERT into T values (v);
Set v=v+1;
If v >=5 Then
Leave loop_lable;
End If;
End Loop;
end;//

4. Lables Label: Marking can be used before the begin repeat while or LOOP statement, the statement label can only be
Valid statements are used before. You can jump out of the loop so that the running instruction reaches the last step of the compound statement.

/***************************** Iterate iterative ***************************************/

1. Iterate: To start a new compound statement by referencing the label of a compound statement
    CREATE PROCEDURE P20 ()
      Begin
        declare v int;
 set v=0
 loop_lable:loop
& nbsp;         if V=3 then
      set v=v+1;
& nbsp;     iterate loop_lable;
   End If;
   insert into T values (v);
   set v=v+1;
   If v>=5 then
     leave loop_lable;
   End If;
 end Lo Op
      end;//