首页 > 其他 > 详细

CREATE OR REPLACE FUNCTION

时间:2014-06-27 11:43:07      阅读:494      评论:0      收藏:0      [点我收藏+]

CREATE OR REPLACE FUNCTION SF_Taishou_Ksai_Date(v_receiptNum IN CHAR,
                                                v_his        IN CHAR)
  RETURN VARCHAR2 DETERMINISTIC IS
  RESULT              VARCHAR2(50);
  v_result_t          VARCHAR2(50);
  v_tmp_code          VARCHAR2(100);
  v_uriage_sha_code   VARCHAR2(50);
  v_Adv_Sha_Code      VARCHAR2(50);
  v_Taishou_Ksai_Date date;
BEGIN

  select *
    INTO v_tmp_code, v_uriage_sha_code
    from (select REPLACE(atom_concat(Ksai_Sha_Code), ‘,‘, ‘‘),
                 tafd.uriage_sha_code
         
            from TB_ADV_FEE_DETAIL tafd
           where tafd.receipt_num = v_receiptNum
             and tafd.his = v_his
           group by tafd.receipt_num, tafd.his, tafd.uriage_sha_code)
   where rownum = 1;

  IF (INSTR(v_tmp_code, v_uriage_sha_code) = 0) THEN
 
    IF (INSTR(v_tmp_code, ‘1‘) = 0) THEN
      v_result_t := ‘‘;
    ELSE
      v_result_t := ‘1‘;
    END IF;
 
    IF (INSTR(v_tmp_code, ‘2‘) = 0) THEN
      v_result_t := v_result_t || ‘‘;
    ELSE
      v_result_t := v_result_t || ‘2‘;
    END IF;
 
    IF (INSTR(v_tmp_code, ‘3‘) = 0) THEN
      v_result_t := v_result_t || ‘‘;
    ELSE
      v_result_t := v_result_t || ‘3‘;
    END IF;
 
    IF (INSTR(v_tmp_code, ‘4‘) = 0) THEN
      v_result_t := v_result_t || ‘‘;
    ELSE
      v_result_t := v_result_t || ‘4‘;
    END IF;
 
    IF (INSTR(v_tmp_code, ‘5‘) = 0) THEN
      v_result_t := v_result_t || ‘‘;
    ELSE
      v_result_t := v_result_t || ‘5‘;
    END IF;
 
    v_uriage_sha_code := substr(v_result_t, 0, 1);
 
  END IF;

  /**対象掲載日*/

  SELECT Ksai_Date, Adv_Sha_Code
    INTO v_Taishou_Ksai_Date, v_Adv_Sha_Code
    FROM TB_ADV_FEE_DETAIL
   WHERE Receipt_Num = v_receiptNum
     AND His = v_his
     AND Ksai_Sha_Code = v_uriage_sha_code
     AND rownum = 1;

  IF (v_Taishou_Ksai_Date = null) THEN
    v_Taishou_Ksai_Date := sysdate;
  END IF;

  /**広告会社名略2  Adv_Sha_Name_Ryaku2*/
  SELECT Adv_Sha_Name_Ryaku2
    INTO RESULT
    FROM TB_ADV_SHA
   WHERE Adv_Sha_Code = v_Adv_Sha_Code
     AND v_Taishou_Ksai_Date between Apply_Start_Date and Apply_End_Date;

  RETURN(RESULT);
END SF_Taishou_Ksai_Date;

 

 

/*SELECT BEFORE HOSEI*/
select tafd.Adv_Sha_Name,
       SF_Taishou_Ksai_Date(tafd.receipt_num, tafd.his),
       tafd.*
  from TB_ADV_FEE_DETAIL tafd
 where tafd.Adv_Sha_Name <>
       SF_Taishou_Ksai_Date(tafd.receipt_num, tafd.his)


/*HOSEI SQL*/
update TB_ADV_FEE_DETAIL tafd
set tafd.Adv_Sha_Name = SF_Taishou_Ksai_Date(tafd.receipt_num, tafd.his),
tafd.Del_Per_Code = ‘founder‘

 where tafd.Adv_Sha_Name <>
       SF_Taishou_Ksai_Date(tafd.receipt_num, tafd.his),

 

/*SELECT AFTER HOSEI*/
 select tafd.Adv_Sha_Name,
        SF_Taishou_Ksai_Date(tafd.receipt_num, tafd.his),
        tafd.*
   from TB_ADV_FEE_DETAIL tafd
  where tafd.Adv_Sha_Name <>
        SF_Taishou_Ksai_Date(tafd.receipt_num, tafd.his),
    and tafd.Del_Per_Code = ‘founder‘;

/*CLEAR HOSEI FLG*/
update TB_ADV_FEE_DETAIL tafd
   set tafd.Del_Per_Code = null
 where tafd.Del_Per_Code = ‘founder‘;


 /*HOSEI SQL*/

 

 

CREATE OR REPLACE FUNCTION,布布扣,bubuko.com

CREATE OR REPLACE FUNCTION

原文:http://www.cnblogs.com/caogang/p/3810804.html

(0)
(0)
   
举报
评论 一句话评论(0
关于我们 - 联系我们 - 留言反馈 - 联系我们:wmxa8@hotmail.com
© 2014 bubuko.com 版权所有
打开技术之扣,分享程序人生!