Mysql中自定義函數編程，mysql自定義函數

Mysql中自定義函數編程，mysql自定義函數

1.語法

1.1 新建函數

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

函數體

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

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

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

(4)返回值類類型。

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

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

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

1.2 刪除

Dropfunction if exists function_name;

1.3 查看

Show function status like ‘partten’
Show create function function_name;

1.4 修改

Alter function function_name函數選項;

2. 程序控制

2.1 IF語句

IF search_condition THEN
statement_list
[ELSEIF search_condition THEN statement_list]
...
[ELSE statement_list]END IF;

2.2 CASE語句

CASE case_value
WHEN when_value THEN statement_list
[WHEN when_value THEN statement_list]
...
[ELSE statement_list]
END CASE; 

2.3 While循環

[begin_label:]WHILE search_condition DO
　　statement_list
END WHILE [end_label];

　　如果需要在循環內提前終止while循環，則需要使用標簽；標簽需要成對出現。

2.4 退出循環

(1)退出整個循環：leave 相當於break

(2)退出當前循環：iterate 相當於 continue

(3)通過退出的標簽決定退出哪個循環。

3. 變量聲明

3.1 語法

DECLARE var_name[,...] type [DEFAULT value]

　　這個語句被用來聲明局部變量。要給變量提供一個默認值，請包含一個DEFAULT子句。值可以被指定為一個表達式，不需要為一個常數。如果沒有DEFAULT子句，初始值為NULL。

3.2 賦值

　　使用語序使用 set 和 select into語句為變量賦值。

3.3 注意　　

(1)在函數內是可以使用全局變量（用戶自定義的變量的）

(2)@XXX 全局變量不用聲明 可以直接@XXX使用。

4.例子

　獲取當前班級內，最大的學號。

(1)參考學生表 

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

(2)計算新增學號 

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 ; 

(3)隨機獲得學生名字。 

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 ;

5.mysql常用內置函數

5.1 數值函數

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

5.2 時間日期函數

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();　　　　　　　　　　　　//從時間戳獲得時間

5.3 字符串函數

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 ) 　　　　　　//逐字符比較兩字串大小

5.4 流程函數

CASE WHEN [condition]THEN result[WHEN [condition]THEN result ...][ELSE result]END 多分支
IF(expr1,expr2,expr3) 雙分支。

5.5 聚合函數

Count()
Sum();
Max();
Min();
Avg();
Group_concat()

5.6 其他常用函數

Md5();
Default();