注意:定义参数的时候,不要将参数名和字段名一致,哪怕是大小写
自定义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
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