mysql 開發基礎系列17 存儲過程和函數(上)

一. 概述　

　存儲過程和函數是事先經過編譯並存儲在資料庫中的一段sql語句集合，可以簡化應用開發人員的很多工作，減少數據在資料庫與應用伺服器之間的傳輸，提高數據處理效率是有好處的。存儲過程和函數的區別在於函數必須有返回值，存儲過程的參數可以使用in,out ,inout類型，而函數參數只能是in類型。 創建需要create routine 許可權， 修改刪除需要alter routine許可權，執行需要execute許可權。

　　1.1 創建存儲過程,調用,刪除

-- 創建
DELIMITER $$
CREATE PROCEDURE proc_city(IN city_id INT ,OUT num INT)
READS SQL DATA
BEGIN
    --  要設置初始值，不然為null 與sqlserver一樣
    SET num=0;
    SELECT * FROM city WHERE city.city_id=city_id;
    SET num=num+1;
END $$
DELIMITER ;

 --  調用
CALL proc_city (2,@num); SELECT @num;

-- 刪除
DROP  PROCEDURE proc_city

　　特征值介紹:
　　CREATE PROCEDURE proc_name()
　　　　{ contains sql | no sql | READS SQL DATA | modifies sql data}
　　這個特征值只是提供給伺服器，並沒有根據這些特征值來約束過程實際使用數據的情況。 contains sql 表示子程式不包含讀或寫數據的語句。no sql表示子程式不包含sql 語句。 READS SQL DATA:表示子程式包含數據的語句，但不包含寫數據。modifies sql data 表示子程式包含寫數據。預設是contains sql。

　　1.2 創建函數,調用,刪除

-- 創建
SET GLOBAL log_bin_trust_function_creators=TRUE;
DELIMITER $$
CREATE FUNCTION fn_city (city_count INT)
RETURNS INT 
BEGIN
DECLARE  city_count1 INT;
    --  要設置初始值，
    SET city_count1=0;
    SET  city_count1=city_count+1;
    RETURN city_count1;
END $$
DELIMITER ;

-- 調用
SELECT  fn_city (2);

-- 刪除
DROP FUNCTION fn_city

　　1.3 查看信息

SELECT * FROM information_schema.Routines WHERE ROUTINE_NAME='proc_city' OR  ROUTINE_NAME='fn_city'

　　1.4 變數的使用

DELIMITER $$
CREATE PROCEDURE proc_demo_declare()
READS SQL DATA
BEGIN
    -- 定義 
    DECLARE i INT ;
    -- 賦值
    SET i=10;
    SELECT i;
    -- 第二種賦值
    SELECT COUNT(1) INTO i  FROM city;
    SELECT i;
END $$
DELIMITER ;

call proc_demo_declare;

1.5 定義Handler遇到問題的處理步驟

--  語法
DECLARE
{EXIT | CONTINUE}
HANDLER FOR
{error-number | SQLSTATE error-STRING | CONDITION}
SQL statement

　　（1） 處理類型{EXIT | CONTINUE} continue: 當遇到執行錯誤時，跳過繼續下麵的語句。exit: 當遇到執行錯誤時，停止語句
　　（2） 觸發條件 （SQLSTATE,MYSQL ERROR,CONDITION）MYSQL錯誤代碼。ANSI-standard SQLSTATE code。命名條件。可使用系統內置的SQLEXCEPTION,SQLWARNING和NOT FOUND。
　　（3）錯誤觸發的操作。

--   以continue為例
DELIMITER $$
CREATE PROCEDURE proc_demo_handler_continue()
BEGIN
    -- 定義條件 continue : 當遇到執行錯誤時，跳過繼續下麵的語句
    DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @x2 = 1;    
    
    -- 這條可以插入
    INSERT INTO  city(country_id,cityname,Citycode) VALUES(2,'中國','cn4');
    SET @x=1;
    
    -- 這條不能插入，外鍵約束fk_city_country
    INSERT INTO  city(country_id,cityname,Citycode) VALUES(8,'中國','cn5');
    SET @x=2;
    
    -- 這條可以插入
    INSERT INTO city(country_id,cityname,Citycode) VALUES(2,'中國','cn6');
    SET @x=3;
END $$
DELIMITER ;

-- 調用 
CALL proc_demo_handler_continue; SELECT @x,@x2;

　　使用了continue: 當遇到執行錯誤時，跳過繼續下麵的語句，三條語句，只插入成功二條成功

--  以exit為例
DELIMITER $$
CREATE PROCEDURE proc_demo_handler_exit()
BEGIN
    -- 定義條件  : 當遇到執行錯誤時，停止語句  
    DECLARE EXIT HANDLER FOR SQLSTATE '23000' SET @x2 = 1;    
    
    -- 這條可以插入
    INSERT INTO  city(country_id,cityname,Citycode) VALUES(2,'中國','cn4');
    SET @x=1;
    
    -- 這條不能插入，外鍵約束fk_city_country
    INSERT INTO  city(country_id,cityname,Citycode) VALUES(8,'中國','cn5');
    SET @x=2;
    --  這條不可以插入
    INSERT INTO city(country_id,cityname,Citycode) VALUES(2,'中國','cn6');
    SET @x=3;
END $$
DELIMITER ;

-- 調用
CALL proc_demo_handler_exit;SELECT @x,@x2;

　　使用了exit: 當遇到執行錯誤時，停止語句, 三條語句，只插入成功第一條