프로시저요.. 0 3 1,875

by 리틀맹 [2009.01.20 14:06:19]


또 왔습니다..^^;

프로시저를 실행시키는데...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;
/

by 웅 [2009.01.20 15:42:35]
EXECUTE IMMEDIATE magamcre ;
요게 빠진것 같네요..

by 마농 [2009.01.20 16:00:53]
1. 실행 구문이 빠진듯 합니다.
DBMS_SQL.EXECUTE(cursor1);
2. 어제날짜 구하기 틀렸습니다.
to_char(sysdate,'RRRRMMDD') -1 ===> to_char(sysdate - 1,'RRRRMMDD')

by 리틀맹 [2009.01.21 09:33:27]
웅님 마농님 감사합니다..^^
댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입