PL/SQL 객체

*뇌를 자극하는 오라클 프로그래밍 (홍형경 저)의 정리입니다.

(블럭1: Counter Is Null)

declare

  counter integer;

begin

  counter := counter +1;

  if counter is null then

    dbms_output.put_line('Result : COUNTER IS Null');

end if;

end;

/

set serveroutput on;

(블럭2: 구구단 출력)

declare

  counter integer;

  i integer;

begin

  for i in 1..10 loop

  counter := (2 * i);

  dbms_output.put_line(' 2 * '||i||' = '||counter);

  end loop;

end;

/

(블럭3: 예외출력하기)

- 수정 전

declare

  counter integer;

begin

  counter := 10;

  counter := counter / 0;

  dbms_output.put_line(counter);

end;

- 수정 후

declare

  counter integer;

begin

  counter := 10;

  counter := counter / 0;

  dbms_output.put_line(counter);

exception when others then

  dbms_output.put_line('ERRORS');

end;

- 수정 후 (2) - 에러 내용 명시

declare

  counter integer;

begin

  counter := 10;

  counter := counter / 0;

  dbms_output.put_line(counter);

exception when zero_divide then

  dbms_output.put_line('ERRORS');

end;

/

- 수정 후 (3) - 0으로 나누기 시도시 1로 나누도록

declare

  counter integer;

begin

  counter := 10;

  counter := counter / 0;

  dbms_output.put_line(counter);

exception when zero_divide then

  counter := counter / 1;

  dbms_output.put_line(counter);

end;

<콜렉션의 사용>

(예제)

declare

  type varray_test is varray(3) of integer;

    -- integer 형 요소 3개로 구성된 varray 타입 선언

  type nested_test is table of varchar2(10);

    -- varchar2(10)형 요소로 구성된 nested table 타입 선언 (최대값이 없다)

  type assoc_array_num_type is table of number index by pls_integer;

    -- 키는 PLS_INTEGER 형이며 값은 number형인 요소들로 구성된 associative array 타입1

  type assoc_array_str_type is table of varchar2(32) index by pls_integer;

    -- 키는 PLS_INTEGER 형이며 값은 varchar2(32)형인 요소들로 구성된 associative array 타입2

  type assoc_array_str_type2 is table of varchar2(32) index by varchar2(64);

  -- 키는 VARCHAR2(64) 형이며 값은 varchar2(32)형인 요소들로 구성된 associative array 타입3

  

  varray1 varray_test; -- varray1 : 변수 / varray_test : 타입(헷갈리면 안됨)

  nested1 nested_test; 

  

  assoc1 assoc_array_num_type; 

  assoc2 assoc_array_str_type;

  assoc3 assoc_array_str_type2;

  

  begin

  varray1 := varray_test(1,2,3);

  nested1 := nested_test('A','B','C','D');

  

  assoc1(3) := 33; -- 키는 3, 값은 33

  assoc2(2) := 'TT'; -- 키는 2 값은 TT

  assoc3('O') := 'ORACLE'; -- 키는 O, 값은 ORACLE

  assoc3('K') := 'KOREA'; -- 키는 K, 값은 KOREA

  

  dbms_output.put_line(varray1(1)); -- varray_test의 첫번째 요소값을 출력, 결과는 1

  dbms_output.put_line(nested1(2)); -- nested_test의 두번째 요소값을 출력, 결과는 B

  

  dbms_output.put_line(assoc1(3)); -- 키 값이 3인 요소값을 출력, 결과는 33

  dbms_output.put_line(assoc2(2)); -- 키 값이 2인 요소값을 출력, 결과는 TT

  dbms_output.put_line(assoc3('O')); -- 키 값이 O인 요소값을 출력, 결과는 ORACLE

  dbms_output.put_line(assoc3('K')); -- 키 값이 K인 요소값을 출력, 결과는 KOREA

  end;

  /

<사용자 정의 데이터 타입(User Defined Data Type, TYPE 객체>

콜렉션을 필요할 때마다 다시 정의해서 사용하는 것은 번거롭기 때문에, 자주 사용되는 콜렉션은 데이터베이스 객체 (TYPE)으로 지정하여 사용할 수 있다. 이것을 사용자 정의 데이터 타입이라고 한다.

  create type alphabet_typ as varray(26) of varchar(2);

  

  declare

    test_alph alphabet_typ;

  begin

    test_alph := alphabet_typ('A','B','C','D');

    

  dbms_output.put_line(test_alph(2));

  

  end;

  /

<콜렉션과 레코드>

콜렉션과 레코드: PL/SQL에서 제공하는 데이터 타입.

(1)콜렉션 : PL/SQL에서 사용되는 배열(Array)형태의 데이터 타입을 통칭하여 콜렉션이라고 한다.

  *배열(Array)이란? : 같은 데이터타입으로 구성된 요소(element)들의 집합.

  +콜렉션의 종류 : 총 3가지

    1. varray(variable array) : 고정 길이를 가진 배열. 선언시 전체 크기가 명시되어야 하며, 각 요소가 순서대로 참조(정의)되어야 한다. 각 요소는 인덱스(=순서번호)를 통해 접근된다.

    2. 중첩테이블(nested table) : varray와 흡사하나 전체 크기를 명시할 필요가 없다는 점, 각 요소가 순서대로 참조(정의) 될 필요가 없다는 점에서 차이점을 갖는다.

    3. Associative array(연관배열, index-by table) : 키와 값의 쌍으로 구성된 콜렉션. 각 요소는 키에 의해 접근된다.

  +참고: 콜렉션은 다른 프로그래밍 언어에서 사용하는 1차원 배열형태의 구조를 갖고 있음.

(2)레코드 : 테이블 형태(2차원)의 데이터 타입. 여러 개의 필드(=컬럼)으로 구성되어 있으며, 해당 필드는 각기 다른 데이터 타입을 가질 수 있다.

  +쓰임 : 레코드가 테이블 형태의 구조를 갖고 있으므로, 실제 대부분의 경우 테이블의 데이터를 읽어오거나 조작하기 위해 PL/SQL 블록 내에서 임시적인 데이터 저장소 역할을 수행한다.

   +참고: 레코드는 다른 프로그래밍 언어에서 사용하는 구조체(Structure) 형태를 갖고 있음.

<콜렉션과 레코드 - 구문형식>

  1. VARRAY

      TYPE 타입명 IS {VARRAY | VARYING ARRAY } (크기) OF 요소데이터 타입 [NOT NULL];

  2. Nested Table

      TYPE 타입명 IS TABLE OF 요소데이터타입 [NOT NULL];

  3. associative array

      TYPE 타입명 IS TABLE OF 요소데이터타입 [NOT NULL] INDEX BY [PLS_INTEGER | BINARY_INTEGER | VARCHAR2(크기)]

  4. 레코드

      TYPE 레코드이름 IS RECORD (필드1 데이터타입1, 필드2 데이터타입2, ...);

 

  참고1. %TYPE

      변수명 스키마명.테이블명.컬럼명%TYPE;

  참고2. %ROWTYPE

      레코드이름 스키마명.테이블명%ROWTYPE;

  참고3. 커서%ROWTYPE을 이용한 레코드 선언

      레코드이름 커서명%ROWTYPE;

예제- 레코드

declare

    -- type으로 선언한 레코드

    type record1 is record (dep_id number not null := 300,

                             dep_name varchar2(30),

                             man_id number, loc_id number);

    -- 위에서 선언한 record1을 받는 변수 선언

    rec1 record1;

    

    -- 테이블명%ROWTYPE을 이용한 레코드 선언

    rec2 hr.departments%rowtype;

    

    cursor c1 is

       select department_id, department_name, location_id

       from hr.departments

       where location_id = 1700;

       

    -- 커서명%rowtype을 이용한 레코드 선언

    rec3 c1%rowtype;

    

    begin

     -- record1 레코드 변수rec1의 dep_name 필드에 값 할당.

     rec1.dep_name := '레코드부서1';

     

     -- rec2 변수에 값 할당

     rec2.department_id := 400;

     rec2.department_name := '레코드부서2';

     rec2.location_id := 2700;

     

     -- rec1의 레코드 값을 departments 테이블에 INSERT

     INSERT INTO hr.departments values rec1;

     

     -- rec2의 레코드 값을 departments 테이블에 INSERT

     INSERT INTO hr.departments values rec2;

     

     -- 커서 오픈

     OPEN c1;

     

     loop

     -- 커서값을 rec3에 할당한다. 개별 값이 아닌 department_id, department_name, location_id 값이 레코드 단위로 할당된다.

     fetch c1 into rec3;

     dbms_output.put_line(rec3.department_id);

     exit when c1%notfound;

     end loop

     

     commit;

     exception when others then

     rollback;

     end;

     /