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

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