6.2 数据库存储过程在线视频

同学们好

我是数据库主讲教师陈安龙

今天给大家介绍6.3节的数据库存储过程

本节的主要内容

要求大家了解存储过程的概念

掌握存储过程创建 删除的方法

掌握存储过程的执行方法

掌握PostgreSQL的PL/SQL基本语法

理解存储过程的优缺点

我们首先来看一下什么是存储过程

存储过程顾名思义是存储在数据库的一种对象

该对象是由一组能完成特定功能的SQL语句的集合所构成

是把经常会被重复使用的SQL语句逻辑块封装起来

经编译后 存储在数据库服务器端

当被再次调用时 就不需要再次编译

当客户端连接到数据库时

用户就通过指定存储过程的名字并给出参数

数据库就可以找到相应的存储过程予以调用

下面我们看一下我们如何来创建存储过程

不同的数据库系统创建存储过程的语法存在着一定的差异

许多数据库为创建存储过程和创建函数提供不同命令

如ORACLE、MySQL、SQL SERVER等数据库

使用CREATE PRECEDURE命令创建存储过程

同时他们还提供了创建函数的方法

使用CREATE FUNCTION创建函数

PostgreSQL将函数和存储过程都统一为函数

我们在这里把它称为存储过程

它使用的CREATE FUNCTION命令来创建的

我们下面看一下创建存储过程的语法

提供了CREATE FUNCTION 是基本的命令

OR REPLACE是关键字

大家看一下AS后跟了两个$符号

这个符号主要是用于声明存储过程的实际代码的开始

也就是存储过程的代码从哪里开始

双$符号是不能省略的

接下来就是声明存储过程的局部变量

接下来是BEGIN END 是存储过程的函数体的语句

就写在这里面

最后结束时有两个$符号结尾的

这两个$符号是标志存储过程的实际代码已经结束

关键字 LANGUAGE指明存储过程是采用什么语言来写

我们下面看一下前面有几个参数

name是指的要创建的存储过程的名字

OR REPLACE关键字的功能

主要是当其存储过程在数据库中存在时

如果没有or replace时 就会出现错误提示

告诉你存储过程已经存储

如果加上这个关键字

将会覆盖同名的已有的存储过程

现在所创建存储过程的代码来替换原有的存储过程

argmode这一个参数主要是存储过程参数的模式的指定

这个地方可以指定为IN OUT

in是指明存储过程的参数是输入参数

如果参数前面是out

说明是一个返回值的参数

如果前面是INOUT 缺省值是IN

argmode：存储过程参数的模式可以为IN、OUT

或INOUT代表参数既可以作为输入值

也可以作为返回值

缺省时或者不指定时它就是IN

argname是指定形式参数的名字

RETURNS是返回值

也就是存储过程执行完了之后

可以返回一个值

也可以用RETURNS TABLE返回一个二维表

也就是数据库存储过程不仅仅返回单个的值

而且可以返回一张表

表的记录返回给应用

下面我们看一个创建存储过程的示例

创建一个名为countRecords()的存储过程

统计STUDENT表的记录数

这个存储过程的名字叫countRecord

是可以覆盖已有的存储过程

括号里没参数 说明存储过程没有参数

returns integer指明返回整数值

$count$指明存储过程的开始

下面是声明一个count变量

类型为integer BEGIN END之间的语句

是统计student表的记录

将统计的值放入count变量

然后通过return返回

最后$count$后面的LANGUAGE的plpgsql指明

该存储过程是用postgreSQL的pl/SQL语句编写的

下面我们来存储过程编写好之后如何执行它

程序员可以在数据库查询管理器的窗口中执行存储过程

下面的语法用select后面跟上存储过程的名字

括号里面是实参 如果没有参数

括号里面为空 但是括号不能省略

或者采用select * from 存储过程名字

再跟上括号存储过程的参数

这种执行存储过程的方式好像在执行查询语句一样

比如说 我们执行刚才的存储过程

用select跟上存储过程

就可以执行刚才创建的存储过程

或者 select * from countRecords()来执行它

这两种方式都可以

如果程序员需要存储过程调用其它存储过程

语法形式可以采用

selectinto 后面跟一个用户自定义变量

from 我的存储过程名带上实参

下面假设我们新定义存储过程 testExec

这个存储过程返回整型类型

下面定义rec变量

在该存储过程中调用刚才的countRecords存储过程

select into rec countRecords中into rec

是将执行存储过的值放到rec变量里

大家注意一下 如果不关心countRecords的返回值

则可用 PERFORM 调用countRecords存储过程

来代替刚才用select执行存储过程

后面返回结果

LANGUAGE plpgsql是指明是plpgsql语言编写的存储过程

下面看一下删除存储过程

如果程序员需要删除存储过程的语法是什么

删除存储过程用DROP FUNCTION

参数 IF EXISTS是如果指定的存储过程不存在

那么发出提示信息

如果存储就直接删掉

name是要删除的存储过程名字

参数的模式：它有输入参数 输出参数 输入输出参数

大家注意 实际并不注意OUT参数

因为判断存储过程只需要输入参数

因为判断函数的身份只需要输入参数的数据类型

参数的名字 大家注意一下

实际上并不注意参数的名字

argtype是存储过程参数的类型

CASCADE是级联删除依赖于存储过程的对象

如果某触发器依赖该存储过程

这时也会删除依赖于它的触发器

RESTRICT是指如果有其它对象依赖该存储过程

则拒绝执行删除该存储过程命令

假如需要删除前面定义的存储过程

我们就可以执行DROP FUNCTION IF EXISTS testExec

在前面定义的存储过程没有参数

所有括号里是空的

现在我们给大家介绍PL/SQL语句

前面的SQL语言是结构化查询语言

而PL/SQL是过程化的查询语言

P是代表precedure L是代表Language

是过程化查询语言

支持循环 条件多分支等语法

我们下面介绍PL/SQL的基本语法

第一声明变量使用关键字declare 声明变量

前面是变量名

后面是变量类型

大家注意：它的声明变量的顺序和其它语言的不同

比如说C语言声明变量

变量类型是在前面

变量名是在后面

但是大家要注意

如果我们声明变量为记录型

这时可以使用RECORD 关键字声明变量为记录型

record关键字仅仅只是一个占位符 它告诉数据库

该变量将要用于存储某个数据库表的记录

这个变量的结构与存储数据库表的记录的结构

是相对应的我们声明rec变量为记录型

count变量为整型

我们来看一下PL/SQL的条件语句

在PL/pgSQL中有以下三种形式的条件语句

与其他高级语言的条件语句意义相同

第一种形式是 IF-THEN 满足if后面的条件

就执行then后面的语句

如果为假就跳到endif后面的语句

第二种形式是 IF-THEN-ELSE两个分支

如果满足if后面的条件

就执行then后面的语句

如果不满足将执行else后面的语句

第三种形式是 IF-THEN-ELSIF-ELSE

如果满足if后面的条件

就执行then后面的语句

如果不满足将判断elseif后面的条件

如果满足就执行then后面的语句

不满足就执行后面的语句

最后一个else只有在前面所有条件

不满足时才执行最后else的语句

下面我们来看循环语句

第一种采用了LOOP关键字

首先遇到LOOP语句 执行下面的语句

遇到END LOOP就返回继续执行

label是前面定义的标签

将返回到标签地方

一般不写label第二个关键字是EXIT

退出循环 when是指满足某个条件的时候

退出所在的循环

请看下面的例子

loop 和end loop中间的第一句count变量自加1

然后判断count是否大于100

大于100就退出循环

跳到endloop后面的语句执行

如果不大于100

继续循环执行count=count+1

直到count>100为止

下面我们看第三个关键字continue

当其满足条件时

继续执行循环体

但需要注意 当满足条件时

不执行continue后的count=count+1

直接到循环开始执行第一个count=count+1

然后exit when判断是否大于100
直接到循环开始执行第一个count=count+1

然后exit when判断是否大于100

大于100后就退出循环在continue处

如果count大于50时

就要执行两处的count=count+1

while循环语句与前面的loop不一样

while跟了一个表达式

当其表达式为真时

就执行loop和end loop中间的语句

前面多了while条件表达式

例如：当amount_owed > 0 和 balance > 0

在执行loop循环的语句

for循环语句

for循环中for里面有变量后有IN跟一个表达式

也就是变量值依次取in表达式中的值

就执行这个循环

大家看一下这个例子

i in 1...10表示i依次取1到10

raise notice'i is %'，%是占位符

依次输出1到10

下面大家看一下

如果用in reverse

是按照后面指定顺序反向处理

变量命令

当其循环语句里是查询语句时

用for后面跟变量 in 查询语句loop endloop

一直要遍历到查询结果都处理完

申明了一个变量 rec record

rec是记录型的变量

for rec IN SELECT sid sname FROM student

从学生表中查询出学生的学号和学生的姓名

查询之后返回结果集

rec的结构和查询结果的结构一样

循环把结果集的记录一条一条地取到rec中输出出来

如何输出

用类似C语言的printf的raise notice

打印输出rec.sid,rec.sname

直到所有记录都打印

下面看看存储过程的优缺点

首先我们看它有什么优点

使用存储过程可以减少网络通信量

当需要大量数据库记录来计算时

把计算写在存储过程中

存储过程存储在服务器上存储过程的处理

是在服务器上执行的

这样就减少了在网络上传输大量数据的通信量

只需要在网络上传递存储过程的参数和返回的结果

第二 执行速度更快

因为服务器的性能都比较好

第三 更强的适应性

因为我们把业务上容易变化的部分写在存储过程中

但业务发生变化时

高级语言的程序不需要改变

只需要改变存储过程

第四 降低了业务实现与应用程序的耦合

业务处理可以使用存储过程来实现

可以降低应用程序与业务实现的耦合

第五 可以降低了开发的复杂性

通过合理地分解设计确认哪些业务在存储过程中

哪些业务在应用程序客户端实现

设计得合理 就可以降低复杂性

第六 它可以保护数据库元信息

因为存储过程可以隐藏一些数据库的元信息

应用程序不需要了解数据库元信息

起到保护作用

第七 可以增强数据库的安全性

因为存储过程存储在数据库中

可以通过数据库的权限管理

来授予用户对存储过程访问权限

如果用户没有对存储过程的访问权限

就不能访问对应的数据库的数据

这样就增加了对数据库的保护

提高了数据库的安全性

下面我们看一下使用存储过程的缺点

第一 SQL本身是一种结构化查询语言

而存储过程本质上是过程化的程序

面对复杂的业务逻辑

过程化处理逻辑相对比较复杂

而SQL语言的优势是面向数据查询而非业务逻辑的处理

这时使用存储过程很难发挥SQL的优势

第二 如果存储过程的参数或返回数据发生变化

不但需要修改存储过程的代码

而且还需要更新主程序调用存储过程的代码

第三 开发调试比较复杂

由于缺乏支持存储过程的集成开发环境

不像高级语言有集成开发环境

现在还很少有存储过程的开发环境

这样调试比较困难

需要程序员有一定的存储过程开发经验

才能开发出性能优良的存储过程

第四 可移植性差

因为不同数据库语言的语法存在较大差异

这时要把一个应用迁移到另外数据库系统上

需要重新开发存储过程

因此 存储过程的移植性比较差

所以 我们在具体开发中

就要考虑什么样的功能在存储过程中

需要设计者做权衡实现什么样的功能在应用程序客户端实现