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

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,’... ’);

1. 變量的使用

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 ;

1. 存儲過程參數使用

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 ;