mysql中的函数与存储过程

mysql中的函数：

1 mysql下创建函数:
  1.1 语法：
    delimiter $$    -- 设置分隔符，默认是; 设置成其他符号，让编译器知道我们函数编写的结束,此处设置成$$
    create function 函数名(参数列表) returns 返回值类型
    begin

    函数语句

    end
    $$  -- 表示结束
    delimiter ;     -- 结束符修改成默认

  例如：编写一个返回a+b的函数
    delimiter $$
    create function get_sum(a int , b int ) returns int
    begin
        declare res int default 0;
        set res = a + b;
        return res;
    end
    $$

    delimiter ;

  -- 函数中不支持sql语句，唯一可用放入函数的sql就是 select... into 变量。在函数当中把查询结果赋值给变量。





  1.2 函数语句：
    1.2.1 定义一个变量： declare 变量名 类型 default 默认值
        例如: declare x varchar(100);
    1.2.2 修改变量值： set 变量名 = 值
        例如: set x = "abc";

    1.2.3 判断语法:
        if 条件1 then
        语句1;
        elif 条件2 then
        语句2;
        else
        语句;
        end if

    1.2.4 循环语法:
        while 条件 do
        语句;
        end while;
        -- leave 可以退出循环 相当于break
        -- iterate 结束本次循环开始下一次循环 相当于continue

    1.2.5 实例:一个输出1到1000之间偶数的函数odd
        delimiter $$ -- 设置结束标识符
        create function odd() returns varchar(300)
        begin
            declare i int default 2;
            declare res varchar(300) default "";
            while i <= 100 do
                set res = concat(res ," ", i);
                      -- 拼接字符串
                set i = i + 2;
            end while;
            return res;
        end;
        $$

        delimiter ;





  1.3 用户变量/会话变量:
    只有当前连接用户有效，其他连接用户我发访问。

    -- 使用@表示声明用户变量。 set @变量名 = 值
    set @ age = 20;

    -- 查看当前用户变量。 select @变量名;
    select @age;

    -- 为用户变量赋值。
    select @age := max(age) from students;

  1.4 系统变量:
    任何用户都可以访问。
    使用@@来标识系统变量。
    使用select来查看系统变量

    -- 查看所有的系统变量
    show variables;

    -- 查看系统变量:  select @@变量名;
    select @@max_connections;

    -- 当前用户/会话中修改系统变量: set 变量名 = 值;
    set max_connections = 1000;



2 查看函数：
    所有函数(和存储过程)存储在mysql数据库下的proc表中
    查看python数据库中的函数:
    select db , name , type from mysql.proc where db="python"

3 调用函数：
    select 函数名(传入参数);

    比如 select get_sum(4,6);


4 删除函数:
    drop function 函数名称;
    例如：
        drop function get_sum;








mysql中的存储过程:
    存储过程是多条sql语句的集合，是一种批处理过程。


1 存储过程的创建：
    1.1 语法：
        delimiter //    -- 更换终止分隔符
        create procedure 存储过程名称(参数列表)
        begin
            sql语句;
        end
        //

        delimiter ;     -- 改回分隔符关键字

        -- sql语句结尾的;会让创建存储过程语句结束
        -- 所以我们自己更改一个终止分隔符， 也可以指定其他符号。

    实例：查询学生信息的存储过程
        delimiter //
        create procedure pro_stu()
        begin
            select * from students;
        end
        //

        delimiter ;

    1.2 参数与返回值:存储过程没有返回值，如果需要向外传递数据，可以设置参数。
        参数有三种类型：
            in 向存储过程内部传递数据 默认就是in类型
            out 存储过程向外传递数据  可以代替返回值的功能
            inout 可以双向传递数据

        例如： 查询指定id的学生信息 并且在外部想要获取学生总人数
            delimiter //
            create procedure get_stu( in id int , out num int )
            begin
                select * from students where students.id = id;
                select count(*) from students into num;

            end
            //

            delimiter ;

        之后调用的借助一个用户变量：
            call get_stu(1,@num);
        之后总人数赋值给了@num 用户变量:
            select @num;




2 查看存储过程:
    所有存储过程和函数都放在mysql数据库下的proc表中
    desc mysql.procG

    主要字段：
        name 名称
        type 类型 存储过程或者函数
        body 正文内容
        db 属于那个数据库

    查看python数据库的所有存储过程
    select db , name , type , body from mysql.proc where db = "python" ;

3 调用存储过程:   call 存储过程名(参数列表);
    例如：
        call proc_stu() ;


4 删除存储过程:   drop procedure 存储过程名称;
    例如：
        drop procedure proc_stu;






存储过程和函数的区别

相同点
    存储过程和函数都是为了可重复的执行操作数据库的 sql 语句的集合.
    存储过程和函数都是一次编译,就会被缓存起来,下次使用就直接命中缓存中已经编译好的 sql, 不需要重复编译
    减少网络交互,减少网络访问流量


不同点
    标识符不同,函数的标识符是 function, 过程:procedure
    函数中有返回值,且必须有返回值,而过程没有返回值,但是可以通过设置参数类型(in,out)来实现多个参数或者返回值
    函数使用 select 调用,存储过程需要使用 call 调用
    select 语句可以在存储过中调用,但是除了 select ... into 之外的 select 语句都不能再函数中调用
    通过 in out 参数,过程相关函数更加灵活,可以返回多个结果
    在实际开发中根据个人喜好选择使用函数或者存储过程