013 - [Oracle PL/SQL] Records - %type

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

 

What is a PL/SQL Record
A PL/SQL record is a composite data structure that is a group of related data stored in fields.
Each field in the PL/SQL record has its own name and data type.

Declaring a PL/SQL Record
1- programmer-defined records.
2- table-based record. %Rowtype
3- cursor-based record. ( will be covered later )

 

# 아래 2가지 표현 방식은 동일한 방식이다.

DECLARE
TYPE t_EMP IS RECORD
( V_EMP_id employees.employee_id%type,
  v_first_name employees.first_name%type,
  v_last_name employees.last_name%type
);

v_emp t_EMP;

BEGIN
  select employee_id,first_name,last_name
  into v_emp.V_EMP_id, v_emp.v_first_name,v_emp.v_last_name
  from
  employees 
  where employee_id=100;
  dbms_output.put_line(v_emp.V_EMP_id||' '||v_emp.v_first_name||' '||v_emp.v_last_name);
END;

------------------------------------------------------------------------

DECLARE
TYPE t_EMP IS RECORD
( V_EMP_id employees.employee_id%type,
  v_first_name employees.first_name%type,
  v_last_name employees.last_name%type
);

v_emp t_EMP;

BEGIN
  select employee_id,first_name,last_name
  into v_emp
  from
  employees 
  where employee_id=100;
  dbms_output.put_line(v_emp.V_EMP_id||' '||v_emp.v_first_name||' '||v_emp.v_last_name);
END;
------------------------------------------------------------------------
100 Steven King

 

 

 

for문을 이용하여 조회 및 출력

 

DECLARE
TYPE t_EMP IS RECORD
( V_EMP_id employees.employee_id%type,
  v_first_name employees.first_name%type,
  v_last_name employees.last_name%type
);

v_emp t_EMP;

BEGIN
  for i in 100..103
  loop
      select employee_id,first_name,last_name
      into v_emp
      from
      employees 
      where employee_id=i;
      dbms_output.put_line(v_emp.V_EMP_id||' '||v_emp.v_first_name||' '||v_emp.v_last_name);
  end loop;
  
END;
------------------------------------------------------------------------
100 Steven King
101 Neena Kochhar
102 Lex De Haan
103 Alexander Hunold