Mysql存儲過程的使用注意事項

叁歲伎倆 2022-05-18 10:11 177阅读 0赞

### **Mysql存儲過程的使用注意事項** ###

**1.注意事項**

**1.1關鍵字DELIMITER $ (定界符號) 注意$前面有一個空額 --作用 多條Sql語句一起執行**

Mysql的sql語句默認以分號(;)為結束符號,Mysql解析器碰到分號就開始執行Sql,需要批次執行就需要使用DELIMITER

*案例:1-1*

DELIMITER $$
    Delete ..;
    Insert into...;
    Select * ..
    $$
    DELIMITER ;

解析:DELIMITER $ 告訴Mysql解析器$為結束符號,執行完sql后,恢復分號為結束符

**1.2關鍵字PREPARE (預執行)  --作用:存儲過程中不能直接執行EXECUTE sql;需要預處理**

*案例:1-2*

Set @sql=’select *...’;
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

**1.3關鍵字CONCAT --作用:字符串拼接有變量的字符串拼接時,常用**

*案例:1-3*

Set @params=’D0255’;
    Set @sql=CONCAT(’select * ... ’,@params,’... ’);

**2.變量的使用**

**2.1.Mysql變量可以免申明,直接使用@params**

**2.2Mysql存儲過程sql語句的變量賦值**

申明的變量採用select a into params,未申明的變量採用select @params= from...

*案例:2-1* 申明變量

DELIMITER $$
    CREATE PROCEDURE Test()
    BEGIN
    DECLARE mailNO INTEGER DEFAULT 0;
    SELECT mail_seq INTO mailNO FROM lrdmailseq;
    SELECT mailNO;
    END$$
    DELIMITER ;

*案例2-2* 未申明變量

DELIMITER $$
    CREATE PROCEDURE Test()
    BEGIN
    SELECT @mailNO:=mail_seq FROM lrdmailseq;
    SELECT @mailNO;
    END$$
    DELIMITER ;

**3.存儲過程參數使用**

**3.1 參數IN表示傳入OUT表示傳出 如:IN seqtype CHAR(1),OUT mailNO BIGINT)**

*案例3-1* 使用CALL GetMailNO('1',@mailNO);SELECT @mailNO;

DELIMITER $$
    -- 編序號 表裡只有一筆資料
    DROP PROCEDURE IF EXISTS `GetMailNO`$$
    CREATE DEFINER=`lrmis`@`%` PROCEDURE `GetMailNO`(IN seqtype CHAR(1),OUT mailNO BIGINT)
    BEGIN
     DECLARE t_error INTEGER DEFAULT 0;
     DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET t_error=1;  -- 遇到異常后繼續 即設定@t_error=1 然後回滾,返回0
     SET mailNO=0;
     START TRANSACTION; -- 開啟事務
     IF seqtype='1' THEN     
         SELECT mail_seq INTO mailNO FROM lrmailseq;
         SET mailNO=mailNO+1;
         UPDATE lrmailseq SET mail_seq=mailNO;
      END IF;
      IF seqtype='2' THEN
         SELECT mail_seq INTO mailNO FROM lromailseq;
         SET mailNO=mailNO+1;    
         UPDATE lromailseq SET mail_seq=mailNO;
      END IF;
      IF seqtype='3' THEN
         SELECT mail_seq INTO mailNO FROM lrdmailseq;
         SET mailNO=mailNO+1;
         UPDATE lrdmailseq SET mail_seq=mailNO;
      END IF;
      IF t_error = 1 THEN  
           ROLLBACK;
      ELSE
          COMMIT;
      END IF;
    END$$
    DELIMITER ;

**3.2變量申明與表中字段變量大小寫敏感**

*案例3-2* 執行 CALL TestDomo(3250792)陷入死循環

DELIMITER $$
    CREATE PROCEDURE TestDomo(IN Mail_no INT)
    BEGIN
    SELECT * FROM lremailstate WHERE mail_no=Mail_no;
    END $$
    DELIMITER;

**3.3聲明的變量不能直接使用 where in(params);**

*案例:3-3* 執行CALL TestDomo('3250792,3210174') 可能只有3250792的值結果出來了

DELIMITER $$
    CREATE PROCEDURE TestDomo(IN _no VARCHAR(4000))
    BEGIN
    SELECT * FROM lremailstate WHERE mail_no IN(_no);
    END$$
    DELIMITER ;

解決方案:

A.採用FIND\_IN\_SET函數

SELECT * FROM lremailstate WHERE FIND_IN_SET(_no ,mail_no);

B.採用cancat拼接后預處理SQL

DELIMITER $$
    CREATE PROCEDURE TestDomo(IN _mailNos VARCHAR(4000))
    BEGIN
    SET @sql=CONCAT("SELECT * FROM lremailstate a WHERE a.mail_no IN (",_mailNos,")");
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
    END$$
    DELIMITER ;