Mysql存储过程

最新推荐文章于 2022-02-28 15:46:48 发布

Lynn_Meng

最新推荐文章于 2022-02-28 15:46:48 发布

阅读量332

点赞数

分类专栏：数据库文章标签：数据库存储过程 mysql

本文链接：https://blog.csdn.net/meng564764406/article/details/52206917

版权

数据库专栏收录该内容

3 篇文章 0 订阅

订阅专栏

存储过程定义：一段写好的SQL代码，特别的就是它是存在数据库的目录里，外部程序可以直接调用数据库里面定义好的存储过程。

优缺点：

优点：

数据库自己内部执行存储过程效率更高、速度更快
存储过程还减少了应用程序同服务器自己的信息交互频率，原本需要多条sql现在只要一条
存储过程重用性比较高，并且是透明的，因为保存在数据库里面所以对任何应用来说都可以使用
存储过程也是种安全的做法，数据库管理员可以对那些没有权限访问数据库中的表格的应用，给他们使用存储过程的权限来获得数据服务,类似与接口

缺点：

存储过程会使得数据库占用的系统资源加大
因为存储过程依旧是sql，所以没办法像编程语言那样写出复杂业务逻辑对应的存储过程
存储过程不容易进行调试
存储过程书写及维护难度都比较大

存储过程与函数的过程

PL/SQl块

匿名块：不保存在数据库中，没有名称，不可以重复使用

命名块：保存在数据库中，有名称，可以重复使用

命名块：存储过程，函数，触发器，包

存储过程与函数

存储过程没有返回值，可以独立使用

函数必须包含一个返回值，只能作为表达式的一部分使用

创建存储过程

1.使用可视化工具创建，在右侧会出现代码

在bigin和end中间加入我们要执行的语句，

同时这里的语句就是我们在mysql中常见存储过程的代码，主要修改存储过程的名字和语句

DELIMITER $$
CREATE
    /*[DEFINER = { user | CURRENT_USER }]*/
    PROCEDURE `bdycdb`.`sum_user`()
    /*LANGUAGE SQL
    | [NOT] DETERMINISTIC
    | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
    | SQL SECURITY { DEFINER | INVOKER }
    | COMMENT 'string'*/
    BEGIN
        SELECT COUNT(*) FROM t_user;
    END$$
DELIMITER ;

使用Execute All Queries 进行运行，注意这里的代码和我们执行查询等操作的窗口是不一致的。

执行完成后，我们在存储过程下会看到刚才我们写的存储过程sum_user

这样使用可视化工具的创建结束。

2.使用代码创建mysql的存储过程

创建一个存储过程hi（）执行语句是select* from t_user

CREATE PROCEDURE hi() SELECT * FROM t_user;

执行完毕就生成了一个存储过程

3、运行sum_user

如果使用可视化的工具创建的存储过程，首先切换到查询窗口
输入我们的执行存储过程的命令
注意执行的函数必须有括弧

CALL sum_user();

4、创建一个有参的存储过程

⑴MySQL 创建存储过程 
“pr_add” 是个简单的 MySQL 存储过程，这个存储过程有两个 int 类型的输入参数 “a”、“b”，返回这两个参数的和。 
drop procedure if exists pr_add;
-- 计算两个数之和

create procedure pr_add
(  
 a int,   b int
)
begin   declare c int;
   if a is null then      set a = 0;
   end if;
   if b is null then
      set b = 0;
   end if; 
  set c = a + b;
   select c as sum; 
 end;

⑵调用 MySQL 存储过程

call pr_add(10, 20);

执行 MySQL 存储过程，存储过程参数为 MySQL 用户变量。

set @a = 10;
set @b = 20;
call pr_add(@a, @b);

5、查看当前数据库有什么存储过程

 
 show procedure status where Db='test';--test是数据库

6、在执行创建存储过程的时候经常出现

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 6

这里解释一下原因，我也遇到过这样的的问题，这里给解答一下，引用一下大神的文章

总所周知，mysql的命令行执行每一条命令是以分号结尾的，也就是说识别是否为一条命令，是根据分号决定的。然而存储过程中设计多条语句，很可能出现多个分号，所以直接把存储过程复制到命令号一般都会失败解决方法是需要加一个分隔符，让命令行知道整个存储过程的代码是完整的一块代码,代码如下

DELIMITER //     
CREATE PROCEDURE p_name (IN b INTEGER(12))     
begin
declare a INTEGER(12);     
set a=12;     
INSERT INTO t VALUES (a);     
SELECT s1* a FROM t;     
End
//

如代码中所示，在存储过程的开头加上“DELIMITER //”,结尾加上“//”就ok了

来源： http://blog.csdn.net/cyxlzzs/article/details/7349784

7、三、MySQL 存储过程特点

创建 MySQL 存储过程的简单语法为：

create procedure 存储过程名字()
(
   [in|out|inout] 参数 datatype
)
begin
   MySQL 语句;
end;

MySQL 存储过程参数如果不显式指定“in”、“out”、“inout”，则默认为“in”。习惯上，对于是“in” 的参数，我们都不会显式指定。 
1. MySQL 存储过程名字后面的“()”是必须的，即使没有一个参数，也需要“()” 
2. MySQL 存储过程参数，不能在参数名称前加“@”，如：“@a int”。下面的创建存储过程语法在 MySQL 中是错误的（在 SQL Server 中是正确的）。 MySQL 存储过程中的变量，不需要在变量名字前加“@”，虽然 MySQL 客户端用户变量要加个“@”。

create procedure pr_add
(
   @a int,  -- 错误
   b int    -- 正确
)

3. MySQL 存储过程的参数不能指定默认值。 
4. MySQL 存储过程不需要在 procedure body 前面加 “as”。而 SQL Server 存储过程必须加 “as” 关键字。

create procedure pr_add
(
   a int,
   b int
)
as              -- 错误，MySQL 不需要 “as”
begin
   mysql statement ...;
end;

5. 如果 MySQL 存储过程中包含多条 MySQL 语句，则需要 begin end 关键字。

create procedure pr_add
(
   a int,
   b int
)
begin
   mysql statement 1 ...;
   mysql statement 2 ...;
end;

6. MySQL 存储过程中的每条语句的末尾，都要加上分号 “;”

 
    ...
   declare c int;
   if a is null then
      set a = 0;
   end if;
   ...
end;

7. MySQL 存储过程中的注释。

 
   
   declare c int;     -- 这是单行 MySQL 注释 （注意 -- 后至少要有一个空格）
   if a is null then  # 这也是个单行 MySQL 注释
      set a = 0;
   end if;
   ...
end;

8. 不能在 MySQL 存储过程中使用 “return” 关键字。 
 set c = a + b;
select c as sum;
end;
9. 调用 MySQL 存储过程时候，需要在过程名字后面加“()”，即使没有一个参数，也需要“()”
call pr_no_param();
10. 因为 MySQL 存储过程参数没有默认值，所以在调用 MySQL 存储过程时候，不能省略参数。可以用 null 来替代。 
call pr_add(10, null);

来源： http://blog.sina.com.cn/s/blog_5c5bc9070100z2bl.html