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;