객체 타입 - 4

REF 데이터 타입

REF 데이터 타입은 다른 인스턴스에 대한 포인터 또는 참조를 제공한다.

REF 타입은 오라클의 내장 타입인데, 내부적으로는 객체 인스턴스의 OID(Object IDentifier)와 다른 몇 가지 정보를 결합한 값을 가진다. REF 타입은 다른 데이터 타입과 마찬가지로 PL/SQL 변수로 선언하여 동일하거나 호환되는 타입의 다른 인스턴스에 대한 참조를 할당받을 수도 있고, SQL문에서는 SELECT와 DML의 대상이 될 수 있으며 WHERE절에도 사용될 수 있다.

-- REF 데이터 타입을 사용한 객체 타입
CREATE OR REPLACE TYPE employee_type UNDER person_type ( -- person_type을 상속
	empno		NUMBER(4),		--사번
    sal			NUMBER(7,2),	--급여
    deptno		NUMBER(2),		--부서 번호
    mgr			REF employee_type,	-- 상급자 로우에 대한 논리적 포인터
    
    MEMBER	FUNCTION manager_name	RETURN VARCHAR2		-- 상급자명
 );
 /
 
CREATE OR REPLACE TYPE BODY employee_type
AS
	-- 사원(SELF)의 매니저 사번(mgr)에 해당하는 Row를 찾아 이름 반환
    MEMBER FUNCTION manager_name RETURN VARCHAR2		-- 상급자명
    IS
    	v_mgr employee_type;
    BEGIN
    	BEGIN
        	SELECT DEREF(mgr)		-- DEREF는 참조(REF)를 실제 값으로 바꿔주는 함수다.
              INTO v_mgr
              FROM DUAL;
            RETURN v_mgr.full_name();
         EXCEPTION WHEN OTHERS THEN
         	RETURN NULL;	--상급자가 없으면 NULL 반환
         END;
     END;
END;
/

위 예제에서 employee_type의 메소드 manager_name은 REF 타입의 속성 mgr을 사용하여 상급자의 이름을 출력한다.

예제에서는 함수 DEREF가 사용되었다. DEREF는 REF 속성에 저장된 OID 값을 사용하여 해당 OID가 가리키는 실제 인스턴스를 찾는 함수이다. 예제에서 DEREF(mgr)은 REF 타입의 속성 mgr이 가리키는 인스턴스를 가져오는데, 이는 employee_type의 인스턴스이다. REF는 데이터 타입이면서 동시에 함수이기도 하다. REF 함수와 DEREF 함수는 서로 반대 역할을 한다.

• REF 함수: 인스턴스를 매개변수로 받아서 인스턴스에 대한 참조(REF)를 반환한다.
• DEREF 함수: 인스턴스의 참조(REF)를 매개변수로 받아서 실제 인스턴스를 반환한다.

REF 함수와 DEREF 함수

-- REF 함수의 사용
REM employee_type의 객체 테이블 생성
CREATE TABLE tb_employee OF employee_type;

REM mgr 속성이 NULL인 로우 추가
INSERT INTO tb_employee VALUES('홍', '길동', '청학동', '일번지', 1000, 9900, 10, NULL);

REM INSERT SELECT문을 사용하여 mgr 값을 가지는 로우 객체 추가
INSERT INTO tb_employee
SELECT '전', '우치', '계룡산', '계룡사', 1002, 5000, 10, REF(e)
  FROM tb_employee e
 WHERE empno = 1000;
 
REM 삽입된 데이터 조회
COL 이름		FORMAT A10
COL 상급자	   FORMAT A10
SELECT e.full_name() 이름, e.manager_name() 상급자
  FROM tb_employee e;
  
이름		상급자
--------- -----------
홍 길동
전 우치	홍 길동

첫 번째 INSERT문에서는 mgr속성으로 NULL 값을 가지는(상급자가 없는) 로우 하나를 추가했다. 두 번째 INSERT문은 mgr 속성의 값으로 첫 번째 INSERT문에서 생성한 로우에 대한 REF를 삽입하기 위해 INSERT SELECT문을 사용했다. REF(e)는 SELECT문이 추출한 로우에 대한 참조(포인터)값을 반환하고(e는 FROM절에 사용된 tb_employee의 Alias이다.), INSERT문이 이 참조 값을 mgr 속성을 위한 값으로 사용하여 테이블에 삽입한다.

 

다음 예제는 person_type에서 상속받은  employee_type의 전체 소스 코드이다.

-- employee_type 전체 소스 코드
DROP TABLE tb_employee;

CREATE OR REPLACE TYPE employee_type UNDER person_type ( -- person_type을 상속
	empno		NUMBER(4),		-- 사번
    sal			NUMBER(7,2),	-- 급여
    deptno		NUMBER(2),		-- 부서 번호
    mgr			REF employee_type,  -- 상급자 로우에 대한 논리적 포인터
    
    OVERRIDING MEMBER FUNCTION display RETURN VARCHAR2,		-- person_type의 display 재정의
    MEMBER FUNCTION dept_name		RETURN VARCHAR2,		-- 부서명
    MEMBER FUNCTION manager_name	RETURN VARCHAR2,		-- 상급자명
    MEMBER PROCEDURE validate_sal(SELF IN OUT employee_type),		-- 급여 정합성 검사
    MEMBER FUNCTION gross_sal		RETURN VARCHAR2,				-- 급여 총액 Method 1
    MEMBER FUNCTION gross_sal(a_incentive number) RETURN VARCHAR2,  -- 급여 총액 Method 2
    CONSTRUCTOR FUNCTION  employee_type(  -- 생성자
    						a_family_name	VARCHAR2, a_given_name	VARCHAR2,
                            a_address_1		VARCHAR2, a_address_2 	VARCHAR2,
                            a_empno			NUMBER,	  a_sal			NUMBER,
                            a_deptno		NUMBER
                          ) RETURN SELF AS RESULT,
    CONSTRUCTOR FUNCTION  employee_type(	-- 생성자
    						a_family_name	VARCHAR2, a_given_name VARCHAR2,
                            a_address_1		VARCHAR2, a_address_2  VARCHAR2,
                            a_empno			NUMBER,	  a_sal		   NUMBER
                          ) RETURN SELF AS RESULT
);
/

CREATE OR REPLACE TYPE BODY employee_type
AS
	-- person_type의 display를 재정의(Override)
    OVERRIDING MEMBER FUNCTION display RETURN VARCHAR2
    IS
    BEGIN
    	-- 슈퍼타입의 메소드는 (SELF AS 슈퍼타입명).을 접두어로 하여 호출한다.
        RETURN (SELF AS person_type).display ||', 사번:'|| empno ||
        		', 급여:'|| NVL(sal, 0) ||', 부서 번호:' || deptno;
    END;
    
    -- 사원의 부서명 반환
    MEMBER FUNCTION dept_name RETURN VARCHAR2
    IS
    	v_dname dept.dname%TYPE;
    BEGIN
    	SELECT a.dname INTO v_dname
          FROM dept a
         WHERE a.deptno = SELF.deptno;
        RETURN v_dname;
    END;
    
    -- 현재 사원(SELF)의 매니저 사번(mgr)에 해당하는 Row를 찾아 이름 반환
    MEMBER FUNCTION manager_name RETURN VARCHAR2		-- 상급자명
    IS
    	v_mgr employee_type;
    BEGIN
      BEGIN
      	SELECT DEREF(mgr)	 -- DEREF는 참조(REF)를 실제 값으로 바꿔주는 함수
          INTO v_mgr
          FROM DUAL;
        RETURN v_mgr.full_name();
      EXCEPTION WHEN OTHERS THEN
      	RETURN NULL;	-- 상급자가 없으면 NULL 반환
      END;
    END;
    
   --급여 정합성을 검사하고 부당한 부분을 바로잡는다.
   MEMBER PROCEDURE validate_sal(SELF IN OUT employee_type)
   IS
   	 c_minimum_salary CONSTANT NUMBER := 500;	-- 최저 임금
   BEGIN
   	IF sal < c_minimum_salary THEN
       sal := c_minimum_salary;
       DBMS_OUTPUT.PUT_LINE('사원 ' || SELF.full_name()|| '(사번 ' || empno ||
         ')의 급여가 최저 임금보다 작습니다. 최저 임금으로 보정되었습니다.');
    END IF;
   END;
 
 	-- 사원의 급여를 반환
    MEMBER FUNCTION gross_sal RETURN VARCHAR2
    IS
    BEGIN
    	RETURN NVL(sal, 0);
    END;
    
    -- 사원의 급여 + 인센티브를 반환
    MEMBER FUNCTION gross_sal(a_incentive number) RETURN VARCHAR2
    IS
    BEGIN
    	RETURN NVL(sal, 0) + NVL(a_incentive, 0);
    END;
    
    -- 기본 생성자가 아닌 추가 생성자 정의
    CONSTRUCTOR FUNCTION employee_type(a_family_name VARCHAR2, a_given_name VARCHAR2,
    								   a_address_1	 VARCHAR2, a_address_2  VARCHAR2,
                                       a_empno		 NUMBER,   a_sal		NUMBER,
                                       a_deptno		 NUMBER) RETURN SELF AS RESULT
    IS
    BEGIN
    	family_name := a_family_name;
        given_name 	:= a_given_name;
        address_1	:= a_address_1;
        address_2	:= a_address_2;
        empno		:= a_empno;
        sal			:= a_sal;
        deptno		:= a_deptno;
        mgr			:= NULL;
        RETURN;
    END;
    
    -- 기본 생성자가 아닌 추가 생성자 정의
    CONSTRUCTOR FUNCTION employee_type(a_family_name VARCHAR2, a_given_name VARCHAR2,
    								   a_address_1	 VARCHAR2, a_address_2	VARCHAR2,
                                       a_empno		 NUMBER,   a_sal		NUMBER
                                      ) RETURN SELF AS RESULT
    IS
    BEGIN
    	family_name	:= a_family_name;
        given_name	:= a_given_name;
        address_1	:= a_address_1;
        address_2	:= a_address_2;
        empno		:= a_empno;
        sal			:= a_sal;
        deptno		:= NULL;
        mgr			:= NULL;
        RETURN;
   END;
END;
/

객체를 테이블에 저장하는 방법

객체는 테이블에 저장하는 방법이 두 가지이다.

1. 객체 테이블로 저장: 객체만을 저장한다. 객체 테이블에 저장된 각 로우는 객체이며, 이를 로우 객체라고 한다. 

-- 객체 저장 예제를 위한 객체 테이블 생성
REM person_type의 객체 테이블 생성
CREATE TABLE tb_person OF person_type;
ALTER TABLE tb_person ADD CONSTRAINT pk_person PRIMARY KEY(family_name, given_name);

REM employee_type의 객체 테이블 생성
CREATE TABLE tb_employee OF employee_type;
ALTER TABLE tb_employee ADD CONSTRAINT pk_employee PRIMARY KEY(empno);

2. 관계형 테이블의 칼럼 객체로 저장: 다른 데이터 타입과 객체 타입을 같이 저장한다. 관계형 테이블에서 객체 타입은 칼럼에 저장될 수 있는데, 이를 칼럼 객체라고 한다.

-- 객체 저장 예제를 위한 관계형 테이블 생성
CREATE TABLE tb_emp(
	person	person_type,		-- 칼럼 객체 선언
    empno	NUMBER(4),			-- 사번
    sal		NUMBER(7,2),		-- 급여
    deptno  NUMBER(2),			-- 부서 번호
    mgr		REF employee_type   -- 상급자에 대한 참조
);

첫 번째 저장 방법인 객체 테이블로 저장된 데이터는 두 가지로 해석할 수 있다.

• 단일 칼럼 형식: 하나의 칼럼을 가지는 테이블로, 칼럼에는 객체 타입 person_type을 가지는 로우 인스턴스가 저장되어 있다.
• 멀티 칼럼 형식: 여러 칼럼을 가지는 테이블로, 객체 타입 person_type과 동일한 속성을 가지는 로우가 저장되어 있다.

-- 단일 칼럼 형식과 멀티 칼럼 형식을 사용하여 테이블 tb_person에 데이터 INSERT
REM 단일 칼럼 형식
INSERT INTO tb_person VALUES(person_type('홍', '길동', '청학동 일번지', ''));
INSERT INTO tb_person VALUES(person_type('김', '삿갓'));
REM 멀티 칼럼 형식
INSERT INTO tb_person VALUES('손', '오공', '화과산 수렴동', '동굴');

-- 다양한 방법을 사용하여 테이블 tb_employee에 데이터 INSERT
REM 단일 컬럼 형식
INSERT INTO tb_employee VALUES(employee_type('홍', '길동', '청학동', '일번지', 1000, 9900, 10));

REM 멀티 칼럼 형식
INSERT INTO tb_employee VALUES('손', '오공', '화과산 수렴동', '동굴', 1001, 9900, 10, NULL);

REM INSERT SELECT문을 사용한 로우 객체 추가
INSERT INTO tb_employee
SELECT '전', '우치', '계룡산', '계룡사', 1002, 5000, 10, REF(e)
  FROM tb_employee e
 WHERE empno = 1000;
 
REM REF 변수를 사용한 로우 객체 추가
DECLARE
	v_ref_emp REF employee_type;
BEGIN
	SELECT REF(E) INTO v_ref_emp
      FROM tb_employee E
     WHERE empno = 1001;
     
     INSERT INTO tb_employee
     	VALUES('제갈', '공명', '촉한', '성도', 1003, 5000, 10, v_ref_emp);
END;

멀티 칼럼과 단일 칼럼으로 구별되는 두 가지 해석 방식에 따라서 쿼리문에서도 이에 대응하는 두 가지 방식의 쿼리가 사용될 수 있다.

-- 멀티 칼럼과 단일 칼럼 해석 방식에 따른 쿼리문
SCOTT> REM 하나의 칼럼을 가지는 테이블로 해석한 쿼리
SCOTT> COL VALUE FORMAT A70
SCOTT> SELECT VALUE(a) AS "VALUE"
		 FROM tb_person a
        ORDER BY family_name, given_name;

VALUE(FAMILY_NAME, GIVEN_NAME, ADDRESS_1, ADDRESS_2);
------------------------------------------------------------------------
PERSON_TYPE('김', '삿갓', NULL, NULL)
PERSON_TYPE('손', '오공', '화과산 수렴동', '동굴')
PERSON_TYPE('홍', '길동', '청학동 일번지', NULL)

SCOTT> REM 여러 칼럼을 가지는 테이블로 해석한 쿼리
SCOTT> COL FAMILY_NAME FORMAT A11
SCOTT> COL GIVEN_NAME  FORMAT A10
SCOTT> COL ADDRESS_1   FORMAT A20
SCOTT> COL ADDRESS_2   FORMAT A20
SCOTT> SELECT a.*
		 FROM tb_person a
        ORDER BY family_name, given_name;
        
FAMILY_NAME	GIVEN_NAME	ADDRESS_1			ADDRESS_2
----------- ----------  ------------------  ---------------------
김			삿갓		   
손			오공	 	  화과산 수렴동		 동굴
홍			길동		  청학동 일번지