CallableStatement 활용(1) - 오라클 프로시저

1. 프로시저 작성

데이터 추가, 삭제, 수정, 학번 검색, 이름 검색, 전체 리스트, 평균 계산

프로시저 작성 시 유의사항 developer에서는 자동 커밋이 되지 않으므로 COMMIT 해야함 

 

%TYPE 퍼센트타입

특정 테이블에 포함되어 있는 컬럼의 자료형을 참조하는 데이터 타입을 의미

pHak score.hak%TYPE

pHak이라는 변수명의 타입을 score 테이블의 hak 컬럼의 타입을 가져와서 쓰겠다는 의미

 

1. SCORE 테이블에 데이터를 추가하는 프로시저 

CREATE OR REPLACE PROCEDURE insertScore 
(
    pHak IN score.hak%TYPE,
    pName IN score.name%TYPE,
    pBirth IN score.birth%TYPE, 
    pKor IN score.kor%TYPE,
    pEng IN score.eng%TYPE,
    pMat IN score.mat%TYPE
)
IS
BEGIN
    INSERT INTO score(hak, name, birth, kor, eng, mat)
        VALUES (pHak, pName, pBirth, pKor, pEng, pMat);
    COMMIT;
END;
/

 

2. SCORE 테이블에 데이터를 수정는 프로시저 

CREATE OR REPLACE PROCEDURE updateScore 
(
    pName IN score.name%TYPE,
    pBirth IN score.birth%TYPE, 
    pKor IN score.kor%TYPE,
    pEng IN score.eng%TYPE,
    pMat IN score.mat%TYPE,
    pHak IN score.hak%TYPE
)
IS
BEGIN
    UPDATE score SET name = pName, birth=pBirth,kor=pKor, eng =pEng, mat =pMat
    WHERE hak = pHak;
    IF SQL%NOTFOUND THEN
        RAISE_APPLICATION_ERROR(-20100, '등록된 자료가 아닙니다');
    END IF;
    COMMIT;
END;
/

사용자 에러코드 함수 

RAISE_APPLICATION_ERROR( [에러코드], [에러메시지] )
에러코드 : -20000 ~ -20999 사이의 코드

에러 발생 시 함수에서 정의한 에러 메시지 출력

 

3. SCORE 테이블의 데이터를 삭제하는 프로시저 

CREATE OR REPLACE PROCEDURE deleteScore
(
    pHak VARCHAR2
)
IS
BEGIN
    DELETE FROM score WHERE hak = pHak;
    IF SQL%NOTFOUND THEN
        RAISE_APPLICATION_ERROR(-20100, '등록된 자료가 아닙니다');
    END IF;
    COMMIT;
END;
/

 

4. SCORE 테이블에서 학번을 검색하여 데이터를 가져오는 프로시저 

CREATE OR REPLACE PROCEDURE readScore 
(
    pResult OUT SYS_REFCURSOR,
    pHak IN VARCHAR2
)
IS
BEGIN
    OPEN pResult FOR
        SELECT hak, name, TO_CHAR(birth, 'YYYY-MM-DD') birth, kor, eng, mat,
            kor+eng+mat tot, (kor+eng+mat)/3 ave
        FROM score
        WHERE hak = pHak;
END;
/

자바에서 매개변수 학번을 입력 받아서 오라클에 넘겨주어야 해서 IN-OUT 사용

pResults는 결과 값을 담는 변수. 자바로 넘길 거니까 OUT

pHak 변수를 입력 받아 오라클에서 쿼리문에 써야 하므로 IN

 

 

5. SCORE 테이블에서 이름을 검색하여 데이터를 가져오는 프로시저 

CREATE OR REPLACE PROCEDURE searchNameScore
(
    pResult OUT SYS_REFCURSOR,
    pName IN VARCHAR2
)
IS
BEGIN
    OPEN pResult FOR
        SELECT hak, name, TO_CHAR(birth, 'YYYY-MM-DD') birth, kor, eng, mat,
            kor+eng+mat tot, (kor+eng+mat)/3 ave
        FROM score
        WHERE INSTR(name, pName)>=1;
END;
/

학번 검색과 동일 

 

6. SCORE 테이블의 모든 데이터를 가져오는 프로시저 

CREATE OR REPLACE PROCEDURE listScore
(
    pResult OUT SYS_REFCURSOR
    
)
IS
BEGIN
    OPEN pResult FOR
        SELECT hak, name, TO_CHAR(birth, 'YYYY-MM-DD') birth, kor, eng, mat,
            kor + eng+ mat tot, (kor+eng+mat)/3 ave,
            RANK() OVER(ORDER BY (kor+eng+ mat)DESC) rank
            FROM score;
END;

 

7. SCORE 테이블에서 국, 영, 수 평균을 계산하는 프로시저 

CREATE OR REPLACE PROCEDURE averagerScore
(
    pKor OUT NUMBER,
    pEng OUT NUMBER,
    pMat OUT NUMBER
    
)
IS
BEGIN
    SELECT NVL(AVG(kor), 0), NVL(AVG(kor), 0), NVL(AVG(mat), 0)
        INTO pKor, pEng, pMat
    FROM score;
END;
/

자바로 결과를 보내야 하므로 OUT 이용