PL/SQL 문장과 커서
*홍형경 저/ 뇌를 자극하는 오라클 PL/SQL 프로그래밍
-- IF문
set serveroutput on
declare
grade char(1);
begin
grade := 'B';
if grade = 'A' THEN
dbms_output.put_line('Excellent');
ELSIF grade = 'B' THEN
dbms_output.put_line('Good');
ELSIF grade = 'C' THEN
dbms_output.put_line('Fair');
ELSIF grade = 'D' THEN
dbms_output.put_line('Poor');
END IF;
END;
-- CASE문
declare
grade char(1);
begin
grade := 'B';
CASE grade
WHEN 'A' THEN
dbms_output.put_line('Excellent');
WHEN 'B' THEN
dbms_output.put_line('Good');
WHEN 'C' THEN
dbms_output.put_line('Fair');
WHEN 'D' THEN
dbms_output.put_line('Poor');
ELSE
dbms_output.put_line('Not Found');
END CASE;
END;
-- LOOP문 (1)
declare
test_number integer;
result_num integer;
begin
test_number := 1;
loop
result_num := 2 * test_number;
if result_num > 20 then
exit;
ELSE
dbms_output.put_line(result_num);
end if;
test_number := test_number + 1;
end loop;
end;
-- LOOP문(2)
declare
test_number integer;
result_num integer;
begin
test_number := 1;
loop
result_num := 2* test_number;
exit when result_num > 20;
dbms_output.put_line(result_num);
test_number := test_number +1;
end loop;
end;
※ (2)번 예제에서처럼 EXIT WHEN 문을 쓰면 IF 구문을 추가로 사용하지 않아도 되기 때문에 구문이 깔끔해진다.
-- WHILE LOOP 문
declare
test_number integer;
result_num integer;
begin
test_number := 1;
result_num := 0;
while result_num < 20 loop
result_num := 2 * test_number;
dbms_output.put_line(result_num);
test_number := test_number + 1;
end loop;
end;
-- FOR ... LOOP 문
declare
test_number integer;
result_num integer;
begin
test_number := 1;
result_num := 0;
dbms_output.put_line('<<first>>');
<<first>>
for test_number in 1..10 loop
result_num := 2 * test_number;
dbms_output.put_line(result_num);
end loop;
dbms_output.put_line('<<second>>');
result_num := 0;
<<second>>
for test_number in reverse 1..10 loop
result_num := 2 * test_number;
dbms_output.put_line(result_num);
end loop;
end;
-- GOTO문
declare
test_number integer;
result_num integer;
begin
test_number := 1;
result_num := 0;
GOTO second;
dbms_output.put_line('<<first>>');
<<first>>
FOR test_number IN 1..10 LOOP
result_num := 2 * test_number;
dbms_output.put_line(result_num);
end loop;
<<second>>
result_num := 0;
dbms_output.put_line('<<second>>');
FOR test_number IN REVERSE 1..10 LOOP
result_num := 2 * test_number;
dbms_output.put_line(result_num);
end loop;
END;ㅈ
-- NULL 문
NULL 문이 사용되는 목적 : 보통 NULL 문장은 예외처리시 자주 사용된다. 즉 사용자가 정의한 특정 예외가 발생하였을 경우나 EXCEPTION WHEN OTHERS, 즉 EXCEPTION 절에서 명시한 예외 이외의 오류가 발생하였을 경우에 NULL 문을 명시한다.
예제(1) IF문을 사용했을 경우
--> IF문 사용시 예외값에 대한 처리(ELSE절)이 없더라도 오류를 발생시키지 않는다.
declare
grade char(1);
begin
grade := 'Z';
if grade = 'A' THEN
dbms_output.put_line('Excellent');
ELSIF grade = 'B' THEN
dbms_output.put_line('Good');
ELSIF grade = 'C' THEN
dbms_output.put_line('Fair');
ELSIF grade = 'D' THEN
dbms_output.put_line('Poor');
END IF;
END;
예제(2) CASE문을 사용했을 경우
--> CASE문 사용시 1. WHEN절이 모든 가능한 경우를 포함하거나
2. 예외값에 대한 처리(ELSE절)가 반드시 포함되어야 한다.
그렇지 않을 경우 에러가 발생하게 된다.
declare
grade char(1);
begin
grade := 'Z';
CASE grade
WHEN 'A' THEN
dbms_output.put_line('Excellent');
WHEN 'B' THEN
dbms_output.put_line('Good');
WHEN 'C' THEN
dbms_output.put_line('Fair');
WHEN 'D' THEN
dbms_output.put_line('Poor');
END CASE;
END;
오류 보고 -
ORA-06592: CASE not found while executing CASE statement
ORA-06512: at line 6
06592. 00000 - "CASE not found while executing CASE statement"
*Cause: A CASE statement must either list all possible cases or have an
else clause.
*Action: Add all missing cases or an else clause.
이 경우 NULL 절을 이용하면 간단히 처리할 수 있다.
declare
grade char(1);
begin
grade := 'Z';
CASE grade
WHEN 'A' THEN
dbms_output.put_line('Excellent');
WHEN 'B' THEN
dbms_output.put_line('Good');
WHEN 'C' THEN
dbms_output.put_line('Fair');
WHEN 'D' THEN
dbms_output.put_line('Poor');
ELSE NULL; --> 아무것도 처리하지 않음
END CASE;
END;
-- 커서
정의: 쿼리에 의해 반환되는 결과셋(*메모리 상에 위치)에 접근하기 위해 사용되는 Cursor.
* 명시적 커서(explicit cursor)란 사용자가 직접 쿼리의 결과에 접근해서 이를 사용하기 위해 명시적으로 선언한 커서를 말한다.
커서 선언 --> 커서 오픈 --> 패치 --> 커서 닫기
* 묵시적 커서(implicit cursor)란 오라클 내부에서 각각의 쿼리 결과에 접근하여 사용하기 위한 내부적 커서라 할 수 있다.
(예제) 명시적 커서
DECLARE -- 커서 선언부
CURSOR emp_csr IS
SELECT employee_id
FROM hr.employees
WHERE department_id = 100;
emp_id hr.employees.employee_id%TYPE;
BEGIN
OPEN emp_csr; -- 커서 오픈
LOOP
FETCH emp_csr INTO emp_id; -- 커서 패치
EXIT WHEN emp_csr%NOTFOUND;
dbms_output.put_line(emp_id);
END LOOP;
CLOSE emp_csr; -- 커서 닫기
END;
(참고) 명시적 커서
이런식으로 쉽게 활용이 가능하다
DECLARE -- 커서 선언부
CURSOR emp_csr IS
SELECT employee_id, first_name, last_name
FROM hr.employees
WHERE department_id = 100;
emp_id hr.employees.employee_id%TYPE;
emp_f_name hr.employees.first_name%TYPE;
emp_l_name hr.employees.last_name%TYPE;
BEGIN
OPEN emp_csr; -- 커서 오픈
LOOP
FETCH emp_csr INTO emp_id, emp_f_name, emp_l_name; -- 커서 패치
EXIT WHEN emp_csr%NOTFOUND;
dbms_output.put_line(emp_id);
dbms_output.put_line(emp_f_name);
dbms_output.put_line(emp_l_name);
END LOOP;
CLOSE emp_csr; -- 커서 닫기
END;
--명시적 커서의 속성
%FOUND : 커서 오픈시 NULL, 패치 시 TRUE, 패치 완료시 FALSE 반환
%ISOPEN : 커서 오픈시 TRUE 반환
%NOTFOUND : 더 이상 패치할 로우가 없음을 의미
%ROWCOUNT: 커서 오픈시 0, 패치 1건당 1씩 증가
-- 묵시적 커서(Implicit Cursor)
선언, 오픈 등의 작업을 할 필요가 없음
명시적 커서의 속성을 묵시적 커서에도 동일하게 사용할 수 있음
가장 최근에 실행된 SQL문장에 대한 커서 = SQL커서
(예제1)
DECLARE
count1 NUMBER;
count2 NUMBER;
BEGIN
SELECT count(*)
INTO count1
FROM hr.employees
WHERE department_id = 100;
count2 := SQL%ROWCOUNT;
dbms_output.put_line('SELECT COUNT IS '||count1);
dbms_output.put_line('ROW COUNT IS '||count2);
END;
'Oracle_DB_Unix admin > (3)뇌자극 오라클 PL/SQL' 카테고리의 다른 글
| PL/SQL 서브프로그램 (0) | 2015.10.08 |
|---|---|
| PL/SQL 객체 (0) | 2015.10.04 |
| [Oracle] 인라인뷰 (0) | 2015.10.03 |
| [Oracle] 서브쿼리의 분류 (0) | 2015.10.03 |
| [Oracle] 연관성 있는 서브쿼리와 연관성 있는 서브쿼리 (0) | 2015.10.03 |