精编推荐Oracle存储过程开发规范与技巧.docx
《精编推荐Oracle存储过程开发规范与技巧.docx》由会员分享，可在线阅读，更多相关《精编推荐Oracle存储过程开发规范与技巧.docx（41页珍藏版）》请在冰豆网上搜索。
精编推荐Oracle存储过程开发规范与技巧
【精编推荐】Oracle存储过程开发规范与技巧
存储过程开发规范与技巧
开发规范
1.书写规范
1）：
程序头书写规范
程序头开始部分应说明程序整体的功能，存储过程名称，编写人，编写日期，修改人，修改日期，版本号以及过程涉及的表和视图。
示例如下：
-----------------------------------------------------------------------------
/*
名称及实现功能:
版本:
（版本号标示：
新建V1.0.0小的修改变为V1.0.1大的修改V1.1.0重构V2.0.0）
Createby***CreateDate2006-06-29
Updateby***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.name
FROMl_deptdept,l_employeeemp
WHEREemp.dept_id=dept.dept_id;
END;
3）注释书写规范
为了提高可读性，应该使用一定数量的注释。
注释大约占总行数的1/5。
1：
注释风格：
注释单独成行、放在语句前面。
2：
应对不易理解的分支条件表达式加注释；
3：
对重要的计算应说明其功能；
4：
过长的函数实现，应将其语句按实现的功能分段加以概括性说明；
5：
每条SQL语句均应有注释说明
6：
对于程序的整体功能，应在程序开始部分说明，可采用单行/多行注释。
（--或/**/方式）
2.命名规范
命名对象
规则
样例
存储过程、包、方法
1业务相关以模块代码开头
gld_assist_check_p
2如果区分全量和增量，在最后加标识
gld_load_to_etl
gld_load_to_etl_full
3全局使用，以global开头
global_procedure_check
变量
以v开头
v_updatemode1
游标
以c开头
c_tablist
内存表
以m开头
m_table1
临时表
以t开头
t_tmpTable
存储过程技术
1．存储过程样例
CREATEORREPLACEPROCEDUREexample（
v_inputINNUMBER,--输入参数
v_outputOUTNUMBER--输出参数
）
IS
PRAGMAAUTONOMOUS_TRANSACTION;
CURSORc1--定义一个游标，在begin之前
IS
SELECTb.tablenamemlogtable,
MAX（remarks）KEEP（DENSE_RANKLASTORDERBYstarttime）
remarks
FROMproc_loga,table_procb
WHERETO_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.procedurename
GROUPBYb.tablename）;--定义结束
c1_recc1%ROWTYPE;--定义接受游标数据行的ROWTYPE
v_mlogtableVARCHAR（30）;
v_postperiodCHAR
（2）;
v_acctbalbeginseqNUMBER;
v_systimeDATE;
BEGIN
v_input:
=0;--变量赋值
v_systime:
=SYSDATE;
OPENc1;--打开游标
LOOP--循环
FETCHc1INTOc1_rec;--从当前游标行赋值c1_rec
EXITWHENc1%NOTFOUND;--游标没有数据退出
v_mlogtable:
=c1_rec.mlogtable;--从行取出具体数据赋给变量
CASETRIM（LOWER（v_mlogtable））--CASE起始
WHEN'String1'--当条件一
THEN--做条件一工作
BEGIN
v_remarks:
=REPLACE（v_remarks,'AA'）;
END;
WHEN'String2'--当条件二
THEN
BEGIN
END;
ELSE--其他条件
NULL;
ENDCASE;--CASE结束
IF（LOWER（SUBSTR（v_mlogtable,1,5））<>'mlog$'）
THEN
SELECTlog_table
INTOv_mlogtable
FROMuser_snapshot_logs
WHERELOWER（MASTER）=LOWER（v_mlogtable）;
ENDIF;
EXECUTEIMMEDIATE'deletefrom'
||v_mlogtable
||'wheresequence$$<='
||TO_CHAR（v_lognum）;
EXITWHEN1>2;--循环跳出条件
ENDLOOP;--循环结束
CLOSEc1;--关闭游标
EXCEPTION
WHENOTHERS
THEN
ROLLBACK;
global_procedure_check.check_end（'checkdataerror01',
v_systime,
1,
SQLCODE||''||SQLERRM
）;
RAISE;
RETURN;
END;
ENDexample;
2．基本知识
1）基本结构
--------------------------------------------------------
CREATEORREPLACEPROCEDUREexample（parameters）--过程声明区
IS
--------------------------------------------------------
v_1NUMBER;--过程中变量声明区--------------------------------------------------------
BEGIN
v_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_tmpnumber
FROMetl_ods_masterdata_tablist;
2〉通过构造SQL赋值：
v_tmpsql:
=
'SELECTlog_tableFROMuser_snapshot_logs'
||v_dblink
||'WHEREUPPER（MASTER）=UPPER（'''
||v_singletab
||'''）';
EXECUTEIMMEDIATEv_tmpsql
INTOv_tmpvarchar;
6）循环
1.无限或简单循环
LOOP
EXITWHEN（退出循环条件）;
ENDLOOP;
2.while循环
WHILEcondition
LOOP
executable_statements;
ENDLOOP;
3.for循环
基于数字的for循环：
FORfor_indexINlow_value..high_value
LOOP
executable_statements;
ENDLOOP;
基于游标的for循环：
FORrecord_indexINmy_cursor
LOOP
executable_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
当前系统时间
DATE
SQLCODE
异常代码
VARCHAR2
SQLERRM
异常描述
VARCHAR2
NO_DATA_FOUND
未找到数据异常
与when搭配
OTHERS
其他所有异常
与when搭配
RAISE
抛出当前异常
RAISE;
DENSE_RANK
非选取字段排序
MIN（B）KEEP（DENSE_RANKFIRSTORDERBYA）
MAX（B）KEEP（DENSE_RANKLASTORDERBYA）
PRAGMAAUTONOMOUS_TRANSACTION
BULKCOLLECTINTO
SQL%ROWCOUNT
使用自治事务，可以使该过程被调用时单独提交
Begin之前使用PRAGMAAUTONOMOUS_TRANSACTION;
将前面执行结果大批放入后面的集合中
BULKCOLLECTINTOcolumntab;
前一个DML语句执行影响行数
作为NUMBER型使用
v_number:
=SQL%ROWCOUNT
DBMS_OUTPUT.put_line（）
输出信息
函数
TO_CHAR
转换NCHAR、NVARCHAR2、CLOB、NCLOB
TO_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）
DECLARE
v_studentrecordemployee%ROWTYPE;
nemployee.create_date%TYPE;
BEGIN
SELECT*
INTOv_studentrecord
FROMemployee
WHEREemp_id=1;
n:
=v_studentrecord.create_date;
DBMS_OUTPUT.put_line（n）;
END;
5．内存表的使用
内存表主要作为数组用。
1）：
一个字段：
PROCEDUREt1
IS
TYPEt_cISTABLEOFtesta.a1%TYPE
INDEXBYBINARY_INTEGER;
aat_c;
BEGIN
aa（0）:
='aaa';
DBMS_OUTPUT.put_line（aa（0））;
END;
2）：
定义多个字段：
PROCEDUREt1
IS
TYPEt_rISRECORD（
t1VARCHAR（10）,
t2VARCHAR（10）
）;
TYPEt_tISTABLEOFt_r
INDEXBYBINARY_INTEGER;
aat_t;
BEGIN
aa（0）.t1:
='aaa';
aa（0）.t2:
='bbb';
DBMS_OUTPUT.put_line（aa（0）.t1）;
DBMS_OUTPUT.put_line（aa（0）.t2）;
END;
6．游标的使用
游标是用来处理使用SELECT语句从数据库中检索到的多行记录的工具。
借助于游标的功能，数据库应用程序可以对一组记录逐个进行处理，每次处理一行。
DECLARE
nNUMBER;
CURSORc
IS
SELECT*
FROMemployee;
BEGIN
FORv_cINc
LOOP
n:
=v_c.emp_id;
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）,
EMP_NAMEVARCHAR（20）,
DEPT_IDVARCHAR（5）,
DEPT_NAMEVARCHAR（20）
）
1DECLARE
2DL_EMP_DEPT_TEMP%ROWTYPE;
3CURSORCIS
4SELECTE.EMP_IDAA,E.EMP_NAMEBB,D.DEPT_IDCC,D.NAMEDD
5FROML_EMPLOYEEE,L_DEPTD
6WHEREE.DEP_ID=D.DEPT_ID;
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．异常处理
例外是一个非致命事件，它立即中断程序的正常执行并引起一个非条件转移，跳转到当
前程序块的例外处理部分。
一些例外，像NO_DATE_FOUND或TO_MANY_ROWS，属于预定义例外用于处理常见的oracle错误，可以被认为是正常的处理部分。
部分ERROR这样的例外表明一个程序错误或一些意料之外的事件。
如下所示：
1）：
正常处理的部分
1DECLARE
2NCHAR;
3BEGIN
4SELECTEMP_NAME
5INTON
6FROMEMPLOYEE;
7DBMS_OUTPUT.PUT_LINE（'N'）;
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_employee
2values（'4','dd','3',sysdate,'2000'）
3/
insertintol_employee
*
第1行出现错误:
ORA-02291:
违反完整约束条件（REPDB.FK_EMP_DEPT）-未找到父项关键字
处理方法：
自定义异常
1declare
2eexception;
3pragmaexception_init（e,-2291）;
4begin
5insertintol_employee
6values（'6','dd','3',sysdate,'2000'）;
7exceptionwhenethen
8DBMS_OUTPUT.PUT_LINE（'违反完整约束条件（REPDB.FK_EMP_DEPT）'）;
9*end;
SQL>/
PL/SQL过程已成功完成。
输出结果为：
违反完整约束条件（REPDB.FK_EMP_DEPT）
10．嵌套
程序块的内部可以有另一个程序块这种情况称为嵌套。
嵌套要注意的是变量，定义在最外部程序块中的变量可以在所有子块中使用，如果在子块中定义了与外部程序块变量相同的变量名，在执行子块时将使用子块中定义的变量。
子块中定义的变量不能被父块引用。
如果字块需要单独提交，应使用自
升级会员 