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

1、存储过程开发规范与技巧开发规范1.书写规范1)：程序头书写规范 程序头开始部分应说明程序整体的功能，存储过程名称，编写人，编写日期，修改人，修 改日期，版本号以及过程涉及的表和视图。示例如下：/*名称及实现功能 :版本 : (版本号标示:新建 V1.0.0小的修改变为 V1.0.1Createby *CreateDate2006 - 06- 29Updateby * updateDate2006 - 06 - 30修改原因Updateby * updateDate2006 - 06 - 31修改原因 :大的修改 V1.1.0 重构 V2.0.0)涉及的表或视图：dump_init辅助表 (DM

2、) ：记录存储过程中使用的物化视图日志序号mlog$_acrcusmrsecindexft_gld_customerdata*/CREATE OR REPLACE源表(ODS):客户第一索引物化视图日志，使用同义词目标表 (DM) ：客户事实表PROCEDURE *2):代码书写规范 语句中出现的所有表名、字段名全部小写，系统保留字、内置函数名、 字大写。连接符or、in、and、以及=、=、=等前后加上一个空格。where 子句书写时，每个条件占一行，语句令起一行时，以保留字或者连接符开1.2.3.Sql 保留始，连接符右对齐。.查询的WHERE滤，原则应使过滤记录数最多的条件

3、放在最前面。多表连接时，使用表的别名来引用列。查找数据库表或视图时，只能取出确实需要的那些字段，不要使用*来代替所有列名。功能相似的过程和函数，尽量写到同一个包中，加强管理。示例如下：BEGIN-查询员工及对应的部门名称SELECT emp . name , FROM l_deptdept , l_employeeempWHEREemp . dept_id= dept . dept_id ;END;1/5。3）注释书写规范123456 式）为了提高可读性，应该使用一定数量的注释。注释大约占总行数的注释风格：注释单独成行、放在语句前面。应对不易理解的分支条件表达式加注释；对重要

4、的计算应说明其功能；过长的函数实现，应将其语句按实现的功能分段加以概括性说明； 每条SQL语句均应有注释说明对于程序的整体功能，应在程序开始部分说明，可采用单行/多行注释。（-或/* */方2. 命名规范命名对象规则样例存储过程、包、方法1业务相关以模块代码开头gld_assist_check_ p2如果区分全量和增量，在最后加标识gld_load_to_etl gld_load_to_etl_full3全局使用，以global开头global_ procedure_check变量以V开头v_up datemode1游标以C开头c tablist内存表以m开头m table1临时表以t开头t_

5、tmp Table存储过程技术1存储过程样例CREATE ORREPLACEv_inputINPROCEDURE example (- 输入参数NUMBER,v_output ) ISOUTNUMBER- 输出参数PRAGMAAUTONOMOUS_TRANSACTION ;- 定义一个游标，在 begin 之前CURSORc1ISSELECTMAX. tablename mlogtable ,( remarks )KEEP (DENSE_RANK LAST ORDER BY starttimeremarksFROM proc_loga , table_proc b( starttime , &

6、#39;yyyy-mm-dd' ) ( SYSDATE - TO_DSINTERVAL TO_CHAR ( intervaldaysAND a . remarks LIKEWHERETO_CHARTO_CHAR) |'SUCCEEDED:%'' 00:00:00'),<= - 转换时间并做比较 ('yyyy-mm-dd'AND a . procedurenamec1_recv_mlogtableGROUP BY b . tablename );%ROWTYPE;VARCHAR ( 30);CHAR ( 2);NUMBER;c1= b

7、 . procedurename- 定义结束- 定义接受游标数据行的 ROWTYPEv_postperiod v_acctbalbeginseq v_systimeDATE;BEGINv_input v_systime0;- 变量赋值OPEN c1 ;LOOPSYSDATE ;- 打开游标FETCH c1 INTO c1_recEXIT WHENc1 %NOTFOUND;v_mlogtablec1_rec . mlogtable- 循环- 从当前游标行赋值 c1_rec- 游标没有数据退出- 从行取出具体数据赋给变量CASE TRIM ( LOWER ( v_mlogtable )WHEN &

8、#39;String1'THENBEGIN-CASE 起始- 当条件一- 做条件一工作v_remarksREPLACE ( v_remarks , 'AA' );END;WHEN 'String2'THEN当条件二BEGINEND;ELSE- 其他条件NULL;END CASE;-CASE 结束1, 5) <> 'mlog$' )IF ( LOWER ( SUBSTR ( v_mlogtableTHENSELECT log_tableINTO v_mlogtableFROMuser_snapshot_logsWHERELOWE

9、R (MASTER) = LOWER ( v_mlogtable ); END IF;EXECUTE IMMEDIATE'delete from '| v_mlogtable| TO_CHAR' where sequence$ <=( v_lognum);EXIT WHEN 1>2;END LOOP;- 循环跳出条件- 循环结束CLOSE c1 ;- 关闭游标EXCEPTIONWHEN OTHERSTHENROLLBACK;global_procedure_checkv_systime. check_end( 'checkdataerror01

10、9;1,SQLCODE | );| SQLERRMRAISE;RETURN;END;END example ;2基本知识1) 基本结构CREATE OR REPLACE PROCEDURE exampleIS( parameters ) - 过程声明区v_1NUMBER;- 过程中变量声明区BEGINv_1:= 0;- 过程内容区END example ;2) 基本类型CHARVARCHAR2VARCHARNUMBERDATE固定长度字符类型 可变长字符类型 可变长字符类型 ( 不建议使用 ) 一切数值类型一切日期类型3) 参数三种： IN 输入参数， OUT 输出参数， IN OUT 输入输

11、出参数。4) 变量的声明在变量声明区声明变量的名称和类型例： v_postperiod CHAR ( 2);可赋初值v_postperiod CHAR ( 2):= '01 '(这里叫变量声明区可能并不恰当，因为游标、自定义类型等，一切需要事先声明的都应在这里声明。)5) 变量的赋值使用： = '为变量赋值1直接使用基本类型赋值例： v_number := 1;2. 使用函数赋值例： v_date :=sysdate ;使用 SQL 语句为变量赋值 通过 sql 直接赋值SELECT COUNT (*)INTO v_tmpnumberFROM etl_ods_maste

12、rdata_tablist312通过构造 SQL 赋值：v_tmpsql :='SELECT log_table FROM user_snapshot_logs'| v_dblink|' WHERE UPPER (MASTER) = UPPER ('''| v_singletab|''')' ;EXECUTE IMMEDIATE v_tmpsqlINTO v_tmpvarchar6)循环1. LOOP无限或简单循环EXIT WHEN (退出循环条件 );END LOOP;2. while 循环WHILE condi

13、tionLOOPexecutable_statements;END LOOP;3. for 循环 基于数字的 for 循环：FOR for_index IN low_value . high_value LOOPexecutable_statements;END LOOP;基于游标的 for 循环：FOR record_index IN my_cursorLOOPexecutable_statements;END LOOP;7）调用其他过程或方法1.如果单独定义，直接使用 例：v retvalO :=f_dum p_initv_systime(v_updatemode'mlog$_gl

14、ddocheader'v_procn ame v_docheaderbeg in seq v_docheadere ndseq);2.如果定义在包下，使用包名+过程名(v_procname )；例：global_ procedure_check.check_ru n3 .固定用法和函数标识作用1用法或类型固定用法：SYSDATE当前系统时间DATESQLCODE异常代码VARCHAR2SQLERRM异常描述VARCHAR2NO_DATA_FOUND未找到数据异常与whe n 搭配OTHERS其他所有异常与whe n 搭配RAISE抛岀当前异常RAISE;DENSE_RANK非选取字段排

15、序MIN (B) KEEP ( DENSE_RANK FIRST ORDER BY A)MAX(B) KEEP (DENSE_RANK LAST ORDERBY A)PRAGMAAUTONOMOUS_TRANSACTIONBULK COLLECT INTOSQL%ROWCOUNT使用自治事务，可以使该过程被调用 时单独提交Begin之 前 使 用P RAGMAAUTONOMOUS_TRANSACTION;将前面执行结果大批放入后面的集合 中BULK COLLECT INTO columntab;前一个DML语句执行影响行数作为NUMBER型使用v_number:= SQL% ROWCOUNTD

16、BMS_OUT PUT.put_l ine ()输岀信息函数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,subs trin g(, postio n)(, occurre nee)返回目标

17、字符串中子字符串的位置。（起始位置和岀现次数为可选）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&#

18、39;,10,'0')RPAD( str1,n,str2)将str1 用str2右补齐至n位RPAD ( '55',10,'0')LTRIM去掉指定字符串左侧的指定字符或字 符集合，默认为空格LTRIM ( ' Way')LTRIM ( '123123Way', '123')RTRIM去掉指定字符串右侧的指定字符或字 符集合，默认为空格RTRIM ( 'Way xyXxyxy', 'xy')POWER( m,n)计算m的n次方POWER 2, 3)Extract (y

19、ear from date)取岀date 的年4. ROWTYPE 的使用可以使用%type和%rowtype属性实现使用其他变量、数据库列或表的数据类型的引 用。type属性提供了所需要的变量的类型及长度。% rowtype属性允许人们定义一个记录变量，它的成员变量拥有表中每一列正确的类型及长度，使用点符号引用记录中的每个成员变 量。这种动态赋值方法是非常有用的，比如变量引用的列的数据类型和大小改变了，如果使 用了 TYPE那么用户就不必修改代码，否则就必须修改代码。CREATE TABLEEMP IDEMPLOYEE (NUMBERNOT NULL,EMP NAMECHAR ( 20),C

20、REATE DATEDATE)DECLAREv stude ntrecordemp loyee %ROWTY PE;empioyee . create_date %TYPE;BEGINSELECT *INTO v studentrecordFROM employeeWHEREemp_id = 1;v stude ntrecord.create dateDBMS_OUTPUT . put_lineEND;5内存表的使用内存表主要作为数组用。1）：一个字段：PROCEDUREt1ISTYPE t_c ISTABLE OF testa. a1%TYPEINDEX BYBINARY_INTEGERaa

21、 t_cBEGINaa ( 0) := 'aaa' ; DBMS_OUTPUT . put_line( aa( 0);END;2）：定义多个字段：PROCEDUREt1ISTYPE t_rIS RECORD (t1VARCHAR ( 10),t2VARCHAR ( 10);TYPE t_tIS TABLE OF t_rINDEX BY BINARY_INTEGERaa t_tBEGINaa( 0). t1aa( 0). t2'aaa''bbb'DBMS_OUTPUT. put_line( aa( 0).t1 );DBMS_OUTPUT. put_

22、line( aa( 0).t2 );END;6游标的使用SELEC语句从数据库中检索到的多行记录的工具。借助于游标的游标是用来处理使用功能，数据库应用程序可以对一组记录逐个进行处理，每次处理一行。DECLARENUMBER;CURSOR cISSELECT *FROM employee ;BEGINFOR v_cIN cLOOPv_c . emp_id ;DBMS_OUTPUT . put_line( n);END LOOP;EXCEPTIONWHEN OTHERSTHENDBMS_OUTPUT . put_line( 'error' );END;7跟踪调试根踪调试主要是检查程

23、序运行的情况，可以在需要检查程序是否执行正确作为输出的依 据：DBMS_OUTPUT.PUT_LINE(G_USERID(-2);执行时设置： set serveroutput on8临时表临时表用于保存事务或者会话的中间结果，临时表中保存的数据只有对当时的会话是 可见的，任何会话都不能看见其他会话的数据。即使 COMMITS后也是不可见的。对于临时表 并行不是问题，即使锁定也不能阻止其他程序的访问。每个数据库创建临时表一次， (ORACLE 的DDL语句是一种消耗较大的动作)并不用每个程序创建一次，并且临时表总保持为空。面这个例子可以说明临时表的运行过程：CREATE GLOBAL TEMP

24、ORARY TABLE REPDB . L_EMP_DEPT_TEMP(EMP_ID VARCHAR( 5), EMP_NAMEDEPT_IDDEPT_NAMEVARCHAR( 20 ),VARCHAR( 5),VARCHAR( 20 )DECLARED L_EMP_DEPT_TEMP%ROWTYPE;CURSOR C ISSELECT E.EMP_ID AA ,E.EMP_NAME BB ,D.DEPT_ID CC ,D.NAME DDFROM L_EMPLOYEE E,L_DEPT DWHERE E.DEP_ID=D.DEPT_ID;BEGINFOR V_C IN C LOOP101112

25、*INSERT INTO L_EMP_DEPT_TEMPVALUES (V_C.AA,V_C.BB,V_C.CC,V_C.DD);END LOOP;END ;SQL> /PL/SQL 过程已成功完成。SQL> SELECT COUNT(*)2 FROM L_EMP_DEPT_TEMPCOUNT(*)SQL> COMMIT提交完成。SQL> SELECT COUNT(*)2 FROM L_EMP_DEPT_TEMPCOUNT(*)9异常处理例外是一个非致命事件，它立即中断程序的正常执行并引起一个非条件转移，跳转到当前程序块的例外处理部分。一些例外，像 NO_DATE_FO

26、UNDO_MANY_RO W属于预定义例外用 于处理常见的oracle错误，可以被认为是正常的处理部分。部分ERrO这样的例外表明一个程 序错误或一些意料之外的事件。如下所示：1)：正常处理的部分 DECLARE N CHAR; BEGIN SELECT EMP_NAME INTO NFROM EMPLOYEE; DBMS_OUTPUT.PUT_LINE('N');8* END; SQL> / DECLARE 第 1 行出现错误 :ORA-01422: 实际返回的行数超出请求的行数ORA-06512: 在 line 4DECLARE N CHAR; BEGIN SELEC

27、T EMP_NAME INTO N FROM EMPLOYEE; DBMS_OUTPUT.PUT_LINE(N); EXCEPTION WHEN TOO_MANY_ROWS THEN DBMS_OUTPUT.PUT_LINE('TOO MANY ROWS RETURN');10* END;PL/SQL 过程已成功完成。 输出结果为： TOO MANY ROWS RETURN2)：非正常处理的部分，自定义异常SQL> insert into l_employee2 values ('4','dd','3',sysdate,&#

28、39;2000')3 /insert into l_employee 第 1 行出现错误 :ORA-02291: 违反完整约束条件 (REPDB.FK_EMP_DEPT) - 未找到父项关键字 处理方法：自定义异常declare e exception ;pragma exception_init(e,-2291);45678begininsert into l_employee values ('6','dd','3',sysdate,'2000') ;exception when e thenDBMS_OUTPUT.P

29、UT_LINE(' 违反完整约束条件 (REPDB.FK_EMP_DEPT)');9* end ;SQL> /PL/SQL 过程已成功完成。 输出结果为： 违反完整约束条件 （REPDB.FK_EMP_DEPT）10嵌套程序块的内部可以有另一个程序块这种情况称为嵌套。嵌套要注意的是变量，定义在最 外部程序块中的变量可以在所有子块中使用，如果在子块中定义了与外部程序块变量相同的 变量名，在执行子块时将使用子块中定义的变量。子块中定义的变量不能被父块引用。如果字块需要单独提交，应使用自治事务。11标签用户可以使用标签使程序获得更好的可读性。 程序块或循环都可以被标记。 标签的

30、形式是 <>。要求使用标签。12记录转储开始时间和结束时间1）在建立中间表后，用脚本或手工在数据表中建立一条记录，以后每次都更新。对每个转储只记录一条记录，不保存历史记录。2）在开始转储时， 读取上次转储结束时间， 只转储从上次转储以来的新增或修改的记录。3）在转储的存储过程中记录开始时间和结束时间， 是否成功。如果失败， 记录失败原因。4）可以用 SQL 语句查找失败的转储，可以查找转储时间过长的转储。表名：转储记录( TRANSLOG ) 字段：程序包名存储过程名中间表名开始时间结束时间成功标识失败原因13授权grant selecton cs_new . ACPStorkFl

31、AssAnaTabto repdbnewrevoke selecton cs_new . BILInvoiceUseEntityDatafrom repdbnew注意：不能为当前用户授权14建立同义词CREATE SYNONYMACPStorkFlAssAnaTabFOR jcerp. ACPStorkFlAssAnaTabdrop SYNONYMBILInvoiceUseEntityData注：建立同义词后用户可以用 select 访问，但不能建立视图。15为表字段加注释COMMENT ON COLUMNHAN_2 . BBBB IS 'B 字段' ;16触发器create

32、or replacetrigger qytest1_trigger3BEFORE INSERT ON mlog$_qytest1FOR EACH ROWbegin:new. snaptime$ end;SYSDATE ;17.自定义类型的赋值自定义类型：CREATE OR REP LACE TYPE rep db new.INPARAMAS VARRAY( 50) OFVARCHAR2（ 25）;自定义类型的赋值:declarein data name inp aram beginin data namein data name= inparam ( 2);inparam( 'isCa

33、lCount'，NO_TX'）；end;18. OBJECT TYPES以 TYPE簡單來說，Oracle Object Types 就是 Oracle 的方式來實現物件（Objects ）的方法，宣告/Prop ertiesType 的宣告/定義中包含了它的 Attributes/定義的方法，類似於Package。ObjectMethods，也就是 Member Fun cti ons/P rocedures本篇來介紹利用 Oracle Object Types來做中介暫存的實作。建立Oracle定義這個 Objectview plaincopyCREATEObject:的內

34、容，可以把它想像為所希望的 to clipboardprint?Row Columns 的定義。col1TYPE type_obj AS OBJECT ( INTEGER ,col2VARCHAR2(60);/CREATETYPE type_objAS OBJECT ( col1INTEGER , col2 VARCHAR2(60 );/建立 Object Collection建立一個 Table Type，這個Table裝的資料列內容（欄位）就是之前所建立的Object view plaincopy to clipboardprint?CREATE OR REP LACETYPE typ e

35、_tab/IS TABLE OF type_objIS TABLE OF typ e_obj;/CREATE OR REP LACE TYPE typ e_tab在PL /SQL 中的應用：定義好前面兩個Type，就可以拿它來在程式中實作了。view plaincopy to clipboardprint?DECLARE-宣告與初始化：=typ e_tab ();obj_typ e_tab typ e_tabBEGIN-多筆給值obj_ty pe_tabtyp e_objtyp e_objtyp e_obj-逐筆給值 obj_typ e_tab obj_typ e_tab:=typ e_tab

36、(37, 'col2_val1'(3, 'col2_val2'(254 , 'col2_val3'.EXTEND;(obj_type_tab)，),);.LAST) := type_obj( 12 , 'col_2_val'）；/*要逐筆給或一次給多筆就純粹看使用時機與方便性.舉例來說，已經有確切可知的數筆資料時，就可以用多 筆給的方式來定義使用.但是，如果資料來源是取自其他媒介，比方說Cursor, 可能就需要跑Loop 去逐筆取值與給值了.*/*再來，可以把已經存放好資料的obj_t yp e_tab,當然，舉一反三，它可以用

37、這樣子的SELECT方式轉化成Reference Cursor像下面這種應用方式去取回資料.及其他相關應用.*/FOR rec IN (SELECT coll , col2 FROM TABLE(CAST( obj_type_tabAS TYPE_TAB )LOOPDBMS_OUTPUT.put_line ( ' rec.coll =':rec.col2 ='II rec . coll IIII rec . col2 );END LOOP;/*輸出結果：rec.collrec.coHrec.coHrec.coH=37; rec.col2 = col2_val1=3; r

38、ec.col2 = col2_val2=254; rec.col2 = col2_val3=12; rec.col2 = col_2_val*/END;19. Tabletype 的使用定义："esultset使用：OPEN resultsetOUTtabletypeFORSELECT DISTINCT b . procname , bFROM temp_tablenames.paramneededa,table_ procbWHERE UPPER(a. table name ) = b . table name查询：DECLARETABNAMESTABNAMES OBJRESULT

39、SETGETTABLENAMES.TABLETYPE ;w idh idVARCHAR2 (30);VARCHAR2 ( 30);BEGINTABNAMES(TABNAMES_OBJ(tabname_list_typ('REP_FT_CO_CPCB_V');GETTABLENAMES . GETTABLESOFVIEW ( TABNAMES, RESULTSET );LOOPFETCH RESULTSET INTOw_id ， h_idEXIT WHENRESULTSET%NOTFOUND;DBMS_OUT PUT.p ut_li neEND LOOP;(w_id IIII h

40、_id );COMMIT;END;20.创建 DB-Linkcreate database link"erp car"-db link-连接使用用户 -用户密码-连接实例名称，与连接实例名相同connectto jferpiden tifiedby "qmnerp"using'ERP CAR'21 . SQL优化1) SELECT子句中避免使用子句中列出所有的 COLUMN时，使用动态SQL列引用 *'是一个方便的方法不幸的 '*'依次转换成所有的列名，这个工当你想在SELECT是，这是一个非常低效的方法.实际 上

41、'ORACLE在解析的过程中，会将 作是通过查询数据字典完成的，这意味着将耗费更多的时间.2）尽量多使用 COMMIT只要有可能，在程序中尽量多使用 COMMIT,这样程序的性能得到提高，需求也会因为COMMIT所释放的资 源而减少：COMMIT所释放的资源：a. 回滚段上用于恢复数据的信息 .b. 被程序语句获得的锁c. redo log buffer 中的空间d. ORACLE 为管理上述 3 种资源中的内部花费3) 减少对表的查询次数在含有子查询的 SQL 语句中 ,要特别注意减少对表的查询1)例如:低效SELECT TAB_NAMEFROM TABLESWHERE TAB_NA

42、ME = ( SELECT TAB_NAME FROM TAB_COLUMNSWHERE VERSION = 604)AND DB_VER= ( SELECT DB_VERFROM TAB_C0LUMNSWHERE VERSION = 604) 高效SELECT TAB_NAMEFROM TABLESWHERE (TAB_NAME,DB_VER)= ( SELECT TAB_NAME,DB_VER)FROM TAB_COLUMNSWHERE VERSION = 604)2)Update 多个 Column 例子 : 低效:UPDA TE EMPSET EMP_CAT = (SELECT MAX

43、(CATEGORY) FROM EMP_CATEGORIES), SAL_RANGE = (SELECT MAX(SAL_RANGE) FROM EMP_CATEGORIES) WHERE EMP_DEPT = 0020;高效:UPDA TE EMPSET (EMP_CA T, SAL_RANGE)= (SELECT MAX(CATEGORY) , MAX(SAL_RANGE) FROM EMP_CATEGORIES)WHERE EMP_DEPT = 0020;4) 用 EXISTS 替代 IN. 在这种情况下 , 使用在许多基于基础表的查询中 , 为了满足一个条件 ,往往需要对另一个表进行联

44、接 EXISTS( 或 NOT EXISTS) 通常将提高查询的效率 .低效:SELECT *FROM EMP ( 基础表 )WHERE EMPNO > 0AND DEPTNO IN (SELECT DEPTNOFROM DEPTWHERE LOC = MELB') 高效:SELECT *FROM EMP ( 基础表 )WHERE EMPNO > 0AND EXISTS (SELECT X'FROM DEPTWHERE DEPT.DEPTNO = EMP.DEPTNOAND LOC = MELB')用 IN 来替换 OR 下面的查询可以被更有效率的语句替换

45、低效 :SELECT：FROM LOCATIONWHERE LOC_ID = 10OR LOC_ID = 20OR LOC_ID = 30 高效SELECT：FROM LOCATIONWHERE LOC_IN IN (10,20,30);5) 用 Where 子句替换 HAVING 子句：这个处理需要排序，避免使用 HAVING 子句， HAVING 只会在检索出所有记录之后才对结果集进行过滤。 总计等操作。 如果能通过 WHERE 子句限制记录的数目，那就能减少这方面的开销。例如 : 低效 :SELECT REGION ，AVG(LOG_SIZE) FROM LOCATION GROUP B

46、Y REGION HA VING REGION != SYDNEY' AND REGION !=PERTH'SYDNEY'高效SELECT REGION ，AVG(LOG_SIZE) FROM LOCATION WHERE REGION REGION !=AND REGION != PERTH' GROUP BY REGIONHAVING 中的条件一般用于对一些集合函数的比较，如 WHERE 子句中。COUNT() 等等。除此而外，一般的条件应该写在其他相关技术1. 作业调度A. 说明1)定时执行数据转储：用 ORACLE 的作业调度的功能。2)定义作业：通过

47、WEB 方式的 ORACLE 管理界面定义作业。 作业名 所有者 命令类型： PL/SQL 块PL/SQL :包名。过程名如果包不属于定义的所有者，需要指定包所在用户名。 begin SYLTEST.TESTSCHE;end;调度:按每天的方式执行，也可以按每月的方式执行3)包的执行顺序如果多个包有依赖关系，可以用存储过程定义执行顺序(在定时器中不能定义)4)对作业的监控在 ORACLE 的管理界面中有日志B. 使用 TOAD 实现作业调度：方式一:直接定义 Jobs (作业) 1选择创建一个作业，输入名称 2输入首次执行时间 (First Excution) 3输入执行频率 (Subsequ

48、ent Excution) 如:每天早上六点执行: 注意:当作业成功完成时， 4选择单选框“ Parse” 或选择“ No Parse”5最后在“ What to excute ”栏中输入作业的具体内容或点击该区域的右上角按钮选择一个存储过程。TRUNC(SYSDATE+1)+6/24系统才会计算下一次执行时间(SYSDATE+1 )默认)在定义作业的时候解析存储过程 在执行作业的时候解析存储过程优点：适用不频繁改动或不改动作业本身即作业执行频率的情况。缺点：作业定义与作业执行频率在一起定义不利于管理。方式二：定义 新建一个1.Sched.Jobs （作业调度）Program （相当于作业内容

49、） 或Schedule （相当于作业运行时间和频率）（此过程可选）2.3.4.例:Sched.Jobs,输入名称在 Progam Info 的 Tab 页选择存储过程(Specify Program Info )或预定义的 Program( Use Predefined P rogram)在Schedule Info的Tab页输入开始、结束时间和运行频率(Spesify Schedule Info)(如下例：)或预定义的 Schedule( Use Predefined Schedule)。Start Date:新建一个2008/01/09 02:00:00.000000 +08:002009

50、/01/09 08:00:00.000000 +08:00FREQ=DAIL Y;INTERVAL=1End Date:Rep eat In terval:优点：1作业和作业执行频率可以分开定义，并且组合出许多作业调度 2更多作业属性可以被定义缺点：相对于方式一，稍显繁琐。2. 物化视图物化视图具有视图的特性，但是又不同于视图。可以基于SELECT语句创建物化视图，但是物化视图可以物理的保存和存储数据。Oracle Database 10G 使对表的汇总计算转向对物化视图的查询.这里给出一个创建物化视图的例子：CREATE MATERIALIZED VIEW "REPDB"

51、.""e ?'REFRESH FORCE，dd-Mo n-yyyy HH:MI:SS AM'START WITH to_date （ '30-6? -2006 11:38:30NEXT sysdate + 1 / 86400ASSELECT emp . emp_id , emp. emp_name , dept . NAMEFROM l_dept dept , l_empioyee empWHEREemp . dep_id = dept . dept_id3. 物化视图日志物化视图日志是根据目标表的目标字段建立的一张表，它记录了目标表的目标字段发生

52、增、删、改时的过程，使我们可以得到这些过程数据，为增量更新提供数据来源。物化视图日志的名称为 MLOG$后面跟基表的名称，如果表名的长度超过 20位，则只取 前20位，当截短后出现名称重复时，Oracle会自动在物化视图日志名称后面加上数字作为 序号物化视图日志中包含目标表的目标列，除此之外还包括以下常用列：列名称说明SNA PTIME$表示刷新时间DMLT YP E$表示 DML操作类型，1表示INSERT，D表示DELETE，U表示UP DATEOLD NEW$表示这个值是新值还是旧值。N （EW）表示新值，0 （ LD ）表示旧值，U表示UP DATE操作时，如果所观察的值没有变化，则记

53、录旧值的行被标记为USEQUENCE$ (可选)给每个操作一个 SEQUENCE号，从而保证刷新时按照顺序进行刷新4. 索引设计与维护数据库会建立一个物理索引对象，而每次运行查询的时候都访问同一个索引。1）：对于小表来说使用索引对于性能不会有任何提高。2）:当你的索引列中有极多的不同的数据和空值时索引会使性能有极大的提高（每个 表中的ID） 0当查询要返回的数据很少时索引可以优化查询（比较好的情况是少于全部数据的 25% 如果你要返回的数据很多时索引会加大系统开销。3）:索引可以提高数据的返回速度，但是它使得数据的更新操作变慢在对记录和索引 进行大量的更新操作更新时，应先删除索引，当执行完更新操作后只需要恢复索引即可。4）:索引会占用数据库的空间设计数据库时，要考虑索引所占用的空间（索引和表一 般应该放在不同的表空间）。5）:不要创建对经常需要更新或修改的字段创建索引（每次更新数据都要更新索引， 增大系统开销）。6）:经常