mysql学习笔记（九）函数和存储过程

一.函数和存储过程

函数和存储过程本质上没有区别，都是先定义一些sql语句将其存储起来，然后在外部对其进行调用。

区别: 函数必须有1个返回值（因为函数名称定义时候returns关键字），存储过程可以不需要返回值，也可以返回多个值。类似于java中int类型函数只有一个返回值，

void函数不需要返回值，但存储过程是一个特别的“void"，因为它还能进行多个值返回 。存储过程的返回值是通过参数进行返回。

使用：

• 函数：一般情况下是用来计算并返回一个计算结果；
• 存储过程： 一般是用来完成特定的数据操作（比如修改、插入数据库表或执行某些DDL语句等等)

函数和存储过程都是以编译的形式存储在数据库当中，而sql语句会先进行编译，在执行。所以当同样功能的sql和函数，函数的执行效率会比sql语句更快。

 

二.存储过程的操作

上面说到存储过程可以将值进行返回，值返回是通过传入的参数进行返回。

参数类型有in  out  inout三种

1、IN类型

使用IN类型来传递信息，存储过程内部可以对参数的值进行修改，但是修改后的值调用者不可见。

delimiter //
create procedure demo_in(in id int)
begin
    if (id is not null)then set id = id + 1;
    end if;
    select id ;
end //

delimiter ;
set @id=2;
call demo_in(@id);      /** 运行结果id = 3  **/
select @id;                 /** 运行结果id = 2  **/

/** 可以看出虽然设置了变量id的值为2，但存储过程内部修改了id的值为3，id的值并未返回给调用者，说明in类型不进行返回。**/

2、out类型

使用out类型来传递信息，在存储过程内部，该值的默认值为NULL，无论调用者是否传值给存储过程。

delimiter //
create procedure demo_out(out id int)
begin
    if (id is not null)then set id = id + 1;
    end if;
    select id ;
end //

delimiter ;
/**测试 **/
set @id=2;
call demo_out(@id);      /** id的值为NULL **/
select @id;              /** id的值为NULL **/

/** 可以看出虽然设置了变量id的值为2，但是在存储过程内部id的值为null,最后id的值在存储过程内修改后返回调用者。 **/ 

3、inout类型

使用inout类型来传递信息，存储过程内部可以对参数的值进行修改，并将最终值返回给调用者。

delimiter //
create procedure demo_inout(inout id int)
begin
    if (id is not null)then set id = id + 1;
    end if;
    select id ;
end //

delimiter ;
/**测试 **/
set @id=2;
call demo_inout(@id);    /** id的值为3 **/
select @id;              /** id的值为3 **/

/** 以看出设置了变量id的值为2，在存储内部将id的值修改为2，最后id的值返回给调用者。**/ 

4.删除存储过程

drop procudure 名称 ;
/**对上面三个例子删除存储 **/
drop procudure demo_in;
drop procudure demo_out;
drop procudure demo_inout;

5.多个参数传值

delimiter //
create procedure demo_inout(inout id int,inout age int)
begin
    if (id is not null)then set id = id+1 , age=age+2;
    end if;
end //

delimiter ;
/**测试 **/
set @id=2;
set @age=23;
call demo_inout(@id,@age);    
select @id,@age;     /** id=3  age=25 **/       

6.随机生成500数据并将其存储

/**建表 **/
create database if not exists `test` default charset utf8 collate utf8_general_ci;
use test;
drop table if exists `card`;
create table `card` (
   `card_id` bigint(20) not null auto_increment comment 'id',
   `card_number` varchar(100) default null comment '卡号',
   primary key (`card_id`)
 ) engine=myisam auto_increment=0 default charset=utf8 checksum=1 delay_key_write=1 row_format=dynamic

/** 存储 **/
drop procedure if exists proc1;
delimiter //
set autocommit = 0 //
create  procedure proc1()
begin
declare v_cnt decimal (10)  default 0 ;
dd:loop
          insert  into card (card_number) values (uuid());
                  commit;
                    set v_cnt = v_cnt+1 ;
                           if  v_cnt = 500 then leave dd;   /** 500为条数，可修改 **/
                          end if;
         end loop dd ;
end;//
delimiter ;

 

三.函数的操作

函数只会返回一个值，不允许返回一个结果集。函数强调返回值，所以函数不允许返回多个值的情况，即使是查询语句。

一：基本语法

　　delimiter 自定义符号　　-- 如果函数体只有一条语句, begin和end可以省略, 同时delimiter也可以省略

　　create function 函数名(形参列表) returns 返回类型　　-- 注意是retruns

　　begin

　　　　函数体　　　　  -- 函数内定义的变量如：set @x = 1; 变量x为全局变量，在函数外面也可以使用

　　　　返回值

　　end

　　自定义符号

　　delimiter ;

 

二:函数操作

查看函数
　　1. show function status [like 'pattern'];　　-- 查看所有自定义函数, 自定义函数只能在本数据库使用。

　　2. show create function 函数名;　　-- 查看函数创建语句

删除函数

　　drop function 函数名;

  -- 例子 --

delimiter //
create function sum_total(a int ,b int) returns int
begin
    set @sum = 1;
    set @sum =a+b+@sum;
    return @sum;
end//


/**测试 **/

delimiter ;
select sum_total(10,20) as total;      /** 结果输出31 **/
select @sum;                           /** sum是全局变量，直接打印即可,结果为31**/ 

 

delimiter //
create function sum_total(a int ,b int) returns int
begin
    declare sum int default 1;
    set sum =a+b+sum;
    return sum;
end//


/**测试 **/

delimiter ;
select sum_total(10,20) as total;      /** 结果输出31 **/
                                       /**局部变量不能在外部输出**/

 

/**先创建student表**/
delimiter //
create function total() returns int
begin
    declare sum int default 0;
    select count(*) into sum from student;
    return sum;
end//



/**查询student中的个数**/

delimiter ;
select sum_total() as total;