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

存储过程开发规范与技巧开发规范1. 书 关于书的成语关于读书的排比句社区图书漂流公约怎么写关于读书的小报汉书pdf 写规范1）：程序头书写规范程序头开始部分应说明程序整体的功能，存储过程名称，编写人，编写日期，修改人，修改日期，版本号以及过程涉及的 表 关于同志近三年现实表现材料材料类招标技术评分表图表与交易pdf视力表打印pdf用图表说话 pdf 和视图。示例如下：-----------------------------------------------------------------------------/*名称及实现功能:版本:(版本号标示：新建V1.0.0小的修改变为V1.0.1大的修改V1.1.0重构V2.0.0)Createby***CreateDate2006-06-29Updateby***updateDate2006-06-30修改原因:Updateby***updateDate2006-06-31修改原因:涉及的表或视图：dump_init辅助表(DM)：记录存储过程中使用的物化视图日志序号mlog$_acrcusmrsecindex源表(ODS)：客户第一索引物化视图日志,使用同义词ft_gld_customerdata目标表(DM)：客户事实表*/CREATEORREPLACEPROCEDURE*******------------------------------------------------------------------------------2）：代码书写规范1.语句中出现的所有表名、字段名全部小写，系统保留字、内置函数名、Sql保留字大写。2.连接符or、in、and、以及＝、<=、>=等前后加上一个空格。3.where子句书写时，每个条件占一行，语句令起一行时，以保留字或者连接符开始，连接符右对齐。 4.查询的WHERE过滤，原则应使过滤记录数最多的条件放在最前面。5.多表连接时，使用表的别名来引用列。6.查找数据库表或视图时，只能取出确实需要的那些字段，不要使用*来代替所有列名。7.功能相似的过程和函数，尽量写到同一个包中，加强管理。示例如下：BEGIN--查询员工及对应的部门名称SELECTemp.name,dept.nameFROMl_deptdept,l_employeeempWHEREemp.dept_id=dept.dept_id;END;3）注释书写规范为了提高可读性，应该使用一定数量的注释。注释大约占总行数的1/5。1：注释风格：注释单独成行、放在语句前面。 2：应对不易理解的分支条件表达式加注释；3：对重要的计算应说明其功能；4：过长的函数实现，应将其语句按实现的功能分段加以概括性说明；5：每条SQL语句均应有注释说明6：对于程序的整体功能，应在程序开始部分说明，可采用单行/多行注释。（--或/**/方式）2.命名规范命名对象规则样例存储过程、包、 方法 快递客服问题件处理详细方法山木方法pdf计算方法pdf华与华方法下载八字理论方法下载 1业务相关以模块代码开头gld_assist_check_p2如果区分全量和增量，在最后加标识gld_load_to_etlgld_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:%'ANDa.procedurename=b.procedurenameGROUPBYb.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_rec.mlogtable;--从行取出具体数据赋给变量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_check.check_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_check.check_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_TRANSACTIONBULKCOLLECTINTOSQL%ROWCOUNT使用自治事务，可以使该过程被调用时单独提交Begin之前使用PRAGMAAUTONOMOUS_TRANSACTION;将前面执行结果大批放入后面的集合中BULKCOLLECTINTOcolumntab;前一个DML语句执行影响行数作为NUMBER型使用v_number:=SQL%ROWCOUNTDBMS_OUTPUT.put_line（）输出信息函数TO_CHAR转换NCHAR、NVARCHAR2、CLOB、NCLOBTO_CHAR（A）转换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,substring(,postion)(,occurrence))返回目标字符串中子字符串的位置。（起始位置和出现次数为可选）INSTR('bug-archie','archie')INSTR('haracter?archie','a',1,2)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;nemployee.create_date%TYPE;BEGINSELECT*INTOv_studentrecordFROMemployeeWHEREemp_id=1;n:=v_studentrecord.create_date;DBMS_OUTPUT.put_line(n);END;5．内存表的使用内存表主要作为数组用。1）：一个字段：PROCEDUREt1ISTYPEt_cISTABLEOFtesta.a1%TYPEINDEXBYBINARY_INTEGER;aat_c;BEGINaa(0):='aaa';DBMS_OUTPUT.put_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_OUTPUT.put_line(aa(0).t1);DBMS_OUTPUT.put_line(aa(0).t2);END;6．游标的使用游标是用来处理使用SELECT语句从数据库中检索到的多行记录的工具。借助于游标的功能，数据库应用程序可以对一组记录逐个进行处理，每次处理一行。DECLAREnNUMBER;CURSORcISSELECT*FROMemployee;BEGINFORv_cINcLOOPn:=v_c.emp_id;DBMS_OUTPUT.put_line(n);ENDLOOP;EXCEPTIONWHENOTHERSTHENDBMS_OUTPUT.put_line('error');END;7．跟踪调试根踪调试主要是检查程序运行的情况，可以在需要检查程序是否执行正确作为输出的依据：DBMS_OUTPUT.PUT_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_OUTPUT.PUT_LINE('N');8*END;SQL>/DECLARE*第1行出现错误:ORA-01422:实际返回的行数超出请求的行数ORA-06512:在line41DECLARE2NCHAR;3BEGIN4SELECTEMP_NAME5INTON6FROMEMPLOYEE;7DBMS_OUTPUT.PUT_LINE(N);8EXCEPTIONWHENTOO_MANY_ROWSTHEN9DBMS_OUTPUT.PUT_LINE('TOOMANYROWSRETURN');10*END;PL/SQL过程已成功完成。输出结果为：TOOMANYROWSRETURN2）：非正常处理的部分，自定义异常SQL>insertintol_employee2values('4','dd','3',sysdate,'2000')3/insertintol_employee*第1行出现错误:ORA-02291:违反完整约束条件(REPDB.FK_EMP_DEPT)-未找到父项关键字处理方法：自定义异常1declare2eexception;3pragmaexception_init(e,-2291);4begin5insertintol_employee6values('6','dd','3',sysdate,'2000');7exceptionwhenethen8DBMS_OUTPUT.PUT_LINE('违反完整约束条件(REPDB.FK_EMP_DEPT)');9*end;SQL>/PL/SQL过程已成功完成。输出结果为：违反完整约束条件(REPDB.FK_EMP_DEPT)10．嵌套程序块的内部可以有另一个程序块这种情况称为嵌套。嵌套要注意的是变量，定义在最外部程序块中的变量可以在所有子块中使用，如果在子块中定义了与外部程序块变量相同的变量名，在执行子块时将使用子块中定义的变量。子块中定义的变量不能被父块引用。如果字块需要单独提交，应使用自治事务。11．标签用户可以使用标签使程序获得更好的可读性。程序块或循环都可以被标记。标签的形式是<>。要求使用标签。12．记录转储开始时间和结束时间1）在建立中间表后，用脚本或手工在数据表中建立一条记录，以后每次都更新。对每个转储只记录一条记录，不保存历史记录。2）在开始转储时，读取上次转储结束时间，只转储从上次转储以来的新增或修改的记录。3）在转储的存储过程中记录开始时间和结束时间，是否成功。如果失败，记录失败原因。4）可以用SQL语句查找失败的转储，可以查找转储时间过长的转储。表名：转储记录（TRANSLOG）字段：程序包名存储过程名中间表名开始时间结束时间成功标识失败原因13．授权grantselectoncs_new.ACPStorkFlAssAnaTabtorepdbnewrevokeselectoncs_new.BILInvoiceUseEntityDatafromrepdbnew注意：不能为当前用户授权14．建立同义词CREATESYNONYMACPStorkFlAssAnaTabFORjcerp.ACPStorkFlAssAnaTabdropSYNONYMBILInvoiceUseEntityData注：建立同义词后用户可以用select访问，但不能建立视图。15．为表字段加注释COMMENTONCOLUMNHAN_2.BBBBIS'B字段';16．触发器createorreplacetriggerqytest1_trigger3BEFOREINSERTONmlog$_qytest1FOREACHROWbegin:new.snaptime$$:=SYSDATE;end;17．自定义类型的赋值自定义类型：CREATEORREPLACETYPErepdbnew.INPARAMASVARRAY(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_tab.EXTEND;obj_type_tab(obj_type_tab.LAST):=type_obj(12,'col_2_val');/**要逐筆給或一次給多筆就純粹看使用時機與方便性.舉例來說,已經有確切可知的數筆資料時,就可以用多筆給的方式來定義使用.但是,如果資料來源是取自其他媒介,比方說Cursor,可能就需要跑Loop去逐筆取值與給值了.*//**再來,可以把已經存放好資料的obj_type_tab,像下面這種應用方式去取回資料.當然,舉一反三,它可以用這樣子的SELECT方式轉化成ReferenceCursor及其他相關應用.*/FORrecIN(SELECTcol1,col2FROMTABLE(CAST(obj_type_tabASTYPE_TAB)))LOOPDBMS_OUTPUT.put_line('rec.col1='||rec.col1||';rec.col2='||rec.col2);ENDLOOP;/**輸出結果:rec.col1=37;rec.col2=col2_val1rec.col1=3;rec.col2=col2_val2rec.col1=254;rec.col2=col2_val3rec.col1=12;rec.col2=col_2_val*/END;19．Tabletype的使用定义：resultsetOUTtabletype使用：OPENresultsetFORSELECTDISTINCTb.procname,b.paramneededFROMtemp_tablenamesa,table_procbWHEREUPPER(a.tablename)=b.tablename;查询：DECLARETABNAMESTABNAMES_OBJ;RESULTSETGETTABLENAMES.TABLETYPE;w_idVARCHAR2(30);h_idVARCHAR2(30);BEGINTABNAMES:=(TABNAMES_OBJ(tabname_list_typ('REP_FT_CO_CPCB_V')));GETTABLENAMES.GETTABLESOFVIEW(TABNAMES,RESULTSET);LOOPFETCHRESULTSETINTOw_id,h_id;EXITWHENRESULTSET%NOTFOUND;DBMS_OUTPUT.put_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'FROMDEPTWHEREDEPT.DEPTNO=EMP.DEPTNOANDLOC=‘MELB')用IN来替换OR下面的查询可以被更有效率的语句替换:低效:SELECT….FROMLOCATIONWHERELOC_ID=10ORLOC_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：包名。过程名如果包不属于定义的所有者，需要指定包所在用户名。beginSYLTEST.TESTSCHE;end;调度：按每天的方式执行，也可以按每月的方式执行3）包的执行顺序如果多个包有依赖关系，可以用存储过程定义执行顺序（在定时器中不能定义）。4）对作业的监控在ORACLE的管理界面中有日志B.使用TOAD实现作业调度：方式一：直接定义Jobs（作业）1．选择创建一个作业，输入名称2．输入首次执行时间(FirstExcution)3．输入执行频率(SubsequentExcution)如：每天早上六点执行：TRUNC(SYSDATE+1)+6/24注意：当作业成功完成时，系统才会计算下一次执行时间（SYSDATE+1）4．选择单选框“Parse”（默认）——在定义作业的时候解析存储过程或选择“NoParse”——在执行作业的时候解析存储过程5．最后在“Whattoexcute”栏中输入作业的具体内容或点击该区域的右上角按钮选择一个存储过程。优点：适用不频繁改动或不改动作业本身即作业执行频率的情况。缺点：作业定义与作业执行频率在一起定义不利于管理。方式二：定义Sched.Jobs（作业调度）1．新建一个Program（相当于作业内容）或Schedule（相当于作业运行时间和频率）（此过程可选）2．新建一个Sched.Jobs，输入名称3．在ProgamInfo的Tab页选择存储过程（SpecifyProgramInfo）或预定义的Program（UsePredefinedProgram）4．在ScheduleInfo的Tab页输入开始、结束时间和运行频率（SpesifyScheduleInfo）（如下例：）或预定义的Schedule（UsePredefinedSchedule）。例：StartDate:2008/01/0902:00:00.000000+08:00EndDate:2009/01/0908: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/86400ASSELECTemp.emp_id,emp.emp_name,dept.NAMEFROMl_deptdept,l_employeeempWHEREemp.dep_id=dept.dept_id3.物化视图日志物化视图日志是根据目标表的目标字段建立的一张表，它记录了目标表的目标字段发生增、删、改时的过程，使我们可以得到这些过程数据，为增量更新提供数据来源。物化视图日志的名称为MLOG$_后面跟基表的名称，如果表名的长度超过20位，则只取前20位，当截短后出现名称重复时，Oracle会自动在物化视图日志名称后面加上数字作为序号物化视图日志中包含目标表的目标列，除此之外还包括以下常用列：列名称说明SNAPTIME$$表示刷新时间DMLTYPE$$表示DML操作类型，I表示INSERT，D表示DELETE，U表示UPDATEOLD_NEW$$表示这个值是新值还是旧值。N（EW）表示新值，O（LD）表示旧值，U表示UPDATE操作时，如果所观察的值没有变化，则记录旧值的行被标记为USEQUENCE$$（可选）给每个操作一个SEQUENCE号，从而保证刷新时按照顺序进行刷新4.索引 设计 领导形象设计圆作业设计ao工艺污水处理厂设计附属工程施工组织设计清扫机器人结构设计 与维护数据库会建立一个物理索引对象，而每次运行查询的时候都访问同一个索引。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.并发控制在转储时，对中间表进行表级锁定。Locktableinexclusivemodenowait;此语句将在专用模式下锁定该表。假如一张表被一个用户专用锁定，那么另一个用户能够在该表上使用的唯一的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/qmnerp@ERPCAR_10.135.29.69FILE=‘E:\TMP.DMP’TABLES=REPORT_TABLE导入：IMPUSERID=REPDNEW/qmnerp@ERPCAR_10.135.29.67FILE=‘E:\TMP.DMP’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.明确地创建索引可以用CREATEINDEX语句明确地创建索引（不含完整性约束）如下语句在emp表的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_C0030523的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设置为8.1.0.0.0或更大的值另外为使用基于函数的索引需要：在创建索引后必须分析表因为NULL值不会被保存在索引中所以必须保证查询不需要被索引的表达式中的任何NULL值某些使用基于函数的索引的例子如下：例：用于大小写敏感搜索的基于函数的索引如下语句基于ename列的大写字母计算，在表emp上创建基于函数的索引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)重建索引不适当的筛分或被增加的增量可能产生索引碎片，为了消除或减小碎片可以重建或合并索引