mysql 存储过程学习

最新推荐文章于 2024-07-15 19:44:21 发布

无聊是无知

最新推荐文章于 2024-07-15 19:44:21 发布

阅读量518

点赞数

分类专栏： mysql 文章标签： mysql 存储过程

本文链接：https://blog.csdn.net/sgravity/article/details/41083225

版权

mysql 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

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;