常量和异常标准化
常量和异常通常通过无包体的包来定义。标准化有利于开发一致性的程序，提高程序重用率，简化代码维护，在所有应用程序贯彻公司的统一标准。
需要标准化的主要有两部分：
- 异常的名称
- 常量的定义
标准化异常定义
创建标准化的异常包，定义所有应用程序需要的自定义异常名称：
CREATE OR REPLACE PACKAGE error_pkg IS
fk_err EXCEPTION;
seq_nbr_err EXCEPTION;
PRAGMA EXECEPTION_INIT(fk_err, -2292);
PRAGMA EXECEPTION_INIT(seq_nbr_err, -2277);
END error_pkg;
/
标准化异常捕获
写一个通用的异常捕获子程序，该程序基于SQLCODE和SQLERRM来显示错误信息，并利用参数来跟踪运行时错误，包括发生错误的过程、发生错误的行。如果设置RAISE_APPLICATION_ERROR的第三个参数为TRUE，将错误信息追加于前一个错误堆栈而不是替换它。
标准化常量
对于值不变的变量，可以将其转化为常量以减少维护和诊断，还可以创建一个包将所有常量定义其中。
CREATE OR REPLACE PACKAGE constant_pkg IS
c_order_received CONSTANT VARCHAR(2) := 'OR';
c_order_shipped CONSTANT VARCHAR(2) := 'OS';
c_min_sal CONSTANT NUMBER(3) := 900;
END constant_pkg;
定义和使用本地子程序
本地子程序
在declare部分定义的子程序
CREATE PROCEDURE employee_sal(id NUMBER) IS
emp employees%ROWTYPE;
FUNCTION tax(salary VARCHAR2) RETURN NUMBER IS
BEGIN
RETURN salary * 0.825
END tax;
BEGIN
SELECT * INTO emp
FROM EMPLOYEES WHERE employee_id = id;
DBMS_OUTPUT.PUT_LINE('Tax: ' || tax(emp.salary));
END;
本地子程序必须定义于declarative section 的最后。
定义者和调用者权限
- 定义者的权限
Oracle8i之前只有这一种，以定义者的权限执行子程序，不需要子程序引用的底层对象的权限。 - 调用者的权限
Oracle8i之后引入，程序按照调用者权限执行，需要具有子程序引用的底层对象的权限
申明调用者权限
CREATE OR REPLACE PROCEDURE add_dept(
id NUMBER, name VARCHAR2) AUTHID CURRENT_USER IS
BEGIN
INSERT INTO departments
VALUES(id, name, NULL, NULL);
END;
当在stand-alone函数、存储过程还有包中使用的时候，SELECT、DML、Native Dynamic SQL和DBMS_SQL用到的对象名一律解析为调用者对应schema名称，对其他package、函数和存储过程的引用则被解析为定义者对应schema。
匿名事务
由主事务发起的独立事务，通过PRAGMA AUTONOMOUS_TRANSCATION指定。
auto.JPG
匿名事务特征
独立于主体事务，调用他的事务会挂起直至调用完成。不同于事务的嵌套，不会随着主体事务的回滚而回滚。提交以后其他会话可见其变化。且只能是独立的子程序而不能是匿名块或嵌套块。
使用匿名事务
PROCEDURE bank_trans(cardnbr NUMNER, loc NUMBER) IS
BEGIN
log_usage(cardnbr, loc);
INSERT INTO txn VALUES(9001, 1000, ...);
END bank_trans;
PROCEDURE log_usage(card_id NUMBER, loc NUMBER)
IS
PRAGMA AUTONOMOUS _TRANSACTION;
BEGIN
INSERT INTO usage
VALUES(card_id, loc)
COMMIT;
END log_usage;
RETURNIG语句
可以使用RETURNING语句返回INSERT、UPDATE和DELETE语句操作结果，从而提高性能，不用后继查询新的值：
CREATE PROCEDURE update_salary(emp_id NUMBER) IS
name employees.last_name%TYPE;
new_sal employees.salary%TYPE;
BEGIN
UPDATE employees
SET salary = salary * 1.1
WHERE employee_id = emp_id
RETURNING last_name, salary INTO name, new_sal;
END update_salary;
/
使用NOCOPY hint传递参数
使用了NOCOPY hint，对于OUT和IN OUT类型的参数，传入变量指针而非值本身，提高了参数传递性能。
DECLARE
TYPE emptabtype IS TABLE OF employees%ROWTYPE;
emp_tab emptabtype;
PROCEDURE populate(tab IN OUT NOCOPY emptabtype) IS BEGIN ... END
BEGIN
populate(emp_tab)
END;
NPCOPY Hint 的效应
如果子程序因为未捕获的异常而退出，那么使用NOCOPY传递的变量不再可靠，所有未完成的修改也无法“roll back”。
远程过程调用（RPC）只能传递值。
忽略NOCOPY HINT的情况
当实际参数为以下情况时，忽略NOCOPY HINT：
- index-by table的元素
- 有约束（例如，有着NOT NULL 约束的标量变量，有着range、size、scale或精确度约束的数值型标量）
- 和形式参数都是记录类型，二者其一或全部通过%ROWTYPE 或者%TYPE定义，但对应列上的约束不同
- 需要隐式数据类型转换
通过外部或者远程过程来调用子程序的时候，ENABLENOCOPY HINT也会被忽略。
在函数定义中使用PARALLEL
PARALLEL_ENABLE hint可用于函数定义
CREATE OR REPLACE FUNCTION f2(p1 NUMBER)
RETURN NUMBER PARALLEL_ENABLE IS
BEGIN
RETURN p1 * 2;
END f2;
表明函数可用于并发查询或者并发DML。
网友评论