mysql 查看存储过程代码 mysql 查看存储过程定义

转载

数据科学家 2023-06-20 09:19:50

文章标签 mysql 查看存储过程代码存储过程 sql SQL 文章分类 MySQL 数据库

1.概念

存储过程（Stored Procedure）是一种在数据库中存储复杂程序，以便外部程序调用的一种数据库对象，为了完成特定功能的SQL语句集，经编译创建并保存在数据库中，用户可通过指定存储过程的名字并给定参数(需要时)来调用执行，思想上很简单，就是数据库 SQL 语言层面的代码封装与重用

存储过程和函数可以理解为一段SQL语句的集合，它们被事先编译好并且存储在数据库中。在 Pascal 语言中，是有”过程”和”函数”的区分的，过程可以理解为

没有返回值的函数。不过在 C 家族语言中，则没有过程这个概念，统一为函数。

--创建一个存储过程的语法为：
create procedure 存储过程名(参数列表) 
begin 存储过程体 
end
call 存储过程名(参数列表)

--如下简单存储过程
DROP PROCEDURE user_procedure; 
create PROCEDURE user_procedure(in x int) -- in 表示输入 
BEGIN 
select * from `user` where id = x; 
END 
-- 执行 
call user_procedure(1);

参数类型：从上面的过程中我们了解到存储过程有参数类型这种说法，

它的类型可以取值有三个：

in 表示只是用来输入。
out 表示只是用来输出。
inout 可以用来输入，也可以用作输出。

DROP PROCEDURE user_procedure_out; 
　　create PROCEDURE user_procedure_out(in x int, out y varchar(10)) 
BEGIN 
　　select user_name into y from `user` where id = x; 
END
-- 执行 
call user_procedure_out(1, @a); 
select @a

2.存储过程理解

调用存储过程与直接执行 SQL 语句的效果是相同的，但是存储过程的一个好处是处理逻辑都封装在数据库端。
当我们调用存储过程的时候，我们不需要了解其中的处理逻辑，一旦处理逻辑发生变化，只需要修改存储过程即可，对调用它的程序完全无影响。
调用存储过程和函数可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，可以提高数据处理的效率。

show create PROCEDURE user_procedure_out;

3.变量

存储过程中是可以使用变量的，我们可以通过 declare 来定义一个局部变量，该变量的作用域只是 begin....end 块中。
变量的定义必须写在符合语句的开头，并且在任何其他语句的前面，我们可以一次声明多个相同类型的变量，我们还可以使用default来赋予默认值。
定义一个变量的语法为： declare 变量名 1 [,变量名 2...] 变量类型 [default 默认值]。
上面的变量类型就是 MySQL 支持的类型，而变量名的取值规则也是一个老生常谈的话题了，就不赘述了。
变量可以直接赋值，还可以通过查询赋值。
直接赋值就是使用 set 来进行赋值，它的语法为： set 变量名 1 = 表达式 1 [,变量名 2=表达式 2...]。
也可以通过查询来将结果赋值给变量，它需要要求查询返回的结果只有一行。

--语法范例： 
select 列名列表 into 变量列表 from 表名
--实例
DROP PROCEDURE user_procedure_2; 
create PROCEDURE user_procedure_2(in x int, out y varchar(10)) 
BEGIN 
    declare s varchar(10) ; 
    select user_name into s from `user` where id = x; 
    set y = s; 
END 
-- 执行
call user_procedure_2(1, @a); 
select @a

4.存储过程中的数据类型

数值类型：Int,float,double,decimal
日期类型：timestamp,date,year
字符串：char,varchar,text

timestamp: 是使用最多的数据类型-》十位数的时间戳 text：一旦用到text类型的时候就可以考虑分表；如果部分表的话，该字段的查询不会直接放在一起查

询，因为多个字段查询中其中如果有text字段的话，就容易遇到慢查询，所以通常的话，如果需要这个值的时候会根据id单独拿这个text字段

5.流程控制语句其他语法

--if 的语法格式为：
if 条件表达式 then 语句
    [elseif 条件表达式 then 语句] ....
    [else 语句]
end if

--case 的语法格式
--首先是第一种写法：
case 表达式
    when 值 then 语句
    when 值 then 语句
    ...
    [else 语句]
end case
--然后是第二种写法：
case
    when 表达式 then 语句
    when 表达式 then 语句
    ....
    [else 语句]
end case

--loop 循环 语法格式为：
[标号:] loop
    循环语句
end loop [标号]

--leave 语句用来从标注的流程构造中退出，它通常和 begin...end 或循环一起使用
leave 标号;

--声明语句结束符，可以自定义:
DELIMITER [符合]
delimiter $$
$$

6.游标

游标也有的资料上称为光标。
我们可以在存储过程中使用游标来对结果集进行循环的处理。
游标的使用步骤基本分为：声明、打开、取值、关闭。

--语法：
DECLARE test_cursor CURSOR FOR 结果集;
--声明游标
OPEN test_cursor;
--打开游标 
CLOSE test_cursor;
--关闭游标 
DECLARE CONTINUE HANDLER FOR NOT FOUND
--结果集查询不到数据自动跳出

总结：

游标的声明的语法： declare 游标名称 cursor for 查询语句;
打开光标的语法： open 游标名称;
获取游标数据： fetch 游标名称 into 变量名 1 [,变量名 2 ....]
关闭游标的语法： close 游标名称;
游标的基本使用须知：对某个表按照循环的处理，判断循环结束的条件是捕获 not found 的条件，当 fetch 光标找不到下一条记录的时候，就会关闭光标然后退出过程。
可能有过 Pascal 编程经验的朋友们都会知道，声明的顺序也是很重要的，在 SQL 中，我们使用 declare 定义的顺序是：变量、条件、游标、应用程序

操作查询出来的数据会放置于临时表中，然后再通过游标去读取数据。

--案例
delimiter $$ 
create procedure exchange(out count int ) 
begin 
    declare supply_id1 int default 0;
    declare amount1 int default 0; 
    -- 游标标识 
    declare blag int default 1; 
    -- 游标 
    declare order_cursor cursor for select supply_id,amount from order_group; 
    -- not found 这个异常进行处理 
    declare continue handler for not found set blag = 0; 
    set count = 0; 
    -- 打开游标 
    open order_cursor;
    -- 遍历 
    read_loop: LOOP 
        fetch order_cursor into supply_id1,amount1; 
        if blag = 0 then 
            leave read_loop; 
        end if;
        if supply_id1 = 1 then 
            set count = count + amount1; 
        end if; 
    end loop read_loop; 
end; 
$$
delimiter ; 
call exchange(@count); 
select @count;

7.存储过程的优缺点

存储过程优点：

第一点优势就是执行速度快。因为我们的每个 SQL 语句都需要经过编译，然后再运行，但是存储过程都是直接编译好了之后，直接运行即可。
第二点优势就是减少网络流量。我们传输一个存储过程比我们传输大量的SQL语句的开销要小得多。
第三点优势就是提高系统安全性。因为存储过程可以使用权限控制，而且参数化的存储过程可以有效地防止SQL注入攻击。保证了其安全性。
第四点优势就是耦合性降低。当我们的表结构发生了调整或变动之后，我们可以修改相应的存储过程，我们的应用程序在一定程度，上需要改动的地方就较小了。
第五点优势就是重用性强。因为我们写好一个存储过程之后，再次调用它只需要一个名称即可，也就是”一次编写，随处调用”,而且使用存储过程也可以让程序的模块化加强。

存储过程的缺点：

第一个缺点就是移植性差。因为存储过程是和数据库绑定的，如果我们要更换数据库之类的操作，可能很多地方需要改动。
第二个缺点就是修改不方便。因为对于存储过程而言，我们并不能特别有效的调试，它的一些bug可能发现的更晚一些，增加了应用的危险性。
第三个缺点就是优势不明显和赘余功能。对于小型 web 应用来说，如果我们使用语句缓存，发现编译SQL的开销并不大，但是使用存储过程却需要检查权限一类的开销，这些赘余功能也会在一定程度上拖累性能。

8.php中的应用

require_once 'db.php';
// $sql = 'create procedure login_procedure_4 (in id int, out ret varchar(10))
// begin
//     declare y int default 0;
//     select shop_name into ret from shop where uid = id;
//     set ret = "ttt";
//   if y = 0 then
//     set ret = "xxxxx";
//   end if;
// end';
// $db->execute($sql);
// $sql = 'call login_procedure_3(87, @ret)';
// var_dump($db->call($sql, 'select @ret'));
// echo '成功';

// 游标
// 1. 声明
/*
declare xxx

declare 游标名 cursor for 查询语句 [select xx,xx,x,x from table_name];
open 游标名 -- 打开
fetch 游标名 into xxx,

业务处理

close 游标名
 */
 $sql = '
 create procedure login_procedure_5 (in id int, out ret varchar(10))
 begin
     declare id int default 0;
  -- 游标标识
  declare blag int default 1;
  declare name varchar(10) default 0;
     declare shop_cursor cursor for select shop_name,uid from shop;

  -- 异常处理 :注意游标在读取数据的时候,不断读取
  declare continue handler for not found set blag = 0;

  open shop_cursor; -- 打开

  read_loop:loop
    fetch shop_cursor into name,id;
    if blag = 0 then
      leave read_loop;
    end if;

    if id = 63 then
      set ret = name;
    end if;
  end loop read_loop;
 end';

 // $db->execute($sql);
 $sql1 = 'call login_procedure_5(87, @ret)';
 var_dump($db->call($sql1, 'select @ret'));
 echo '成功';

/**
     * [call description]
     * @param  string $sql 查询的语句
     * @param  string $select_param 参数
     * @return [type]
     */
    public function call($sql, $select_param = null)
    {
        $stmt = $this->pdo->prepare($sql);
        if ($stmt->execute()) {
            if (isset($select_param)) {
                return $this->pdo->query($select_param)->fetchAll();
            } else {
              return $this->pdo->fetchAll();
            }
            return true;
        } else {
            return false;
        }
    }

9.小结：