MySQL 存儲過程

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

存儲過程是通過給定的語法格式編寫自定義的數據庫API，類似于給數據庫編寫可執行函數。

</blockquote>

簡介

存儲過程是一組為了完成特定功能的SQL語句集合，是經過編譯后存儲在數據庫中。

存儲過程增強了SQL語言的功能和靈活性，它可以使用流控制語句編寫來完成復雜的判斷和計算。

存儲過程是把完成特定功能的SQL語句集合統一在數據庫中進行處理，避免了多次網絡IO請求造成的網絡負載。 

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

基本語法

基本結構 

DELIMITER // DROP PROCEDURE IF EXISTS some_func; CREATE PROCEDURE some_func
( IN param1 INT, IN param2 VARCHAR(32),
    OUT res INT ) BEGIN SQL-SCRIPT END //
DELIMITER;

調用語句如下， 

CALL some_func(params...);

變量

在存儲過程中，函數參數包含三種變量，

  • IN 輸入參數，必須在調用存儲過程時指定

  • OUT 輸出參數，可在存儲過程內部被改變，返回該結果

  • INOUT 輸入輸出參數，調用時指定并且可被改變和返回
    • </ul>

    在存儲過程內部，參數的定義如下， 

    DECLARE var_name [, var_name] var_type [default value];

    用戶變量 

    SET @mvar = 'Hello World';

    變量賦值 

    SET var_name = 表達式;

    條件語句 

    if var=0 then insert into person values ('f'); else insert into person values ('ff'); end if;

    case語句 

    case var
when 0 then insert into person values ('f'); when 1 then insert into person values ('ff'); else insert into person values ('fff'); end case;

    循環語句

    WHILE語句，同C語言中的while語句一樣。 

    while var < N do insert into person values ('f'); set var = var + 1; end while;

    REPEAT語句，類似于C語言中的do while語句。 

    repeat insert into person values ('f'); set var = var + 1; until var >= N end repeat;

    LOOP語句，沒有結束的判斷語句，利用leave來跳出循環，類似于break。 

    set @var = 0; loop_name:loop set @var = @var + 1; if @var > 5 then
    leave loop_name; end if; end loop loop_name; select @var;

    基本函數

    mysql內置了一些函數，這些函數可以極大地提高編寫存儲過程的效率。

    字符串操作如下， 

    CHARSET(str) //獲取字符集
CONCAT(str1, str2, ...) //聯接字符串
INSTR(str, substr) //返回substr出現在str中的第一個位置
LOCATE(substr, str, start_position) //返回substr在str的start_position開始第一次出現的位置
LCASE(str) //將所有字符轉換為小寫
LEFT(str, length) //返回str從左邊開始的length個字符
LENGTH(str) //返回str長度
LOAD_FILE(file_name) //讀取文見內容
LPAD(str, length, pad) //重復在str的首部插入pad，直到str的長度達到length
LTRIM(str) //去除str首部的空格
RTRIM(str) //去除str尾部的空格
STRCMP(str1, str2) //字符串比較
SUBSTRING(str, start_position, length) //截取字符串，默認第一個字符下標為1

    math相關操作如下， 

    ABS(i) //絕對值
BIN(i) //十進制->二進制
CEILING(i) //向上取整
CONV(i, from, to) //進制轉換
FLOOR(i) //向下取整
FORMAT(i, n) //保留小數位數
HEX(i) //轉十六進制
LEAST(i0, i1, i2, ..) //求最小值
MOD(i, demoninator) //求余
POWER(I, POWER) //求指數
RAND([seed]) //隨機數
ROUND(I, [, DECIMALS]) //四舍五入，decimals為保留小數位數
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) //分返回符號,正負或0

    參考

    http://xdj651897373-126-com.iteye.com/blog/1819924
    http://blog.tankywoo.com/2015/04/01/mysql-stored-procedure.html
    http://stackoverflow.com/questions/8549619/mysql-dynamically-build-query-string-in-a-stored-procedure-based-on-logic

 本文由用戶 jopen 自行上傳分享，僅供網友學習交流。所有權歸原作者，若您的權利被侵害，請聯系管理員。
 轉載本站原創文章，請注明出處，并保留原始鏈接、圖片水印。
 本站是一個以用戶分享為主的開源技術平臺，歡迎各類分享！