최프 단지 생성 예제

CREATE OR REPLACE PROCEDURE p_house(
    p_aptcode apart.apt_code%type
    , p_dong house.dong%type
    , p_floor house.ho%type
    , p_ho house.ho%type
    , p_area HOUSE.HOUSE_AREA%type
)
is
    val number;
    v_cnt number := 1;
begin
    loop
        select count(*)
            into val
        from house
        where instr(house_code ,p_aptcode||'D'||LPAD(p_dong,4,'0')||'H'||to_char(v_cnt,'FM00')||'0'||p_ho) > 0;
        if val = 0 then
            insert into house(
                house_code,
                dong,
                ho,
                move_yn,
                apt_code,
                house_area
            )
            values (
                p_aptcode||'D'||LPAD(p_dong,4,'0')||'H'||to_char(v_cnt,'FM00')||'0'||p_ho
                , p_dong
                , to_char(v_cnt,'FM00')||'0'||p_ho
                , 'N'
                , P_APTCODE
                , p_area
            );
        end if;
        exit when v_cnt = TO_NUMBER(p_floor);
        v_cnt := v_cnt+1;
    end loop;
end;

exec P_HOUSE('A0001','1406','15','1','87');

변경 예제 반환값이 없어서 OUT 매개 변수 추가

CREATE OR REPLACE PROCEDURE p_house(
    p_aptcode in apart.apt_code%type
    , p_dong in house.dong%type
    , p_floor in house.ho%type
    , p_ho in house.ho%type
    , p_area in HOUSE.HOUSE_AREA%type
    , p_result out number
)
is
    val number;
    v_cnt number := 1;
    out_cnt number :=0;
begin
    loop
        select count(*)
            into val
        from house
        where instr(house_code ,p_aptcode||'D'||LPAD(p_dong,4,'0')||'H'||to_char(v_cnt,'FM00')||'0'||p_ho) > 0;
        if val = 0 then
            insert into house(
                house_code,
                dong,
                ho,
                move_yn,
                apt_code,
                house_area
            )
            values (
                p_aptcode||'D'||LPAD(p_dong,4,'0')||'H'||to_char(v_cnt,'FM00')||'0'||p_ho
                , lpad(p_dong,4,'0')
                , to_char(v_cnt,'FM00')||'0'||p_ho
                , 'N'
                , P_APTCODE
                , p_area
            );
            out_cnt := out_cnt+1;
        end if;
        exit when v_cnt = TO_NUMBER(p_floor);
        v_cnt := v_cnt+1;
    end loop;
    p_result := out_cnt;
end;

var rcnt number;
exec p_house('A0002','101','4','2','33', :rcnt);
print rcnt;

---------------------------------------------

CREATE OR REPLACE PROCEDURE p_house(
    p_aptcode in apart.apt_code%type
    , p_dong in house.dong%type
    , p_floor in house.ho%type
    , p_ho in house.ho%type
    , p_area in HOUSE.HOUSE_AREA%type
)
is
    val number;
    v_cnt number := 1;
begin
    loop
        select count(*)
            into val
        from house
        where instr(house_code ,p_aptcode||'D'||LPAD(p_dong,4,'0')||'H'||to_char(v_cnt,'00')||'0'||p_ho) > 0;
        if val = 0 then
            insert into house(
                house_code,
                dong,
                ho,
                move_yn,
                apt_code,
                house_area
            )
            values (
                p_aptcode||'D'||LPAD(p_dong,4,'0')||'H'||to_char(v_cnt,'00')||'0'||p_ho
                , lpad(p_dong,4,'0')
                , to_char(v_cnt,'00')||'0'||p_ho
                , 'N'
                , P_APTCODE
                , p_area
            );
        end if;
        exit when v_cnt = TO_NUMBER(p_floor);
        v_cnt := v_cnt+1;
    end loop;
end;

exec p_house('A0002','101','4','2','33')

구매/사용 내역 관련 프로시저

create or replace PROCEDURE P_PRODDETAIL(
    p_prodId in PRODDETAIL.PROD_ID%type
    , p_prodIoDate in PRODDETAIL.PROD_IO_DATE%type
    , p_prodIoQty in PRODDETAIL.PROD_IO_QTY%type
    , p_prodIo in PRODDETAIL.PROD_IO%type
)
is
begin
    INSERT INTO PRODDETAIL (
        PROD_ID,
        PROD_IO_DATE,
        PROD_IO_QTY,
        PROD_IO,
        IO_NO
    ) VALUES (
        p_prodId,
        p_prodIoDate,
        p_prodIoQty,
        p_prodIo,
        (SELECT MAX(IO_NO)+1 FROM PRODDETAIL)
    );
    IF p_prodIo = 'USE' THEN 
        UPDATE PROD
        SET PROD_QTY = PROD_QTY - p_prodIoQty
        WHERE PROD_ID = p_prodId;
    ELSE
        UPDATE PROD
        SET PROD_QTY = PROD_QTY + p_prodIoQty
        WHERE PROD_ID = p_prodId;
    END IF;

end;

입력하면 구매/사용내역에 등록하고, 해당 물품의 수량을 변경한다.