■ 프로시저

프로시저 : 자주사용하는 SQL문작성 -> 필요시 호출 -> 반환값이 없음.
특정한 로직을 처리하기만 하고 결과 값을 반환하지 않는 서브프로그램입니다.

■ 프로시저 구조

CREATE OR REPLACE PROCEDURE proc_if_exam(
    int_num IN NUMBER -- 전달되는 파라미터
)
IS
    tmp_grade VARCHAR2(2); -- 등급보관용 문자변수
BEGIN
    IF int_num >= 90 THEN
        tmp_grade := 'A';
    ELSIF int_num >= 80 THEN
        tmp_grade := 'B';
    ELSE
        tmp_grade := 'c';
    END IF;
    DBMS_OUTPUT.PUT_LINE('등급은' || tmp_grade || '입니다.');
END;

숫자를 전달받으면 등급을 구해주는 프로시저

01. 프로시저 생성

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

CREATE OR REPLACE PROCEDURE proc_if_exam(
    int_num IN NUMBER -- 전달되는 파라미터
)
IS
    tmp_grade VARCHAR2(2); -- 등급보관용 문자변수
BEGIN
    IF int_num >= 90 THEN
        tmp_grade := 'A';
    ELSIF int_num >= 80 THEN
        tmp_grade := 'B';
    ELSE
        tmp_grade := 'c';
    END IF;
    DBMS_OUTPUT.PUT_LINE('등급은' || tmp_grade || '입니다.');
END;

02. 프로시저 실행

프로시저는 반환 값이 없으므로 함수처럼 SELECT절에는 사용할 수 없다.

EXEC 혹은 EXECUTE 프로시저명(매개변수1 값, 매개변수2 값, ...);

EXEC proc_if_exam(96);

03. OUT 매개변수

OUT 매개변수는 프로시저 실행 시점에 OUT 매개변수를 변수 형태로 전달하고, 프로시저 실행부에서 이 매개변수에 특정 값을 가져올 수 있는 것이다. 그리고 나서 실행이 끝나면 전달한 변수를 참조해 값을 가져올 수 있는 것이다. OUT매개변수는 반드시 OUT키워드 명시해야한다.

CREATE OR REPLACE PROCEDURE proc_member_insert(
    in_userid IN MEMBER.USERID%TYPE,
    in_userpw IN MEMBER.USERPW%TYPE,
    in_username IN MEMBER.USERNAME%TYPE,
    in_userage IN MEMBER.USERAGE%TYPE,
    in_userphone IN MEMBER.USERPHONE%TYPE,
    in_usergender IN MEMBER.USERGENDER%TYPE,
    out_ret OUT NUMBER 
)
IS
BEGIN
    INSERT INTO MEMBER(userid, userpw, 
    username, userage, 
    userphone, usergender, 
    userdate)
    VALUES (in_userid, in_userpw, 
    in_username, in_userage, in_userphone, 
    in_usergender, CURRENT_DATE);
    COMMIT;
    out_ret := 1;   
EXCEPTION WHEN OTHERS THEN
    ROLLBACK;
    out_ret := 0;
END;

프로시저 테스트

DECLARE
    out_ret NUMBER(1):= -1;
BEGIN
    -- 생성한 프로시저 호출
    proc_member_insert('d12345','p','가나다',23,'010-1111', 'F', out_ret);
    DBMS_OUTPUT.PUT_LINE('결과갑은 =>' || out_ret);
END;

◆ 문제2+자바연동

회원정보를 전달하면 회원아이디가 존재하면 이름과 나이, 연락처, 성별 업데이트
존재하지 않으면 추가하기

CREATE OR REPLACE PROCEDURE proc_member_upsert(
    in_userid IN MEMBER.USERID%TYPE,
    in_userpw IN MEMBER.USERPW%TYPE,
    in_username IN MEMBER.USERNAME%TYPE,
    in_userage IN MEMBER.USERAGE%TYPE,
    in_userphone IN MEMBER.USERPHONE%TYPE,
    in_usergender IN MEMBER.USERGENDER%TYPE,
    out_ret OUT NUMBER 
)
IS
    tmp_chk NUMBER(1) :=0; --존재유무 확인용
BEGIN
    -- 존재유무 확인 tmp_chk에 숫자가 들어감
    SELECT COUNT(*) INTO tmp_chk FROM member m WHERE m.userid = in_userid;    
    IF tmp_chk = 0 THEN
    INSERT INTO MEMBER(userid, userpw, username, userage, userphone, usergender, userdate)
    VALUES (in_userid, in_userpw, in_username, in_userage, in_userphone, in_usergender, CURRENT_DATE) ;
    ELSE
    UPDATE member 
    SET username = in_username, userage = in_userage, userphone = in_userphone, usergender = in_usergender 
    WHERE userid = in_userid;
    END IF;
    COMMIT;
    out_ret := 1;
EXCEPTION WHEN OTHERS THEN
    ROLLBACK;
    out_ret:=0;
END;