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

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

开发规范

1.书写规范

1）：程序头书写规范

程序头开始部分应说明程序整体的功能，存储过程名称，编写人，编写日期，修改人，修

改日期，版本号与过程涉及的表与视图。示比如下：

名称及实现功能：

版本：（版本号标示：新建VI.0.0小的修改变为VI.0.1大的修改VI.1.0重构V2.0.0）

CreatebyCreateDate2UU6-U6-29

UpdatebyupdateDate2006-06-30

修改原因：

UpdatebyupdateDate2006-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

一查询员工及对应的部门名称

SELECT,

FROMl_deptdept,l_employeeeirp

WHEREemp.dept_id=dept.dept_id;

END;

3）注释书写规范

为了提高可读性，应该使用一定数量的注释。注释大约占总行数的1/5。

1：注释风格：注释单独成行、放在语句前面。

2：应对不易懂得的分支条件表达式加注释；

3：对重要的计算应说明其功能；

4：过长的函数实现，应将其语句按实现的功能分段加以概括性说明；

5：每条SQL语句均应有注释说明

6：关于程序的整体功能，应在程序开始部分说明，可使用单行/多行注释。（-或者/**/

方式）

2.命名规范

命名对象规则样例

1业务有关以模块代码开头qld_assist_check_p

2假如区分全量与增量，在最后加标识gld_load_to_etl

存储过程、包、方法

gld_load_to_etl_ful1

3全局使用，以global开头global_procedure_check

变量以v开头v_updatemode1

游标以c开头c_tablist

内存表以m开头m_tablel

临时表以t开头t_tmpTable

存储过程技术

1.存储过程样例

CREATEORREPLACEPROCEDUREexample(

v_inputINNUMBER,一输入参数

v_outputOUTNUMBER一输出参数

)

IS

PRAGMAAUTONOMOUS_TRANSACTION;

CURSORcl一定义一个游标，在begin之前

IS

SELECTb.tablenamemlogtable,

MAX(remarks)KEEP(DENSE_RANKLASTORDERBYstarttime)

remarks

FROMproc_loga,table_procb

WHERETOCHAR(starttimc,1yyyy-mm-dd')<-一一转换时间并做比4,交

TOCHAR(SYSDATE-TODSINTERVAL(

TO_CHAR(intervaldays)||,00:00:00*),*yyyy-mm-dd')

ANDa.remarksLIKE'SUCCEEDED:%1

ANDa.procedurename=b.procedurename

GROUPBYb.tablename);一定义结束

Cl带ROWTYPE;一定义同意游标数据行的ROWTYPE

v_mlogtableVARCHAR(30);

v_postperiodCHAR(2);

1bpginsaqNUMRF.R;

v_systimeDATE;

BEGIN

v_input:-0;变量赋值

v_systime:=SYSDATE;

OPENcl;一打开游标

LOOP--循环

FETCHclINTOcl_rec;一从当前游标行赋值cl_rec

EXITWHENcl%NOTFOUND;一游标没有数据退出

v_mlogtable;-cl_rec.mlogtable;一从行取出具体数据赋给变量

CASETRIM(LOWER(v_mlogtable))—CASE起始

WHEN'Stringl1--当条件一

THEN--做条件一工作

BEGIN

v_remarks:=REPLACE(v_remarks,*AA*);

END;

WHEN'String2'―当条件二

THEN

BEGIN

END;

ELSE—其他条件

NULL;

ENDCASE;--CASE结束

1

IF(LOWER(SUBSTR(v_mlogtablez1,5))<>'mlog$)

THEN

SELECTlog_table

INTOv_mlogtable

FROMuser_snapshot_logs

WHERELOWER(MASTER)=LOWER(v_mlogtable);

ENDIF;

EXECUTEIMMEDIATE'deletefrom

IIv_mlogtable

I|'wheresequence$$<='

I|TO_CHAR(v_lognum);

EXITWHEN1>2;一循环跳出条件

ENDLOOP;一循环结束

4)变量的声明

在变量声明区声明变量的名称与类型

例：v_postperiodCHAR(2);

可赋初值

v_postperiodCHAR⑵：='01';

(这里叫变量声明区可能并不恰当，由于游标、自定义类型等，一切需要事先声明的都应在这里声明。)

5)变量的赋值

使用'：='为变量赋值

1.直接使用基本类型赋值

例：v_number:=1;

2.使用函数赋值

例：v_date:=sysdate;

3.使用SQL语句为变量赋值

1〉通过sql直接赋值

SELECTCOUNT(*)

INTOv_tmpnumber

FROMetl_ods_masterdata_tablist;

2〉通过构造SQL赋值：

v_tmpsql:=

'SELECTlog_tableFROMuser_snapshct_logs'

I|v_dblink

I|'WHEREUPPER(MASTER)=UPPER('1'

I|v_singletab

II"

EXECUTEIMMEDIATEv_tmpsql

INTOv_tmpvarchar;

6)循环

i.无限或者简单循环

LOOP

EXITWHEN(退出循环条件)；

ENDLOOP；

2.while循环

WHILEcondition

LOOP

executable_statements;

ENDLOOP;

3.工循环

基于数字的for循环：

FORfor_indexINlov/_value..high_value

LOOP

executable_statements;

ENDLOOP；

基于游标的for循环：

FORrecord_indexINmy_cursor

LOOP

PXPClltAhlAtPHlADtS；

ENDLOOP；

7)调用其他过程或者方法

1.假如单独定义，直接使用

例：v_retvalO:=

(v_updatemode,

v_systime,

'mlog$_glddocheader,,

v_procname,

v_docheaderbeginseq,

v_docheaderendseq

)；

2.假如定义在包下，使用包名+过程名

例：(v_procname);

3.固定用法与函数

标识作用用法或者类型

固定用法：

SYSDATE当前系统时间DATE

SQLCODE特殊代码VARCHAR2

SQLERRM特殊描述VARCHAR2

NO_DATA_FOUND未找到数据特殊与when搭配

OTHERS其他所有特殊与when搭配

RAISE抛出当前特殊RAISE;

MIN(B)KEEP(DENSE_RANKFIRSTORDER

BYA)

DENSE_RANK非选取字段排序

MAX(B)KEEP(DENSE_RANKLASTORDER

BYA)

PRAGMA使用自治事务，能够使该过程被调用Begin之前使用PRAGMA

AUTONOMOUS_TRANSA时单独提交AUTONOMOUS_TRANSACTION;

CTION将前面执行结果大批放入后面的集合BULKCOLLECTINTOcolumntab;

BULKCOLLECTINTO中

SQL%ROWCOUNT前一个DML语句执行影响行数作为NUMBER型使用

v_nuirber:=SQL%ROWCOUNT

DBMS_OUTPUT.put_l输出信息

ine()

函数

转换NCHAR、NVARCHAR2、CLOB、TO_CHAR(A)

NCLOD

TO_CHAR

转换DATE型为指定格式TO_CHAR(time,'yyyy-mm-dd*)

转换NUMBER型为指定格式TO_CHAR(564.70,'$999.9')

TO_DATE转换字符串为指定日期to_date('1900-01-011,'YYYY-MM-DD*)

INSTR(string,subs返回目标字符串中子字符串的位置。INSTR('bug-archie',*archie')

tring(,postion)(,(起始位置与出现次数为可选)INSTR('haracter?archie*,'a*,1,2)

occurrence))

LENGTH获得指定字符串长度LENGTH('CANDIDE')

LOWER将指定字符串转换成小写LOWER('LETTERS')

UPPER将指定字符串转换成大写UPPER('letters')

LPAD(strl,n,str2)将strl用str2左补齐至n位LPAD('55',10,'O')

RPAD(strl,n,str2)将strl用str2右补齐至n位RPAD('55',10,'01)

去掉指定字符串左侧的指定字符或者LTRIM('Way1)

LTRIM

,,,,

字符集合，默认为空格LTRIM(123123Wayz123)

去掉指定字符串右侧的指定字符或者RTRIM('WayxyXxyxy',*xy')

RTRIM

字符集合，默认为空格

POWER(m,n)计算m的n次方POWER(2,3)

Extract(yearfrom取出date的年

date)

4.ROWTYPE的使用

能够使用%type与％rowtype属性实现使用其他变量、数据库列或者表的数据类型的引

用。机ype属性提供了所需要的变量的类型及长度。%rowtype属性同意人们定义一个记录变

量，它的成员变量拥有表中每一列正确的类型及长度，使用点符号引用记录中的每个成员变

量。这种动态赋值方法是非常有用的，比如变量引用的列的数据类型与大小改变了，假如使

用了%TYPE,那么用户就不必修改代码，否则就务必修改代码。

CREATETABLEEMPLOYEE(

EMP_IDNUMBERNOTNULL,

EMP_NAMECHAR(20),

CREATE_DATEDATS)

DECLARE

v_studentrecordemployee彩ROWTYPE;

employee.create_date%TYPE;

BEGIN

SELECT*

INTOv_studentrecord

FROMemployee

WHEREemp_id=1;

n:=v_studentrecord.create_date;

DBMS_OUTPUT.put_line(n);

END;

5.内存表的使用

内存表要紧作为数组用。

1)：一个字段：

PROCEDUREtl

IS

TYPEt_CISTABLEOFtesta.al%TYPE

INDEXBYBINARY_INTEGER;

aatc;

BEGIN

aa(0):='aaa*;

DBMS_OUTPUT.put_line(aa(0));

END;

2):定义多个字段:

PROCEDUREtl

IS

TYPEt_rISRECORD(

tlVARCHAR(10),

t2VARCHAR(10)

)；

TYPEt_tISTABLEOFt_r

INDEXBYBINARY_INTEGER;

aat_t;

BEGIN

aa(0).tl:=*aaa,;

aa(0).t2:=*bbb';

DBMS_OUTPUT.put_lino(aa(0).tl);

DBMS_OUTPUT.put_line(aa(0).t2);

END;

6.游标的使用

游标是用来处理使用SELECT语句从数据库中检索到的多行记录的工具。借助于游标的

功能，数据库应用程序能够对一组记录逐个进行处理，每次处理一行。

DECLARE

nNUMBER;

CURSORc

IS

SELECT*

FROMemployee;

BEGIN

FORv_cINc

LOOP

n:=v_n.pmp_ici;

DBMS_OUTPUT.put_line(n);

ENDLOOP;

EXCEPTION

WHENOTHERS

THEN

DBMS_OUTPUT.put_line('error');

END;

7.跟踪调试

根踪调试要紧是检查程序运行的情况，能够在需要检查程序是否执行正确作为输出的根

据：DBMS_OUTPUT.PUT_LINE(G_USERID(-2));

执行时设置：setserveroutputon

8.临时表

临时表用于储存事务或者者会话的中间结果，临时表中储存的数据只有对当时的会话

是可见的，任何会话都不能看见其他会话的数据。即使COMMIT之后也是不可见的。关于临时

表并行不是问题，即使锁定也不能阻止其他程序的访问。每个数据库创建临时表一次，(ORACLE

的DDL语句是一种消耗较大的动作)并不用每个程序创建一次，同时临时表总保持为空。

下面这个例子能够说明临时表的运行过程：

CREATEGLOBALTEMPORARYTABLEREPDB.L_EMP_DEPT_TEMP

(

EMP_IDVARCHAR(5)z

EMP_NAMEVARCHAR：20),

DEPT_IDVARCHAR：5),

DEPT_NAMEVARCHAR：20)

)

1DECLARE

2DL_EMP_DEPT_TEMP%ROWTYPE;

3CURSORCIS

4SELECTE.EMP」DAA,E.EMP_NAMEBB,D.DEPT_IDCC,D.NAMEDD

5FROML_EMPLOYEEE,L_DEPTD

6WHEREF.DFPID=DDEPTID：

7BEGIN

8FORV_CINCLOOP

9INSERTINTOL_EMP_DEPT_TEMP

10VALUES(V_C.AA,V_C.BB.V_C.CC,V_C.DD);

11ENDLOOP;

12*END;

SQL>/

PL/SQL过程已成功完成。

SQL>SELECTCOUNT(*)

2FROML_EMP_DEPT_TEMP

3/

COUNT(*)

3

SQL>COMMIT

2/

提交完成。

SQL>SELECTCOUNT(*)

2FROML_EMP_DEPT_TEMP

3/

COUNT(*)

0

9.特殊处理

例外是一个非致命事件，它立即中断程序的正常执行并引起一个非条件转移，跳转到当

前程序块的例外处理部分。一些例外，像NODATEFOUND或者TOMANYROWS,属于预定义例外

用于处理常见的。racle错误，能够被认为是正常的处理部分。部分ERROR这样的例外说明一个

程序错误或者一些意料之外的事件。如下所示：

1)：正常处理的部分

1DECLARE

2NCHAR;

3BEGIN

4SELECTEMP_NAME

5INTON

6FROMEMPLOYEE;

7DBMS_OUTPUT.PUT_L1NECN');

8*END;

SQL>/

DECLARE

*

第1行出现错误：

ORA-01422:实际返回的行数超出请求的行数

ORA-06512:在line4

1DECLARE

2NCHAR;

3BEGIN

4SELECTEMP.NAME

5INTON

6FROMEMPLOYEE;

7DBMS_OUTPUT.PUT_LINE(N);

8EXCEPTIONWHENTOO_MANY_ROWSTHEN

9DBMS_OUTPUT.PUT_LINE(TOOMANYROWSRETURN');

10*END;

PL/SQL过程已成功完成。输出结果为：TOOMANYROWSRETURN

2):非正常处理的部分，自定义特殊

SQL>insertintol_eniployee

2values('4','dd',3,sysdate,'20()0')

3/

insertintol_cniployce

*

第1行出现错误：

ORA-02291:违反完整约束条件(REPDB.FK_EMP_DEPT)-未找到父项关键字

处理方法：自定义特殊

1declare

2eexception;

3pragmaexccplion_init(e,-2291);

4begin

5insertintol_empioyee

6values('6','dd',3,sysdaie；2OOO');

7exceptionwhenethen

8DBMS_OUTPUT.PUT_LINEC违反完整约束条件(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.ACPStorkFlAssAnaTabtorepdbnew

revokeselectoncs_new.BILInvoiceUseEntityDatafromrepdbnew

注意：不能为当前用户授权

14.建立同义词

CREATESYNONYMACPStorkFlAssAnaTabFORjcerp.ACPStorkFlAssAnaTab

dropSYNONYMBILInvoiceUseEntityData

注：建立同义词后用户能够用select访问，但不能建立视图。

15.为表字段加注释

COMMENTONCOLUMNHAN_2.BBBBIS'B字段，

16.触发器

createorreplacetriggerqytestl_trigger3BEFOREINSERTONmlog$_qyt2Stl

FOREACHROW

begin

:new.snaptime$$:=SYSDATE;

and;

17.自定义类型的赋值

自定义类型：

CREATEORREPLACETYPErepdbnew.INPARAMASVARRAY(50)OF

VARCHAR2(25);

自定义类型的赋值：

declare

indatanameinparam;

begin

indataname:=inparam(2);

indataname:=inparam(*isCalCount*,*NOTX1);

end;

18.OBJECTTYPES

曾举来言兑，OracleObjectTypes就是Oracle以TYPE的方式来‘酊兄物件(Objects)的方法，宣告/

定羲的方法，^似於Package。ObjectType的宣告/定羲中包含了它的Attributes/Properties典

Methods,也就是MemberFunctions/Procedureso

本篇5k介貂利用OracleObjectTypes来做中介暂存的^作。

建立OracleObject:

定善il低1Object的内容，能够把它想像卷所希望的RowColumns的定善。

viewplaincopytoclipboardprint?

CREATETYPEtype_objASOBJECT(

collINTEGER,

CO12VARCHAR2(60)

)；

/

CREATETYPEtype_objASOBJECT(collINTEGER,col2VARCHAR2(60));/

建立ObjectCollection:

建立一催ITableType,ilflUTable装的资料列内容(悯位)就是之前所建立的Object。

viewplaincopytoclipboardprint?

CREATEORREPLACE

TYPEtype_tabISTABLEOFtype_obj;

/

CREATEORREPLACETYPEtype_tabISTABLEOFtype_obj;/

在PL/SQL中的l癖用：

定装好前面雨做1Typo,就能够拿它来在程式中^作了.

viewplaincopytoclipboardprint?

DECLARE

-宣告典初始化

obj_type_tabtype_tab:=type_tab();

BEGIN

一多肇^值

obj_type_tabtype_tab(

type_obj(37,,col2_vall«),

typp_r>bj(*ccl2_va12')z

type_obj(254,'co12val3*));

-逐肇给值

obj_type_tab.EXTEND;

obj_type_tab(obj_type_tab.LAST):=type_obj(12,'col_2_val1);

要逐第给或者一次^多第就触粹看使用畤械典方便性.黎侬也襦己^有硅切可知的数肇资料畤,就能够用

多肇^的方式来定羲使用.但是,假如资料来源是取自其他媒介,比方^Cursor,可能就需要跑Loop去

逐肇取值典给值了.

*/

/**

W,能够把已^存放好资料的。bj_type_tab,像下面道槿鹰用方式去取回资料.

富然,聚一反三,它能够用道棣子的SELECT方式樽化成ReferenceCursor及其他相^^用.

*/

FORrecIN(SELECTcoll,CO12FROMTABLE(CAST(obj_type_tabASTYPE_TAB)))

LOOP

DBMS_OUTPUT.put_line('rec.coll='IIrec.collII

,;rec.col2='IIrec.co12);

ENDLOOP;

/**

翰出^果:

rec.coll=37;rec.col2=col2_vall

rec.coll=3;rec.coll=col2_val2

rec.coll=254;rec.col2=col2_val3

rec.coll=12;rec.co12-col_2_val

*/

END;

19.Tabletype的使用

定义：resultsetOUTLabletype

使用：OPENresultsetFOR

SELECTDISTINCTcname,b.paramneeded

FROMtemp_tablenamesa,table_procb

WHEREUPP5R(a.tablename)=b.tablename;

查洵：DECLARE

TABNAMESTABNAMES_OBJ;

RESULTSETGETTABLENAMES.TABLETYPE;

w_idVARCHAR2(30);

h_idVARCHAR2(30)；

BEGIN

TABNAMES:=(TABNAMES_OBJ(tabname_list_typ('REP_FT_CO_CPCB_V1)));

GETTABLENAMES.GETTABLESOFVIEW(TABNAMES,RESULTSET);

T.OOP

FETCHRESULTSETINTOw_id,h_id;

EXITWHENRESULTSET%NOTFOUND;

DBMSJDUTPUT.put_line(w_id|I'J||h_id);

ENDLOOP;

COMMIT;

END;

20.创建DB・Link

createdatabaselink"erpcar"--dblink名称,与连接实例名相同

connecttojferp一连接使用用户

identifiedby*'qmnerpn--用户密码

using•ERPCAR'--连接实例

21.SQL优化

1)SELECT子句中避免使用'*'

当你想在SELECT子句中列出所有的COLUMN时，使用动态SQL列引用是一个方便的方法.不幸的

是,这是一个非常低效的方法.实际上QRACLE在解析的过程中，会将如依次转换成所有的列名，这个工

作是通过查询数据字典完成的，这意味着将耗费更多的时间.

2)尽量多使用COMMIT

只要有可能,在程序中尽量多使用COMMIT,这样程序的性能得到提高,需求也会由于COMMIT所释放的资

源而减少：

COMMIT所释放的资源：

a.回滚段上用于恢复数据的信息.

b.被程序语句获得的锁

c.redologbuffer中的空间

d.ORACLE为管理上述3种资源中的内部花费

3）减少对表的查询次数

在含有子查询的SQL语句中，要特别注意减少对表的查询.

1)比如：

低效

SELECTTAB.NAME

FROMTABLES

WHERETAR_NAME=(SEIECTTAR_NAME

FROMTAB_COLUMNS

WHEREVERSION=604)

ANDDB_VER=(SELECTDB_VER

FROMTAB_C0LUMNS

WHEREVERSION=604)

高效

SELECTTAB.NAME

FROMTABLES

WHERE(TAB_NAME.DB_VER)

=(SELECTTAB_NAME,DB_VER)

FROMTAB_COLUMNS

WHEREVERSION=604)

2)Update多个Column例子：

低效：

UPDATEEMP

SETEMP.CAT=(SELECTMAX(CATEGORY)FROMEMP_CATEGORIES),

SAL_RANGE=(SELECTMAX(SAL_RANGE)FROMEMP_CATEGORIES)

WHEREEMP_DEPT=0020;

高效：

UPDATEEMP

SET(EMP.CAT,SAL.RANGE)

=(SELECTMAX(CATEGORY),MAX(SAL_RANGE)

FROMEMP_CATEGORIES)

WHEREEMP_DEPT=0020;

4)用EXISTS替代IN

在许多基于基础表的杳询中，为了满足一个条件，往往需要对另一个表进行联接.在这种情况下，使用

EXISTS(或者NOTEXISTS)通常将提高查询的效率.

低效：

SELECT*

FROMEMP(基加匕表)

WHEREEMPNOX)

ANDDEPTNOIN(SELECTDEPTNO

FROMDEPT

WHERELOC=4MELB')

高效：

SELECT*

FROMEMP(基础表)

WHEREEMPNO>0

ANDEXISTS(SELECT4X'

FROMDEPT

WHEREDEPT.DEPTNO=EMP.DEPTNO

ANDLOC='MELB，)

用IN来替换OR

下±1.的查询能够被更有效率的语句替换：

低效：

SELECT....

FROMLOCATION

WHERELOC_ID=10

ORLOC_ID=20

ORLOC_ID=30

高效

SELECT...

FROMLOCATION

WHERELOC_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—PERTHGROUPBYREGION

HAVING中的条件通常用于对一些集合函数的比较，如COUNT。等等。除此而外，通常的条件应该写在

WHERE子句中。

其他有关技术

L作业调度

A.说明

1）定时执行数据转储：用ORACLE的作业调度的功能。

2）定义作业：通过WEB方式的ORACLE管理界面定义作业。

作业名

所有者

命令类型：PL/SQL块

PL/SQL：包名。过程名

假如包不属于定义的所有者，需要指定包所在用户名。

begin

SYLTEST.TESTSCHE;

end;

调度：按每天的方式执行，也能够按每月的方式执行

3）包的执行顺序

假如多个包有依靠关系，能够用存储过程定义执行顺序（在定时器中不能定义）。

4）对作业的监控

在ORACLE的管理界面中有日志

B.使用TOAD实现作业调度：

方式一：直接定义Jobs（作业）

1.选择创建一个作业，输入名称

2.输入首次执行时间（FirstExcution）

3.输入执行频率（SubsequentExcinion）

如：每天早上六点执行：TRUNC（SYSDATE+1）+6/24

注意：当作业成功完成时，系统才会计算下一次执行时间（SYSDATE+I）

4.选择单选框“Parse”（默认）一一在定义作业的时候解析存储过程

或者选择"NoParse"一一在执行作业的时候解析存储过程

5.最后在“Whattoexcute”栏中输入作业的具体内容或者点击该区域的右上角按钮选择一个存储过程。

优点：适用不频繁改动或者不改动作业本身即作业执行频率的情况。

缺点：作业定义与作业执行频率在一起定义不利于管理。

方式二：定义SchecLJobs（作业调度）

I.新建一个Program（相当于作业内容）或者Schedule（相当于作业运行时间与频率）（此过程可选）

2.新建一个Sched.Jobs,输入名称

3.在ProgainInfo的Tab页选择存储过程（SpecifyProgramInfo）或者预定义的Program（UsePredefined

Program）

4.在ScheduleInfo的Tab页输入开始、结束时间与运行频率（SpcsifyScheduleInfo）（如下例；）或者预

定义的Schedule(UsePredefinedSchedule)o

例：StartDate:2008/01/0902:00:00.000000+08:00

EndDate:2009/01/0908:00:00.000000+08:00

RepeatInterval:FREQ=DAILY:INTERVAL=1

优点：1作业与作业执行频率能够分开定义，同时组合出许多作业调度

2更多作业属性能够被定义

缺点：相关于方式一，稍显繁琐,

2.物化视图

物化视图具有视图的特性，但是又不一致于视图。能够基于SELECT语句创建物化视图,

但是物化视图能够物理的储存与存储数据。OracleDatabase10G使对表的汇总计算转向对

物化视图的查询.这里给出一个创建物化视图的例子：

CREATEMATERIALIZEDVIEW”REPDB”.””

REFRESHFORCE

STARTWITHto_date(*30-6OA-200611:38:30EIIQ','dd-Mon-yyyyHH:MI:SSAM')

NEXTsysdate+1/86400

AS

SELECTemp.emp_id,emp.emp_name,dept.NAME

FROMl_deptdept,l_employeeemp

WHEREemp.dep_id=dept.dept_id

3.物化视图日志

物化视图日志是根据目标表的目标字段建立的一张表，它记录了目标表的目标字段发生

增、删、改时的过程，使我们能够得到这些过程数据，为增量更新提供数据来源。

物化视图日志的名称之MLOG$_后面跟基表的名称，假如表名的长度超过20位，则只取

前20位，当截短后出现名称重复时，Oracle会自动在物化视图日志名称后面加上数字作为

序号

物化视图日志中包含目标表的目标列，除此之外还包含下列常用歹IJ：

列名称说明

SNAPTIME$$表示刷新时间

DMLTYPE$$表示DML操作类型，I表示INSERT,D表示DELETE,U表示UPDATE

OI,D_NFW$$表示这个值是新值坏是旧值cN(EW)表示新值,O(LD)表示IH值.U表示

UPDATE操作时，假如所观察的值没有变化，则记录旧值的行被标记为U

SEQUENCE$$(可选)给每个操作一个SEQUENCE号，从而保证刷新时按照顺序进行刷新

4.索引设计与保护

数据库会建立一个物理索引对象，而每次运行查询的时候都访问同一个索引。

1)：关于小表来说使用索引关于性能不可能有任何提高。

2)：当你的索引列中有极多的不一致的数据与空值时索引会使性能有极大的提高(每

个表中的ID）o

当查询要返回的数据很少时索引能够优化查询（比较好的情况是少于全部数据的

25%）假如你要返回的数据很多时索引会加大系统开销。

3）：索引能够提高数据的返回速度，但是它使得数据的更新操作变慢在对记录与索引

进行大量的更新操作更新时，应先删除索引，当执行完更新操作后只需要恢复索引即可。

4）：索引会占用数据库的空间设计数据库时，要考虑索引所占用的空间（索引与表通

常应该放在不一致的表空间）。

5）：不要创建对经常需要更新或者修改的字段创建索引（每次更新数据都要更新索引，

增大系统开销）。

6）：经常在WHERE子句中出现的字段需要加索引，这样能够避免全表扫描，提高数据

的访问速度。

5.分区设计

在物理上把一个表或者索引分割成更小、更易于管理的区块。就应用程序访问数据库

而言，只有一个表或者一个索引，在物理上构成这个表，或者者索引可能有很多分区，每个

分区都是一个独立的对象，他能够由自己操作，或者者是更大的对象的一部分。分区的目的

在于简化大型表与索引的管理。提高可用性，减轻管理负担，提高DML与查询的性能。

1）表分区

范围分区：指定应该存储在一起的数据范围。比如时间

CREATETABLELPURCHASE（

ORDERIDNUMBERNOTNULL,

ORDERNUMBERNUM