프로시저

수호천사임다·2024년 9월 24일

오라클

목록 보기

46/53

-- 자주 실행해야 하는 업무 흐름(SQL)을 미리 작성하여 데이터베이스 내에 저장해 두었다가 필요할 때마다
-- 호출하여 실행(SELECT, UPDATE, INSERT가 될 수도 있다. ) - 자바에서 void

👉 프로시저 생성

CREATE PROCEDURE 프로시저명
IS 
BEGIN
    자주 실행할 명령어
    COMMIT;
END;

👉 사용 예시

CREATE PROCEDURE plnsertTest
IS 
BEGIN
    INSERT INTO test(num, name, score, grade) VALUES (test_seq.NEXTVAL, '김자바', 80, 'B');
    COMMIT;
END;

👉 프로시저 수정

IN 파라미터 : 프로시저를 전달되는 함수(읽기 전용)
파라미터 타입의 크기를 명시하지 않는다.
OR REPLACE 사용

-- 점수가 0 ~ 100 사이인 경우인 INSERT 되도록 변경 
-- RAISE_APPLICATION_ERROR(에러코드, 메시지)
-- 에러코드 : -20999 ~ 20000 
CREATE OR REPLACE PROCEDURE plnsertTest
(
    pName IN VARCHAR2, 
    pScore IN NUMBER 
)
IS 
    --필요한 변수 잡기
    vGrade VARCHAR2(10);
BEGIN
    IF pScore < 0 OR pScore > 100 THEN 
        RAISE_APPLICATION_ERROR(-20001, '점수는 0 ~ 100 사이만 가능합니다.');
    END IF;
    IF pScore >= 90 THEN vGrade := 'A';
    ELSIF pScore >= 80 THEN vGrade := 'B';
    ELSIF pScore >= 70 THEN vGrade := 'C';
    ELSIF pScore >= 60 THEN vGrade := 'D';
    ELSE vGrade := 'F';
    END IF;
    
    INSERT INTO test(num, name, score, grade) VALUES (test_seq.NEXTVAL, pName, pScore, vGrade);
    COMMIT;
END;
/

👉 프로시저 실행

EXEC에 주석을 달면 안된다.

EXEC 프로시저명;

👉 프로시저 목록 확인

SELECT * FROM user_procedures;

👉 프로시저 소스 확인

SELECT * FROM user_source;

👉 의존관계 확인

SELECT * FROM user_dependencies;

문제

-- 3R개의 테이블 작성
-- 테이블명 : ex1
    -- num 숫자 기본키
    -- name 문자(30) NN
-- 테이블명 : ex2
    -- num 숫자 기본키, ex1 테이블의 참조키
    -- brith 날짜 NN
-- 테이블명 : ex3
    -- num 숫자 기본키, ex1 테이블의 참조키
    -- score 숫자(3) NN
    -- grade 문자 (10) NN
-- 시퀀스 : ex_seq
    -- 1씩 증가

-- ex1, ex2, ex3 테이블에 레코드를 추가하는 프로시저 작성
-- 프로시저명: pInsertEx
-- 인수 : 이름, 생년월일, 점수
-- 점수는 0 ~ 100 사이가 아니면 등록되지 않도록 예외처리
-- grade는 80점 이상은 우수, 60점 이상은 보통, 그렇지 않으면 미달
-- grade는 CASE문 사용

    CREATE TABLE ex1 (
        num NUMBER PRIMARY KEY,
        name VARCHAR2(30) NOT NULL
    );
    
    CREATE TABLE ex2 (
        num NUMBER PRIMARY KEY,
        brith DATE NOT NULL
    );
    ALTER TABLE ex2 ADD CONSTRAINT ex2_ex1_FK_num FOREIGN KEY (num) REFERENCES ex1(num);
          -- num 숫자 기본키, ex1 테이블의 참조키
    CREATE TABLE ex3 (
        num NUMBER PRIMARY KEY,
        score NUMBER(3) NOT NULL,
        grade VARCHAR2(10) NOT NULL
    );
    ALTER TABLE ex3 ADD CONSTRAINT ex3_ex1_FK_num FOREIGN KEY (num) REFERENCES ex1(num);

    CREATE SEQUENCE ex_seq1
    INCREMENT BY 1
    START WITH 1
    NOMAXVALUE
    NOCACHE
    NOCYCLE;
    
    SELECT * FROM seq;
    
    CREATE OR REPLACE PROCEDURE pInsertEx(
        -- 파라미터 인수
        pName IN VARCHAR2,
        pBirth IN VARCHAR2,
        pScore NUMBER
    )
    IS
        -- 변수잡기
        vGrade VARCHAR2(10);
    BEGIN
         CASE
          WHEN PScore < 0 OR pScore > 100 THEN
         RAISE_APPLICATION_ERROR(-20001, '점수는 0 ~ 100 사이만 가능합니다.');
          WHEN pScore >= 80 THEN vGrade := '우수';
          WHEN pScore >= 60 THEN vGrade := '보통';
          ELSE vGrade := '미달';
        END CASE;
        INSERT INTO ex1(num, name) VALUES (ex_seq1.NEXTVAL, pName);
        INSERT INTO ex2(num, brith) VALUES (ex_seq1.CURRVAL, TO_DATE(pBirth, 'YYYY-MM-DD'));
        INSERT INTO ex3(num, score, grade) VALUES (ex_seq1.CURRVAL,pScore, vGrade);
        COMMIT;
    END;
/

...

    -- 3R개의 테이블 작성
    -- 테이블명 : ex1
        -- num 숫자 기본키
        -- name 문자(30) NN
    -- 테이블명 : ex2
        -- num 숫자 기본키, ex1 테이블의 참조키
        -- brith 날짜 NN
    -- 테이블명 : ex3
        -- num 숫자 기본키, ex1 테이블의 참조키
        -- score 숫자(3) NN
        -- grade 문자 (10) NN
    -- 시퀀스 : ex_seq
        -- 1씩 증가

    -- ex1, ex2, ex3 테이블에 레코드를 추가하는 프로시저 작성
    -- 프로시저명: pInsertEx
    -- 인수 : 이름, 생년월일, 점수
    -- 점수는 0 ~ 100 사이가 아니면 등록되지 않도록 예외처리
    -- grade는 80점 이상은 우수, 60점 이상은 보통, 그렇지 않으면 미달
    -- grade는 CASE문 사용
    
    CREATE TABLE ex1 (
        num NUMBER PRIMARY KEY,
        name VARCHAR2(30) NOT NULL
    );
    
    CREATE TABLE ex2 (
        num NUMBER PRIMARY KEY,
        brith DATE NOT NULL
    );
    ALTER TABLE ex2 ADD CONSTRAINT ex2_ex1_FK_num FOREIGN KEY (num) REFERENCES ex1(num);
          -- num 숫자 기본키, ex1 테이블의 참조키
    CREATE TABLE ex3 (
        num NUMBER PRIMARY KEY,
        score NUMBER(3) NOT NULL,
        grade VARCHAR2(10) NOT NULL
    );
    ALTER TABLE ex3 ADD CONSTRAINT ex3_ex1_FK_num FOREIGN KEY (num) REFERENCES ex1(num);

    CREATE SEQUENCE ex_seq1
    INCREMENT BY 1
    START WITH 1
    NOMAXVALUE
    NOCACHE
    NOCYCLE;
    
    SELECT * FROM seq;
    
    CREATE OR REPLACE PROCEDURE pInsertEx(
        -- 파라미터 인수
        pName IN VARCHAR2,
        pBirth IN VARCHAR2,
        pScore NUMBER
    )
    IS
        -- 변수잡기
        vGrade VARCHAR2(10);
    BEGIN
         CASE
          WHEN PScore < 0 OR pScore > 100 THEN
         RAISE_APPLICATION_ERROR(-20001, '점수는 0 ~ 100 사이만 가능합니다.');
          WHEN pScore >= 80 THEN vGrade := '우수';
          WHEN pScore >= 60 THEN vGrade := '보통';
          ELSE vGrade := '미달';
        END CASE;
        INSERT INTO ex1(num, name) VALUES (ex_seq1.NEXTVAL, pName);
        INSERT INTO ex2(num, brith) VALUES (ex_seq1.CURRVAL, TO_DATE(pBirth, 'YYYY-MM-DD'));
        INSERT INTO ex3(num, score, grade) VALUES (ex_seq1.CURRVAL,pScore, vGrade);
        COMMIT;
    END;
/
-- INSERT INTO ALL로 하기  3개가 모두 들어가거나 모두 안 들어간다 

EXEC pInsertEx('김자바', '2000-10-10', 90);
SELECT * FROM ex2;

    -- 점수는 0 ~ 100 사이가 아니면 등록되지 않도록 예외처리
    -- grade는 80점 이상은 우수, 60점 이상은 보통, 그렇지 않으면 미달
    -- grade는 CASE문 사용

-- 레코드 수정하는 방법
   CREATE OR REPLACE PROCEDURE pUpdateEx(
        -- 파라미터 인수
        pNum IN NUMBER,
        pName IN VARCHAR2,
        pBirth IN VARCHAR2,
        pScore NUMBER
    )
    IS
        -- 변수잡기
        vGrade VARCHAR2(10);
    BEGIN
          IF 
            pScore < 0 OR pScore > 100 THEN
            RAISE_APPLICATION_ERROR(-20001, '점수는 0 ~ 100 사이만 가능합니다.');
          END IF;
          
          IF pScore >= 80 THEN vGrade := '우수';
          ELSIF pScore >= 80 THEN vGrade := '보통';
          ELSE vGrade := '미달';
        END IF;
        UPDATE  ex1 SET  name = pName WHERE num = pNum;
        UPDATE  ex2 SET  brith = TO_DATE(pBirth, 'YYYY-MM-DD') WHERE  num = pNum;    
        UPDATE ex3 SET   score = pScore, grade = vGrade WHERE  num = pNum;
        COMMIT;
    END;
/
SELECT * FROM ex3;
EXEC pUpdateEx(13, '김김이', '2000-10-11', 75);
SELECT * FROM ex1;
SELECT * FROM ex2;
SELECT * FROM ex3;


   CREATE OR REPLACE PROCEDURE pDeleteEx(
        -- 파라미터 인수
        pNum IN NUMBER
    )
    IS
        -- 변수잡기
        vGrade VARCHAR2(10);
    BEGIN
        DELETE FROM ex3 WHERE num = pNum;
        DELETE FROM ex2 WHERE num = pNum;
        DELETE FROM ex1 WHERE num = pNum;
        COMMIT;
    END;
/
EXEC pDeleteEx(13);
SELECT * FROM ex1;
SELECT * FROM ex2;
SELECT * FROM ex3;

-- ex1, ex2, ex3 테이블에서 기본키 조건에 만족하는 레코드를 출력하는 프로시저 작성
    -- 프로시저명 : pSelectOneEx
    -- 파라미터 : pk 번호
    
    

-- ex1, ex2, ex3 테이블의 모든 레코드를 출력하는 프로시저 작성
    -- 프로시저명 : pSelectEx

CREATE OR REPLACE PROCEDURE pSelectOneEx(
    pNum IN NUMBER 
)
IS
    -- 타입 생성
    TYPE MYTYPE IS RECORD
    (
        num ex1.num%TYPE,
        name ex1.name%TYPE,
        birth ex2.brith%TYPE,
        score ex3.score%TYPE,
        grade ex3.grade%TYPE
    );
    
    rec MYTYPE;
BEGIN
    SELECT e1.num, name, brith, score, grade INTO rec
    FROM ex1 e1
    JOIN ex2 e2 ON e1.num = e2.num
    JOIN ex3 e3 ON e1.num = e3.num
    WHERE e1.num = pNum;
    
    DBMS_OUTPUT.PUT(rec.num || ':');
    DBMS_OUTPUT.PUT(rec.name || ':');
    DBMS_OUTPUT.PUT(rec.birth || ':');
    DBMS_OUTPUT.PUT(rec.score || ':');
    DBMS_OUTPUT.PUT_LINE(rec.grade || ':');
END;
/

EXEC pSelectOneEx(14);
SELECT * FROM ex3;

CREATE OR REPLACE PROCEDURE pSelectEx 
IS
BEGIN
    FOR rec IN (
        SELECT e1.num, name, brith, score, grade
        FROM ex1 e1
        JOIN ex2 e2 ON e1.num = e2.num
        JOIN ex3 e3 ON e2.num = e3.num
    ) LOOP
    DBMS_OUTPUT.PUT(rec.num || ':');
    DBMS_OUTPUT.PUT(rec.name || ':');
    DBMS_OUTPUT.PUT(rec.brith || ':');
    DBMS_OUTPUT.PUT(rec.score || ':');
    DBMS_OUTPUT.PUT_LINE(rec.grade);
    END LOOP;
END;

EXEC pSelectEx;

-- OUT 파라미터
    -- IN 파라미터: 읽기전용, 프로시저에 전달하는 파라미터(기본)
    -- OUT 파라미터: 프로시저의 결과를 호출한 곳으로 돌려주는 파라미터, 값 변경 가능
    -- IN OUT 파라미터 
    
CREATE OR REPLACE PROCEDURE pSelectOutTest
(
    pNum IN NUMBER,
    pName OUT VARCHAR2,
    pScore OUT NUMBER,
    PGrade OUT VARCHAR2
)
IS
BEGIN
  --  pNum := 14; -- 에러 IN은 읽기전용
    SELECT name, score, grade INTO pName, pScore, pGrade
    FROM test
    WHERE num = pNum;
END;
/

CREATE OR REPLACE PROCEDURE pSelectOutResult
IS
    vName VARCHAR2(30);
    vScore NUMBER(3);
    vGrade VARCHAR2(10);
BEGIN
    -- 다른 프로시저 호출
    pSelectOutTest(1, vName, vScore, vGrade);
    
    DBMS_OUTPUT.PUT_LINE(vName || ':' || vScore || ':' || vGrade);
END;
/
EXEC pSelectOutResult;

SELECT * FROM test;

수호천사임다

이전 포스트

PL/SQL

다음 포스트

프로시저

오라클

👉 프로시저 생성

👉 사용 예시

👉 프로시저 수정

👉 프로시저 실행

👉 프로시저 목록 확인

👉 프로시저 소스 확인

👉 의존관계 확인

문제

PL/SQL

함수

0개의 댓글