mysql存储过程是什么意思 mysql存储过程有什么用

转载

墨守成规de网工 2023-08-15 09:51:50

文章标签 mysql存储过程是什么意思 mysql 存储过程数据库 SQL 文章分类 MySQL 数据库

一、存储过程概述

1、什么是存储过程
存储过程个人理解可以概括为一下几点：

存储过程是一组SQL语句集合，可以主动调用并给予参数完成程序中的单独功能。
存储过程是一种可进行编译的函数，在某种程度上是类似于OOP面向对象中方法。
存储过程可以视为是为了优化SQL批量处理而创建的。

2、为什么要用存储过程
很多应用程序中都会用到存储过程，个人对存储过程的优点可以概括为以下几点：

具有可复用性，程序中可以多次调用，可以省去很多的麻烦。不必多写SQL语句，只需要修改存储过程，对程序源代码毫无影响。
具有比较快的执行速度，存储过程都是先编译后存在数据库中（也就是预编译），之后用户可以指定过程名给定参数进行call调用，所以相比较大量的SQL语句进行批量处理，每次都要编译后执行，相对速度就慢很多。
具有较强的灵活性：在存储过程中不光只是简单的增删改查，还可以进行流程控制、参数声明等，这样就可以完成较为复杂的SQL运算要求。
具有一定安全性，用户在调用存储过程的过程中，只是在网络中显示调用存储过程的，而不会过多显示底层SQL语句，这样就可以对权限进行控制，保护了数据。

二、创建存储过程

SQL语句的编写不同的数据库写法会有许多不同，这里就以MySQL为例。
1、存储过程的常用语法

CREATE PROCEDURE  过程名([[IN|OUT|INOUT] 参数名 数据类型[,[IN|OUT|INOUT] 参数名 数据类型…]]) [特性 ...] 过程体

（1）创建存储过程：

drop procedure if exists MyPorcedure ;  
    create procedure MyPorcedure () 
    begin 
    select* from shops_info; 
    end;

存储过程体格式为开始与结束标志使用begin与end，它们之间是SQL语句或流程控制语句。
（2）调用存储过程

call MyPorcedure();

调用存储过程用到关键字call
（3）存储过程的参数
存储过程中有三种类型的的参数，分别为：输入、输出、输入输出。即IN,OUT,INOUT。

IN：参数的值在调用存储过程时被指定，在存储过程中修改后的值不能被返回。但是内部的修改对调用者来说是不可见的，相当于函数中的内部局部变量。
OUT：在存储过程中可以被修改，可以被返回，无论调用者是否传值给存储过程,在存储过程中默认值为NULL。相当于函数中return parm，给调用者使用的
INOUT：调用时候被指定，在存储过程中可以被改变，也可以返回。

IN类型：

调用存储过程时候被指定数值，内部被修改后无法返回被修改的值，即指定的值是几，返回的就是几。

delimiter //

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

delimiter ;
/*-----调用时候指定值-----*/
set @id = 10;

/*-----存储过程中内部修改了id的值，其运行的结果in_id = 11-----*/
call pr_test(@id);

/*-----id的值并未返回给调用者，其运行的结果in_id = 10-----*/
select @id as out_id;

mysql存储过程是什么意思 mysql存储过程有什么用_mysql存储过程是什么意思

mysql存储过程是什么意思 mysql存储过程有什么用_mysql存储过程是什么意思_02

OUT类型：

调用存储过程时候无论是被指定还是未指定数值，内部被修改后会返回被修改的值，即指定的值是几，返回的不是指定的值，而是内部被修改后更新的值。

delimiter //

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

delimiter ;
*-----调用时候指定id = 10，但是OUT类型默认的都是NULL-----*/
set @id = 10;

/*-----运行的结果in_id = 0-----*/
call pr_test(@id);

/*-----运行的结果in_id = 0：NULL内部被改为0了-----*/
select @id as out_id;

mysql存储过程是什么意思 mysql存储过程有什么用_SQL_03

存储内部调用之后id值被修改，内部in_id与外部out_id是一样的

mysql存储过程是什么意思 mysql存储过程有什么用_存储过程_04

INOUT类型：

值在存储过程内部修改后，可以被返回给调用者

delimiter //

create procedure pr_test(inout id int)
begin
if(id is not null)then set id = id + 1;
else set id = 0;
end if;
select id as in_id;
end;
//

delimiter ;

set @id = 10;

/*-----运行的结果in_id = 11-----*/
call pr_test(@id);

/*-----运行的结果in_id = 11-----*/
select @id as out_id;

mysql存储过程是什么意思 mysql存储过程有什么用_mysql_05

mysql存储过程是什么意思 mysql存储过程有什么用_mysql存储过程是什么意思_06

（4）分隔符DELIMITER
MySQL默以“；”分号为分割号，如果不对存储过程使用分割号的话，编译过程很可能会出错，会以为存储过程是SQL语句而进行处理，所以首先得声明分隔符号，一般用”//”或者“;;”

/*-----改默认的;号为//-----*/
DELIMITER //
  CREATE PROCEDURE Myproc(OUT s int)
    BEGIN
      ....
    END
    //
/*-----改回默认的;分割号-----*/
DELIMITER ;

（5）存储过程中的变量
存储过程中的变量分为系统变量与用户变量。

定义变量：用declare

DECLARE var_name[varname]...date_type[DEFAULT VALUE];

定义用户变量MYPARAM 类型为INT，默认值为100。

DECLARE MYPARAM INT DEFAULT 100;

为变量赋值：用set赋值可以修改默认值。

SET var_name=expr[var_name=expr]...

expr可以是具体的数值或者字符串，也可以是表达式。
声明两个变量，分别为var1、var2和var3。

DECLARE var1,var2,var3 INT;
SET var1=10,var2=20;
SET var3=var1+var2;

（6）存储过程中常用到的流程控制语法

IF-THEN-ELSE语句:

语法格式：

if...then...
else...
end if;

示例：

DROP PROCEDURE IF EXISTS Mypro;
DELIMITER //
CREATE PROCEDURE Mypro(IN parameter int)
  BEGIN
    DECLARE var int;
    SET var=parameter+1;
    IF var=0 THEN
      INSERT INTO t VALUES (17);
    END IF ;
    IF parameter=0 THEN
      UPDATE t SET s1=s1+1;
    ELSE
      UPDATE t SET s1=s1+2;
    END IF ;
  END ;
  //
DELIMITER ;

CASE-WHEN-THEN-ELSE语句

语法格式：

case param
   when value1 then
         ...
   when value2 then
         ...
   when value3 then
         ...
   else
             ...
   end case;

实例：

DELIMITER //
  CREATE PROCEDURE Mypro (IN parameter INT)
    BEGIN
      DECLARE var INT;
      SET var=parameter+1;
      CASE var
        WHEN 0 THEN
          INSERT INTO t VALUES (17);
        WHEN 1 THEN
          INSERT INTO t VALUES (18);
        ELSE
          INSERT INTO t VALUES (19);
      END CASE ;
    END ;
  //
DELIMITER ;

WHILE-DO…END-WHILE语句：
语法格式：

while 条件表达式 do
    .....
end while;

实例：

DELIMITER //
  CREATE PROCEDURE Myproc()
    BEGIN
      DECLARE var INT;
      SET var=0;
      WHILE var<6 DO
        INSERT INTO t VALUES (var);
        SET var=var+1;
      END WHILE ;
    END;
  //
DELIMITER ;

2、关于存储过程相关语法

（1）删除存储过程
一般在我们创建存储过程之前，都要判断是否存在存储过程，如果存在则删除存储过程。