014 - [Oracle PL/SQL] Records - %rowtype

<샘플코드에서 사용한 데이터는 HR 스키마이고, 오라클 설치시 생성할 수 있는 기본 스키마 입니다>

 

select * from DEPARTMENTS
where department_id=10;
/*
------------------------------------------------------------
DEPARTMENT_ID DEPARTMENT_NAME                MANAGER_ID LOCATION_ID
------------- ------------------------------ ---------- -----------
           10 Administration                        200        1700

*/


# 테이블을 데이터는 제외하고 구조를 복사
create table copy_DEPARTMENTS
as select * from  DEPARTMENTS where 1=2;

# 새로운 테이블에는 데이터가 없는것을 확인
select * from copy_DEPARTMENTS;
--no rows selected

 

 

 

declare
# 레코드 정의
type t_dept is record
( v_dept_id       departments.department_id%type,
  v_dept_name     departments.department_name%type,
  v_dept_manager  departments.manager_id%type,
  v_dept_loc      departments.location_id%type
);

v_dept t_dept;

begin

  select department_id,department_name,manager_id,location_id
  into v_dept 
  from DEPARTMENTS  
  where department_id=10;

# 사용예시, 새로운 테이블에 데이터를 입력할때 레코드를 사용하면 편리함
  insert into copy_DEPARTMENTS values v_dept;
  /*
  insert into copy_DEPARTMENTS values (v_dept.v_dept_id,v_dept.v_dept_name,.....
  */
end;
/*
PL/SQL procedure successfully completed.
*/



select * from copy_DEPARTMENTS;
/*
DEPARTMENT_ID DEPARTMENT_NAME                MANAGER_ID LOCATION_ID
------------- ------------------------------ ---------- -----------
           10 Administration                        200        1700
*/

 

 

 

# using the %rowtype
# %rowtype 타입으로 레코드 정의를 간단하게 할수 있다.

declare
	v_dept DEPARTMENTS%rowtype;

begin

select department_id,department_name,manager_id,location_id
into v_dept 
from DEPARTMENTS  where department_id=10;

insert into copy_DEPARTMENTS values v_dept;
/*
insert into copy_DEPARTMENTS values (v_dept.department_id,v_dept.department_name,.....
*/

end;
/*
PL/SQL procedure successfully completed.
*/

select * from copy_DEPARTMENTS;
/*
DEPARTMENT_ID DEPARTMENT_NAME                MANAGER_ID LOCATION_ID
------------- ------------------------------ ---------- -----------
           10 Administration                        200        1700
           10 Administration                        200        1700
*/

 

 

 

# using the %rowtype  in update 

declare
	v_dept DEPARTMENTS%rowtype;

begin

    v_dept.department_id:=10;
    v_dept.department_name:='test';

# 정의되지않은 필드는 null 처리되므로 업데이트에서 사용할 경우 주의가 필요함
# 각 필드를 직접지정해서 업데이트하는 방식이 명확할때도 있음
    update copy_DEPARTMENTS
    set row=v_dept
    where rownum = 1;

end;
/*
PL/SQL procedure successfully completed.
*/



select rownum, a.* from copy_DEPARTMENTS a;
/*
    ROWNUM DEPARTMENT_ID DEPARTMENT_NAME                MANAGER_ID LOCATION_ID
---------- ------------- ------------------------------ ---------- -----------
         1            10 test                                                 
         2            10 Administration                        200        1700
*/