創(chuàng)建存儲(chǔ)過程

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

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

proc_parameter中的每個(gè)參數(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ù)有多個(gè)取值。其取值說明如下：
<li>LANGUAGE SQL:說明routine_body部分是由SQL語言的語句組成，這也是數(shù)據(jù)庫系統(tǒng)默認(rèn)的語言.
<li>[NOT] DETERMINISTIC:指明存儲(chǔ)過程的執(zhí)行結(jié)果是否是確定的。DETERMINISTIC表示結(jié)果是確定的。每次執(zhí)行存儲(chǔ)過程時(shí)，相同的輸入會(huì)得到相同的輸出。NOT DETERMINISTIC表示結(jié)果是非確定的，相同的輸入可能得到不同的輸出。默認(rèn)情況下，結(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ù)的語句。默認(rèn)情況下，系統(tǒng)會(huì)指定為CONTAINS SQL。
<li>SQL SECURITY { DEFINER | INVOKER }:指明誰有權(quán)限來執(zhí)行。DEFINER表示只有定義者自己才能夠執(zhí)行；INVOKER表示調(diào)用者可以執(zhí)行。默認(rèn)情況下，系統(tǒng)指定的權(quán)限是DEFINER。

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

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 

上述代碼中，存儲(chǔ)過程名稱為num_from_employee；輸入變量為emp_id；輸出變量為count_num。SELECT語句從employee表查詢d_id值等于emp_id的記錄，并用COUNT(*)計(jì)算d_id值相同的記錄的條數(shù)，最后將計(jì)算結(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í)行完畢后，沒有報(bào)出任何出錯(cuò)信息就表示存儲(chǔ)函數(shù)已經(jīng)創(chuàng)建成功。以后就可以調(diào)用這個(gè)存儲(chǔ)過程，數(shù)據(jù)庫中會(huì)執(zhí)行存儲(chǔ)過程中的SQL語句。

說明：MySQL中默認(rèn)的語句結(jié)束符為分號(hào)（;）。存儲(chǔ)過程中的SQL語句需要分號(hào)來 結(jié)束。為了避免沖突，首先用"DELIMITER &&"將MySQL的結(jié)束符設(shè)置為&&。最后再用"DELIMITER ;"來將結(jié)束符恢復(fù)成分號(hào)。這與創(chuàng)建觸發(fā)器時(shí)是一樣的。

COMMENT 'string'：注釋信息。

技巧：創(chuàng)建存儲(chǔ)過程時(shí)，系統(tǒng)默認(rèn)指定CONTAINS SQL，表示存儲(chǔ)過程中使用了SQL語句。但是，如果存儲(chǔ)過程中沒有使用SQL語句，最好設(shè)置為NO SQL。而且，存儲(chǔ)過程中最好在COMMENT部分對(duì)存儲(chǔ)過程進(jìn)行簡單的注釋，以便以后在閱讀存儲(chǔ)過程的代碼時(shí)更加方便。

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

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

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

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

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

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

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

上述代碼中，存儲(chǔ)函數(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é)果顯示，存儲(chǔ)函數(shù)已經(jīng)創(chuàng)建成功。該函數(shù)的使用和MySQL內(nèi)部函數(shù)的使用方法一樣。

變量的使用

在存儲(chǔ)過程和函數(shù)中，可以定義和使用變量。用戶可以使用DECLARE關(guān)鍵字來定義變量。然后可以為變量賦值。這些變量的作用范圍是BEGIN…END程序段中。本小節(jié)將講解如何定義變量和為變量賦值。

定義變量

MySQL中可以使用DECLARE關(guān)鍵字來定義變量。定義變量的基本語法如下：

DECLARE  //DECLARE關(guān)鍵字是用來聲明變量的
var_name[,...] //變量的名稱，這里可以同時(shí)定義多個(gè)變量
type//用來指定變量的類型
[DEFAULT value] //將變量默認(rèn)值設(shè)置為value，沒有使用DEFAULT子句時(shí)，默認(rèn)值為NULL

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

DECLARE  my_sql  INT  DEFAULT 10 ; 

為變量賦值

MySQL中可以使用SET關(guān)鍵字來為變量賦值。SET語句的基本語法如下：

SET//SET關(guān)鍵字是用來為變量賦值的 
 var_name//變量的名稱
 = expr//賦值表達(dá)式
 [, var_name = expr] ... //一個(gè)SET語句可以同時(shí)為多個(gè)變量賦值，各個(gè)變量的賦值語句之間用逗號(hào)隔開

`下面為變量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)用存儲(chǔ)過程

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 {
//這里最好不要這么干，因?yàn)轵?qū)動(dòng)名寫死在程序中了 Class.forName("com.mysql.jdbc.Driver");
//實(shí)際項(xiàng)目中，這里應(yīng)用 DataSource 數(shù)據(jù)，如果用框架， 
//這個(gè)數(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 第幾個(gè)，不同的數(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();