Oracle存储过程开发规范与技巧

{管理信息化ORACLE}Oracle存储过程开发规范与技巧示例如下：BEGIN--查询员工及对应的部门名称SELECT,FROMl_deptdept,l_employeeempWHERE_id=_id;END;3）注释书写规范为了提高可读性，应该使用一定数量的注释。注释大约占总行数的1/5。1：注释风格：注释单独成行、放在语句前面。2：应对不易理解的分支条件表达式加注释；3：对重要的计算应说明其功能；4：过长的函数实现，应将其语句按实现的功能分段加以概括性说明；5：每条SQL语句均应有注释说明6：对于程序的整体功能，应在程序开始部分说明，可采用单行/多行注释。（--或/**/方式）2.命名规范命名对象规则样例1业务相关以模块代码开头gld_assist_check_p存储过程、包、2gld_load_to_etl方法gld_load_to_etl_full3全局使用，以global开头global_procedure_check变量以v开头v_updatemode1游标以c开头c_tablist内存表以m开头m_table1临时表以t开头t_tmpTable存储过程技术1．存储过程样例CREATEORREPLACEPROCEDUREexample(v_inputINNUMBER,--输入参数v_outputOUTNUMBER--输出参数)ISPRAGMAAUTONOMOUS_TRANSACTION;CURSORc1--定义一个游标，在begin之前ISSELECTb.tablenamemlogtable,MAX(remarks)KEEP(DENSE_RANKLASTORDERBYstarttime)remarksFROMproc_loga,table_procbWHERETO_CHAR(starttime,'yyyy-mm-dd')<=--转换时间并做比较TO_CHAR(SYSDATE-TO_DSINTERVAL(TO_CHAR(intervaldays)||'00:00:00'),'yyyy-mm-dd')

ANDa.remarksLIKE'SUCCEEDED:%'ANDcedurename=cedurenameGROUPBYb.tablename);--定义结束c1_recc1%ROWTYPE;--定义接受游标数据行的ROWTYPEv_mlogtableVARCHAR(30);v_postperiodCHAR(2);v_acctbalbeginseqNUMBER;v_systimeDATE;BEGINv_input:=0;--变量赋值v_systime:=SYSDATE;OPENc1;--打开游标LOOP--循环FETCHc1INTOc1_rec;--从当前游标行赋值c1_recEXITWHENc1%NOTFOUND;--游标没有数据退出v_mlogtable:=c1_;--从行取出具体数据赋给变量CASETRIM(LOWER(v_mlogtable))--CASE起始WHEN'String1'--当条件一THEN--做条件一工作BEGINv_remarks:=REPLACE(v_remarks,'AA');END;WHEN'String2'--当条件二THENBEGINEND;ELSE--其他条件NULL;ENDCASE;--CASE结束IF(LOWER(SUBSTR(v_mlogtable,1,5))<>'mlog$')THENSELECTlog_tableINTOv_mlogtableFROMuser_snapshot_logsWHERELOWER(MASTER)=LOWER(v_mlogtable);ENDIF;EXECUTEIMMEDIATE'deletefrom'||v_mlogtable||'wheresequence$$<='||TO_CHAR(v_lognum);EXITWHEN1>2;--循环跳出条件ENDLOOP;--循环结束CLOSEc1;--关闭游标EXCEPTIONWHENOTHERSTHENROLLBACK;global_procedure__end('checkdataerror01',v_systime,1,SQLCODE||''||SQLERRM);RAISE;RETURN;END;ENDexample;2．基本知识1)基本结构--------------------------------------------------------CREATEORREPLACEPROCEDUREexample（parameters）--过程声明区IS--------------------------------------------------------v_1NUMBER;--过程中变量声明区--------------------------------------------------------BEGINv_1:=0;--过程内容区ENDexample;--------------------------------------------------------2)基本类型CHAR固定长度字符类型VARCHAR2可变长字符类型VARCHAR可变长字符类型(不建议使用)NUMBER一切数值类型DATE一切日期类型3)参数三种：IN输入参数，OUT输出参数，INOUT输入输出参数。4)变量的声明在变量声明区声明变量的名称和类型例：v_postperiodCHAR(2);可赋初值v_postperiodCHAR(2):=’01’;（这里叫变量声明区可能并不恰当，因为游标、自定义类型等，一切需要事先声明的都应在这里声明。）5)变量的赋值使用‘：=’为变量赋值1．直接使用基本类型赋值例：v_number:=1;2.使用函数赋值例：v_date:=sysdate;3．使用SQL语句为变量赋值1〉通过sql直接赋值SELECTCOUNT(*)INTOv_tmpnumberFROMetl_ods_masterdata_tablist;2〉通过构造SQL赋值：v_tmpsql:='SELECTlog_tableFROMuser_snapshot_logs'||v_dblink||'WHEREUPPER(MASTER)=UPPER('''||v_singletab||''')';EXECUTEIMMEDIATEv_tmpsqlINTOv_tmpvarchar;6)循环1.无限或简单循环LOOPEXITWHEN(退出循环条件);ENDLOOP;2.while循环WHILEconditionLOOPexecutable_statements;ENDLOOP;3.for循环基于数字的for循环：FORfor_indexINlow_value..high_valueLOOPexecutable_statements;ENDLOOP;基于游标的for循环：FORrecord_indexINmy_cursorLOOPexecutable_statements;ENDLOOP;7)调用其他过程或方法1．如果单独定义，直接使用例：v_retval0:=f_dump_init(v_updatemode,v_systime,'mlog$_glddocheader',v_procname,v_docheaderbeginseq,v_docheaderendseq);2．如果定义在包下，使用包名+过程名例：global_procedure__run(v_procname)；3．固定用法和函数标识作用用法或类型固定用法：SYSDATE当前系统时间DATESQLCODE异常代码VARCHAR2SQLERRM异常描述VARCHAR2NO_DATA_FOUND未找到数据异常与when搭配OTHERS其他所有异常与when搭配RAISE抛出当前异常RAISE;DENSE_RANK非选取字段排序MIN(B)KEEP(DENSE_RANKFIRSTORDERBYA)MAX(B)KEEP(DENSE_RANKLASTORDERBYA)PRAGMAAUTONOMOUS_TRAN使用自治事务，可以使该过程被调用Begin之前使用SACTION时单独提交PRAGMAAUTONOMOUS_TRANSACTION;BULKCOLLECTINTO将前面执行结果大批放入后面的集合BULKCOLLECTINTOcolumntab;SQL%ROWCOUNT中前一个DML语句执行影响行数作为NUMBER型使用v_number:=SQL%ROWCOUNTDBMS__line（）输出信息函数转换NCHARNVARCHAR2CLOBNCLOBTO_CHAR（A）TO_CHAR转换DATE型为指定格式TO_CHAR(time,'yyyy-mm-dd')转换NUMBER型为指定格式TO_CHAR(564.70,'$999.9')TO_DATE转换字符串为指定日期to_date('1900-01-01','YYYY-MM-DD')INSTR(string,substrin返回目标字符串中子字符串的位置。INSTR('bug-archie','archie')g(,postion)(,occurren（起始位置和出现次数为可选）INSTR('haracter?archie','a',1,2)ce))LENGTH获得指定字符串长度LENGTH('CANDIDE')LOWER将指定字符串转换成小写LOWER('LETTERS')UPPER将指定字符串转换成大写UPPER('letters')LPAD(str1,n,str2)将str1用str2左补齐至n位LPAD('55',10,'0')RPAD(str1,n,str2)将str1用str2右补齐至n位RPAD('55',10,'0')LTRIM去掉指定字符串左侧的指定字符或字符集合，默认为空格LTRIM('Way')LTRIM('123123Way','123')RTRIM去掉指定字符串右侧的指定字符或字符集合，默认为空格RTRIM('WayxyXxyxy','xy')POWER（m,n）计算m的n次方POWER(2,3)Extract(yearfromdate)取出date的年4．ROWTYPE的使用可以使用%type和%rowtype%type%rowtype属性允许人们定义一个记录变量，它的成员变量拥有表中每一列正确的类型及长度,使用%TYPE,那么用户就不必修改代码，否则就必须修改代码。CREATETABLEEMPLOYEE(EMP_IDNUMBERNOTNULL,EMP_NAMECHAR(20),CREATE_DATEDATE）DECLAREv_studentrecordemployee%ROWTYPE;n_date%TYPE;BEGINSELECT*INTOv_studentrecordFROMemployeeWHEREemp_id=1;n:=v__date;DBMS__line(n);END;5．内存表的使用内存表主要作为数组用。1）：一个字段：PROCEDUREt1ISTYPEt_cISTABLEOFtesta.a1%TYPEINDEXBYBINARY_INTEGER;aat_c;BEGINaa(0):='aaa';DBMS__line(aa(0));END;2）：定义多个字段：PROCEDUREt1ISTYPEt_rISRECORD(t1VARCHAR(10),t2VARCHAR(10));TYPEt_tISTABLEOFt_rINDEXBYBINARY_INTEGER;aat_t;BEGINaa(0).t1:='aaa';aa(0).t2:='bbb';DBMS__line(aa(0).t1);DBMS__line(aa(0).t2);END;6．游标的使用游标是用来处理使用SELECT语句从数据库中检索到的多行记录的工具。行。DECLAREnNUMBER;CURSORcISSELECT*FROMemployee;BEGINFORv_cINcLOOPn:=v_c.emp_id;DBMS__line(n);ENDLOOP;EXCEPTIONWHENOTHERSTHENDBMS__line('error');END;7．跟踪调试为输出的依据：DBMS__LINE(G_USERID(-2));执行时设置：setserveroutputon8．临时表时的会话是可见的，任何会话都不能看见其他会话的数据。即使COMMIT之后也是不可见的。对于临时表并行不是问题，即使锁定也不能阻止其他程序的访问。ORACLE的DDL语句是一种消耗较大的动作）并不用每个程序创建一次，并且临时表总保持为空。下面这个例子可以说明临时表的运行过程：CREATEGLOBALTEMPORARYTABLEREPDB.L_EMP_DEPT_TEMP(EMP_IDVARCHAR(5),EMP_NAMEVARCHAR(20),DEPT_IDVARCHAR(5),DEPT_NAMEVARCHAR(20))1DECLARE2DL_EMP_DEPT_TEMP%ROWTYPE;3CURSORCIS4SELECTE.EMP_IDAA,E.EMP_NAMEBB,D.DEPT_IDCC,D.NAMEDD5FROML_EMPLOYEEE,L_DEPTD6WHEREE.DEP_ID=D.DEPT_ID;7BEGIN8FORV_CINCLOOP9INSERTINTOL_EMP_DEPT_TEMP10VALUES(V_C.AA,V_C.BB,V_C.CC,V_C.DD);11ENDLOOP;12*END;SQL>/PL/SQL过程已成功完成。SQL>SELECTCOUNT(*)2FROML_EMP_DEPT_TEMP3/COUNT(*)----------3SQL>COMMIT2/提交完成。SQL>SELECTCOUNT(*)2FROML_EMP_DEPT_TEMP3/COUNT(*)----------09．异常处理跳转到当NO_DATE_FOUND或TO_MANY_ROWS预定义例外用于处理常见的oracle错误，可以被认为是正常的处理部分。部分ERROR这样的例外表明一个程序错误或一些意料之外的事件。如下所示：1）：正常处理的部分1DECLARE2NCHAR;3BEGIN4SELECTEMP_NAME5INTON6FROMEMPLOYEE;7DBMS__LINE('N');8*END;SQL>/DECLARE*第1行出现错误:ORA-01422:实际返回的行数超出请求的行数ORA-06512:在line41DECLARE2NCHAR;3BEGIN4SELECTEMP_NAME5INTON6FROMEMPLOYEE;7DBMS__LINE(N);8EXCEPTIONWHENTOO_MANY_ROWSTHEN9DBMS__LINE('TOOMANYROWSRETURN');10*END;PL/SQL过程已成功完成。输出结果为：TOOMANYROWSRETURN2）：非正常处理的部分，自定义异常SQL>insertintol_employee2values('4','dd','3',sysdate,'2000')3/insertintol_employee*第1行出现错误:ORA-02291:违反完整约束条件(_EMP_DEPT)-未找到父项关键字处理方法：自定义异常1declare2eexception;3pragmaexception_init(e,-2291);4begin5insertintol_employee6values('6','dd','3',sysdate,'2000');7exceptionwhenethen8DBMS__LINE('违反完整约束条件(_EMP_DEPT)');9*end;SQL>/PL/SQL过程已成功完成。输出结果为：违反完整约束条件(_EMP_DEPT)10．嵌套的变量不能被父块引用。如果字块需要单独提交，应使用自治事务。11．标签的形式是<>。要求使用标签。12．记录转储开始时间和结束时间1对每个转储只记录一条记录，不保存历史记录。2）在开始转储时，读取上次转储结束时间，只转储从上次转储以来的新增或修改的记录。3）在转储的存储过程中记录开始时间和结束时间，是否成功。如果失败，记录失败原因。4）可以用SQL语句查找失败的转储，可以查找转储时间过长的转储。表名：转储记录（TRANSLOG）字段：程序包名存储过程名中间表名开始时间结束时间成功标识失败原因13．授权grantselectoncs_torepdbnewrevokeselectoncs_fromrepdbnew注意：不能为当前用户授权14．建立同义词CREATESYNONYMACPStorkFlAssAnaTabFORdropSYNONYMBILInvoiceUseEntityData注：建立同义词后用户可以用select访问，但不能建立视图。15．为表字段加注释COMMENTONCOLUMNHAN_2.BBBBIS'B字段';16．触发器createorreplacetriggerqytest1_trigger3BEFOREINSERTONmlog$_qytest1FOREACHROWbegin:$$:=SYSDATE;end;17．自定义类型的赋值自定义类型：CREATEORREPLACETYPEASVARRAY(50)OFVARCHAR2(25);自定义类型的赋值：declareindatanameinparam;beginindataname:=inparam(2);indataname:=inparam('isCalCount','NO_TX');end;18．OBJECTTYPES簡單來說，OracleObjectTypes就是Oracle以TYPE的方式來實現物件(Objects)的方法，宣告/定義的方法，類似於Package。ObjectType的宣告/定義中包含了它的Attributes/Properties與Methods，也就是MemberFunctions/Procedures。本篇來介紹利用OracleObjectTypes來做中介暫存的實作。建立OracleObject:定義這個Object的內容，可以把它想像為所希望的RowColumns的定義。viewplaincopytoclipboardprint?CREATETYPEtype_objASOBJECT(col1INTEGER,col2VARCHAR2(60));/CREATETYPEtype_objASOBJECT(col1INTEGER,col2VARCHAR2(60));/建立ObjectCollection:建立一個TableType，這個Table裝的資料列內容(欄位)就是之前所建立的Object。viewplaincopytoclipboardprint?CREATEORREPLACETYPEtype_tabISTABLEOFtype_obj;/CREATEORREPLACETYPEtype_tabISTABLEOFtype_obj;/在PL/SQL中的應用:定義好前面兩個Type，就可以拿它來在程式中實作了。viewplaincopytoclipboardprint?DECLARE--宣告與初始化obj_type_tabtype_tab:=type_tab();BEGIN--多筆給值obj_type_tab:=type_tab(type_obj(37,'col2_val1'),type_obj(3,'col2_val2'),type_obj(254,'col2_val3'));--逐筆給值obj_type_;obj_type_tab(obj_type_):=type_obj(12,'col_2_val');/**要逐筆給或一次給多筆就純粹看使用時機與方便性.舉例來說,已經有確切可知的數筆資料時,就可以用多筆給的方式來定義使用.但是,如果資料來源是取自其他媒介,比方說Cursor,可能就需要跑Loop去逐筆取值與給值了.*//**再來,可以把已經存放好資料的obj_type_tab,像下面這種應用方式去取回資料.當然,舉一反三,它可以用這樣子的SELECT方式轉化成ReferenceCursor及其他相關應用.*/FORrecIN(SELECTcol1,col2FROMTABLE(CAST(obj_type_tabASTYPE_TAB)))LOOPDBMS__line('1='||1||';2='||2);ENDLOOP;/**輸出結果:1=37;2=col2_val11=3;2=col2_val21=254;2=col2_val31=12;2=col_2_val*/END;19．Tabletype的使用定义：resultsetOUTtabletype使用：OPENresultsetFORSELECTDISTINCTcname,b.paramneededFROMtemp_tablenamesa,table_procbWHEREUPPER(a.tablename)=b.tablename;查询：DECLARETABNAMESTABNAMES_OBJ;RESULTSET;w_idVARCHAR2(30);h_idVARCHAR2(30);BEGINTABNAMES:=(TABNAMES_OBJ(tabname_list_typ('REP_FT_CO_CPCB_V')));(TABNAMES,RESULTSET);LOOPFETCHRESULTSETINTOw_id,h_id;EXITWHENRESULTSET%NOTFOUND;DBMS__line(w_id||','||h_id);ENDLOOP;COMMIT;END;20．创建DB-Linkcreatedatabaselink"erpcar"--dblink名称，与连接实例名相同connecttojferp--连接使用用户identifiedby"qmnerp"--用户密码using'ERPCAR'--连接实例21．SQL优化1)SELECT子句中避免使用‘*‘当你想在SELECT子句中列出所有的COLUMN时,使用动态SQL列引用‘*'是一个方便的方法.不幸的是,这是一个非常低效的方法.实际上,ORACLE在解析的过程中,会将'*'依次转换成所有的列名,这个工作是通过查询数据字典完成的,这意味着将耗费更多的时间.2)尽量多使用COMMIT只要有可能,在程序中尽量多使用COMMIT,这样程序的性能得到提高,需求也会因为COMMIT所释放的资源而减少:COMMIT所释放的资源:a.回滚段上用于恢复数据的信息.b.被程序语句获得的锁c.redologbuffer中的空间d.ORACLE为管理上述3种资源中的内部花费3)减少对表的查询次数在含有子查询的SQL语句中,要特别注意减少对表的查询.1)例如:低效SELECTTAB_NAMEFROMTABLESWHERETAB_NAME=(SELECTTAB_NAMEFROMTAB_COLUMNSWHEREVERSION=604)ANDDB_VER=(SELECTDB_VERFROMTAB_C0LUMNSWHEREVERSION=604)高效SELECTTAB_NAMEFROMTABLESWHERE(TAB_NAME,DB_VER)=(SELECTTAB_NAME,DB_VER)FROMTAB_COLUMNSWHEREVERSION=604)2)Update多个Column例子:低效:UPDATEEMPSETEMP_CAT=(SELECTMAX(CATEGORY)FROMEMP_CATEGORIES),SAL_RANGE=(SELECTMAX(SAL_RANGE)FROMEMP_CATEGORIES)WHEREEMP_DEPT=0020;高效:UPDATEEMPSET(EMP_CAT,SAL_RANGE)=(SELECTMAX(CATEGORY),MAX(SAL_RANGE)FROMEMP_CATEGORIES)WHEREEMP_DEPT=0020;4)用EXISTS替代IN在许多基于基础表的查询中,为了满足一个条件,往往需要对另一个表进行联接.在这种情况下,使用EXISTS(或NOTEXISTS)通常将提高查询的效率.低效:SELECT*FROMEMP(基础表)WHEREEMPNO>0ANDDEPTNOIN(SELECTDEPTNOFROMDEPTWHERELOC=‘MELB')高效:SELECT*FROMEMP(基础表)WHEREEMPNO>0ANDEXISTS(SELECT‘X'FROMDEPTWHERE=ANDLOC=‘MELB')用IN来替换OR下面的查询可以被更有效率的语句替换:

低效:SELECT….FROMLOCATION

WHERELOC_ID=10

ORLOC_ID=20ORLOC_ID=30高效SELECT…FROMLOCATIONWHERELOC_ININ(10,20,30);5)用Where子句替换HAVING子句：避免使用HAVING子句，HAVING只会在检索出所有记录之后才对结果集进行过滤。这个处理WHERE例如:低效:SELECTREGION，AVG(LOG_SIZE)FROMLOCATIONGROUPBYREGIONHAVINGREGION!=‘SYDNEY'ANDREGION!=‘PERTH'高效SELECTREGION，AVG(LOG_SIZE)FROMLOCATIONWHEREREGIONREGION!=‘SYDNEY'ANDREGION!=‘PERTH'GROUPBYREGIONHAVING中的条件一般用于对一些集合函数的比较，如COUNT()应该写在WHERE子句中。其他相关技术1.作业调度A.说明1）定时执行数据转储：用ORACLE的作业调度的功能。2）定义作业：通过WEB方式的ORACLE管理界面定义作业。作业名所有者命令类型：PL/SQL块PL/SQL：包名。过程名如果包不属于定义的所有者，需要指定包所在用户名。begin;end;调度：按每天的方式执行，也可以按每月的方式执行3）包的执行顺序4）对作业的监控在ORACLE的管理界面中有日志B.使用TOAD实现作业调度：方式一：直接定义Jobs（作业）1．选择创建一个作业，输入名称2．输入首次执行时间(FirstExcution)3．输入执行频率(SubsequentExcution)如：每天早上六点执行：TRUNC(SYSDATE+1)+6/24注意：当作业成功完成时，系统才会计算下一次执行时间（SYSDATE+1）4．选择单选框“Parse”（默认）——在定义作业的时候解析存储过程或选择“NoParse”——在执行作业的时候解析存储过程5．最后在“Whattoexcute”栏中输入作业的具体内容或点击该区域的右上角按钮选择一个存储过程。优点：适用不频繁改动或不改动作业本身即作业执行频率的情况。缺点：作业定义与作业执行频率在一起定义不利于管理。方式二：定义（作业调度）1．新建一个Program（相当于作业内容）或Schedule过程可选）2．新建一个，输入名称3．在ProgamInfo的Tab页选择存储过程（SpecifyProgramInfo）或预定义的Program

（UsePredefinedProgram）4．在ScheduleInfo的TabSpesifyScheduleInfo下例：）或预定义的Schedule（UsePredefinedSchedule例：StartDate:2:00:00.000000+08:00EndDate:8:00:00.000000+08:00RepeatInterval:FREQ=DAILY;INTERVAL=1优点：1作业和作业执行频率可以分开定义，并且组合出许多作业调度2更多作业属性可以被定义缺点：相对于方式一，稍显繁琐。2.物化视图物化视图具有视图的特性，但是又不同于视图。可以基于SELECT语句创建物化视图，但是物化视图可以物理的保存和存储数据。OracleDatabase10G使对表的汇总计算转向对物化视图的查询.这里给出一个创建物化视图的例子：CREATEMATERIALIZEDVIEW"REPDB".""REFRESHFORCESTARTWITHto_date('30-6ÔÂ-200611:38:30ÉÏÎç','dd-Mon-yyyyHH:MI:SSAM')NEXTsysdate+1/86400ASSELECT_id,_name,FROMl_deptdept,l_employeeempWHERE_id=_id3.物化视图日志供数据来源。物化视图日志的名称为MLOG$_后面跟基表的名称，如果表名的长度超过20位，则只取前20位，当截短后出现名称重复时，Oracle会自动在物化视图日志名称后面加上数字作为序号物化视图日志中包含目标表的目标列，除此之外还包括以下常用列：列名称说明SNAPTIME$$表示刷新时间DMLTYPE$$表示DMLI表示INSERTD表示DELETEU表示UPDATEOLD_NEW$$N（EWO（LDU表示UPDATE操作时，如果所观察的值没有变化，则记录旧值的行被标记为USEQUENCE$$（可选）给每个操作一个SEQUENCE号，从而保证刷新时按照顺序进行刷新4.索引设计与维护引。1）：对于小表来说使用索引对于性能不会有任何提高。2)提高（每个表中的ID）。25%）如果你要返回的数据很多时索引会加大系统开销。3)需要恢复索引即可。4)：索引会占用数据库的空间设计数据库时，要考虑索引所占用的空间（索引和表一般应该放在不同的表空间）。5)更新索引，增大系统开销）。6)WHERE提高数据的访问速度。5.分区设计负担，提高DML和查询的性能。1）表分区范围分区：指定应该存储在一起的数据范围。例如时间CREATETABLEL_PURCHASE(ORDER_IDNUMBERNOTNULL,ORDER_NUMBERNUMBER,SUPPLYER_IDNUMBER,UNITNUMBER,PRICENUMBER,ORDER_DATEDATE)PARTITIONBYRANGE(ORDER_DATE)(PARTITIONPART1VALUESLESSTHAN(TO_DATE('2006-06-1600:00:00','SYYYY-MM-DDHH24:MI:SS'))TABLESPACEL_TS_PART1PARTITIONPART2VALUESLESSTHAN(TO_DATE('2007-06-1600:00:00','SYYYY-MM-DDHH24:MI:SS'))TABLESPACEL_TS_PART2)2）索引分区局部索引分区6.并发控制在转储时，对中间表进行表级锁定。Locktable<tablename>inexclusivemodenowait;用户能够在该表上使用的唯一的SQL语句是select语句。在该锁被解除以前，不允许其他用户在此表上进行插入、修改或任何DDL操作SETTRANSACTIONISOLATIONLEVELREADCOMMITTED;COMMIT语句终止事务，永久保存数据库的变化，同时释放所有LOCK,ROLLBACK终止现行事务释放所有LOCK，但不保存数据库的任何变化在存储过程开始调用LOCK，在执行COMMIT或ROLLBACK后事务完成，自动解锁。7.查询表占用空间查询表分配空间：select*fromuser_segmentswheresegment_name=UPPER('REPORT_TABLE')查询BLOB字段分配空间select*fromuser_segmentswheresegment_name=(selectsegment_namefromuser_lobswheretable_name=UPPER('REPORT_TABLE'))查询字段实际占用：selectSUM(LENGTH(content))fromREPORT_TABLE8.导入导出带BOLB字段的表导出：EXPUSERID=REPDNEW/=‘E:\’TABLES=REPORT_TABLE导入：IMPUSERID=REPDNEW/=‘E:\’TABLES=REPORT_TABLE专题1.索引1)定义索引是与表和簇相关的可选的结构，它能使对应于表的SQL语句执行得更快。正如有Oracle不用重写任何查询而使用索引。其结果是一样的，但感觉更快Oracle提供了几种索引模式，它们提供复杂的效率功能它们是：B-树索引——默认的和最常用的B-树簇索引——特意为簇定义的索引散列簇索引——特意为散列簇定义的索引全局和局部索引——相应于分区表和索引逆转键索引——特别适用于Oracle真实应用簇的应用位图索引——紧凑的特别适用于具有少量值集的列基于函数的索引——包含函数/表达式的预先计算的值域索引——特别针对应用或插件索引在逻辑上和物理上都与相关的表的数据无关。作为无关的结构，索引需要存储空间创建或删除。一个索引不会影响基本的表、数据库应用或其他索引。当插入、更改和删Oracle访问以前被索引了的数据时就可能慢了。2)添加索引准则在插入或装载了数据后为表创建索引更加有效率如果需要经常地检索大表中的少于15%的簇越多，百分比越高。为了改善多个表的联结的性能，索引列常用于联结。注意主键和唯一键自动具有索引但应该在外键上创建索引小表不需要索引。如果查询所花的时间太长了，可能这个表已经由小变大了。某些列特别适合作索引，具有如下一个或多个特征：1）列中的值相对比较唯一2）取值范围大（适合作常规索引）3）取值范围小（适位作位图索引）列中有许多空值，但经常查询所有具有值的行，此时使用如下语法：WHERECOL_X>-9.99*power(10,125)上面的语法比下面的好WHERECOL_XISNOTNULL这是因为前一句使用了COL_X列上的索引假设COL_X是一个数值列具有如下特征的列不太适合作索引：1）列中有许多空值，但又不查询非空值。LONG和LONGRAW列不能被索引。单个索引项的大小不能比数据块中可用空间的一半多太多（减去某些杂项开销）。3)创建索引1.创建索引条件要在你自己的模式中创建索引，至少要满足如下条件之一：要被索引的表或簇是在你自己的模式中你在要被索引的表上有INDEX权限你具有CREATEANYINDEX系统权限要在其他模式中创建索引，要满足如下所有条件：你具有CREATEANYINDEX系统权限其他模式的拥有者在保存索引或索引分区的表空间中有限额或者他具有UNLIMITEDTABLESPACE系统权限2.创建索引首先建立一张表：CREATETABLEemp(enameVARCHAR2(10BYTE),eidVARCHAR2(10BYTE))A.明确地创建索引可以用CREATEINDEXemp表的ename列上创建一个名为emp_ename的索引：CREATEINDEXemp_enameONemp(ename)TABLESPACEusersSTORAGE(INITIAL20KNEXT20kPCTINCREASE75)PCTFREE0;注意为该索引明确地指定了几个存储设置和一个表空间。如果没有给索引指定存储选项（如INITIAL和NEXT）就自动使用默认或指定的表空间的默认存储选项。B.明确地创建唯一索引一索引不会在列值上施加这个限制。可用CREATEUNIQUEINDEX语句来创建唯一索引。如下例子创建一个唯一索引:CREATEUNIQUEINDEXemp_unique_indexONemp(eid)TABLESPACEusers;换句语说，可以在希望的列上定义UNIQUE完整性约束。Oracle通过自动地在唯一键上定义一个唯一索引来保证UNIQUE完整性约束。C.创建位图索引指定，将会建立B-Tree索引，下例将建立一个位图（BitMap）索引：createbitmapindexemp_indexonemp(ename)D.创建与约束相关索引Oracle通过在唯一键或主键上创建一个唯一索引，来在表上实施UNIQUEKEY或PRIMARYKEY完整性约束，当启用约束时Oracle自动创建该索引。创建默认的索引如下例，当我们建立一个含有主键的表CREATETABLEemp1(empnoNUMBER(5)PRIMARYKEY,ageINTEGER)ENABLEPRIMARYKEY;这个时候会自动创建一个名字为SYS_C的UNIQYE索引。我们还可以为默认索引指定存储参数，如下例：CREATETABLEemp1(empnoNUMBER(5)PRIMARYKEY,ageINTEGER)ENABLEPRIMARYKEYUSINGINDEXTABLESPACEusersPCTFREE0;创建指定的索引用USINGINDEX子句来指定这些选项如下几个语句是几个例子：例1CREATETABLEa(a1INTPRIMARYKEYUSINGINDEX(createindexaiona(a1)));例2CREATETABLEb(b1INT,b2INT,CONSTRAINTbu1UNIQUE(b1,b2)USINGINDEX(createuniqueindexbionb(b1,b2)),CONSTRAINTbu2UNIQUE(b2,b1)USINGINDEXbi);例3CREATETABLEc(c1INT,c2INT);CREATEINDEXciONc(c1,c2);ALTERTABLEcADDCONSTRAINTcpkPRIMARYKEY(c1)USINGINDEXci;子句，以便在使用索引之前先创建索引。E.创建基于函数的索引通过创建基于函数的索引我们可以：创建更强有力的分类可以用UPPER和LOWER函数执行区分大小写的分类，用DESC关键词执行降序分类，用NLSSORT函数执行基于语言的分类。预先计算出计算密集的函数的值并在索引中将其分类可以在索引中存储要经常访问的、计算密集的函数。当需要访问值时该值已经计算出来了，因此，极大地改善了查询的执行性能。增加优化器可以执行范围扫描而不是全表扫描的情况的数量例如考虑如下WHERE子句中的表达式CREATEINDEXidxONExample_tab(column_a+column_b);SELECT*FROMexample_tabWHEREcolumn_a+column_b<10;因为该索引是建立在（column_a+column_b）之上的，所以优化器可以为该查询使用范围扫描。创建和使用函数索引条件：为了在你自己的模式中创建基于函数的索引，你必须被授予QUERYREWRITE系统权限为了在另一个模式中或在另一个模式的表上创建索引，你必须具有CREATEANYINDEX和GLOBALQUERYREWRITE权限必须定义如下初始化参数才能创建基于函数的索引：将QUERY_REWRITE_INTEGRITY设置为TRUSTED将QUERY_REWRITE_ENABLED设置为TRUE将COMPATIBLE设置为.0或更大的值另外为使用基于函数的索引需要：在创建索引后必须分析表因为NULL值不会被保存在索引中所以必须保证查询不需要被索引的表达式中的任何NULL值某些使用基于函数的索引的例子如下：enameemp上创建基于函数的索引idxCREATEINDEXidxONemp(UPPER(ename));现在SELECT语句使用UPPER（ename）上的基于函数的索引来检索所有的名字开头为JOH的职员SELECT*FROMempWHEREUPPER(ename)LIKE'JOH%';例：用基于函数的索引来预先计算算术表达式这条语句基于一个表达式创建一个基于函数的索引CREATEINDEXidxONt(a+b*(c-1),a,b);SELECT语句可以使用索引范围扫描（在下面的SELECT语句中，表达式是该索引的前缀）或者索引完全扫描（当索引指定了一个高并行度时更可取）SELECTaFROMtWHEREa+b*(c-1)<100;4)更改索引更改索引权限：为了更改索引，你的模式必须包含该索引，或者你必须要具有ALTERANYINDEX系统权限。更改索引方式：a)更改索引的存储特征用ALTERINDEX语句来更改任何索引的存储参数。包括那些由Oracle创建的用于实施主键和唯一键的完整性约束的索引。例如如下语句更改emp_ename索引：ALTERINDEXemp_enameSTORAGE(PCTINCREASE50);不能更改存储参数INITIAL和MINEXTENTS配给该索引的分区。b)重建索引不适当的筛分或被增加的增量可能产生索引碎片，为了消除或减小碎片可以重建或合并索引。重建索引时是将现存的索引作为数据源。用这种方法创建索引，允许修改存储特征或将索引再用CREATEINDEX语句来创建索引而言，重建现存的索引提供了更好的性。如下语句重建现存的索引emp_name：ALTERINDEXemp_nameREBUILD;REBUILD子句必须立即地紧跟在索引名之后并出现在任何其他选项之前它c)合并索引在B-树叶子数据块能被释放重用的情况下，可以用如下语句将这些叶子数据块合并：ALTERINDEXemp_enameCOALESCE;d)监视索引使用Oracle提供了一种监视索引的方法以便决定它们正在被使用或没被使用。如果一个索引目前没被使用，那么，可将其删除因而减少了不需要的语句负荷。为了开始监视一个索引的使用发布如下语句：ALTERINDEXemp_enameMONITORINGUSAGE以后发布如下语句来停止该监视：ALTERINDEXemp_enameNOMONITORINGUSAGE可用查询视图V$OBJECT_USAGEUSEDYES或NO取决于该索引在被监视期间是否已经被使用。该视图还包含该监视阶段的开始和结束时间。还包含一个MONITORING列（YES/NO），以标识是否现在激活了使用监视。每次指定MONITORINGUSAGE时，V$OBJECT_USAGE视图都被针对指定的索引进行重新设置，以前的使用信息被清除或重新设置，新的开始时间被记录下来。当指定NOMONITORINGUSAGE又不执行进一步的监视时，则该监视阶段的结束时间被记录下来，在下一次发布ALTERINDEXMONITORINGUSAGE语句之前，该视图的信息保持不变。5)删除索引删除索引权限：要想删除索引则该索引必须包含在你的模式中，或你必须具有DROPANYINDEX系统权限删除索引原因：不再需要该索引该索引没有为针对其相关的表所发布的查询提供所期望的性能改善。例如表可能很小，或者尽管表中有许多行但只有很少的索引项。应用没有用该索引来查询数据该索引已经变成无效了必须在重建之前删除该索引该索引已经变得太碎了必须在重建之前删除该索引删除索引方式：1.对于明确创建的索引，使用DROPINDEXDROPINDEXemp_ename;2.删除一个与约束相关的索引要删除一个与约束相关的索引，必须停用或删除该约束本身。3．删除表也就自动也删除了所有与其相关的索引删除索引结果：对象6)察看索引信息下面的视图显示有关索引的信息：视图说明DBA_INDEXESDBA视图描述数据库中所有表上的索引，USER视图局限于用户所拥有的索ALL_INDEXESALL视图描述用户可以访问的所有表上的索引。这些视图中的某些列USER_INDEXES包含有由DBMS_STATS包或ANALYZE语句产生的统计数据。DBA_IND_COLUMNS这些视图描述表上的索引的列。这些视图中的某些列包含有由ALL_IND_COLUMNSDBMS_ATATS包或ANALYZE语句产生的统计数据。USER_IND_COLUMNSDBA_IND_EXPRESSIONSDBA_IND_EXPRESSI这些视图描述表上的基于函数的索引的表达式ALL_IND_EXPRESSIONSUSER_IND_EXPRESSIONSINDEX_STATS存储最后一条ANALYZEINDEXVALIDATESTRUCTURE语句所产生INDEX_HISTOGRAM的信息V$OBJECT_USAGE包含由ALTERINDEXMONITORINGUSAGE语句所产生的索引使用的信息7)屏蔽索引语句的执行计划中有不良索引时，可以人为地屏蔽该索引（通过添加计算或函数），方法：数值型：在索引字段上加0，例如select*fromempwhereemp_no+0=v_emp_no;字符型：在索引字段上加‘’，例如select*fromtg_cdr01wheremsisdn||’’=v_msisdn;8)索引限制可能是oracle的索引限制造成的。oracleoracle索引的系统开销造成性能更差。下面是一些常见的索引限制问题。1、使用不等于操作符(<>,!=)下面这种情况，即使在列dept_id有一个索引，查询语句仍然执行一次全表扫描select*fromdeptwherestaff_num<>1000;但是开发中的确需要这样的查询，难道没有解决问题的办法了吗？有！通过把用or语法替代不等号进行查询，就可以使用索引，以避免全表扫描：上面的语句改成下面这样的，就可以使用索引了。select*fromdeptsherestaff_num<1000ordept_id>1000;2、使用isnull或isnotnull使用isnull或isnuonullnull列中有很多nullblog文章里做详细解释）。在sql语句中使用null会造成很多麻烦。解决这个问题的办法就是：建表时把需要索引的列定义为非空(notnull)3、使用函数where子句中对存在索引的列使用函数时，会使优化器忽略掉这些索引。下面的查询就不会使用索引：select*fromstaffwheretrunc(birthdate)='01-MAY-82';但是把函数应用在条件上，索引是可以生效的，把上面的语句改成下面的语句，就可以通过索引进行查找。select*fromstaffwherebirthdate<(to_date('01-MAY-82')+0.9999);4、比较不匹配的数据类型dept_id是一个varchar2型的字段，在这个字段上有索引，但是下面的语句会执行全表扫描。select*fromdeptwheredept_id=900198;这是因为oracle会自动把where子句转换成to_number(dept_id)=900198，就是3所说的情况，这样就限制了索引的使用。把SQL语句改为如下形式就可以使用索引select*fromdeptwheredept_id='900198';5、使用like子句使用like子句查询时，数据需要把所有的记录都遍历来进行判断，索引不能发挥作用，这种情况也要尽量避免。9)索引优化经常一起使用多个字段检索记录，组合索引比单索引更有效；把最常用的列放在最前面，例：CREATEINDEXemp_indexONemp(ename,eid)在where条件中使用ename或ename,eideid使用索引察看使用SETAUTOTRACEON察看SQL语句使用索引情况，如下例：SQL>SETAUTOTRACEONSQL>select*fromempwhereename='KING'注：在数据库服务器本机使用SQLPlus可以使用本功能SETAUTOTRACE使用语法：SETAUTOT[RACE]{OFF|ON|TRACE[ONLY]}[EXP[LAIN]][STAT[ISTICS]]注：关于索引的更多说明，参考Oracle9i数据库管理员指南.pdf2.分区表1)定义分区表是对VLDB（VeryLargeDateBase，非常大的数据库）的一种支持和维护方法。分区表允许将数据分成被称为分区甚至子分区的更小的、更好管理的块。表或索引的分区和子分区都共享相同的逻辑属性（例如共享相同的列和约束定义）。分区操作对现存的应用和运行在分区表上的标准DML语句来说是透明的。2)使用分区的准则1.使用范围分区要想将行映射到基于列值范围的分区就使用范围分区方法。当数据可以被划分成逻辑范要考虑其他的分区方法了。2.使用列表分区当你需要明确地控制如何将行映射到分区时，就使用列表分区方法。可以在每个分区的设计的。3.使用散列分区如果数据不那么容易进行范围分区，但为了性能和管理的原因又想分区时，就使用散列因为你可以通过在I/O能。4.使用组合（复合）分区组合分区方法是在分区中使用范围分区方法分区数据，而在子分区中则使用散列分区方法。组合分区很适于历史数据和条块数据两者，它改善了范围分区及其数据放置的管理性，并提供了散列分区的并行机制的优点。3)创建分区1.创建范围分区创建范围分区时，你须指定：分区方法：范围（PARTITIONBYRANGE）分区列标识分区边界的分区描述表空间（可选）如下列子创建一个4个分区的表。一个分区对应一个季度的销售情况列。sale_yearsale_month和sale_day是分区列，即它们的值组成一个特殊的行的分区键。VALUESLESSTHAN行将被存储在分区中。每个分区都有名字（sales