Stored procedures in MySQL

Create a simple stored procedure

Stored procedure Proc_adder function is very simple, two integer input parameters A and b, an integer output parameter sum, the function is to calculate the input parameters A and B results, assigned to the output parameter sum;

A few notes:

DELIMITER;; : Previously said, put the default input terminator;

Definer: Creator;

Call: Calling a stored procedure, using the Invoke command

--------------------------------Procedure structure for ' proc_adder '------------------------------DROP Procedure IF EXISTS ' Proc_adder ';D elimiter;; CREATE definer= ' root ' @ ' localhost ' PROCEDURE ' proc_adder ' (in a int, in B int, out sum int) BEGIN    #Routine body goes here ...    DECLARE c int;    If a is null and then set a = 0;     End If;      If B is null and then set b = 0;    End If;    Set Sum  = a + b; END;;D Elimiter;

　　Perform the above stored results to verify that the results are correct, such as the result OK:

Set @b=5;call Proc_adder (2,@b,@s); select @s as Sum;

Control statements in a stored procedure

If statement:

--------------------------------Procedure structure for ' proc_if '------------------------------DROP Procedure if EXISTS ' proc_if ';D elimiter;; CREATE definer= ' root ' @ ' localhost ' PROCEDURE ' proc_if ' (in type int) BEGIN    #Routine body goes    here ... DECLARE c varchar (+);    IF type = 0 Then        set c = ' param is 0 ';    ELSEIF type = 1 then        set c = ' param is 1 ';    ELSE        Set c = ' param is others, not 0 or 1 ';    END IF;    Select C; END;;D Elimiter;

Case statement:

--------------------------------Procedure structure for ' proc_case '------------------------------DROP Procedure IF EXISTS ' proc_case ';D elimiter;; CREATE definer= ' root ' @ ' localhost ' PROCEDURE ' proc_case ' (in type int) BEGIN    #Routine body goes    here ... DECLARE c varchar (+);    Case type when    0 then        set c = ' param is 0 ';    When 1 then        set c = ' param is 1 ';    ELSE        Set c = ' param is others, not 0 or 1 ';    END case;    Select C; END;;D Elimiter;

Loop While statement:

--------------------------------Procedure structure for ' proc_while '------------------------------DROP Procedure IF EXISTS ' Proc_while ';D elimiter;; CREATE definer= ' root ' @ ' localhost ' PROCEDURE ' proc_while ' (in n int) BEGIN    #Routine body goes    here ... DECLARE I int;    DECLARE s int;    SET i = 0;    SET s = 0;    While I <= n do        set S = s + i;        Set i = i + 1;    END while;    SELECT s; END;;D Elimiter;

Stored procedures in MySQL