存储过程中一些用到的语句的整理

一、存储过程格式：

1）首先在存储过程开始阶段加要注释：若详尽一些，可以参考下面的列表。正常情况一定要加的是描述，创建人，创建时间。如果后期有修改要加上修改人，修改时间，修改内容及原因

  /****************************************************/

  /* 指标: INDEX_01
  /* 指标描述：毛收入实际值
  /* 传入参数-1：END_DATE_IN VARCHAR2
  /* 传入参数-2：
  /* 传入参数....(如有需要，可以继续添加)
  /* 参数格式：YYYYMMDD
  /* 作者：LJQ
  /* 创建时间：2014-9-16 18:43
  /* 修改人：
  /* 修改时间：
  /****************************************************/

2)一般存储过程中有两种：一种是带参数的存储过程，一种是不带参数的存储过程。

不带参数的存储过程示例：

CREATE OR REPLACE PROCEDURE PRO_CUSTBD_ODS_MONITOR IS
BEGIN
/*
  执行语句
*/
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;
END;

带参数的存储过程示例：

PROCEDURE PRO_INFOLOC_YHL_SJ(END_DATE_IN VARCHAR2) IS
BEGIN
  /*
  带有参数END_DATE_IN的语句
  */
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;
END;

3）存储过程中可以定义一些变量并给这些变量赋值。例如：

PROCEDURE PRO_INFOLOC_YHL_SJ(END_DATE_IN VARCHAR2) IS
    C_ZB_CODE CONSTANT VARCHAR2(30) := 'PRO_INFOLOC_YHL_SJ';
    --常量：定义常量时需要使用CONSTANT关键字，并且必须在声明时就为该常量赋值。而且在程序其他部分不能修改该常量的值。
    --常量的语法格式为：constant_name CONSTANT data_type {:=| default } value; 赋值号是  :=
    L_JOB_ID    NUMBER;
    L_VER_MONTH VARCHAR2(8); 
  BEGIN
    L_JOB_ID    := PAC_INFOLOC_UTIL.LOG_JOB_INTERFACE_START(G_PKG_NAME,
                                                            C_ZB_CODE,
                                                            END_DATE_IN);
    L_VER_MONTH := substr(END_DATE_IN, 1, 6);
  
    /*语句
    delete from LOC_T_ODS_PIC_EMP t where t.ver_month = L_VER_MONTH;
    */


  EXCEPTION
    WHEN OTHERS THEN
      ROLLBACK;
  END;

4）存储过程中一些特殊语句的解释：

PRAGMA AUTONOMOUS_TRANSACTION
当前的存储过程作为已有事务的子事务运行，子事务的commit，rollback操作不影响父事务的状态

oracle中的sqlcode和sqlerrm函数

sqlcode函数用来返回pl/sql执行内部异常代码。
sqlerrm函数返回指定错误代码的错误信息。
sqlcode和sqlerrm是不能直接在sql语句中使用，必须先将其赋值给变量后，才能在sql语句中使用。
sqlerrm用的最多的是在pl/sql编程中来捕获异常的详细信息，特别是在when other中使用非常有用。

dbms_utility.format_error_backtrace 返回当前异常相应的描述。系统为最近一次生成的异常设置了一个栈，并跟踪它的传递过程，而这个函数使用这个栈，然后返回该异常的整个传递过程。这个函数对错误的定位和实施下一步处理起着至关重要的作用。

 execute immediate 解析并马上执行动态的SQL语句
 execute immediate,它解析并马上执行动态的SQL语句或非运行时创建的PL/SQL块

SQL%ROWCOUNT 用于记录修改的条数
sql%rowcount用于记录修改的条数，就如你在sqlplus下执行delete from之后提示已删除xx行一样，
 这个参数必须要在一个修改语句和commit之间放置，否则你就得不到正确的修改行数。

例如：l_logmemo := substr(sqlcode || ';' || Sqlerrm ||
                        dbms_utility.format_error_backtrace,
                        1,
                        500);

execute immediate 'alter table dm_custbd_index_month truncate partition part_cusind_cas012'; 

二.包与存储过程示例

CREATE OR REPLACE PACKAGE PAC_INFOLOC_STATION_MAIN IS
--包头 可以加注释
 PROCEDURE PRO_INFOLOC_YHL_SJ(END_DATE_IN VARCHAR2);
END PAC_INFOLOC_STATION_MAIN;


CREATE OR REPLACE PACKAGE BODY PAC_INFOLOC_STATION_MAIN IS
--包体
 /****************************************************/
  /* 指标: PRO_INFOLOC_YHL_SJ
  /* 指标描述：实际值
  /* 传入参数-1：END_DATE_IN VARCHAR2
  /* 传入参数-2：
  /* 传入参数....(如有需要，可以继续添加)
  /* 参数格式：YYYYMMDD
  /* 作者：LJQ
  /* 创建时间：2014-9-16 18:43
  /* 修改人：
  /* 修改时间：
  /****************************************************/
  PROCEDURE PRO_INFOLOC_YHL_SJ(END_DATE_IN VARCHAR2) IS
    C_ZB_CODE CONSTANT VARCHAR2(30) := 'PRO_INFOLOC_YHL_SJ'; --指标编码
    L_JOB_ID    NUMBER; --JOB ID
    L_VER_MONTH VARCHAR2(8); --月份YYYYMM
  BEGIN
    L_JOB_ID    := PAC_INFOLOC_UTIL.LOG_JOB_INTERFACE_START(G_PKG_NAME,
                                                            C_ZB_CODE,
                                                            END_DATE_IN);
    L_VER_MONTH := substr(END_DATE_IN, 1, 6);
  
    delete from LOC_T_ODS_PIC_EMP t where t.ver_month = L_VER_MONTH;
    insert into LOC_T_ODS_PIC_EMP
      (VER_MONTH,
       VER_DATE,
       PRODUCT_CAT,
       XTJYT_NAME,
       REPORT_DATE,
       RESP_NO,
       SEG1,
       PERSON_NAME,
       PERSON_NO,
       CHECK_RESP_TYPE,
       PERSON_TYPE,
       BL_NUM,
       INSERT_TIME,
       PRODUCT_CODE)
      select TO_CHAR(T.REPORT_DATE, 'YYYYMM'),
             TO_CHAR(T.REPORT_DATE, 'YYYYMMDD'),
             T.PRODUCT_CAT,
             T.XTJYT_NAME,
             to_char(T.Report_Date, 'YYYYMMDD'),
             T.RESP_NO,
             T.SEG1,
             T.PERSON_NAME,
             LPAD(T.PERSON_NO, 8, 0) AS PERSON_NO,
             DECODE(T.CHECK_RESP_TYPE, '部件', '采购', T.CHECK_RESP_TYPE),
             T.PERSON_TYPE,
             count(*),
             TO_CHAR(SYSDATE, 'YYYYMMDDHH24MISS'),
             T.PRODUCT_CODE
        from infoloc_pic_itf_temp t
       where to_char(t.report_date, 'YYYYMM') = L_VER_MONTH
         and trim(t.check_resp_type) in ('部件', '设计')
         and t.person_no is not null
         and t.person_no || t.row_id not in
             (select substr(t2.appeal_id, 0, length(t2.appeal_id) - 14)
                from APP.CEI_CHECK_RESULT_FCT t2
               where t2.check_results = 'SU04'
                 and t2.appeal_id is not null)
       group by T.PRODUCT_CAT,
                T.XTJYT_NAME,
                T.report_date,
                T.RESP_NO,
                T.SEG1,
                T.PERSON_NAME,
                T.PERSON_NO,
                T.CHECK_RESP_TYPE,
                T.PERSON_TYPE,
                T.PRODUCT_CODE;
  
    COMMIT; --提交
    PAC_INFOLOC_UTIL.LOG_JOB_INTERFACE_END(L_JOB_ID);
  EXCEPTION
    WHEN OTHERS THEN
      PAC_INFOLOC_UTIL.LOG_ERROR(G_PKG_NAME, C_ZB_CODE, SQLERRM); --错误监控日志
      ROLLBACK;
  END;


END PAC_INFOLOC_STATION_MAIN;

CREATE OR REPLACE PACKAGE PAC_INFOLOC_UTIL IS


  -- Author  : 创建者
  -- Created : 创建时间（格式：YYYY-MM-DD HH:MI）
  -- Purpose : INFOLOC
  G_PKG_NAME CONSTANT VARCHAR2(40) := 'PAC_INFOLOC_UTIL';
  FUNCTION LOG_JOB_INTERFACE_START(PKG_NAME  VARCHAR2,
                                   SUB_NAME  VARCHAR2,
                                   YESTERDAY VARCHAR2) RETURN NUMBER;


  PROCEDURE LOG_JOB_INTERFACE_END(JOB_SEQ NUMBER);


  PROCEDURE LOG_ERROR(PKG_NAME IN VARCHAR2,
                      ZB_CODE  IN VARCHAR2,
                      ERROR    IN VARCHAR2);
END PAC_INFOLOC_UTIL;


CREATE OR REPLACE PACKAGE BODY PAC_INFOLOC_UTIL IS


  PROCEDURE MAIN(YESTERDAY VARCHAR2) IS
    JOB_ID NUMBER;


  /****************************************************/
  /* 指标: LOG_JOB_SHOW
  /* 指标描述：指标计算任务日志(前台显示用）
  /* 传入参数-1：PKG_NAME 任务主标题
  /* 传入参数-2：SUB_NAME 任务副标题
  /* 传入参数-3  TYPE 任务类型
  /* 传入参数-4  YESTERDAY（YYYYMMDD） 计算日期
  /* 作者：XIAODB
  /* 创建时间：2013-4-8 14:00
  /* 修改人：
  /* 修改时间：
  /****************************************************/
  FUNCTION LOG_JOB_INTERFACE_START(PKG_NAME  VARCHAR2,
                                   SUB_NAME  VARCHAR2,
                                   YESTERDAY VARCHAR2) RETURN NUMBER
  
   IS
    PRAGMA AUTONOMOUS_TRANSACTION;
    JOB_SEQ NUMBER;
  BEGIN
  
    SELECT SEQ_LOCSYS_JOB_MONITOR.NEXTVAL INTO JOB_SEQ FROM DUAL;
  
    INSERT INTO LOC_T_SYS_JOB_MONITOR_ZB
      (JOB_ID, PKG_NAME, SUB_NAME, START_DATE, VERSION_DATE)
    VALUES
      (JOB_SEQ, PKG_NAME, SUB_NAME, SYSDATE, YESTERDAY);
    COMMIT;
  
    RETURN JOB_SEQ;
  EXCEPTION
    WHEN OTHERS THEN
      ROLLBACK;
  END;
  /****************************************************/
  /* 指标: LOG_JOB_END
  /* 指标描述：指标计算任务日志
  /* 传入参数-1：JOB_SEQ  任务序列号
  /* 作者：XIAODB
  /* 创建时间：2013-4-8 14:00
  /* 修改人：
  /* 修改时间：
  /****************************************************/
  PROCEDURE LOG_JOB_INTERFACE_END(JOB_SEQ NUMBER) IS
    PRAGMA AUTONOMOUS_TRANSACTION;
  
  BEGIN
  
    UPDATE LOC_T_SYS_JOB_MONITOR_ZB
       SET END_DATE = SYSDATE,
           HS_TIME =
           (SYSDATE - START_DATE) * 24 * 60 * 60 --此处单位为秒，可以自由设定
     WHERE JOB_ID = JOB_SEQ;
    COMMIT;
  
  EXCEPTION
    WHEN OTHERS THEN
      ROLLBACK;
  END;


  /****************************************************/
  /* 指标: LOG_JOB_END
  /* 指标描述：指标计算任务日志
  /* 传入参数-1：JOB_SEQ  任务序列号
  /* 作者：XIAODB
  /* 创建时间：2013-4-8 14:00
  /* 修改人：
  /* 修改时间：
  /****************************************************/
  PROCEDURE LOG_ERROR(PKG_NAME IN VARCHAR2,
                      ZB_CODE  IN VARCHAR2,
                      ERROR    IN VARCHAR2) IS
  
    PRAGMA AUTONOMOUS_TRANSACTION;
  
  BEGIN
  
    INSERT INTO LOC_T_SYS_ERROR_LOG
      (PKG_NAME, ZB_CODE, ERROR, INSERT_TIME)
    VALUES
      (PKG_NAME, ZB_CODE, ERROR, TO_CHAR(SYSDATE, 'YYYYMMDDHH24MISS'));
    COMMIT;


  EXCEPTION
    WHEN OTHERS THEN
      ROLLBACK;
  END;


END PAC_INFOLOC_UTIL;

示例二：

Procedure sp_custbd_kpi_cusind_ca is
  --============================================
  --= 过程功能: ***数量
  --= 作者: LJQ
  --= 创建时间: 2016-06-24
  --=========================================


begin


  ------清空客户****分区表


  execute immediate 'alter table dm_custbd_index_month truncate partition part_cusind_ca';


  -----------客户******
  insert into /*+append*/
  dm_custbd_index_month --nologging
    select t.c_month,
           t.c_soto,
           'CUSIND_CA',
           t.kpi_value,
           '0',
           '0',
           SYSDATE,
           'LJQ',
           SYSDATE,
           'LJQ'
      from (select a.c_month,a.c_soto,a.kpi_value
              from DM_CUSTBD_INDEX_MONTH a
             where a.index_id = 'CUSIND_CAS1002') t;


  --将上一步的SQL的执行结果条目数记录到变量中


  ROWLINE := SQL%ROWCOUNT;


  commit;


  --insert成功将结果写入日志表


  insert into dm_custbd_t_log
  values
    (sysdate,
     'PKG_CUSTBD_KPI_MONTH',
     'sp_custbd_kpi_cusind_cas007',
     'dm_custbd_index_month',
     ROWLINE,
     'success');
  commit;


exception
  when others then
    -------异常处理
    l_logmemo := substr(sqlcode || ';' || Sqlerrm ||
                        dbms_utility.format_error_backtrace,
                        1,
                        500);
  
    -------异常日志
    insert into dm_custbd_t_log
    values
      (sysdate,
       'PKG_CUSTBD_KPI_MONTH',
       'sp_custbd_kpi_cusind_cas007',
       'dm_custbd_index_month',
       1,
       l_logmemo);
    commit;
  
end sp_custbd_kpi_cusind_ca;