packages与package bodies 使用

您能帮我看一下，我写的这个吗？
 
 
create or replace package PKG_UBI_TOTW_1 is

 

  function f_get_transactionid return integer;

  procedure executeIMMEDIATE(i_proc_name      in varchar2,
                             i_targ_tablename in varchar2,
                             i_statdate       in date,
                             i_sql1           in varchar2,
                             i_sql2           in varchar2,
                             i_sql3           in varchar2,
                             i_sql4           in varchar2,
                             i_sql5           in varchar2);
  procedure executeIMMEDIATE(i_proc_name      in varchar2,
                             i_targ_tablename in varchar2,
                             i_statdate       in date,
                             i_sql1           in varchar2);
  procedure executeIMMEDIATENoException(i_proc_name      in varchar2,
                                        i_targ_tablename in varchar2,
                                        i_statdate       in date,
                                        i_sql1           in varchar2,
                                        i_sql2           in varchar2,
                                        i_sql3           in varchar2,
                                        i_sql4           in varchar2,
                                        i_sql5           in varchar2);
  procedure executeIMMEDIATENoException(i_proc_name      in varchar2,
                                        i_targ_tablename in varchar2,
                                        i_statdate       in date,
                                        i_sql1           in varchar2);
 
  procedure createUpdatecar(i_calcdate in varchar2,
                            i_statdate in varchar2);

end PKG_UBI_TOTW_1;
/

 

create or replace package body PKG_UBI_TOTW_1 is

  -- Function and procedure implementations
  function f_get_transactionid return integer is
    i_max_id integer;
  begin
    select max(RECORD_NUM) + 1
      into i_max_id
      from UBI_DATA_RULE
     where file_type = 'FLT'
       and transaction_code = 0;
 
    update UBI_DATA_RULE
       set record_num = i_max_id -- 输入ID
     where file_type = 'FLT'
       and transaction_code = 0;
    COMMIT;
    return i_max_id;
  end;

procedure createUpdatecar(i_calcdate in varchar2,
                          i_statdate in varchar2) as
    TYPE ref_cursor_type IS REF CURSOR; --定义一个动态游标 
    csr_flt ref_cursor_type;
    TYPE row_flt_type IS RECORD(
         POLICY_NUMBER                       VARCHAR2(22),          
         ENROLLED_VIN                        VARCHAR2(30),          
         DEVICE_ID                           VARCHAR2(50),          
         POLICY_VALID_DATE                   DATE,                  
         REPORT_DATE                         DATE,                  
         RCD_START_DATE                      DATE,                  
         RCD_END_DATE                        DATE,                  
         POLICY_STAT_DATE                    DATE,                  
         VEHICLE_YEAR                        VARCHAR2(4) ,          
         VEHICLE_MAKE                        VARCHAR2(100),
         VEHICLE_MODEL                       VARCHAR2(10),        
         SCORE_ENROLLED_VIN                  VARCHAR2(10),          
         SCORE_ACCOUNT_ID                    VARCHAR2(22),          
         STAT_DATE                           DATE);                 
 
begin
    --i_CalcDate 20151001表示15年10月01日
    --i_StatDate 20151001标识15年10月01日
    DECLARE
    --异常定义
    E_NUMERIC_FORMAT EXCEPTION; --数字格式异常
    VSQL              VARCHAR2(30000);
    V_EXISTS_TMPTABLE NUMBER(2) := 0;
    --V_LAST_STATDATE   VARCHAR2(10);
    I_ROW_NUMBER      NUMBER :=0;
    row_flt      row_flt_type;
    begin
        -- 输入参数有效性判断
        IF NOT (LTRIM(I_CALCDATE, '0123456789') IS NULL) and
  NOT (LTRIM(I_STATDATE, '0123456789') IS NULL) THEN
            RAISE E_NUMERIC_FORMAT;
        END IF;
       
    /* SELECT TO_CHAR(MAX(T.STATDATE),'YYYY-MM-DD')
  INTO V_LAST_STATDATE
  FROM UBI_POLICY T
  WHERE T.STATDATE < TO_DATE(I_STATDATE,'YYYY-MM-DD');
        if V_LAST_STATDATE IS NULL then
            V_LAST_STATDATE := '2015-01-01';
        end if;*/
       
        /**临时记录保单,投标车辆, 车载设备表******************************/
        --临时车险保单存在批单记录
        select count(1)
        into V_EXISTS_TMPTABLE
        from user_tables a
        where a.TABLE_NAME = 'TMP_UBI_FLT_CMAIN';
        if (V_EXISTS_TMPTABLE > 0) then
            VSQL := 'DROP TABLE TMP_UBI_FLT_CMAIN';
            EXECUTE IMMEDIATE VSQL;
        end if;
       
        --临时记录 保单+批单
        VSQL := '
            CREATE TABLE TMP_UBI_FLT_CMAIN AS
            SELECT distinct PY.POLICYNO as POLICY_NUMBER,
                PY.STARTDATE   as POLICY_VALID_DATE, -- 保单生效日期
                PY.UPDATEDATE  as REPORT_DATE,       -- 生成保单日期
                PY.STATDATE    as RCD_START_DATE,    -- 保单起保日期
                PY.ENDDATE     as RCD_END_DATE,      -- 保单失效日期
                PY.INPUTDATE   as POLICY_STAT_DATE,  -- 日期
                PY.ENDSTATDATE as RCD_END_STAT_DATE, -- 日期
                PY.CERTINO, -- 表主键
                PY.MODELCODE   as POLICY_MODEL_CODE, -- 车型代码(车系+车型)
                PY.CARBRAND    as POLICY_CAR_BRAND,  -- 车型品牌
                PY.VINNO       as POLICY_VINNO,      -- VINNO
                PY.FRAMENO     as POLICY_FRAMENO,    -- 车架号
                PY.ENDORTYPE   as POLICY_ENDORTYPE,  -- 操作类型
                PY.POLICYNO as SCORE_ACCOUNT_ID, TRUNC(SYSDATE) as STAT_DATE
            FROM STATCMAIN PY, STATCMAIN PT
            WHERE PY.CLASSCODE = ''05''  -- 车险
                AND PY.CERTITYPE = ''E'' -- 批单
                --AND PY.EUNDERWRITEENDDATE > DATE ''2015-12-01''
                AND PY.EUNDERWRITEENDDATE > TO_DATE(''' || I_CALCDATE || ''',''YYYY-MM-DD'')
                AND PY.ENDSTATDATE = DATE ''5000-01-01'' -- 有效保单
                AND (PY.ENDORTYPE LIKE ''%40%'' OR PY.ENDORTYPE LIKE ''%42%'' OR PY.ENDORTYPE LIKE ''%88%'')
                AND PY.POLICYNO = PT.POLICYNO
                AND PY.FRAMENO <> PT.FRAMENO'; -- 车架号
        -- 更新车载设备的信息
        /**临时记录保单,投标车辆, 车载设备表******************************/
        --临时记录 保单+投保车辆+车载设备表
        VSQL := '
            CREATE TABLE TMP_UBI_FLT_VEHICLE AS
            SELECT DF.POLICY_NUMBER, DF.CERTINO,
                DF.POLICY_VALID_DATE, DF.REPORT_DATE, DF.RCD_START_DATE, DF.RCD_END_DATE, DF.POLICY_STAT_DATE,
                DF.POLICY_VINNO as VEHICLE_VINNO, -- VINNO
                TO_CHAR(T2.ENROLLDATE,''YYYY'') as VEHICLE_YEAR, -- 出厂年份
                T1.FACTORY as VEHICLE_MAKE, -- 生产厂商 CarBrand
                T1.CARSERIESCODE as VEHICLE_MODEL, -- 车型代码
                DF.SCORE_ACCOUNT_ID, DF.STAT_DATE
            FROM TMP_UBI_FLT_CMAIN DF
                left join STATDCARMODEL T1 on DF.POLICY_MODEL_CODE = T1.MODELCODE
                inner join STATCITEMCAR T2 on DF.CERTINO = T2.CERTINO
            order by DF.POLICY_NUMBER asc, DF.CERTINO asc' ;
        -- 根据批单表记录, 获取每次更新明细
        select count(1)
        into V_EXISTS_TMPTABLE
        from user_tables a
        where a.TABLE_NAME = 'TMP_UBI_FLT_SCORE';
        if (V_EXISTS_TMPTABLE > 0) then
            VSQL := 'DROP TABLE TMP_UBI_FLT_SCORE';
            EXECUTE IMMEDIATE VSQL;
        end if;
       
        --临时记录 保单+批单
        VSQL := '
            CREATE TABLE TMP_UBI_FLT_SCORE AS
            SELECT distinct PY.POLICY_NUMBER as SCORE_POLICY_NUMBER,
                PY.SCORE_ACCOUNT_ID as SCORE_ACCOUNT_ID,
                PY.DEVICE_ID        as SCORE_DEVICE_ID,
                PY.ENROLLED_VIN     as SCORE_ENROLLED_VIN,
                PY.VEHICLE_YEAR     as SCORE_VEHICLE_YEAR,
                PY.VEHICLE_MAKE     as SCORE_VEHICLE_MAKE,
                PY.VEHICLE_MODEL    as SCORE_VEHICLE_MODEL,
                PY.ENROLLED_VIN as ENROLLED_VIN2,
                PY.VEHICLE_YEAR as ENROLLED_YEAR2,
                PY.VEHICLE_MAKE as ENROLLED_MAKE2,
                PY.VEHICLE_MODEL as ENROLLED_MODEL2
            FROM UBI_SCORE_USER PY
            WHERE PY.END_STAT_DATE = DATE ''5000-01-01'' '; -- 有效保单
       
        -- 临时记录 重置投保车辆的信息
        VSQL := '
            update TMP_UBI_FLT_SCORE
            set ENROLLED_VIN2= '''',
                ENROLLED_YEAR2='''',
                ENROLLED_MAKE2='''',
                ENROLLED_MODEL2='''' ';
       
        -- 根据保单号关联, 关联批单记录更新投保车辆的VIN, 车辆出厂年份, 生产厂商, 型号
        VSQL := '
            update TMP_UBI_FLT_SCORE DF
            set (ENROLLED_VIN2, ENROLLED_YEAR2, ENROLLED_MAKE2, ENROLLED_MODEL2) = (select FV.VEHICLE_VINNO, FV.VEHICLE_YEAR, FV.VEHICLE_MAKE, FV.VEHICLE_MODEL
                from TMP_UBI_FLT_SCORE DF, TMP_UBI_FLT_VEHICLE FV
                where DF.SCORE_POLICY_NUMBER = FV.POLICY_NUMBER)
            where exists (select 1
                from TMP_UBI_FLT_SCORE DF, TMP_UBI_FLT_VEHICLE FV
                where DF.SCORE_POLICY_NUMBER = FV.POLICY_NUMBER) ';
       
        -- 根据规则生成Transaction_ID
        -- 关联表 UBI_DATA_RULE, 生成RECORD_NUM
        VSQL := ' DELETE FROM UBI_EXPORT_FLT_DETAIL WHERE TRANSACTION_CODE in  (''507'', ''503'')';
       
        -- 根据规则生成Transaction_ID
        -- 关联表 UBI_DATA_RULE, 生成RECORD_NUM
        VSQL := ' DELETE FROM UBI_EXPORT_FLT_DETAIL WHERE TRANSACTION_CODE in  (''507'', ''503'') AND STAT_DATE =  TO_DATE(''' ||
              I_CALCDATE || ''',''YYYYMMDD'') ';

  executeIMMEDIATE('PKG_UBI_TOTW.createUpdatecar',
                       'UBI_EXPORT_FLT_DETAIL',
                       TO_DATE(I_CALCDATE, 'YYYYMMDD'),
                       VSQL);
   
-- 游标
      open csr_flt for 'select FV.POLICY_NUMBER,
                 FV.VEHICLE_VINNO as ENROLLED_VIN,
                 DF.SCORE_DEVICE_ID as DEVICE_ID,
                 FV.POLICY_VALID_DATE,
                 FV.REPORT_DATE,
                 FV.RCD_START_DATE,
                 FV.RCD_END_DATE,
                 FV.POLICY_STAT_DATE,
                 FV.VEHICLE_YEAR,
                 FV.VEHICLE_MAKE, FV.VEHICLE_MODEL,
                 DF.SCORE_ENROLLED_VIN,
                 FV.SCORE_ACCOUNT_ID,
                 FV.STAT_DATE
        from TMP_UBI_FLT_VEHICLE FV, TMP_UBI_FLT_SCORE DF
        where FV.POLICY_NUMBER = DF.SCORE_POLICY_NUMBER
        order by FV.STAT_DATE asc, FV.POLICY_NUMBER asc;
      loop
         fetch csr_flt
             into row_flt;
        exit when csr_flt%rowtype;
      I_ROW_NUMBER := f_get_transactionid();
           dbms_output.put_line(I_ROW_NUMBER);
           I_ROW_NUMBER := 0;
  
         if I_ROW_NUMBER > 0 then
          -- 插入表 UBI_EXPORT_FLT_DETAIL
          VSQL := '
          INSERT INTO UBI_EXPORT_FLT_DETAIL
            (FLT_FILE_NAME,
             TRANSACTION_ID,
             TRANSACTION_STATUS,
             TRANSACTION_CODE,
             TRANSACTION_DATE,
             TRANSACTION_EFFECTIVE_DATE,
             COMPANY_NAME,
             COMPANY_ID,
             PROGRAM_ID, --CUSTOMER_ID,
             POLICY_NUMBER,
             POLICY_EFFECTIVE_DATE,
             COUNTRY_CODE,
             ENROLLED_VIN_1,
             ENROLLED_VIN_2,
             VEHICLE_IDENTIFIER_1,
             VEHICLE_IDENTIFIER_2,
             VEHICLE_YEAR,
             VEHICLE_MAKE,
             VEHICLE_MODEL,
             DEVICE_ID,
             DRIVEABILITY_SCOREVERSION,
             SCORE_ACCOUNT_ID,
             STAT_DATE)
          VALUES
            (NULL,
             '||I_ROW_NUMBER||',
             ''1'',
             507,
          503,
             '''||to_char(row_flt.STAT_DATE, 'YYYY-MM-DD') || 'T' ||
             to_char(row_flt.STAT_DATE, 'hh24:mi:ss')||''',
             '''||to_char(row_flt.STAT_DATE, 'yyyy-mm-dd') || 'T' ||
             to_char(row_flt.STAT_DATE, 'hh24:mi:ss')||''',
             ''SUNS'',
             50000,
             400,
             '''||row_flt.POLICY_NUMBER||''',
             '''||to_char(row_flt.STAT_DATE, 'yyyy-mm-dd') || 'T' ||
             to_char(row_flt.STAT_DATE, 'hh24:mi:ss')||''',
             ''CN'',
             '''||row_flt.ENROLLED_VIN||''',
            NULL,
             NULL,
             NULL,
             '''||row_flt.VEHICLE_YEAR||''',
             '''||row_flt.VEHICLE_MAKE||''',
             '''||row_flt.VEHICLE_MODEL||''',
             '''||row_flt.DEVICE_ID||''',
             ''USNCW02'',
             '''||row_flt.SCORE_ACCOUNT_ID||''',
             TO_DATE('''||to_char(row_flt.STAT_DATE,'YYYY-MM-DD')||''',''yyyy-mm-dd'')) ';
       
          executeIMMEDIATE('PKG_UBI_TOTW.createNewAccount',
                           'UBI_EXPORT_FLT_DETAIL',
                           TO_DATE(I_CALCDATE, 'YYYYMMDD'),
                           VSQL);
     update UBI_DATA_RULE
         set RECORD_NUM := (I_ROW_NUMBER + 1) -- 输入ID
            where FILE_TYPE := 'FLT'
               and TRANSACTION_CODE = 0;
        end if;
      end loop;
      close csr_flt;
       
      --临时车险保单存在批单记录
        select count(1)
        into V_EXISTS_TMPTABLE
        from user_tables a
        where a.TABLE_NAME = 'TMP_UBI_FLT_CMAIN';
        if (V_EXISTS_TMPTABLE > 0) then
            VSQL := 'DROP TABLE TMP_UBI_FLT_CMAIN';
            EXECUTE IMMEDIATE VSQL;
        end if;
        /*  --临时设备交易记录表
        select count(1)
        into V_EXISTS_TMPTABLE
        from user_tables a
        where a.TABLE_NAME = 'TMP_UBI_FLT_VEHICLE';
        if (V_EXISTS_TMPTABLE > 0) then
            VSQL := 'DROP TABLE TMP_UBI_FLT_VEHICLE';
            EXECUTE IMMEDIATE VSQL;
        end if;
       

       
        --临时设备交易记录表
        select count(1)
        into V_EXISTS_TMPTABLE
        from user_tables a
        where a.TABLE_NAME = 'TMP_UBI_FLT_VEHICLE';
        if (V_EXISTS_TMPTABLE > 0) then
            VSQL := 'DROP TABLE TMP_UBI_FLT_VEHICLE';
            EXECUTE IMMEDIATE VSQL;
        end if;*/
       
        -- 根据批单表记录, 获取每次更新明细
        select count(1)
        into V_EXISTS_TMPTABLE
        from user_tables a
        where a.TABLE_NAME = 'TMP_UBI_FLT_SCORE';
        if (V_EXISTS_TMPTABLE > 0) then
            VSQL := 'DROP TABLE TMP_UBI_FLT_SCORE';
            EXECUTE IMMEDIATE VSQL;
        end if;
       
    end;
   
end createUpdatecar;
 
procedure executeIMMEDIATE(i_PROC_NAME      in varchar2,
                             i_TARG_TABLENAME in varchar2,
                             i_statdate       in date,
                             i_sql1           in varchar2,
                             i_sql2           in varchar2,
                             i_sql3           in varchar2,
                             i_sql4           in varchar2,
                             i_sql5           in varchar2) as
    v_begintime date;
    v_error_msg varchar(100);
  begin
 
    v_begintime := sysdate;
    EXECUTE IMMEDIATE i_sql1 || i_sql2 || i_sql3 || i_sql4 || i_sql5;
    insert into ubi_sql_log
      (PROC_NAME,
       TARG_TABLENAME,
       SQL1,
       SQL2,
       SQL3,
       SQL4,
       SQL5,
       STATDATE,
       BEGINTIME,
       ENDTIME,
       STATUS,
       MESSAGE)
    values
      (i_PROC_NAME,
       i_TARG_TABLENAME,
       i_SQL1,
       i_SQL2,
       i_SQL3,
       i_SQL4,
       i_SQL5,
       i_STATDATE,
       v_BEGINTIME,
       sysdate,
       '0',
       'Successed');
    commit;
  exception
    when others then
      v_error_msg := SUBSTR(SQLERRM, 1, 100);
      insert into ubi_sql_log
        (PROC_NAME,
         TARG_TABLENAME,
         SQL1,
         SQL2,
         SQL3,
         SQL4,
         SQL5,
         STATDATE,
         BEGINTIME,
         ENDTIME,
         STATUS,
         MESSAGE)
      values
        (i_PROC_NAME,
         i_TARG_TABLENAME,
         i_SQL1,
         i_SQL2,
         i_SQL3,
         i_SQL4,
         i_SQL5,
         i_STATDATE,
         v_BEGINTIME,
         sysdate,
         '2',
         v_error_msg);
      commit;
      /*抛出异常，主程序中处理*/
      RAISE_APPLICATION_ERROR(-20001, SQLERRM);
  end executeIMMEDIATE;
  procedure executeIMMEDIATE(i_proc_name      in varchar2,
                             i_targ_tablename in varchar2,
                             i_statdate       in date,
                             i_sql1           in varchar2) AS
  BEGIN
    executeIMMEDIATE(i_proc_name,
                     i_targ_tablename,
                     i_statdate,
                     i_sql1,
                     NULL,
                     NULL,
                     NULL,
                     NULL);
  END executeIMMEDIATE;
  procedure executeIMMEDIATENoException(i_proc_name      in varchar2,
                                        i_targ_tablename in varchar2,
                                        i_statdate       in date,
                                        i_sql1           in varchar2,
                                        i_sql2           in varchar2,
                                        i_sql3           in varchar2,
                                        i_sql4           in varchar2,
                                        i_sql5           in varchar2) AS
    v_begintime date;
  BEGIN
    v_begintime := sysdate;
    EXECUTE IMMEDIATE i_sql1 || i_sql2 || i_sql3 || i_sql4 || i_sql5;
    insert into ubi_sql_log
      (PROC_NAME,
       TARG_TABLENAME,
       SQL1,
       SQL2,
       SQL3,
       SQL4,
       SQL5,
       STATDATE,
       BEGINTIME,
       ENDTIME,
       STATUS,
       MESSAGE)
    values
      (i_PROC_NAME,
       i_TARG_TABLENAME,
       i_SQL1,
       i_SQL2,
       i_SQL3,
       i_SQL4,
       i_SQL5,
       i_STATDATE,
       v_BEGINTIME,
       sysdate,
       '0',
       'Successed');
    commit;
  exception
    when others then
      null;
  END executeIMMEDIATENoException;
  procedure executeIMMEDIATENoException(i_proc_name      in varchar2,
                                        i_targ_tablename in varchar2,
                                        i_statdate       in date,
                                        i_sql1           in varchar2) AS
  BEGIN
    executeIMMEDIATENoException(i_proc_name,
                                i_targ_tablename,
                                i_statdate,
                                i_sql1,
                                NULL,
                                NULL,
                                NULL,
                                NULL);
  END executeIMMEDIATENoException;

end PKG_UBI_TOTW_1;
/

create or replace package PACK_tools is

procedure proc_start_etllog(table_Name varchar2,v_maxtime  date , cid out integer); ------数据处理开始日志

procedure proc_end_etllog( cid integer)  ;  -----数据处理结束日志

procedure proc_error_etllog( cid integer,mess varchar2)  ;  -----数据处理错误日志
  
procedure proc_stat_table( tablename varchar2)  ;  -----收集表的统计信息
  
function  proc_like_str(  sour varchar2, tar varchar2) return varchar2  ;   ------比较字符串   ，字符串之间是or关系
  
function  proc_like_str_first(  sour varchar2, tar varchar2) return varchar2  ;   ------字符串开始
  
function  proc_normalcount_str(  sour varchar2, tar varchar2) return varchar2  ;   -------Normalcount 比较
function  proc_null_str(  sour varchar2, tar varchar2) return varchar2  ;
function  proc_like_and_str(  sour varchar2, tar varchar2) return varchar2  ;   ------比较字符串  ,字符串之间是and关系

function  proc_notlike_str( sour varchar2, tar varchar2) return varchar2 ;  -----比较字符串
  
function  proc_between_str( sour number, tar varchar2) return varchar2 ;  -----比较字符串
function  proc_in_str( sour varchar2, tar varchar2) return varchar2 ;  -----比较字符串  
  

end PACK_tools;
 
 
 
------------------------------------
create or replace package body PACK_tools is

  /*
  * 数据处理开始日志
  */
  procedure proc_start_etllog(table_Name varchar2,
                              v_maxtime  date,
                              cid        out integer) as
  
  begin
    INSERT INTO ETL_Log
      (ID, TableLevel, TableName, ETLBeginDate, DataFromDate, Flag)
    VALUES
      (ETL_ID.NEXTVAL,
       'DW',
       table_Name,
       sysdate,
       to_date(v_maxtime, 'yyyy/mm/dd hh24:mi:ss'),
       'UnFinished')
    returning id into cid;
    commit;
  end proc_start_etllog;
  /*
  *数据处理结束日志
  *
  */

  procedure proc_end_etllog(cid integer) as
  begin
    UPDATE ETL_log
       SET ETLEndDate = sysdate, Flag = 'Succeed'
     WHERE id = cid;
    commit;
  end proc_end_etllog;