数据库存储过程

最新推荐文章于 2024-03-07 14:39:49 发布

Rxlywz

最新推荐文章于 2024-03-07 14:39:49 发布

阅读量892

点赞数 2

分类专栏：数据库文章标签：数据库数据库服务器 sql mysql

数据库专栏收录该内容

1 篇文章 1 订阅

订阅专栏

本文主要以mysql为例

什么是存储过程？

存储过程就是存储在数据库服务器中的一组为了完成特定功能的sql语句集，经编译后存储在数据库中，用户通过指定存储过程名称并给出参数（存储过程带有参数）来执行它。存储过程可以由一个应用程序通过一个调用来执行，而且允许用户声明变量。同时存储过程可以接受和输出参数，返回执行存储过程的状态值，也可以嵌套调用。

存储过程的优缺点：

优点

1、减少网络通信量。调用一个行数不多的存储过程和直接调用sql语句网络通信量差别不是很大。但是如果存储过程包含上百条sql语句，其性能比一条一条的调用要高的多。

2、执行速度快。存储过程创建的时候，数据库已经对其进行了一次解析和优化。其次，存储过程一旦执行，在内存中就会保留这个存储过程，下次执行同样的存储过程时，可以从内存中直接读取。

3、安全性更好。存储过程是通过向用户授予权限（而不是基于表），它们可以提供对特定数据的访问，提高代码安全，不如防止sql注入。

4、业务逻辑可以封装存储过程，这样不仅容易维护，而且执行效率高。

缺点

1、当从一种数据库迁移到另一种数据库时，不少存储过程的编写要进行部分修改。

2、存储过程需要花费一定的时间去学习。

存储过程的作用

存储例成包括存储过程和存储函数。

存储过程主要完成增删改查等工作。

存储函数只完成查询工作，可接受输入参数并返回一个结果。

步骤：

创建mysql存储过程、存储函数

create procedure 存储过程名(参数)

存储过程体

create function 存储函数名(参数)

样例：

DELIMITER // 
CREATE PROCEDURE proc1(OUT sint) 
BEGIN
SELECTCOUNT(*) INTO s FROM user; 
END
//

注：

（1）这里需要注意的是DELIMITER//和DELIMITER;两句，DELIMITER是分割符的意思，因为MySQL默认以";"为分隔符，如果我们没有声明分割符，那么编译器会把存储过程当成SQL语句进行处理，则存储过程的编译过程会报错，所以要事先用DELIMITER关键字申明当前段分隔符，这样MySQL才会将";"当做存储过程中的代码，不会执行这些代码，用完了之后要把分隔符还原。（如果使用的是mysql管理工具，不需要声明）

例子：

DELIMITER //  
CREATE PROCEDURE demo_in_parameter(IN p_in int)  
LANGUAGE SQL 

DETERMINISTIC

SQL SQCURITY DEFINER
COMMENT 'A procedure'
BEGIN   
SELECT p_in;   
SET p_in=2;   
SELECT p_in;   
END;   
//

注：LANGUAGE SQL指定使用的语言

DETERMINISTIC当确定每次的存储过程的输入和输出都是相同的内容时，可以使用该关键词，否则默认为 NOT DETERMINISTIC

SQL SQCURITY DEFINER表示调用时检查用户的权限。当值为INVOKER时，表示是用户调用该存储过程时检查，默认为DEFINER,即创建存储过程时检查。

COMMENT 'A procedure'存储过程的注释说明部分

BEGIN END中是存储过程的主体部分。

存储过程的参数

CREATE PROCEDURE proc1 () 这个存储过程中是空的参数列表

输入参数：CREATE PROCEDURE proc1 (IN varname DATA-TYPE) 这个存储过程中有一个输出参数，名称为varname，后面是跟数据类型DATA-TYPE,IN参数是默认的，因此可以省略不写。（该参数的值必须在调用存储过程时指定，在存储过程中修改该参数的值不能被返回）

输出参数：CREATE PROCEDURE proc1 (OUT varname DATA-TYPE) 这个存储过程中varname为输出参数。（该值可在存储过程内部被改变，并可返回）

输入输出参数：CREATE PROCEDURE proc1 (INOUT varname DATA-TYPE) 这个存储过程中，varname既是输入参数也是输出参数。（调用时指定，并且可被改变和返回）

执行结果：

  mysql > SET @p_in=1;  
  mysql > CALL demo_in_parameter(@p_in);  
  +------+  
  | p_in |  
  +------+  
  |   1  |   
  +------+  
   
 +------+  
 | p_in |  
 +------+  
 |   2  |   
 +------+  
  
 mysql> SELECT @p_in;  
 +-------+  
 | @p_in |  
 +-------+  
 |  1    |  
 +-------+

p_in虽然在存储过程中被修改，但并不影响@p_id的值

OUT

执行结果:
1.  mysql > SET @p_out=1;  
2.  mysql > CALL sp_demo_out_parameter(@p_out);  
3.  +-------+  
4.  | p_out |   
5.  +-------+  
6.  | NULL  |   
7.  +-------+  
8.   
9. +-------+  
10.| p_out |  
11.+-------+  
12.|   2   |   
13.+-------+  
14. 
15.mysql> SELECT @p_out;  
16.+-------+  
17.| p_out |  
18.+-------+  
19.|   2   |  
20.+-------+

INOUT

 mysql > DELIMITER //   
 mysql > CREATE PROCEDURE demo_inout_parameter(INOUT p_inout int)   
       -> BEGIN
       -> SELECT p_inout;  
       -> SET p_inout=2;  
       -> SELECT p_inout;   
       -> END;  
       -> //   
  mysql > DELIMITER ;

 执行结果:
  mysql > SET @p_inout=1;  
  mysql > CALL demo_inout_parameter(@p_inout) ;  
  +---------+  
  | p_inout |  
  +---------+  
  |    1    |  
  +---------+  

  +---------+  
  | p_inout |   
  +---------+  
  |    2    |  
  +---------+  
  mysql > SELECT @p_inout;  
  +----------+  
  | @p_inout |   
  +----------+  
  |    2     |  
  +----------+

调用存储过程的方法

调用存储过程的方法很简单，只需要使用call命令即可，后面跟要调用存储过程的名称及输入的变量列表，比如：

CALL stored_procedure_name (param1, param2, ....)

CALL procedure1(10 , 'string parameter' , @parameter_var);

修改和删除存储过程

可以用ALTER的语法去修改存储过程的主要特征和参数，要修改其存储过程的主体部分的话，必须要先删除然后再重建。比如下面修改存储过程num_from_employee的定义。将读写权限改为MODIFIES SQL DATA，并指明调用者可以执行。代码执行如下：

ALTER PROCEDURE demo_in_parameter
MODIFIES SQL DATA SQL SECURITY INVOKER ;

而删除存储过程的语法为使用DROP关键词即可（从mysql表格中删除一个或多个存储）。如下

DROP PROCEDURE IF EXISTS demo_in_parameter;

MySQL存储过程的查询

通过以下语句查询存储过程

select name from mysql.proc where db=’数据库名’;

或者

select routine_name from information_schema.routines where routine_schema='数据库名';

或者

show procedure status where db='数据库名';

进行查询。

如果我们想知道，某个存储过程的详细，那我们又该怎么做呢？是不是也可以像操作表一样用describe 表名进行查看呢？

查看当前存储过程的详细

SHOW CREATE PROCEDURE 数据库.存储过程名;

定义变量

局部变量声明一定要放在存储过程体的开始

DECLARE varname[,varname...] DATA-TYPE [DEFAULT defaultvalue];

注：DATA-TYPE为Mysql数据类型，如:int, float, date,varchar(length)

样例：

     DECLARE a, b INT DEFAULT 5;
　 DECLARE str VARCHAR(50);
     DECLARE l_varchar varchar(255) DEFAULT 'This will not be padded';
　DECLARE today TIMESTAMP DEFAULT CURRENT_DATE;
   DECLARE l_datetime datetime DEFAULT '1999-12-31 23:59:59';
     DECLARE v1, v2, v3 TINYINT;

变量赋值

SET 变量名 = 表达式值 [,variable_name= expression ...]

一旦定义好变量，就可以在存储过程中对其进行赋初值,并进行各类相关的操作，比如：

DELIMITER //
　　CREATE PROCEDURE `var_proc` (IN paramstr VARCHAR(20))
　　BEGIN
　　DECLARE a, b INT DEFAULT 5;
　　DECLARE str VARCHAR(50);
　　DECLARE today TIMESTAMP DEFAULT CURRENT_DATE;
　　DECLARE v1, v2, v3 TINYINT;
　　INSERT INTO table1 VALUES (a);
　　SET str = 'I am a string';
　　SELECT CONCAT(str,paramstr), today FROM table2 WHERE b>=5;
　　END //

用户变量

在MySQL客户端使用用户变量

mysql > SELECT 'Hello World' into @x;  
  mysql > SELECT @x;  
  +------------+  
  |   @x        |  
  +-------------+  
  | Hello World |  
  +-------------+  
  mysql > SET @y='Goodbye Cruel World';  

  mysql > SELECT @y;  
+---------------------+  
|     @y              |  
+---------------------+  
| Goodbye Cruel World |  
+---------------------+  
mysql > SET @z=1+2+3;  
mysql > SELECT @z;  
+------+  
| @z   |  
+------+  
|  6   |  
+------+

在存储过程中使用用户变量

  mysql > CREATE PROCEDURE GreetWorld( ) SELECT CONCAT(@greeting,' World');  
  mysql > SET @greeting='Hello';  
  mysql > CALL GreetWorld( );  
  +----------------------------+  
  | CONCAT(@greeting,' World') |  
  +----------------------------+  
  |  Hello World               |  
  +----------------------------+

在存储过程间传递全局范围的用户变量

  mysql> CREATE PROCEDURE p1()   SET @last_procedure='p1'; 
  mysql> CREATE PROCEDURE p2() SELECT CONCAT('Last procedure was ',@last_procedure);  
  mysql> CALL p1( );  
  mysql> CALL p2( );  
  +-----------------------------------------------+  
  | CONCAT('Last procedure was ',@last_proc       |  
  +-----------------------------------------------+  
  | Last procedure was p1                         |  
  +-----------------------------------------------+

注意:

①用户变量名一般以@开头

②滥用用户变量会导致程序难以理解及管理

MYSQL存储过程的语法结构

MYSQL存储过程中支持IF，CASE，ITERATE，LEAVE LOOP，WHILE和REPEAT等语法结构和语句

IF 语句：if语句使用的是if…then end if的语法结构，例子如下:

DELIMITER //
　　CREATE PROCEDURE `proc_IF` (IN param1 INT)
　　BEGIN
　　DECLARE variable1 INT;
　　SET variable1 = param1 + 1;
　　IF variable1 = 0 THEN
　　SELECT variable1;
　　END IF;
　　IF param1 = 0 THEN
　　SELECT 'Parameter value = 0';
　　ELSE
　　SELECT 'Parameter value <= 0';
　　END IF;
　　END //

CASE语句：当有很多IF语句时，就应该考虑使用CASE语句了，它是多分支选择语句，有两种写法：

第一种写法：

DELIMITER //
　　CREATE PROCEDURE `proc_CASE` (IN param1 INT)
　　BEGIN
　　DECLARE variable1 INT;
　　SET variable1 = param1 + 1;
　　CASE variable1
　　WHEN 0 THEN
　　INSERT INTO table1 VALUES (param1);
　　WHEN 1 THEN
　　INSERT INTO table1 VALUES (variable1);
　　ELSE
　　INSERT INTO table1 VALUES (99);
　　END CASE;
　　END //

第二种写法：

DELIMITER //
　　CREATE PROCEDURE `proc_CASE` (IN param1 INT)
　　BEGIN
　　DECLARE variable1 INT;
　　SET variable1 = param1 + 1;
　　CASE
　　WHEN variable1 = 0 THEN
　　INSERT INTO table1 VALUES (param1);
　　WHEN variable1 = 1 THEN
　　INSERT INTO table1 VALUES (variable1);
　　ELSE
　　INSERT INTO table1 VALUES (99);
　　END CASE;
　　END //

WHILE语句：WHILE语句跟普通编程语言中的while语句差不多，例子如下：

DELIMITER //
　　CREATE PROCEDURE `proc_WHILE` (IN param1 INT)
　　BEGIN
　　DECLARE variable1, variable2 INT;
　　SET variable1 = 0;
　　WHILE variable1
　　INSERT INTO table1 VALUES (param1);
　　SELECT COUNT(*) INTO variable2 FROM table1;
　　SET variable1 = variable1 + 1;
　　END WHILE;
　　END //

MYSQL存储过程中的游标

游标提供了一种对从表中检索出的数据进行操作的灵活手段，就本质而言，游标实际上是一种能从包括多条数据记录的结果集中每次提取一条记录的机制。MYSQL中的游标的语法如下：

DELIMITER //
　　CREATE PROCEDURE `proc_CURSOR` (OUT param1 INT)
　　BEGIN
　　DECLARE a, b, c INT;
　　DECLARE cur1 CURSOR FOR SELECT col1 FROM table1;
　　DECLARE CONTINUE HANDLER FOR NOT FOUND SET b = 1;
　　OPEN cur1;
　　SET b = 0;
　　SET c = 0;
　　WHILE b = 0 DO
　　FETCH cur1 INTO a;
　　IF b = 0 THEN
　　SET c = c + a;
　　END IF;
　　END WHILE;
　　CLOSE cur1;
　　SET param1 = c;
　　END //

其中，DECLARE cur1 CURSOR FOR SELECT col1 FROM table1;

表示将从table1表中选取col1列的内容放到游标curl中，即每次游标遍历的结果都放在curl中，要注意游标只能向前遍历，而不能向后，并且注意，游标不能更新，最后关闭游标。

Rxlywz

关注

2
点赞
踩
4

收藏

觉得还不错? 一键收藏
0
评论
数据库存储过程

什么是存储过程？存储过程就是存储在数据库服务器中的一组为了完成特定功能的sql语句集，经编译后存储在数据库中，用户通过指定存储过程名称并给出参数（存储过程带有参数）来执行它。存储过程的作用存储例成包括存储过程和存储函数。存储过程主要完成增删改查等工作，存储函数只完成查询工作，可接受输入参数并返回一个结果。步骤：创建mysql存储过程、存储函数create p
复制链接

扫一扫