또 왔습니다..^^;
프로시저를 실행시키는데...insert 문이 실행이 안되서요..
어제날짜의 테이블은 생성되는데 insert 문을 안타는건지 데이터가 생성이 안되네요..
제가 날마다 어제날짜의 일일 테이블을 생성해서 데이터 생성까지 해야하거든요..
그냥 insert ~ select 문을 돌려보면 잘 됩니다.. 에러가 나오는것도 아니고..답답하네요..ㅠ
처음 프로시저 만들어본거라서... 보시고 잘못된 문법이나 오류사항있으면 좀 알려주세요...
감사합니다..
CREATE OR REPLACE PROCEDURE magamdb
IS
cursor1 INTEGER;
dbdate VARCHAR2(10);
magamcre VARCHAR2(1000);
magamins VARCHAR2(3000);
BEGIN
-- 어제날짜를 얻어옴
SELECT to_char(sysdate,’RRRRMMDD’) -1
INTO dbdate
FROM dual;
-- CREATE TABLE명령어 생성
magamcre := ’CREATE TABLE TS_MAGAM_DAILY_’ || dbdate || ’ (SERVICE_CODE CHAR(2)
NOT NULL,RCV_SEQ CHAR(14) NOT NULL,RCV_COUNT INTEGER NOT NULL,CARID
INTEGER,ALLOCAR_CARNUMBER CHAR(8),CARNUMBER_HAN VARCHAR2(14),ALLOCAR_DATE
DATE,ALLOCAR_AGENT VARCHAR2(30),ACPT_CELLPHONE VARCHAR2(14),OPEN_DATE VARCHAR2
(8),CLOSE_DATE VARCHAR2(8),CUSTBUB_CODE CHAR(6),RCV_DATE CHAR(8),RCV_DATETIME
DATE,RCV_AGENT VARCHAR2(30),CANCEL_DATE DATE,GC_DATE DATE,GC_AGENT VARCHAR2
(30),GDC_DATE DATE,FAIL_DATE DATE,FAIL_AGENT VARCHAR2(30),GC_FAILCODE CHAR
(2),GC_FAILDATETIME DATE,GC_FAIL_AGENT VARCHAR2(30),STATUS CHAR(2),RCV_KIND CHAR
(3),PCG_PHONE VARCHAR2(14),DONG_NAME VARCHAR2(50),DESTI_MEMO VARCHAR2(100),ALLOCAR_FAIL
INTEGER,ALLOCAR_SUCC INTEGER,GETCAR INTEGER,GETDOWNCAR INTEGER,PCG_CANCEL
INTEGER,CUST_CANCEL INTEGER,NORMAL_YN INTEGER,CHEUYAK_AM_YN INTEGER,CHEUYAK_PM_YN
INTEGER,DARKNIGHT_YN INTEGER)’;
CREATE TABLE명령어 화면에 출력
DBMS_OUTPUT.PUT_LINE(magamcre);
cursor1 := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(cursor1, magamcre, dbms_sql.v7);
DBMS_SQL.CLOSE_CURSOR(cursor1);
-- 전날 마감데이터 insert
magamins := ’INSERT INTO TS_MAGAM_DAILY_’ || dbdate ||
’ (rcv_seq,rcv_count,service_code,rcv_date,rcv_datetime,rcv_agent,status,rcv_kind,pcg_phone,desti_memo,
carid,allocar_carnumber,carnumber_han,allocar_date,allocar_agent,acpt_cellphone,fail_date,fail_agent,
cancel_date,gc_failcode, gc_faildatetime,gc_fail_agent,gc_date, gc_agent,dong_name, gdc_date,custbub_code,
open_date, close_date) SELECT a.rcv_seq, a.rcv_count, a.service_code, a.rcv_date, a.rcv_datetime,a.rcv_agent,
a.status, a.rcv_kind, a.pcg_phone, a.desti_memo,i.carid, c.allocar_carnumber, i.carnumber_han,c.allocar_date,
c.allocar_agent, c.acpt_cellphone,d.fail_date, d.fail_agent, e.cancel_date,f.gc_failcode, f.gc_faildatetime,
f.gc_fail_agent,g.gc_date, g.gc_agent,g.dong_name, h.gdc_date,i.custbub_code, i.open_date, i.close_date
FROM ts_receive_his a, (SELECT rcv_seq, MAX (rcv_count) rcv_count FROM ts_receive_his WHERE
service_code = 61 AND rcv_date =’|| dbdate ||’ GROUP BY rcv_seq) b, ts_allocar_his c, ts_allocar_fail_his d,
ts_cancel_his e, ts_getcar_fail_his f, ts_getcar_his g, ts_getdowncar_his h, ts_car i WHERE a.rcv_seq =
b.rcv_seq AND a.rcv_count = b.rcv_count AND a.rcv_seq = c.rcv_seq(+) AND a.rcv_count = c.rcv_count(+) AND
a.rcv_seq = d.rcv_seq(+) AND a.rcv_count = d.rcv_count(+) AND a.rcv_seq = e.rcv_seq(+) AND a.rcv_count =
e.rcv_count(+) AND a.rcv_seq = f.rcv_seq(+) AND a.rcv_count = f.rcv_count(+) AND a.rcv_seq = g.rcv_seq(+) AND
a.rcv_seq = h.rcv_seq(+) AND c.allocar_carnumber = i.carnumber(+)’;
DBMS_OUTPUT.PUT_LINE(magamins);
cursor1 := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(cursor1, magamins, dbms_sql.v7);
DBMS_SQL.CLOSE_CURSOR(cursor1);
END;
/