SQL,PL/SQL编程规范

       必须接受的限制：

       ·完整是不可能的；

       ·一致性是不可能的；

         通常保留字和关键字应该大写，但是自定义函数通常不会那么严格。

后缀名命名规范

       在文本文件中维护代码，而不要依赖于工具，这样可以进行版本控制，同时保护代码的不被破坏。

       如下：

       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 < SYSDATE;

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 < SYSDATE;

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 < 10000

THEN

    ...

ELSIF sal < 20000

THEN

    ...

ELSIF sal < 30000

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 < SYSDATE

THEN

    date_in_past := TRUE;

ELSE

    date_in_past := FALSE;

END IF;

使用如下格式：

date_in_past := hiredate < SYSDATE;

总结

       如下：

IF (shipdate < ADD_MONTHS (SYSDATE, +3) OR

    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 < ADD_MONTHS(SYSDATE, +3)

    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;