mysql 存储过程学习

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)BEGINDECLARE 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)BEGINDECLARE 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 ptypewhen 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)BEGINSELECT 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=utf8DELIMITER //CREATE PROCEDURE getorderno(IN orderNamePre char(2),IN num int, OUT newOrderNo varchar(32))BEGINDECLARE currentDate varchar (15);   -- 当前日期,有可能包含时分秒DECLARE maxNo int DEFAULT 0;     -- 离现在最近的满足条件的订单编号的流水号最后5位，如：SH2013011000002的maxNo=2DECLARE oldOrderNo varchar(32) DEFAULT ''; -- 离现在最近的满足条件的订单编号if num = 8 then    select DATE_FORMAT(NOW(),"%Y%m%d") INTO currentDate;    -- 订单编号形式:前缀+年月日+流水号，如：SH2013011000002elseif 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)：订单编号如果不为‘‘截取订单的最后5END 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;