Oracle APEX에서 PURCHASING 품목을 입력하면 자동으로 INVENTORIES 해당 ITEM의 INVENTORY에 추가되는 기능을 넣으려면 어떤 SQL과 Function을 쓰면 좋을까요? 만일 해당아이템이 INVENTORY에 존재하지 않으면 아이템을 추가로 생성하고 재고를 추가합니다. (INVENTORIES Table은 BARCODE,INVENTORY의 컬럼으로 구성되어 있습니다.)
Oracle APEX에서 PURCHASING 품목 입력 시 INVENTORIES 테이블에 자동으로 반영되는 기능을 구현하려면 트리거(TRIGGER)와 프로시저(PROCEDURE)를 활용하는 것이 효과적입니다. 아래는 요구사항을 충족하는 SQL과 함수의 예시입니다.
요구사항 분석
PURCHASING 테이블에 새 품목이 추가될 때:
INVENTORIES 테이블에서 해당 품목(BARCODE)을 확인.
존재하면 해당 품목의 INVENTORY를 증가.
존재하지 않으면 새 품목을 추가하고 초기 재고를 설정.
INVENTORIES 테이블 구조:
BARCODE (품목 식별자)
INVENTORY (재고 수량)
구현 방법
1. 트리거 생성
PURCHASING 테이블에 데이터가 삽입될 때마다 자동으로 INVENTORIES를 업데이트하는 트리거를 만듭니다.
sql
CREATE OR REPLACE TRIGGER trg_purchasing_to_inventory
AFTER INSERT ON PURCHASING
FOR EACH ROW
DECLARE
v_count NUMBER;
BEGIN
-- INVENTORIES 테이블에서 해당 BARCODE가 존재하는지 확인
SELECT COUNT(*)
INTO v_count
FROM INVENTORIES
WHERE BARCODE = :NEW.BARCODE;
IF v_count > 0 THEN
-- 이미 존재하면 INVENTORY 수량 증가
UPDATE INVENTORIES
SET INVENTORY = INVENTORY + :NEW.QUANTITY
WHERE BARCODE = :NEW.BARCODE;
ELSE
-- 존재하지 않으면 새 레코드 삽입
INSERT INTO INVENTORIES (BARCODE, INVENTORY)
VALUES (:NEW.BARCODE, :NEW.QUANTITY);
END IF;
END;
/
설명:
:NEW.BARCODE: PURCHASING 테이블에 삽입된 품목의 바코드.
:NEW.QUANTITY: PURCHASING 테이블에서 입력된 구매 수량(필요에 따라 컬럼명 조정).
트리거는 INSERT 후에 실행되며, INVENTORIES를 자동으로 업데이트.
2. 프로시저 생성 (필요 시 수동 실행용)
트리거 대신 APEX 페이지에서 버튼 클릭 등으로 실행하고 싶다면 프로시저를 사용할 수 있습니다.
sql
CREATE OR REPLACE PROCEDURE update_inventory(p_barcode IN VARCHAR2, p_quantity IN NUMBER)
IS
v_count NUMBER;
BEGIN
-- INVENTORIES에서 BARCODE 확인
SELECT COUNT(*)
INTO v_count
FROM INVENTORIES
WHERE BARCODE = p_barcode;
IF v_count > 0 THEN
-- 존재하면 재고 증가
UPDATE INVENTORIES
SET INVENTORY = INVENTORY + p_quantity
WHERE BARCODE = p_barcode;
ELSE
-- 없으면 새로 추가
INSERT INTO INVENTORIES (BARCODE, INVENTORY)
VALUES (p_barcode, p_quantity);
END IF;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
RAISE_APPLICATION_ERROR(-20001, 'Inventory update failed: ' || SQLERRM);
END update_inventory;
/
사용법:
APEX에서 프로시저 호출: update_inventory(:P1_BARCODE, :P1_QUANTITY);
:P1_BARCODE, :P1_QUANTITY는 APEX 페이지의 입력 항목.
3. APEX에서 적용
트리거 사용 시: PURCHASING 테이블에 데이터를 삽입하면 자동 실행되므로 별도 작업 불필요.
프로시저 사용 시:
APEX 페이지에 "Submit" 버튼 추가.
버튼 클릭 시 Dynamic Action 또는 Process를 만들어 프로시저 호출.
예: Process에서 PL/SQL 코드로 update_inventory(:P1_BARCODE, :P1_QUANTITY); 실행.
추가 고려 사항
컬럼 매핑 확인:
PURCHASING 테이블에 BARCODE와 QUANTITY 컬럼이 있다고 가정했습니다. 실제 컬럼명에 따라 :NEW.BARCODE, :NEW.QUANTITY를 조정하세요.
트랜잭션 관리:
트리거에서는 COMMIT/ROLLBACK을 직접 사용하지 않으므로, 오류 처리를 위해 EXCEPTION 블록을 추가할 수 있습니다.
성능 최적화:
INVENTORIES 테이블의 BARCODE 컬럼에 인덱스를 생성하면 검색 속도가 향상됩니다:
sql
CREATE INDEX idx_inventories_barcode ON INVENTORIES(BARCODE);
테스트 예시
sql
-- PURCHASING 테이블에 데이터 삽입
INSERT INTO PURCHASING (BARCODE, QUANTITY) VALUES ('12345', 10);
-- INVENTORIES 확인
SELECT * FROM INVENTORIES WHERE BARCODE = '12345';
-- 결과: BARCODE = '12345', INVENTORY = 10
-- 동일 품목 추가 구매
INSERT INTO PURCHASING (BARCODE, QUANTITY) VALUES ('12345', 5);
-- INVENTORIES 확인
SELECT * FROM INVENTORIES WHERE BARCODE = '12345';
-- 결과: BARCODE = '12345', INVENTORY = 15
