mysql預存程序學習

標籤：

一、預存程序的建立

文法：

CREATE PROCEDURE sp_name (參數)
合法的SQL語句

mysql> delimiter // mysql> CREATE PROCEDURE simpleproc (OUT param1 INT)    -> BEGIN    ->   SELECT COUNT(*) INTO param1 FROM example_members;    -> END    -> //Query OK, 0 rows affected (0.00 sec) mysql> delimiter ; mysql> CALL simpleproc(@a);Query OK, 0 rows affected (0.00 sec)

　mysql> select @a//
  +------+
  | @a |
  +------+
  | 8 |
  +------+
  1 row in set (0.01 sec)

 

二、輸入輸出

IN/OUT 變數名 類型

 

CREATE PROCEDURE testproc(IN p_in int)  

CREATE PROCEDURE testproc(OUT p_out int)  

三、變數

變數的定義：DECLARE 變數名 type

　　DECLARE l_int int unsigned default 4000000;  

　　DECLARE l_numeric number(8,2) DEFAULT 9.95;  

　　DECLARE l_date date DEFAULT ‘1999-12-31‘;  

變數的賦值：

　　SET 變數名 = 值

 

四、注釋

單行注釋：-- 

多行注釋：/* */ 

五、預存程序的使用

調用：call procedurename(參數)

查詢：show procedure status where db=‘dbname‘

修改：ALTER PROCEDURE sp_name [characteristic ...]

刪除：DROP PROCEDURE IF EXISTS simpleproc;

六、預存程序控制語句

條件陳述式：

if ,then, else, end if

create procedure test(in a int) if a > 1 then   select 1; elseif a>2 then   select 2; else -- do nothing -- set @tmp=1; end if;  

case, when

SELECT    case gender WHEN 1 THEN ‘male‘ WHEN 0 THEN ‘female‘end  as genderFROMstudent

 

三種迴圈：

while , do , end while

WHILE x  <= 5 DO     SET  str = CONCAT(str,x,‘,‘);     SET  x = x + 1; END WHILE;

repeat end repeat

CREATE PROCEDURE p_repeat() BEGIN     DECLARE v INT;     SET v = 0;     REPEAT         INSERT INTO t VALUES(v);         SET v = v + 1;         UNTIL v>=5     END REPEAT; END;// 

 

loop end loop

CREATE PROCEDURE p_loop() BEGIN     DECLARE v INT;     SET v = 0;     loop_label:LOOP         INSERT INTO t VALUES(v);         SET v = v + 1;         IF v>=5 THEN             LEAVE loop_label; 　　　　 END IF; 　　END LOOP; END;//  

 

 

七、預存程序的常用函數

① 字串類

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個字元,

② 數學類

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

③ 日期時間類

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預存程序學習