Notes for writing stored procedures in MySQL

I. MySQL Stored Procedure

First, let's talk about the creation Syntax:

CREATE PROCEDURE sp_name ([proc_parameter[,...]])    [characteristic ...] routine_body

Here is a simple example.

"Pr_add" is a simple MySQL stored procedure. This MySQL stored procedure has two int-type input parameters: "A" and "B", and returns the sum of the two parameters.

drop procedure if exists 'procDailyOnlineTime';  

DELIMITER //CREATE  PROCEDURE `procDailyOnlineTime`(cal_date VARCHAR(11),redo tinyint(1))begindeclare tbl_name VARCHAR(30);declare date_str VARCHAR(20);if length(cal_date)=0 then set cal_date = date_format(sysdate(),'yyyy-MM-dd'); end if;if(redo=1) thendelete from user_online_time where biz_date=cal_date;end if;select @flag:= count(id) from user_online_time where biz_date=cal_date;if(@flag=0) thenset date_str = replace(cal_date,'-','_');set tbl_name = concat('basic_player_log_',date_str); set @temp_sql = concat("insert into user_online_time (account_id,account_name,char_id,char_name,online_time,biz_date,create_time) select account_id,account_name,char_id,char_name,sum(online_time),'",cal_date,"',now() from ",tbl_name," group by char_id");prepare cmd from @temp_sql; execute cmd;deallocate prepare cmd;end if;end //DELIMITER ;

Ii. Call the MySQL Stored Procedure

call procDailyOnlineTime('2012-11-11', 0);  

Execute the MySQL stored procedure. The stored procedure parameter is a MySQL user variable.

set @a = 10;   set @b = 20;   call pr_add(@a, @b);  

Iii. MySQL stored procedure features

The simple syntax for creating a MySQL stored procedure is:

Create procedure stored procedure name () ([in | Out | inout] datatype) Begin MySQL statement; end;

If "in", "out", and "inout" are not explicitly specified for MySQL stored procedure parameters, the default value is "in ". Traditionally, we will not explicitly specify the "in" parameter.

1. The "()" after the MySQL stored procedure name is required. Even if there is no parameter, "()" is required.

2. For MySQL stored procedure parameters, you cannot add "@" before the parameter name, for example, "@ A int ". The following syntax for creating a stored procedure is incorrect in MySQL (correct in SQL Server ). You do not need to add "@" before the variable name in the MySQL stored procedure, although the mysql client user variable requires "@".

Create procedure pr_add (@ A int, -- error B INT -- correct)

3. The default value cannot be specified for MySQL stored procedure parameters.

4. You do not need to add "as" before procedure body to the MySQL stored procedure ". The SQL Server Stored Procedure must contain the "as" keyword.

Create procedure pr_add (A int, B INT) as -- error. MySQL does not need "as" begin MySQL statement...; end;

5. If the MySQL Stored Procedure contains multiple MySQL statements, the begin end keyword is required.

create procedure pr_add   (   a int,   b int   )   begin   mysql statement 1 ...;   mysql statement 2 ...;   end;  

6. Add a semicolon (;) to the end of each statement in the MySQL stored procedure.

...   declare c int;   if a is null then   set a = 0;   end if;   ...   end;  

7. Notes in the MySQL stored procedure.

/* This is a multi-line MySQL comment. */Declare C int; -- this is a single-row MySQL comment (Note that there must be at least one space) if A is null then # this is also a single-row MySQL comment set a = 0; end if ;... end;

8. The "return" keyword cannot be used in MySQL stored procedures.

Set c = a + B; select C as sum;/* return C; -- cannot be used in MySQL stored procedures. Return can only appear in functions. */End;

9. when calling the MySQL stored procedure, you need to add "()" after the process name. Even if there is no parameter, you also need "()"

call pr_no_param();  

10. Because there is no default value for the MySQL stored procedure parameters, you cannot omit the parameters when calling the MySQL stored procedure. It can be replaced by null.

 

11. How to execute the SQL string concatenated In the stored procedure (functions are similar to sp_executesql of MS-sqlserver)

prepare cmd from 'select * from user where id=?';set @id=1;execute cmd using @id;deallocate prepare cmd;

12. You need to modify the delimiter before creating a stored procedure. After creating a restored delimiter

DELIMITER //CREATE PROCEDURE sp_name ([proc_parameter[,...]])    [characteristic ...] routine_bodyDELIMITER ;