mysql 存儲過程

注意：定義參數的時候，不要将參數名和字段名一緻，哪怕是大小寫

自定義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

mysql 存儲過程

繼續閱讀

MyBatis-Plus 之AR模式

ASP程式設計經典例子

ASP程式設計中20個非常有用的例子

龍珠訓練營task04

阿裡雲天池龍珠計劃SQL訓練營打卡

阿裡雲天池龍珠計劃SQL訓練營day1

實驗樓sql進階之成績管理系統的資料操作(window)

Oracle的基本操作

回文樹學習小結

SQL優化SQL語句優化的目的

JAVA高效程式設計指南

查找檔案中的字元串

關于SQL語言

SQL語言基礎：常用的資料查詢語句

neo4j之cypher使用文檔

sqlServer根據經緯查距離