计算机二级MySQL笔记（第十章存储过程）

骑猪看日落 2021-10-29 21:24 518阅读 0赞

# 存储过程概述 #

在数据库的实际操作中，经常一个完整的操作需要多条语句处理多个表才能完成，而SQL语句在执行过程中，需要先编译，再执行，当需要执行的SQL语句很多时，执行效率就成了一个瓶颈问题。存储过程因此而诞生。

存储过程是一组为了完成某特定功能的SQL语句集。这组语句集经过编译后，会存储在数据库中，可随时调用执行，而不必重新编译，从而加大执行效率。所以，存储过程有以下几个优点：

*  增强SQL语句的功能和灵活性。
 *  良好的封装性。
 *  高性能。
 *  可减少网络流量。存储过程在服务端运行，当客户端调用该存储过程时，网络中的传送只是该调用语句，从而降低网络负载。
 *  确保数据库的安全性和完整性。

# 创建存储过程 #

其语法格式为：

--创建语法
    CREATE
        [DEFINER = { user | CURRENT_USER }]
    　PROCEDURE sp_name ([proc_parameter[,...]])
        [characteristic ...] routine_body
     
     --参数语法
    proc_parameter:
        [ IN | OUT | INOUT ] param_name type
     
     --特征设定
    characteristic:
        COMMENT 'string'
      | LANGUAGE SQL
      | [NOT] DETERMINISTIC
      | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
      | SQL SECURITY { DEFINER | INVOKER }
     
     --存储过程体
    routine_body:
    　　Valid SQL routine statement

*  当有多个参数时，参数列表用逗号隔开。也可以没有参数（**此时存储过程名称后仍需加上一对括号**）。
 *  参数的取名不要与数据表的列名相同。
 *  存储过程体中如果有多条SQL语句时，使用`begin...end`结构。只有一条语句时，可以省略。
 *  注意一个十分重要的命令，`delimiter`的使用，用来更SQL语句的改默认结束标志。

下面用一个实例说明：

--在数据库db_school中创建一个存储过程，用于实现给定表tb_student中一个学生的学号，即可修改表中该学生的性别为一个指定的性别。
    use db_school;
    
    delimiter !!	--修改默认语句结束标志符为感叹号！！
    create procedure sp_update(in sno char(10), in ssex char(2))
    begin
    	update tb_student set sex=ssex where studentNo=sno;
    end !!	--已经修改过，所以这里是感叹号结束
    delimiter ;		--更改回默认分号结束符

# 存储过程体 #

存储过程体是创建存储过程中的核心部分，以下介绍几种用于构造存储过程体的常用语法元素：

## 1、局部变量 ##

局部变量用来存储存储过程中的临时结果，使用时应注意：

*  只能在begin…end语句块中声明。
 *  必须在存储过程的开头出声明。
 *  作用域仅限于它的begin…end语句块，其他语句块不可调用它。
 *  不同于“**用户变量**”，前面没有“**@**”符号。

语法如下：

declare var_name type [default value]
    --多个变量名，用逗号隔开。default value 可选项。

## 2、set 语句 ##

`set`语句为局部变量赋值，语法如下：

set var_name = expr
    --多个变量赋值，用逗号隔开

## 3、select…into语句 ##

`select...into`语句将选定列的值直接存储到局部变量中，注意其结果集只能有一行数据。其语法格式为：

select col_name into var_name from...
    -- ...为查询语句的语法。参考第五章笔记

## 4、流程控制语句 ##

**（1）条件判断语句**  
`if-then-else`语句

if 条件 then 结果
    	elseif 条件 then 结果
    	else 结果
    end if

`case`语句  
case语句有两种语法格式：

case case_value
    	when when_value then statement_list
    	else statement_list
    end case
    --case_value指定要被判断的值或表达式
    --when_value指定要与case_value进行比较的值
    --倘若比较为真，则执行对应的statement_list; 否则，执行else子句中的指定语句。

第二种语法格式没有在关键字case后指定参数，而是直接在when-then语句块中指定表达式，这种语法格式可以实现更复杂的条件判断，使用起来也更方便些。

case 
    	when search_condition then statement_list
    	else statement_list
    end case
    --search_condition指定一个比较表达式

**（2）循环语句**  
`while`语句

[begin_label:] while search_condition do
    				    statement_list
    			   end while [end_label]

`repeat`语句

[begin_label:] repeat statement_list
    			   until search_condition	
    			   end repeat [end_label]

`loop`语句

[begin_label:] loop statement_list	
    			   end loop [end_label]

以上三个循坏语句使用时，应注意：

*  begin\_label、end\_label是循坏语句的标注，必须使用相同的名字，且成对出现。
 *  使用`leave`语句和`interate`语句来退出循环结构，其语法格式为：`leave label`/`interate label`。他们的区别在于，leave语句是**结束整个循环**，interate语句只是**退出当前循环**。

## 5、游标 ##

在使用select语句检索数据时，如果返回的结果集中拥有多行数据，这些数据无法直接被一行一行的处理，此时就需要使用游标。

游标是一个被select语句检索出来的结果集。

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

**（1）、声明游标**

declare cursor_name cursor for select_statement
    --select语句不能有into子句

**（2）、打开游标**

open cursor

**（3）、读取数据**

fetch cursor_name into var_name

**（4）、关闭游标**

close cursor

# 调用存储过程 #

call sp_name()

# 查看存储过程 #

show procedure status; --查看数据库中的存储过程
    show create procedure sp_name;  --查看某个存储过程

# 删除存储过程 #

drop procedure if exists sp_name;

# 存储函数 #

存储函数与存储过程相似，都是一组SQL语句集，区别在于：

*  存储函数不能拥有输出参数。因为其本身就是输出参数。
 *  使用`select`语句调用存储函数，而不使用`call`语句。
 *  存储函数体必须包含一条`return`语句，而这条语句不允许包含在存储过程中。

## 1、创建存储函数 ##

其语法格式为：

create function 函数名(参数) returns 数据类型
    begin
    	函数体
    end;

注意以下两点：

*  存储函数名不能与存储过程相同。
 *  参数只有名称和类型，不能指定关键字`in,out,inout`。
 *  函数体中必须包含一个 `return value` 语句。

--创建存储函数，要求函数能根据给定的学号返回学生的性别，如果没有给定的学号，则返回“没有该学生”
    delimiter !!
    create function fn_search(sno)
    begin
    	declare ssex char(2);
    	select sex into ssex from tb_student where studentNo=sno;
    	if ssex is null then
    		return(select '没有该学生')
    	elseif ssex='男' then
    		return(select '男')
    	else
    		return(select '女')
    	end if;   
    end !!
    delimiter ;

书本上把上面代码中的 if 语句拆分成了两个 if 语句，我认为是完全没有必要的。

## 2、调用存储函数 ##

select 函数名(参数)；

## 3、查看存储函数 ##

show function status;	--	查看数据库中的存储函数
    show create function 函数名; 	--查看存储函数具体信息

## 4、删除存储函数 ##

drop function if exists 函数名;

# 课后习题 #

![习题][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poaWNoZW5nX3h1_size_16_color_FFFFFF_t_70]  
**1、创建存储过程**

use db_test;
    
    delimiter !!
    create procedure sp_update_content(in cname char(10), in caddress varchar(20))
    begin
    	update content set address=caddress where name=cname;
    end !!
    delimiter ;

**2、删除存储过程**

drop procedure if exists sp_update_content;

**3、创建存储函数**

use db_test;
    
    delimiter !!
    create function fn_search(sno char(10), cno char(10)) 
    returns float
    deterministic  --指定对同样的输入参数产生相同的结果
    begin
    	declare var_score float;
    	select score into var_score from tb_score 
    	where studentNo=sno and courseNo=cno;
    	if sno is null then
    		return (select 0);
    	else 
    		return (select var-score);
    	end if;
    end !!
    delimiter ;

**3、调用存储函数**

select fn_search(sno, cno);

**4、删除存储函数**

drop function if exists fn_search;

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poaWNoZW5nX3h1_size_16_color_FFFFFF_t_70]: /images/20211029/58ac399fe0c142aab4a7f06a38d5953a.png