데이터베이스 질문과 답변
CREATE OR REPLACE PROCEDURE INSERT_INOUTITEM_PROC ( p_item_key VARCHAR2
,p_inout_flag VARCHAR2
,p_inout_date VARCHAR2
,p_ware_cd VARCHAR2
,p_inout_num VARCHAR2
) IS
BEGIN
IF EXISTS (SELECT 'X' FROM STOCK WHERE ITEMKEY = p_item_key AND WARECD = p_ware_cd) THEN
insert into INOUTITEM values(TO_CHAR(SYSDATE, 'YYMMDD')||LPAD(TO_CHAR(inout_key.NEXTVAL),8,'0')
,p_item_key, p_inout_flag, p_inout_date, p_ware_cd, p_inout_num);
IF p_inout_flag = 1 THEN --1 입고
update stock set stocknum = (select stocknum from stock where itemkey = p_item_key
and warecd = P_ware_cd)+p_inout_num
where itemkey = p_item_key and warecd = p_ware_Cd;
ELSIF p_inout_flag = 2 THEN --2 출고
update stock set stocknum = (select stocknum from stock where itemkey = p_item_key
and warecd = P_ware_cd)-p_inout_num
where itemkey = p_item_key and warecd = p_ware_cd;
END IF;
ELSIF NOT EXISTS (SELECT 'X' FROM STOCK WHERE ITEMKEY = p_item_key AND WARECD = p_ware_cd) THEN
insert into INOUTITEM values(TO_CHAR(SYSDATE, 'YYMMDD')||LPAD(TO_CHAR(inout_key.NEXTVAL),8,'0')
,p_item_key, p_inout_flag, p_inout_date, p_ware_cd, p_inout_num);
insert into stock values (p_item_key, p_ware_cd, p_inout_num);
END IF;
EXCEPTION
WHEN OTHERS THEN
sys.dbms_output.put_line(SQLERRM);
raise;
END INSERT_INOUTITEM_PROC;
MSSQLSERVER에서는 if문에 바로 exists가 먹혔었는데 오라클에서는PLS-00240 애러가 발생해 구글링해보니 오라클의경우 커서로 처리해야한다는데 커서가 뭔지 어떻게 처리해야할지 모르겠습니다
커서 개념과 사용법좀 알려주세요 ㅠㅠ
by 오상민
[2018.07.18 22:22:57]
CREATE OR REPLACE PROCEDURE INSERT_INOUTITEM_PROC ( p_item_key VARCHAR2
,p_inout_flag VARCHAR2
,p_inout_date VARCHAR2
,p_ware_cd VARCHAR2
,p_inout_num number
) IS
v_count number;
BEGIN
SELECT COUNT(*) INTO v_count
FROM STOCK WHERE ITEMKEY = p_item_key AND WARECD = p_ware_cd;
IF v_count > 0 THEN
insert into INOUTITEM values(TO_CHAR(SYSDATE, 'YYMMDD')||LPAD(TO_CHAR(inout_key.NEXTVAL),8,'0')
,p_item_key, p_inout_flag, p_inout_date, p_ware_cd, p_inout_num);
IF p_inout_flag = 1 THEN --1 입고
update stock set stocknum = (select stocknum from stock where itemkey = p_item_key
and warecd = P_ware_cd)+p_inout_num
where itemkey = p_item_key and warecd = p_ware_Cd;
ELSIF p_inout_flag = 2 THEN --2 출고
update stock set stocknum = (select stocknum from stock where itemkey = p_item_key
and warecd = P_ware_cd)-p_inout_num
where itemkey = p_item_key and warecd = p_ware_cd;
END IF;
ELSE
insert into INOUTITEM values(TO_CHAR(SYSDATE, 'YYMMDD')||LPAD(TO_CHAR(inout_key.NEXTVAL),8,'0')
,p_item_key, p_inout_flag, p_inout_date, p_ware_cd, p_inout_num);
insert into stock values (p_item_key, p_ware_cd, p_inout_num);
END IF;
EXCEPTION
WHEN OTHERS THEN
sys.dbms_output.put_line(SQLERRM);
raise;
END INSERT_INOUTITEM_PROC;
자문자답입니다 구루비 검색하다 위와 같이 처리하는 방법을 찾았네요 ^^;
by 마농
[2018.07.23 10:06:55]
조회 조건이
1. 유니크한 조건이라면?
- 카운트해도 1건만 읽게 되겟지만
2. 중복이 많은 조건이라면?
- Count 는 Exists 와 달리 여러행을 읽게 됩니다.
- 1건만 읽고 종료되도록 ROWNUM = 1 조건을 추가해 주세요.
by 오상민
[2018.07.23 11:15:42]
답변 감사합니다 ^^
) 버튼을 클릭하여 작성 하시면 됩니다.