新手玩mysql stored procedure(存储过程),期间遇到了不少麻烦,为此贴出来,希望能为也正在玩存储过程的人提供一个demo。欢迎拍砖。
/*特别注明:下边的SQL语句都是用test数据库*/
use test;
create table user
(
id int not null auto_increment primary key,
userName varchar(30),
userPsw varchar(20)
);
/*取消
create unique index user_id_index on user(id);
drop index user_id_index on user;
*/
use test;
create index user_name_index on user(userName);
use test;
/*登录存储过程*/
DELIMITER $$
create procedure `login`
(
in userName varchar(30) ,
in userPsw varchar(20)
)
BEGIN
DECLARE selectname varchar(30);
DECLARE selectpsw varchar(20);
DECLARE result bool;
select user.userName,user.userPsw into selectname, selectpsw
from user
where user.userName=userName and user.userPsw=userPsw;
IF selectname is not null and selectpsw is not null then
set result=true;
ELSE set result=false;
end if;
select result;
end$$
DELIMITER ;
/*注册存储过程*/
DELIMITER $$
create PROCEDURE `regist`
(
/*out result bool;*/
in userName varchar(30),
in userPsw varchar(20)
)
BEGIN
DECLARE result bool;
DECLARE select_count int;
SELECT COUNT(user.userName) into select_count
from user where user.userName=userName and user.userPsw=userPsw;
IF select_count=0 THEN
/*无重复则插入*/
INSERT INTO USER(user.userName,user.userPsw)
values(userName,userPsw);
set result=true;
/*否则,不插入,返回插入失败*/
ELSE set result=false;
/*用于临时显示结果,待注释*/
SELECT RESULT;
END IF;
END$$
DELIMITER ;
use test;
drop procedure check_user;
drop procedure login;
use test;
insert into user(userName,userPsw) values
('ding','ding');
/*下边是验证存储过程*/
use test;
call regist('ding','ding');
call login('admin','admin');
其实mysql 存储过程并不难写,关键是注意什么时候要“;”什么时候不要分号就OK了,否则会莫名其妙地编译错误。