PL/SQL注释风格

PL/SQL Comment Style

常常纠结程序的编程风格和注释的风格。PL/SQL中的编程风格也是各种流派，所以我就看了下Oracle内置函数的编程风格和注释风格，自己总结了下，记录下来，当作参考。

Document Style

----------------
--  主题(大写)
--
--  正文开始
--  啦啦啦啦
--  第一段落结束.
-- 
--  第二段开始
--  啦啦啦
--  第二段结束。

-----------------
-- 主题1
-- 
-- 正文开始
-- 正文结束。

Procedure Example

存储过程栗子

procedure waitany(name out varchar2,
                    message out varchar2,
                    status out integer,
                    timeout in number default maxwait);
--  描述过程的作用...
--   接着说...
--  输入参数：
--    第一个参数：...
--      第一个参数参数描述...
--    第N个参数: ....
--  输出参数(返回值)：
--    第一个参数： ...
--  异常处理：...
--    处理描述...

procedure waitany(name out varchar2,
                    message out varchar2,
                    status out integer,
                    timeout in number default maxwait);
  --  Wait for an alert to occur for any of the alerts for which this
  --    session is registered.  Although probably unusual, the same session
  --    that waits for the alert may also first signal the alert.  In this
  --    case remember to commit after the signal and prior to the wait.
  --    Otherwise a lock request exception (status 4) will occur.  This
  --    call always performs a commit.
  --  Input parameters:
  --    timeout
  --      The maximum time to wait for an alert.  If no alert occurs before
  --      timeout seconds, then this call will return with status of 1.
  --  Output parameters:
  --    name
  --      The name of the alert that occurred, in uppercase.
  --    message
  --      The message associated with the alert.  This is the message
  --      provided by the 'signal' call.  Note that if multiple signals
  --      on this alert occurred before the waitany call, then the message
  --      will correspond to the most recent signal call.  Messages from
  --      prior signal calls will be discarded.
  --    status
  --      0 - alert occurred
  --      1 - timeout occurred
  --  Errors raised:
  --    -20000, ORU-10024: there are no alerts registered.
  --       Cause: You must register an alert before waiting.
  --

DBMS_ALERT Example

oracle中的DBMS_ALERT栗子。

create or replace package sys.dbms_alert is

  ------------
  --  OVERVIEW
  --
  --  This package provides support for the asynchronous (as opposed to
  --  polling) notification of database events.  By appropriate use of
  --  this package and database triggers, an application can cause itself
  --  to be notified whenever values of interest in the database are
  --  changed.
  --
  --  For example, suppose a graphics tool is displaying a graph of some
  --  data from a database table.  The graphics tool can, after reading and
  --  graphing the data, wait on a database alert ('dbms_alert.waitone')
  --  covering the data just read.  The tool will automatically wake up when
  --  the data is changed by any other user.  All that is required is that a
  --  trigger be placed on the database table which then performs a signal
  --  ('dbms_alert.signal') whenever the trigger is fired.
  --
  --  Alerts are transaction based.  This means that the waiting session
  --  does not get alerted until the transaction signalling the alert commits.
  --
  --  There can be any number of concurrent signallers of a given alert, and
  --  there can be any number of concurrent waiters on a given alert.
  --
  --  A waiting application will be blocked in the database and cannot do
  --  any other work.
  --
  --  Most of the calls in the package, except for 'signal', do commits.
  --

  -----------
  --  EXAMPLE
  --
  --  Suppose the application wishes to graph average salaries, say by
  --  department, for all employees.  So the application needs to know
  --  whenever 'emp' is changed.  The application would look like this:
  --
  --      dbms_alert.register('emp_table_alert');
  --    readagain:
  --      <read the emp table and graph it>
  --      dbms_alert.waitone('emp_table_alert', :message, :status);
  --      if status = 0 then goto readagain; else <error condition>
  --
  --  The 'emp' table would have a trigger similar to the following:
  --
  --    create trigger emptrig after insert or update or delete on emp
  --    begin
  --      dbms_alert.signal('emp_table_alert', 'message_text');
  --    end;
  --
  --  When the application is no longer interested in the alert, it does
  --    dbms_alert.remove('emp_table_alert');
  --  This is important since it reduces the amount of work required by
  --  the alert signaller.
  --
  --  If a session exits (or dies) while there exist registered alerts,
  --  they will eventually be cleaned up by future users of this package.
  --
  --  The above example guarantees that the application will always see
  --  the latest data, although it may not see every intermediate value.


  --------------
  --  VARIATIONS
  --
  --  The application can register for multiple events and can then wait for
  --  any of them to occur using the 'waitany' call.
  --
  --  An application can also supply an optional 'timeout' parameter to the
  --  'waitone' or 'waitany' calls.  A 'timeout' of 0 returns immediately
  --  if there is no pending alert.
  --
  --  The signalling session can optionally pass a message which will be
  --  received by the waiting session.
  --
  --  Alerts may be signalled more often than the corresponding application
  --  'wait' calls.  In such cases the older alerts are discaded.  The
  --  application always gets the latest alert (based on transaction commit
  --  times).
  --
  --  If the application does not require transaction based alerts, then the
  --  'dbms_pipe' package may provide a useful alternative
  --
  --  If the transaction is rolled back after the call to 'dbms_alert.signal',
  --  no alert will occur.
  --
  --  It is possible to receive an alert, read the data, and find that no
  --  data has changed.  This is because the data changed after the *prior*
  --  alert, but before the data was read for that *prior* alert.


  --------------------------
  --  IMPLEMENTATION DETAILS
  --
  --  In most cases the implementation is event-driven, i.e., there are no
  --  polling loops.  There are two cases where polling loops can occur:
  --
  --    1) Parallel mode.  If your database is running parallel mode then
  --       a polling loop is required to check for alerts from another
  --       instance.  The polling loop defaults to one second and is settable
  --       by the 'set_defaults' call.
  --    2) Waitany call.  If you use the 'waitany' call, and a signalling
  --       session does a signal but does not commit within one second of the
  --       signal, then a polling loop is required so that this uncommitted
  --       alert does not camouflage other alerts.  The polling loop begins
  --       at a one second interval and exponentially backs off to 30 second
  --       intervals.
  --
  --  This package uses the dbms_lock package (for synchronization between
  --  signallers and waiters) and the dbms_pipe package (for asynchronous
  --  event dispatching).

  -------------------------------------------------------
  --  INTERACTION WITH MULTI-THREADED AND PARALLEL SERVER
  --
  --  When running with the parallel server AND multi-threaded server, a
  --  multi-threaded (dispatcher) "shared server" will be bound to a
  --  session (and therefore not shareable) during the time a session has
  --  any alerts "registered", OR from the time a session "signals" an
  --  alert until the time the session commits.  Therefore, applications
  --  which register for alerts should use "dedicated servers" rather than
  --  connecting through the dispatcher (to a "shared server") since
  --  registration typically lasts for a long time, and applications which
  --  cause "signals" should have relatively short transactions so as not
  --  to tie up "shared servers" for too long.

  ------------
  --  SECURITY
  --
  --  Security on this package may be controlled by granting execute on
  --  this package to just those users or roles that you trust.  You may
  --  wish to write a cover package on top of this one which restricts
  --  the alertnames used.  Execute privilege on this cover package can
  --  then be granted rather than on this package.


  -------------
  --  RESOURCES
  --
  --  This package uses one database pipe and two locks for each alert a
  --  session has registered.


  ---------------------
  --  SPECIAL CONSTANTS
  --
  maxwait constant integer :=  86400000; -- 1000 days
  --  The maximum time to wait for an alert (essentially forever).


  ----------------------------
  --  PROCEDURES AND FUNCTIONS
  --
  procedure set_defaults(sensitivity in number);
  --  Set various defaults for this package.
  --  Input parameters:
  --    sensitivity
  --      In case a polling loop is required (see "Implementation Details"
  --      above), this is the time to sleep between polls.  Deafult is 5 sec.
  --
  procedure register(name in varchar2, cleanup in boolean default TRUE);
  --  Register interest in an alert.  A session may register interest in
  --    an unlimited number of alerts.  Alerts should be de-registered when
  --    the session no longer has any interest (see 'remove').  This call
  --    always performs a 'commit'.
  --  Input parameters:
  --    name
  --      The name of the alert in which this session is interested.
  --      WARNING:  Alert names beginning with 'ORA$' are reserved for use for
  --      products provided by Oracle Corporation.  Name must be 30 bytes
  --      or less.  The name is case-insensitive.
  --    cleanup
  --      This specifies whether we should perform cleanup of any orphaned
  --      pipes that may exist and are used by the dbms_alert package. This
  --      cleanup is only performed on the first call to "register" for each
  --      package instantiation. The default for the parameter is TRUE.
  --
  procedure remove(name in varchar2);
  --  Remove alert from registration list.  Do this when the session is no
  --    longer interested in an alert.  Removing an alert is important
  --    since it will reduce the amount of work done by signalers of the alert.
  --    If a session dies without removing the alert, that alert will
  --    eventually (but not immediately) be cleaned up.  This call always
  --    performs a commit.
  --  Input parameters:
  --    name
  --      The name of the alert to be removed from registration list. The
  --      name is case-insensitive.
  --
  procedure removeall;
  --  Remove all alerts for this session from registration list.  Do this
  --    when the session is no longer interested in any alerts.  Removing
  --    alerts is important since it will reduce the amount of work done
  --    by signalers of the alert.  If a session dies without removing all
  --    of its alerts, the alerts will eventually (but not immediately)
  --    be cleaned up.  This call always performs a commit.
  --
  --    This procedure is called automatically upon first reference to this
  --    package during a session.  Therefore no alerts from prior sessions
  --    which may have terminated abnormally can affect this session.
  procedure waitany(name out varchar2,
                    message out varchar2,
                    status out integer,
                    timeout in number default maxwait);
  --  Wait for an alert to occur for any of the alerts for which this
  --    session is registered.  Although probably unusual, the same session
  --    that waits for the alert may also first signal the alert.  In this
  --    case remember to commit after the signal and prior to the wait.
  --    Otherwise a lock request exception (status 4) will occur.  This
  --    call always performs a commit.
  --  Input parameters:
  --    timeout
  --      The maximum time to wait for an alert.  If no alert occurs before
  --      timeout seconds, then this call will return with status of 1.
  --  Output parameters:
  --    name
  --      The name of the alert that occurred, in uppercase.
  --    message
  --      The message associated with the alert.  This is the message
  --      provided by the 'signal' call.  Note that if multiple signals
  --      on this alert occurred before the waitany call, then the message
  --      will correspond to the most recent signal call.  Messages from
  --      prior signal calls will be discarded.
  --    status
  --      0 - alert occurred
  --      1 - timeout occurred
  --  Errors raised:
  --    -20000, ORU-10024: there are no alerts registered.
  --       Cause: You must register an alert before waiting.
  --
  procedure waitone(name in varchar2,
                    message out varchar2,
                    status out integer,
                    timeout in number default maxwait);
  --  Wait for specified alert to occur. If the alert was signalled since
  --    the register or last waitone/waitany, then this call will return
  --    immediately.  The same session that waits for the alert may also
  --    first signal the alert.  In this case remember to commit after the
  --    signal and prior to the wait.  Otherwise a lock request exception
  --    (status 4) will occur.  This call always performs a commit.
  --  Input parameters:
  --    name
  --      The name of the alert to wait for. The name is case-insensitive.
  --    timeout
  --      The maximum time to wait for this alert.  If no alert occurs before
  --      timeout seconds, then this call will return with status of 1.
  --      If the named alert has not been registered then the this call
  --      will return after the timeout period expires.
  --  Output parameters:
  --    message
  --      The message associated with the alert.  This is the message
  --      provided by the 'signal' call.  Note that if multiple signals
  --      on this alert occurred before the waitone call, then the message
  --      will correspond to the most recent signal call.  Messages from
  --      prior signal calls will be discarded.  The message may be up to
  --      1800 bytes.
  --    status
  --      0 - alert occurred
  --      1 - timeout occurred
  --
  procedure signal(name in varchar2,
                   message in varchar2);
  --  Signal an alert.
  --  Input parameters:
  --    name
  --      Name of the alert to signal.  The effect of the signal call only
  --      occurs when the transaction in which it is made commits.  If the
  --      transaction rolls back, then the effect of the signal call is as
  --      if it had never occurred.  All sessions that have registered
  --      interest in this alert will be notified.  If the interested sessions
  --      are currently waiting, they will be awakened.  If the interested
  --      sessions are not currently waiting, then they will be notified the
  --      next time they do a wait call.  Multiple sessions may concurrently
  --      perform signals on the same alert.  However the first session
  --      will block concurrent sessions until the first session commits.
  --      Name must be 30 bytes or less. It is case-insensitive.  This call
  --      does not perform a commit.
  --    message
  --      Message to associate with this alert.  This will be passed to
  --      the waiting session.  The waiting session may be able to avoid
  --      reading the database after the alert occurs by using the
  --      information in this message.  The message must be 1800 bytes or less.

end;

注释模版

CREATE OR REPLACE PROCEDURE TEST_PA_COMMENT_STYLE(I_COMP_ID    IN VARCHAR2, -- 1
                                                  I_MODE_IDS   IN VARCHAR2, -- 2
                                                  I_OBJ_TYPES  IN VARCHAR2, -- 3
                                                  I_OBJECTS    IN VARCHAR2, -- 4
                                                  I_DATE_POINT IN VARCHAR2 -- 5
                                                  ) IS
  -------------------------------
  -- TEST_PA_COMMENT_STYLE：                                                      
  -- 功能：
  --   根据对方数据添加“清理关键字”, 
  --   根据“借方”或者“借方”给对方添加关键字.    
  -- 编写人：LJN
  -- 编写日期：2017-12-26
  -- 内部提交(commit): NO
  --
  -- 策略(vpd)：
  --   时间和单位
  --
  -- 中间表（temporary table）：
  --   1. GT_GET_GLDX_SES
  --   2. GT_GET_KMTMP_SES

  -----------------------
  -- 输入（IN）参数说明：
  -- I_OBJ_TYPES：
  --   单位id
  -- I_MODE_IDS: 
  --   清理方式id，多个逗号分隔.
  -- I_OBJ_TYPES：
  --   管理对象类型id，多个逗号分隔.
  -- I_OBJECTS：
  --   管理对象id.
  -- I_DATE_POINT：
  --   时间点；格式YYYY-MM-DD.

  ---------------
  -- 输出(out)参数：
  --

  ---------------
  -- 异常说明：
  --   E_IN_PARAM_EMPTY_EX
  --     参数为空异常

  -----------
  -- 变量声明
  --   V_SQL：
  --     SQL语句.

  ----------
  -- 子程序
  --   FU_ADD_QUOTES_STR
  --     添加英文引号
  --   FU_BUILD_IN_CONDITION
  --     构建IN条件

  -----------
  -- 异常定义
  --   V_ERR_MSG
  --     错误消息.

  V_SQL VARCHAR2(8000);
  V_ERR_MSG  VARCHAR2(4000);
  V_ERR_CODE BINARY_INTEGER;
  E_PARAM_EMPTY_CODE CONSTANT BINARY_INTEGER DEFAULT - 20001;

  ---------------
  -- 参数为空异常.
  E_IN_PARAM_EMPTY_EX EXCEPTION;
  PRAGMA EXCEPTION_INIT(E_IN_PARAM_EMPTY_EX, -20001);

  

  FUNCTION FU_ADD_QUOTES_STR(I_STR       IN VARCHAR2, -- 1
                             I_SEPARATOR IN VARCHAR2 DEFAULT ',' -- 2
                             ) RETURN VARCHAR2 IS
    V_ID_STR    VARCHAR2(50);
    V_INDEX1    NUMBER(5) DEFAULT 0;
    V_INDEX2    NUMBER(5) DEFAULT 1;
    V_CONDITION VARCHAR2(4000);
    V_ID        VARCHAR2(4000);
    O_STR       VARCHAR2(4000);
  
  BEGIN
    V_ID_STR := I_STR;
    IF V_ID_STR IS NOT NULL THEN
      V_INDEX1 := INSTR(V_ID_STR, I_SEPARATOR);
      WHILE V_INDEX1 > -1 LOOP
        V_INDEX1 := INSTR(V_ID_STR, I_SEPARATOR, V_INDEX2);
        IF V_INDEX1 = 0 THEN
          V_ID     := SUBSTR(V_ID_STR, V_INDEX2);
          V_INDEX1 := -1;
        ELSE
          V_ID     := SUBSTR(V_ID_STR, V_INDEX2, V_INDEX1 - V_INDEX2);
          V_INDEX2 := V_INDEX1 + 1;
        END IF;
        IF V_CONDITION IS NOT NULL THEN
          V_CONDITION := V_CONDITION || ',';
        END IF;
        V_CONDITION := V_CONDITION || '''' || V_ID || '''';
      END LOOP;
    END IF;
    O_STR := V_CONDITION;
    RETURN(O_STR);
  END FU_ADD_QUOTES_STR;
  -- END FU_ADD_QUOTES_STR

  FUNCTION FU_BUILD_IN_CONDITION(I_NUM_STR   IN VARCHAR2, -- 1
                                 I_CONDT_STR IN VARCHAR2, -- 2
                                 I_SEPARATOR IN VARCHAR2 DEFAULT ',' --3
                                 ) RETURN VARCHAR2 IS
  
    V_CONDITION VARCHAR2(4000);
    O_STR       VARCHAR2(4000);
  
  BEGIN
    V_CONDITION := FU_ADD_QUOTES_STR(I_NUM_STR, I_SEPARATOR);
    O_STR       := I_CONDT_STR || ' IN (' || V_CONDITION || ')';
    RETURN(O_STR);
  END FU_BUILD_IN_CONDITION;

BEGIN
  ------------
  -- 参数处理
  -- 单位ID
  IF I_COMP_ID IS NULL THEN
    RAISE E_IN_PARAM_EMPTY_EX;
  END IF;

  -- 清理方式ID
  IF I_MODE_IDS IS NULL THEN
    RAISE E_IN_PARAM_EMPTY_EX;
  END IF;
  -------------
  -- 处理临时表
  EXECSQL('DELETE FROM GT_GET_KMTMP_SES');
  EXECSQL('DELETE FROM GT_GET_GLDX_SES');
  EXECSQL('DELETE FROM HZ_LS_KMHZBQUERY_SEC');
  ------------
  -- 科目
  --   GT_GET_KMTMP_SES

  -- 确定清理方式科目设置层级 及科目方向(ITEMCODE科目方向, LEAF清理层次)
  V_SQL := 'INSERT INTO HZ_LS_KMHZBQUERY_SEC (IID, ITEMCODE, LEAF)' ||
           ' SELECT ';
  --SELECT * FROM XTQLMODE X WHERE X.DWDH='0001' AND NVL(X.TYBZ, 0) = 0 AND X.QLMODEID IN ();

  ------------
  -- 对象类型

  -----------
  -- 管理对象

  ---------——---------
  -- 查询出未清理数据：
  --   1. qlbz=0

EXCEPTION
  ------------
  -- 参数为空
  WHEN E_IN_PARAM_EMPTY_EX THEN
    V_ERR_CODE := SQLCODE;
    V_ERR_MSG  := '参数为空.';
    DBMS_STANDARD.RAISE_APPLICATION_ERROR(V_ERR_CODE, V_ERR_MSG);
    DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
  WHEN OTHERS THEN
    V_ERR_CODE := SQLCODE;
    V_ERR_MSG  := SQLERRM(SQLCODE);
    ROLLBACK; -- OTHERS中必须显式回滚
  
    -------------
    -- 打印异常栈
    DBMS_STANDARD.RAISE_APPLICATION_ERROR(V_ERR_CODE, V_ERR_MSG);
    DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
    RAISE; -- 继续抛异常
END TEST_PA_COMMENT_STYLE;