8章 Oracle 的其它对象

第8章Oracle的其它对象8.1序列8.2同义词8.3存储过程8.4触发器8.5函数8.6包数据库对对象简介介Oracle数数据库库对象又又称模式式对象数据库对对象是逻逻辑结构构的集合合，最基基本的数数据库对对象是表表其他数据据库对象象包括：：数据库对对象同义词序列视图索引8.1..1在在OMEC中中创建序序列8.1..2使使用SQL命命令创建建序列8.1..3维维护序序列8.1序列序列序列是用用于生成成唯一、、连续序序号的对对象序列可以以是升序序的，也也可以是是降序的的使用CREATESEQUENCE语句句创建序序列SQL>>CREATESEQUENCEtoys_seqSTARTWITH10INCREMENTBY10MAXVALUE2000MINVALUE10NOCYCLECACHE10;指定第一一个序号号从10开始指定序号号之间的的间隔为为10表示序列列的最大大值为2000表示序列列的最小小值为10在达到最最大值后后停止生生成下一一个值指定内存存中预先先分配的的序号数数8.1..2使用SQL命令创建建序列CREATESEQUENCEsequence__name[INCREMENTBYn]][STARTWITHn][MAXVALUEn||NOMAXVALUE]][MINVALUEn||NOMINVALUE][CYCLE||NOCYCLE][CACHEn||NOCACHE]][ORDER||NOORDER]8.1..2使用SQL命令创建建序列参数说明明其中sequence_name表表示创建建的序列列名称，，n表示示任意正正整数值值。INCREMENTBY：：指定序序列递增增或递减减的间隔隔数值，，缺省值值为1。。STARTWITH：序列列的起始始值。MINVALUE：序序列可允允许的最最小值。。若指定定为NOMINVALUE，，则对升升序序列列将使用用默认值值1，而而对降序序序列使使用默认认值-1.0E28。。8.1..2使用SQL命令创建建序列参数说明明MAXVALUE：序序列可允允许的最最大值。。对降序序序列，，将使用用该序列列默认的的最大值值．若指指定为NOMAXVALUE，则将将对升序序序列使使用默认认值-1.0E28((-10的28次方))．而对对降序序序列使用用默认值值-1。。CYCLE：指指定在达达到序列列最小值值或最大大值之后后，序列列应继续续生成值值。若指指定为NOCYCLE，则序序列将在在达到最最小值或或最大值值后停止止生成任任何值。。缺省值值是NOCYCLE。。8.1..2使用SQL命令创建建序列CACHE：由由数据库库预分配配并存储储序列值值的数目目。默认认值为为20。若指指定为NOCACHE，则不不预分配配序列值值的数目目。ORDER：缺缺省值为为NOORDER。指指定ORDER参数使使Oracle9i在并并行环境境下，按按照请求求的顺序序来产生生序列号号。8.1..3维护序序列列1.查查询序列列信息序列的信信息可以以在ALL_SEQUENCE和USER_SEQUENCE数据字典典中找到到，其中中USER_SEQUENCE的数数据结构构如下表表所示。。8.1..3维护序序列列列名具体含义SEQUENCE_NAMEMIN_VALUEMAX_VALUEINCREMENT_BYCYCLE_FLAGORDER_FLAGCACHE_SIZELAST_NUMBER序列名最小值最大值增量循环标志次序标志缓冲大小最后一个数访问序列列通过序列列的伪列列来访问问序列的的值NEXTVAL返回回序列的的下一个个值CURRVAL返回回序列的的当前值值SQL>>INSERTINTOtoys((toyid,toyname,toyprice)VALUES((toys_seq.NEXTVAL,‘‘TWENTY’,25));SQL>>INSERTINTOtoys((toyid,toyname,toyprice)VALUES((toys_seq.NEXTVAL,’’MAGICPENCIL’,75));指定序列列的下一一个值SQL>>SELECTtoys_seq.CURRVALFROMdual;;检索序列列的当前前值8.1..3维护序序列列2．修改改序列当修改序序列时，，注意不不要使依依赖于序序列号的的主键不不唯一。。利用OEMC修修改序列列在OEMC的的界面中中，选择择要修改改的序列列，单击击鼠标右右键，从从弹出的的快捷菜菜单中选选择“查查看/编编辑详细细资料””，激活活“编辑辑序列””窗口，，在窗口口中对序序列进行行修改即即可。8.1..3维护序序列列利用SQL命令修改改序列ALTERSEQUENCEsequence_name[INCREMENTBYn]][STARTWITHn][MAXVALUEn||NOMAXVALUE]][MINVALUEn||NOMINVALUE]][CYCLE||NOCYCLE][CACHEn||NOCACHE]][ORDER||NOORDER]8.1..3维护序序列列3．删除序序列在OEMC的界面中中，选择择要删除除的序列列，单击鼠标标右键，，从弹出出的快捷捷菜单中中选择“移移去”即即可。用SQL语句删除除一个序序列和删删除别的的对象类类似。其语法形形式是DROPSEQUENCEsequence_name更改和删删除序列列SQL>>ALTERSEQUENCEtoys_seqMAXVALUE5000CYCLE;使用ALTERSEQUENCE语句修改改序列，，不能更改改序列的的STARTWITH参数使用DROPSEQUENCE语句删除除序列SQL>>DROPSEQUENCEtoys_seq;8.2..1在OEMC中创建同同义词8.2..2使用SQL命令创建建同义词词8.2同义词词同义词3-1同义词是是现有对对象的一一个别名名。简化SQL语句句隐藏对象象的名称称和所有有者提供对对对象的公公共访问问同义词共共有两种种类型：：同义词私有同义义词公有同义义词私有同义义词只能能在其模模式内访访问，且且不能与与当前模模式的对对象同名名。公有同义义词可被被所有的的数据库库用户访访问。同义词3-2CREATESYNONYMempFORSCOTT.emp;SCOTT.emp的别名模式名表名私有同义义词公有同义义词CREATEPUBLICSYNONYMemp__synFORSCOTT.emp;同义词名名称同义词3-3创建或替替换现有有的同义义词CREATEORREPLACESYNONYMemp__synFORSCOTT.emp;替换现有有的同义义词SQL>>DROPSYNONYMemp;SQL>>DROPPUBLICSYNONYMemp__syn;删除同义义词8.3..1在OEMC中创建存存储过程程8.3..2使用SQL命令创建建存储过过程8.3存储过过程程子程序2-1命名的PL//SQL块，，编译并并存储在在数据库库中。子程序的的各个部部分：声明部分分可执行部部分异常处理理部分((可选))子程序的的分类：：过程－－执行行某些操操作函数－－执行行操作并并返回值值子程序2-2子程序的的优点：：模块化将程序分分解为逻逻辑模块块可重用性性可以被任任意数目目的程序序调用可维护性性简化维护护操作安全性通过设置置权限，，使数据据更安全全过程8-1过程是用用于完成成特定任任务的子子程序例如：前往售票票厅询问关于于车票的的信息排队等候候在柜台购购买车票票过程8-2创建过程程的语法法：CREATE[[ORREPLACE]]PROCEDURE<procedurename>>[((<parameterlist>)]]IS|AS<localvariabledeclaration>>BEGIN<executablestatements>[EXCEPTION<exceptionhandlers>]END;;创建过程程，可指指定运行行过程需需传递的的参数处理异常常包括在过过程中要要执行的的语句过程8-3CREATEORREPLACEPROCEDUREfind_emp(emp__noNUMBER))ASempnameVARCHAR2(20);;BEGINSELECTenameINTOempnameFROMEMPWHEREempno=emp__no;DBMS_OUTPUT.PUT__LINE(''雇员姓名名是'||empname);EXCEPTIONWHENNO_DATA__FOUNDTHENDBMS_OUTPUT.PUT__LINE(('雇员编号号未找到到');ENDfind_emp;/过程8-4过程参数数的三种种模式：：IN用于接受受调用程程序的值值默认的参参数模式式OUT用于向调调用程序序返回值值INOUT用于接受受调用程程序的值值，并向向调用程程序返回回更新的的值过程8-5SQL>>CREATEORREPLACEPROCEDUREitemdesc(item_codeINVARCHAR2)ISv_itemdescVARCHAR2(5);BEGINSELECTitemdescINTOv_itemdescFROMitemfileWHEREitemcode=item_code;DBMS_OUTPUT.PUT__LINE(item_code||'项目的说说明为'||v_itemdesc);END;;/SQL>>SETSERVEROUTPUTONSQL>>EXECUTEitemdesc(('i201'');执行过程程的语法法：EXECUTEprocedure_name(parameters__list);;过程8-6SQL>>CREATEORREPLACEPROCEDUREtest(value1INVARCHAR2,value2OUTNUMBER))ISidentityNUMBER;BEGINSELECTITEMRATEINTOidentityFROMitemFileWHEREitemcode=value1;IFidentity<<200THENvalue2::=100;ENDIF;;END;;DECLAREvalue1VARCHAR2((5)::=''i202'';value2NUMBER;BEGINtest(value1,,value2);;DBMS_OUTPUT.PUT__LINE(''value2的值为'||TO_CHAR(value2)));END;;/过程8-7CREATEORREPLACEPROCEDUREswap(p1INOUTNUMBER,,p2INOUTNUMBER))ISv_tempNUMBER;;BEGINv_temp:=p1;p1::=p2;p2::=v_temp;END;;/SQL>>SETSERVEROUTONSQL>>DECLAREnum1NUMBER::=100;;num2NUMBER::=200;;BEGINswap(num1,,num2));DBMS_OUTPUT.PUT__LINE(''num1=='|||num1);;DBMS_OUTPUT.PUT__LINE(''num2=='|||num2);;END;;/过程8-8将过程的的执行权权限授予予其他用用户：删除过程程：SQL>>GRANTEXECUTEONfind_empTOMARTIN;;SQL>>GRANTEXECUTEONswapTOPUBLIC;;SQL>>DROPPROCEDUREfind_emp;函数4-1函数是可可以返回回值的命命名的PL//SQL子程程序。创建函数数的语法法：CREATE[[ORREPLACE]]FUNCTION<functionname>[[(param1,,param2)]RETURN<<datatype>IS||AS[localdeclarations]BEGINExecutableStatements;;RETURNresult;EXCEPTIONExceptionhandlers;;END;;函数4-2定义函数数的限制制：函数只能能接受IN参数，而而不能接接受INOUT或OUT参数形参不能能是PL/SQL类型函数的返返回类型型也必须须是数据据库类型型访问函数数的两种种方式：：使用PL/SQL块使用SQL语句函数4-3创建函数数：从SQL语语句调用用函数：：CREATEORREPLACEFUNCTIONfun__helloRETURNVARCHAR2ISBEGINRETURN''朋友，您您好';END;;/SQL>>SELECTfun__helloFROMDUAL;;函数4-4CREATEORREPLACEFUNCTIONitem_price_range(priceNUMBER)RETURNVARCHAR2ASmin__priceNUMBER;;max__priceNUMBER;;BEGINSELECTMAX(ITEMRATE),MIN(ITEMRATE)INTOmax__price,min__priceFROMitemfile;IFprice>>=min__priceANDprice<<=max__priceTHENRETURN''输入的单单价介于于最低价价与最高高价之间间';ELSERETURN''超出范围围';ENDIF;;END;;/DECLAREPNUMBER::=300;;MSGVARCHAR2((200);BEGINMSG::=item_price_range(300));DBMS_OUTPUT.PUT__LINE(MSG));END;;/过程和函函数的比比较过程函数作为PL/SQL语句执行作为表达式的一部分调用在规格说明中不包含RETURN子句必须在规格说明中包含RETURN子句不返回任何值必须返回单个值可以包含RETURN语句，但是与函数不同，它不能用于返回值必须包含至少一条RETURN

语句函数4-5删除函数数从SQL表达式式调用函函数有以以下限制制：从SELECT语句调调用的任任何函数数均不能能修改数数据库表表当远程执执行或并并行执行行时，函函数不得得读取或或写入程程序包中中变量的的值。从SELECT，VALUES或SET子子句调用用的函数数可以写写入程序序包中变变量，所所有其它它子句中中的函数数均不能能写入程程序包。。如果函数数调用执执行UPDATE的存存储过程程，则该该函数不不能在SQL语语句内使使用。DROPFUNCTIONitem__price__range；；8.4..l利用OEMC创建触发发器8.4..2使用SQL命令创建建触发器器8.4触发器器触发器触发器是是当特定定事件出出现时自自动执行行的存储储过程特定事件件可以是是执行更更新的DML语语句和DDL语语句触发器不不能被显显式调用用触发器的的功能：：自动生成成数据自定义复复杂的安安全权限限提供审计计和日志志记录启用复杂杂的业务务逻辑创建触发发器的语语法CREATE[[ORREPLACE]]TRIGGERtrigger_nameAFTER||BEFORE||INSTEADOF[INSERT][[[OR]UPDATE[[OFcolumn_list]][[OR]DELETE]]ONtable_or_view_name[REFERENCING{{OLD[[AS]]old//NEW[[AS]]new}]][FOREACHROW][WHEN((condition)]pl/sql__block;;触发器的的组成部部分3-1触发器由由三部分分组成：：触发器语语句（事事件）定义激活活触发器器的DML事事件和和DDL事事件触发器限限制执行触发发器的条条件，该该条件必必须为真真才能激激活触发发器触发器操操作（主主体）包含一些些SQL语语句和代代码，它它们在发发出了触触发器语语句且触触发限制制的值为为真时运运行触发器的的组成部部分3-2SQL>>CREATEORREPLACETRIGGERtrig_salAFTERUPDATEOFempsalONsalary__records

…触发器语语句为salary__records表创建trig-sal触发器在更新emp__sal列之后激激活触发发器触发器限限制SQL>>……FOREACHROWWHEN(NEW..empsal>OLD..empsal)DECLARESal__diffNUMBER;; … 只有在WHEN子句中的的条件得得到满足足时，才才激活trig_sal触发器触发器操操作SQL>>…BEGINsal__diff:=:NEW..empsal-:OLD..empsal;DBMS_OUTPUT.PUT__LINE(‘‘工资差额额：’sal__diff);END;;如果WHEN子句中的的条件得得到满足足，将执执行BEGIN块中的代代码触发器的的组成部部分3-3Oracle数据库更新表保存更新新激活触发器AFTER触发器的的工作原原理BEFORE触发器的的工作原原理更新表激活触发器保存更新新Oracle数据库创建触发发器CREATEORREPLACETRIGGERaiu__itemfileAFTERINSERTONitemfileFOREACHROWBEGINIF((:NEW..qty_hand=0))THENDBMS_OUTPUT.PUT__LINE(''警告：已已插入记记录，但但数量为为零');ELSEDBMS_OUTPUT.PUT__LINE(‘‘已插入记记录');ENDIF;;END;;/触发器类类型6-1触发器的的类型有有：触发器类类型模式(DDL)触发器DML触发器数据库级级触发器语句级触触发器行级触发发器INSTEADOF触发器触发器类类型6-2DDL触触发器器数据库级级触发器器DML触触发器器语句级触触发器行级触发发器INSTEADOF触发发器在模式中中执行DDL语句时执执行在发生打打开、关关闭、登登录和退退出数据据库等系系统事件件时执行行在对表或或视图执执行DML语句时执执行无论受影影响的行行数是多多少，都都只执行行一次对DML语句修改改的每个个行执行行一次用于用户户不能直直接使用用DML语句修改改的视图图触发器类类型6-3行级触发发器SQL>>CREATETABLETEST_TRG(IDNUMBER,NAMEVARCHAR2(20));;SQL>>CREATESEQUENCESEQ__TEST;SQL>>CREATEORREPLACETRIGGERBI__TEST_TRGBEFOREINSERTORUPDATEOFIDONTEST_TRGFOREACHROWBEGINIFINSERTINGTHENSELECTSEQ_TEST..NEXTVALINTO:NEW..IDFROMDUAL;ELSERAISE_APPLICATION_ERROR(--20020,,'不允许更更新ID值！');ENDIF;;END;;/触发器类类型6-4SQL>>CREATEORREPLACETRIGGERtrgdemoAFTERINSERTORUPDATEORDELETEONorder_masterBEGINIFUPDATINGTHENDBMS_OUTPUT.PUT__LINE(‘‘已更新ORDER_MASTER中的数据据');ELSIFDELETINGTHENDBMS_OUTPUT.PUT__LINE(‘‘已删除ORDER_MASTER中的数据据');ELSIFINSERTINGTHENDBMS_OUTPUT.PUT__LINE(‘‘已在ORDER_MASTER中插入数数据');ENDIF;;END;;/语句级触触发器触发器类类型6-5SQL>>CREATEORREPLACETRIGGERupd__ord_viewINSTEADOFUPDATEONord__viewFOREACHROWBEGINUPDATEorder_masterSETvencode=:NEW..vencodeWHEREorderno=:NEW..orderno;DBMS_OUTPUT.PUT__LINE(‘‘已激活触触发器');END;;/INSTEADOF触发发器触发器类类型6-6SQL>>CREATETABLEdropped_obj(obj__nameVARCHAR2(30),,obj__typeVARCHAR2(20),,drop_dateDATE);SQL>>CREATEORREPLACETRIGGERlog__drop_objAFTERDROPONSCHEMABEGININSERTINTOdropped_objVALUES((ORA_DICT_OBJ_NAME,ORA__DICT_OBJ__TYPE,SYSDATE);;END;;/模式触发发器启用和禁禁用触发发器删除触发发器启用、禁禁用和删删除触发发器SQL>>ALTERTRIGGERaiu__itemfileDISABLE;SQL>>ALTERTRIGGERaiu__itemfileENABLE;;SQL>>DROPTRIGGERaiu__itemfile;查看有关关触发器器的信息息SQL>>SELECTTRIGGER_NAMEFROMUSER_TRIGGERSWHERETABLE_NAME='EMP'';SQL>>SELECTTRIGGER_TYPE,,TRIGGERING__EVENT,,WHEN__CLAUSEFROMUSER__TRIGGERSWHERETRIGGER_NAME==''BIU_EMP_DEPTNO'';USER_TRIGGERS数据据字典视视图包含含有关触触发器的的信息程序包程序包是是对相关关过程、、函数、、变量、、游标和和异常等等对象的的封装程序包由由规范和和主体两两部分组组成声明程序序包中公公共对象象。包括括类型、、变量、、常量、、异常、、游标规规范和子子程序规规范等声明程序序包私有有对象和和实现在在包规范范中声明明的子程程序和游游标程序包规范主体创建程序序包2-1程序包规规范CREATE[[ORREPLACE]]PACKAGEpackage_nameIS||AS[Publicitemdeclarations][Subprogramspecification]END[[package__name];;程序包主主体CREATE[[ORREPLACE]]PACKAGEBODYpackage_nameIS||AS[Privateitemdeclarations][Subprogrambodies]][BEGINInitialization]END[[package__name];;创建程序序包2-2CREATEORREPLACEPACKAGEpack_meISPROCEDUREorder_proc(ornoVARCHAR2);;FUNCTIONorder_fun((ornosVARCHAR2)RETURNVARCHAR2;ENDpack_me;/CREATEORREPLACEPACKAGEBODYpack_meASPROCEDUREorder_proc(ornoVARCHAR2)ISstatCHAR((1);;BEGINSELECTostatusINTOstatFROMorder_masterWHEREorderno=orno;……ENDorder_proc;FUNCTIONorder_fun((ornosVARCHAR2)RETURNVARCHAR2ISicodeVARCHAR2(5);ocodeVARCHAR2(5);BEGIN……ENDorder_fun;ENDpack_me;/程序包的的优点模块化更轻松的的应用程程序设计计信息隐藏藏新增功能能性能更佳佳有关子程程序和程程序包的的信息USER_OBJECTS视视图包包含用户户创建的的子程序序和程序序包的信信息USER_SOURCE视视图存储储子程序序和程序序包的源源代码SELECTobject__name,object__typeFROMUSER__OBJECTSWHEREobject__typeIN(('PROCEDURE',,'FUNCTION',,'PACKAGE',,'PACKAGEBODY'');SELECTline,textFROMUSER__SOURCEWHERENAME='TEST';内置程序序包扩展数据据库的功功能为PL/SQL提提供对SQL功能能的访问问用户SYS拥拥有所所有程序序包是公有同同义词可以由任任何用户户访问内置程序序包一些内置置程序包包：程序包名称说明STANDARD和DBMS_STANDARD定义和扩展PL/SQL语言环境DBMS_LOB提供对LOB数据类型进行操作的功能DBMS_OUTPUT处理PL/SQL块和子程序输出调试信息DBMS_RANDOM提供随机数生成器DBMS_SQL允许用户使用动态SQLDBMS_XMLDOM用DOM模型读写XML类型的数据DBMS_XMLPARSERXML解析，处理XML文档内容和结构DBMS_XMLQUERY提供将数据转换为XML类型的功能DBMS_XSLPROCESSOR提供XSLT功能，转换XML文档UTL_FILE用PL/SQL程序来读写操作系统文本文件内置程序序包SQL>>SETSERVEROUTPUTONSQL>>BEGINDBMS_OUTPUT.PUT__LINE(''打印三角角形');FORiIN1..9LOOPFORjIN1..iLOOPDBMS_OUTPUT.PUT(('*'');ENDLOOPfor__j;DBMS_OUTPUT.NEW__LINE;ENDLOOPfor__i;END;;/DBMS_OUTPUT包显显示PL/SQL块块和子子程序的的调试信信息。内置程序序包DBMS_LOB包包提供用用于处理理大型对对象的过过程和函函数DB