mysql 存儲過程和存儲函數(shù)

WindySky 2016-05-17

展開全文

存儲過程

delimiter //

drop procedure if exists reku_2_search_charActionLog_noLogin_count; -- 如果存在則刪除
create definer `pay_center`@`localhost` procedure reku_2_search_charActionLog_noLogin_count(start_date varchar(20),end_date varchar(20)) --definer `pay_center`@`localhost` 相當(dāng)于只允許本機的pay_center權(quán)限的用戶創(chuàng)建
begin lable_exit: -- 這兒可以理解為開啟跳轉(zhuǎn)
begin
if start_date is not null and end_date is not null then -- 如果 start_date 和end_date 參數(shù)不為空的話定義用戶變量，全局的
   set @start_date = unix_timestamp(start_date); 此函數(shù) 相當(dāng)于把具體時間轉(zhuǎn)換為時間戳和php中的 strtotime()一個意思
   set @end_date = unix_timestamp(end_date);
else
   if start_date is null then
   set @start_date = unix_timestamp(date_format(current_timestamp(),'%Y-%m-%d 00:00:00'));
   set @end_date = unix_timestamp(end_date);
   end if;
   if end_date is null then
   set @start_date = unix_timestamp(start_date);
   set @end_date   = unix_timestamp(date_format(current_timestamp(),'%Y-%m-%d 23:59:59'));
   end if;
end if;

if @start_date - @end_date > 0 then
select '時間不對';
leave lable_exit;
end if;
set @query = 'select count(*) as cnt from game_forbidinfo where phpTime >=? and phpTime <=?';
prepare stmt_sql_str from @query; --預(yù)定義sql
execute stmt_sql_str using @start_date,@end_date;   執(zhí)行預(yù)定義同時按照順序給？賦值

end lable_exit; 跳出
end;

drop procedure if exists reku_2_search_charActionLog_noLogin_res;
create procedure reku_2_search_charActionLog_noLogin_res(start_date varchar(20),end_date varchar(20),limits varchar(40))
begin
set @limit = limits;
set @query = "select gf.*, ru.rolename from game_forbidinfo as gf left join role_user as ru on gf.charid=ru.roleid where phpTime >=? and phpTime <=? limit ?";
prepare stmt_sql_str from @query;

execute stmt_sql_str using @start_date,@end_date,@limit;
end;

delimiter ;

刪除存儲過程：drop procedure reku_2_search_charActionLog_noLogin_res;

查看所有存儲過程： show procedure status where db='reku';

查看指定存儲過程代碼： show create procedure where 'reku.reku_2_search_charActionLog_noLogin_res';

存儲過程和存儲函數(shù)的不同點在于編寫存儲代碼應(yīng)用的環(huán)境(同時語法有兩處不同第一行多了一個：returns 類型最后一行返回 return 值 ) ，當(dāng)編寫存儲代碼只是為了返回一個狀態(tài) 或者一個標(biāo)示時，建議用存儲函數(shù)

create function factorypp (num int)

returns int
DETERMINISTIC --必須加這個
begin
declare result int default 1;
while num >0 do
set result = num * result;
set num = num -1;
end while;
return result; --返回結(jié)果
end;

調(diào)用存儲函數(shù) 用 select (注意：不用call)

select factorypp (10);

delimiter //

create definer `pay_center`@`localhost` function factorypp（num int）

begin

declare result int(11) default 1;

while num >0 do

set result = num * result;

set num = num -1;

end while;

return result;

end;