精编推荐Oracle存储过程开发规范与技巧Word下载.docx
- 文档编号:18326772
- 上传时间:2022-12-15
- 格式:DOCX
- 页数:41
- 大小:46.30KB
精编推荐Oracle存储过程开发规范与技巧Word下载.docx
《精编推荐Oracle存储过程开发规范与技巧Word下载.docx》由会员分享,可在线阅读,更多相关《精编推荐Oracle存储过程开发规范与技巧Word下载.docx(41页珍藏版)》请在冰豆网上搜索。
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'
),'
)
ANDa.remarksLIKE'
SUCCEEDED:
%'
ANDa.procedurename=b.procedurename
GROUPBYb.tablename);
--定义结束
c1_recc1%ROWTYPE;
--定义接受游标数据行的ROWTYPE
v_mlogtableVARCHAR(30);
v_postperiodCHAR
(2);
v_acctbalbeginseqNUMBER;
v_systimeDATE;
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;
String2'
--当条件二
THEN
ELSE--其他条件
NULL;
ENDCASE;
--CASE结束
IF(LOWER(SUBSTR(v_mlogtable,1,5))<
>
'
mlog$'
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
ROLLBACK;
global_procedure_check.check_end('
checkdataerror01'
v_systime,
1,
SQLCODE||'
||SQLERRM
);
RAISE;
RETURN;
ENDexample;
2.基本知识
1)基本结构
--------------------------------------------------------
CREATEORREPLACEPROCEDUREexample(parameters)--过程声明区
v_1NUMBER;
--过程中变量声明区--------------------------------------------------------
v_1:
--过程内容区
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
executable_statements;
3.for循环
基于数字的for循环:
FORfor_indexINlow_value..high_value
基于游标的for循环:
FORrecord_indexINmy_cursor
7)调用其他过程或方法
1.如果单独定义,直接使用
v_retval0:
f_dump_init(v_updatemode,
mlog$_glddocheader'
v_procname,
v_docheaderbeginseq,
v_docheaderendseq
2.如果定义在包下,使用包名+过程名
global_procedure_check.check_run(v_procname);
3.固定用法和函数
标识
作用
用法或类型
固定用法:
SYSDATE
当前系统时间
DATE
SQLCODE
异常代码
VARCHAR2
SQLERRM
异常描述
NO_DATA_FOUND
未找到数据异常
与when搭配
OTHERS
其他所有异常
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,'
转换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'
haracter?
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('
LTRIM
去掉指定字符串左侧的指定字符或字符集合,默认为空格
LTRIM('
Way'
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;
SELECT*
INTOv_studentrecord
FROMemployee
WHEREemp_id=1;
n:
=v_studentrecord.create_date;
DBMS_OUTPUT.put_line(n);
5.内存表的使用
内存表主要作为数组用。
一个字段:
PROCEDUREt1
IS
TYPEt_cISTABLEOFtesta.a1%TYPE
INDEXBYBINARY_INTEGER;
aat_c;
aa(0):
aaa'
DBMS_OUTPUT.put_line(aa(0));
定义多个字段:
TYPEt_rISRECORD(
t1VARCHAR(10),
t2VARCHAR(10)
TYPEt_tISTABLEOFt_r
aat_t;
aa(0).t1:
aa(0).t2:
bbb'
DBMS_OUTPUT.put_line(aa(0).t1);
DBMS_OUTPUT.put_line(aa(0).t2);
6.游标的使用
游标是用来处理使用SELECT语句从数据库中检索到的多行记录的工具。
借助于游标的功能,数据库应用程序可以对一组记录逐个进行处理,每次处理一行。
nNUMBER;
CURSORc
FROMemployee;
FORv_cINc
LOOP
=v_c.emp_id;
EXCEPTION
DBMS_OUTPUT.put_line('
error'
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过程已成功完成。
SELECTCOUNT(*)
2FROML_EMP_DEPT_TEMP
3/
COUNT(*)
----------
3
COMMIT
2/
提交完成。
0
9.异常处理
例外是一个非致命事件,它立即中断程序的正常执行并引起一个非条件转移,跳转到当
前程序块的例外处理部分。
一些例外,像NO_DATE_FOUND或TO_MANY_ROWS,属于预定义例外用于处理常见的oracle错误,可以被认为是正常的处理部分。
部分ERROR这样的例外表明一个程序错误或一些意料之外的事件。
如下所示:
正常处理的部分
2NCHAR;
3BEGIN
4SELECTEMP_NAME
5INTON
6FROMEMPLOYEE;
7DBMS_OUTPUT.PUT_LINE('
N'
8*END;
*
第1行出现错误:
ORA-01422:
实际返回的行数超出请求的行数
ORA-06512:
在line4
7DBMS_OUTPUT.PUT_LINE(N);
8EXCEPTIONWHENTOO_MANY_ROWSTHEN
9DBMS_OUTPUT.PUT_LINE('
TOOMANYROWSRETURN'
10*END;
输出结果为:
TOOMANYROWSRETURN
非正常处理的部分,自定义异常
insertintol_employee
2values('
4'
dd'
3'
sysdate,'
2000'
insertintol_employee
ORA-02291:
违反完整约束条件(REPDB.FK_EMP_DEPT)-未找到父项关键字
处理方法:
自定义异常
1declare
2eexception;
3pragmaexception_init(e,-2291);
4begin
5insertintol_employee
6values('
6'
);
7exceptionwhenethen
8DBMS_OUTPUT.PUT_LINE('
违反完整约束条件(REPDB.FK_EMP_DEPT)'
9*end;
违反完整约束条件(REPDB.FK_EMP_DEPT)
10.嵌套
程序块的内部可以有另一个程序块这种情况称为嵌套。
嵌套要注意的是变量,定义在最外部程序块中的变量可以在所有子块中使用,如果在子块中定义了与外部程序块变量相同的变量名,在执行子块时将使用子块中定义的变量。
子块中定义的变量不能被父块引用。
如果字块需要单独提交,应使用自
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 精编 推荐 Oracle 存储 过程 开发 规范 技巧