# 【mysql的編程專題④】存儲過程 標簽（空格分隔）： Mysql --- > 類似函數,但是沒有返回值,把sql進行封裝，便于多次使用或多種應用程序共享使用.不能用在SQL語句中，只能使用CALL調用; ## 創建存儲過程 ### 語法 ```sql CREATE PROCEDURE sp_name ([proc_parameter[,...]]) [characteristic ...] routine_body ``` > ``sp_name``是存儲過程的名稱；``proc_parameter``表示存儲過程的參數列表； ``characteristic``指定存儲過程的特性；``routine_body``參數是SQL代碼的內容，多條sql以``;``隔開，可以用``BEGIN…END``來標志SQL代碼的開始和結束。 ### 參數 proc_parameter中的每個參數由3部分組成。這3部分分別是輸入輸出類型、參數名稱和參數類型。 ```sql [ IN | OUT | INOUT ] param_name type ``` > IN表示輸入參數(可以看做聲明參數為函數的局部變量,內部做改變外部不被影響)；OUT表示輸出參數(調用結束后外部可以訪問到這個變量,內部能改變外部的變量)； INOUT表示既可以是輸入，也可以是輸出; ```sql CREATE PROCEDURE pr_param_in(in id int) BEGIN IF id is not null THEN set id = id + 1; END IF; SELECT id as id_inner; END // mysql> call pr_param_in(10); -- 如果存儲過程的輸出類型為in的話，不會報錯； +----------+ | id_inner | +----------+ | 11 | +----------+ mysql> call pr_param_in(@id); +----------+ | id_inner | +----------+ | 101 | +----------+ 1 row in set Query OK, 0 rows affected mysql> select @id as id_out; -- in是不會影響外部的變量的； +--------+ | id_out | +--------+ | 100 | +--------+ 1 row in set CREATE PROCEDURE pr_param_out (OUT id INT) BEGIN SELECT id AS id_inner_1; -- 如果參數的輸出類型為out的話，那外面的int必須是一個變量，并且無論傳什么參數進來都為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 // call pr_param_out(10); -- 1411錯誤：子程序%s的OUT或INOUT參量不是變量。 1414 - OUT or INOUT argument 1 for routine test.pr_param_out is not a variable or NEW pseudo-variable in BEFORE trigger mysql> set @var = 10; Query OK, 0 rows affected mysql> call pr_param_out(@var); +------------+ | id_inner_1 | +------------+ | NULL | +------------+ 1 row in set +------------+ | id_inner_3 | +------------+ | 1 | +------------+ 1 row in set mysql> select @var as id_out; -- 如果參數的輸出類型為out的話，傳入什么值都會變為NULL，然后在內部都會改變外部變量的值； +--------+ | id_out | +--------+ | 1 | +--------+ 1 row in set CREATE PROCEDURE pr_param_inout (INOUT id INT) BEGIN SELECT id AS id_inner_1; -- 如果參數的輸出類型為inout的話，那外面的int必須是一個變量。外面傳的變量是能夠進來的； 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 // mysql> set @var = 100; mysql> call pr_param_inout(@var); +------------+ | id_inner_1 | +------------+ | 100 | +------------+ 1 row in set +------------+ | id_inner_2 | +------------+ | 101 | +------------+ 1 row in set +------------+ | id_inner_3 | +------------+ | 101 | +------------+ 1 row in set mysql> select @var; -- 能夠被改變； +------+ | @var | +------+ | 101 | +------+ ``` + type參數指定存儲過程的參數類型，該類型可以是MySQL數據庫的任意數據類型; + 參數是可有可無; + 參數名和字段名最好不要一樣; ```sql DELIMITER // CREATE PROCEDURE p1() SELECT * FROM t;// -- 如果只有一句sql的話，可以不用寫begin和end； ``` ### 存儲過程的特性 characteristic參數有多個取值。其取值說明如下： + LANGUAGE SQL：說明routine_body部分是由SQL語言的語句組成，這也是數據庫系統默認的語言。 + [NOT] DETERMINISTIC：指明存儲過程的執行結果是否是確定的。DETERMINISTIC表示結果是確定的。每次執行存儲過程時，相同的輸入會得到相同的輸出。NOT DETERMINISTIC表示結果是非確定的，相同的輸入可能得到不同的輸出。默認情況下，結果是非確定的。 + { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }：指明子程序使用SQL語句的限制。CONTAINS SQL表示子程序包含SQL語句，但不包含讀或寫數據的語句；NO SQL表示子程序中不包含SQL語句；READS SQL DATA表示子程序中包含讀數據的語句；MODIFIES SQL DATA表示子程序中包含寫數據的語句。默認情況下，系統會指定為CONTAINS SQL。 + SQL SECURITY { DEFINER | INVOKER }：指明誰有權限來執行。DEFINER表示只有定義者自己才能夠執行；INVOKER表示調用者可以執行。默認情況下，系統指定的權限是DEFINER。 + COMMENT 'string'：注釋信息。 > 技巧：創建存儲過程時，系統默認指定CONTAINS SQL，表示存儲過程中使用了SQL語句。但是，如果存儲過程中沒有使用SQL語句，最好設置為NO SQL。而且，存儲過程中最好在COMMENT部分對存儲過程進行簡單的注釋，以便以后在閱讀存儲過程的代碼時更加方便。 ### Example1 ```sql CREATE PROCEDURE num_from_employee (IN emp_id INT, OUT count_num INT ) READS SQL DATA COMMENT 'this is a test_pro' BEGIN SELECT COUNT(*) INTO count_num FROM employee WHERE d_id=emp_id ; END ``` > 上述代碼中，存儲過程名稱為num_from_employee；輸入變量為emp_id；輸出變量為count_num。SELECT語句從employee表查詢d_id值等于emp_id的記錄，并用COUNT(*)計算d_id值相同的記錄的條數，最后將計算結果存入count_num中,comment為注釋; **更多的例子請看流程控制** ## 查看存儲過程 ```sql SHOW PROCEDURE STATUS\G *************************** 1. row *************************** Db: test Name: p1 Type: PROCEDURE Definer: root@localhost Modified: 2015-05-01 09:49:20 Created: 2015-05-01 09:49:20 Security_type: DEFINER Comment: character_set_client: utf8 collation_connection: utf8_general_ci Database Collation: latin1_swedish_ci ``` ## 刪除存儲過程 ```sql mysql> drop procedure p1; -- p1 存儲過程的名稱 Query OK, 0 rows affected ```