mysql中自訂函數編程，mysql自訂函數

mysql中自訂函數編程，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自訂函數怎寫？

mysql> CREATE FUNCTION HelloWorld4()
-> RETURNS VARCHAR(20)
-> BEGIN
-> RETURN 'Hello World!';
-> END;
-> //
Query OK, 0 rows affected (0.00 sec)

mysql> select HelloWorld4() //
+---------------+
| HelloWorld4() |
+---------------+
| Hello World! |
+---------------+
1 row in set (0.00 sec)

如果你會其它資料庫的話， 那麼看看那個 SQL 預存程序編寫 參考手冊 對你會有些協助。

pan.baidu.com/...715080
 
MySQL怎測試自訂函數

不知道你的表的具體情況，我舉例說明哈：
--建立測試表
create table tb12
(id int identity(1,1) not null,
zl int null,
yf decimal(8,2) null
)
go
--添加測試資料
insert into tb12
select 80,null
union all
select 105,null
union all
select 5000,null
union all
select 6000,null
go

select * from tb12
----------------------------------
id zl yf
----------- ----------- ----------
1 80 NULL
2 105 NULL
3 5000 NULL
4 6000 NULL

（所影響的行數為 4 行）

--drop FUNCTION funShipFee
--建立計算郵費的內嵌資料表值函式funShipFee
CREATE FUNCTION funShipFee()
RETURNS TABLE
AS
RETURN (SELECT id,zl,yf=
case when zl<=100 then zl*0.3
when zl between 101 and 5000 then zl*0.15
else zl*0.12
end
FROM tb12)
--調用funShipFee將返回結果集如下：
SELECT *
FROM funShipFee()
-------------------------------------------
id zl yf
----------- ----------- ---------------
1 80 24.00
2 105 15.75
3 5000 750.00
4 6000 720.00

（所影響的行數為 4 行）