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)beginselect x + y into sum;end

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

create procedure a(in x int, in y int, out sum int)beginselect x + y into sum;endmysql> 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))beginif 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语句中作为查询语句的一个部分调用；而存储过程一般是作为一个独立的部分来执行