mysql 的储存过程

常见的mysql客户端phpmyadmin低版本不支持储存过程(高版本还不知道)，你可以用命令行或Navicat for MySQL运行下面的sql

例子1：

DELIMITER //
create procedure  show_pro(in id integer,out s varchar(20))
begin
declare s varchar(20);
if (length(id)=11) then select ‘A_B_C_D’ into s;
elseif(length(id)=8) then select ‘A_B_C’ into s;
elseif(length(id)=5) then select ‘A_B’ into s;
elseif(length(id)=2) then select ‘A’ into s;
end if;
select s;
end;
//

 

调用方法： call  show_pro(11,@aa);//

 

例子2：

DELIMITER //
create procedure proc_name (in parameter integer)
begin
declare variable varchar(20);
if parameter=1 then
set variable=’MySQL’;
else
set variable=’PHP’;
end if;
select variable;
end;
//

调用：call  proc_name(1);//

 

在写两个复杂一点的例子：

delimiter //
create procedure changeuserpassword
(IN p_userid INT ,IN p_useroldpw VARCHAR(50),IN p_usernewpw VARCHAR(50), OUT p_retcode VARCHAR(20))
BEGIN
declare tmp_useroldpw varchar(50) ;
select password into tmp_useroldpw from user_info where id = p_userid;
IF tmp_useroldpw IS NOT NULL THEN
IF tmp_useroldpw = p_useroldpw THEN
update user_info set password = p_usernewpw where id = p_userid;
SET p_retcode =’1′;
ELSE
SET p_retcode=’-1′;
END IF;
ELSE
SET p_retcode = ‘-2′;
END IF;
END;
//
delimiter //
create procedure updateuserlogonstate(IN p_userid INT ,IN p_clienttype VARCHAR(20),IN p_sessionid VARCHAR(50))
BEGIN
declare tmp_sessionid varchar(50) ;
SELECT sessionid  INTO tmp_sessionid FROM user_logon_state WHERE id =p_userid AND clienttype=p_clienttype ;
IF tmp_sessionid IS NOT NULL THEN
UPDATE user_logon_state SET sessionid=p_sessionid  WHERE id=p_userid AND clienttype=p_clienttype;
ELSE
INSERT INTO user_logon_state (id,sessionid,clienttype)values(p_userid,p_sessionid,p_clienttype);
END IF;
END;
//