MySQL stored procedures

Read Catalogue

• What is a stored procedure
• What are the features
• Create a simple stored procedure
• Control statements in a stored procedure
• The drawbacks of stored procedures

What is a stored procedure

Simply put, is a set of SQL statements, powerful, can implement some more complex logic functions, similar to the Java language method;

PS: The stored procedure is a bit like a trigger, a set of SQL sets, but the stored procedure is called actively, and the function is more powerful than the trigger, and the trigger is automatically called after something is triggered;

What are the features

There are input and output parameters, can declare variables, have If/else, case,while and other control statements, by writing stored procedures, can achieve complex logic functions;

General features of functions: modularity, encapsulation, code reuse;

Fast, only the first execution needs to be compiled and optimized steps, subsequent calls can be directly executed, eliminating the above steps;

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;

--------------------------------Procedure structure for ' proc_adder '------------------------------DROPPROCEDUREIFEXISTS' Proc_adder ';D elimiter;;CREATE Definer= ' root ' @ ' localhost 'PROCEDURE ' Proc_adder ' (In aIntIn Bint, outSumInt)BEGIN#Routine body goes here ...DECLARE CInt;If aIsNullThenSet a= 0end if if b is null then" Span style= "COLOR: #0000ff" >set b = 0< Span style= "COLOR: #000000" >; 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:

@b=5; call Proc_adder (2,@b,@s);  sum;       

Control statements in a stored procedure

If statement:

--------------------------------Procedure structure for ' proc_if '------------------------------DROPPROCEDUREIFEXISTS' Proc_if ';D elimiter;;CREATE Definer= ' root ' @ ' localhost 'PROCEDURE ' proc_if ' (In typeInt)BEGIN#Routine body goes here ...DECLARE Cvarchar500);IF type=0ThenSet 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 '------------------------------DROPPROCEDUREIFEXISTS' Proc_case ';D elimiter;;CREATE Definer= ' root ' @ ' localhost 'PROCEDURE ' Proc_case ' (In typeInt)BEGIN#Routine body goes here ...DECLARE Cvarchar500);CaseTypeWhen0ThenSet 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 '------------------------------DROPPROCEDUREIFEXISTS' Proc_while ';D elimiter;;CREATE Definer= ' root ' @ ' localhost 'PROCEDURE ' Proc_while ' (In nInt)BEGIN#Routine body goes here ...DECLARE IInt;DECLARE sInt;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                 

Other: Slightly ~

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 stored procedures