mysql的函数与存储过程

函数

查看函数

show function status;
select 'name' from mysql.proc where db= 'test' and 'type'='function';

mysql函数的创建

基本格式如下
create function 函数名(参数 类型, 参数 类型...)
returns 返回类型
begin
    declare 变量 类型, 变量 类型
    函数体
end
(begin与end之间的为函数体, 语句之间使用分号隔开)

函数的删除

drop function function_name;

函数的创建

以下创建helloworld的函数, 作用仅仅是返回一个字符串

mysql> delimiter #
mysql> create function helloworld()
    -> returns varchar(20)
    -> begin
    -> return "hello world";
    -> end
    -> #
Query OK, 0 rows affected (0.01 sec)

这是输出结果

mysql> select helloworld()#
+--------------+
| helloworld() |
+--------------+
| hello world  |
+--------------+
1 row in set (0.00 sec)

再看看带参数的函数定义, 注意这里参数名的参数类型是相反的, 和一般编程语言的定义不同

mysql> create function sayhello(name varchar(10))
    -> returns varchar(100)
    -> begin
    -> return concat("hello ", name);
    -> end
    -> #
Query OK, 0 rows affected (0.00 sec)

输出结果

mysql> select sayhello("xanarry")#
+---------------------+
| sayhello("xanarry") |
+---------------------+
| hello xanarry       |
+---------------------+
1 row in set (0.00 sec)

定义多个参数的函数, 并在函数中使用定义的变量

下面我定义一个函数, 输入两个数a,b. 返回从a到b的求和结果

mysql> create function mysum(a int, b int) #定义函数名和参数及其类型
    -> returns int                         #定义返回参数类型
    -> begin                               #函数体开始
    -> declare sum int;                    #定义sum为int变量保持求和结果
    -> set sum = 0;                        #初始化sum为0
    -> while a <= b do                     #当a<=b时应该执行循环语句, 注意循环语句有和do
    ->     set sum = sum + a;              #求和
    ->     set a = a + 1;                  #自加
    -> end while;                          #标记循环体结束
    -> return sum;                         #返回sum
    -> end                                 #结束函数定义
    -> #
Query OK, 0 rows affected (0.00 sec)

验证函数的正确性

mysql> select  mysum(1,10)#
+-------------+
| mysum(1,10) |
+-------------+
|          55 |
+-------------+
1 row in set (0.01 sec)

对与mysql函数返回类型有一个需要注意的地方就是函数是不能返回一个table, 只能是一个具体的值, 虽然sql标准是可以返回一个表

创建过程

创建语句的语法

create procedure 过程名(in 参数 类型, out 参数 类型, inout 参数 类型)
begin
    语句集合;
end

下面是一个简单例子, 过程接收两个输入参数, 并接收一个输出参数保存结果

create procedure a(in x int, y int, out sum int)
begin
select x + y into sum;
end

其中对于同一种类的参数, 可以每个参数写一个in, 或者只写一个in, 然后后面跟参数

create procedure a(in x int, in y int, out sum int)
begin
select x + y into sum;
end

mysql> call a(12, 23, @sum_v)

mysql> select @sum_v
+--------+
| @sum_v |
+--------+
|     35 |
+--------+

再来两个复杂点的procedure

输出所有部门的总工资, 然后将指定部门的总工资输出到变量
delimiter //
drop procedure if exists a//
create procedure a(in dept varchar(30), out tsalary int)
begin
select dept_name, sum(salary) from instructor group by dept_name;
select sum(salary) into tsalary from instructor group by dept_name having dept_name = dept;
end
//

下面这个procedure接收输入数据, 然后验证数据的合法性, 如果满足要求则插入, 将状态吸入status变量

drop procedure if exists a//
create procedure a(in name varchar(10), pass varchar(10), addr varchar(30), out status varchar(100))
begin
if name = ""
then
    select "name can not be empty" into status;
elseif char_length(pass) < 6 
then
    select "length of passworld must bigger than 6" into status;
else
    insert into user(name, pass, addr) values(name, pass, addr);
    select "done" into status;
end if;
end
//

过程的删除

drop procedure if exists a

函数与过程的区别

过程可以理解为一组操作的集合, 并且可以在这些操作中获取某些有用的数据

函数只能通过 return 语句返回单个值或表对象；而存储过程不允许执行 return，但可以通过 OUT 参数返回多个值

函数可以嵌入在SQL语句中使用，可以在SELECT语句中作为查询语句的一个部分调用；而存储过程一般是作为一个独立的部分来执行