数据库存储过程课件.ppt

1、1 数据库原理及应用数据库系统原理与开发电子科技大学-陈安龙 了解存储过程的概念 掌握存储过程创建、删除的方法 掌握存储过程的执行方法 掌握PostgreSQL的PL/SQL基本语法 理解存储过程的优缺点2 数据库原理及应用数据库系统原理与开发电子科技大学-陈安龙一一、什么、什么是存储过程是存储过程存储过程（Stored Procedure）是一种数据库的对象；由一组能完成特定功能的SQL 语句集构成；是把经常会被重复使用的SQL语句逻辑块封装起来，经编译后，存储在数据库服务器端；当被再次调用时，而不需要再次编译；当客户端连接到数据库时，用户通过指定存储过程的名字并给出参数，数据库就可以找到相

2、应的存储过程予以调用。3 数据库原理及应用数据库系统原理与开发电子科技大学-陈安龙二、创建存储过程二、创建存储过程不同的数据库系统创建存储过程的语法存在差异；许多数据库为创建存储过程和函数提供不同命令；如ORACLE、MySQL、SQL SERVER等数据库，使用CREATE PRECEDURE命令创建存储过程，使用CREATE FUNCTION命令创建函数。PostgreSQL使用CREATE FUNCTION命令创建存储过程。4 数据库原理及应用数据库系统原理与开发电子科技大学-陈安龙三、创建存储过程的语法三、创建存储过程的语法CREATE OR REPLACE FUNCTION name

3、 (argmode argname argtype DEFAULT|=default_expr ,.)RETURNS retype|RETURNS TABLE(column_name column_type,.)AS$/$用于声明存储过程的实际代码的开始用于声明存储过程的实际代码的开始DECLARE -声明段声明段BEGIN -函数体语句函数体语句END;$LANGUAGE lang_name;/$表明代码的结束表明代码的结束,LANGUAGE后面指明所用的编程语言后面指明所用的编程语言（1）name：要创建的存储过程名；（3）argmode：存储过程参数的模式可以为IN、OUT或INOUT，

4、缺省值是IN。（4）argname：形式参数的名字。（5）RETURNS：返回值；RETURNS TABLE：返回二维表（2）OR REPLACE：覆盖同名的存储过程；5 数据库原理及应用数据库系统原理与开发电子科技大学-陈安龙四、创建存储过程的示例四、创建存储过程的示例创建一个名为countRecords()的存储过程统计STUDENT表的记录数。CREATE OR REPLACE FUNCTION countRecords()RETURNS integer AS$count$declare count integer;BEGIN SELECT count(*)into count FROM

5、 STUDENT;RETURN count;END;$count$LANGUAGE plpgsql;6 数据库原理及应用数据库系统原理与开发电子科技大学-陈安龙五、执行存储过程五、执行存储过程如果程序员需要在查询窗口执行存储过程，语法形式如下：select 存储过程名（参数）；或者：select *from 存储过程名（参数）；例如：select countRecords();或者：select *from countRecords();7 数据库原理及应用数据库系统原理与开发电子科技大学-陈安龙五、执行存储过程（续）五、执行存储过程（续）如果程序员需要存储过程调用其它存储过程，语法形式如下：

6、select into 自定义变量 from 存储过程名（参数）；CREATE OR REPLACE FUNCTION testExec()returns integer AS$declare rec integer;BEGIN select into rec countRecords();/如果不关心如果不关心countRecords()的返回值，则可用的返回值，则可用 PERFORM countRecords()代替代替;return rec;END;$LANGUAGE plpgsql;8 数据库原理及应用数据库系统原理与开发电子科技大学-陈安龙六、删除存储过程六、删除存储过程如果程序员需

7、要删除存储过程，语法形式如下：DROP FUNCTION IF EXISTS name(argmode argname argtype,.)CASCADE|RESTRICT 主要参数：（1）IF EXISTS：如果指定的存储过程不存在，那么发出提示信息。（2）name：现存的存储过程名称。（3）argmode：参数的模式：IN(缺省),OUT,INOUT,VARIADIC。请注意，实际并不注意OUT参数，因为判断存储过程的身份只需要输入参数。（4）argname：参数的名字。请注意，实际上并不注意参数的名字，因为判断函数的身份只需要输入参数的数据类型。（5）argtype：如果有的话，是存储过

8、程参数的类型。（6）CASCADE：级联删除依赖于存储过程的对象(如触发器)。（7）RESTRICT：如果有任何依赖对象存在，则拒绝删除该函数；这个是缺省值。9 数据库原理及应用数据库系统原理与开发电子科技大学-陈安龙六、删除存储过程（六、删除存储过程（续续）例子：假如需要删除前面定义的存储过程 testExec()DROP FUNCTION IF EXISTS testExec()10 数据库原理及应用数据库系统原理与开发电子科技大学-陈安龙七七、PL/SQL基本语法基本语法1、声明局部变量。变量声明的语法如下：declare 变量名 变量类型；如果声明变量为记录类型，变量声明格式为：var

9、iable_name RECORD;注：RECORD不是真正的数据类型，只是一个占位符。例如：declare count intger；rec RECORD；11 数据库原理及应用数据库系统原理与开发电子科技大学-陈安龙七七、PL/SQL基本基本语法（语法（续续）2、条件语句 在PL/pgSQL中有以下三种形式的条件语句，与其他高级语言的条件语句意义相同。1).IF-THEN IF boolean-expression THEN statementsEND IF;2).IF-THEN-ELSEIF boolean-expression THEN statementsELSE 3).IF-THE

10、N-ELSIF-ELSEIF boolean-expression THEN statementsELSIF boolean-expression THEN statementsELSIF boolean-expression THEN statementsELSE statementsEND IF;12 数据库原理及应用数据库系统原理与开发电子科技大学-陈安龙七七、PL/SQL基本基本语法（续）语法（续）3、循环语句1).LOOP 语句LOOP statementsEND LOOP label;2).EXIT EXIT label WHEN expression;例如：LOOP count=

11、count+1;EXIT WHEN count 100;END LOOP;3).CONTINUE CONTINUE label WHEN expression;例如：LOOP count=count+1;EXIT WHEN count 100;CONTINUE WHEN count 0 AND balance 0 LOOP -do something END LOOP;5).FORFOR name IN REVERSE expression.expression LOOP statementsEND LOOP;例如：FOR i IN 1.10 LOOP RAISE NOTICE i IS%,i

12、;END LOOP;FOR i IN REVERSE 10.1 LOOP -do something END LOOP;14 数据库原理及应用数据库系统原理与开发电子科技大学-陈安龙七七、PL/SQL基本基本语法（续）语法（续）4、遍历命令结果FOR record_or_row IN query LOOP statementsEND LOOP;FOR循环可以遍历命令的结果并操作相应的数据，例如：declare rec RECORD；FOR rec IN SELECT sid,sname FROM student LOOP raise notice%-,%-,rec.sid,rec.sname;

13、END LOOP;15 数据库原理及应用数据库系统原理与开发电子科技大学-陈安龙八、存储过程的优缺点八、存储过程的优缺点1、使用存储过程的优点 (1)减少网络通信量 (2)执行速度更快 (3)更强的适应性 (4)降低了业务实现与应用程序的耦合 (5)降低了开发的复杂性 (6)保护数据库元信息 (7)增强了数据库的安全性16 数据库原理及应用数据库系统原理与开发电子科技大学-陈安龙八、存储过程的优缺点八、存储过程的优缺点2、使用存储过程的缺点 (1)SQL本身是一种结构化查询语言，而存储过程本质上是过程化的程序；面对复杂的业务逻辑，过程化处理逻辑相对比较复杂；而SQL语言的优势是面向数据查询而非业务逻辑的处理。(2)如果存储过程的参数或返回数据发生变化，一般需要修改存储过程的代码，同时还需要更新主程序调用存储过程的代码。(3)开发调试复杂，由于缺乏支持存储过程的集成开发环境，存储过程的开发调试要比一般程序困难。(4)可移植性差