6.2-数据库存储过程.ppt

数据库原理及应用,数据库系统原理与开发,电子科技大学,-,陈安龙,6.3,数据库存储过程,了解存储过程的概念,掌握存储过程创建、删除的方法,掌握存储过程的执行方法,掌握,PostgreSQL,的,PL/SQL,基本语法,理解存储过程的优缺点,【,本节的主要内容,】,一,、什么,是存储过程,存储过程（,Stored Procedure,）是一种数据库的对象；,由一组能完成特定功能的,SQL,语句集构成；,是把经常会被重复使用的,SQL,语句逻辑块封装起来，经编译后，存储在数据库服务器端；,当被再次调用时，而不需要再次编译；,当客户端连接到数据库时，用户通过指定存储过程的名字并给出参数，数据库就可以找到相应的存储过程予以调用。,二、创建存储过程,不同的数据库系统创建存储过程的语法存在差异；,许多数据库为创建存储过程和函数提供不同命令；,如,ORACLE,、,MySQL,、,SQL SERVER,等数据库，使用,CREATE PRECEDURE,命令创建存储过程，使用,CREATE FUNCTION,命令创建函数。,PostgreSQL,使用,CREATE FUNCTION,命令创建存储过程。,三、创建存储过程的语法,CREATE,OR REPLACE,FUNCTION,name,(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,，缺省值是,IN,。,（,4,）,argname,：,形式参数的名字。,（,5,）,RETURNS,：,返回值；,RETURNS TABLE,：,返回二维表,（,2,）,OR REPLACE,：,覆盖同名的存储过程；,四、创建存储过程的示例,创建一个名为,countRecords(),的存储过程统计,STUDENT,表的记录数。,CREATE,OR REPLACE,FUNCTION,countRecords(),RETURNS integer,AS$count$,declare,count integer;,BEGIN,SELECT count(*)into count FROM STUDENT;,RETURN count;,END;,$count$LANGUAGE,plpgsql,;,五、执行存储过程,如果程序员需要在查询窗口执行存储过程，语法形式如下：,select,存储过程名（参数）；,或者：,select,*,from,存储过程名（参数）；,例如：,select countRecords();,或者：,select,*,from countRecords();,五、执行存储过程（续）,如果程序员需要存储过程调用其它存储过程，语法形式如下：,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;,六、删除存储过程,如果程序员需要删除存储过程，语法形式如下：,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,：如果有的话，是存储过程参数的类型。,（,6,）,CASCADE,：级联删除依赖于存储过程的对象,(,如触发器,),。,（,7,）,RESTRICT,：如果有任何依赖对象存在，则拒绝删除该函数；这个是缺省值。,六、删除存储过程（,续,）,例子：假如需要删除前面定义的存储过程,testExec(),DROP FUNCTION,IF EXISTS testExec(),七,、,PL/SQL,基本语法,1,、声明局部变量。,变量声明的语法如下：,declare,变量名 变量类型；,如果声明变量为记录类型，变量声明格式为：,variable_name,RECORD,;,注：,RECORD,不是真正的数据类型，只是一个占位符。,例如：,declare,count intger,；,rec,RECORD,；,七,、,PL/SQL,基本,语法（,续,）,2,、条件语句,在,PL/pgSQL,中有以下三种形式的条件语句，与其他高级语言的条件语句意义相同。,1).IF-THEN,IF boolean-expression THEN,statements,END IF;,2).IF-THEN-ELSE,IF boolean-expression THEN,statements,ELSE,3).IF-THEN-ELSIF-ELSE,IF boolean-expression THEN,statements,ELSIF boolean-expression THEN,statements,ELSIF boolean-expression THEN,statements,ELSE,statements,END IF;,七,、,PL/SQL,基本,语法（续）,3,、循环语句,1).LOOP,语句,LOOP,statements,END LOOP label;,2).EXIT,EXIT label WHEN expression;,例如：,LOOP,count=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).FOR,FOR,name IN REVERSE expression.expression,LOOP,statements,END LOOP;,例如：,FOR,i IN 1.10,LOOP,RAISE NOTICE i IS%,i;,END LOOP;,FOR i,IN REVERSE,10.1 LOOP,-do something,END LOOP;,七,、,PL/SQL,基本,语法（续）,4,、遍历命令结果,FOR,record_or_row IN query,LOOP,statements,END LOOP;,FOR,循环可以遍历命令的结果并操作相应的数据，例如：,declare,rec,RECORD,；,FOR rec IN SELECT sid,sname FROM student LOOP,raise notice%-,%-,rec.sid,rec.sname;,END LOOP;,八、存储过程的优缺点,1,、使用存储过程的优点,(1),减少网络通信量,(2),执行速度更快,(3),更强的适应性,(4),降低了业务实现与应用程序的耦合,(5),降低了开发的复杂性,(6),保护数据库元信息,(7),增强了数据库的安全性,八、存储过程的优缺点,2,、使用存储过程的缺点,(1),SQL,本身是一种结构化查询语言，而存储过程本质上是过程化的程序；面对复杂的业务逻辑，过程化处理逻辑相对比较复杂；而,SQL,语言的优势是面向数据查询而非业务逻辑的处理。,(2),如果存储过程的参数或返回数据发生变化，一般需要修改存储过程的代码，同时还需要更新主程序调用存储过程的代码。,(3),开发调试复杂，由于缺乏支持存储过程的集成开发环境，存储过程的开发调试要比一般程序困难。,(4),可移植性差,