Procedure - 프로시저

select * from user_procedures;

해당 계정의 모든 프로시저 목록 확인

 

-- 인서트 프로시저
CREATE OR REPLACE PROCEDURE insertScore
(
    pHak In VARCHAR2,
    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;
/

EXEC insertScore('7878', '호호호', '2000-10-10', 80, 90, 90);

insert 프로시저

CREATE만 써도 되지만 나중에 수정을 위해서 OR REPLACE 도 붙이는 편이 좋다.

(

 이 부분에 들어가 있는 pHak 등등 애들은 변수(파라미터) 이다.

)

pHak = 변수명 / IN 뒤에 있는건 변수의 형태 VARCHAR2처럼 직접 형식을 지정할 수 도 있고,

score.name%TYPE 처럼 score 테이블의 name의 타입과 같이하라 처럼 어떤 컬럼의 형식을 그대로 따라가게 할 수 있다.

 

IS 부분은 파라미터 안에서만 쓰는 프로시저 내의 변수이다.

여기서는 프로시저 내의 변수는 사용하지 않으니 그냥 IS만 쓰고 건너 뛴다.

 

BEGIN 부분은 실제로 쿼리문을 작성하는 곳 COMMIT은 반드시 필요함

 

만든 프로시저는 EXEC 명령어를 사용해서 이용.

 

-- update 프로시저
CREATE OR REPLACE PROCEDURE updateScore 
(
    pHak In VARCHAR2,
    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
    UPDATE score SET name=pName, birth=pBirth, kor=pKor, eng=pEng, mat=pMat 
    WHERE hak=pHak;
    COMMIT;
END;
/

EXEC updateScore('7878', '가가가', '2000-10-10', 80, 90, 90);

업데이트 프로시저

 

CREATE OR REPLACE PROCEDURE deleteScore 
(
    pHak In VARCHAR2
)
IS
BEGIN
    DELETE FROM score WHERE hak=pHak;
    COMMIT;
END;
/

EXEC deleteScore('7878');

삭제 프로시저

 

-- 데이터 개수
CREATE OR REPLACE PROCEDURE countScore
(
    pResult OUT NUMBER
)
IS
BEGIN
    SELECT COUNT(*) INTO pResult FROM score;
END;
/

데이터의 개수 구하는 프로시저

SELECT문의 결과를 pResult에 담는다.

 

자바로 결과 보낼때는 OUT 

자바에서 결과 받을때는 IN

 

 

-- 리스트
CREATE OR REPLACE PROCEDURE listSCore
(
    pResult OUT SYS_REFCURSOR,
    pStart IN NUMBER,
    pEnd IN NUMBER
)
IS
BEGIN
    
    OPEN pResult FOR
        SELECT * FROM (
            SELECT ROWNUM rnum, tb.* FROM (
                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
            ) tb WHERE ROWNUM <= pEnd
        ) WHERE rnum >= pStart;

END;
/

리스트 출력 프로시저

자바에서 start, end 파라미터 받아서 사용하는 모습

그리고 결과값은 내보내는 모습

 

-- 하나의 레코드 가져오기
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 
    FROM score
    WHERE hak = pHak;
END;
/

하나의 레코드 가져오는 프로시저