6. PL/SQL 조합 데이터 유형 작업

6. PL/SQL 조합 데이터 유형 작업

 

A. 조합 데이터 유형

§스칼라 타입과는 달리 다중 값을 가질 수 있음

§모든 관련 데이터를 ‘단일 단위’로 보유-> 쉬운 액세스, 수정

§2가지 유형

§1. PL/SQL 레코드

§2. PL/SQL 컬렉션

-연관배열(INDEX BY 테이블)

-중첩 테이블

-VARRAY

 

B. PL/SQL 레코드

§관련은 있지만 유사하지 않은 데이터를 논리적 단위로 처리할 때 이용

§다양한 유형의 변수 보유 가능

§집합적 논리 단위가 생성되어, 액세스와 조작이 쉬워짐

** 서로 다른 데이터 유형의 값을 저장하려는 경우, 한번에

    하나씩만 저장할 때 사용!

 

§‘필드’라고 하는 구성요소(of 스칼라, 레코드, INDEX BY TABLE)를 하나 이상 포함해야 함

§USER-DEFINED 데이터 유형

§테이블 행의 부분집합일 수 있음

§논리적 단위로 처리

§테이블에서 행을 패치하여 처리하는 데 편리

 

§각 레코드는 필요한 만큼 필드 가질 수 있음

§초기값 할당 가능, NOT NULL 정의 가능

§필드 초기화할 때 DEFAULT와 := 사용 가능

§선언부에서 RECORD 유형 정의 및, USER-DEFINED 유형 선언

§중첩 레코드를 선언하고 참조할 수 있음
(한 레코드가 다른 레코드의 구성요소가 되는 것)

 

C. PL/SQL 레코드 생성

 

[ TYPE 선언 ]

§DECLARE 섹션에서 레코드 “정의”

§레코드 유형의 내부 구성 요소 선언, 선택적으로 초기화

 

[ USER-DEFINED 레코드 선언 ]

§DECLARE 섹션에서 레코드 정의 후,
USER-DEFINED 레코드 선언

 

D. PL/SQL 레코드 구조

 

 

E. %ROWTYPE 속성

§DB 테이블 또는 뷰의 열 컬렉션에 따라 변수 선언

§%ROWTYPE 앞에는 DB 테이블 또는 뷰 이름을 접두어로 붙임

§레코드의 필드는 테이블 또는 뷰의 열에서
이름 및 데이터 타입을 가져온다.

 

§테이블 또는 뷰의 전체 행을 포함할 수 있는 레코드를 선언할 때 사용

§레코드는 커서나 커서 변수에서 패치한 전체 행을 저장할 수 있다.

§“ RECORD_NAME.FILED_NAME “ 형식으로 개별 필드를
참조할 때는 ‘점 표기법’ 사용

§필드 값 할당 “RECORD_NAME.FIELD_NAME := VALUE;

§SELECT 또는 FETCH 문을 사용하여 레코드에 값 할당 가능

 

F. PL/SQL 레코드 생성 예제

DECLARE

   TYPE t_rec IS RECORD

  (v_sal number(8),

   v_minsal number(8) default 1000,

   v_hire_date employees.hire_date%type,

   v_recl employees%rowtype);

   v_myrec t_rec;

BEGIN

   v_myrec.v_sal := v_myrec.v_minsal + 500;

   v_myrec.v_hire_date := sysdate;

   SELECT * INTO v_myrec.v_recl

  FROM employees WHERE  employee_id = 100;

   DBMS_OUTPUT.PUT_LINE(v_myrec.v_recl.last_name ||’ ‘||

   to_char(v_myrec.v_hire_date) ||’ ‘|| to_char(v_myrec.v_sal));

END;

 

G. %ROWTYPE 속성의 장점

 

§기본 데이터베이스 열의 개수와 데이터 타입을 알 필요 없음
런타임에 변경될 수 있음
-> 유지 관리가 단순화 됨. 기본 테이블이 변경되는 경우,
    이 속성을 사용하여 선언된 변수의 데이터 타입도 동적으로
    변경됨.

§DDL문이 테이블에서 열을 변경할 경우, PL/SQL 프로그램 단위가 무효화됨.
=> 프로그램 재컴파일되면 자동으로 새로운 테이블 형식 반영

§‘SELECT * 문’, ‘INSERT, UPDATE문 (행 레벨)’ 이용한
행 검색에 유용

ex)

1)

CREATE TABLE retired_emps

  (EMPNO NUMBER(4),  ENAME VARCHAR2(10),

   JOB VARCHAR2(9),  MGR NUMBER(4),

   HIREDATE DATE,   LEAVEDATE DATE,

   SAL NUMBER(7,2),   COMM NUMBER(7,2),

   DEPTNO NUMBER(2));

 

DECLARE

   v_employee_number number := 124;

   v_emp_rec employees%ROWTYPE;

BEGIN

   SELECT * INTO v_emp_rec FROM employees

   WHERE employee_id = v_employee_number;

   INSERT INTO retired_emps

   VALUES(v_emp_rec.employee_id, v_emp_rec.last_name,

              v_emp_rec.job_id, v_emp_rec.manager_id,

              v_emp_rec.hire_date, sysdate,

              v_emp_rec.salary, v_emp_rec.commission_pct,

              v_emp_rec.department_id);

END;

/

SELECT * FROM retired_emps;

2) INSERT

DECLARE

   v_employee_number number := 124;

   v_emp_rec retired_emps%ROWTYPE;

BEGIN

   SELECT employee_id, last_name, job_id, manager_id,

              hire_date, hire_date, salary, commission_pct,

       department_id

   INTO v_emp_rec FROM employees

   WHERE employee_id = v_employee_number;

   INSERT INTO retired_emps VALUES v_emp_rec;

END;

/

SELECT * FROM retired_emps;

3) update

SET VERIFY OFF

DECLARE

   v_employee_number number := 124;

   v_emp_rec retired_emps%ROWTYPE;

BEGIN

   SELECT * INTO v_emp_rec FROM retired_emps;

   v_emp_rec.leavedate :=CURRENT_DATE;

   UPDATE retired_emps SET ROW = v_emp_rec

   WHERE empno = v_employee_number;

END;

/

SELECT * FROM retired_emps;

 

H. PL/SQL 컬렉션

§데이터를 단일 단위로 처리하는 데 사용

§동일한 데이터 유형의 값을 저장하는 경우에 사용

§컬렉션 유형

-연관 배열 (INDEX BY 테이블)

-중첩 테이블

-VARRAY

 

I.  연관 배열(INDEX BY TABLE)

 

 

§연관 배열은 USER-DEFINED 조합 데이터 유형

§‘키-값’ 쌍의 집합

§연관 배열은 “두 개의 열”이 있는 PL/SQL 컬렉션

§정수 또는 문자열 데이터 타입의 PRIMARY KEY 열
-> 이 값을 인덱스로 사용하여 데이터 저장
***키 값은 순차적이지 않을 수도 있음

§스칼라 또는 레코드 유형의 열
-> 값을 보유

 

§연관 배열은 선언 시 채워지지 않음
=> 초기화 불가

§연관 배열을 채우려면 명시적 실행문 필요

§DB 테이블과 마찬가지로 연관 배열의 크기에도 제약 없음
=> 새 행이 추가됨에 따라 행 수가 동적으로 늘어날 수 있음

§키는 순차적이 아닐 수 있음

§키는 양수 및 음수일 수 있음

 

J. 연관 배열의 구조

 

 

[ UNIQUE KEY 열의 데이터 타입 ]

§BINARY_INTEGER / PLS_INTEGER인 숫자
number 보다 적은 저장영역 필요, 산술연산은
number 보다 빠름

§VARCHAR2 또는 하위 영역

[ VALUE 열의 데이터 타입 ]

§스칼라 타입

§레코드 타입

 

K. 연관배열 생성 단계

 

L. 연관배열 생성 및 액세스

 

M. INDEX BY TABLE 메소드

 

 

O. INDEX BY RECORD TABLE 옵션

§DB 테이블의 모든 필드에 대한 정보를 하나의 배열 정의에
저장할 수 있음

§%ROWTYPE 속성을 사용, 테이블의 한 행을 나타내는
레코드 선언

 

ex)

1)

DECLARE

   TYPE dept_table_type IS TABLE OF

     departments%ROWTYPE

   INDEX BY PLS_INTEGER;

   dept_table dept_table_type;

BEGIN

   SELECT * INTO dept_table(1) FROM departments

   WHERE department_id = 10;

   DBMS_OUTPUT.PUT_LINE(dept_table(1).department_id ||’ ‘||

     dept_table(1).department_name ||’ ‘||

   dept_table(1).manager_id);

END;

/

2)

DECLARE

  TYPE emp_table_type IS TABLE OF

     employees%ROWTYPE   INDEX BY PLS_INTEGER;

  my_emp_table   emp_table_type;

  max_count   NUMBER(3) := 104;

BEGIN

  FOR I IN 100..max_count

  LOOP

  SELECT * INTO my_emp_table(i) FROM employees

  WHERE employee_id = I;

  END LOOP;

  FOR I IN my_emp_table.FIRST..my_emp_table.LAST

  LOOP

  DBMS_OUTPUT.PUT_LINE(my_emp_table(i).last_name);

  END LOOP;

END;

/

 

P. 중첩테이블

 

§중첩 테이블의 기능은 연관 배열과 비슷하지만
중첩 테이블 구현에서 차이가 있음

§연관 배열과 달리 중첩 테이블은 DB에 저장 가능
-- 항상 순서 없이 저장됨

§중첩 테이블의 크기 역시 동적으로 증가
하지만! 최대 2GB

§UNIQUE KEY값이 음수일 수 없음
첫번째 열을 키로 참조하지만, 중첩 테이블에는 키 없음
-> 숫자열이 있음

 

§연관 배열과 달리 ‘INDEX BY’ 절이 없음

§초기화 가능, 초기화 하지 않으면 NULL로 초기화

 

Q. VARRAY

 

 

§가변 크기 배열이며, 크기에 제약이 있다는 점을 제외하고
‘연관 배열’과 비슷

§VARRAY 유형의 항목을 VARRAYs 라고 함

§VARRAYs 에는 고정된 상한값이 있음
-> 선언 시 상한값 지정해야 함!

§최대 크기: 2GB

 

§중첩 테이블과의 차이점: ‘물리적 저장 모드’
-- VARRAY의 크기가 4KB보다 작으면 VARRAY의 요소는
   테이블 데이터와 함께 인라인으로 저장된다. (중첩 테이블은
   순서 상관없이 항상 저장)

§SQL을 사용해서 DB에 VARRAY 유형 생성 가능

§VARRAY의 크기는 3으로 제한됨 -> 즉, 요소의 개수

§생성자 이용, 초기화 가능