創(chuàng)建存儲過程

MySQL中，創(chuàng)建存儲過程的基本形式如下：

1.CREATE PROCEDURE sp_name//存儲過程的名稱自行定義
 ([proc_parameter[,...]]) //proc_parameter表示存儲過程的參數(shù)列表 
 2.[characteristic ...] //characteristic參數(shù)指定存儲過程的特性
routine_body //routine_body參數(shù)是SQL代碼的內(nèi)容蛮浑，可以用BEGIN…END來標志SQL代碼的開始和結(jié)束沮稚。

proc_parameter中的每個參數(shù)由3部分組成册舞。這3部分分別是輸入輸出類型调鲸、參數(shù)名稱和參數(shù)類型。其形式如下：

[ IN | OUT | INOUT ] param_name type 
//1.IN表示輸入?yún)?shù)
//2.OUT表示輸出參數(shù)
//3.INOUT表示既可以輸入也可以是輸出參數(shù)
//param_name表示參數(shù)的名稱自行定義
//type表示參數(shù)的類型饶唤，該類型可以是MySQL數(shù)據(jù)庫的任意數(shù)據(jù)類型

characteristic參數(shù)有多個取值募狂。其取值說明如下：
<li>LANGUAGE SQL:說明routine_body部分是由SQL語言的語句組成祸穷，這也是數(shù)據(jù)庫系統(tǒng)默認的語言.
<li>[NOT] DETERMINISTIC:指明存儲過程的執(zhí)行結(jié)果是否是確定的勺三。DETERMINISTIC表示結(jié)果是確定的。每次執(zhí)行存儲過程時祈远，相同的輸入會得到相同的輸出车份。NOT DETERMINISTIC表示結(jié)果是非確定的牡彻，相同的輸入可能得到不同的輸出。默認情況下严就，結(jié)果是非確定的器罐。
<li>{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }:指明子程序使用SQL語句的限制技矮。CONTAINS SQL表示子程序包含SQL語句，但不包含讀或?qū)憯?shù)據(jù)的語句袒炉；NO SQL表示子程序中不包含SQL語句我磁；READS SQL DATA表示子程序中包含讀數(shù)據(jù)的語句驻襟；MODIFIES SQL DATA表示子程序中包含寫數(shù)據(jù)的語句沉衣。默認情況下，系統(tǒng)會指定為CONTAINS SQL存谎。
<li>SQL SECURITY { DEFINER | INVOKER }:指明誰有權限來執(zhí)行既荚。DEFINER表示只有定義者自己才能夠執(zhí)行栋艳；INVOKER表示調(diào)用者可以執(zhí)行。默認情況下晴叨，系統(tǒng)指定的權限是DEFINER篙螟。

下面創(chuàng)建一個名為num_from_employee的存儲過程问拘。代碼如下：

CREATE  PROCEDURE  num_from_employee (IN emp_id INT, OUT count_num INT )  
          READS SQL DATA  
          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值相同的記錄的條數(shù)，最后將計算結(jié)果存入count_num中皆怕。代碼的執(zhí)行結(jié)果如下：

mysql> DELIMITER &&  
mysql> CREATE  PROCEDURE  num_from_employee
(IN emp_id INT, OUT count_num INT )  
    -> READS SQL DATA  
    -> BEGIN  
    -> SELECT  COUNT(*)  INTO  count_num  
    -> FROM  employee  
    -> WHERE  d_id=emp_id ;  
    -> END &&  
Query OK, 0 rows affected (0.09 sec)  
mysql> DELIMITER ; 

代碼執(zhí)行完畢后愈腾，沒有報出任何出錯信息就表示存儲函數(shù)已經(jīng)創(chuàng)建成功岂津。以后就可以調(diào)用這個存儲過程，數(shù)據(jù)庫中會執(zhí)行存儲過程中的SQL語句橱乱。

說明：MySQL中默認的語句結(jié)束符為分號（;）仅醇。存儲過程中的SQL語句需要分號來 結(jié)束魔种。為了避免沖突，首先用"DELIMITER &&"將MySQL的結(jié)束符設置為&&叶摄。最后再用"DELIMITER ;"來將結(jié)束符恢復成分號安拟。這與創(chuàng)建觸發(fā)器時是一樣的糠赦。

COMMENT 'string'：注釋信息锅棕。

技巧：創(chuàng)建存儲過程時淌山，系統(tǒng)默認指定CONTAINS SQL泼疑，表示存儲過程中使用了SQL語句。但是移稳，如果存儲過程中沒有使用SQL語句会油，最好設置為NO SQL钞啸。而且，存儲過程中最好在COMMENT部分對存儲過程進行簡單的注釋梭稚，以便以后在閱讀存儲過程的代碼時更加方便絮吵。

創(chuàng)建存儲函數(shù)

在MySQL中蹬敲，創(chuàng)建存儲函數(shù)的基本形式如下：

CREATE FUNCTION sp_name //函數(shù)名自行定義
([func_parameter[,...]])  //函數(shù)參數(shù)表
        RETURNS type  //指定返回值的類型
        [characteristic ...] //參數(shù)指定存儲函數(shù)的特性，該參數(shù)的取值與存儲過程中的取值是一樣的,具體參考上面
routine_body //SQL代碼的內(nèi)容急波，可以用BEGIN…END來標志SQL代碼的開始和結(jié)束澄暮。

func_parameter可以由多個參數(shù)組成阱扬，其中每個參數(shù)由參數(shù)名稱和參數(shù)類型組成，其形式如下：

param_name//參數(shù)是存儲函數(shù)的參數(shù)名稱
type //指定存儲函數(shù)的參數(shù)類型馍刮，該類型可以是MySQL數(shù)據(jù)庫的任意數(shù)據(jù)類型

下面創(chuàng)建一個名為name_from_employee的存儲函數(shù)卡啰。代碼如下：

CREATE  FUNCTION  name_from_employee (emp_id INT )  
          RETURNS VARCHAR(20)  
          BEGIN  
              RETURN  (SELECT  name  
              FROM  employee  
              WHERE  num=emp_id );  
          END 

上述代碼中，存儲函數(shù)的名稱為name_from_employee姊扔；該函數(shù)的參數(shù)為emp_id梅誓；返回值是VARCHAR類型佛南。SELECT語句從employee表查詢num值等于emp_id的記錄，并將該記錄的name字段的值返回及穗。代碼的執(zhí)行結(jié)果如下：

mysql> DELIMITER &&  
mysql> CREATE  FUNCTION  name_from_employee (emp_id INT )  
    -> RETURNS VARCHAR(20)  
    -> BEGIN  
    -> RETURN  (SELECT  name  
    -> FROM  employee  
    -> WHERE  num=emp_id );  
    -> END&&  
Query OK, 0 rows affected (0.00 sec)  
mysql> DELIMITER ; 

結(jié)果顯示埂陆，存儲函數(shù)已經(jīng)創(chuàng)建成功。該函數(shù)的使用和MySQL內(nèi)部函數(shù)的使用方法一樣焚虱。

變量的使用

在存儲過程和函數(shù)中鹃栽，可以定義和使用變量民鼓。用戶可以使用DECLARE關鍵字來定義變量蓬抄。然后可以為變量賦值。這些變量的作用范圍是BEGIN…END程序段中饮亏。本小節(jié)將講解如何定義變量和為變量賦值克滴。

定義變量

MySQL中可以使用DECLARE關鍵字來定義變量优床。定義變量的基本語法如下：

DECLARE  //DECLARE關鍵字是用來聲明變量的
var_name[,...] //變量的名稱胆敞，這里可以同時定義多個變量
type//用來指定變量的類型
[DEFAULT value] //將變量默認值設置為value，沒有使用DEFAULT子句時仍翰，默認值為NULL

下面定義變量my_sql，數(shù)據(jù)類型為INT型越平，默認值為10秦叛。代碼如下：

DECLARE  my_sql  INT  DEFAULT 10 ; 

為變量賦值

MySQL中可以使用SET關鍵字來為變量賦值挣跋。SET語句的基本語法如下：

SET//SET關鍵字是用來為變量賦值的 
 var_name//變量的名稱
 = expr//賦值表達式
 [, var_name = expr] ... //一個SET語句可以同時為多個變量賦值狞换，各個變量的賦值語句之間用逗號隔開

`下面為變量my_sql賦值為30修噪。代碼如下：

SET  my_sql = 30 ; 

MySQL中還可以使用SELECT…INTO語句為變量賦值。其基本語法如下：

SELECT  
  col_name[,…] //表示查詢的字段名稱
INTO  
  var_name[,…] //變量的名稱
FROM  
  table_name //表的名稱
WEHRE  
  condition //查詢條件

下面從employee表中查詢id為2的記錄膨报，將該記錄的d_id值賦給變量my_sql现柠。代碼如下：

SELECT  
  d_id  
INTO  
  my_sql  
FROM  
  employee  
WEHRE 
  id=2 ; 

用 JDBC 如何調(diào)用存儲過程

import java.sql.CallableStatement; import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import java.sql.Types;
publicclass JdbcTest {
/**
* @paramargs */
public static voidmain(String[] args) {
// TODO Auto-generated method stub 
Connection cn = null; 
CallableStatement cstmt =null;
try {
//這里最好不要這么干弛矛，因為驅(qū)動名寫死在程序中了 Class.forName("com.mysql.jdbc.Driver");
//實際項目中，這里應用 DataSource 數(shù)據(jù)周循，如果用框架湾笛， 
//這個數(shù)據(jù)源不需要我們編碼創(chuàng)建闰歪，我們只需 Datasource ds =context.lookup()
//cn = ds.getConnection();
cn = DriverManager.getConnection("jdbc:mysql:///test","root","root"); 
cstmt = cn.prepareCall("{callinsert_Student(?,?,?)}"); 
cstmt.registerOutParameter(3,Types.INTEGER); 
cstmt.setString(1,"wangwu");
cstmt.setInt(2, 25);
cstmt.execute();
//get 第幾個库倘，不同的數(shù)據(jù)庫不一樣，建議不寫 System.out.println(cstmt.getString(3));
} catch (Exception e) {
// TODO Auto-generated catchblock e.printStackTrace();
}
finally
{
} }
}
/*try{cstmt.close();}catch(Exceptione){} try{cn.close();}catch(Exceptione){}*/ try {
if(cstmt !=null) cstmt.close();
if(cn !=null) cn.close();
} catch (SQLException e) {
// TODO Auto-generatedcatch block e.printStackTrace();