第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语句创建序列SQLCREATESEQUENCEtoys_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返回序列的当前值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_name[INCREMENTBYn][STARTWITHn][MAXVALUEn|NOMAXVALUE][MINVALUEn|NOMINVALUE][CYCLE|NOCYCLE][CACHEn|NOCACHE][ORDER|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-2CREATESYNONYMempFORSCOTT.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创建过程的语法：CREATE[ORREPLACE]PROCEDUREprocedurename[(parameterlist)]IS|ASlocalvariabledeclarationBEGINexecutablestatements[EXCEPTIONexceptionhandlers]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-5SQLCREATEORREPLACEPROCEDUREitemdesc(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-6SQLCREATEORREPLACEPROCEDUREtest(value1INVARCHAR2,value2OUTNUMBER)ISidentityNUMBER;BEGINSELECTITEMRATEINTOidentityFROMitemFileWHEREitemcode=value1;IFidentity200THENvalue2:=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;/SQLSETSERVEROUTONSQLDECLAREnum1NUMBER:=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子程序。创建函数的语法：CREATE[ORREPLACE]FUNCTIONfunctionname[(param1,param2)]RETURNdatatypeIS|AS[localdeclarations]BEGINExecutableStatements;RETURNresult;EXCEPTIONExceptionhandlers;END;函数4-2定义函数的限制：函数只能接受IN参数，而不能接受INOUT或OUT参数形参不能是PL/SQL类型函数的返回类型也必须是数据库类型访问函数的两种方式：使用PL/SQL块使用SQL语句函数4-3创建函数：从SQL语句调用函数：CREATEORREPLACEFUNCTIONfun_helloRETURNVARCHAR2ISBEGINRETURN'朋友，您好';END;/SQLSELECTfun_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_