데이터베이스 질문과 답변
CREATE OR REPLACE PROCEDURE TEST.P_CALL_ASSIGN
IS
P_RENT_MNG_ID VARCHAR2(10);
P_RENT_L_CD VARCHAR2(20);
P_RENT_DT VARCHAR2(10);
P_RENT_START_DT VARCHAR2(10);
P_GUBUN VARCHAR2(1);
P_CLIENT_ID VARCHAR2(7);
P_R_CNT number(5);
V_MNG_ID varchar2(10);
V_ASS_ID varchar2(10);
V_SEQ number(5);
V_USE varchar2(1);
P_DAY NUMBER(2);
N_SYSDATE VARCHAR2(8);
N_SEQ NUMBER(3);
D_CNT NUMBER(3);
D_CLIENT_ID VARCHAR2(6);
S_CNT number(2);
V_SQLCODE NUMBER;
V_SQLERRM VARCHAR2(255);
P_CLS_DT VARCHAR2(10);
-- cont call 배정
CURSOR CUR_CAR IS
select
a.rent_mng_id, a.rent_l_cd, replace(nvl(b.rent_dt,a.rent_dt),' ','') as rent_dt, b.rent_start_dt,
decode(a.call_st , 'N', '9', cp.gubun ) as gubun , a.client_id
from cont a, fee b, car_reg d, cont_etc ce,
(select a.rent_mng_id, a.rent_l_cd, '1' as gubun from cont_call a where poll_id <> 0 group by a.rent_mng_id, a.rent_l_cd ) cp,
(select a.rent_mng_id, a.rent_l_cd, reg_id from cont_call a where poll_id = 0 group by a.rent_mng_id, a.rent_l_cd , reg_id ) cpr
where nvl(a.use_yn,'Y')='Y' and a.car_st not in ( '2' , '4', '5' )
and a.rent_mng_id=b.rent_mng_id and a.rent_l_cd=b.rent_l_cd
and substr(b.rent_start_dt, 1, 4) =to_char(sysdate,'YYYY') and b.rent_start_dt > '20140131'
and a.car_mng_id=d.car_mng_id(+)
and a.rent_mng_id=cp.rent_mng_id(+) and a.rent_l_cd=cp.rent_l_cd(+)
and a.rent_mng_id=cpr.rent_mng_id(+) and a.rent_l_cd=cpr.rent_l_cd(+)
and a.rent_mng_id=ce.rent_mng_id(+) and a.rent_l_cd=ce.rent_l_cd(+) and ce.rent_suc_dt is null
and b.rent_st = '1' and nvl(a.car_gu,a.reg_id) = '1'
and cpr.reg_id is null
and b.rent_start_dt is not null and decode(a.call_st , 'N', '9', cp.gubun ) is null
order by client_id, decode(a.call_st , 'N', '9', cp.gubun ) desc, b.rent_start_dt, nvl(b.rent_dt,a.rent_dt);
BEGIN
N_SEQ := 0;
D_CLIENT_ID := '';
OPEN CUR_CAR;
LOOP
FETCH CUR_CAR
INTO P_RENT_MNG_ID, P_RENT_L_CD, P_RENT_DT, P_RENT_START_DT, P_GUBUN , P_CLIENT_ID;
EXIT WHEN CUR_CAR%NOTFOUND;
BEGIN
N_SEQ := N_SEQ + 1;
IF ( D_CLIENT_ID = P_CLIENT_ID ) THEN
GOTO GET_DUP_RTN;
END IF;
SELECT a.REG_ID into V_MNG_ID from cont_call a, ( select max(to_char(answer_date, 'yyyymmddhh24miss')||answer) answer from cont_call where poll_id = 0 ) b
where to_char(a.answer_date, 'yyyymmddhh24miss')||a.answer = b.answer;
IF (V_MNG_ID = '000071' ) THEN
V_MNG_ID := '000194';
ELSE
V_MNG_ID := '000071';
END IF;
BEGIN
insert into cont_call (rent_mng_id, rent_l_cd, poll_id, answer, answer_date, reg_id) values
(P_RENT_MNG_ID, P_RENT_L_CD, 0, nvl(ltrim(to_char(N_SEQ, '000')), '001') , sysdate, V_MNG_ID);
END;
D_CLIENT_ID := P_CLIENT_ID;
GOTO GET_NEXT_RTN;
<<GET_DUP_RTN>>
begin
update cont set call_st = 'N' where rent_mng_id = P_RENT_MNG_ID and rent_l_cd = P_RENT_L_CD;
end;
<<GET_NEXT_RTN>>
select to_char(sysdate, 'yyyymmdd') into N_SYSDATE FROM DUAL;
END;
END LOOP;
CLOSE CUR_CAR;
COMMIT;
DBMS_OUTPUT.PUT_LINE( 'cont call');
-- service call 배정 -
DECLARE
S_SEQ NUMBER (3);
CURSOR c4 IS
select distinct s.serv_dt, a.* from (
select /*+ merge(v) */
cp.gubun as gubun ,'카드' p_gubun, a.buy_dt as rent_dt , v.rent_mng_id, v.rent_l_cd,
to_number(i.seq) seq, i.item_code car_mng_id, i.serv_id
from card_doc a, card b, card_doc_item i , cont_n_view v,
(select a.rent_mng_id, a.rent_l_cd, a.car_mng_id, a.serv_id, '1' as gubun from service_call a where a.poll_id <> 0 group by a.rent_mng_id, a.rent_l_cd , a.car_mng_id, a.serv_id ) cp ,
(select a.rent_mng_id, a.rent_l_cd, a.car_mng_id, a.serv_id, a.reg_id from service_call a where a.poll_id = 0 group by a.rent_mng_id, a.rent_l_cd , a.car_mng_id, a.serv_id, a.reg_id ) cpr
where a.cardno=b.cardno
and a.cardno = i.cardno and a.buy_id = i.buy_id
and i.rent_l_cd = cp.rent_l_cd(+) and i.item_code= cp.car_mng_id(+) and i.serv_id = cp.serv_id(+)
and i.rent_l_cd = cpr.rent_l_cd(+) and i.item_code= cpr.car_mng_id(+) and i.serv_id = cpr.serv_id(+)
and cpr.reg_id is null
and a.acct_code = '00005' and a.acct_code_g = '6' and a.buy_dt >= '20100801' and v.car_gu = '1'
and substr( a.buy_dt,1,6)=to_char(sysdate,'YYYYMM')
and i.rent_l_cd = v.rent_l_cd and v.car_st not in ( '2' , '4' , '5')
and v.client_id not in ( '000010' ,'007507' , '002212' ,'013607' , '012743' , '014374' , '005956' , '015854', '023036')
union all
select /*+ no_merge(v) */
cp.gubun as gubun , '현금' p_gubun, b.p_pay_dt as rent_dt , v.rent_mng_id, v.rent_l_cd,
a.i_seq seq, a.p_cd3 car_mng_id, a.p_cd5 serv_id
from pay_item a, pay b, cont_n_view v,
(select a.rent_mng_id, a.rent_l_cd, a.car_mng_id, a.serv_id, '1' as gubun from service_call a where a.poll_id <> 0 group by a.rent_mng_id, a.rent_l_cd , a.car_mng_id, a.serv_id ) cp ,
(select a.rent_mng_id, a.rent_l_cd, a.car_mng_id, a.serv_id, a.reg_id from service_call a where a.poll_id = 0 group by a.rent_mng_id, a.rent_l_cd , a.car_mng_id, a.serv_id, a.reg_id ) cpr
where a.reqseq=b.reqseq
and a.p_cd2 = cp.rent_l_cd(+) and a.p_cd3 = cp.car_mng_id(+) and a.p_cd5 = cp.serv_id(+)
and a.p_cd2 = cpr.rent_l_cd(+) and a.p_cd3 = cpr.car_mng_id(+) and a.p_cd5 = cpr.serv_id(+)
and cpr.reg_id is null
and a.acct_code = '45700' and a.acct_code_g = '6' and b.p_pay_dt >= '20100801' and v.car_gu = '1'
and substr( b.p_pay_dt,1,6)=to_char(sysdate,'YYYYMM')
and a.p_cd2 = v.rent_l_cd and v.car_st not in ( '2' , '4' , '5')
and v.client_id not in ( '000010', '007507' , '002212', '013607' , '012743', '014374' , '005956' , '015854' , '023036')
) a, service s
where a.car_mng_id = s.car_mng_id and a.serv_id = s.serv_id
and substr(s.serv_dt, 1, 6) = substr(a.rent_dt, 1, 6)
and s.serv_st in ( '1', '2', '3')
order by 2 desc, 3 desc , 4 desc ;
BEGIN
S_SEQ := 0;
FOR c4_r IN c4 LOOP
S_SEQ := S_SEQ + 1;
-- DBMS_OUTPUT.PUT_LINE( '0_' || N_SEQ);
-- 000071, 000194 - 5월20일부터 이원화
-- service call 중복
select count(*) into s_cnt from service_call
where rent_mng_id = c4_r.rent_mng_id and rent_l_cd = c4_r.rent_l_cd and car_mng_id = c4_r.car_mng_id and serv_id = c4_r.serv_id;
if ( s_cnt < 1) then
SELECT a.REG_ID into V_MNG_ID from service_call a, ( select max(to_char(answer_date, 'yyyymmddhh24miss')||answer) answer from service_call where poll_id = 0 ) b
where to_char(a.answer_date, 'yyyymmddhh24miss')||a.answer = b.answer;
IF (V_MNG_ID = '000071' ) THEN
V_MNG_ID := '000194';
ELSE
V_MNG_ID := '000071';
END IF;
BEGIN
insert into service_call (rent_mng_id, rent_l_cd, car_mng_id, serv_id, poll_id, answer, answer_date, reg_id) values
(c4_r.rent_mng_id, c4_r.rent_l_cd, c4_r.car_mng_id, c4_r.serv_id, 0, nvl(ltrim(to_char(S_SEQ, '000')), '001') , sysdate, V_MNG_ID);
-- DBMS_OUTPUT.PUT_LINE( '1_' || c4_r.rent_l_cd);
END;
end if;
END LOOP;
END;
COMMIT;
DBMS_OUTPUT.PUT_LINE( 'service call');
>>>>>>>>>>> 여기에 추가 하고 싶어요 <<<<<<<<<<<<<<<<<<<<<<
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
-- R_CHK := '1';
dbms_output.put_line(' SQLERRM' ) ;
V_SQLCODE := SQLCODE;
V_SQLERRM := V_SQLERRM || SUBSTR(SQLERRM, 1, 250);
RAISE_APPLICATION_ERROR(-20001, '콜 계약 배정 에러!!' || P_RENT_L_CD );
BEGIN
INSERT INTO STAT_BATCH_ERROR (save_dt, procedure_nm, SQLERRM, sqlcode) VALUES (SYSDATE, 'P_CALL_ASSIGN', V_SQLERRM, TO_NUMBER(V_SQLCODE));
END;
END;
이게 원래 있던 프로시져인데요 아래 내용을 추가해서 하나로 만들고 싶습니다.
CREATE OR REPLACE PROCEDURE TEST.P_CALL_ASSIGN_RM IS P_RENT_MNG_ID VARCHAR2 (10); P_RENT_L_CD VARCHAR2 (20); P_CLS_DT VARCHAR2 (10); P_RENT_START_DT VARCHAR2 (10); P_GUBUN VARCHAR2 (1); P_CLIENT_ID VARCHAR2 (7); P_R_CNT number(5); V_MNG_ID varchar2 (10); V_ASS_ID varchar2 (10); V_SEQ number(5); V_USE varchar2(1); P_DAY NUMBER (2); N_SYSDATE VARCHAR2(8); N_SEQ NUMBER (3); D_CNT NUMBER (3); D_CLIENT_ID VARCHAR2(6); S_CNT number(2); V_SQLCODE NUMBER; V_SQLERRM VARCHAR2(255); -- cont call 배정 CURSOR CUR_CAR IS SELECT a.rent_mng_id, a.rent_l_cd, b.cls_dt, a.rent_start_dt, decode(a.call_st , 'N', '9', cp.gubun ) AS gubun, a.client_id FROM cont a, cls_etc b, client c, users i, users df, car_reg c, (SELECT * FROM DOC_SETTLE WHERE doc_st='11') l , (SELECT a.rent_mng_id, a.rent_l_cd, '1' AS gubun FROM cont_call a WHERE poll_id <> 0 GROUP BY a.rent_mng_id, a.rent_l_cd) cp, (SELECT a.rent_mng_id, a.rent_l_cd, reg_id, answer_date FROM cont_call a WHERE poll_id = 0) cpr WHERE a.car_st<>'2' AND a.client_id=c.client_id AND a.rent_mng_id=b.RENT_MNG_ID AND a.rent_l_cd=b.rent_l_cd AND a.car_mng_id = c.car_mng_id(+) AND a.rent_l_cd=l.doc_id(+) AND l.user_id1=i.user_id (+) AND b.dft_saction_id=df.user_id(+) AND a.rent_mng_id=cp.rent_mng_id(+) AND a.rent_l_cd=cp.rent_l_cd(+) AND a.rent_mng_id=cpr.rent_mng_id(+) AND a.rent_l_cd=cpr.rent_l_cd(+) AND substr(b.cls_dt, 1, 4) =to_char(sysdate,'YYYY') AND b.cls_dt > '20140131' AND b.cls_st = '14' order by client_id, decode(a.call_st , 'N', '9', cp.gubun ) desc, b.CLS_DT; BEGIN N_SEQ := 0; D_CLIENT_ID := ''; OPEN CUR_CAR; LOOP FETCH CUR_CAR INTO P_RENT_MNG_ID, P_RENT_L_CD, P_CLS_DT, P_RENT_START_DT, P_GUBUN , P_CLIENT_ID; EXIT WHEN CUR_CAR%NOTFOUND; BEGIN N_SEQ := N_SEQ + 1; IF ( D_CLIENT_ID = P_CLIENT_ID ) THEN GOTO GET_DUP_RTN; END IF; SELECT a.REG_ID into V_MNG_ID from cont_call a, ( select max(to_char(answer_date, 'yyyymmddhh24miss')||answer) answer from cont_call where poll_id = 0 ) b where to_char(a.answer_date, 'yyyymmddhh24miss')||a.answer = b.answer; IF (V_MNG_ID = '000071' ) THEN V_MNG_ID := '000194'; ELSE V_MNG_ID := '000071'; END IF; BEGIN insert into cont_call (rent_mng_id, rent_l_cd, poll_id, answer, answer_date, reg_id) values (P_RENT_MNG_ID, P_RENT_L_CD, 0, nvl(ltrim(to_char(N_SEQ, '000')), '001') , sysdate, V_MNG_ID); -- DBMS_OUTPUT.PUT_LINE( '1_' || V_MNG_ID); END; D_CLIENT_ID := P_CLIENT_ID; GOTO GET_NEXT_RTN; <<GET_DUP_RTN>> begin update cont set call_st = 'N' where rent_mng_id = P_RENT_MNG_ID and rent_l_cd = P_RENT_L_CD; end; <<GET_NEXT_RTN>> select to_char(sysdate, 'yyyymmdd') into N_SYSDATE FROM DUAL; END; END LOOP; CLOSE CUR_CAR; COMMIT; DBMS_OUTPUT.PUT_LINE( 'cont_rm call'); EXCEPTION WHEN OTHERS THEN ROLLBACK; -- R_CHK := '1'; dbms_output.put_line( SQLERRM ) ; V_SQLCODE := SQLCODE; V_SQLERRM := V_SQLERRM || SUBSTR(SQLERRM, 1, 250); RAISE_APPLICATION_ERROR(-20001, '콜 계약 배정 에러!!' || P_RENT_L_CD ); BEGIN INSERT INTO STAT_BATCH_ERROR (save_dt, procedure_nm, SQLERRM, sqlcode) VALUES (SYSDATE, 'P_CALL_ASSIGN', V_SQLERRM, TO_NUMBER(V_SQLCODE)); END; END;
하나로 합쳐 볼려고 했는데 계속 에러가 발생을 해서
오류(363): PLS-00103: 심볼 "end-of-file"를 만났습니다 다음 중 하나가 기대될 때: (
begin case declare end exit for goto if loop mod null pragma raise return select update when while with <식별자> <큰 따옴표로 구분된 식별자> <바인드 변수> << continue close current delete fetch lock insert open rollback savepoint set sql execute commit forall merge pipe purge
세미콜론, 주석 등등 다 찾아서 없애보기도 했는데 계속 납니다. 그래서 비슷한 내용이라 따로 만들었는데 에러없이 프로시저 생성이 됩니다.
도움 부탁드립니다.
by 마농
[2016.12.21 18:26:03]
PL/SQL 블럭 단위로 작성하시면 됩니다.
PL/SQL 블럭은 [DECLARE ~ BEGIN ~ EXCEPTION ~ END] 로 이루어 집니다.
BEGIN, END 는 필수이고, DECLARE, EXCEPTION 은 선택 사항이구요.
하나의 블럭은 하나의 다른 블럭을 포함할 수 있습니다.
( 메인 블럭 : (서브블럭) (서브블럭) (서브블럭) ) 형태로 작성하시면 됩니다.
by 보일듯말듯
[2016.12.22 10:54:09]
감사하빈다. 달아주신 댓글 보고 하니 일단 에러없는 프로시저 생성이 되었습니다. 감사합니다.
) 버튼을 클릭하여 작성 하시면 됩니다.