mysql存储过程详解参考这个文章
转:http://blog.csdn.net/dengsilinming/article/details/8484880
看了解释的挺详细的 就自己写了几个
下面是我自己写的
DROP TABLE IF EXISTS `points_balance`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `points_balance` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`user_id` bigint(20) unsigned NOT NULL COMMENT '用户id',
`total` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '用户总的积分',
`remaining` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '余额',
`created_time` int(10) unsigned NOT NULL COMMENT '创建时间',
`update_time` int(10) unsigned NOT NULL COMMENT '上次修改时间',
`status` tinyint(1) NOT NULL DEFAULT '0' COMMENT '用户状态:1:被冻结0:正常',
PRIMARY KEY (`id`),
UNIQUE KEY `user_id` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8 COMMENT='账户情况';
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `points_consume`
--
DROP TABLE IF EXISTS `points_consume`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `points_consume` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`user_id` bigint(20) unsigned NOT NULL COMMENT '用户id',
`points` int(10) unsigned NOT NULL COMMENT '积分',
`description` text CHARACTER SET utf8 NOT NULL COMMENT '积分用途说明',
`product_id` int(10) unsigned NOT NULL COMMENT '游戏产品id',
`mark` varchar(10) CHARACTER SET utf8 NOT NULL COMMENT '标识 活动',
`created_time` int(10) unsigned NOT NULL,
`status` tinyint(1) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
KEY `user_id` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=32 DEFAULT CHARSET=ucs2 COMMENT='积分消耗明细';
/*!40101 SET character_set_client = @saved_cs_client */;
DROP TABLE IF EXISTS `points_obtain`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `points_obtain` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`user_id` bigint(20) unsigned NOT NULL COMMENT '用户id',
`points` int(10) unsigned NOT NULL COMMENT '积分',
`description` text CHARACTER SET utf8 NOT NULL COMMENT '积分用途说明',
`product_id` int(10) unsigned NOT NULL COMMENT '游戏产品id',
`mark` varchar(10) CHARACTER SET utf8 NOT NULL COMMENT '标识 活动',
`created_time` int(10) unsigned NOT NULL,
`status` tinyint(1) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
KEY `user_id` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=44 DEFAULT CHARSET=ucs2 COMMENT='积分获取明细';
/*!40101 SET character_set_client = @saved_cs_client */;
/**用户积分增加存储过程*/
/**
--userId 用户id
--point 积分
--remark 积分说明
--status 状态
*/
DELIMITER //
CREATE PROCEDURE addpoint(IN userId int,IN point int,IN remark varchar(255),OUT status int)
BEGIN
DECLARE oldUserId int default 0; -- 是否存在这个用户
insert into points_obtain(`user_id`,`points`,`description`,`created_time`) values(userId,point,remark,UNIX_TIMESTAMP(now()));
select user_id into oldUserId from points_balance where user_id = userId limit 1;
if oldUserId = 0 then
insert into points_balance(user_id,created_time) values(userId,unix_timestamp(now()));
end if;
update points_balance set total = total+point , remaining = remaining+point,update_time=unix_timestamp(now()) where user_id = userId;
select row_count() into status; --ROW_COUNT()返回被前面语句升级的、插入的或删除的行数
end;
//
DELIMITER ;
set @status = 0;
CALL addpoint(123456,100,'add point',@status);
select @status;
/**
积分增减存储过程
--userId 用户id
--point 积分
--类型 ptype 1增加 2减少
--remark 积分说明
--status 状态
*/
DELIMITER //
CREATE PROCEDURE pointchange(IN userId int, IN point int, IN ptype int, IN remark varchar(255),OUT status int)
BEGIN
DECLARE oldUserId int default 0; -- 是否存在这个用户
select user_id into oldUserId from points_balance where user_id = userId limit 1;
if oldUserId = 0 then
insert into points_balance(user_id,created_time) values(userId,unix_timestamp(now()));
end if;
case ptype
when 1 then
insert into points_obtain(`user_id`,`points`,`description`,`created_time`) values(userId,point,remark,UNIX_TIMESTAMP(now()));
update points_balance set total = total+point , remaining = remaining+point,update_time=unix_timestamp(now()) where user_id = userId;
select row_count() into status;
when 2 then
insert into points_consume(`user_id`,`points`,`description`,`created_time`) values(userId,point,remark,UNIX_TIMESTAMP(now()));
update points_balance set remaining = remaining-point,update_time=unix_timestamp(now()) where user_id = userId;
select row_count() into status;
end case;
end;
//
DELIMITER ;
CALL pointchange(123456,100,1,'consume point',@status);
CALL pointchange(123456,100,2,'consume point',@status);
这个是参考这篇文章的作者 自己又写了一遍
/**查询一个表的数量的存储过程*/
DELIMITER //
CREATE PROCEDURE count_table_num(OUT num int)
BEGIN
SELECT count(1) INTO num from tmp;
END;
//
DELIMITER ;
set @tbl_count=0;
CALL count_table_num(@tbl_count);
SELECT @tbl_count;
/**生成订单编号的存储过程*/
CREATE TABLE `test_orders` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`orderNo` varchar(25) NOT NULL DEFAULT '',
`orderName` char(10) NOT NULL DEFAULT '',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=76 DEFAULT CHARSET=utf8
DELIMITER //
CREATE PROCEDURE getorderno(IN orderNamePre char(2),IN num int, OUT newOrderNo varchar(32))
BEGIN
DECLARE currentDate varchar (15); -- 当前日期,有可能包含时分秒
DECLARE maxNo int DEFAULT 0; -- 离现在最近的满足条件的订单编号的流水号最后5位,如:SH2013011000002的maxNo=2
DECLARE oldOrderNo varchar(32) DEFAULT ''; -- 离现在最近的满足条件的订单编号
if num = 8 then
select DATE_FORMAT(NOW(),"%Y%m%d") INTO currentDate; -- 订单编号形式:前缀+年月日+流水号,如:SH2013011000002
elseif num = 12 then
select date_format(now(),"%Y%m%d%H%i%s") into currentDate;
else
select DATE_FORMAT(NOW(),"%Y%m%d%H%i") into currentDate;
end if;
-- 有多条时只显示离现在最近的一条
SELECT IFNULL(orderNo,'') INTO oldOrderNo From test_orders where SUBSTRING(orderNo,3,num) = currentDate and SUBSTRING(orderNo,1,2) = orderNamePre and length(orderNo) = 7+num order by id desc limit 1;
IF oldOrderNo != '' THEN
SET maxNo = CONVERT(SUBSTRING(oldOrderNo,-5),DECIMAL); -- SUBSTRING(oldOrderNo, -5):订单编号如果不为‘‘截取订单的最后5
END IF;
SELECT CONCAT(orderNamePre,currentDate,LPAD((maxNo+1),5,'0')) INTO newOrderNo; -- LPAD((maxNo + 1), 5, '0'):如果不足5位,将用0填充左边
insert into test_orders(orderNo,orderName) values (newOrderNo,'testNo'); -- 向订单表中插入数据
select newOrderNo;
end;
//
DELIMITER ;
set @orderNo='';
CALL getorderno('SB',12,@orderNo);
select @orderNo;