萬盛學電腦網

 萬盛學電腦網 >> 數據庫 >> mysql教程 >> MySQL 存儲過程的函數與基本用法

MySQL 存儲過程的函數與基本用法

存儲過程是數據庫系統幾乎都支持提供的一種提升性能方法,下面我來mysql入門者提供一些關於MySQL 存儲過程的函數與基本用法,有需要了解存儲過程的同學可參考本文章。

基本用法

MySQL 存儲過程是從 MySQL 5.0 開始逐漸增加新的功能。存儲過程在實際應用中也是優點大於缺點。不過最主要的還是執行效率和SQL 代碼封裝。特別是 SQL 代碼封裝功能,如果沒有存儲過程。

在外部程序訪問數據庫時(例如 PHP),要組織很多 SQL 語句。

特別是業務邏輯復雜的時候,一大堆的 SQL 和條件夾雜在 PHP 代碼中,讓人不寒而栗。現在有了 MySQL 存儲過程,業務邏輯可以封裝存儲過程中,這樣不僅容易維護,而且執行效率也高。

一、MySQL 創建存儲過程

“pr_add” 是個簡單的 MySQL 存儲過程,這個MySQL 存儲過程有兩個 int 類型的輸入參數 “a”、“b”,返回這兩個參數的和。

 代碼如下 復制代碼

drop procedure if exists pr_add;  


計算兩個數之和

 代碼如下 復制代碼

create procedure pr_add   

(   

a int,   

b int   

)   

begin   

declare c int;   

if a is null then   

set a = 0;   

end if;   

if b is null then   

set b = 0;   

end if;   

set c = a + b;   

select c as sum;   

/*   

return c;  


不能在 MySQL 存儲過程中使用。return 只能出現在函數中。

*/   

end;  


二、調用 MySQL 存儲過程

call pr_add(10, 20);  


執行 MySQL 存儲過程,存儲過程參數為 MySQL 用戶變量。

 代碼如下 復制代碼

set @a = 10;   

set @b = 20;   

call pr_add(@a, @b);  


 

三、MySQL 存儲過程特點

創建 MySQL 存儲過程的簡單語法為:

 代碼如下 復制代碼

create procedure 存儲過程名字()   

(   

[in|out|inout] 參數 datatype   

)   

begin   

MySQL 語句;   

end;  


MySQL 存儲過程參數如果不顯式指定“in”、“out”、“inout”,則默認為“in”。習慣上,對於是“in” 的參數,我們都不會顯式指定。

1. MySQL 存儲過程名字後面的“()”是必須的,即使沒有一個參數,也需要“()”

2. MySQL 存儲過程參數,不能在參數名稱前加“@”,如:“@a int”。下面的創建存儲過程語法在 MySQL 中是錯誤的(在 SQL Server 中是正確的)。 MySQL 存儲過程中的變量,不需要在變量名字前加“@”,雖然 MySQL 客戶端用戶變量要加個“@”。

 代碼如下 復制代碼

create procedure pr_add   

(   

@a int, -- 錯誤   

b int -- 正確   

)  


 

3. MySQL 存儲過程的參數不能指定默認值。

4. MySQL 存儲過程不需要在 procedure body 前面加 “as”。而 SQL Server 存儲過程必須加 “as” 關鍵字。

 代碼如下 復制代碼

create procedure pr_add   

(   

a int,   

b int   

)   

as -- 錯誤,MySQL 不需要 “as”   

begin   

mysql statement ...;   

end;  


5. 如果 MySQL 存儲過程中包含多條 MySQL 語句,則需要 begin end 關鍵字。

 代碼如下 復制代碼

create procedure pr_add   

(   

a int,   

b int   

)   

begin   

mysql statement 1 ...;   

mysql statement 2 ...;   

end;  


6. MySQL 存儲過程中的每條語句的末尾,都要加上分號 “;”

...   

 代碼如下 復制代碼

declare c int;   

if a is null then   

set a = 0;   

end if;   

...   

end;  


 

7. MySQL 存儲過程中的注釋。

 代碼如下 復制代碼

/*   

這是個   

多行 MySQL 注釋。   

*/   

declare c int; -- 這是單行 MySQL 注釋 (注意 -- 後至少要有一個空格)   

if a is null then # 這也是個單行 MySQL 注釋   

set a = 0;   

end if;   

...   

end;  

8. 不能在 MySQL 存儲過程中使用 “return” 關鍵字。

 代碼如下 復制代碼

set c = a + b;   

select c as sum;   

/*   

return c; -- 不能在 MySQL 存儲過程中使用。return 只能出現在函數中。   

*/   

end;  


9. 調用 MySQL 存儲過程時候,需要在過程名字後面加“()”,即使沒有一個參數,也需要“()”

 代碼如下 復制代碼

call pr_no_param();  


10. 因為 MySQL 存儲過程參數沒有默認值,所以在調用 MySQL 存儲過程時候,不能省略參數。可以用 null 來替代。

mysql存儲過程基本函數

一.字符串類

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

 代碼如下 復制代碼

mysql> select substring('abcd',0,2);
+-----------------------+
| substring('abcd',0,2) |
+-----------------------+
|                       |
+-----------------------+
1 row in set (0.00 sec)
 


mysql> select substring('abcd',1,2);
+-----------------------+
| substring('abcd',1,2) |
+-----------------------+
| ab                    |
+-----------------------+
1 row in set (0.02 sec)

TRIM([[BOTH|LEADING|TRAILING] [padding] FROM]string2) //去除指定位置的指定字符
UCASE (string2 ) //轉換成大寫
RIGHT(string2,length) //取string2最後length個字符
SPACE(count) //生成count個空格

二.數學類

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為小數位數]

注:返回類型並非均為整數,如:
(1)默認變為整形值

 代碼如下 復制代碼

mysql> select round(1.23);
+-------------+
| round(1.23) |
+-------------+
|           1 |
+-------------+
1 row in set (0.00 sec)

mysql> select round(1.56);
+-------------+
| round(1.56) |
+-------------+
|           2 |
+-------------+
1 row in set (0.00 sec)

(2)可以設定小數位數,返回浮點型數據

 代碼如下 復制代碼

mysql> select round(1.567,2);
+----------------+
| round(1.567,2) |
+----------------+
|           1.57 |
+----------------+
1 row in set (0.00 sec)

SIGN (number2 ) //返回符號,正負或0
SQRT(number2) //開平方

 


三.日期時間類
 

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) //分

mysql存儲過程總結

核心提示: 一.創建存儲過程1.基本語法:

 代碼如下 復制代碼 create procedure sp_name()
begin

.........
end2.參數傳遞二.調用存儲過程1.基本語法:call sp_name()
注意:存儲過程名稱後面必須加括號,哪怕該

 一.創建存儲過程

1.基本語法:

 代碼如下 復制代碼 create procedure sp_name()
begin
.........
end

2.參數傳遞
二.調用存儲過程

1.基本語法:call sp_name()
注意:存儲過程名稱後面必須加括號,哪怕該存儲過程沒有參數傳遞
三.刪除存儲過程

1.基本語法:

 代碼如下 復制代碼 drop procedure sp_name//

2.注意事項
(1)不能在一個存儲過程中刪除另一個存儲過程,只能調用另一個存儲過程
四.區塊,條件,循環


1.區塊定義,常用
begin
......
end;
也可以給區塊起別名,如:
lable:begin
...........
end lable;
可以用leave lable;跳出區塊,執行區塊以後的代碼
2.條件語句

 代碼如下 復制代碼 if 條件 then
statement
else
statement
end if;

3.循環語句
(1).while循環

 代碼如下 復制代碼

[label:] WHILE expression DO

statements

END WHILE [label] ;

(2).loop循環

[label:]

 代碼如下 復制代碼

LOOP

statements

END LOOP [label];(3).repeat until循環

[label:] REPEAT

statements

UNTIL expression

END REPEAT [label] ;

五.其他常用命令

1.show procedure status
顯示數據庫中所有存儲的存儲過程基本信息,包括所屬數據庫,存儲過程名稱,創建時間等
2.show create procedure sp_name
顯示某一個存儲過程的詳細信息

copyright © 萬盛學電腦網 all rights reserved