프로시져 생성 오류가 발생하여 질문드립니다.. 어디가 오류인지 모르겠습니다.. 오류select 해보니,
"PLS-00103: 심볼 "EXCEPTION"를 만났습니다 다음 중 하나가 기대될 때:
( begin case declare else 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
json_exists json_value json_query json_object json_array
"
이렇게 뜹니다..
create or replace procedure test_transfer
(
V_WITHDRAW_CODE CHAR,
V_DEPOSIT_CODE CHAR,
V_AMOUNT NUMBER,
V_WITHDRAW_ACCOUNT VARCHAR2,
V_DEPOSIT_ACCOUNT VARCHAR2,
V_HOLDER VARCHAR2,
V_COUNTERNAME VARCHAR2
)
IS
BEGIN
CASE WHEN V_WITHDRAW_CODE = 'J' THEN
update bank_account set balance = balance - V_AMOUNT where acct_no = V_WITHDRAW_ACCOUNT;
WHEN V_WITHDRAW_CODE = 'S' THEN
update sj_account_info@Sejin_LINK set balance = balance - V_AMOUNT
where id = (select member_id from sj_account_info@Sejin_LINK where account_number = V_WITHDRAW_ACCOUNT );
CASE WHEN V_DEPOSIT_CODE ='J' THEN
update bank_account set balance = balance + V_AMOUNT where acct_no = V_DEPOSIT_ACCOUNT;
WHEN V_DEPOSIT_CODE ='S' THEN
update sj_account_info@Sejin_LINK set balance = balance + V_AMOUNT
where id = (select member_id from sj_account_info@Sejin_LINK where account_number = V_DEPOSIT_ACCOUNT );
CASE WHEN V_WITHDRAW_CODE = 'J' THEN
INSERT INTO BANK_TRANSACTION(TRANSACTION_NO, AMOUNT, COUNTERPART_ACCT_NO, TYPE,
COUNTERPART, COUNTERPART_BANK, ACCT_NO)
VALUES(TRANSACTION_SEQ.NEXTVAL, V_AMOUNT, V_DEPOSIT_ACCOUNT, '출금', V_COUNTERNAME, V_DEPOSIT_CODE, V_WITHDRAW_ACCOUNT);
WHEN V_WITHDRAW_CODE ='S' THEN
insert into sj_account_transfer_info@Sejin_LINK(id, account_number, tran_amt, inout_type)
values('transfer' || sj_seq_transfer_info_id.nextval@Sejin_LINK, V_WITHDRAW_ACCOUNT,
V_AMOUNT, 'O');
CASE WHEN V_DEPOSIT_CODE = 'J' THEN
INSERT INTO BANK_TRANSACTION(TRANSACTION_NO, AMOUNT, COUNTERPART_ACCT_NO, TYPE, COUNTERPART, COUNTERPART_BANK, ACCT_NO)
VALUES(TRANSACTION_SEQ.NEXTVAL, V_AMOUNT, V_WITHDRAW_ACCOUNT, '입금', V_HOLDER, V_WITHDRAW_CODE, V_DEPOSIT_ACCOUNT);
WHEN V_DEPOSIT_CODE ='S' THEN
insert into sj_account_transfer_info@Sejin_LINK(id, account_number, tran_amt, inout_type)
values('transfer' || sj_seq_transfer_info_id.nextval@Sejin_LINK, V_DEPOSIT_ACCOUNT,
V_AMOUNT, 'I');
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
END;
/