MySQL預存程序以及在Java中的程式調用

MySQL預存程序以及在Java中的程式調用

預存程序是一種儲存在資料庫中的程式（就像正規語言裡的子程式一樣），準確的來說，MySQL支援的“routines（常式）”有兩種：一是我們說的預存程序， 二是在其他SQL語句中可以傳回值的函數（使用起來和Mysql預裝載的函數一樣，如pi()）。本文是為初學習MySQL預存程序的朋友寫的，在本文中我們以在MySQL的CLI（command line interface）中建立一個預存程序的樣本開始，然後通過在Java程式中調用我們建立好的預存程序，讓讀者對MySQL的預存程序有個大概的瞭解和入門。

MySQL預存程序及Java中預存程序的調用 

一、建立MySQL預存程序樣本

下面是具體的建立過程：

--啟動MySQL服務
C:/Documents and Settings/Zengming Zhang>net start mysql

MySQL 服務已經啟動成功。

--登入MySQL控制台
C:/Documents and Settings/Zengming Zhang>mysql -u root -p
Enter password: **********
Welcome to the MySQL monitor.  Commands end with ; or /g.
Your MySQL connection id is 1 to server version: 5.0.18-nt

Type 'help;' or '/h' for help. Type '/c' to clear the buffer.

--選擇資料庫
mysql> use test;
Database changed

--建立樣本用表
mysql> create table zzm(
    -> id int primary key auto_increment,
    -> name varchar(10)
    -> );
Query OK, 0 rows affected (0.20 sec)
mysql> insert into zzm(name) values('zhang');
Query OK, 1 row affected (0.08 sec)

mysql> insert into zzm(name) values('zeng');
Query OK, 1 row affected (0.05 sec)

mysql> insert into zzm(name) values('ming');
Query OK, 1 row affected (0.05 sec)

mysql> select * from zzm;
+----+-------+
| id | name  |
+----+-------+
|  1 | zhang |
|  2 | zeng  |
|  3 | ming  |
+----+-------+
3 rows in set (0.00 sec)

--更改命令結束符（因為在procedure中經常要用到預設的命令結束符--分號(;)
--所以在建立procedure的時候需要定義新的結束符以說明建立procedure的命令結束）
--這裡將結束符號改成貨幣符號--$
mysql> delimiter $

--建立預存程序p3
--此預存程序的過程名是p3，該過程包含兩個參數，
--一個是輸入類型的（以IN標示），參數名是nameid，類型是int，
--一個是輸出類型的（以OUT標示），參數名是person_name，類型是varchar(10)
--此預存程序的作用是查詢出zzm表的全部內容，會輸出結果集（data set），然後
--再查詢表中記錄的ID是nameid的欄位name，將其輸出到第二個輸出類型的參數裡面，這個查詢
--不會輸出結果集。
mysql> create procedure p3(IN nameid int, OUT person_name varchar(10))
    -> begin
    -> select * from test.zzm;
    -> select zzm.name into person_name from test.zzm where zzm.id = nameid;
    -> end
    -> $
Query OK, 0 rows affected (0.00 sec)

--建立完成，查看資料庫中所有已經建立的預存程序
mysql> show procedure status $
+------+------+-----------+----------------+---------------------+---------------------+---------------+---------+
| Db  | Name | Type      | Definer        | Modified            | Created            | Security_type | Comment |
+------+------+-----------+----------------+---------------------+---------------------+---------------+---------+
| test | p3  | PROCEDURE | root@localhost | 2009-08-18 16:40:21 | 2009-08-18 16:40:21 | DEFINER      |        |
+------+------+-----------+----------------+---------------------+---------------------+---------------+---------+
1 row in set (0.02 sec)

--調用預存程序
mysql> call p3(3,@name) $
+----+-------+
| id | name  |
+----+-------+
|  1 | zhang |
|  2 | zeng  |
|  3 | ming  |
+----+-------+
3 rows in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> select @name $
+-------+
| @name |
+-------+
| ming  |
+-------+
1 row in set (0.00 sec)

二、在Java程式中調用預存程序的程式樣本

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Types;

public class Main {

 public static void main(String[] args) {
  /* JDBC串連MySQL資料庫的參數 */
  String driverName = "com.mysql.jdbc.Driver";
  String userName = "root";
  String userPasswd = "nicegiving";
  String dbName = "test";
  String url = "jdbc:mysql://localhost/" + dbName + "?user=" + userName
    + "&password=" + userPasswd;
  Connection connection = null;
  CallableStatement stmt = null;
 
  try {
  // 載入資料庫驅動程式
  Class.forName(driverName).newInstance();
  // 串連資料庫
  connection = DriverManager.getConnection(url);
  // 調用預存程序，此預存程序有2個參數
  stmt = connection.prepareCall("{call p3(?,?)}");
  // 第一個參數是輸入的，在此設定第一個參數的值：將第一個參數設定成整數值3
  stmt.setInt(1, 3);
  // 第二個參數是輸出的，在此設定第二個參數的輸出類型為VARCHAR
  stmt.registerOutParameter(2, Types.VARCHAR);
  // 執行預存程序
  boolean hadResults = stmt.execute();
 
  // 如果有查詢語句的話，此執行過程會返回結果集，在此處理結果集裡面的東西
  System.out.println("Data from table:");
  while (hadResults) {
    ResultSet rs = stmt.getResultSet();
    while(rs.next()){
    String id = rs.getString(1);
    String name = rs.getString(2);
    System.out.println("ID = "+id+"/tName = " + name);
    }
    hadResults = stmt.getMoreResults();
  }
 
  // 擷取預存程序的傳回值
  System.out.println("/nData from procedure:");
  String name = stmt.getString(2); // 獲得第二個參數，因為第二個參數是輸出類型的
  System.out.println("Name = " + name);
  } catch (Exception e) {
  System.out.println(e.toString());
  } finally {
  try {
    stmt.close();
    connection.close();
  } catch (Exception ex) {
    System.out.println(ex.getMessage());
  }
  }
 }

}

--程式執行結果--
Data from table:
ID = 1 Name = zhang
ID = 2 Name = zeng
ID = 3 Name = ming

Data from procedure:
Name = ming

三、總結

本文中給出最基本的MySQL預存程序的建立以及在Java程式的調用的樣本，目的是建立讀者對MySQL預存程序以及在程式中使用的大概瞭解和入門，希望可以對您有所協助。讀者可以參考其他的專業書籍對MySQL預存程序進行深入的探討，本人也會在後續的文章中發表這方面的最新學習成果和大家討論。

本文永久更新連結地址：