数据库编程

本文共--字 阅读约--分钟 | 浏览: -- Last Updated: 2021-04-18

一、存储过程

存储过程：是一组为了完成某项特定功能的 SQL 语句集，可以由 声明式的 SQL 语句 和 过程式的 SQL 语句 组成。这组语句集经过编译后存储在数据库中，用户通过指定存储过程的名字和参数来调用并执行存储过程，而不必重新编译。

这种通过定义一段程序存储在数据库中的方式，可加大数据库操作语句的执行效率。

使用存储过程的好处：

  • 可增强 SQL 语言的功能和灵活性；
  • 良好的封装性；
  • 高性能；
  • 可减少网络流量；
  • 存储过程作为一种安全机制来确保数据库的安全性和数据的完整性；

1.结束标志修改 DELIMITER

DELIMITER 用于将 MySQL 语句的结束标志临时修改为其他符号，从而使得 MySQL 服务器可以完整地处理存储过程体中所有的 SQL 语句。

在 MySQL 中，服务器处理 SQL 语句默认是以分号作为语句的结束标志。

-- 语法格式 是用户定义的结束符，要避免使用反斜杠(\)
DELIMITER $$

2.创建存储过程 CREATE PROCEDURE

在 MySQL 中，是使用 CREATE PROCEDURE 语句来创建存储过程，语法格式：

CREATE PROCEDURE sp_name([proc_parameter [, ...]) routine_body

sp_name：存储过程的名称；

proc_parameter：指定存储过程的参数列表；多个参数间用逗号分隔；

proc_parameter 的格式为：[IN|OUT|INOUT] param_name type，IN输入，OUT输出，INOUT输入输出。

routine_body：存储过程的主体部分，也称为存储过程体，包含了在过程调用的时候必须执行的 SQL 语句。这个部分以 BEGIN 开始，以 END 结束。

举例：在数据库 mysql_test 中创建一个存储过程，用于实现给定表 customers 中一个客户 id 号即可修改表 customers 中该客户的性别为一个指定的性别。

> USE mysql_test;

> DELIMITER $$

> CREATE PROCEDURE sp_update_sex (IN cid INT,IN csex CHAR(1))
  BEGIN
    UPDATE customers SET cust_sex=csex WHERE cust_id=cid;
  END $$

3.声明局部变量 DECLARE

在存储过程体中可以使用 DECLARE 语句声明局部变量，用来存储存储过程体中的临时结果。

语法格式：

-- var_name 变量名
-- DEFAULT 默认值，没有指定的话默认为NULL
DECLARE var_name[,…] type [DEFAULT value]

-- 举例
DECLARE cid INT;

使用局部变量需要注意的事项：

  • 局部变量只能在存储过程体的 BEGIN…END 语句块中声明；
  • 局部变量必须在存储过程体的开头处声明；
  • 局部变量的作用范围仅限于声明它的 BEGIN…END 语句块；

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

  • 局部变量声明时，在其前面没有使用@符号，并且只能被声明它的BEGIN...END语句块中的语句所使用；
  • 用户变量声明时，会在其名称前使用@ 符号，同时已声明的用户变量存于整个会话之中。

系统变量前面会是@@

4.为局部变量赋值 SET

在 MySQL 中，可以使用 SET 语句为局部变量赋值，语法格式为：

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

-- 示例
SET cid=910；

5.选定列的值直接存储到局部变量中 SELECT…INTO

在 MySQL 中，可以使用 SELECT…INTO 语句把选定列的值直接存储到局部变量中。

语法格式：

-- col_name：指定列名
-- var_name：指定要赋值的变量名
-- table_expr : SELECT 语句中的 FROM 子句及后面的语法部分。
SELECT col_name[,…] INTO var_name[,…] table_expr

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

6.控制语句流程

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

条件判断语句：IF…THEN…ELSE 和 CASE

循环语句： WHILE 语句、 REPEAT 语句和 LOOP 语句。可以使用 ITERATE 语句退出当前循环，重新开始一个循环。

WHILE：

WHILE 条件 DO
  循环体
END WHILE

REPEAT:

REPEAT
  循环体
UNTIL 条件
END REPEAT;

LOOP:

loop_name:LOOP
  IF 条件 THEN
    LEAVE loop_name;
  END IF;
END LOOP;

7.游标 CURSOR

游标 是一个被 SELECT 语句检索出来的结果集。在存储了游标后，应用程序或用户就可以根据需要滚动或浏览其中的数据。使用的步骤如下：

  • 声明游标： DECLARE CURSOR
  • 打开游标： OPEN
  • 读取数据： FETCH…INTO
  • 关闭游标： CLOSE

说明：

  • 在使用游标之前，必须先声明游标。
  • 在定义游标之后，必须打开游标才能使用。一个游标可以被多次打开。
  • 游标相当于一个指针，指向当前一行数据。
  • 在结束游标使用时，必须关闭游标。如果没有明确关闭游标， MySQL 将会在到达 END 语句时自动关闭它。

需要注意的点：

  • 游标只能用于存储过程或存储函数中，不能单独在查询操作中使用；
  • 在存储过程或存储函数中可以定义多个游标，但是在一个 BEGIN…END 语句块中每一个游标的名字必须是唯一的；
  • 游标不是一条 SELECT 语句，是被 SELECT 语句检索出来的结果集。

8.调用存储过程 CALL

创建好存储过程后，可以使用 CALL 语句在程序或其他存储过程中调用。

CALL sp_name([paramter[, ...]])
CALL sp_name[()]

sp_name ：指定被调用存储过程的名称；

parameter ：指定调用存储过程所要使用的参数；

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

9.删除存储过程 DROP PROCEDURE

在 MySQL 中，可以使用 DROP PROCEDURE 语句删除数据库中已创建的存储过程。

DROP PROCEDURE [IF EXISTS] sp_name

二、存储函数

1.存储函数与存储过程的相同点和不同点

相同点：由 SQL 语句和过程式语句所组成的代码片段，并且可以被应用程序和其他 SQL 语句调用。

不同点：

  • 存储函数自身就是输出参数，不能拥有输出参数；存储过程可以拥有输出参数；
  • 存储函数可以直接被调用，不需要使用 CALL 语句；存储过程的调用需要使用 CALL 语句；
  • 存储函数必须包含一条 RETURN 语句；存储过程不允许包含 RETURN 语句。

2.创建存储函数 CREATE FUNCTION

在 MySQL 中，使用 CREATE FUNCTION 语句来创建存储函数，语法格式：

CREATE FUNCTION sp_name(func_parameter[, ...])
  RETURNS type
  routine_body

sp_name ：存储函数的名称;

func_parameter：语法格式是：param_name type，指定存储函数的参数列表；这里参数只有名称和类型，不能指定 IN,OUT 和 INOUT。

routine_body：存储函数的主体部分，也称为存储函数体。存储函数体中必须包含一个 RETURN value 语句。

3.调用存储函数 SELECT

在 MySQL 中，使用 SELECT 语句来调用存储函数：

SELECT sp_name([func_parameter[, ...]])

4.删除存储函数 DROP FUNCTION

在 MySQL 中，使用 DROP FUNCTION 语句来删除存储函数，语法格式：

DROP FUNCTION [IF EXISTS] sp_name