【精编推荐】Oracle存储过程开发规范与技巧

【精编推荐】Oracle存储过程开发规范与技巧

存储过程开发规范与技巧

开发规范

1.书写规范

1）：程序头书写规范

程序头开始部分应说明程序整体的功能，存储过程名称，编写人，编写日期，修改人，修改日期，版本号以及过程涉及的表和视图。示例如下：

-----------------------------------------------------------------------------

/*

名称及实现功能:

版本: ( 版本号标示：新建 V1.0.0 小的修改变为V1.0.1 大的修改V1.1.0 重构V2.0.0)

Create by***Create Date2006-06-29

Update by*** update Date2006-06-30

修改原因:

Update by*** update Date2006-06-31

修改原因:

涉及的表或视图：

dump_init 辅助表(DM)：记录存储过程中使用的物化视图日志序号

mlog$_acrcusmrsecindex 源表(ODS)：客户第一索引物化视图日志,使用同义词

ft_gld_customerdata 目标表(DM)：客户事实表

*/

CREATE OR REPLACE PROCEDURE *******

------------------------------------------------------------------------------ 2）：代码书写规范

1.语句中出现的所有表名、字段名全部小写，系统保留字、内置函数名、Sql保留

字大写。

2.连接符or、in、and、以及＝、<=、>=等前后加上一个空格。

3.where子句书写时，每个条件占一行，语句令起一行时，以保留字或者连接符开

始，连接符右对齐。

4.查询的WHERE过滤，原则应使过滤记录数最多的条件放在最前面。

5.多表连接时，使用表的别名来引用列。

6.查找数据库表或视图时，只能取出确实需要的那些字段，不要使用*来代替所有

列名。

7.功能相似的过程和函数，尽量写到同一个包中，加强管理。

示例如下：

BEGIN

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

SELECT https://www.360docs.net/doc/9913543124.html,, https://www.360docs.net/doc/9913543124.html,

FROM l_dept dept,l_employee emp

WHERE emp.dept_id = dept.dept_id;

END;

3）注释书写规范

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

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

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

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

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

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

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

2.命名规范

存储过程技术

1．存储过程样例

CREATE OR REPLACE PROCEDURE example(

v_input IN NUMBER, --输入参数

v_output OUT NUMBER --输出参数

)

IS

PRAGMA AUTONOMOUS_TRANSACTION;

CURSOR c1 --定义一个游标，在begin之前

IS

SELECT b.tablename mlogtable,

MAX (remarks)KEEP(DENSE_RANK LAST ORDER BY starttime)

remarks FROM proc_log a,table_proc b

WHERE TO_CHAR (starttime,'yyyy-mm-dd')<= --转换时间并做比较

TO_CHAR ( SYSDATE - TO_DSINTERVAL (

TO_CHAR (intervaldays) || ' 00:00:00'),'yyyy-mm-dd') AND a.remarks LIKE'SUCCEEDED:%'

AND a.procedurename = b.procedurename

GROUP BY b.tablename); --定义结束

c1_rec c1%ROWTYPE; --定义接受游标数据行的ROWTYPE

v_mlogtable VARCHAR(30);

v_postperiod CHAR(2);

v_acctbalbeginseq NUMBER;

v_systime DATE;

BEGIN

v_input :=0; --变量赋值

v_systime := SYSDATE;

OPEN c1;--打开游标

LOOP --循环

FETCH c1 INTO c1_rec; --从当前游标行赋值c1_rec

EXIT WHEN c1%NOTFOUND; --游标没有数据退出

v_mlogtable := c1_rec.mlogtable; --从行取出具体数据赋给变量

CASE TRIM (LOWER (v_mlogtable)) --CASE起始

WHEN'String1' --当条件一

THEN --做条件一工作

BEGIN

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

END;

WHEN'String2' --当条件二

THEN

BEGIN

END;

ELSE --其他条件

NULL;

END CASE; --CASE结束

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

THEN

SELECT log_table

INTO v_mlogtable

FROM user_snapshot_logs

WHERE LOWER (MASTER)= LOWER (v_mlogtable);

END IF;

EXECUTE IMMEDIATE'delete from '

|| v_mlogtable

|| ' where sequence$$ <= '

|| TO_CHAR (v_lognum);

EXIT WHEN 1>2; --循环跳出条件

END LOOP; --循环结束

CLOSE c1; --关闭游标

EXCEPTION

WHEN OTHERS

THEN

ROLLBACK;

global_procedure_check.check_end('checkdataerror01',

v_systime,

1,

SQLCODE || ' ' || SQLERRM

);

RAISE;

RETURN;

END;

END example;

2．基本知识

1) 基本结构

-------------------------------------------------------- CREATE OR REPLACE PROCEDURE example（parameters）--过程声明区IS

--------------------------------------------------------

v_1 NUMBER; --过程中变量声明区

-------------------------------------------------------- BEGIN

v_1 :=0; --过程内容区

END example;

--------------------------------------------------------

2) 基本类型

CHAR 固定长度字符类型

VARCHAR2 可变长字符类型

VARCHAR 可变长字符类型(不建议使用)

NUMBER 一切数值类型

DATE 一切日期类型

3) 参数

三种：IN 输入参数，OUT输出参数，IN OUT 输入输出参数。

4) 变量的声明

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

例：v_postperiod CHAR(2);

可赋初值

v_postperiod CHAR(2):=’01’;

（这里叫变量声明区可能并不恰当，因为游标、自定义类型等，一切需要事先声明的都应在这里声明。）5) 变量的赋值

使用‘：=’为变量赋值

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

例：v_number := 1;

2.使用函数赋值

例：v_date := sysdate;

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

1〉通过sql直接赋值

SELECT COUNT (*)

INTO v_tmpnumber

FROM etl_ods_masterdata_tablist;

2〉通过构造SQL赋值：

v_tmpsql :=

'SELECT log_table FROM user_snapshot_logs'

|| v_dblink

|| ' WHERE UPPER (MASTER) = UPPER ('''

|| v_singletab

|| ''')';

EXECUTE IMMEDIATE v_tmpsql

INTO v_tmpvarchar;

6) 循环

1.无限或简单循环

LOOP

EXIT WHEN(退出循环条件);

END LOOP;

2.while循环

WHILE condition

LOOP

executable_statements;

END LOOP;

3.for循环

基于数字的for循环：

FOR for_index IN low_value .. high_value

LOOP

executable_statements;

END LOOP;

基于游标的for循环：

FOR record_index IN my_cursor

LOOP

executable_statements;

END LOOP;

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．固定用法和函数

4．ROWTYPE的使用

可以使用%type 和% rowtype属性实现使用其他变量、数据库列或表的数据类型的引用。%type属性提供了所需要的变量的类型及长度。% rowtype属性允许人们定义一个记录变量，它的成员变量拥有表中每一列正确的类型及长度,使用点符号引用记录中的每个成员变

量。这种动态赋值方法是非常有用的，比如变量引用的列的数据类型和大小改变了，如果使用了%TYPE,那么用户就不必修改代码，否则就必须修改代码。

CREATE TABLE EMPLOYEE(

EMP_ID NUMBER NOT NULL,

EMP_NAME CHAR(20),

CREATE_DATE DATE）

DECLARE

v_studentrecord employee%ROWTYPE;

n employee.create_date%TYPE;

BEGIN

SELECT*

INTO v_studentrecord

FROM employee

WHERE emp_id =1;

n := v_studentrecord.create_date;

DBMS_OUTPUT.put_line (n);

END;

5．内存表的使用

内存表主要作为数组用。

1）：一个字段：

PROCEDURE t1

IS

TYPE t_c IS TABLE OF testa.a1%TYPE

INDEX BY BINARY_INTEGER;

aa t_c;

BEGIN

aa (0):='aaa';

DBMS_OUTPUT.put_line (aa (0));

END;

2）：定义多个字段：

PROCEDURE t1

IS

TYPE t_r IS RECORD(

t1 VARCHAR(10),

t2 VARCHAR(10)

);

TYPE t_t IS TABLE OF t_r

INDEX BY BINARY_INTEGER;

aa t_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

n NUMBER;

CURSOR c

IS

SELECT*

FROM employee;

BEGIN

FOR v_c IN c

LOOP

n := v_c.emp_id;

DBMS_OUTPUT.put_line (n);

END LOOP;

EXCEPTION

WHEN OTHERS

THEN

DBMS_OUTPUT.put_line ('error');

END;

7．跟踪调试

根踪调试主要是检查程序运行的情况，可以在需要检查程序是否执行正确作为输出的依据：DBMS_OUTPUT.PUT_LINE(G_USERID(-2));

执行时设置：set serveroutput on

8．临时表

临时表用于保存事务或者会话的中间结果，临时表中保存的数据只有对当时的会话是可见的，任何会话都不能看见其他会话的数据。即使COMMIT之后也是不可见的。对于临时表并行不是问题，即使锁定也不能阻止其他程序的访问。每个数据库创建临时表一次，（ORACLE 的DDL语句是一种消耗较大的动作）并不用每个程序创建一次，并且临时表总保持为空。

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

CREATE GLOBAL TEMPORARY TABLE REPDB.L_EMP_DEPT_TEMP

(

EMP_ID VARCHAR(5),

EMP_NAME VARCHAR(20),

DEPT_ID VARCHAR(5),

DEPT_NAME VARCHAR(20)

)

1 DECLARE

2 D L_EMP_DEPT_TEMP%ROWTYPE;

3 CURSOR C IS

4 SELECT E.EMP_ID AA ,E.EMP_NAME BB ,D.DEPT_ID CC ,https://www.360docs.net/doc/9913543124.html, DD

5 FROM L_EMPLOYEE E,L_DEPT D

6 WHERE E.DEP_ID=D.DEPT_ID;

7 BEGIN

8 FOR V_C IN C LOOP

9 INSERT INTO L_EMP_DEPT_TEMP

10 V ALUES (V_C.AA,V_C.BB,V_https://www.360docs.net/doc/9913543124.html,,V_C.DD);

11 END LOOP;

12* END ;

SQL> /

PL/SQL 过程已成功完成。

SQL> SELECT COUNT(*)

2 FROM L_EMP_DEPT_TEMP

3 /

COUNT(*)

----------

3

SQL> COMMIT

2 /

提交完成。

SQL> SELECT COUNT(*)

2 FROM L_EMP_DEPT_TEMP

3 /

COUNT(*)

----------

9．异常处理

例外是一个非致命事件，它立即中断程序的正常执行并引起一个非条件转移，跳转到当前程序块的例外处理部分。一些例外，像NO_DATE_FOUND或TO_MANY_ROWS，属于预定义例外用于处理常见的oracle错误，可以被认为是正常的处理部分。部分ERROR这样的例外表明一个程序错误或一些意料之外的事件。如下所示：

1）：正常处理的部分

1 DECLARE

2 N CHAR;

3 BEGIN

4 SELECT EMP_NAME

5 INTO N

6 FROM EMPLOYEE;

7 DBMS_OUTPUT.PUT_LINE('N');

8* END;

SQL> /

DECLARE

*

第 1 行出现错误:

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

ORA-06512: 在line 4

1 DECLARE

2 N CHAR;

3 BEGIN

4 SELECT EMP_NAME

5 INTO N

6 FROM EMPLOYEE;

7 DBMS_OUTPUT.PUT_LINE(N);

8 EXCEPTION WHEN TOO_MANY_ROWS THEN

9 DBMS_OUTPUT.PUT_LINE('TOO MANY ROWS RETURN');

10* END;

PL/SQL 过程已成功完成。输出结果为：TOO MANY ROWS RETURN

2）：非正常处理的部分，自定义异常

SQL> insert into l_employee

2 values ('4','dd','3',sysdate,'2000')

3 /

insert into l_employee

*

第 1 行出现错误:

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

处理方法：自定义异常

1 declare

2 e exception ;

3 pragma exception_init(e,-2291);

4 begin

5 insert into l_employee

6 values ('6','dd','3',sysdate,'2000') ;

7 exception when e then

8 DBMS_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．授权

grant select on cs_new.ACPStorkFlAssAnaTab to repdbnew

revoke select on cs_new.BILInvoiceUseEntityData from repdbnew

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

14．建立同义词

CREATE SYNONYM ACPStorkFlAssAnaTab FOR jcerp.ACPStorkFlAssAnaTab

drop SYNONYM BILInvoiceUseEntityData

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

15．为表字段加注释

COMMENT ON COLUMN HAN_2.BBBB IS'B字段';

16．触发器

create or replace trigger qytest1_trigger3 BEFORE INSERT ON mlog$_qytest1 FOR EACH ROW

begin

:new.snaptime$$ := SYSDATE;

end;

17．自定义类型的赋值

自定义类型：

CREATE OR REPLACE TYPE repdbnew.INPARAM AS VARRAY(50)OF

VARCHAR2(25);

自定义类型的赋值：

declare

indataname inparam;

begin

indataname := inparam(2);

indataname:= inparam('isCalCount','NO_TX');

end;

18．OBJECT TYPES

簡單來說，Oracle Object Types就是 Oracle 以TYPE的方式來實現物件(Objects)的方法，宣告/定義的方法，類似於Package。Object Type的宣告/定義中包含了它的Attributes/Properties 與Methods，也就是Member Functions/Procedures。

本篇來介紹利用 Oracle Object Types來做中介暫存的實作。

建立 Oracle Object:

定義這個Object的內容，可以把它想像為所希望的Row Columns的定義。

view plaincopy to clipboardprint?

CREATE TYPE type_obj AS OBJECT(

col1 INTEGER,

col2 VARCHAR2(60)

);

/

CREATE TYPE type_obj AS OBJECT( col1 INTEGER, col2 VARCHAR2(60));/

建立Object Collection:

建立一個Table Type，這個Table裝的資料列內容(欄位)就是之前所建立的Object。

view plaincopy to clipboardprint?

CREATE OR REPLACE

TYPE type_tab IS TABLE OF type_obj;

/

CREATE OR REPLACE TYPE type_tab IS TABLE OF type_obj;/