mysql 系统表存储过程_数据库系统（六）---MySQL语句及存储过程

樱桃Anne

于 2021-02-27 11:20:59 发布

阅读量494

点赞数

文章标签： mysql 系统表存储过程

本文链接：https://blog.csdn.net/weixin_42521424/article/details/114806487

版权

1、存储过程是一组为了完成某项特定功能的 SQL 语句集，其实质上就是一段存储在数据库中的代码，它可以由声明式的 SQL 语句(如 CREATE、UPDATE 和SELECT 等语句)和过程式 SQL 语句(如 IF…THEN…ELSE 控制结构语句)组成。创建的存储过程保存在数据库的数据字典中。

使用存储过程通常具有以下优势：

1) 可增强 SQL 语言的功能和灵活性；

2) 良好的封装性；

3) 高性能；

4) 可减少网络流量；

5) 存储过程可作为一种安全机制来确保数据库的安全性和数据的完整性。

2、创建存储过程

2.1)DELIMITER 命令

DELIMITER 命令将 MySQL 语句的结束标志临时修改为其他符号，从而使得 MySQL 服务器可以完整地处理存储过程体中所有的 SQL 语句，而后可通过 DELIMITER 命令再将 MySQL 语句的结束标志改回为 MySQL 的默认结束标志，即分号(；)。

DELIMITER 命令的语法格式： DELIMITER $$

2.2)创建存储过程

在 MySQL 中，是使用 CREATE PROCEDURE 语句来创建存储过程，其常用的语法格式是： CREATE PROCEDURE sp_name([proc_parameter[，…]]) Routine_body

"sp_name" 用于指定存储过程的名称，且默认在当前数据库中创建；

"proc_parameter" 用于指定存储过程的参数列表；

"routine_body" 表示存储过程的主体部分，也称为存储过程体。

其中，语法项“proc_parameter”的语法格式是： [IN|OUT|INOUT] param_name type

IN 输入参数：表示调用者向过程传入值(传入值可以是字面量或变量)；

OUT 输出参数：表示过程向调用者传出值(可以返回多个值)(传出值只能是变量)；

INOUT 输入输出参数：既表示调用者向过程传入值，又表示过程向调用者传出值(值只能是变量)；

2.3)存储过程体

局部变量：在存储过程体中可以声明局部变量，用来存储存储过程体中的临时结果。在 MySQL 中，可以使用 DECLARE 语句来声明局部变量，并且同时还可以对该局部变量赋予一个初始值，其使用的语法格式是： DECLARE var_name[，…] type [DEFAULT value]

其中，

1)"var_name" 用于指定局部变量的名称；

2)"type" 用于声明局部变量的数据类型；

3) DEFAULT 子句用于为局部变量指定一个默认值，若没有指定，则默认为 NULL。需要注意的事项如下：

1) 局部变量只能在存储过程体的 BEGIN…END 语句块中声明。

2) 局部变量必须在存储过程体的开头处声明。

3) 局部变量的作用范围仅限于声明它的 BEGIN…END 语句块，其他语句块中的语句不可以使用它。

4) 局部变量不同于用户变量，两者的区别是：

局部变量声明时，在其前面没有使用@符号，只能被声明它的 BEGIN…END 语句块中的语句所使用；

用户变量在声明时，会在其名称前面使用@符号，同时已声明的用户变量存在于整个会话之中。

4) SET 语句在 MySQL 中，可以使用 SET 语句为局部变量赋值，语法格式是： SET var_name=expr[，var_name=expr] …

5) SELECT…INTO 语句在 MySQL 中，可以使用 SELECT…INTO 语句把选定列的值直接存储到局部变量中，语法格式是： SELECT col_name[,…] INTO var_name[,…] table_expr 其中：

“col_name”用于指定列名；

“var_name”用于指定要赋值的变量名；

“table_expr”表示 SELECT 语句中的 FROM 子句及后面的语法部分。

注意：存储过程体中的 SELECT…INTO 语句返回的结果集只能有一行数据。

6)流程控制语句

在 MySQL 中，可以在存储过程体中，使用条件判断语句和循环语句这样两类用于控制语句流程的过程式 SQL 语句。

(1) 条件判断语句常用的条件判断语句有 IF…THEN…ELSE 语句和 CASE 语句；

(2) 循环语句常用的循环语句有 WHILE 语句、REPEAR 语句和 LOOP 语句。此外，循环语句中还可以使用 ITERATE 语句，但它只能出现在循环语句的 LOOP、REPEAT 和 WHILE 子句中，用于表示退出当前循环，且重新开始一个循环。

eg：

select...into...

mysql> delimiter $$mysql> CREATE PROCEDURE proc_add_stu(

-> IN sNo INTEGER,

-> OUT sidint-> )mysql> BEGIN#存储过程开始

->insert into student(s_no) values(sNo);

->SELECT LAST_INSERT_ID() intosid; #将选定列的值直接存储到局部变量中

->END$$ #存储过程结束

mysql> delimiter;　　#将语句的结束符号恢复为分号

mysql> call pro_add_stu('0001');

in输入参数(默认，可省略不写)

mysql>delimiter $$

mysql> create procedure in_proce(in p_in int)-> begin

-> 　　selectp_in;-> 　　set p_in=0; #局部变量赋值(begin...和end之间)-> selectP_in;-> end$$

mysql>delimiter ;

mysql> set @p_in=1; #全局变量@p_in赋值

mysql> call in_param(@p_in); #将全局变量@p_in的值作为参数传递给局部变量p_in+------+

| p_in |

+------+

| 1 |

+------+

| P_in |

+------+

| 0 |

+------+

mysql> select @p_in; #输出全局变量@p_in的结果+-------+

| @p_in |

+-------+

| 1 |

+-------+

以上可以看出，p_in 在存储过程中被修改，但并不影响 @p_id 的值，因为前者为局部变量、后者为全局变量。

out输出参数

mysql> delimiter //mysql> create procedure out_proce(out p_out int)-> begin

-> selectp_out;-> set p_out=2;-> selectp_out;-> end

-> //mysql>delimiter ;

mysql> set @p_out=1;

mysql> call out_proce(@p_out);+-------+

| p_out |

+-------+

| NULL |

+-------+

#因为out是向调用者输出参数，不接收输入的参数，所以存储过程里的p_out为null+-------+

| p_out |

+-------+

| 2 |

+-------+

mysql> select @p_out; #输出全局变量(用户变量)结果+--------+

| @p_out |

+--------+

| 2 |

+--------+

#调用了out_proce存储过程，输出参数，改变了p_out变量的值

inout输入参数(尽量少用)

mysql>delimiter $$

mysql> create procedure inout_proce(inout p_inout int)-> begin

-> selectp_inout;-> set p_inout=2;-> selectp_inout;-> end

->$$

mysql>delimiter ;

mysql> set @p_inout=1;

mysql> call inout_proce(@p_inout);+---------+

| p_inout |

+---------+

| 1 |

+---------+

| p_inout |

+---------+

| 2 |

+---------+

mysql> select @p_inout;+----------+

| @p_inout |

+----------+

| 2 |

+----------+

#调用了inout_param存储过程，接受了输入的参数，也输出参数，改变了变量

变量作用域

内部的变量在其作用域范围内享有更高的优先权，当执行到 end。变量时，内部变量消失，此时已经在其作用域外，变量不再可见了，应为在存储过程外再也不能找到这个申明的变量，但是你可以通过 out 参数或者将其值指派给会话变量来保存其值。

mysql > DELIMITER //mysql> CREATE PROCEDUREproc3()-> begin

-> declare x1 varchar(5) default 'outer';-> begin

-> declare x1 varchar(5) default 'inner';-> selectx1;-> end;-> selectx1;-> end;-> //mysql> DELIMITER ;

条件语句

mysql > DELIMITER //mysql> CREATE PROCEDURE proc2(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;-> //mysql> DELIMITER ;

循环语句

mysql > DELIMITER //mysql> CREATE PROCEDUREproc4()-> begin

-> declare var int;-> set var=0;-> while var<6do-> insert into t values(var);-> set var=var+1;-> end while;-> end;-> //mysql> DELIMITER ;

2.5) 删除存储过程

DROP PROCEDURE[IF EXISTS] sp_name 其中，“sp_name”用于指定要删除的存储过程的名称。

2.4)游标

游标是一个被 SELECT 语句检索出来的结果集。即，游标可以遍历返回的多行结果。

在 MySQL 中，使用游标的具体步骤如下：

(1) 声明游标 DECLARE cursor_name CURSOR FOR select_statement 其中，

“cursor_name”用于指定要创建的游标的名称，其命名规则与表名相同；

“select_statement”用于指定一个 SELECT 语句，其会返回一行或多行的数据，且需注意此处的 SELECT 语句不能有 INTO 子句。

(2) 打开游标 OPEN cursor_name 其中，“cursor_name”用于指定要打开的游标。

(3) 读取数据 FETCH cursor_name INTO var_name[,var_name]… 其中，“cursor_name”用于指定已打开的游标；语法项“var_name”用于指定存放数据的变量名。

(4) 关闭游标 CLOSE cursor_name 其中，语法项“cursor_name”用于要关闭的游标。

(5)在使用游标过程中，需要注意以下几点：

1) 游标只能用于存储过程或存储函数中，不能单独在查询操作中使用。

2) 在存储过程或存储函数中可以定义多个游标，但是在一个 BEGIN…END 语句块中每一个游标的名字必须是唯一的。

3) 游标不是一条 SELECT 语句，是被 SELECT 语句检索出来的结果集。模块四调用存储过程 CALL sp_name[parameter[,…]] CALL sp_name[()] 在此语法格式中：

语法项“sp_name”用于指定被调用的存储过程的名称。如果要调用某个特定数据库的存储过程，则需要在前面加上该数据库的名称。

语法项“parameter”用于指定调用存储过程所要使用的参数。调用语句中参数的个数必须等于存储过程的参数个数。

当调用没有参数的存储过程时，使用 CALL sp_name()语句与使用 CALL sp_name 语句是相同的。

eg:

create procedurep1()begin

declare id int;declare name varchar(15);--声明游标

declare mc cursor for select * fromclass;--打开游标

openmc;--获取结果

fetch mc intoid,name;--这里是为了显示获取结果

selectid,name;--关闭游标

closemc;end;

三、存储函数

1、存储函数和存储过程的区别：

(1) 存储函数不能拥有输出参数，这是因为存储函数自身就是输出参数；而存储过程可以拥有输出参数。

(2) 可以直接对存储函数进行调用，且不需要使用 CALL 语句；而对存储过程的调用，需要使用 CALL 语句。

(3) 存储函数中必须包含一条 RETURN 语句，而这条特殊的 SQL 语句不允许包含于存储过程中。

2、创建存储函数 CREATE FUNCTION sp_name([func_parameter[，…]]) RETURNS type routine_body 其中，语法项“func_parameter”的语法格式是： param_name type

在此语法格式中：

(1) 语法项“sp_name”用于指定存储函数的名称，需注意，存储函数不能与存储过程具有相同的名字。

(2) 语法项“func_parameter”用于指定存储函数的参数，这里的参数只有名称和类型，不能指定关键字“IN”“OUT”和“INOUT”。

(3) RETURNS 子句用于声明存储函数返回值的数据类型，其中 type 用于指定返回值的数据类型。

(4) 语法项“routine_body”用于指定存储函数的主体部分，也称为存储函数体。所有在存储过程中使用的 SQL 语句在存储函数中同样也适用，包括前面所介绍的局部变量、SET 语句，流程控制语句、游标等。但是，存储函数体中还必须包含一个 RETURN value 语句，其中 value 用于指定存储函数的返回值。

eg:

#删除已经存在的存储函数DROP FUNCTION IF EXISTSfunc_stu;

#创建存储函数(声明返回类型为varChar(50))CREATE FUNCTION func_stu(in_id INT) RETURNS VARCHAR(50)BEGIN

DECLARE o_name VARCHAR(50); #声明局部变量SELECT name INTO o_name FROMtb_stuWHERE id =in_id; #tb_stu指事先创建好的数据库RETURNo_name;END;

3、调用存储函数

成功创建存储函数后，就可以如同调用系统内置函数一样，使用关键字 SELECT 对其进行调用，语法格式是： SELECT sp_name([func_parameter[，…]])

eg:

SELECT func_stu(1);

4、删除存储函数

在 MySQL 中，可以使用 DROP FUNCTION 语句来实现，语法格式： DROP FUNCTION[IF EXISTS] sp_name 其中，语法项“sp_name”指定要删除的存储函数的名称。

eg:

DROP FUNCTION IF EXISTS func_stu;

5、修改存储函数

ALTER FUNCTION func_name [characteristic ...]characteristic:

COMMENT'string'

|LANGUAGE SQL| { CONTAINS SQL | NO SQL | READS SQL DATA |MODIFIES SQL DATA }| SQL SECURITY { DEFINER | INVOKER }

樱桃Anne

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
mysql 系统表存储过程_数据库系统（六）---MySQL语句及存储过程

1、存储过程是一组为了完成某项特定功能的 SQL 语句集，其实质上就是一段存储在数据库中的代码，它可以由声明式的 SQL 语句(如 CREATE、UPDATE 和SELECT 等语句)和过程式 SQL 语句(如 IF…THEN…ELSE 控制结构语句)组成。创建的存储过程保存在数据库的数据字典中。使用存储过程通常具有以下优势：1) 可增强 SQL 语言的功能和灵活性；2) 良好的封装性；3) 高性...
复制链接

扫一扫