MySql數據庫之存儲過程學習
之前在工作中總是聽別人提到存儲過程，覺得是個很高深的東西，利用工作之余，看了下相關的知識，現將學習知識總結如下，希望可以為剛學習的人提供些許幫助。
開發環境：Navicat For Mysql。
MySQL存儲過程
1.1、CREATE PROCEDURE （創建）
CREATE PROCEDURE存儲過程名 (參數列表)
BEGIN
SQL語句代碼塊
END
注意：
由括號包圍的參數列必須總是存在。如果沒有參數，也該使用一個空參數列()。每個參數默認都是一個IN參數。要指定為其它參數，可在參數名之前使用關鍵詞 OUT或INOUT
實例演練：
eg1，帶(輸出參數)返回值的存儲過程：
1、建表
create table abin5(
id int,
name5 VARCHAR(39)
)
2、創建存儲過程
create procedure pabin5(out n int)
BEGIN
select count(*) from abin5;
END
3、測試存儲過程
call pabin5(@n)
eg2，帶輸入參數的存儲過程：
1、建立存儲過程
create procedure pabin6(in n int)
BEGIN
SELECT * FROM abin5 where id=n;
END
2、測試存儲過程
SET @n=1;
CALL pabin6(@n)
或者
CALL pabin6(1)
在mysql客戶端定義存儲過程的時候使用delimiter命令來把語句定界符從;變為//。
當使用delimiter命令時，你應該避免使用反斜槓(‘"’)字符，因為那是MySQL的轉義字符。
如：
mysql> delimiter //
mysql> CREATE PROCEDURE simpleproc (OUT param1 INT)
-> BEGIN
-> SELECT COUNT(*) INTO param1 FROM t;
-> END
-> //
Query OK, 0 rows affected (0.00 sec)
1.2 ALTER PROCEDURE （修改）
ALTER PROCEDURE 存儲過程名SQL語句代碼塊
這個語句可以被用來改變一個存儲程序的特征。
1.3 DROP PROCEDURE （刪除）
DROP PROCEDURE IF EXISTS存儲過程名
eg:DROP PROCEDURE IF EXISTS proc_employee (proc_employee 存儲過程名)
這個語句被用來移除一個存儲程序。不能在一個存儲過程中刪除另一個存儲過程，只能調用另一個存儲過程
1.4 SHOW CREATE PROCEDURE（類似於SHOW CREATE TABLE，查看一個已存在的存儲過程）
SHOW CREATE PROCEDURE 存儲過程名
1.5 SHOW PROCEDURE STATUS （列出所有的存儲過程）
SHOW PROCEDURE STATUS
1.6 CALL語句（存儲過程的調用）
CALL 存儲過程名(參數列表)
CALL語句調用一個先前用CREATE PROCEDURE創建的程序。
CALL語句可以用聲明為OUT或的INOUT參數的參數給它的調用者傳回值。
存儲過程名稱後面必須加括號，哪怕該存儲過程沒有參數傳遞
1.7 BEGIN ... END（復合語句）
[begin_label:]
BEGIN
[statement_list]
END
[end_label]
存儲子程序可以使用BEGIN ... END復合語句來包含多個語句。
statement_list 代表一個或多個語句的列表。statement_list之內每個語句都必須用分號（；）來結尾。
復合語句可以被標記。除非begin_label存在,否則end_label不能被給出,並且如果二者都存在,他們必須是同樣的。
1.8 DECLARE語句（用來聲明局部變量）
DECLARE語句被用來把不同項目局域到一個子程序：局部變量
DECLARE僅被用在BEGIN ... END復合語句裡，並且必須在復合語句的開頭，在任何其它語句之前。
1.9 存儲程序中的變量
1.1 DECLARE局部變量
DECLARE var_name[,...] type [DEFAULT value]
這個語句被用來聲明局部變量。
要給變量提供一個默認值，請包含一個DEFAULT子句。
值可以被指定為一個表達式，不需要為一個常數。
如果沒有DEFAULT子句，初始值為NULL。
局部變量的作用范圍在它被聲明的BEGIN ... END塊內。
它可以被用在嵌套的塊中，除了那些用相同名字聲明變量的塊。
1.2 變量SET語句
SET var_name = expr [, var_name = expr]
在存儲程序中的SET語句是一般SET語句的擴展版本。
被參考變量可能是子程序內聲明的變量，或者是全局服務器變量。
在存儲程序中的SET語句作為預先存在的SET語法的一部分來實現。這允許SET a=x, b=y, ...這樣的擴展語法。
其中不同的變量類型（局域聲明變量及全局和集體變量）可以被混合起來。
這也允許把局部變量和一些只對系統變量有意義的選項合並起來。
1.3 SELECT ... INTO語句
SELECT col_name[,...] INTO var_name[,...] table_expr
這個SELECT語法把選定的列直接存儲到變量。
因此，只有單一的行可以被取回。
SELECT id,data INTO x,y FROM test.t1 LIMIT 1;
注意，用戶變量名在MySQL 5.1中是對大小寫不敏感的。
重要: SQL變量名不能和列名一樣。如果SELECT ... INTO這樣的SQL語句包含一個對列的參考，並包含一個與列相同名字的局部變量，MySQL當前把參考解釋為一個變量的名字。
1.10 MySQL 存儲過程參數類型（in、out、inout）
MySQL 存儲過程參數（in）
MySQL 存儲過程 “in” 參數：跟 C 語言的函數參數的值傳遞類似， MySQL 存儲過程內部可能會修改此參數，但對 in 類型參數的修改，對調用者（caller）來說是不可見的（not visible）。
MySQL 存儲過程參數（out）
MySQL 存儲過程 “out” 參數：從存儲過程內部傳值給調用者。在存儲過程內部，該參數初始值為 null，無論調用者是否給存儲過程參數設置值
MySQL 存儲過程參數（inout）
MySQL 存儲過程 inout 參數跟 out 類似，都可以從存儲過程內部傳值給調用者。不同的是：調用者還可以通過 inout 參數傳遞值給存儲過程。
總結
如果僅僅想把數據傳給 MySQL 存儲過程，那就使用“in” 類型參數；如果僅僅從 MySQL 存儲過程返回值，那就使用“out” 類型參數；如果需要把數據傳給 MySQL 存儲過程，還要經過一些計算後再傳回給我們，此時，要使用“inout” 類型參數。
1.11 例子：
1.1 創建存儲過程
帶(輸出參數)返回值的存儲過程：
--刪除存儲過程
DROP PROCEDURE IF EXISTS proc_employee_getCount
--創建存儲過程
CREATE PROCEDURE proc_employee_getCount(out n int)
BEGIN
SELECT COUNT(*) FROM employee ;
END
--MYSQL調用存儲過程
CALL proc_employee_getCount(@n);
帶輸入參數的存儲過程：
--刪除存儲過程
DROP PROCEDURE IF EXISTS proc_employee_findById;
--創建存儲過程
CREATE PROCEDURE proc_employee_findById(in n int)
BEGIN
SELECT * FROM employee where id=n;
END
--定義變量
SET @n=1;
--調用存儲過程
CALL proc_employee_findById(@n);
操作存儲過程時應注意:
1. 刪除存儲過程時只需要指定存儲過程名即可，不帶括號；
2. 創建存儲過程時，不管該存儲過程有無參數，都需要帶括號；
3. 在使用SET定義變量時應遵循SET的語法規則；
SET @變量名=初始值；
4. 在定義存儲過程參數列表時，應注意參數名與數據庫中字段名區別開來，否則將出現無法預期的結果
1.12 Java代碼調用存儲過程(JDBC)
相關API：java.sql.CallableStatement
使用到java.sql.CallableStatement接口，該接口專門用來調用存儲過程;
該對象的獲得依賴於java.sql.Connection;
通過Connection實例的prepareCall()方法返回CallableStatement對象
prepareCall()內部為一固定寫法{call 存儲過程名(參數列表1,參數列表2)}可用?占位
eg: connection.prepareCall("{call proc_employee(?)}");
存儲過程中參數處理:
輸入參數:通過java.sql.CallableStatement實例的setXXX()方法賦值，用法等同於java.sql.PreparedStatement
輸出參數:通過java.sql.CallableStatement實例的registerOutParameter(參數位置, 參數類型)方法賦值,其中參數類型主要使用java.sql.Types中定義的類型
Java代碼調用帶輸入參數的存儲過程 (根據輸入ID查詢雇員信息)
publicvoid executeProcedure()
{
try {
/**
*callableStatementjava.sql.CallableStatement
*connectionjava.sql.Connection
*jdbc調用存儲過程原型
*{call存儲過程名(參數列表1,參數列表2)}可用?代替
*/
callableStatement=connection.prepareCall("{call proc_employee_findById(?)}");
callableStatement.setInt(1, 1); //設置輸入參數
resultSet=callableStatement.executeQuery();//執行存儲過程
if(resultSet.next())
{
System.out.println(resultSet.getInt(1)+""t"+resultSet.getString(2));
}
} catch (SQLException e) {
e.printStackTrace();
}
}
Java代碼調用帶輸出參數的存儲過程 (返回數據庫中的記錄數)
publicvoid executeProcedure()
{
try {
/**
*callableStatementjava.sql.CallableStatement
*connectionjava.sql.Connection
*jdbc調用存儲過程原型
*{call存儲過程名(參數列表1,參數列表2)}可用?代替
*/
callableStatement=connection.prepareCall("{call proc_employee_getCount(?)}");
//設置輸出參數
callableStatement.registerOutParameter(1, Types.INTEGER);
//執行存儲過程
resultSet=callableStatement.executeQuery();
if(resultSet.next())
{
System.out.println(resultSet.getInt(1));
}
} catch (SQLException e) {
e.printStackTrace();
}
}
