MySql学习心得之存储过程
程序员文章站
2024-03-01 09:10:16
先来看段mysql查询文章回复语句：
复制代码 代码如下:#查询文章回复-- ------------------------------ procedure struc...
先来看段mysql查询文章回复语句：
复制代码 代码如下:
#查询文章回复
-- ----------------------------
-- procedure structure for `sp_select_reply_article`
-- ----------------------------
drop procedure if exists `sp_select_reply_article`;
delimiter ;;
create definer=`root`@`localhost` procedure `sp_select_reply_article`(in `ra_id` int,in `pagefrom` int,in `pagesize` int)
begin
#routine body goes here...
set @ra_id = ra_id;
set @pagefrom = pagefrom;
set @pagesize = pagesize;
set @ssra = concat('select * from gk_article where id = ? limit ?,?');
prepare sqlquery from @ssra;
execute sqlquery using @ra_id,@pagefrom,@pagesize;
end
delimiter ;
#技术点1：mysql5.1不支持limit参数（mysql5.5就支持了），如果编写存储过程时使用limit做变量，那是需要用动态sql来构建的，而这样做性能肯定没有静态sql好。主要代码如下：
复制代码 代码如下:
set @ssra = concat('select * from gk_article where id = ? limit ?,?');
prepare sqlquery from @ssra;
execute sqlquery using @ra_id,@pagefrom,@pagesize;
#技术点2：如果同时需要返回受影响行数需要在语句后面添加语句：row_count()函数，两条语句之间需要“;”分隔。
复制代码 代码如下:
#更新数据
-- ----------------------------
-- procedure structure for `sp_update_permission`
-- ----------------------------
drop procedure if exists `sp_update_permission`;
delimiter ;;
create definer=`root`@`localhost` procedure `sp_update_permission`(in `puser_uid` varchar(20),in `plevel` int,in `ppower` int)
begin
#routine body goes here...
set @puser_uid = puser_uid;
set @plevel = plevel;
set @ppower = ppower;
update gk_permission set `level` = @plevel, power = @ppower where user_uid = convert(@puser_uid using utf8) collate utf8_unicode_ci;
end
delimiter ;
#技术点3：mysql进行字符串比较时发生错误（illegal mix of collations (utf8_unicode_ci,implicit) and (utf8_general_ci,implicit) for operation '='），解决方法：将比较等式一边进行字符串转换，如改为“convert(b.fullcode using utf8) collate utf8_unicode_ci”，主要代码如下：
复制代码 代码如下:
update gk_permission set `level` = @plevel, power = @ppower where user_uid = convert(@puser_uid using utf8) collate utf8_unicode_ci;
复制代码 代码如下:
#插入数据
-- ----------------------------
-- procedure structure for `sp_insert_user`
-- ----------------------------
drop procedure if exists `sp_insert_user`;
delimiter ;;
create definer=`root`@`localhost` procedure `sp_insert_user`(in `uid` varchar(20),in `upw` varchar(32),in `name` varchar(20),in `sex` int,in `phone` varchar(20),in `u_id` int,in `s_id` int,in `j_id` int)
begin
#routine body goes here...
set @uid = uid;
set @upw = upw;
set @uname = uname;
set @sex = sex;
set @phone = phone;
#由于外键约束，所以添加的外键字段需要在对应外键所在表有相应数据
set @u_id = u_id;
set @s_id = s_id;
set @j_id = j_id;
set @verifytime = date('0000-00-00');
insert into gk_user(uid,upw,uname,sex,phone,u_id,s_id,j_id,verifytime)
values(@uid,@upw,@uname,@sex,@phone,@u_id,@s_id,@j_id,@verifytime);
#查询结果会自动返回受影响行数
end
delimiter ;
复制代码 代码如下:
#根据id删除数据
-- ----------------------------
-- procedure structure for `sp_delete_exchange_by_id`
-- ----------------------------
drop procedure if exists `sp_delete_exchange_by_id`;
delimiter ;;
create definer=`root`@`localhost` procedure `sp_delete_exchange_by_id`(in `eid` int)
begin
#routine body goes here...
set @eid = eid;
delete from gk_exchange where id = @eid;
end
delimiter ;
复制代码 代码如下:
#通过账号查询用户或者管理员
-- ----------------------------
-- procedure structure for `sp_select_user_by_uid`
-- ----------------------------
drop procedure if exists `sp_select_user_by_uid`;
delimiter ;;
create definer=`root`@`localhost` procedure `sp_select_user_by_uid`(in `uid` varchar(20),in `getadmin` int)
begin
#routine body goes here...
set @uid = uid;
#set @getadmin = getadmin;
#查询管理员
if (getadmin = 1) then
select us.*, un.`name`, se.`name`, jo.`name`, pe.`level`, pe.power from gk_user as us, gk_unit as un, gk_section as se, gk_jobtitle as jo, gk_permission as pe where us.u_id = un.id and us.s_id = se.id and us.j_id = jo.id and us.uid = pe.user_uid and us.uid = convert(@uid using utf8) collate utf8_unicode_ci;
end if;
#查询用户
if (getadmin = 0) then
select us.*, un.`name`, se.`name`, jo.`name` from gk_user as us, gk_unit as un, gk_section as se, gk_jobtitle as jo where us.u_id = un.id and us.s_id = se.id and us.j_id = jo.id and us.uid = convert(@uid using utf8) collate utf8_unicode_ci;
end if;
end
delimiter ;
#技术点4：这个存数过程需要用到控制语句（if else elseif while loop repeat leave iterate）。
复制代码 代码如下:
if (getadmin = 1) then
#语句…
end if;
#技术点5：在传入参数不匹配的情况下报错（column count doesn't match value count at row 1），这个就是细心问题了，详细检查参数吧。
#技术点6：获取当前时间的函数：now()
#技术点7：“`”这个符号是反单引号，两个反单引号夹起来的会被当做变量，一般是在定义字段时遇到关键字冲突的时候会用到。
上一篇： FCKeditor 实战技巧