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();

        }

    }

 

You May Also Like