Oracle-存储过程、函数、触发器和包

Oracle存储过程、函数、触发器和包主要内容存储过程函数触发器包存储过程和函数存储过程和函数是存入数据库的PL/SQL块。但存储过程和函数不同于已经介绍过的PL/SQL程序块，我们通常把PL/SQL程序称为无名块，而存储过程和函数是以命名的方式存储于数据库中的。和PL/SQL程序相比，存储过程有很多优点，具体归纳如下：存储过程和函数以命名的数据库对象形式存储于数据库的数据字典中，事先经过编译排除错误。可以重复调用、效率更高。存储过程和函数可由数据库提供安全保证，要想使用存储过程和函数，需要有存储过程和函数的所有者的授权，只有被授权的用户或创建者本身才能执行存储过程或调用函数。像其他高级语言的过程和函数一样，可以传递参数给存储过程或函数，参数的传递也有多种方式。存储过程可以有返回值，也可以没有返回值，存储过程的返回值必须通过参数带回；函数有一定的数据类型，像其他的标准函数一样，我们可以通过对函数名的调用返回函数值。创建存储过程创建存储过程，需要有CREATEPROCEDURE或CREATEANYPROCEDURE的系统权限。创建一个存储过程的基本语句如下：CREATE[ORREPLACE]PROCEDURE存储过程名[(参数[IN|OUT|INOUT]数据类型...)]{AS|IS}[说明部分]BEGIN可执行部分[EXCEPTION错误处理部分]END[过程名];可选关键字ORREPLACE表示如果存储过程已经存在，则用新的存储过程覆盖，通常用于存储过程的重建。参数部分用于定义多个参数(如果没有参数，就可以省略)。参数有三种形式：IN、OUT和INOUT。如果没有指明参数的形式，则默认为IN。关键字AS也可以写成IS，后跟过程的说明部分，可以在此定义过程的局部变量。编写存储过程可以使用任何文本编辑器或直接在SQL*Plus环境下进行，编写好的存储过程必须要在SQL*Plus环境下进行编译，生成编译代码，原代码和编译代码在编译过程中都会被存入数据库。编译成功的存储过程就可以在Oracle环境下进行调用了。创建存储过程－说明删除、修改和执行一个存储过程在不需要时可以删除。删除存储过程的人是过程的创建者或者拥有DROPANYPROCEDURE系统权限的人。删除存储过程的语法：DROPPROCEDURE存储过程名；如果要重新编译一个存储过程，则只能是过程的创建者或者拥有ALTERANYPROCEDURE系统权限的人。语法如下：ALTERPROCEDURE存储过程名COMPILE；执行存储过程：执行(或调用)存储过程的人是过程的创建者或是拥有EXECUTEANYPROCEDURE系统权限的人或是被拥有者授予EXECUTE权限的人。方法1：call模式名.存储过程名[(参数...)];方法2：BEGIN模式名.存储过程名[(参数...)];END传递的参数必须与定义的参数类型、个数和顺序一致(如果参数定义了默认值，则调用时可以省略参数)。参数可以是变量、常量或表达式，用法参见下一节。如果是调用本账户下的存储过程，则模式名可以省略。要调用其他账户编写的存储过程，则模式名必须要添加。存储过程没有参数，在调用时，直接写过程名即可。见例子－－输出hello,world!存储过程调用说明createorreplaceprocedureparapro(idinvarchar2default'001',nameinvarchar2default'mly')isbeginDbms_Output.put_line(id);Dbms_Output.put_line(name);endparapro;/*declarev_idvarchar2(100);v_namevarchar2(100);beginparapro();parapro('002');parapro(name='mao');parapro(id=v_id,name=v_name);end;*/参数的作用是向存储过程传递数据，或从存储过程获得返回结果。正确的使用参数可以大大增加存储过程的灵活性和通用性。参数定义语法:参数名参数类型数据类型DEFAULT值；参数的类型有三种IN定义输出变量，传入参数给存储过程。可以是变量或者值OUT定义输出变量，从存储过程中拿到数据。只能是变量INOUT既是输出也是输入参数。只能是变量。例子：分别带上不同类型的参数参数传递主要内容存储过程函数触发器包需要有CREATEFUNCTION或CREATEANYFUNCTION的系统权限。创建存储函数的语法和创建存储过程的类似，即CREATE[ORREPLACE]FUNCTION函数名[(参数[IN]数据类型...)]－－参数是可选的，但只能是IN类型(可以省略)RETURN数据类型－－return返回值类型，不可省略{AS|IS}[说明部分]BEGIN可执行部分RETURN(表达式)[EXCEPTION错误处理部分]END[函数名];创建函数删除一个存储函数在不需要时可以删除，但删除的人应是函数的创建者或者是拥有DROPANYfunction系统权限的人。其语法如下：DROPFUNCTION函数名；重新编译重新编译一个存储函数时，编译的人应是函数的创建者或者拥有ALTERANYfunction系统权限的人。语法如下：ALTERfunction函数名COMPILE；调用函数的调用者应是函数的创建者或拥有EXECUTEANYfunction系统权限的人函数的引用和存储过程不同，函数要出现在程序体中，可以参加表达式的运算或单独出现在表达式中，其形式如下：变量名:=函数名(...)函数的例子删除、修改、调用函数createorreplacefunctiongetnamebyidfun(idinvarchar2)returnvarchar2isResultvarchar2(100);beginselectnameintoresultfromstudentwherestudentid=id;return(Result);exceptionwhenno_data_foundthenreturn'nodatafound!';endgetnamebyidfun;/*declarev_idvarchar2(100):='001';v_namevarchar2(100);beginv_name:=getnamebyidfun(v_id);Dbms_Output.put_line(v_name);end;*/例子可以通过对数据字典的访问来查询存储过程或函数的有关信息，如果要查询当前用户的存储过程或函数的源代码，可以通过对USER_SOURCE数据字典视图的查询得到。USER_SOURCE的结构如下：DESCRIBEUSER_SOURCEselectTEXTfromuser_sourceWHERENAME='EMP_COUNT';DESCRIBEGET_EMP_NAMESELECTSTATUSFROMUSER_OBJECTSWHEREOBJECT_NAME='EMP_LIST';存储过程和函数的查看主要内容存储过程函数触发器包触发器介绍触发器是一种特殊的存储过程，它在插入，删除或修改特定表中的数据时（事件）触发执行.触发器的触发事件分可为3类，分别是DML事件、DDL事件和数据库事件。每类事件包含若干个事件，如下表所示。数据库的事件是具体的，在创建触发器时要指明触发的事件。种类关键字含义INSERT在表或视图中插入数据时触发UPDATE修改表或视图中的数据时触发DML事件(3种)DELETE在删除表或视图中的数据时触发CREATE在创建新对象时触发ALTER修改数据库或数据库对象时触发DDL事件(3种)DROP删除对象时触发STARTUP数据打开时触发SHUTDOWN在使用NORMAL或IMMEDIATE选项关闭数据库时触发LOGON当用户连接到数据库并建立会话时触发LOGOFF当一个会话从数据库中断开时触发数据库事件(5种)SERVERERROR发生服务器错误时触发触发器类型触发器的类型可划分为4种：数据操纵语言(DML)触发器、替代(INSTEADOF)触发器、数据定义语言(DDL)触发器和数据库事件触发器。种类简称作用数据操纵语言触发器DML触发器创建在表上，由DML事件引发的触发器替代触发器INSTEADOF触发器创建在视图上，用来替换对视图进行的插入、删除和修改操作数据定义语言触发器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触发器需要CREATETRIGGER系统权限。创建DML触发器的语法如下：CREATE[ORREPLACE]TRIGGER触发器名{BEFORE|AFTER|INSTEADOF}触发事件1[OR触发事件2...]ON表名WHEN触发条件[FOREACHROW]声明部分BEGIN主体部分END;ORREPLACE：表示如果存在同名触发器，则覆盖原有触发器。BEFORE、AFTER和INSTEADOF：说明触发器的类型。WHEN触发条件：表示当该条件满足时，触发器才能执行。触发事件：指INSERT、DELETE或UPDATE事件，事件可以并行出现，中间用OR连接。对于UPDATE事件，还可以用以下形式表示对某些列的修改会引起触发器的动作：UPDATEOF列名1，列名2...ON表名：表示为哪一个表创建触发器。FOREACHROW：表示触发器为行级触发器，省略则为语句级触发器。DML触发器创建－说明触发器的创建者或具有DROPANYTIRGGER系统权限的人才能删除触发器。删除触发器的语法如下：DROPTIRGGER触发器名可以通过命令设置触发器的可用状态，使其暂时关闭或重新打开，即当触发器暂时不用时，可以将其置成无效状态，在使用时重新打开。该命令语法如下：ALTERTRIGGER触发器名{DISABLE|ENABLE}其中，DISABLE表示使触发器失效，ENABLE表示使触发器生效。同存储过程类似，触发器可以用SHOWERRORS检查编译错误。DML触发器修改和删除例子oracle设置自增字段操作用触发器记录一张表的