[PLSQL] procedure

-함수와 비슷한 구조

-함수는 결과 리턴이 있지만 PROCEDURE는 결과 리턴이 없는 것이 특징(리턴을 위한 별도의 코드(변수)가 있음)

-리턴 키워드 대신, 리턴 역할을 하는 변수를 필요 갯수만큼 만들어서 사용함

​

1. 생성

 CREATE OR REPLACE PROCEDURE 프로시져이름(
      매개변수명1 [IN|OUT|IN OUT] 데이터타입[:=디폴트 값],
      매개변수명2 [IN|OUT|IN OUT] 데이터타입[:=디폴트 값], ...
 )
 IS[AS]
      변수,상수 선언
 BEGIN
      실행부
 [EXCEPTION
      예외처리부]
 END[프로시져이름];

-CREATE OR REPLACE PROCEDURE: 프로시져를 생성하는 구문

-매개변수명1 [IN|OUT|IN OUT]: 매개변수를 만들되 전달되는 전달인수를 받는 IN변수와 리턴역할을 할 수 있는 OUT 변수를 만들 때 사용함. 입력변수와 출력변수의 역할이 동시에 부여되려면 IN OUT을 같이 기술함

-프로시져는 기본적으로 리턴값이 없지만(실제 RETURN 명령을 사용하지 않음) 변수의 속성에 OUT 속성하나를 부여함으로써 리턴의 역할을 흉내낼 수 있게 사용 가능(변수 속성이 IN인 경우 생략 가능)

 

2. 레코드 추가

-- RENTLIST 테이블에 레코드를 추가하는 프로시져
CREATE OR REPLACE procedure newRentList(
    p_bnum IN rentlist.bnum%TYPE,
    p_mnum IN rentlist.mnum%TYPE,
    p_discount IN rentlist.discount%TYPE  )
IS
BEGIN
    insert into rentlist(rentdate, num, bnum, mnum, discount)
    values(sysdate, rent_seq.nextVal, p_bnum, p_mnum, p_discount);
    commit;
END;

 

3. OUT, IN OUT 매개변수 사용

-- newRentList 프로시져에서 
-- 입력된 오늘 날짜를 호출한 곳에서 되돌려 받아서 출력함
CREATE OR REPLACE procedure newRentList(
    p_bnum IN rentlist.bnum%TYPE,
    p_mnum IN rentlist.mnum%TYPE,
    p_discount IN rentlist.discount%TYPE, 
    p_outdate OUT rentlist.rentdate%TYPE)
IS
    v_sysdate rentlist.rentdate%TYPE:=SYSDATE;
BEGIN
    insert into rentlist(rentdate, num, bnum, mnum, discount)
    values(v_sysdate, rent_seq.nextVal, p_bnum, p_mnum, p_discount);
    commit;
    p_outdate:=v_sysdate; 
-- OUT 변수에 오늘 날짜를 담고 있는 변수값을 대입함
END;
-- OUT 변수는 프로시져 내에서 그 변수에 값을 대입하는 동작으로 
-- 프로시져를 호출할 때 지정한 변수까지 값을 리턴하는 효과를 볼 수 있음
DECLARE
    v_curdate rentlist.rentdate%TYPE;
BEGIN
    -- 익명블록에서 프로시져를 호출할 때는 exec를 사용하지 않음
    newrentlist(7,2,200,v_curdate);
    -- 프로시져가 아니고 함수였다면 
    -- v_curdate=newrentlist(7,2,200); 와 같이 사용할테지만
    -- 프로시져이기 때문에 리턴 받을 변수가 
    -- 전달인수 자리에 쓰여진 거라고 보시면 됨

    -- 프로시져의 OUT 변수 자리에는 값을 넣는게 아니라 
    -- OUT 변수를 통해 돌아올 값을 저장할 변수명을 지정함
    -- 프로시져 마지막줄에 있는 p_outdate := v_sysdate; 
    -- 명령에 의해 v_sysdate 값이 p_outdate 변수에 대입되면
    -- 결국 호출 시에 지정한 변수 v_curdate에 
    -- 값이 리턴되어진 것 같이 사용이 가능함
    DBMS_OUTPUT.PUT_LINE(v_curdate);
END;

 

4. IN, OUT, IN OUT 사용

-- IN 변수와 OUT 변수와 IN OUT 변수
CREATE OR REPLACE PROCEDURE parameter_test(
    p_var1 IN VARCHAR2,
    p_var2 OUT VARCHAR2,
    p_var3 IN OUT VARCHAR2)
IS
BEGIN
    DBMS_OUTPUT.PUT_LINE('p_var1 value = ' || p_var1);
    DBMS_OUTPUT.PUT_LINE('p_var2 value = ' || p_var2);
    -- OUT 변수는 전달인수를 전달받지 못하는 변수임. 
    -- 값을 전달해줘도 적용되지 않음
    DBMS_OUTPUT.PUT_LINE('p_var3 value = ' || p_var3);
    -- 다만 IN과 같이 있는 OUT 변수는 전달받아 적용도 가능
    p_var2:='B2';
    p_var3:='C2';
    -- OUT 기능이 부여된 두개의 변수에 같이 넣으면 
    -- 전달인수로서의 변수(익명블럭의 v_var2, v_var3)에 리턴됨
    -- p_var1:='A2'; 
    -- IN 변수는 전달인수에 의해 값이 정해질뿐 임의로 값을 변경하지 못함
END;
DECLARE
    v_var1 VARCHAR2(10):='A';
    v_var2 VARCHAR2(10):='B';
    v_var3 VARCHAR2(10):='C';
BEGIN
    parameter_test(v_var1,v_var2,v_var3);
    -- 프로시져에 OUT 변수로 전달된 변수는 
    -- 프로시져 실행후 프로시져 내에서 넣어준 값이 저장되어 있게 됨
    DBMS_OUTPUT.PUT_LINE('v_var1 value = ' || v_var1);
    DBMS_OUTPUT.PUT_LINE('v_var2 value = ' || v_var2);
    DBMS_OUTPUT.PUT_LINE('v_var3 value = ' || v_var3);
END;

 

5. IN OUT 변수의 사용 규칙

-IN 변수는 전달인수로 전달되어 저장된 값을 참조만 할 수 있고 값을 할당할 수 없음

-OUT 변수에는 전달인수로 값을 전달할 수는 있지만 참조할 수 없으므로 의미가 없는 전달

-OUT 변수와 IN OUT 변수는 디폴트값을 지정할 수 없음

-IN 변수에는 변수, 상수, 각 데이터형에 따른 값을 전달인수로 전달할 수 있지만 OUT 변수와 IN OUT 변수는 반드시 변수 형태로 전달인수를 넣어줘야함

 

6. DEFAULT VALUE

CREATE OR REPLACE procedure newRentList(
    p_bnum IN rentlist.bnum%TYPE,
    p_mnum IN rentlist.mnum%TYPE,
    p_discount IN rentlist.discount%TYPE:=100)
IS
    v_sysdate rentlist.rentdate%TYPE:=SYSDATE;
BEGIN
    insert into rentlist(rentdate, num, bnum, mnum, discount)
    values(v_sysdate, rent_seq.nextVal, p_bnum, p_mnum, p_discount);
    commit;
END;

exec newRentList(10, 10);
-- 매개변수 인수 전달 시, 순서 변경
exec newRentList(p_mnum => 5, p_bnum => 6);

select*from rentlist order by num desc;

 

7. RETURN

-프로시져에서 RETURN 은 값을 리턴하겠다는 명령이 아니고, 현시점에서 프로시져를 끝내겠다는 뜻

-- rentlist 에 레코드를 추가하기 전에 전달된 도서번호와 회원번호가 없다면
-- 해당 도서번호가 없음 또는 해당 회원번호가 없음 이라고 출력하고 
-- 중간에 프로시져가 끝나도록 작성
create or replace procedure newRentList(
    p_bnum IN rentlist.bnum%TYPE,
    p_mnum IN rentlist.mnum%TYPE,
    p_discount IN rentlist.discount%TYPE := 100 )
is
    bcnt Number;
    mcnt Number;
begin
    select count(*) into bcnt from booklist where num = p_bnum;
    if bcnt=0 then
        DBMS_OUTPUT.PUT_LINE('해당 도서번호가 없습니다');
        return;
    end if;
    select count(*) into mcnt from memberlist where num = p_mnum;
    if mcnt=0 then
        DBMS_OUTPUT.PUT_LINE('해당 회원번호가 없습니다');
        return;
    end if;
    insert into rentlist( rentdate, num, bnum, mnum, discount)
    values( sysdate , rent_seq.nextVal, p_bnum, p_mnum, p_discount );
    commit;
end;
​