Oracle :: PROCEDURE(프로시저)

(파일명 20231103_01_scott(plsql), 20231103_02_scott, 20231106_01_scott(plsql), 20231106_02_scott  )

■ PROCEDURE (프로시저)

- PL/SQL 에서 가장 대표적인 구조인 스토어드 프로시저는 

  개발자가 자주 작성해야 하는 업무의 흐름을 미리 작성하여 데이터베이스 내에 저장해 두었다가

  필요할 때 마다 호출하여 실행할 수 있도록 처리해주는 구문!

 

■ 형식 및 구조 / 실행(호출)

-- 형식 및 구조
/*
CREATE [OR REPLACE] PROCEDURE 프로시저명
[( 매개변수 IN 데이터타입
   매개변수 OUT 데이터타입
   매개변서 INOUT 데이터타입
)]
IS
    [-- 주요 변수 선언]
BEGIN
    -- 실행 구문;
    ...
    [EXCEPTION
        -- 예외 처리 구문;]
END;        
*/


-- 실행(호출)
/*
EXE[CUTE] 프로시저명[(인수1, 인수2, ...)];
*/

※ FUNCTION 과 비교했을 때, RETURN 반환자료형 부분이 존재하지 않으며, RETURN문 자체도 존재하지 않다!

    프로시저 실행 시 넘겨주게 되는 매개변수 종류는 IN, OUT, INOUT 으로 구분된다.

 

■ 실습1

-- 프로시저 생성 전 테이블 확인
SELECT *
FROM TBL_STUDENTS;
--==>> moon	문정환	010-1111-1111	제주도 서귀포시

SELECT *
FROM TBL_IDPW;
--==>> moon	java006$

---------------------------------------------------------------------------------------------
--○ 프로시저 생성
CREATE OR REPLACE PROCEDURE PRC_STUDENT_INSERT
( V_ID      IN TBL_IDPW.ID%TYPE
, V_PW      IN TBL_IDPW.PW%TYPE
, V_NAME    IN TBL_STUDENTS.NAME%TYPE
, V_TEL     IN TBL_STUDENTS.TEL%TYPE
, V_ADDR    IN TBL_STUDENTS.ADDR%TYPE
)
IS
    -- 선언부 내용 없음. 없으면 안써도 된다.
BEGIN
    -- TBL_IDPW 테이블에 데이터 입력 → INSERT
    INSERT INTO TBL_IDPW(ID, PW)
    VALUES(V_ID, V_PW);
    
    -- TBL_STUDENTS 테이블에 데이터 입력 → INSERT
    INSERT INTO TBL_STUDENTS(ID, NAME, TEL, ADDR)
    VALUES(V_ID, V_NAME, V_TEL, V_ADDR);
    
    -- 커밋
    COMMIT;
END;
--==>> Procedure PRC_STUDENT_INSERT이(가) 컴파일되었습니다.
---------------------------------------------------------------------------------------------
--○ 프로시저 생성 후 실행
EXEC PRC_STUDENT_INSERT('wool', 'java006$', '정한울', '010-2222-2222', '서울 강남구');
--==>> PL/SQL 프로시저가 성공적으로 완료되었습니다.

--○ 프로시저 호출 이후 확인
SELECT *
FROM TBL_IDPW;
--==>>
/*
moon	java006$
wool	java006$
*/

SELECT *
FROM TBL_STUDENTS;
--==>>
/*
moon	문정환	010-1111-1111	제주도 서귀포시
wool	정한울	010-2222-2222	서울 강남구
*/

--> 프로시저 생성 후 실행으로 2개 테이블에 모두 데이터 입력된 것을 확인할 수 있다!
-------------------------------------------------------------------------------------------
--○ TBL_STUDENTS 테이블에서 전화번호와 주소 데이터를 수정하는(변경하는)
--   프로시저를 작성한다.
--   단, ID 와 PW 가 일치하는 경우에만 수정을 진행할 수 있도록 처리한다.
--   프로시저명 : PRC_STUDENT_UPDATE()
/*
실행 예)
EXEC PRC_STUDENT_UPDATE('moon', 'java000', '010-9999-9999', '강원도 횡성');
--> 데이터 수정 X

EXEC PRC_STUDENT_UPDATE('moon', 'java0006$', '010-9999-9999', '강원도 횡성');
--> 데이터 수정 ○
*/
-- 내가 작성한 코드
CREATE OR REPLACE PROCEDURE PRC_STUDENT_UPDATE
( V_ID     IN TBL_IDPW.ID%TYPE
, V_PW     IN TBL_IDPW.PW%TYPE
, V_TEL    IN TBL_STUDENTS.TEL%TYPE
, V_ADDR   IN TBL_STUDENTS.ADDR%TYPE
)
IS
BEGIN 
    UPDATE TBL_STUDENTS
    SET TEL = V_TEL, ADDR = V_ADDR
    WHERE ID = V_ID
      AND V_PW= (SELECT PW
                 FROM TBL_IDPW
                 WHERE ID = V_ID);
           
    COMMIT;
END;

-- 같이 한거
-- 방법 1.
CREATE OR REPLACE PROCEDURE PRC_STUDENT_UPDATE
( V_ID      IN TBL_IDPW.ID%TYPE             -- 겹치는 데이터가 있을 때, 부모 테이블에 있는거 쓰는게 바람직. 이 경우는 1:1 관계로 부모자식 관계 아니라서 아무거나 써도 상관없음
, V_PW      IN TBL_IDPW.PW%TYPE
, V_TEL     IN TBL_STUDENTS.TEL%TYPE
, V_ADDR    IN TBL_STUDENTS.ADDR%TYPE
)
IS
    V_PW2   TBL_IDPW.PW%TYPE;
    V_FLAG  NUMBER := 0;        -- 오라클에는 BOOLEAN 자료형이 없으니 숫자형으로 임의 데이터(0) 지정 // 패스워드일치 : 1 / 패스워드불일치 : 2
    
BEGIN

    SELECT PW INTO V_PW2
    FROM TBL_IDPW
    WHERE ID = V_ID;
    
    IF (V_PW = V_PW2)       -- 패스워드 일치
        THEN V_FLAG := 1;
    ELSE                    -- 패스워드 불일치
        V_FLAG := 2;
    END IF;

    UPDATE TBL_STUDENTS
    SET TEL=V_TEL, ADDR=V_ADDR
    WHERE ID = V_ID
      AND V_FLAG = 1;
      
    COMMIT
END;
--==>> Procedure PRC_STUDENT_UPDATE이(가) 컴파일되었습니다.


-- 방법 2.
CREATE OR REPLACE PROCEDURE PRC_STUDENT_UPDATE
( V_ID      IN TBL_IDPW.ID%TYPE             -- 겹치는 데이터가 있을 때, 부모 테이블에 있는거 쓰는게 바람직. 이 경우는 1:1 관계로 부모자식 관계 아니라서 아무거나 써도 상관없음
, V_PW      IN TBL_IDPW.PW%TYPE
, V_TEL     IN TBL_STUDENTS.TEL%TYPE
, V_ADDR    IN TBL_STUDENTS.ADDR%TYPE
)
IS
BEGIN
    UPDATE (SELECT T1.ID, T1.PW, T2.TEL, T2.ADDR
            FROM TBL_IDPW T1 JOIN TBL_STUDENTS T2
            ON T1.ID = T2.ID) T
    SET T.TEL=V_TEL, T.ADDR=V_ADDR
    WHERE T.ID = V_ID
      AND T.PW = V_PW;
    
    COMMIT;
END;
--==>> Procedure PRC_STUDENT_UPDATE이(가) 컴파일되었습니다.
------------------------------------------------------------------------------------------------
--○ 프로시저 생성 후 실행 테스트 (잘못된 패스워드)
EXEC PRC_STUDENT_UPDATE('moon', 'java000', '010-9999-9999', '강원도 횡성')
--==>> PL/SQL 프로시저가 성공적으로 완료되었습니다.

--○ 프로시저 호출 후 테이블 조회
SELECT *
FROM TBL_STUDENTS;
--==>> 
/*
moon	문정환	010-9999-9999	제주도 서귀포시            -- 데이터 바뀌지 않음
wool	정한울	010-2222-2222	서울 강남구
*/


--○ 프로시저 생성 후 실행 테스트 (올바른 패스워드)
EXEC PRC_STUDENT_UPDATE('moon', 'java006$', '010-9999-9999', '강원도 횡성');
--==>> PL/SQL 프로시저가 성공적으로 완료되었습니다.

--○ 프로시저 호출 후 테이블 조회
SELECT *
FROM TBL_STUDENTS;
--==>>
/*
moon	문정환	010-9999-9999	강원도 횡성
wool	정한울	010-2222-2222	서울 강남구
*/

--○ 프로시저 생성 후 실행 테스트 (잘못된 패스워드)
EXEC PRC_STUDENT_UPDATE('wool', 'java000', '010-8888-8888', '전남 목포');
--==>> PL/SQL 프로시저가 성공적으로 완료되었습니다.

--○ 프로시저 호출 후 테이블 조회
SELECT *
FROM TBL_STUDENTS;
--==>
/*
moon	문정환	010-9999-9999	강원도 횡성
wool	정한울	010-2222-2222	서울 강남구
*/

--○ 프로시저 생성 후 실행 테스트 (올바른 패스워드)
EXEC PRC_STUDENT_UPDATE('wool', 'java006$', '010-8888-8888', '전남 목포');
--==>> PL/SQL 프로시저가 성공적으로 완료되었습니다.

--○ 프로시저 호출 후 테이블 조회
SELECT *
FROM TBL_STUDENTS;
--==>
/*
moon	문정환	010-9999-9999	강원도 횡성
wool	정한울	010-8888-8888	전남 목포
*/

 

■ 실습2

- 실습2를 진행하면서 실습의 편의를 위해 총점, 평균, 등급 컬럼을 만들었지만,

  이는 기존 컬럼 데이터로 쿼리문을 통해 계산 가능한 값들이다.

  이런 값들로 컬럼을 만드는 것은 실제 테이블 구조에 적합하지도 바람직하지도 않다!!!

--○ 데이터 입력 시 특정 항목의 데이터만 입력하면
--   내부적으로 다른 추가항목에 대한 처리가 함께 이루어질 수 있도록 하는
--   프로시저를 작성한다.(생성한다.)
--   프로시저명 : PRC_SUNGJUCK_INSERT()
/*
문제 인식)
현재 TBL_SUNGJUK 테이블은
HAKBUN, NAME, KOR, ENG, MAT, TOT, AVG, GRADE
(학변, 이름 ,국어점수, 영어점수, 수학점수, 총점, 평균, 등급)
컬럼으로 구성되어 있다.
이 테이블을 대상으로 특정 항목(학번, 이름, 국어점수, 영어점수, 수학점수)만 입력하면
추가 항목(총점, 평균, 등급)은 알아서 처리될 수 있도록  프로시저를 구성하라는 것이다.

실행 예)
EXEC PRC_SUNGJUK_INSERT(1, '김다슬', 90, 80, 70);

→ 이와 같은 프로시저 호출로 처리된 결과
학번  이름  국어점수    영어점수    수학점수    총점  평균  등급
1   김다슬       90         80         70      240   80    B
*/



CREATE OR REPLACE PROCEDURE PRC_SUNGJUK_INSERT
( V_HAKBUN      IN TBL_SUNGJUK.HAKBUN%TYPE
, V_NAME        IN TBL_SUNGJUK.NAME%TYPE
, V_KOR         IN TBL_SUNGJUK.KOR%TYPE
, V_ENG         IN TBL_SUNGJUK.ENG%TYPE
, V_MAT         IN TBL_SUNGJUK.MAT%TYPE
)
IS
    -- 선언부
    -- INSERT 쿼리문을 수행하기 위해 필요한 추가 변수 구성
    V_TOT     TBL_SUNGJUK.TOT%TYPE;
    V_AVG     TBL_SUNGJUK.AVG%TYPE;
    V_GRADE   TBL_SUNGJUK.GRADE%TYPE;
BEGIN
    -- 실행부
    
    -- 선언부에 추가로 선언한 주요 변수들에 값을 담아내야 한다.
    
    V_TOT := V_KOR + V_ENG + V_MAT;
    V_AVG := V_TOT / 3;
    
    IF (V_AVG >= 90)
        THEN V_GRADE := 'A';
    ELSIF (V_AVG >= 80)
        THEN V_GRADE := 'B';
    ELSIF (V_AVG >= 70)
        THEN V_GRADE := 'C';
    ELSIF (V_AVG >= 60)
        THEN V_GRADE := 'D';
    ELSE
        V_GRADE := 'F';
    END IF;
    
    -- INSERT 쿼리문 수행
    INSERT INTO TBL_SUNGJUK(HAKBUN, NAME, KOR, ENG, MAT, TOT, AVG, GRADE)
    VALUES(V_HAKBUN, V_NAME, V_KOR, V_ENG, V_MAT, V_TOT, V_AVG, V_GRADE);
    
    -- 커밋
    COMMIT;
END;
--==>> Procedure PRC_SUNGJUK_INSERT이(가) 컴파일되었습니다.
--------------------------------------------------------------------------------------------
--○ 프로시저 생성 후 실행
EXEC PRC_SUNGJUK_INSERT(1, '김다슬', 90, 80, 70);
EXEC PRC_SUNGJUK_INSERT(2, '노은하', 80, 70, 65);
--==>>
/*
1	김다슬	90	80	70	240	80	    B
2	노은하	80	70	65	215	71.7	C
*/
--------------------------------------------------------------------------------------------
--○ TBL_SUNGJUK 테이블에서 특정 학생의 점수
--   (학번, 국어점수, 영어점수, 수학점수) 데이터 수정 시
--   총점, 평균, 등급까지 함께 수정되는 프로시저를 생성한다.
--   프로시저명 : PRC_SUNGJUK_UPDATE()

/*
실행 예)
EXEC PRC_SUNGJUK_UPDATE(2, 50, 50, 50);

→ 프로시저 호출로 처리된 결과
학번  이름  국어점수    영어점수    수학점수    총점  평균  등급
 1   김다슬     90         80         70       240   80    B
 2   노은하     50         50          50      150   50    F

*/

CREATE OR REPLACE PROCEDURE PRC_SUNGJUK_UPDATE
( V_HAKBUN  IN TBL_SUNGJUK.HAKBUN%TYPE
, V_KOR     IN TBL_SUNGJUK.KOR%TYPE
, V_ENG     IN TBL_SUNGJUK.ENG%TYPE
, V_MAT     IN TBL_SUNGJUK.MAT%TYPE
)
IS
    V_TOT   TBL_SUNGJUK.TOT%TYPE;
    V_AVG   TBL_SUNGJUK.AVG%TYPE;
    V_GRADE TBL_SUNGJUK.GRADE%TYPE;

BEGIN      
    
    V_TOT := V_KOR+V_ENG+V_MAT;
    V_AVG := V_TOT/3;
    
    IF (V_AVG >= 90)
        THEN V_GRADE := 'A';
    ELSIF (V_AVG >= 80)
        THEN V_GRADE := 'B';
    ELSIF (V_AVG >= 70)
        THEN V_GRADE := 'C';
    ELSIF (V_AVG >= 60)
        THEN V_GRADE := 'D';
    ELSE
        V_GRADE := 'F';
    END IF;

    -- UPDATE 쿼리문 수행
    UPDATE TBL_SUNGJUK
    SET KOR = V_KOR, ENG=V_ENG, MAT=V_MAT,
        TOT = V_TOT, AVG = V_AVG, GRADE = V_GRADE
    WHERE HAKBUN = V_HAKBUN;

      
    COMMIT;
   
END;
--==>> Procedure PRC_SUNGJUK_UPDATE이(가) 컴파일되었습니다.
--------------------------------------------------------------------------------------------
--○ 프로시저 생성 후 실행(GHCNF)
EXEC PRC_SUNGJUK_UPDATE(2, 50, 50, 50);
--==>> PL/SQL 프로시저가 성공적으로 완료되었습니다.

--○ 프로시저 호출 이후 테이블 조회
SELECT *
FROM TBL_SUNGJUK;
--==>>
/*
1	김다슬	90	80	70	240	80	B
2	노은하	50	50	50	150	50	F
*/

 

■ 실습 3

--○ TBL_INSA 테이블을 대상으로 신규 데이터 입력 프로시저를 작성한다.
--   NUM, NAME, SSN, IBSADATE, CITY, TEL, BUSEO, JIKWI, BASICPAY, SUDANG 으로 구성된 컬럼 중
--   NUM(사원번호) 항목을 제외한 항목의 데이터 입력 시 
--   해당 항목의 값은 기존 부여된 사원 번호의 마지막 번호 그 다음 번호를
--   자동으로 입력 처리할 수 잇는 프로시저로 구성한다.
--   프로시저명 : PRC_INSA_INSERT()
/*
실행 예)
EXEC PRC_INSA_INSERT('최혜인','970812-2234567', SYSDATE, '서울', '010-1111-1111'
                    , '개발부', '대리', 2000000, 2000000);
→ 위와 같이 프로시저 호출 및 실행
1061 최혜인 970812-2234567 2023-11-06 서울 010-1111-1111 개발부 대리 2000000 2000000
의 데이터가 신규 입력된 상황
 
*/
-- 제가 해보았습니다!
CREATE OR REPLACE PROCEDURE PR_INSA_INSERT
(
  V_NAME      IN TBL_INSA.NAME%TYPE
, V_SSN       IN TBL_INSA.SSN%TYPE
, V_IBSADATE  IN TBL_INSA.IBSADATE%TYPE
, V_CITY      IN TBL_INSA.CITY%TYPE
, V_TEL       IN TBL_INSA.TEL%TYPE
, V_BUSEO     IN TBL_INSA.BUSEO%TYPE
, V_JIKWI     IN TBL_INSA.JIKWI%TYPE
, V_BASICPAY  IN TBL_INSA.BASICPAY%TYPE
, V_SUDANG    IN TBL_INSA.SUDANG%TYPE
)
IS
    V_NUM   TBL_INSA.NUM%TYPE;

BEGIN
    SELECT MAX(NUM) INTO V_NUM
    FROM TBL_INSA;
    
    V_NUM := V_NUM + 1;
    
    INSERT INTO TBL_INSA(NUM, NAME, SSN, IBSADATE, CITY, TEL, BUSEO, JIKWI, BASICPAY, SUDANG)
    VALUES (V_NUM, V_NAME, V_SSN, V_IBSADATE, V_CITY, V_TEL, V_BUSEO, V_JIKWI, V_BASICPAY, V_SUDANG);
    
    COMMIT;

END;
--==>> Procedure PR_INSA_INSERT이(가) 컴파일되었습니다.

-- 같이해볼까요
CREATE OR REPLACE PROCEDURE PR_INSA_INSERT
( V_NAME      IN TBL_INSA.NAME%TYPE
, V_SSN       IN TBL_INSA.SSN%TYPE
, V_IBSADATE  IN TBL_INSA.IBSADATE%TYPE
, V_CITY      IN TBL_INSA.CITY%TYPE
, V_TEL       IN TBL_INSA.TEL%TYPE
, V_BUSEO     IN TBL_INSA.BUSEO%TYPE
, V_JIKWI     IN TBL_INSA.JIKWI%TYPE
, V_BASICPAY  IN TBL_INSA.BASICPAY%TYPE
, V_SUDANG    IN TBL_INSA.SUDANG%TYPE
)
IS
    V_NUM   TBL_INSA.NUM%TYPE
BEGIN
    -- 기존 부여된 사원번호의 마지막 번호
    SELECT NVL(MAX(NUM), 0) INTO V_NUM
    FROM TBL_INSA;

    -- 데이터 입력 쿼리문 구성
    INSERT INTO TBL_INSA(NUM, NAME, SSN, IBSADATE, CITY, TEL, BUSEO, JIKWI, BASICPAY, SUDANG)
    VALUES (V_NUM+1, V_NAME, V_SSN, V_IBSADATE, V_CITY, V_TEL, V_BUSEO, V_JIKWI, V_BASICPAY, V_SUDANG);
    
    --커밋
    COMMIT;
    
    /*
    -- 기존 부여된 사원번호의 마지막 번호
    SELECT MAX(NVL(NUM, 0))+1 INTO V_NUM            V_NUM에 값 넣어줄 때 부터 +1 해줘는 방식으로 해도 무방
    FROM TBL_INSA;

    -- 데이터 입력 쿼리문 구성
    INSERT INTO TBL_INSA(NUM, NAME, SSN, IBSADATE, CITY, TEL, BUSEO, JIKWI, BASICPAY, SUDANG)
    VALUES (V_NUM, V_NAME, V_SSN, V_IBSADATE, V_CITY, V_TEL, V_BUSEO, V_JIKWI, V_BASICPAY, V_SUDANG);
    
    --커밋
    COMMIT;
    */
END;
--==>> Procedure PR_INSA_INSERT이(가) 컴파일되었습니다.

---------------------------------------------------------------------------------------------
--   PRC_INSA_INSERT() 프로시저 생성 후 테스트
EXEC PR_INSA_INSERT('최혜인','970812-2234567', SYSDATE, '서울', '010-1111-1111', '개발부', '대리', 2000000, 2000000);
--==>> PL/SQL 프로시저가 성공적으로 완료되었습니다.

--○ 프로시저 호출(실행) 후 확인
SELECT *
FROM TBL_INSA;
--==>>
/*
            :
1061	최혜인	970812-2234567	2023-11-06	서울	010-1111-1111	개발부	대리	2000000	2000000
*/