1. 저장 프로시저 (PROCEDURE)
사용자가 만든 PL/SQL문을 데이터베이스에 저장한다.
복잡한 DML문을 필요할 때마다 다시 입력할 필요 없이 간단하게 호출하여 복잡한 DML문의 실행 결과를 얻을 수 있다.
[ 형식 ]
CREATE [OR REPLACE] PROCEDURE procedure_name
( argument1 [MODE] data_type, -- argument는 매개변수
argument2 [MODE] data_type...-- MODE는 IN, OUT, INOUT 3가지 중 하나
)
IS
local_variable declaration
BEGIN
statement1; -- 실행되는 문장
statement2;
...
END;
/
프로시저 삭제 : DROP PROCEDURE 프로시저 이름명
오류 확인 : SHOW ERROR
(1) 매개변수 IN, OUT, IN OUT
1) IN 매개변수
외부에서 프로시저로 입력되는 값을 저장
2) OUT 매개변수
프로시저 내부에서 외부로 사용되는 값을 저장
3) INOUT 매개변수
IN, OUT의 두 가지 용도로 사용
예제) IN, OUT 매개 변수를 갖는 저장 프로시저 생성
1) 저장 프로시저 생성
CREATE OR REPLACE PROCEDURE SEL_EMPNO
( VEMPNO IN EMP.EMPNO%TYPE, -- 사원번호로 조회할 것이므로 IN으로 지정
VENAME OUT EMP.ENAME%TYPE, -- 사원번호로 얻어와 출력할 정보는 OUT으로 지정
VSAL OUT EMP.SAL%TYPE,
VJOB OUT EMP.JOB%TYPE
)
IS
BEGIN
SELECT ENAME, SAL, JOB INTO VENAME, VSAL, VJOB
FROM EMP
WHERE EMPNO = VEMPNO;
END;
/
2) 저장 프로시저 호출
-- 프로시저 수행 후 구해진 결과를 받아오기 위해 바인드 변수 선언
VARIABLE VAR_ENAME VARCHAR2(15);
VARIABLE VAR_SAL NUMBER;
VARIABLE VAR_JOB VARCHAR2(9);
EXECUTE SEL_EMPNO(7788, :VAR_ENAME, :VAR_SAL, :VAR_JOB) -- 생성된 저장 프로시저 실행
-- OUT 매개변수에서 값을 받아오기 위해서는 프로시저 호출시 변수 앞에 콜론(:)을 붙여야 한다.
3) 프로시저 결과값을 출력
PRINT VAR_ENAME
결과)
VAR_ENAME
---------
SCOTT
문제) 사원명으로 검색해서 해당 사원의 직급을 얻어오는 저장 프로시저 작성
CREATE OR REPLACE PROCEDURE SEL_EMPNAME
(VENAME IN EMP.ENAME%TYPE,VJOB OUT EMP.JOB%TYPE)
IS
BEGIN
SELECT JOB INTO VJOB FROM EMP
WHERE ENAME = VENAME;
END;
/
결과)
VARIABLE VAR_ENAME VARCHAR2(15);
VARIABLE VAR_JOB VARCHAR2(9);
PRINT VAR_ENAME
PRINT VAR_JOB
VAR_JOB
----------
ANALYST
2. 저장 함수 (FUNCTION)
저장 프로시저와 유사하며 결과를 되돌려 받을 수 있다. (RETURN값 있음)
[ 형식 ]
CREATE [OR REPLACE] FUNCTION function_name (
argument1 [mode] data_type,
argument2 [mode] data_type...
)
IS
RETURN data_type;
BEGIN
statement1;
statement2;
RETURN variable_name;
END;
[호출형식]
EXECUTE : variable_name := function_name(argument_list);
예제) 특별 보너스를 지급하기 위한 저장 함수 작성하기 (보너스는 급여의 200%)
CREATE OR REPLACE FUNCTION CAL_BONUS (VEMPNO IN EMP.EMPNO%TYPE)
RETURN NUMBER -- 함수 CAL_BONUS에 의해 되돌려지는 값은 수치 데이터
IS
VSAL NUMBER(7,2); -- 함수 CAL_BONUS 내부에서만 사용할 지역변수 VSAL 선언
BEGIN
SELECT SAL INTO VSAL -- 매개변수 VEMPNO로 사원을 조회하여 컬럼값을 얻어 VSAL에 저장
FROM EMP
WHERE EMPNO = VEMPNO;
RETURN (VSAL * 200); -- 조회결과로 얻어진 급여로 보너스를 구해서 함수 결과값 리턴
END;
Function CAL_BONUS이(가) 컴파일되었습니다.
VARIABLE VAR_RES NUMBER; -- 함수 결과값을 저장할 변수 선언
EXECUTE :VAR_RES := CAL_BONUS(7788); -- 함수 호출. 결과값은 := 연산자 왼쪽변수에 저장
PL/SQL 프로시저가 성공적으로 완료되었습니다.
PRINT VAR_RES; -- 변수 값을 출력하여 구해진 보너스 확인
결과)
VAR_RES
----------
600000
문제) 사원명으로 검색하여 해당 사원의 직급을 얻어오는 저장 함수를 작성
CREATE OR REPLACE FUNCTION SEL_EMPNAME02 (VENAME IN EMP.ENAME%TYPE)
RETURN VARCHAR
IS
VJOB EMP.JOB%TYPE;
BEGIN
SELECT JOB INTO VJOB
FROM EMP
WHERE ENAME = VENAME;
RETURN VJOB;
END;
3. 커서 (CURSOR)
처리 결과를 건건이 처리할 수 있는 기능. 단독으로 쓰지 않고 프로시저 안에 쓴다.
(1) 커서의 형식과 개념
암시적인 커서 : 일반적으로 사용하는 SQL문. 한 번 실행에 하나의 결과 값을 얻어온다.
명시적인 커서 : SQL문 결과가 여러 개의 행이 리턴될 때 사용. CURSOR~OPEN~FETCH~CLOSE로 진행
[ 형식 ]
DECLARE
CURSOR cursor_name IS statement; -- 커서 선언
BEGIN
OPEN cursor_name; -- 커서 열기
FETCH cursor_name INTO variable_name; -- 커서에서 데이터 읽어와 변수에 저장
CLOSE cursor_name; -- 커서 닫기
END;
1) DECLARE CURSOR (커서 선언)
명시적으로 CURSOR를 선언하기 위해 CURSOR 문장을 사용한다.저장한다.
CURSOR cursor_name IS SELECT문;
커서이름은 PL/SQL 식별자이며, SELECT문은 INTO가 없는 SELECT문이 온다.
2) OPEN CURSOR (커서 열기)
질의를 수행하고 검색조건을 충족하는 결과셋을 생성하기 위해 커서를오픈한다.
OPEN cursor_name;
3) FETCH CURSOR
FETCH문은 결과셋에서 로우 단위로 데이터를 읽어들인다. 각 인출(FETCH) 후 커서는 결과셋에서 다음 행으로 이동한다.
FETCH cursor_name INTO variable1, variable2...;
FETCH문은 현재 행에 대한 정보를 얻어온 뒤 INTO 뒤에 기술한 변수에 저장한다.
여러 개의 로우에 대한 결과값을 모두 처리하려면 LOOP 반복문 안에 FETCH문을 기술해야 한다.
커서가 끝에 위치하게 되면 EXIT로 반복문을 탈출한다. 탈출 조건은 EXIT WHEN 뒤에 쓴다.
4) 커서의 상태
커서 속성으로 FETCH문이 사용된 반복문에서 탈출 조건으로 쓸 수 있다.
%NOTFOUND : 커서 영역 자료가 모두 FETCH 되었다면 TRUE
%FOUND : 커서 영역에 FETCH 되지 않은 자료가 있다면 TRUE
%ISOPEN : 커서가 OPEN된 상태이면 TRUE
%ROWCOUNT : 커서가 얻어온 레코드의 개수
5) CLOSE CURSOR (커서 닫기)
SELECT문장이 다 처리된 후에는 CURSOR를 닫는다.
CLOSE cursor_name;
예제) 부서 테이블의 모든 내용을 조회하기
커서를 사용하여 부서 테이블의 모든 내용을 조회하는 예제
CREATE OR REPLACE PROCEDURE CURSOR_SAMPLE01
IS
VDEPT DEPT%ROWTYPE; -- 커서 실행 결과를 저장할 지역변수를 레퍼런스 변수로 선언
CURSOR C1 -- 커서 C1 선언
IS
SELECT * FROM DEPT; -- 부서 내용을 모두 조회하는 SELECT문을 커서에 담음
BEGIN
DBMS_OUTPUT.PUT_LINE('부서번호 / 부서명 / 지역명');
DBMS_OUTPUT.PUT_LINE('---------------------------');
OPEN C1;
LOOP -- SELECT문 결과가 여러개의 행이면 LOOP~END LOOP 반복문 이용
FETCH C1 INTO VDEPT.DEPTNO, VDEPT.DNAME, VDEPT.LOC; -- 커서가 한 개 행의 정보를 읽어와 INTO 뒤 변수에 저장
EXIT WHEN C1%NOTFOUND; -- 더이상 처리할 내용이 없으면 반복문 벗어남.
DBMS_OUTPUT.PUT_LINE(VDEPT.DEPTNO||' '||VDEPT.DNAME||' '||VDEPT.LOC);
END LOOP;
CLOSE C1;
END;
/
[ 형식 ]
FOR record_name IN cursor_name LOOP
satement1;
satement2;
...
END LOOP;
예제) 부서 테이블의 모든 내용을 출력하기
OPEN~FETCH~CLOSE 없이 FOR~LOOP~END LOOP문을 사용하여 더 간단하게 커서를 처리할 수 있다.
CREATE OR REPLACE PROCEDURE CURSOR_SAMPLE02
IS
VDEPT DEPT%ROWTYPE;
CURSOR C1
IS
SELECT * FROM DEPT;
BEGIN
DBMS_OUTPUT.PUT_LINE('부서번호 / 부서명 / 지역명');
DBMS_OUTPUT.PUT_LINE('---------------------------');
FOR VDEPT IN C1 LOOP
EXIT WHEN C1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(VDEPT.DEPTNO||' '||VDEPT.DNAME||' '||VDEPT.LOC);
END LOOP;
END;
/
EXECUTE CURSOR_SAMPLE02;
결과)
부서번호 / 부서명 / 지역명
---------------------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
'오라클 기초' 카테고리의 다른 글
| PL/SQL 패키지와 트리거 (0) | 2016.01.22 |
|---|---|
| PL/SQL 반복문: BASIC LOOP, FOR LOOP, WHILE LOOP (1) | 2016.01.20 |
| PL/SQL 선언문: IF~THEN~ELSIF~ELSE~END IF (0) | 2016.01.20 |
| PL/SQL 대입문: SELECT INTO, TABLE TYPE, RECORD TYPE (0) | 2016.01.20 |
| 동의어 (SYNONYM, 시노늄) (0) | 2016.01.20 |
