mysql存儲過程procedure

jopen 11年前發布 | 60K 次閱讀 MySQL 數據庫服務器

存儲過程的概念：

所謂的存儲過程就是存儲在數據庫當中的可以執行特定工作(查詢和更新)的一組SQL代碼的程序段。

2、存儲過程的優點：
（1）存儲過程只在創造時進行編譯，以后每次執行存儲過程都不需再重新編譯，而一般SQL語句每執行一次就編譯一次,所以使用存儲過程可提高數據庫執行速度。
（2）當對數據庫進行復雜操作時（如對多個表進行Update、Insert、Query、Delete時），可將此復雜操作用存儲過程封裝起來與數據庫提供的事務處理結合一起使用。
（3）存儲過程可以重復使用，可減少數據庫開發人員的工作量。
（4）安全性高，可設定只有某些用戶才具有對指定存儲過程的使用權。

3、關于MySQL的存儲過程

存儲過程是數據庫存儲的一個重要的功能，但是MySQL在5.0以前并不支持存儲過程，這使得MySQL在應用上大打折扣。好在MySQL 5.0終于開始已經支持存儲過程，這樣即可以大大提高數據庫的處理速度，同時也可以提高數據庫編程的靈活性。

4、mysql存儲過程的創建：

格式：

CREATE PROCEDURE sp_name ([proc_parameter[,...]])    
        [characteristic ...] routine_body

（1）默認地，子程序與當前數據庫關聯。要明確地把子程序與一個給定數據庫關聯起來，可以在創建子程序的時候指定其名字為db_name.sp_name。

（2）參數解釋：

sp_name 存儲過程的名字
proc_parameter指定參數為IN， OUT，或INOUT
characteristic 特征
routine_body 包含合法的SQL過程語句。

（3）簡單的例子：

mysql> DELIMITER //  
mysql> CREATE PROCEDURE proc1(OUT s int)  
    -> BEGIN 
    -> SELECT COUNT(*) INTO s FROM user;  
    -> END 
    -> //  
mysql> DELIMITER ;

注：

1）這里需要注意的是DELIMITER //和DELIMITER ;兩句，DELIMITER是分割符的意思，因為MySQL默認以";"為分隔符，如果我們沒有聲明分割符，那么編譯器會把存儲過程當成SQL語句進行處理，則存儲過程的編譯過程會報錯，所以要事先用DELIMITER關鍵字申明當前段分隔符，這樣MySQL才會將";"當做存儲過程中的代碼，不會執行這些代碼，用完了之后要把分隔符還原。

2）存儲過程根據需要可能會有輸入、輸出、輸入輸出參數，這里有一個輸出參數s，類型是int型，如果有多個參數用","分割開。

3）過程體的開始與結束使用BEGIN與END進行標識。

4、列出所有的存儲過程

SHOW  PROCEDURE  STATUS;

5、調用存儲過程：

mysql > SET @p_out=0;  
mysql > CALL proc1(@p_out); 
mysql> SELECT @p_out;

查看變量p_out的前后結果：

+-------+  
| p_out |   
+-------+  
| 0     |   
+-------+  

+-------+  
| p_out |  
+-------+  
|   2   |   
+-------+

6、 刪除存儲過程：

DROP PROCEDURE  IF  EXISTS proc1

7、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查詢user信息)

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

        }

    }

8、MySQL存儲過程的基本函數：

(1).字符串類

CHARSET(str) //返回字串字符集
CONCAT (string2 [,... ]) //連接字串
INSTR (string ,substring ) //返回substring首次在string中出現的位置,不存在返回0
LCASE (string2 ) //轉換成小寫
LEFT (string2 ,length ) //從string2中的左邊起取length個字符
LENGTH (string ) //string長度
LOAD_FILE (file_name ) //從文件讀取內容
LOCATE (substring , string [,start_position ] ) 同INSTR,但可指定開始位置
LPAD (string2 ,length ,pad ) //重復用pad加在string開頭,直到字串長度為length
LTRIM (string2 ) //去除前端空格
REPEAT (string2 ,count ) //重復count次
REPLACE (str ,search_str ,replace_str ) //在str中用replace_str替換search_str
RPAD (string2 ,length ,pad) //在str后用pad補充,直到長度為length
RTRIM (string2 ) //去除后端空格
STRCMP (string1 ,string2 ) //逐字符比較兩字串大小,
SUBSTRING (str , position [,length ]) //從str的position開始,取length個字符,
注：mysql中處理字符串時，默認第一個字符下標為1，即參數position必須大于等于1

(2).數學類

ABS (number2 ) //絕對值
BIN (decimal_number ) //十進制轉二進制
CEILING (number2 ) //向上取整
CONV(number2,from_base,to_base) //進制轉換
FLOOR (number2 ) //向下取整
FORMAT (number,decimal_places ) //保留小數位數
HEX (DecimalNumber ) //轉十六進制
注：HEX()中可傳入字符串，則返回其ASC-11碼，如HEX('DEF')返回4142143
也可以傳入十進制整數，返回其十六進制編碼，如HEX(25)返回19
LEAST (number , number2 [,..]) //求最小值
MOD (numerator ,denominator ) //求余
POWER (number ,power ) //求指數
RAND([seed]) //隨機數
ROUND (number [,decimals ]) //四舍五入,decimals為小數位數]

(3).日期時間類

ADDTIME (date2 ,time_interval ) // 將 time_interval 加到 date2
CONVERT_TZ (datetime2 ,fromTZ ,toTZ ) // 轉換時區
CURRENT_DATE ( ) // 當前日期
CURRENT_TIME ( ) // 當前時間
CURRENT_TIMESTAMP ( ) // 當前時間戳
DATE (datetime ) // 返回 datetime 的日期部分
DATE_ADD (date2 , INTERVAL d_value d_type ) // 在 date2 中加上日期或時間
DATE_FORMAT (datetime ,FormatCodes ) // 使用 formatcodes 格式顯示 datetime
DATE_SUB (date2 , INTERVAL d_value d_type ) // 在 date2 上減去一個時間
DATEDIFF (date1 ,date2 ) // 兩個日期差
DAY (date ) // 返回日期的天
DAYNAME (date ) // 英文星期
DAYOFWEEK (date ) // 星期 (1-7) ,1 為星期天
DAYOFYEAR (date ) // 一年中的第幾天
EXTRACT (interval_name FROM date ) // 從 date 中提取日期的指定部分
MAKEDATE (year ,day ) // 給出年及年中的第幾天 , 生成日期串
MAKETIME (hour ,minute ,second ) // 生成時間串
MONTHNAME (date ) // 英文月份名
NOW ( ) // 當前時間
SEC_TO_TIME (seconds ) // 秒數轉成時間
STR_TO_DATE (string ,format ) // 字串轉成時間 , 以 format 格式顯示
TIMEDIFF (datetime1 ,datetime2 ) // 兩個時間差
TIME_TO_SEC (time ) // 時間轉秒數 ]
WEEK (date_time [,start_of_week ]) // 第幾周
YEAR (datetime ) // 年份
DAYOFMONTH(datetime) // 月的第幾天
HOUR(datetime) // 小時
LAST_DAY(date) //date 的月的最后日期
MICROSECOND(datetime) // 微秒
MONTH(datetime) // 月
MINUTE(datetime) // 分返回符號 , 正負或 0
SQRT(number2) //開平方

參照：http://my.oschina.net/u/195896/blog/75310

http://www.blogjava.net/sxyx2008/archive/2009/11/24/303497.html

http://pcwanli.blog.163.com/blog/static/4531561120123243279258/

http://wenku.baidu.com/view/d41d44bec77da26925c5b0de.html

本文由用戶 jopen 自行上傳分享，僅供網友學習交流。所有權歸原作者，若您的權利被侵害，請聯系管理員。

轉載本站原創文章，請注明出處，并保留原始鏈接、圖片水印。

本站是一個以用戶分享為主的開源技術平臺，歡迎各類分享！

本文地址：http://www.baiduhome.net/lib/view/open1364095739843.html

MySQL 數據庫服務器

mysql存儲過程procedure

SHOW PROCEDURE STATUS;
5、調用存儲過程：

相關經驗

相關資訊

相關文檔

目錄

mysql存儲過程procedure

SHOW PROCEDURE STATUS;5、調用存儲過程：

相關經驗

相關資訊

相關文檔

目錄

SHOW PROCEDURE STATUS;
5、調用存儲過程：