源碼庫詳解MYSQL存儲過程
2024-07-24 12:40:26
供稿：網友
一、 簡介 存儲過程（Stored Procedure）是一組為了完成特定功能的SQL語句集，經編譯后存儲在數據庫中。用戶通過指定存儲過程的名字并給出參數（如果該存儲過程帶有參數）來執行它。存儲過程是數據庫中的一個重要對象，任何一個設計良好的數據庫應用程序都應該用到存儲過程。 雖然常用術語是存儲過程（stored procedure），但MySQL實際上實現了兩中類型，除了存儲過程外，還有存儲函數（stored routine）,它們統稱為存儲例程。 二、基本格式 1、存儲過程 CREATE PROCEDURE 過程名 ([過程參數[,...]]) [特性 ...] 過程體 如創建: CEATE PROCEDURE p1 (a INT) SELECT a; 調用一下：CALL p1(8); 將顯示： +------+ | a | +------+ | 8 | +------+ 1 row in set (0.00 sec) 2、存儲函數 CREATE FUNCTION 函數名 ([函數參數[,...]]) RETURNS 返回類型 [特性 ...] 函數體 如創建：CREATE FUNCTION f1 (x INT) RETURNS INT RETURN x; /* 過程函數一次只能返回一個值 調用一下：SELECT f1 (3); 將顯示： +-------+ | f1(3) | +-------+ | 3 | +-------+ 1 row in set (0.00 sec) 3、過程參數 [ IN | OUT | INOUT ] 參數名 參數類型 4、函數參數 參數名 參數類型 5、返回類型 有效的MySQL數據類型即可 6、過程體/函數體格式如下 BEGIN 有效的SQL語句 END 7、特性（一般不要求） LANGUAGE SQL | [NOT] DETERMINISTIC | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA } | SQL SECURITY { DEFINER | INVOKER } | COMMENT 'string' 8、存儲過程中參數的IN,OUT,INOUT類型 存儲過程可以接受輸入參數，并把參數返回給調用方。不過，對于每個參數，需要聲明其參數名、數據類型，還要指定此參數是用于向過程傳遞信息，還是從過程傳回信息，或是二者兼有。 為聲明參數的作用，使用如下3個關鍵字之一： ˜ IN : IN參數只用來向過程傳遞信息，為默認值。 ˜ OUT : OUT參數只用來從過程傳回信息。 ˜ INOUT ： INOUT參數可以向過程傳遞信息，如果值改變，則可再從過程外調用。 如果僅僅想把數據傳給 MySQL 存儲過程，那就使用“in” 類型參數；如果僅僅從 MySQL 存儲過程返回值，那就使用“out” 類型參數；如果需要把數據傳給 MySQL 存儲過程，還要經過一些計算后再傳回給我們，此時，要使用“inout” 類型參數。 對于任何聲明為OUT或INOUT的參數，，當調用存儲過程時需要在參數名前加上@符號，這樣該參數就可以在過程外調用了。 下面舉三個實例：MySQL 存儲過程 “in” 參數：跟 C 語言的函數參數的值傳遞類似， MySQL 存儲過程內部可能會修改此參數，但對 in 類型參數的修改，對調用者（caller）來說是不可見的（not visible）。 create procedure pr_param_in( in id int -- in 類型的 MySQL 存儲過程參數)begin if (id is not null) then set id = id + 1; end if; select id as id_inner;end;set @id = 10; call pr_param_in(@id); select @id as id_out;mysql> call pr_param_in(@id);+----------+| id_inner |+----------+| 11 |+----------+ mysql> select @id as id_out;+--------+| id_out |+--------+| 10 |+--------+可以看到：用戶變量 @id 傳入值為 10，執行存儲過程后，在過程內部值為：11（id_inner），但外部變量值依舊為：10（id_out）。 MySQL 存儲過程 “out” 參數：從存儲過程內部傳值給調用者。在存儲過程內部，該參數初始值為 null，無論調用者是否給存儲過程參數設置值。 create procedure pr_param_out( out id int)begin select id as id_inner_1; /* id 初始值為 null*/ if (id is not null) then set id = id + 1; select id as id_inner_2; else select 1 into id; end if; select id as id_inner_3;end;set @id = 10; call pr_param_out(@id); select @id as id_out;mysql> set @id = 10;mysql>mysql> call pr_param_out(@id); /*外部變量不能傳給存儲過程*/+------------+| id_inner_1 |+------------+| NULL |+------------+ +------------+| id_inner_3 |+------------+| 1 |+------------+ mysql> select @id as id_out; /*過程將改變的值傳給外部變量*/+--------+| id_out |+--------+| 1 |+--------+可以看出，雖然我們設置了用戶定義變量 @id 為 10，傳遞 @id 給存儲過程后，在存儲過程內部，id 的初始值總是 null（id_inner_1）。最后 id 值（id_out = 1）傳回給調用者。 MySQL 存儲過程 inout 參數跟 out 類似，都可以從存儲過程內部傳值給調用者。不同的是：調用者還可以通過 inout 參數傳遞值給存儲過程。 drop procedure if exists pr_param_inout; create procedure pr_param_inout( inout id int)begin select id as id_inner_1; -- id 值為調用者傳進來的值 if (id is not null) then set id = id + 1; select id as id_inner_2; else select 1 into id; end if; select id as id_inner_3;end;set @id = 10; call pr_param_inout(@id); select @id as id_out;mysql> set @id = 10; mysql>mysql> call pr_param_inout(@id);+------------+| id_inner_1 |+------------+| 10 |+------------+ +------------+| id_inner_2 |+------------+| 11 |+------------+ +------------+| id_inner_3 |+------------+| 11 |+------------+mysql>mysql> select @id as id_out;+--------+| id_out |+--------+| 11 |+--------+從結果可以看出：我們把 @id（10），傳給存儲過程后，存儲過程最后又把計算結果值 11（id_inner_3）傳回給調用者。 MySQL 存儲過程 inout 參數的行為跟 C 語言函數中的引用傳值類似。 通過以上例子：如果僅僅想把數據傳給 MySQL 存儲過程，那就使用“in” 類型參數；如果僅僅從 MySQL 存儲過程返回值，那就使用“out” 類型參數；如果需要把數據傳給 MySQL 存儲過程，還要經過一些計算后再傳回給我們，此時，要使用“inout” 類型參數。 9、聲明和設置變量 （1） 聲明變量 在存儲例程使用局部變量之前，必須聲明局部變量，變量聲明通過DECLARE語句實現，其原型如下： DECLARE variable_name TYPE ; 如：DECLARE x VARCHAR(254); 在聲明變量時，聲明必須放在BEGIN/END塊中。此外聲明必須在執行該塊任何其它語句之前進行。 （2） 設置變量 SET語句用來設置聲明的存儲例程變量值。其原型如下： SET variable_name =value; 如下聲明和設置變量過程： DECLARE x INT; SET x = 155; 也可使用SELECT…..INTO語句設置變量。 如： SELECT 155 INTO x; 當然，此變量是聲明該變量的BEGIN/END塊作用范圍內的一個局部變量。如果希望在存儲例程外使用此變量，需要將其作為OUT變量傳遞。
