mysql 自訂函數

標籤：

文法：

建立：

Create function function_name(參數列表)returns傳回值類型

函數體

函數名，應該合法的標識符，並且不應該與已有的關鍵字衝突。

一個函數應該屬於某個資料庫，可以使用db_name.funciton_name的形式執行當前函數所屬資料庫，否則為當前資料庫。

參數部分，由參數名和參數類型組成。

傳回值類類型

函數體由多條可用的mysql語句，流程式控制制，變數聲明等語句構成。

多條語句應該使用begin end語句塊包含。

注意，一定要有return傳回值語句。

刪除：

Dropfunction if existsfunction_name;

查看：

Show function status like ‘partten’

Show create functionfunction_name;

修改：

Alter functionfunction_name函數選項。

例子：

Hello world!

IF語句

IF search_conditionTHEN

statement_list

[ELSEIF search_conditionTHENstatement_list]

...

[ELSE statement_list]ENDIF;

CASE語句

CASE case_value

WHEN when_valueTHENstatement_list

[WHEN when_value THENstatement_list]

...

[ELSE statement_list]

END CASE; 

迴圈：

While

[begin_label:]WHILEsearch_conditionDO

statement_list

END WHILE [end_label];

如果需要在迴圈內提前終止 while迴圈，則需要使用標籤；標籤需要成對出現。

退出迴圈

退出整個迴圈leave 相當於break

退出當前迴圈iterate 相當於 continue

通過退出的標籤決定退出哪個迴圈。

變數聲明：
文法：
DECLARE var_name[,...] type [DEFAULT value]
這個語句被用來聲明局部變數。要給變數提供一個預設值，請包含一個DEFAULT子句。值可以被指定為一個運算式，不需要為一個常數。如果沒有DEFAULT子句，初始值為NULL。
使用
語序使用 set 和 select into語句為變數賦值。

注意在函數內是可以使用全域變數（使用者自訂的變數的）@XXX 全域變數不用聲明 可以直接@XXX使用。

例子：擷取當前班級內，最大的學號。 

參考學生表 

create table join_student( 

stu_id int not null auto_increment, 

stu_no char(10), 

class_id int not null, 

stu_name varchar(10), 

stu_info text, 

primary key (stu_id) 

); 

計算新增學號 

drop function if existssno;

 delimiter $$ #在包含有語句塊時 可以更換語句結束符“；” 為“$$” 

create function sno(c_id int)returns char(10) 

begin 

declare last_no char(10); #聲明一個局部變數 用來儲存當前最大的學號， 如果沒有就為null

declare class_name char(10); 

select stu_no from join_student where class_id=c_id order by stu_no desc limit 1 into last_no; 

if last_no is null then #如果為空白代表當前班級沒有學生 從1開始，獲得班級名字 

return concat ((select c_name from join_class where id=c_id into class_name),‘001‘); #concat() 函數的作用是連接字串。 

else 

return concat(left(last_no,7),lpad(right(last_no,3) + 1, 3, ‘0‘)); 

end if;

 #return @last_no; 

end 

$$

delimiter ; 

隨機獲得學生名字。 

drop function if exists sname; 

delimiter $$ 

create function sname() returns char(2) 

begin 

declare first_name char(16) default ‘趙錢孫李周吳鄭王馮陳褚衛蔣沈韓楊‘; 

declare last_name char(10) default ‘甲乙丙丁戊己庚辛壬癸‘; 

declare full_name char(2); 

set full_name=concat(substring(first_name,floor(rand()*16+1), 1), substring(last_name,floor(rand()*10+1), 1)); 

return full_name; 

end 

$$ 

delimiter ;

========================================================================================

mysql常用內建函數

數值函數

Abs(X)，絕對值abs(-10.9) = 10

Format(X，D)，格式化千分位元值format(1234567.456, 2) =1,234,567.46

Ceil(X),向上取整ceil(10.1) = 11

Floor(X),向下取整floor (10.1) = 10

Round(X),四捨五入去整

Mod(M,N) M%N M MOD N 求餘 10%3=1

Pi(),獲得圓周率

Pow(M,N) M^N

Sqrt(X)，算術平方根

Rand(),隨機數

TRUNCATE(X,D) 截取D位小數

時間日期函數

Now(),current_timestamp(); 當前日期時間

Current_date();當前日期

current_time();目前時間

Date(‘yyyy-mm-dd HH;ii:ss’);擷取日期部分

Time(‘yyyy-mm-dd HH;ii:ss’);擷取時間部分

Date_format(‘yyyy-mm-dd HH;ii:ss’,’%D %y %a %d %m %b %j‘);

Unix_timestamp();獲得unix時間戳記

From_unixtime();//從時間戳記獲得時間

字串函數

LENGTH(string ) //string長度，位元組

CHAR_LENGTH(string) //string的字元個數

SUBSTRING(str ,position [,length ]) //從str的position開始,取length個字元

REPLACE(str ,search_str ,replace_str) //在str中用replace_str替換search_str

INSTR(string ,substring ) //返回substring首次在string中出現的位置

CONCAT(string [,... ]) //串連字串

CHARSET(str) //返回字串字元集

LCASE(string ) //轉換成小寫

LEFT(string ,length ) //從string2中的左邊起取length個字元

LOAD_FILE(file_name) //從檔案讀取內容

LOCATE(substring , string [,start_position ]) //同INSTR,但可指定開始位置

LPAD(string ,length ,pad ) //重複用pad加在string開頭,直到字串長度為length

LTRIM(string ) //去除前端空格

REPEAT(string ,count ) //重複count次

RPAD(string ,length ,pad) //在str後用pad補充,直到長度為length

RTRIM(string ) //去除後端空格

STRCMP(string1 ,string2 ) //逐字元比較兩字串大小

流程函數：

CASE WHEN [condition]THEN result[WHEN [condition]THEN result ...][ELSE result]END 多分支

IF(expr1,expr2,expr3) 雙分支。

彙總函式

Count()

Sum();

Max();

Min();

Avg();

Group_concat()

其他常用函數

Md5();

Default();

mysql 自訂函數