proc_batch_t

create or replace procedure proc_batch_t IS
--V_LOG ETL_PROCLOG%ROWTYPE;
P_I_DATE varchar2(8);
procname varchar2(100);
v_sqlcount number(9);
step_num number(2);
detailtime varchar(14);
errno number(9);
errmsg varchar(200);
errlevel char(1);
P_O_CODE varchar2(2);
BEGIN
V_SQLCOUNT := 0;
PROCNAME := '';
P_O_CODE := '0';
P_I_DATE := '1998';
STEP_NUM := 1;
DETAILTIME := TO_CHAR(SYSDATE, 'YYYYMMDDHH24MISS');
DELETE FROM EMP_MIDDLE WHERE CJRQ = P_I_DATE;
V_SQLCOUNT := SQL%ROWCOUNT;
COMMIT;
INSERT INTO ETL_PROCLOG
VALUES
(P_I_DATE,
PROCNAME,
V_SQLCOUNT,
STEP_NUM,
DETAILTIME,
'0',
NULL,
NULL,
NULL);
COMMIT;

STEP_NUM := 2;
DETAILTIME := TO_CHAR(SYSDATE, 'YYYYMMDDHH24MISS');
INSERT INTO emp_middle
(empno, ename, job, mgr, hiredate, sal, comm, deptno, CJRQ)
SELECT empno,
ename,
job,
mgr,
hiredate,
sal,
comm,
deptno,
TO_CHAR(SYSDATE, 'HH24MISS')
FROM EMP
WHERE 1 = 1;
V_SQLCOUNT := SQL%ROWCOUNT;
COMMIT;
INSERT INTO ETL_PROCLOG
VALUES
(P_I_DATE,
PROCNAME,
V_SQLCOUNT,
STEP_NUM,
DETAILTIME,
'0',
NULL,
NULL,
NULL);
COMMIT;
EXCEPTION
WHEN OTHERS THEN
P_O_CODE := '1';
ERRNO := SQLCODE;
ERRMSG := SUBSTR(SQLERRM, 1, 200);
ERRLEVEL := '2';
ROLLBACK;
INSERT INTO ETL_PROCLOG
VALUES
(P_I_DATE,
PROCNAME,
V_SQLCOUNT,
STEP_NUM,
DETAILTIME,
P_O_CODE,
ERRNO,
ERRMSG,
ERRLEVEL);
COMMIT;
END;