SQL,PL/SQL编程规范

real_time

  必须接受的限制：
       ·完整是不可能的；
       ·一致性是不可能的；
         通常保留字和关键字应该大写，但是自定义函数通常不会那么严格。
后缀名命名规范
       在文本文件中维护代码，而不要依赖于工具，这样可以进行版本控制，同时保护代码的不被破坏。
       如下：
       Package：pkg；
       包体：pkb；
       Procedure：prc；
       Function：fnc；
       对象类型：typ；
       对象类型体：tyb；
       Trigger：trg；
       其他PL/SQL：pls；
       其他SQL：sql；
标识符命名规范
       由5部分组成：。primary identifier最为重要。
Scope     
       通常g代表全局；
       l代表本地；
       p代表进出参数；
Type
       两种典型的标量值为常量(k)或者变量(v)。但是如果再分为VARCHAR2或DATE就过于复杂，不过通常使用g代表gv。还有一些集合类型包括：
       c代表游标；
       cp代表游标参数；
       r代表记录类型；
       除了自定义类型外，还包括用户自定义类型和子类型。
       --使用子类型的唯一意图是构建一个内部控制的自我描述的类型；
Primary Identifier
       应该能够清晰地描述变量的意图，通常为单词或者短语。通常避免缩写，除非真的能够自我描述，否则考虑其他的名称。
Suffix
       通常为IN, OUT, or IN OUT，in通常省略。
标识符命名例子
游标声明
       游标名通常以c_开头；
循环索引
       通常不要使用FOR i IN 1..12 LOOP和FOR r IN c_discrepancies LOOP形式的，而是使用FOR r_emp IN c_emp LOOP或FOR i_month IN 1..12 LOOP。
集合（PL/SQL）类型
       通常使用_TT作为后缀比较好，如TYPE account_tt IS TABLE OF accounts%ROWTYPE;
PL/SQL记录类型
       通常使用rectype作为后缀，如G_ACCOUNT_RECTYPE。
       TYPE g_address_rectype IS RECORD (...);  
PL/SQL纪录实例
       定义纪录实例是不需要rectype，命名规范同正常的变量；
PL/SQL对象实例
       通常使用o_作为前缀
模式级别的对象类型
       通常使用_OT作为后缀；
模式级别的集合类型
       通常也以_TT作为后缀；
代码格式
缩进
       使用tab键作为PL/SQL块缩进的单元，不要使用空格；
       在SQL中使用空格键，不要使用tab键；
对齐
       ·在过程/函数中，在每一行声明一个新的参数；
FUNCTION invoice_address
    ( p_account_id ACCOUNTS.ACCOUNT_ID%TYPE
    , p_date DATE )
    RETURN ADDRESS;
       ·不一定需要这种格式：
v_account_id           ACCOUNTS.ACCOUNT_ID%TYPE;
v_customer_id          CUSTOMERS.CUSTOMER_ID%TYPE;
使用大小写增加可读性
       通常变量应该是小写的，其方法和过程应该为大写，如v_timer.SHOW('Punctuation check completed.');而不要混合大小写，这只会降低可读性。
例子：
       应该使用以下格式：
v_firstname := 'Lynne';
       而非：
         v_firstname:='Lynne';
注释风格
       应该使注释容易输入和维护，代码应该越少越好，如下：
          /*********************************************************/
         / +-----------------------------------------------------+ /
        / /                                                     / /
       / /  Main section:                                      / /
      / /  Added for version 1.23.4 WR 01/02/03               / /
     / /  Delete all the customers and fire the employees.   / /
    / /                                                     / /
   / +-----------------------------------------------------+ /
  /*********************************************************/
应该使用一下代码代替：
-- Delete all the customers and fire the employees:
同时注视也要注意缩进。

SQL布局指南
       不要使用以下格式：
SELECT last_name, first_name
  FROM employees
WHERE department_id = 15
   AND hire_date
UPDATE employees
   SET hire_date = SYSDATE
WHERE hire_date IS NULL
   AND termination_date IS NULL;

而是使用以下格式：
SELECT last_name, first_name
FROM   employees
WHERE  department_id = 15
AND    hire_date
INSERT INTO employees
( emp_id
, emp_firstname
, emp_lastname )
VALUES
( emp_seq.NEXTVAL
, r_emp.firstname
, r_emp.lastname );
UPDATE  employees
SET    salary = salary * v_raise_factor
WHERE   department_id = v_department_id
AND     termination_date IS NULL;
使用左对齐可以很清楚地明白是否为相同的语句，以及语句之间同等级的条件；
在代码中留出空间
       ·对SELECT中的每个列使用单独的行；
       ·将FROM中的每个表放在单独的行上；
       ·WHERE中的每个条件一行；
SELECT last_name
     , c.name
     , MAX(sh.salary) best_salary_ever
FROM   employees e
     , companies c
     , salary_history sh
WHERE  e.company_id = c.company_id
AND    e.employee_id =sh.employee_id
AND    e.hire_date > ADD_MONTHS(SYSDATE, -60);
不要使用过多的空格
       ·代码之间空行不要超过一行；
       ·子程序之间空两行；
表和列的别名使用有意义的缩写
       不要使用以下形式的缩写：
SELECT cols
FROM   employees a
     , companies b
     , profiles c
     , sales d
WHERE  b.com_id = a.emp_com_id
AND    c.pro_com_id = b.com_id
AND    d.sal_com_id (+)= c.pro_com_id
而是使用以下形式：
SELECT cols
FROM   employees emp
     , companies com
     , profiles pro
     , sales sal
WHERE  com.com_id = emp.emp_com_id
AND    pro.pro_com_id = com.com_id
AND    sal.sal_com_id (+)= pro.pro_com_id
虽然Oracle不会以出现的顺序进行连接，但是为了更好的理解，通常从左到右进行编
写连接。
SELECT da.
     , da.object_type
     , da.owner
     , DECODE(aa.object_name, NULL, 'No', 'Yes' ) granted
     , s.synonym_name
FROM   all_objects aa
     , dba_objects da
     , all_synonyms s
WHERE  (   da.object_name LIKE UPPER('&object')
        OR (    da.object_name LIKE UPPER(SUBSTR('&obj',INSTR('&obj','.')+1))
            AND da.owner = UPPER(SUBSTR('&object',1,INSTR('&object','.')-1))
           )
       )
AND    aa.object_name (+)= da.object_name
AND    aa.object_type (+)= da.object_type
AND    aa.owner (+)= da.owner
AND    s.table_name (+)= da.object_name
AND    s.table_owner (+)= da.owner
ORDER BY  da.object_name, da.object_type, da.owner;
另一种风格如下：
SELECT DISTINCT
       s.sid
     , s.username
     , a.owner ||'.' || a.object object_name
     , p.username  locked_by
     , l.type
     , DECODE(l.request,
              0, 'None',
              1, 'Null (NULL)',
              2, 'Row-S (SS)',
              3, 'Row-X (SX)',
              4, 'Share (S)',
              5, 'S/Row-X (SSX)',
              6, 'Exclusive (X)',
                'Unknown(' || l.request || ')' ) AS mode_requested
     , DECODE  -- Alternative bullet-style (use only one style at a time!)
       ( l.lmode
       , 0, 'None'
       , 1, 'Null (NULL)'
       , 2, 'Row-S (SS)'
       , 3, 'Row-X (SX)'
       , 4, 'Share (S)'
       , 5, 'S/Row-X (SSX)'
       , 6, 'Exclusive (X)'
         , 'Unknown(' || l.lmode || ')' ) AS mode_held
     , CASE
       WHEN l.ctime > 86400 * 2 -- More than 2 days
            THEN FLOOR(l.ctime/86400) || ' days '
                 || TO_CHAR(TO_DATE(systdate),'HH24:MI:SS')
       WHEN l.ctime > 86400  -- More than 1 day
            THEN FLOOR(l.ctime/86400) || ' day '
                 || TO_CHAR(TO_DATE(sysdate),'HH24:MI:SS')
       ELSE
            TO_CHAR(TO_DATE(ROUND(l.ctime),'SSSSS'),'HH24:MI:SS')
       END AS TIME_HELD
FROM   v$lock l
     , v$process p
     , v$access a
     , v$session s
WHERE  a.sid (+)= l.sid
AND    s.sid (+)= l.sid
AND    p.addr (+)= s.paddr
AND    a.owner (+) NOT IN ('PUBLIC','SYS')
ORDER BY 1,2,3,4
PL/SQL编程标准
异常
命名异常
       使用RAISE exception_name时要注意，特别是用户定义异常，如RAISE invalid_account，但是没有方法提供解释的伴随信息。作为一条规则，通常应该避免抛出用户自定义异常，仅在WHEN中策是它们，比较如下：
       应该避免如下：
         RAISE errorpkg.fatal_error;
       而是使用如下：
RAISE_APPLICATION_ERROR
( errorpkg.k_fatal_error
, 'Credit check failed for account ' || r_acc.acc_id
, TRUE );
避免使用如下：
ERROR_PKG.LOG_ERROR('Invalid account ' || r_acc.acc_id);
RAISE errorpkg.fatal_error;
而是使用如下：
v_error_text := 'Invalid account ' || r_acc.acc_id;
ERRORPKG.LOG_ERROR(v_error_text);
RAISE_APPLICATION_ERROR(errorpkg.k_invalid_account, v_error_txt );
错误码
       需要注意的是，使用RAISE_APPLICATION_ERROR时，实际的错误码并不需要特别关心。通常应用程序中只需要几个特定的错误即可，可以在包级别声明几个常量，如：k_error_code CONSTANT PLS_INTEGER := -20042，然后在调用RAISE_APPLICATION_ERROR时使用k_error_code。
越少越好
       通常使用过程简单的产生错误报告，如“Could not generate invoice for order 4231”。

尽可能使用%TYPE
当存储从表中提取的值时使用%TYPE
       如下：
PROCEDURE format_customer
    ( p_customer_id  customers.cst_id%TYPE )
IS
    v_first_name     customers.cst_first_name%TYPE;
    v_last_name      customers.cst_last_name%TYPE;
    v_address        customers.cst_address_l1%TYPE;
    v_city           customers.cst_city%TYPE;
    v_national_ins#  customers.cst_national_ins_number%TYPE;
BEGIN
    ...
END;
使用%TYPE标准化非数据库声明
       如下：
v_revenue NUMBER(20,2) DEFAULT 0;
v_total_revenue v_revenue%TYPE;      
此时，v_revenue就可以充当revenue数据的标准变量，随后当需要更改revenue时就可以仅更改v_revenue，需要注意，DEFAULT 0不会继承。

删除不使用的变量
       尽量在编写完成后立刻检查，否则会花费大量时间而且乏味。

避免变量循环使用
       一个变量只用于一个目的。

在自我描述的文档中使用子类型
       SUBTYPE room_number IS rooms.room_number%TYPE;
...
-- A declaration using the subtype:
v_open_room ROOM_NUMBER;
是一种特定类型的NUMBER。
常量
       ·从代码中删除所有文本常量和数字，使用常量代替他们。可以确保系统的可维护性。
       ·确保该常量的值仅仅被设置一次。
       ·如果发现程序中某些变量值没有变化，需要首先检查逻辑是否正确；如果正确，则更改为常量；
       ·如果将一个变量改为常量，需要同时更改其名称。
       使用常量的原因是可以提高可读性和可维护性。

可以使用SQL解决的，不要使用PL/SQL
       应用尽量使用SQL代替PL/SQL循环，如下：
FOR i_year IN 1..20
LOOP
    INSERT INTO table1 -- Twenty INSERT statements passed to the SQL engine
    SELECT *
    FROM   table2
    WHERE  starting_year = i_year;
END LOOP;
      
INSERT INTO table1
SELECT *
FROM   v1table2
WHERE  starting_year BETWEEN 1 AND 20;

循环
       ·对于FOR和WHILE循环不要EXIT和RETURN；
       ·仅在执行指定次数时使用FOR循环；
       ·仅在条件边界为FALSE时退出循环；
       ·Cursor FOR loop通常效率均比OPEN-FETCH-EXIT-CLOSE好，并且后者更复杂；作为一个规则，仅在以下情况下使用OPEN-FETCH-EXIT-CLOSE：
         ·要与游标变量一起工作，编译器不能自动声明ref cursors的cursor%ROWTYPE；
         ·希望在循环后保留值。
       ·使用匿名游标循环，FOR r IN (SELECT cols FROM sometable) LOOP；但是不能重用，也不能使用cursor's %ROWTYPE。

条件表达式
布局
       ·不要增加冗余的括号；
IF (x = 1)
IF ((x = 1) AND (y = 2))
使用以下形式：
IF x = 1 AND y = 2
       ·THEN关键字可以在IF的一行，也可以在下一行；
       ·恰当的布局IF中的条件：
IF TO_CHAR(SYSDATE,'D') > 1 AND MYPACKAGE.MYPROCEDURE(1,2,3,4) NOT BETWEEN 1 AND 99 THEN
IF TO_CHAR(SYSDATE,'D') > 1   
AND MYPACKAGE.MYPROCEDURE(1,2,3,4) NOT BETWEEN 1 AND 99
THEN
使用以下格式：
IF TO_CHAR(SYSDATE,'D') > 1
AND MYPACKAGE.MYPROCEDURE(1,2,3,4) NOT BETWEEN 1 AND 99
THEN
       ·如果混合or和and，除非很简单，否则一般使用空格缩进：
         IF a = 1 AND (b = 2 OR c = 3 OR d = 4) THEN
         使用以下格式：
IF a = 1
AND (   b = 2
     OR c = 3
     OR d = 4 )
THEN
确保ELSIF中的条件是排斥的
IF sal BETWEEN 0 AND 10000
THEN
    ...
ELSIF sal BETWEEN 10000 AND 20000
THEN
    ...
ELSIF sal BETWEEN 20000 AND 30000
THEN
    ...
ELSE
    ...
END IF;
需要使用以下格式：
IF sal
THEN
    ...
ELSIF sal
THEN
    ...
ELSIF sal
THEN
    ...
ELSE
    ...
END IF;
使用逻辑元素提高系统可读性
IF total_sal BETWEEN 10000 AND 50000
AND emp_status(emp_rec.empno) = 'N'
AND MONTHS_BETWEEN(emp_rec.hiredate, SYSDATE) > 10
THEN
    GIVE_RAISE(emp_rec.empno);
END IF;
应该使用以下格式：
eligible_for_raise :=
    total_sal BETWEEN 10000 AND 50000
    AND emp_status(emp_rec.empno) = 'N'
    AND MONTHS_BETWEEN(emp_rec.hiredate, SYSDATE) > 10;

IF eligible_for_raise
THEN
    GIVE_RAISE(emp_rec.empno);
END IF;

避免在将值赋给逻辑变量时使用IF
IF hiredate
THEN
    date_in_past := TRUE;
ELSE
    date_in_past := FALSE;
END IF;
使用如下格式：
date_in_past := hiredate
总结
       如下：
IF (shipdate
    order_date >= ADD_MONTHS (SYSDATE, -2)) AND
    cust_priority_type ='HIGH' AND
    order_status = 'O'
THEN
    ship_order('EXPRESS');

ELSIF (order_date >= ADD_MONTHS (SYSDATE, -2) OR
    ADD_MONTHS (SYSDATE, 3) > shipdate) AND
    order_status = 'O'
THEN
    ship_order('STANDARD');
END IF;
应该使用如下格式：
IF (   v_shipdate
    OR v_order_date >= ADD_MONTHS(SYSDATE, -2) )
AND v_cust_priority = 'HIGH'
AND v_order_status = 'O'
THEN
    SHIP_ORDER(k_ship_express);

ELSIF (   v_order_date >= ADD_MONTHS(SYSDATE, -2)
       OR ADD_MONTHS(SYSDATE, 3) > v_shipdate)
AND v_order_status = 'O'
THEN
    SHIP_ORDER(k_ship_standard);
END IF;


本文来自ChinaUnix博客，如果查看原文请点：http://blog.chinaunix.net/u/4864/showart_480340.html