Best Code Practice

Best Code Practice
I. The Development Process
1. 开发中最重要的几个方面:
    1.1 选择合适的开发工具: TOAD格式化代码, Plsql Developer编码, UtraEdit, Eclipse, CVS, Beyond Compare;
    1.2 如何在PL/SQL里面写SQL.
    1.3 建立异常处理架构: 应该有制定的参考手册.
    1.4 Code Reveiw 和 Testing: 代码必须经1人以上复审(Review), 必须建立严格的测试制度(testing regimen).
2. 遇到问题,思考30分钟后不能解决,就应该向同事请求帮助.
3. 互相查看代码:
    3.1 The buddy system: 每个程序员一个buddy, 随时随地准备查看代码并提供反馈;
    3.2 Formal code walkthroughs: 程序员在小组成员前复审代码;
    3.3 结对编程(极限编程的一部分)Pair programming: 一人编程,一人同时在旁边查看;
4. Validate standards against source code in the database.
    编写一些代码,放到数据库里,便于用这些代码来查看项目工作的情况(例如程序是否完成,程序里是否有违背规范的地方,等等);
5. Generate code whenever possible and appropriate.
    5.1 将常用的需求写成函数或过程;
    5.2 使用更好的工具;
    5.3 使用别人的代码.
6. Set up and use formal unit testing procedures.
7. Get independent testers for functional sign-off.
    · Work with the customer to define the set of tests that must be run
      successfully before an application is considered to be ready for production.
    · Establish a distinct testing group—either a devoted Quality Assurance
      organization or simply a bunch of developers who haven't write any of the
      software to be tested.

II. Coding Style and Conventions
1. Adopt a consistent, readable format that is easy to maintain.
    参见项目Atradius - CICS_rewrite 中的格式标准
2. Adopt logical, consistent naming conventions for modules and data structures.
    参见项目Atradius - CICS_rewrite 中的命名规范
3. Standardize module and program headers.
    参见项目Atradius - CICS_rewrite 中的程序头部定义
4. Tag module END statements with module names.
    在END之后知名package的名字或者是function,procedure的名字
5. Name procedures with verb phrases and functions with noun phrases.
    使用动词定义procedure, 使用名词定义function.
6. Self-document using block and loop labels.
    例如:           LOOP
                  ... lots of month-related code ...
           END LOOP monthly_analysis;
7. Express complex expressions unambiguously using parentheses.
    对于复杂表达式,应该准确无误的使用括号.
8. Use vertical code alignment to emphasize vertical relationships.
    应该在格式标准中给出标准, 例如:

        WHERE COM.company_id           = SAL.company_id
            AND COM.company_type_cd  = TYP.company_type_cd
            AND TYP.company_type_cd   = CFG.company_type_cd
            AND COM.region_cd              = REG.region_cd
            AND REG.status                   = RST.status;
9. Comment tersely with value-added information.
    在任何需要的地方添加注释, 不应该使用/**/来完成多行注释,应该在每行注释前用--标记
10. Adopt meaningful naming conventions for source files.
    对于package的声明文件: *.psp
    对于package体,使用:     *.pbd

III. Variables and Data Structures
3.1 Declaring Variables and Data Structures
3.1.1 Match datatypes to computational usage.
      

数据类型	问题和建议
NUMBER	如果没有像NUMBER(12,2)这样明确指明小数点, Oracle就会默认使用38位的小数. 如果你不需要这么长(38位)小数,那么就会浪费内存.
CHAR	这个类型是固定长度的字符串,主要是为了兼容早期的Oracle版本才会使用.赋给CHAR变量的字符串如果长度不够,会自动从右边补空格,这会造成以外错误.如果不是必须使用CHAR类型,应该尽量不使用.
VARCHAR	这是为了兼容性是出现的VARCHAR2的变体,尽量使用VARCHAR2而不是使用VARCHAR.
VARCHAR2	使用VARCHAR2的最大挑战是应该避免定义其固定长度,例如VARCHAR2(30).应该尽量使用%TYPE和SUBTYPE. 在Oracle8之前,为了方便操作和赋值的,VARCHAR2被当作变长字符串,但是Oracle会为全部长度申请空间.例如,定义了VARCHAR2(2000), 即使你之服了一个3字节长度的值给他,Oracle也会申请2000个字节的字符串.
INTEGER	如果整数值的范围在–231+1 .. 231–1 (a.k.a. – 2147483647 .. 2147483647)之间,那么最好使用PLS_INTEGER. 这是操作整数的最有效的方法.

Check there is no count(*) in the sql query(It can be replaced with count(Primary_key)).
Check if columns used in the where condition are indexed.
Check if at least one join condition is present if the query is referring to two tables (i,e Atleast N-1 join conditions should be present if the query uses N tables).
Check if proper aliases are used for the tables containing join conditions.
Check there is no use of pseudo columns (Rowid ,Rownum) in the query.
Check there is no use of NVL or NVL2 or NULLIF function on the column if that column in the base table is not null.
Check if values clause has all the proper column names specified in the Insert queries.
Check if the update statement has at least one suitable where condition.
Check if SQL queries/sub-queries have correct precedence.
3.1.2 Anchor variables to database datatypes using %TYPE and %ROWTYPE.
3.1.3 Use SUBTYPE to standardize application-specific datatypes.
3.1.4 Do not hard-code VARCHAR2 lengths.
3.1.5 Use CONSTANT declarations for variables whose values do not change.
3.1.6 Perform complex variable initialization in the executable section.
    Example
        Here's some dangerous code, since it isn't at all apparent what these functions do
    and what they pass back:
            CREATE OR REPLACE PROCEDURE find_bestsellers
            IS
                l_last_title book.title%TYPE :=
                last_search (SYSDATE);
                l_min_count INTEGER(3) :=
                bestseller.limits (bestseller.low);
                        BEGIN
                            And here is a much safer approach:
                            CREATE OR REPLACE PROCEDURE find_bestsellers
                        IS
                            l_last_title book.title%TYPE;
                            l_min_count INTEGER(3);
                            PROCEDURE init IS
                                BEGIN
                                    l_last_title:= last_search (SYSDATE);
                                    l_min_count:=
                                                bestseller.limits (bestseller.low);
                                EXCEPTION
                    -- Trap and handle all errors
                    -- inside the program
                    END;
            BEGIN
                init;

3.2 Using Variables and Data Structures
3.2.1 Replace complex expressions with Boolean variables and functions.
3.2.2 Do not overload data structure usage.
        Reliance on a "time-saver" short-cut should raise a red flag.
3.2.3 Remove unused variables and code.
3.2.4 Clean up data structures when your program terminates (successfully or with an error).
3.2.5 Beware of and avoid implicit datatype conversions.
        Not use:
             DECLARE my_birthdate DATE := '09-SEP-58';
        but use:
             DECLARE my_birthdate DATE := TO_DATE ('09-SEP-58', 'DD-MON-RR');
3.2.6 Package application-named literal constants together.
3.2.7 Centralize TYPE definitions in package specifications.
3.2.8 Use package globals judiciously and only in package bodies.
        (尽量使用参数而不是全局变量来传递数值,这样可以有效降低耦合性.)
3.2.9 Expose package globals using "get and set" modules.

IV. Control Structures
4.1 Conditional and Boolean Logic
    4.1.1 Use ELSIF with mutually exclusive clauses.
    4.1.2 Use IF...ELSIF only to test a single, simple condition.
               Breaking an expression into smaller pieces can aid maintainability; if and when the logic changes, you can change one IF clause without affecting the logic of others.
    4.1.3 Replace and simplify IF statements with Boolean expressions.
4.2 Loop Processing
    4.2.1 Never EXIT or RETURN from WHILE and FOR loops.
    4.2.2 Use a single EXIT in simple loops.
    4.2.3 Use a simple loop to avoid redundant code required by a WHILE loop.
    4.2.4 Never declare the FOR loop index.
    4.2.5 Scan collections using FIRST, LAST, and NEXT in loops.
    4.2.6 Move static expressions outside of loops and SQL statements.
    4.2.7 Use anonymous blocks within IF statements to conserve resources.
    4.2.8 Label and highlight GOTOs if using this normally unnecessary construct.
V. Exception Handling
     Set guidelines for application-wide error handling before you start coding.
     参照Atradius的异常处理标准.
     应该建立一个package,包含一下内容:
      . 处理异常情况的procedure,例如写error log;
      . 一个raise程序,用来隐藏复杂的RAISE_APPLICATION_ERROR 和 application-specific error numbers.
      . 一个函数(function),用来针对给定的error number返回一个错误信息.
5.1 抛出异常(Raising Exceptions)
5.1.1 Verify preconditions using standardized assertion routines that raise violation exceptions.
5.1.2 Use the default exception-handling model to communicate module status back to calling PL/SQL programs.
5.1.3 Catch all exceptions and convert to meaningful return codes before returning to non-PL/SQL host programs.
       参照Atradius项目. PL/SQL将异常以特定的格式返回到调用它的Java层.
5.1.4 Use your own raise procedure in place of explicit calls to RAISE_APPLICATION_ERROR.
5.1.5 Only RAISE exceptions for errors, not to branch execution control.
5.1.6 Do not overload an exception with multiple errors unless the loss of information is intentional.

5.2 处理异常Handling Exceptions
5.2.1 Handle exceptions that cannot be avoided but can be anticipated.
5.2.2 Avoid hard-coded exposure of error handling by using standard, declarative procedures.
5.2.3 Use named constants to soft-code application-specific error numbers and messages.
5.2.4 Include standardized modules in packages to dump package state when errors occur.
5.2.5 Use WHEN OTHERS only for unknown exceptions that need to be trapped.

5.3 声明异常Declaring Exceptions
5.3.1 Standardize named application exceptions in package specifications.
       CREATE OR REPLACE PACKAGE overdue
       IS
            excessive_lateness EXCEPTION;
            PRAGMA EXCEPTION_INIT (
                    excessive_lateness, -20700);
            fetch_out_of_sequence EXCEPTION;
                    PRAGMA EXCEPTION_INIT (
                        fetch_out_of_sequence, -1003);
5.3.2 Document all package exceptions by module in package specifications.
        函数或者procedure的说明应该放在其上方,而且尽量避免使用/**/,应该在每行前使用--.
5.3.3 Use the EXCEPTION_INIT pragma to name system exceptions that might be raised by your program.

VI.  在PL/SQL中写SQL(Writing SQL in PL/SQL)
      SQL-00: Establish and follow clear rules for how to write SQL in your application.
       . 不要重复一条SQL语句;
       . 将所有的SQL语句封装到程序接口之内(通常是使用package);
       . 写代码的时候要始终警惕,要假定低层数据结构会被改变;
       . 使用PL/SQL对于SQL的增强功能方面的优势(Take advantage of PL/SQL-specific enhancements for SQL.).
6.1 常用SQL和事务管理(General SQL and Transaction Management.)
6.1.1 SQL-01: Qualify PL/SQL variables with their scope names when referenced inside SQL statements.
       在识别标识符(例如变量,参数等)时,SQL的级别总是高于PL/SQL. PL/SQL中定义的变量,参数,传递到SQL语句中,如果此变量和某个表名或字段名相同,那么,它就会被SQL当作表名或者字段名,而不是当作PL/SQL定义的变量来处理.
       例子:
             CREATE OR REPLACE PROCEDURE show_fav_flavor (
                                  pref_type IN VARCHAR2)
            IS
                pref VARCHAR2(100);
            BEGIN
                SELECT preference INTO pref
                  FROM personal_preferences PP
                WHERE PP.pref_type = pref_type;  --Just like 1 = 1;
                pl (pref);
            END;
       解决方法: 1. 将 pref_type 定义为 perf_type_in;
                    2. 使用 WHERE PP.pref_type = show_fav_flavor.pref_type;
6.1.2 SQL-02: Use incremental COMMITs to avoid rollback segment errors when changing large numbers of rows.
       当更新很多行的时候,可以每隔一定的行数,进行一次COMMIT.
6.1.3 SQL-03: Use autonomous transactions to isolate the effect of COMMITs and ROLLBACKs (Oracle8i).
        将PL/SQL代码块定义成自治事务块(autonomous transaction), 可以在这个代码块内save 或者 roll back, 而不会影响代码块之外.
        方法:
            CREATE OR REPLACE PROCEDURE log_error (
                    code IN INTEGER, msg IN VARCHAR2)
            AS
                    PRAGMA AUTONOMOUS_TRANSACTION;
            BEGIN
                    INSERT INTO error_log
                            (errcode, errtext, created_on, created_by)
                    VALUES
                            (code, msg, SYSDATE, USER);
                    COMMIT;
            EXCEPTION
                    WHEN OTHERS THEN ROLLBACK;
            END;

6.2 从PL/SQL中查询数据(Querying Data from PL/SQL).
6.2.1 SQL-04: Put single-row fetches inside functions; never hard-code a query in your block.
       将单行查询放到函数里,不要在代码块中直接写查询语句.
       BEGIN
            SELECT title INTO l_title      -- HARD-CODED
              FROM book                      -- QUERY...
            WHERE isbn =isbn_in;          -- BAD IDEA!
       应该定义:
            PACKAGE te_book
            IS
                FUNCTION title (isbn_in IN book.isbn%TYPE)
                RETURN book.title%TYPE;
       然后,就可以这样使用了:
       BEGIN
            l_title := te_book.title (isbn_in);
6.2.2 隐藏DUAL表的使用(SQL-05: Hide reliance on the dual table.)
        DAUL表是一个虚拟表, Oracle或者开发者可以通过它来访问SQL引擎(SQL engine)内的功能, 这些功能是PL/SQL不能完成的.
        不要在程序内直接使用DAUL表, 将它放到自己写的函数里, 需要用的时候,直接调用函数.
6.2.3 SQL-06: Define multi-row cursors in packages so they can be used from multiple programs.
        在package中定义多个cursor, 这样既可以方便在其他程序中使用.
6.2.4 SQL-07: Fetch into cursor records, never into a hard-coded list of variables.
       定义游标类型的记录变量,将游标内容取出到游标类型的记录里,而不是直接放到某几个字段变量里.
        cursor_name_record       cursor_name%ROWTYPE;
6.2.5 SQL-08: Use COUNT only when the actual number of occurrences is needed.
       只有在必须的条件下才可以使用COUNT, 一般情况下不要使用COUNT.
       不能使用COUNT(*), 应该使用COUNT(primary key).
6.2.6 SQL-09: Use a cursor FOR loop to fetch all rows in a cursor unconditionally.
6.2.7 SQL-10: Never use a cursor FOR loop to fetch just one row.
6.2.8 SQL-11: Specify columns to be updated in a SELECT FOR UPDATE statement.
6.2.9 SQL-12: Parameterize explicit cursors.
        应该将Cursor中要用到的值放到cursor的参数列表里面.
6.2.10 SQL-13: Use RETURNING to retrieve information about modified rows (Oracle8).
       使用RETURNING来返回你在INSERT(UPDATE, SELECT, INSERT, DELETE)时,改变的记录的信息.
       INSERT INTO patient (patient_id, last_name, first_name)
              VALUES (patient_seq.NEXTVAL, 'FEUERSTEIN', 'STEVEN')
         RETURNING patient_id INTO l_patient_id;
6.2.11 SQL-14: Use BULK COLLECT to improve performance of multi-row queries (Oracle8i).
       当经常从数据库中返回大量记录的时候,可以使用BULK COLLECT来查询.

6.3 Changing Data from PL/SQL
6.3.1 SQL-15: Encapsulate INSERT, UPDATE, and DELETE statements behind procedure calls.
        不要将DML语句写到应用程序代码里面.
6.3.2 SQL-16: Reference cursor attributes immediately after executing the SQL operation.
       在执行SQL(sql1)操作后,要立即引用Cursor标志. 否则的话,如果又执行了(sql2),则cursor的标志反映的就是sql2的结果了.
       在PL/SQL中, INSERT, UPDATE 和 DELETE语句都被做为"隐式CURSOR".
       You can obtain information about the results of the implicit operation most recently
executed in your session by checking any of the following cursor attributes:

Attribute	Returns
SQL%ROWCOUNT	DML语句产生效果的行数
SQL%ISOPEN	总是为FALSE,因为对于这些,cursor总是隐式的打开或关闭.
SQL%FOUND	如果语句至少影响了一行记录,则为TRUE
SQL%NOTFOUND	如果语句至少影响了一行记录,则为FALSE

6.3.3 SQL-17: Check SQL%ROWCOUNT when updating or removing data that "should" be there.
       使用INSERT, UPDATE和DELETE语句时, 要检查SQL%ROWCOUNT的值,以确保语句正确,完整的得到了执行.(对于Update和Delete语句,即使没有改变任何一条记录,也不会产生Exception).
6.3.4 SQL-18: Use FORALL to improve performance of collection-based DML (Oracle8i).
       FORALL语句能通过减少PL/SQL语句执行器和SQL引擎间的上下文交换(context switches), 来大幅度的提升DML语句的性能.

6.4 动态SQL和动态PL/SQL(Dynamic SQL and Dynamic PL/SQL)
6.4.1 SQL-19: Encapsulate dynamic SQL parsing to improve error detection and cleanup.(没搞懂)
       封装动态SQL分析, 用于提高错误检测和清理的性能.
6.4.2 SQL-20: Bind, do not concatenate, variable values into dynamic SQL strings. (查看Bind和Concatenate的区别)
       使用Bind而不是concatenate,将变量值赋给动态sql语句.
6.4.3 SQL-21: Soft-code the maximum length of columns in DBMS_SQL.DEFINE_COLUMN calls.
6.4.4 SQL-22: Apply the invoker rights method to all stored code that executes dynamic SQL (Oracle8i).
       将"invoker rights method"权限应用到所有执行动态SQL语句的代码里去.
       通过在程序头部添加AUTHID CURRENT_USER来实现.
6.4.5 SQL-23: Format dynamic SQL strings so they can be easily read and maintained.
       格式化动态语句,以便于阅读和维护.

VII. Program Construction
    PL/SQL中共有三类程序: Procedure, Function 和 Trigger
7.1 Structure and Parameters
    结构和参数
7.1.1 MOD-01: Encapsulate and name business rulesand formulas behind function headers.
7.1.2 MOD-02: Standardize module structure using function and procedure templates.
7.1.3 MOD-03: Limit execution section sizes to a single page using modularization.
7.1.4 MOD-04: Use named notation to clarify, self-document, and simplify module calls.
    什么是 NAMED NOTATION:   
        使用这种语法,在参数列表里指明参数的名字和它的值: parameter name =>  value
        在调用一个程序时,如果遇到下列情况,就应该使用NAMED NOTATION:
            a. 程序的的参数列表很长,而且令人迷惑;
            b. 程序很少被用到,或者说,对这个程序或者它的列表不熟悉;
            c. 程序的多个IN类型参数有默认值;
            d. 有时重载程序(overloaded programs)的设计要求使用named notation. (In some cases, it actually requires named notation due to the parameter list design of overloaded programs (as is necessary with the built-in package, DBMS_OBFUSCATION_TOOLKIT). )
7.1.5 MOD-05: Avoid side-effects in your programs.
7.1.6 MOD-06: Use NOCOPY to minimize overhead when collections and records are [IN] OUT parameters (Oracle8i).
       传数值给参数的时候,共有2种方式: (引用传递(指向内存的同一位置), 值传递(将值在内存中重新拷贝一份))
       PL/SQL的参数传递依据一下2个原则:
          . IN参数的的传递是采用引用传递;
          . OUT或者IN OUT的传递是采用值传递;
       当传给OUT或者IN OUT的值非常大的时候,程序的性能就会下降.以下两个方法可以用来解决这个问题:
          . 使用NOCOPY提示,这样PL/SQL就不会拷贝;
          . 全局化(Globalize)数据结构,这样就不需要传递此数据了,而是直接在程序里面调用.
       Example:
       PROCEDURE analyze_results (
            date_in IN DATE,
            values IN OUT NOCOPY numbers_varray,
            validity_flags IN OUT NOCOPY validity_rectype
        );

7.2 Functions
7.2.1 MOD-07: Limit functions to a single RETURN statement in the execution section.
       一个函数,只使用一个RETURN语句
7.2.2 MOD-08: Keep functions pure by avoiding [IN] OUT parameters.
        尽量避免在function中使用[IN] OUT参数.
       SQL语句中,不能调用带有OUT或者IN OUT参数的function.
                     不能调用返回record或者被索引的表(index-by table)的function.
       当需要返回多行信息的时候,可以采用以下方法:
          1. Return a record or collection of values
          2. Break up the single function into multiple functions, all returning scalar values
          3. Change your function into a procedure
7.2.3 MOD-09: Never return NULL from Boolean functions.
        不要从Boolean型函数里返回NULL.

7.3 Triggers
7.3.1 MOD-10: Minimize the size of trigger execution sections.      
7.3.2 MOD-11: Consolidate "overlapping" DML triggers to control execution order.
7.3.3 MOD-12: Raise exceptions to report on donothing INSTEAD OF triggers.
7.3.4 MOD-13: Implement server problem logs and "to do" lists using database triggers.
7.3.5 MOD-14: Use ORA_% public synonyms to reference database and schema event trigger attributes.
       使用ORA_%来获取触发triger的事件.
       所有触发trigger的事件的信息都可以通过DBMS_STANDARD包来获取.
        · ora_sysevent: The system event that invokes the system trigger
        · ora_dict_obj_owner : The object owner on which the DDL statement is being done
        · ora_dict_obj_name : The object name on which the DDL statement is being performed
7.3.6 MOD-15: Validate complex business rules with DML triggers. (还不清楚)
7.3.7 MOD-16: Populate columns of derived values with triggers.
7.3.8 MOD-17: Use operational directives to provide more meaningful error messages from within triggers.

VIII. Package Construction
1. PKG-01: Group related data structures and functionality together in a single package.
2. PKG-02: Provide well-defined interfaces to business data and functional manipulation using packages.
3. PKG-03: Freeze and build package specifications before implementing package bodies.
4. PKG-04: Implement flexible, user-adjustable functionality using package state toggles and related techniques.
5. PKG-05: Build trace "windows" into your packages using standardized programs.
    建立窗口时,需要做一下步骤:
    · Add tracing code inside the package body.
    · Supply an on-off switch in the specification so that users can open and close the window.
6. PKG-06: Use package body persistent data structures to cache and optimize data-driven processing. (还不清楚)
    可以将用户名放到专门的package里,便于数据库的迁移.
7. PKG-07: Insulate applications from Oracle version sensitivity using version-specific implementations.
    使用version-specific来避免Oracle的版本问题.
    当我们的程序需要在多个版本的Oracle上运行时, 可以采用以下方法:
        · Use "lowest common denominator" features that are available in all versions. (最小公分母还不清楚).
        · Use the best and most appropriate features available in each version.
    Here are the basic steps you need to take to achieve this effect:
        1. Extract all version-specific logic into separate package bodies, separated by database version.
        2. Create a function that returns the current Oracle version.
        3. Modify or create the main (public) package to call each of the version-specific programs, based on the current Oracle version.
        4. Compile and use the code in each different database version.
8. PKG-08: Avoid bloating package code with unnecessary but easy-to-build modules.
9. PKG-09: Simplify and encourage module usage using overloading to widen calling options.
10. PKG-10: Consolidate the implementation of related overloaded modules.
11. PKG-11: Separate package specifications and bodies into different source code files.
12. PKG-12: Use a standard format for packages that include comment headers for each type of element defined in the package.

IX. Built-in Packages
9.1 DBMS_OUTPUT  
9.1.1 BIP-01: Avoid using the DBMS_OUTPUT.PUT_LINE procedure directly.
       在程序中,不应该直接出现DBMS_OUTPUT.PUT_LINE.
9.2 UTL_FILE
9.2.1 BIP-02: Improve the functionality and error handling of UTL_FILE by using a comprehensive encapsulation package.
9.2.2 BIP-03: Validate the setup of UTL_FILE with simple tests.
9.2.3 BIP-04: Handle expected and named exceptions when performing file I/O.
9.2.3 BIP-05: Encapsulate UTL_FILE.GET_LINE to avoid propagating the NO_DATA_FOUND exception.
9.2.4 BIP-06: Soft-code directory names in your calls to UTL_FILE.FOPEN.

9.3 DBMS_PIPE
9.3.1 BIP-07: Encapsulate interaction with specific pipes.
9.3.2 BIP-08: Provide explicit and appropriate timeout values when you send and receive messages.
9.3.3 BIP-09: Use RESET_BUFFER in exception handlers and before you pack data into the message buffer.

9.4 DBMS_ JOB
9.4.1 BIP-10: Use your own submission procedure to improve job management capabilities.
9.4.2 BIP-11: Trap all errors in DBMS_ JOB-executed stored procedures and modify the job queue accordingly.

经验:
1. 对象在定义以前,都应该使用cursor来删除可能已经存在的此对象.
2. 定义了一个TABLE类型后,
           TYPE t_jobs_rec IS TABLE OF jobs%ROWTYPE
                INDEX BY BINARY_INTEGER;
            l_t_jobs_rec    t_jobs_rec;
       如果使用了l_t_jobs_rec.DELETE(n), 那么就不能使用 FOR i IN l_t_jobs_rec.FIRST .. l_t_jobs_rec.LAST.
       因为l_t_jobs_rec的索引不是连续的,如果删除了l_t_jobs_rec(9), 则9这个索引就不存在了,只剩下l_t_jobs_rec(0)..l_t_jobs_rec(8), l_t_jobs_rec(10)..l_t_jobs_rec(n);