PL/SQL 커서(CURSOR) ,프로시저(PROCEDURE),저장함수(FUNCTION)

 

 

• 커서 ( CURSOR )

 --오라클 서버에서 SQL문 실행할때마다 처리를 위한 메모리공간

 --Oracle은 프로시저 내부에서 커서Cursor를 사용할수 있음.

 --행의 집합을 다루기에 많은 편리한 기능을 제공

 --테이블에서 여러개의 행을 쿼리한 후 , 쿼리의 결과인 행 집합을 한 행씩 처리하기 위한 방식

*처리 순서

커서 선언 (CURSOR 커서이름 )  --> 커서열기 ( OPEN 커서이름) 

--> LOOP문으로 ( 커서에서 데이터 가져오기(FETCH) --> 데이터 처리  ) -->커서 닫기 (CLOSE 커서이름 )

 

%NOTFOUND  :  커서 영역의 자료가 모두 FETCH 됐었다면 TRUE

%FOUND  :  커서영역에 FETCH 되지않은 자료가 있다면 TRUE

%ISOPEN  :  커서가 OPEN 상태이면 TRUE

%ROWCOUNT  :  커서가 얻어 온 레코드의 개수

 

          EX )))   OPEN ~ LOOP~ FETCH ~ END LOOP ~ CLOSE

 

          EX ))  FOR  ~ LOOP ~ END LOOP

                   FOR문을 사용하면 OPEN~CLOSE 사용없이.

 

          EX )) 부서번호가 20일때  사원번호,사원이름,급여정보,부서번호 출력

 

          EX )) 사원테이블에서 MANAGER 인 특정직책 출력

 

 

 

 

 

• 프로시저 ( PROCEDURE )

--PL/SQL 을 저장해놓고 필요한 경우만 호출할때. 

--오라클에서 데이터베이스에 저장할수있도록 저장 프로시저라는것을 제공한다.

 

            EX ))

 

EMP01 데이터 삭제.

 

            EX ))  IN 매개변수   :적는값

aaa, bbb  행 삭제.

 

            EX ))  OUT 매개변수  : 받아오는값.

 

 

 

 

• 저장함수

            EX ))  형식  

CREATE OR REPLACE FUNCTION 함수명 (
    변수명 IN VARCHAR2     )
RETURN VARCHAR2 -- 반환되는 값의 datatype
    IS 
     변수 VARCHAR2(10); 
    BEGIN
        SELECT  리턴할값
        INTO    변수
        FROM    DUAL
        WHERE   1 = 변수;
    RETURN 변수; -- 리턴문 필수
   [EXCEPTION]
     EXCEPTION 처리
   [RETURN]
END;

 

--함수는 모두 입력 파라미터로 사용. ( IN, OUT 사용X )

--RETURN문으로 반환할 값의 데이터 형식을 지정하고 ,

  본문 안에서는 RETURN 문으로 하나의 값을 반환해야함.

--저장 프로시저와 거의 유사 , 실행결과를 RETURN 받는다는점이 다르다.

            EX ))  

            EX ))  부서번호로 부서명 찾기, 없으면 '해당부서없음 ' 출력

 

CREATE OR REPLACE FUNCTION GETDNAME 
(VDEPTNO IN DEPT_TBL.DEPTNO%TYPE)
RETURN DEPT_TBL.DNAME%TYPE
IS 
VDNAME DEPT_TBL.DNAME%TYPE;
BEGIN
SELECT DNAME INTO VDNAME FROM DEPT_TBL
WHERE DEPTNO = VDEPTNO;
RETURN VDNAME;
EXCEPTION
  WHEN OTHERS THEN
  RETURN '해당 부서 없음' ;
END;

VAR DDNAME NVARCHAR2(20);
EXECUTE :DDNAME:=GETDNAME(40);
PRINT DDNAME;