암시적 커서
SELECT문
형식)
SELECT select_list
INTO variable_name | record_name
FROM table
WHERE condition;
주의사항
반드시 하나의 행만 검색
검색되는 데이터행이 없거나 2개 이상일 경우 예외 발생
다수 개의 데이터행을 검색할 때는 명시적 커서 사용
INSERT문
형식)
DECLARE
v_no dept.deptno%TYPE;
v_name dept.dname%TYPE;
v_loc dept.loc%TYPE;
BEGIN
INSERT INTO dept
VALUES (50,'SALES','서울');
SELECT deptno, dname,loc
INTO v_no, v_name, v_loc
FROM dept WHERE deptno=50;
DBMS_OUTPUT.PUT_LINE('부서번호:'||TO_CHAR(v_no));
DBMS_OUTPUT.PUT_LINE('부서이름:'||v_name);
DBMS_OUTPUT.PUT_LINE('위치:'||v_loc);
END;
UPDATE문
형식)
DECLARE
v_no dept.deptno%TYPE;
v_name dept.dname%TYPE;
v_loc dept.loc%TYPE;
BEGIN
UPDATE dept SET loc='수원' WHERE deptno=50;
SELECT deptno, dname,loc
INTO v_no, v_name, v_loc
FROM dept WHERE deptno=50;
DBMS_OUTPUT.PUT_LINE('부서번호:'||TO_CHAR(v_no));
DBMS_OUTPUT.PUT_LINE('부서이름:'||v_name);
DBMS_OUTPUT.PUT_LINE('위치:'||v_loc);
END;
DELETE문
형식)
BEGIN
DELETE FROM dept
WHERE deptno=50;
END;
커서
SQL 처리 결과가 저장된 작업 영역에 이름을 지정하고 저장된 정보를 접근할 수 있게 함
SQL명령을 실행시키면 서버는 명령을 파싱하고 실행하기 위한 메모리 영역을 오픈하는데 이 영역을 커서라고 부른다.
커서의 종류
암시적 커서(Implicit Cursor)
: 모든 DML과 PL/SQL SELECT문에 암시적으로 PL/SQL이 선언
명시적 커서(Explicit Cursor)
: 프로그래머가 선언하고 명령하며 블럭의 실행 가능한 부분에서 특정 명령을 통해 조작
커서 : 암시적 커서
SQL문장이 처리되는 곳에 대한 익명의 어드레스
오라클 데이터 베이스에서 실행되는 모든 SQL문장 : 암시적 커서
SQL문이 실행되는 순간 자동으로 열림과 닫힘 실행
속성)
속성 | 설명 |
---|---|
SQL%ROWCOUNT | 해당 SQL문에 영향을 받는 행의 수 |
SQL%FOUND | 해당 SQL문의 영향을 받는 행의 수가 1개 이상일 경우 TRUE |
SQL%NOTFOUND | 해당 SQL문에 영향을 받는 행의 수가 없을 경우 TRUE |
SQL%ISOPEN | 암시적 커서가 열려 있는지의 여부 검색항상 FALSE(실행한 후 바로 커서를 닫기 때문) |
예)
DECLARE
v_sal emp.sal%TYPE;
BEGIN
SELECT sal
INTO v_sal
FROM emp
WHERE empno = 7934;
IF SQL%FOUND THENN
DBMS_OUTPUT.PUT_LINE('데이터 존재');
END IF;
UPDATE emp
SET sal = sal * 1.1
WHERE empno = 7934;
v_update_row := SQL%ROWCOUNT;
DBMS_OUTPUT.PUT_LINE('급여인상 사원수:'||TO_CHAR(v_update_row));
END;
명시적 커서
개념
커서 선언과 처리
커서 선언
DECLARE
CURSOR cursor_name IS
select문;
커서 연결
OPEN cursor_name;
:커서 안의 검색이 실행
:검색시 아무런 데이터 행을 추출하지 못할 경우 예외 발생
커서로부터의 데이터 패치
FETCH cursor_name INTO variable1, variable2, …;
:현재 데이터행을 OUTPUT변수에 리턴
:한 행씩 데이터를 패치
:주의할 점
→커서의 SELECT문의 컬럼의 수와 OUTPUT변수의 수가 동일해야 함
→커서의 SELECT문의 컬럼 데이터 타입과 OUTPUT변수의 데이터 타입이 동일해야함
커서 닫기
CLOSE cursor_name;
:사용을 끝낸 커서는 반드시 닫아야함
:필요하다면 커서를 OPEN을 통해 다시 열 수 있음
:커서를 닫은 상태에서 패치 할 수 없음
커서 선언과 처리 예)
DECLARE
CURSOR dept_cnt IS
SELECT dname, loc
FROM dept WHERE deptno = 10;
v_dname dept.dname%TYPE;
v_dloc dept.loc%TYPE;
BEGIN
OPEN dept_cnt;
FETCH dept_cnt INTO v_dname, v_dloc;
DBMS_OUTPUT.PUT_LINE('부서명:'||v_dname);
DBMS_OUTPUT.PUT_LINE('부서위치:'||v_dloc);
CLOSE dept_cnt;
END;
명시적 커서의 속성
속성
속성 | 설명 |
---|---|
%ROWCOUNT | 현재까지 반환된 모든 데이터 행의 수 |
%FOUND | Fetch한 데이터가 행을 리턴하면 TRUE |
%NOTFOUND | Fetch한 데이터가 행을 리턴하지 않으면 TRUE |
%ISOPEN | 커서가 열려 있으면 TRUE |
→%NOTFOUND : 루프 종료 시점을 검사할 때 사용
→%ROWCOUNT : 정확한 숫자만큼의 행 추출에 사용
→%ISOPEN : 커서가 열려있는지 검사할 때 사용
예)
DECLARE
v_empno emp.empno%TYPE;
v_ename emp.ename%TYPE;
CURSOR emp_list IS
SELECT empno, ename
FROM emp;
BEGIN
OPEN emp_list;
LOOP
FETCH emp_list INTO v_empno, v_ename;
EXIT WHEN emp_list%NOTFOUND;
DBMS_OPTPUT.PUT_LINE(v_ename);
END LOOP;
DBMS_OUTPUT.PUT_LINE
('전체데이터 수'||TO_CHAR(emp_list%ROWCOUNT));
CLOSE emp_list;
END;
FOR문에서 커서 사용
특징
: FOR문을 사용하면 커서의 OPEN, FETCH, CLOSE가 자동 발생하므로 따로 기술할 필요 없음
: 레코드 이름이 자동 선언되므로 따로 선언할 필요 없음
형식
FOR record_name IN cursor_name LOOP
statement;
.....
END LOOP
예시
DECLARE
CURSOR dept_cnt IS
SELECT b.dname, COUNT(a.empno) cnt
FROM emp a, dept b
WHERE a.deptno = b.deptno
GROUP BY b.dname;
BEGIN
FOR emp_list IN dept_cnt LOOP
DBMS_OUTPUT.PUT_LINE('부서명:'||emp_list.dname);
DBMS_OUTPUT.PUT_LINE('사원수:'||TO_CHAR(emp_list.cnt));
END LOOP;
END;
파라미터가 있는 커서
CURSOR cursor_name [(parameter_name datatype, …)]
IS
SELECT statement
DECLARE
CURSOR emp_list(v_deptno emp.deptno%TYPE) IS
SELECT ename
FROM emp
WHERE deptno = v_deptno;
BEGIN
DBMS_OUTPUT.PUT_LINE('**입력한 부서 직원**');
--패러미터 변수의 값을 전달(OPEN될 때 값 전달)
FOR emplst IN emp_list(10) LOOP
DBMS_OUTPUT.PUT_LINE('부서번호:'||emplst.ename);
END LOOP;
FOR emplst IN emp_list(20) LOOP
DBMS_OUTPUT.PUT_LINE('부서번호 20:'||emplst.ename);
END LOOP;
END;
예외 | 설명 | 처리 |
---|---|---|
미리 정의된 오라클 서버 예외 | PL/SQL에서 자주 발생하는 약 20개의 오류 | 선언할 필요도 없고, 발생시에 예외 절로 자동트랩 |
미리 정의되지 않은 오라클 서버 예외 | 미리 정의된 오라클 서버 오류를 제외한 모든 오류 | 선언부에서 선언해야하고 발생시 자동트랩 |
사용자 정의 예외 | 개발자가 정한 조건에 만족하지 않을 경우 발생하는 오류 | 선언부에서 선언하고 실행부에서 RAISE문을 사용하여 발생 |
EXCEPTION
WHEN exception1 [OR exception2 ...] THEN
statement;
...
[WHEN exception3 [OR exception4 ...] THEN
statement;
]
...
주의사항WHEN OTHERS절은 맨 마지막에 위치
예외 처리절은 EXCEPTION부터 시작
여러 개의 예외 처리부 허용
예외가 발생하면 여러 개의 예외 처리부 중 하나의 예외 처리부로 트랩(Trap)
미리 정의된 예외
NO_DATA_FOUND: SELET문이 아무런 데이터 행을 반환하지 못할 때
TOO_MANY_ROWS: SELECT문이 2개 이상의 행을 반환할 떄
ZERO_DIVIDE: 0으로 나눌 때
….
미리 정의된 예외 예
DECLARE
v_emp emp%ROWTYPE;
BEGIN
SELECT empno, ename, deptno
INTO v_emp.empno, v_emp.ename, v_emp.deptno
FROM emp
WHERE deptno = 10;
DBMS_OUTPUT.PUT_LINE('사번:'||v_emp.empno;
DBMS_OUTPUT.PUT_LINE('이름:'||v_emp.ename;
DBMS_OUTPUT.PUT_LINE('부서번호:'||v_emp.deptno;
EXCEPTION
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE('TOO_MANY_ROWS에러 발생');
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('NO_DATA_FOUND에러 발생');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('기타 에러 발생');
END;
미리 정의되지 않은 예외 처리방법
1단계 : 예외의 이름을 선언(선언절)
2단계 : PRAGMA EXCEPTION_INIT문장으로 예외의 이름과 오라클 서버 오류 번호를 결합(선언절)
→ PRAGMA : 컴파일러에게 직접 명령을 내리는 지시어
→ PRAGMA EXCEPTION_INIT : 예외 이름이 발생하면 오라클 서버 오류 번호로 컴파일러에게 지시
3단계 : 예외가 발생할 경우 해당 예외를 참조(예외절)
미리 정의되지 않은 예외 예)
DECLARE
not_null_test EXCEPTION; --1단계
PRAGMA EXCEPTION_INIT(not_null_test, -1400); --2단계
BEGIN
INSERT INTO emp(ename,deptno)
VALUES('tiger',30);
EXCEPTION
WHEN not_null_test THEN --3단계
DBMS_OUTPUT.PUT_LINE('not null 에러 발생');
END;
결과 : not null에러발생
사용자 정의 예외 : 오라클 저장함수 RAISE_APPLICATION_ERROR를 사용하여 오류코드 -20000부터 -20999의 범위 내에서 사용자 정의 예외 생성 가능
사용자 정의 예외 처리방법
1단계 : 예외의 이름을 선언(선언절)
2단계 : RAISE문을 사용하여 직접적으로 예외 발생(실행절)
3단계 : 예외가 발생할 경우 해당 예외를 참조(예외절)
사용자 정의 예외 예)
DECLARE
-- 예외 이름 선언
user_define_error EXCEPTION; --1단계
cnt NUMBER;
BEGIN
SELECT COUNT(empno)
INTO cnt
FROM emp
WHERE deptno = 10;
IF cnt < 5 THEN
--RAISE문을 사용하여 직접적으로 예외 발생
RAISE user_define_error; --2단계
END IF;
EXCEPTION
--예외가 발생할 경우 해당 예외를 참조
WHEN user_definn_error THEN --3단계
RAISE_APPLICATION_ERROR(-20001,'사원 부족');
END;
SQLCODE, SQLERRM
WHEN OTHERS문으로 트랩되는 오류들의 실제 오류 코드와 설명을 볼 때 사용
SQLCODE : 실행된 프로그램이 성공적으로 종료하였을 때는 오류번호 0을 포함하며, 그렇지 못할 경우에는 해당 오류코드 번호 포함
SQLERRM : SQLCODE에 포함된 오라클 오류 번호에 해당하는 메시지를 가짐
SQLCODE값 | 설명 |
---|---|
0 | 오류 없이 성공적으로 종료 |
1 | 사용자 정의 예외 번호 |
+100 | NO_DATA_FOUND 예외 번호 |
음수 | 위의 것을 제외한 오라클 서버 에러 번호 |