oracle procedure 杂记

---

create or replace PROCEDURE  pro_inf_normal
IS
    V_OPERATING_DATE   table.OPERATING_DATE%TYPE;
    V_STN_NO           table.STN_NO        %TYPE;
    V_MACH_CODE        table.MACH_CODE     %TYPE;
    V_SHIFT_CODE       table.SHIFT_CODE    %TYPE;
    U_SHIFT_CODE       table.SHIFT_CODE    %TYPE;
    COUNT_REC           NUMBER(12);

CURSOR  c1 is
select *
from table

BEGIN
COUNT_REC := 0;
open c1;
Loop
fetch c1 INTO V_OPERATING_DATE, V_STN_NO, V_MACH_CODE, V_SHIFT_CODE;
exit when c1%NOTFOUND;
COUNT_REC := 0;

SELECT COUNT(*) INTO COUNT_REC  from table WHERE    column = V_OPERATING_DATE

IF COUNT_REC >= 1
THEN
    select columna into U_SHIFT_CODE  from table WHERE       column     = V_STN_NO

    UPDATE table
    set column = TO_CHAR(U_SHIFT_CODE)
    where OPERATING_DATE = V_OPERATING_DATE
    AND    columna     = V_STN_NO
    AND    columnb    = V_MACH_CODE
    AND    columnc = 'D';
    commit;
END IF;

end LOOP;
close c1;
END pro_inf_normal;

create or replace procedure        pro_inf_output_file_with_param(STN_NO_INPUT IN VARCHAR2)
IS

    V_STN_NO                    DAY_END_RPT_CONFIRM.STN_NO%TYPE;
    V_OPERATING_DATE         DAY_END_RPT_CONFIRM.OPERATING_DATE      %TYPE;

    COUNT_STN            NUMBER(12);

        STN_MSG          VARCHAR2(1000);
        SUMMARY_MSG        VARCHAR2(1000);
        v_sumFilename   VARCHAR2(50);
        f_sumHandle             UTL_FILE.FILE_TYPE;
        v_outFilename   VARCHAR2(50);
        f_outHandle             UTL_FILE.FILE_TYPE;


cursor c1 is

SELECT OPERATING_DATE,STN_NO
FROM   DAY_END_RPT_CONFIRM 
WHERE CONFIRMATION = 'N' 
AND STN_NO = TO_NUMBER(STN_NO_INPUT);

Begin


SELECT COUNT(*) INTO COUNT_STN
FROM   DAY_END_RPT_CONFIRM 
WHERE CONFIRMATION = 'N' 
AND OPERATING_DATE = TRUNC(SYSDATE-1)
AND STN_NO = TO_NUMBER(STN_NO_INPUT);

IF COUNT_STN > 0
THEN     
/*base on oracle directry get file dir*/
       v_sumFilename := 'DAY_END_SUMMARY.' || to_char(sysdate,'DDMMYYYY');
       f_sumHandle     :=UTL_FILE.FOPEN('ALERT_OUT_DIR', v_sumFilename, 'A');
       v_outFilename := 'DAY_END_STN'||STN_NO_INPUT||'.' || to_char(sysdate,'DDMMYYYY');
       f_outHandle     :=UTL_FILE.FOPEN('ALERT_OUT_DIR', v_outFilename, 'A');
      /*write content to file*/  
UTL_FILE.PUT_LINE(f_sumHandle,'');
UTL_FILE.PUT_LINE(f_sumHandle,'Station no. : '||STN_NO_INPUT);
UTL_FILE.PUT_LINE(f_sumHandle,'');
UTL_FILE.PUT_LINE(f_outHandle,'');
UTL_FILE.PUT_LINE(f_outHandle,'Station no. : '||STN_NO_INPUT);
UTL_FILE.PUT_LINE(f_outHandle,'Operating Date: ');
UTL_FILE.PUT_LINE(f_outHandle,'');
open c1;
Loop
fetch c1 INTO V_OPERATING_DATE,V_STN_NO;
exit when c1%NOTFOUND;

STN_MSG:= to_char(V_OPERATING_DATE,'DD-MON-YYYY');
UTL_FILE.PUT_LINE(f_outHandle,STN_MSG);

SUMMARY_MSG:= 'Operating Date: '||to_char(V_OPERATING_DATE,'DD-MON-YYYY');
UTL_FILE.PUT_LINE(f_sumHandle,SUMMARY_MSG);

end LOOP;
close c1;
UTL_FILE.PUT_LINE(f_outHandle,'');
UTL_FILE.PUT_LINE(f_outHandle,'not yet confirm.');
UTL_FILE.PUT_LINE(f_outHandle,'Please confirm immediately.');
UTL_FILE.PUT_LINE(f_outHandle,'');
UTL_FILE.PUT_LINE(f_outHandle,'Thank you for your attention.');
UTL_FILE.PUT_LINE(f_outHandle,'');
UTL_FILE.PUT_LINE(f_outHandle,'Best regards,');
UTL_FILE.PUT_LINE(f_outHandle,'AFCTRM Support Team');
UTL_FILE.FCLOSE(f_outHandle);
UTL_FILE.FCLOSE(f_sumHandle);
END IF;
END pro_inf_output_file_with_param;

create or replace procedure   proc_inc_merage
IS
BEGIN

MERGE into tablea target
USING (SELECT a,b,c,d,e FROM tableb ) source 
    ON (target.stn_no =source.stn_no and target.operating_date=source.op_dt
         and target.mim_tkt_type=source.tkt_type_co 
         and target.mim_tkt_sub_type=source.tkt_subtype_co) 
WHEN MATCHED THEN UPDATE SET target.patronage=source.amount,target.upduser= 'AFCMIM', target.updtime = sysdate where target.patronage<> source.amount 
WHEN NOT MATCHED THEN  INSERT values(source.op_dt,source.stn_no,source.tkt_type_co,source.tkt_subtype_co,source.amount, 'AFCMIM',  sysdate);  


commit;

 EXCEPTION
        WHEN OTHERS THEN rollback;

END proc_inc_merage;

---