Oracle存储过程、函数、触发器和包ppt课件.ppt

Oracle,存储过程、函数、触发器和包,主要内容,存储过程函数触发器包,存储过程和函数,存储过程和函数是存入数据库的PL/SQL块。但存储过程和函数不同于已经介绍过的PL/SQL程序块，我们通常把PL/SQL程序称为无名块，而存储过程和函数是以命名的方式存储于数据库中的。和PL/SQL程序相比，存储过程有很多优点，具体归纳如下：存储过程和函数以命名的数据库对象形式存储于数据库的数据字典中，事先经过编译排除错误。可以重复调用、效率更高。存储过程和函数可由数据库提供安全保证，要想使用存储过程和函数，需要有存储过程和函数的所有者的授权，只有被授权的用户或创建者本身才能执行存储过程或调用函数。像其他高级语言的过程和函数一样，可以传递参数给存储过程或函数，参数的传递也有多种方式。存储过程可以有返回值，也可以没有返回值，存储过程的返回值必须通过参数带回；函数有一定的数据类型，像其他的标准函数一样，我们可以通过对函数名的调用返回函数值。,创建存储过程,创建存储过程，需要有CREATE PROCEDURE或CREATE ANY PROCEDURE的系统权限。创建一个存储过程的基本语句如下：CREATE OR REPLACE PROCEDURE 存储过程名(参数IN|OUT|IN OUT 数据类型.)AS|IS说明部分BEGIN可执行部分EXCEPTION 错误处理部分END 过程名;,可选关键字OR REPLACE 表示如果存储过程已经存在，则用新的存储过程覆盖，通常用于存储过程的重建。参数部分用于定义多个参数(如果没有参数，就可以省略)。参数有三种形式：IN、OUT和IN OUT。如果没有指明参数的形式，则默认为IN。关键字AS也可以写成IS，后跟过程的说明部分，可以在此定义过程的局部变量。编写存储过程可以使用任何文本编辑器或直接在SQL*Plus环境下进行，编写好的存储过程必须要在SQL*Plus环境下进行编译，生成编译代码，原代码和编译代码在编译过程中都会被存入数据库。编译成功的存储过程就可以在Oracle环境下进行调用了。,创建存储过程说明,删除、修改和执行,一个存储过程在不需要时可以删除。删除存储过程的人是过程的创建者或者拥有DROP ANY PROCEDURE系统权限的人。删除存储过程的语法：DROP PROCEDURE 存储过程名；如果要重新编译一个存储过程，则只能是过程的创建者或者拥有ALTER ANY PROCEDURE系统权限的人。语法如下：ALTER PROCEDURE 存储过程名 COMPILE；执行存储过程：执行(或调用)存储过程的人是过程的创建者或是拥有EXECUTE ANY PROCEDURE系统权限的人或是被拥有者授予EXECUTE权限的人。 方法1：call 模式名.存储过程名(参数.); 方法2：BEGIN 模式名.存储过程名(参数.);END,传递的参数必须与定义的参数类型、个数和顺序一致(如果参数定义了默认值，则调用时可以省略参数)。参数可以是变量、常量或表达式，用法参见下一节。如果是调用本账户下的存储过程，则模式名可以省略。要调用其他账户编写的存储过程，则模式名必须要添加。存储过程没有参数，在调用时，直接写过程名即可。见例子输出hello,world!,存储过程调用说明,create or replace procedure parapro(id in varchar2 default 001 , name in varchar2 default mly) isbegin Dbms_Output.put_line(id); Dbms_Output.put_line(name);end parapro;/*declare v_id varchar2(100) ; v_name varchar2(100);beginparapro(); parapro(002);parapro(name=mao);parapro(id=v_id,name=v_name);end;*/,参数的作用是向存储过程传递数据，或从存储过程获得返回结果。正确的使用参数可以大大增加存储过程的灵活性和通用性。参数定义语法:参数名 参数类型 数据类型 DEFAULT 值；参数的类型有三种IN 定义输出变量，传入参数给存储过程。可以是变量或者值OUT 定义输出变量，从存储过程中拿到数据。只能是变量IN OUT 既是输出也是输入参数。只能是变量。例子：分别带上不同类型的参数,参数传递,主要内容,存储过程函数触发器包,需要有CREATE FUNCTION或CREATE ANY FUNCTION的系统权限。创建存储函数的语法和创建存储过程的类似，即CREATE OR REPLACE FUNCTION 函数名(参数IN 数据类型.) 参数是可选的，但只能是IN类型(可以省略)RETURN 数据类型return 返回值类型，不可省略AS|IS说明部分BEGIN可执行部分RETURN (表达式)EXCEPTION错误处理部分END 函数名;,创建函数,删除一个存储函数在不需要时可以删除，但删除的人应是函数的创建者或者是拥有DROP ANY function系统权限的人。其语法如下：DROP FUNCTION 函数名；重新编译重新编译一个存储函数时，编译的人应是函数的创建者或者拥有ALTER ANY function系统权限的人。语法如下：ALTER function 函数名 COMPILE；调用函数的调用者应是函数的创建者或拥有EXECUTE ANY function系统权限的人函数的引用和存储过程不同，函数要出现在程序体中，可以参加表达式的运算或单独出现在表达式中，其形式如下：变量名:=函数名(.)函数的例子,删除、修改、调用函数,create or replace function getnamebyidfun(id in varchar2 ) return varchar2 is Result varchar2(100);begin select name into result from student where studentid =id; return(Result);exception when no_data_found then return no data found! ;end getnamebyidfun;/*declare v_id varchar2(100) :=001; v_name varchar2(100);beginv_name := getnamebyidfun(v_id);Dbms_Output.put_line(v_name);end;*/,例子可以通过对数据字典的访问来查询存储过程或函数的有关信息，如果要查询当前用户的存储过程或函数的源代码，可以通过对USER_SOURCE数据字典视图的查询得到。USER_SOURCE的结构如下：DESCRIBE USER_SOURCEselect TEXT from user_source WHERE NAME=EMP_COUNT;DESCRIBE GET_EMP_NAMESELECT STATUS FROM USER_OBJECTS WHERE OBJECT_NAME=EMP_LIST;,存储过程和函数的查看,主要内容,存储过程函数触发器包,触发器介绍,触发器是一种特殊的存储过程，它在插入，删除或修改特定表中的数据时（事件）触发执行.触发器的触发事件分可为3类，分别是DML事件、DDL事件和数据库事件。每类事件包含若干个事件，如下表所示。数据库的事件是具体的，在创建触发器时要指明触发的事件。,触发器类型,触发器的类型可划分为4种：数据操纵语言(DML)触发器、替代(INSTEAD OF)触发器、数据定义语言(DDL)触发器和数据库事件触发器。,DML触发器,DML触发器是定义在表上的触发器，由DML事件引发。编写DML触发器的要素是：确定触发的表，即在其上定义触发器的表。确定触发的事件，DML触发器的触发事件有INSERT、UPDATE和DELETE三种确定触发时间。触发的时间有BEFORE和AFTER两种，分别表示触发动作发生在DML语句执行之前和语句执行之后。确定触发级别，有语句级触发器和行级触发器两种。语句级触发器表示SQL语句只触发一次触发器，行级触发器表示SQL语句影响的每一行都要触发一次。如果有多个触发器被定义成为相同时间、相同事件触发,则最后定义的触发器被触发，其他触发器不执行。,其它说明,一个触发器可由多个不同的DML操作触发。在触发器中，可用INSERTING、DELETING、UPDATING谓词来区别不同的DML操作。这些谓词可以在IF分支条件语句中作为判断条件来使用。在行级触发器中，用:new 和:old(称为伪记录)来访问数据变更前后的值。INSERT语句插入一条新记录，所以没有:old记录DELETE语句删除掉一条已经存在的记录，没有:new记录UPDATE语句既有:old，也有:new，代表修改前后的记录引用具体的某一列的值的方法是：:old.字段名或:new.字段名触发器体内禁止使用COMMIT、ROLLBACK、SAVEPOINT语句，也禁止直接或间接地调用含有上述语句的存储过程。,DML触发器创建,创建DML触发器需要CREATE TRIGGER系统权限。创建DML触发器的语法如下：CREATE OR REPLACE TRIGGER 触发器名BEFORE|AFTER|INSTEAD OF 触发事件1 OR 触发事件2.ON 表名WHEN 触发条件FOR EACH ROW声明部分BEGIN主体部分END;,OR REPLACE：表示如果存在同名触发器，则覆盖原有触发器。BEFORE、AFTER和INSTEAD OF：说明触发器的类型。WHEN 触发条件：表示当该条件满足时，触发器才能执行。 触发事件：指INSERT、DELETE或UPDATE事件，事件可以并行出现，中间用OR连接。对于UPDATE事件，还可以用以下形式表示对某些列的修改会引起触发器的动作：UPDATE OF 列名1，列名2.ON 表名：表示为哪一个表创建触发器。FOR EACH ROW：表示触发器为行级触发器，省略则为语句级触发器。,DML触发器创建说明,触发器的创建者或具有DROP ANY TIRGGER系统权限的人才能删除触发器。删除触发器的语法如下：DROP TIRGGER 触发器名可以通过命令设置触发器的可用状态，使其暂时关闭或重新打开，即当触发器暂时不用时，可以将其置成无效状态，在使用时重新打开。该命令语法如下：ALTER TRIGGER 触发器名 DISABLE|ENABLE其中，DISABLE表示使触发器失效，ENABLE表示使触发器生效。同存储过程类似，触发器可以用SHOW ERRORS 检查编译错误。,DML触发器修改和删除,例子,oracle设置自增字段操作用触发器记录一张表的操作日志CREATE TABLE logs(LOG_ID NUMBER(10) PRIMARY KEY,LOG_TABLE VARCHAR2(10) NOT NULL,LOG_DML VARCHAR2(10),LOG_KEY_ID NUMBER(10),LOG_DATE DATE,LOG_USER VARCHAR2(15);设置LOG_ID自增，添加行级触发器注意 when条件中new引用不需要:,特殊字段,Insert中:new表示新添的行Update中:new表示修改的行，:old表示未修改前的行Delete中:old表示要删除的行,语法,(Before|after) insert or update or delete on usersBefore insert or update of name on usersInstead of 只适用于视图,例子,create or replace trigger rowtrigger before insert or update or delete on users for each row when (new.name=fff or new.password=ggg)declare - local variables herebegin case when inserting then dbms_output.put_line(inserting); dbms_output.put_line(:new.name);when updating then dbms_output.put_line(updating); dbms_output.put_line(:new.name); dbms_output.put_line(:old.name);when deleting then dbms_output.put_line(deleting); dbms_output.put_line(:old.name);end case;end rowtrigger;,主要内容,存储过程函数触发器包,包的概念和组成,包是用来存储相关程序结构的对象，它存储于数据字典中。包由两个分离的部分组成：包头(PACKAGE)和包体(PACKAGE BODY)。包头是包的说明部分，是对外的操作接口，对应用是可见的；包体是包的代码和实现部分，对应用来说是不可见的黑盒。包中可以包含的程序结构如表所示：,包的组成,说明部分可以出现在包的三个不同的部分出现在包头中的称为公有元素出现在包体中的称为私有元素出现在包体的过程(或函数)中的称为局部变量。在包体中出现的过程或函数，如果需要对外公用，就必须在包头中说明，包头中的说明应该和包体中的说明一致。,包可以方便地将存储过程和函数组织到一起，每个包又是相互独立的。在不同的包中，过程、函数都可以重名，这解决了在同一个用户环境中命名的冲突问题。包增强了对存储过程和函数的安全管理，对整个包的访问权只需一次授予。在同一个会话中，公用变量的值将被保留，直到会话结束。 区分了公有过程和私有过程，包体的私有过程增加了过程和函数的保密性。包在被首次调用时，就作为一个整体被全部调入内存，减少了多次访问过程或函数的I/O次数。,包的优点,包由包头和包体两部分组成，包的创建应该先创建包头部分，然后创建包体部分。创建、删除和编译包的权限同创建、删除和编译存储过程的权限相同。创建包头的简要语句如下：CREATE OR REPLACE PACKAGE 包名IS|AS公有变量定义公有类型定义公有游标定义公有异常定义函数说明过程说明END;,创建包和包体,创建包体的简要语法如下：CREATE OR REPLACE PACKAGE BODY 包名IS|AS私有变量定义私有类型定义私有游标定义私有异常定义函数定义过程定义END;,创建包和包体,删除包头：DROP PACKAGE 包头名删除包体：DROP PACKAGE BODY 包体名重新编译包头：ALTER PACKAGE 包名 COMPILE PACKAGE重新编译包体：ALTER PACKAGE 包名 COMPILE PACKAGE BODY在包头中说明的对象可以在包外调用，调用的方法和调用单独的过程或函数的方法基本相同，惟一的区别就是要在调用的过程或函数名前加上包的名字(中间用“.”分隔)。但要注意，不同的会话将单独对包的公用变量进行初始化，所以不同的会话对包的调用属于不同的应用。,包的其它操作,作业,1.对学生成绩表的增删改查用存储过程和函数实现要求带参数和返回值,注意处理exception.2.实现一个触发器,一张销售表(id,时间,销售额,销售员),一张销售统计表(年,月,日,总销售额).在每次给销售表添加记录的时候,自动把数据累加到统计表.注意处理异常情况.,The end,谢 谢,