当前位置：首页 > news >正文

mysql 存储过程

news 来源：原创 2024/5/5 18:01:41

注意：定义参数的时候，不要将参数名和字段名一致，哪怕是大小写

自定义Sql异常处理

show errors  #显示错误

DECLARE CONTINUE HANDLER for SQLEXCEPTION [sql]  #出错了执行[sql]，然后继续往下执行

DECLARE EXIT HANDLER for SQLEXCEPTION [sql]  #异常时仅执行[sql]，停止执行

场景：用户转账

调试方式：call SP_TRANSFER('1','2',1)。可以将注释解除SELECT SLEEP(5)，执行第一个存储过程时，直接将账户取空，然后迅速另起一个会话执行一定的金额。是不会出现负数的情况的

参数配置：IN fromuser varchar(50),IN touser varchar(50),IN _money decimal(10,2)

BEGIN
    DECLARE fromuser_money DECIMAL(10,2) DEFAULT 0;
    DECLARE touser_money DECIMAL(10,2) DEFAULT 0;
    DECLARE Is_OK bit DEFAULT TRUE;  ###默认是成功 
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET Is_OK = FALSE; ###如果出错则修改标识
    START TRANSACTION;
        SELECT user_money into fromuser_money from user_balance WHERE user_id = fromuser for update;
        SELECT touser_money into touser_money from user_balance WHERE user_id = touser for update;
        IF fromuser_money < _money THEN
                ROLLBACK;
                SELECT '金额不够' as result;
        ELSE                
                SET fromuser_money = fromuser_money - _money;
              SET touser_money = touser_money + _money;
                update user_balance SET user_money = fromuser_money WHERE user_id = fromuser;
                ###SELECT SLEEP(5); 测试专用，可以趁这个时间另起一个会话执行本存储过程
                update user_balance SET user_money = touser_money WHERE user_id = touser;            
        END IF;

        IF Is_OK = TRUE THEN
                COMMIT;
                SELECT '转账成功' as result;                
        ELSE
                ROLLBACK;
                SELECT '出错' as result;
        END IF;
END

场景：用户余额变动时，更新（插入）到流水表，并且记录到日志表中，但这里是先插入日志，如果成功再进入下一步。

知识点：

1、不存在则更新，存在则插入；

2、事务回滚与提交、异常

3、语法学习

调试方式：CALL SP_UPDATE_OR_INSERT(3,1,"用户充值",'10');

参数配置：IN _user_id int,IN _log_type varchar(255),IN _log_des varchar(255),IN _log_value decimal(10,2)

BEGIN
    
    DECLARE t_error int DEFAULT 0;
    DECLARE usercount int DEFAULT 0;
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET t_error = 1;
    START TRANSACTION;

    #插入用户充值日志表
    INSERT INTO user_balance_log(user_id,log_type,log_des,log_value) values (_user_id,_log_type,_log_des,_log_value);

    #代表日志表插入成功
    IF ROW_COUNT() > 0 THEN
        #查找用户是否存在表中
        SELECT COUNT(*) INTO usercount from user_balance where user_id = _user_id;
        #如果存在则更新,否则就插入
        IF usercount > 0 THEN            
            UPDATE user_balance SET user_money = user_money + _log_value WHERE user_id = _user_id;
        ELSE            
            INSERT INTO user_balance(user_id,user_money) VALUES (_user_id,_log_value);
        END IF;
    END IF;
    


    

    #判断错误
    if t_error = 1 THEN
        ROLLBACK;    #回滚
    ELSE
        COMMIT;        #提交
    END IF;
    
END

场景：生成测试数据

BEGIN

SET @NUM = 1;

WHILE @NUM < 1000000 DO

    INSERT INTO TEST_1 (user_name,user_pwd) VALUES (CONCAT('USER',@NUM),'123');

    SET @NUM = @NUM + 1;
END WHILE;
    
END;

场景：用户登录

调试方式：call SP_USER_LOG('Lee','123')

参数配置：IN _user_name varchar(50),IN _user_pwd varchar(50)

BEGIN
     SET @gid = 0;
     SET @user_name = '';
     SET @_result = 'login success';
     SELECT id,user_name INTO @gid,@user_name from user_sys where user_name = _user_name and user_pwd = _user_pwd limit 1;
     if @gid = 0 then
             set @_result = 'login error';
     end if;
     select * from (select @_result as _result) a,(select @gid,@user_name) b;
END

场景：点击次数，点赞次数

学习链接：http://www.jtthink.com/course/play/351

调试方式：call SP(1,'192.168.22.14','110');

知识点和注意点：

1、clickdate 的数据类型为“date”，插入值为 CURRENT_DATE

2、clicknum默认值为1

3、prod_click 表的字段如下：id、prod_id、user_ip、clickdate、user_id、clicknum

4、prod_main 表的字段如下：prod_id、prod_name

参数配置：IN _prod_id int,IN _user_ip varchar(15),IN _user_id int

BEGIN
    SET @NUM =0;
    SET @COUNT = 0;
  SELECT * FROM prod_main WHERE prod_id = _prod_id limit 1;
    SET @NUM = FOUND_ROWS();
    IF @NUM = 1 THEN
        SELECT COUNT(*) INTO @COUNT FROM prod_click WHERE prod_id = _prod_id AND
        user_ip = _user_ip AND user_id = _user_id AND clickdate = CURRENT_DATE;        
        IF @COUNT > 0 THEN
            UPDATE prod_click SET clicknum = clicknum + 1 WHERE prod_id = _prod_id AND
            user_ip = _user_ip AND user_id = _user_id AND clickdate = CURRENT_DATE;
        ELSE
            INSERT INTO prod_click(prod_id,user_ip,user_id,clickdate) VALUES (_prod_id,_user_ip,_user_id,CURRENT_DATE);
        END IF;
    END IF;
END

http://www.jtthink.com/course/play/351

场景：通过sql循环遍历Select的数据进行操作

知识点：游标

BEGIN
    DECLARE isend int DEFAULT 0;
    DECLARE pid int;
    DECLARE cnum int;
    DECLARE cur CURSOR FOR SELECT prod_id,sum(clicknum) FROM prod_click GROUP BY prod_id;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET isend = 1;
    open cur;#打开游标
    fetch cur INTO pid,cnum;  
    WHILE isend != 1 DO
            UPDATE prod_main SET prod_click = cnum WHERE prod_id = pid;
        　　fetch cur INTO pid,cnum;  
    END WHILE;
　　CLOSE cur;
END

场景：分页

参数配置：IN startrow int,IN pagesize int

BEGIN
    IF startrow = 0 THEN
                    SELECT 
                                    * 
                        FROM 
                                    prod_main 
                ORDER BY 
                                    prod_id DESC                         
                    LIMIT 
                                    pagesize;
    ELSE
                SELECT 
                                * 
                    FROM 
                                prod_main 
                 WHERE 
                                prod_id > (SELECT * FROM prod_main ORDER BY prod_id DESC LIMIT startrow,1)         
            ORDER BY 
                                prod_id DESC 
                    LIMIT 
                                pagesize;
    END IF;
END

场景：拼接字符串

知识点：CONCAT和游标、去除最后一个字符串

BEGIN
    DECLARE Is_End INT DEFAULT 0;
    DECLARE _str VARCHAR(50) DEFAULT '';
    DECLARE _id INT;
    DECLARE _answer VARCHAR(50);
    DECLARE cur CURSOR FOR SELECT id,answer FROM question_library where id > 100 ORDER BY RAND() LIMIT 10;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET Is_End = 1;
    OPEN cur;
    FETCH cur INTO _id,_answer;
    WHILE Is_End != 1 DO
        SELECT CONCAT(_id,',',_str) INTO _str;
        FETCH cur INTO _id,_answer;
    END WHILE;
    CLOSE cur;
    SELECT left(_str,LENGTH(_str)-1) AS question;  #去掉最后一个字符串“，”
END

场景：字符串分割

知识点：临时表,字符串分割

参数配置：IN _str varchar(50)

BEGIN

set @id = _str; 
set @i = 0;
DROP TEMPORARY TABLE IF EXISTS tem_result;
CREATE TEMPORARY TABLE tem_result(id BIGINT(20) NOT NULL);  
SET @cnt = 1+(LENGTH(@id) - LENGTH(REPLACE(@id,',','')));

WHILE @i < @cnt DO
    SET @i = @i + 1;
    SET @result = REVERSE(SUBSTRING_INDEX(REVERSE(SUBSTRING_INDEX(@id,',',@i)),',',1));
    INSERT INTO tem_result(id) VALUES (@result);
END WHILE;

SELECT * FROM tem_result;

END

场景:商品添加，表结构请参考：http://www.cnblogs.com/CyLee/p/5686757.html

知识点：动态sql,游标与临时表的结合

入参：IN _prod_name varchar(50),IN _class_id int,IN _attr_sql varchar(2000)

调用：

CALL sp_new_prod('测试商品',1,'

SELECT 1 AS attr_id,\'中国出版社\' AS attr_value,0 AS prod_price
UNION SELECT 3 AS attr_id,\'16开\' AS attr_value,30 AS prod_price
UNION SELECT 3 AS attr_id,\'32开\' AS attr_value,35 AS prod_price

');

BEGIN
    #Routine body goes here...
  DECLARE isend int DEFAULT 0;
  DECLARE _prod_id int;  
  DECLARE _attr_id int;
  declare _attr_value varchar(20);
  declare _prod_price DECIMAL(5,2);
  DECLARE cur CURSOR FOR  select  prod_id,attr_id,attr_value,prod_price from temp_a;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET isend = 1;  


     #添加一条记录到商品主表并且获取id
   set @pid=0; 
   INSERT into prod_main(prod_name,prod_classid) values(_prod_name,_class_id);
   set @pid=LAST_INSERT_ID();


     #通过程序获取商品的属性和属性对应的价格，放入临时表
   drop TEMPORARY table if EXISTS temp_a;
   set @ss=CONCAT('create TEMPORARY table temp_a as select ? as prod_id, a.* from (',_attr_sql,' ) a');
     PREPARE pname from @ss;
     EXECUTE pname using @pid;
     DEALLOCATE PREPARE pname;


        #循环临时表，循环插入商品价格表
        open cur;#打开游标
        fetch cur into _prod_id,_attr_id,_attr_value,_prod_price; 
        while isend!=1 do
                insert into prod_attr(prod_id,attr_id,attr_value) values(_prod_id,_attr_id,_attr_value); #插入商品属性表
                set @getid=LAST_INSERT_ID(); 
                insert into prod_price(prod_id,prod_attr_id,prod_price) values(_prod_id,_attr_id,_prod_price);
         fetch cur into   _prod_id,_attr_id,_attr_value,_prod_price; 
        end while;
        close cur;#关闭游标
        DROP TEMPORARY table temp_a; #删除临时表



END

场景：商品添加与属性添加

入参：IN _prod_classid int,IN _prod_name varchar(50),IN _attr_sql text

调用：CALL sp_new_prod(1,'测试商品','select 1 as attr_id, \'中国出版社\' as attr_value UNION select 2 as attr_id, \'刘勇\' as attr_value');

注意：这种方式的插入，不能有自增id，如果有请模仿上面的demo

begin
insert into `test`.`prod_main` ( `prod_classid`, `prod_name`) values 
(_prod_classid,_prod_name);

set @pid = LAST_INSERT_ID();
#insert into prod_attr select 1 as prod_id,a.* from (select 1 as attr_id, '中国出版社' as attr_value UNION select 2 as attr_id, '刘勇' as attr_value) as a
set @ss = CONCAT('insert into prod_attr select ? as prod_id,a.* from (',_attr_sql,' ) as a');
PREPARE pname from @ss;
EXECUTE pname using @pid; #赋值给？
DEALLOCATE PREPARE pname;

end