注意：定义参数的时候，不要将参数名和字段名一致，哪怕是大小写
自定义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
场景：生成测试数据
BEGINSET @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)
BEGINset @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_priceUNION 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
begininsert 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 aset @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