第8章 Oracle 的其它对象.ppt

第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语句创建序列,SQLCREATESEQUENCEtoys_seqSTARTWITH10INCREMENTBY10MAXVALUE2000MINVALUE10NOCYCLECACHE10;,指定第一个序号从10开始,指定序号之间的间隔为10,表示序列的最大值为2000,表示序列的最小值为10,在达到最大值后停止生成下一个值,指定内存中预先分配的序号数,8.1.2使用SQL命令创建序列,CREATESEQUENCEsequence_nameINCREMENTBYnSTARTWITHnMAXVALUEn|NOMAXVALUEMINVALUEn|NOMINVALUECYCLE|NOCYCLECACHEn|NOCACHEORDER|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维护序列,访问序列,通过序列的伪列来访问序列的值NEXTVAL返回序列的下一个值CURRVAL返回序列的当前值,SQLINSERTINTOtoys(toyid,toyname,toyprice)VALUES(toys_seq.NEXTVAL,TWENTY,25);SQLINSERTINTOtoys(toyid,toyname,toyprice)VALUES(toys_seq.NEXTVAL,MAGICPENCIL,75);,指定序列的下一个值,SQLSELECTtoys_seq.CURRVALFROMdual;,检索序列的当前值,8.1.3维护序列,2修改序列当修改序列时，注意不要使依赖于序列号的主键不唯一。利用OEMC修改序列在OEMC的界面中，选择要修改的序列，单击鼠标右键，从弹出的快捷菜单中选择“查看/编辑详细资料”，激活“编辑序列”窗口，在窗口中对序列进行修改即可。,8.1.3维护序列,利用SQL命令修改序列ALTERSEQUENCEsequence_nameINCREMENTBYnSTARTWITHnMAXVALUEn|NOMAXVALUEMINVALUEn|NOMINVALUECYCLE|NOCYCLECACHEn|NOCACHEORDER|NOORDER,8.1.3维护序列,3删除序列在OEMC的界面中，选择要删除的序列，单击鼠标右键，从弹出的快捷菜单中选择“移去”即可。用SQL语句删除一个序列和删除别的对象类似。其语法形式是DROPSEQUENCEsequence_name,更改和删除序列,SQLALTERSEQUENCEtoys_seqMAXVALUE5000CYCLE;,使用ALTERSEQUENCE语句修改序列，不能更改序列的STARTWITH参数,使用DROPSEQUENCE语句删除序列,SQLDROPSEQUENCEtoys_seq;,8.2.1在OEMC中创建同义词8.2.2使用SQL命令创建同义词,8.2同义词,同义词3-1,同义词是现有对象的一个别名。简化SQL语句隐藏对象的名称和所有者提供对对象的公共访问同义词共有两种类型：,同义词,私有同义词,公有同义词,私有同义词只能在其模式内访问，且不能与当前模式的对象同名。,公有同义词可被所有的数据库用户访问。,同义词3-2,CREATESYNONYMempFORSCOTT.emp;,SCOTT.emp的别名,模式名,表名,私有同义词,公有同义词,CREATEPUBLICSYNONYMemp_synFORSCOTT.emp;,同义词名称,同义词3-3,创建或替换现有的同义词,CREATEORREPLACESYNONYMemp_synFORSCOTT.emp;,替换现有的同义词,SQLDROPSYNONYMemp;,SQLDROPPUBLICSYNONYMemp_syn;,删除同义词,8.3.1在OEMC中创建存储过程8.3.2使用SQL命令创建存储过程,8.3存储过程,子程序2-1,命名的PL/SQL块，编译并存储在数据库中。子程序的各个部分：声明部分可执行部分异常处理部分(可选)子程序的分类：过程执行某些操作函数执行操作并返回值,子程序2-2,子程序的优点：模块化将程序分解为逻辑模块可重用性可以被任意数目的程序调用可维护性简化维护操作安全性通过设置权限，使数据更安全,过程8-1,过程是用于完成特定任务的子程序例如：,前往售票厅,询问关于车票的信息,排队等候,在柜台购买车票,过程8-2,创建过程的语法：CREATEORREPLACEPROCEDURE()IS|ASBEGINEXCEPTIONEND;,创建过程，可指定运行过程需传递的参数,处理异常,包括在过程中要执行的语句,过程8-3,CREATEORREPLACEPROCEDUREfind_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-5,SQLCREATEORREPLACEPROCEDUREitemdesc(item_codeINVARCHAR2)ISv_itemdescVARCHAR2(5);BEGINSELECTitemdescINTOv_itemdescFROMitemfileWHEREitemcode=item_code;DBMS_OUTPUT.PUT_LINE(item_code|项目的说明为|v_itemdesc);END;/SQLSETSERVEROUTPUTONSQLEXECUTEitemdesc(i201);,执行过程的语法：EXECUTEprocedure_name(parameters_list);,过程8-6,SQLCREATEORREPLACEPROCEDUREtest(value1INVARCHAR2,value2OUTNUMBER)ISidentityNUMBER;BEGINSELECTITEMRATEINTOidentityFROMitemFileWHEREitemcode=value1;IFidentitySETSERVEROUTONSQLDECLAREnum1NUMBER:=100;num2NUMBER:=200;BEGINswap(num1,num2);DBMS_OUTPUT.PUT_LINE(num1=|num1);DBMS_OUTPUT.PUT_LINE(num2=|num2);END;/,过程8-8,将过程的执行权限授予其他用户：删除过程：,SQLGRANTEXECUTEONfind_empTOMARTIN;SQLGRANTEXECUTEONswapTOPUBLIC;,SQLDROPPROCEDUREfind_emp;,函数4-1,函数是可以返回值的命名的PL/SQL子程序。创建函数的语法：CREATEORREPLACEFUNCTION(param1,param2)RETURNIS|ASlocaldeclarationsBEGINExecutableStatements;RETURNresult;EXCEPTIONExceptionhandlers;END;,函数4-2,定义函数的限制：函数只能接受IN参数，而不能接受INOUT或OUT参数形参不能是PL/SQL类型函数的返回类型也必须是数据库类型访问函数的两种方式：使用PL/SQL块使用SQL语句,函数4-3,创建函数：从SQL语句调用函数：,CREATEORREPLACEFUNCTIONfun_helloRETURNVARCHAR2ISBEGINRETURN朋友，您好;END;/,SQLSELECTfun_helloFROMDUAL;,函数4-4,CREATEORREPLACEFUNCTIONitem_price_range(priceNUMBER)RETURNVARCHAR2ASmin_priceNUMBER;max_priceNUMBER;BEGINSELECTMAX(ITEMRATE),MIN(ITEMRATE)INTOmax_price,min_priceFROMitemfile;IFprice=min_priceANDpriceCREATEORREPLACETRIGGERtrig_salAFTERUPDATEOFempsalONsalary_records,触发器语句,为salary_records表创建trig-sal触发器,在更新emp_sal列之后激活触发器,触发器限制,SQLFOREACHROWWHEN(NEW.empsalOLD.empsal)DECLARESal_diffNUMBER;,只有在WHEN子句中的条件得到满足时，才激活trig_sal触发器,触发器操作,SQLBEGINsal_diff:=:NEW.empsal-:OLD.empsal;DBMS_OUTPUT.PUT_LINE(工资差额：sal_diff);END;,如果WHEN子句中的条件得到满足，将执行BEGIN块中的代码,触发器的组成部分3-3,Oracle数据库,更新,表,保存更新,激活,触发器,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-2,DDL触发器数据库级触发器DML触发器语句级触发器行级触发器INSTEADOF触发器,在模式中执行DDL语句时执行,在发生打开、关闭、登录和退出数据库等系统事件时执行,在对表或视图执行DML语句时执行,无论受影响的行数是多少，都只执行一次,对DML语句修改的每个行执行一次,用于用户不能直接使用DML语句修改的视图,触发器类型6-3,行级触发器,SQLCREATETABLETEST_TRG(IDNUMBER,NAMEVARCHAR2(20);SQLCREATESEQUENCESEQ_TEST;SQLCREATEORREPLACETRIGGERBI_TEST_TRGBEFOREINSERTORUPDATEOFIDONTEST_TRGFOREACHROWBEGINIFINSERTINGTHENSELECTSEQ_TEST.NEXTVALINTO:NEW.IDFROMDUAL;ELSERAISE_APPLICATION_ERROR(-20020,不允许更新ID值！);ENDIF;END;/,触发器类型6-4,SQLCREATEORREPLACETRIGGERtrgdemoAFTERINSERTORUPDATEORDELETEONorder_masterBEGINIFUPDATINGTHENDBMS_OUTPUT.PUT_LINE(已更新ORDER_MASTER中的数据);ELSIFDELETINGTHENDBMS_OUTPUT.PUT_LINE(已删除ORDER_MASTER中的数据);ELSIFINSERTINGTHENDBMS_OUTPUT.PUT_LINE(已在ORDER_MASTER中插入数据);ENDIF;END;/,语句级触发器,触发器类型6-5,SQLCREATEORREPLACETRIGGERupd_ord_viewINSTEADOFUPDATEONord_viewFOREACHROWBEGINUPDATEorder_masterSETvencode=:NEW.vencodeWHEREorderno=:NEW.orderno;DBMS_OUTPUT.PUT_LINE(已激活触发器);END;/,INSTEADOF触发器,触发器类型6-6,SQLCREATETABLEdropped_obj(obj_nameVARCHAR2(30),obj_typeVARCHAR2(20),drop_dateDATE);SQLCREATEORREPLACETRIGGERlog_drop_objAFTERDROPONSCHEMABEGININSERTINTOdropped_objVALUES(ORA_DICT_OBJ_NAME,ORA_DICT_OBJ_TYPE,SYSDATE);END;/,模式触发器,启用和禁用触发器删除触发器,启用、禁用和删除触发器,SQLALTERTRIGGERaiu_itemfileDISABLE;,SQLALTERTRIGGERaiu_itemfileENABLE;,SQLDROPTRIGGERaiu_itemfile;,查看有关触发器的信息,SQLSELECTTRIGGER_NAMEFROMUSER_TRIGGERSWHERETABLE_NAME=EMP;SQLSELECTTRIGGER_TYPE,TRIGGERING_EVENT,WHEN_CLAUSEFROMUSER_TRIGGERSWHERETRIGGER_NAME=BIU_EMP_DEPTNO;,USER_TRIGGERS数据字典视图包含有关触发器的信息,程序包,程序包是对相关过程、函数、变量、游标和异常等对象的封装程序包由规范和主体两部分组成,声明程序包中公共对象。包括类型、变量、常量、异常、游标规范和子程序规范等,声明程序包私有对象和实现在包规范中声明的子程序和游标,程序包,规范,主体,创建程序包2-1,程序包规范CREATEORREPLACEPACKAGEpackage_nameIS|ASPublicitemdeclarationsSubprogramspecificationENDpackage_name;,程序包主体CREATEORREPLACEPACKAGEBODYpackage_nameIS|ASPrivateitemdeclarationsSubprogrambodiesBEGINInitializationENDpackage_name;,创建程序包2-2,CREATEORREPLACEPACKAGEpack_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);BEGINENDorder_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拥有所有程序包是公有同义词可以由任何用户访问,内置程序包,一些内置程序包：,内置程序包,SQLSETSERVEROUTPUTONSQLBEGINDBMS_OUTPUT.PUT_LINE(打印三角形);FORiIN1.9LOOPFORjIN1.iLOOPDBMS_OUTPUT.PUT(*);ENDLOOPfor_j;DBMS_OUTPUT.NEW_LINE;ENDLOOPfor_i;END;/,DBMS_OUTPUT包显示PL/SQL块和子程序的调试信息。,内置程序包,DBMS_LOB包提供用于处理大型对象的过程和函数DBMS_XMLQUERY包用于将查询结果转换为XML格式,内置程序包,SQLDECLAREresultCLOB;xmlstrVARCHAR2(32767);lineVARCHAR2(2