MLP) [Oracle] PL/SQL - 함수(Function) / 커서(Cursor)

함수(Function)

• 프로시저의 각 프로세스를 수행하기 위해 필요한 기능
• 일반적인 프로그래밍 언어에서 사용되는 함수와 같이 복잡한 프로그래밍 지원

 

함수 문법

CREATE OR REPLACE FUNCTION 함수명(
        매개변수명1 데이터타입,
        매개변수명2 데이터타입, ...
)
RETURN 데이터타입
IS | AS
        변수 및 상수 선언
BEGIN
        실행 문장
        RETURN 반환값
        EXCEPTION 문장
END;

 

프로시저와 함수의 차이

프로시저(Procedure)	함수(Function)
특정 작업 수행	특정 계산 수행
리턴값이 없을 수도 있음	리턴값이 반드시 존재해야 함
리턴값을 여러 개 가질 수 있음	리턴값을 하나만 가질 수 있음
서버(DB)에서 기술	클라이언트에서 기술
수식내에서 사용 불가	수식내에서만 사용 가능
단독으로 문장 구성 가능	단독으로 문장 구성 불가

 

 

함수 사용 예제

--사원명을 인파라미터로 전달하면 해당 사원의 사번을 반환하는 함수를 구성해보자.
CREATE OR REPLACE FUNCTION get_empno(name IN EMP.ENAME%TYPE)
RETURN EMP.EMPNO%TYPE
IS
    vno EMP.EMPNO%TYPE;
BEGIN
    SELECT empno
    INTO vno
    FROM emp WHERE ename = upper(name);
    RETURN vno;
    dbms_output.put_line(name||'님의 사번은 '||vno||'입니다');
END;
/
--함수 실행
SELECT get_empno('king') FROM dual;

실행 결과

 

---

커서(Cursor)

• 일반 프로그래밍 언어의 파일 처리 방법과 유사
• 행의 집합을 다룰 수 있는 편리한 기능 제공
• 테이블에서 여러 개의 행을 질의 후, 질의 결과인 행 집합을 한 행씩 처리
• 프로시저 내부에서 커서 사용

 

커서 처리 순서

 

커서의 종류

1. 묵시적 커서
   • 오라클 내부에서 자동으로 생성되어 사용하는 커서
   • (INSERT, UPDATE, DELETE, SELECT INTO)이 실행될 때마다 자동으로 만들어져 사용된다.
2. 명시적 커서
   • 사용자가 직접 정의해서 사용하는 커서
   • 자바의 ResultSet과 비슷한 개념

---

1. 묵시적 커서

--[실습] 사번을 인파라미터로 전달하면 해당 사원의 급여를 출력하고
--급여를 10% 인상한 뒤, 급여가 인상된 사원수를 출력하는 프로시저 작성
-------------------------------------
CREATE OR REPLACE PROCEDURE implicit_cr (pno IN emp.empno%TYPE)
IS
    vsal emp.sal%TYPE;
    v_cnt NUMBER;
BEGIN
    SELECT sal
    INTO vsal
    FROM emp WHERE empno = pno;
    --검색한 데이터가 있다면
    IF SQL%FOUND THEN
        dbms_output.put_line('검색한 데이터가 있어요. 급여는 '||vsal||'입니다.');
    END IF;
    UPDATE emp SET sal=sal*1.1 WHERE empno=pno;
    --묵시적 커서를 이용해 수정된 행의 수를 저장
    v_cnt := SQL%ROWCOUNT;
    --ROWCOUNT: SELECT INTO, INSERT, UPDATE, DELETE 문의 영향을 받은 ROW의 개수 반환
    dbms_output.put_line('급여가 10% 인상된 사원수는 '||v_cnt||'명 입니다.');
END;
/
-------------------------------------
EXEC implicit_cr(7788); --급여가 10% 인상된 사원수는 1명 입니다.
SELECT * FROM emp WHERE empno=7788; --3000에서 3300으로 인상됨

 

묵시적 커서를 이용하면 다수의 행을 조회할 수 없다.다수의 행을 조회하려고 시도하면 too_many_rows exception이 발생한다.

다중행의 결과를 받아오려면 명시적 커서를 이용하거나 FOR 루프를 이용하는 방법이 있다.

 

2. 명시적 커서

CURSOR 커서명 IS
SELECT문
1. 커서 열기 - OPEN 커서명
2. 데이터 인출 - FETCH 커서명 INTO 변수
    ※ FETCH할 때 반복문이 필요함 (LOOP문, WHILE LOOP문, FOR LOOP문 등을 이용)
3. 커서 닫기 - CLOSE 커서명

 

--BBS테이블을 CURSOR을 이용하여 최신 순으로 출력해보자.
CREATE OR REPLACE PROCEDURE bbs_all
IS
    K BBS%ROWTYPE;
    --커서 선언
    CURSOR bcr IS
    SELECT no, title, writer FROM bbs
    ORDER BY no DESC;
BEGIN
    -- 커서 열기
    OPEN bcr;
    -- 반복문 돌면서 데이터 인출
    LOOP
    -- FETCH INTO
    FETCH bcr INTO K.no, K.title, K.writer;
    EXIT WHEN bcr%NOTFOUND;
    dbms_output.put_line(RPAD(K.no, 6)||RPAD(K.title, 20)||RPAD(K.writer,10));
    END LOOP;
    -- 커서 닫기
    CLOSE bcr;
END;
/
--------------------------------------
EXEC bbs_all;

실행 결과