이펙티브 오라클 (2009년)
%TYPE과 %ROWTYPE을 사용하라 0 0 42,728

by 구루비스터디 %TYPE %ROWTYPE PLSQL [2018.05.26]


PL/SQL은 코드에 사용된 프로그램 변수와 테이블의 데이터 유형이 완벽하게 동기화 되는 쉬운 방법을 제공한다.


변수 유형

case 1

  • Oracle 7.3 이하 에선 정상 실행
  • Oracle 8 이상 에선 ORA-06502 발생
  • user_con_columns.column_name 이 7.3 이하 에선 VARCHAR2(30), 8 이상 에선 VARCHAR2(4000)



declare
  l_cname varchar2(30);
begin

  select column_name into l_cname
    from user_cons_columns
   where constraint_name = 'CHECK_CONS'
      and position is null;

  dbms_output.put_line ( l_cname );
end;
/


case 2

  • 항상 정상 실행
  • user_con_columns.column_name%type 사용으로 VARCHAR2 길이 차이로 인한 문제 예방됨

declare
  l_cname user_cons_columns.co
lumn_name%type;
begin

  select column_name into l_cname
    from user_cons_columns
   where constraint_name = 'CHECK_CONS'
      and position is null;

  dbms_output.put_line ( l_cname );
end;
/


레코드 유형 데모#1

테이블


create or replace package demo_pkg
as
  procedure process_data ( p_inputs in varchar2 );
end;
/

create or replace package body demo_pkg
as
  type emp_rec is record
  (
    empno number(4),
    ename varchar2(10),
    job varchar2(9),
    mgr number(4),
    hiredate date,
    sal number(7,2),
    comm number(7,2),
    deptno number(2)
  );

  procedure process1 ( p_record in emp%rowtype )
  is
  begin
    null;
  end;

  procedure process2 ( p_record in emp_rec )
  is
  begin
    null;
  end;

  procedure process_data ( p_inputs in varchar2 )
  is
  begin
    for x in (select * from emp where ename like p_inputs)
    loop
      process1(x);
    end loop;

    for x in (select * from emp where ename like p_inputs)
    loop
      process2(x);
    end loop;
  end;

end;
/


커서


create or replace package demo_pkg
as
  procedure process_data ( p_inputs in varchar2 );
end;
/

create or replace package body demo_pkg
as
  cursor c1 (p_inputs in varchar2)
  is
  select emp.ename, emp.hiredate, dept.dname
    from emp, dept
   where emp.deptno = dept.deptno
     and emp.ename like p_inputs;

  procedure process ( p_record in c1%rowtype )
  is
  begin
    null;
  end;

  procedure process_data ( p_inputs in varchar2 )
  is
  begin
    for x in c1(p_inputs)
    loop
      process(x);
    end loop;
  end;

end;
/


커서(템플릿 커서/동적 SQL)


create or replace package demo_pkg
as
  procedure process_data ( p_cname in varchar2, p_inputs in varchar2 );
end;
/

create or replace package body demo_pkg
as
  cursor TEMPLATE_cursor
  is
  select emp.ename, emp.hiredate, dept.dname
    from emp, dept;

  type rc is ref cursor;

  procedure process ( p_record in template_cursor%rowtype )
  is
  begin
    null;
  end;

  procedure process_data ( p_cname in varchar2, p_inputs in varchar2 )
  is
    l_cursor rc;
    l_rec template_cursor%rowtype;
  begin
    open l_cursor
    for
    'select emp.ename, emp.hiredate, dept.dname
      from emp, dept
     where emp.deptno = dept.deptno
         and emp.' || p_cname || ' like :x'
    using p_inputs;

    loop
      fetch l_cursor into l_rec;
      exit when l_cursor%notfound;
      process(l_rec);
    end loop;
    close l_cursor;
  end;

end;
/


데모

테이블

데모#1 - 레코드 유형 - 테이블


SQL> exec demo_pkg.process_data( '%' );

PL/SQL 처리가 정상적으로 완료되었습니다.

SQL> alter table emp add x number;

테이블이 변경되었습니다.

SQL> alter package demo_pkg compile body;

경고: 컴파일 오류와 함께 패키지 본문이 변경되었습니다.

SQL> show errors package body demo_pkg;
PACKAGE BODY DEMO_PKG에 대한 오류:

LINE/COL ERROR
-------- -----------------------------------------------------------------
37/7     PL/SQL: Statement ignored
37/7     PLS-00306: 'PROCESS2' 호출 시 인수의 갯수나 유형이 잘못되었습니다
SQL> alter table emp drop column x;

테이블이 변경되었습니다.

SQL> alter package demo_pkg compile body;

패키지 본문이 변경되었습니다.

SQL> show errors package body demo_pkg;
오류가 없음.
SQL> alter table emp modify ename varchar2(11);

테이블이 변경되었습니다.

SQL> update emp set ename = rpad( ename, 11, 'x' );

28 행이 갱신되었습니다.

SQL> exec demo_pkg.process_data( '%' );
BEGIN demo_pkg.process_data( '%' ); END;

*
1행에 오류:
ORA-06502: PL/SQL: 수치 또는 값 오류: 문자열 버퍼가 너무 작습니다
ORA-06512: "OCSTUDY.DEMO_PKG", 줄 37에서
ORA-06512: 줄 1에서


커서

데모#1 - 레코드 유형 - 커서


SQL> exec demo_pkg.process_data( '%' );

PL/SQL 처리가 정상적으로 완료되었습니다.

SQL> alter table emp add x number;

테이블이 변경되었습니다.

SQL> alter package demo_pkg compile body;

패키지 본문이 변경되었습니다.

SQL> show errors package body demo_pkg;
오류가 없음.
SQL> alter table emp drop column x;

테이블이 변경되었습니다.

SQL> alter package demo_pkg compile body;

패키지 본문이 변경되었습니다.

SQL> show errors package body demo_pkg;
오류가 없음.
SQL> alter table emp modify ename varchar2(11);

테이블이 변경되었습니다.

SQL> update emp set ename = rpad( ename, 11, 'x' );

28 행이 갱신되었습니다.

SQL> exec demo_pkg.process_data( '%' );

PL/SQL 처리가 정상적으로 완료되었습니다.


커서(템플릿 커서/동적 SQL)

데모#1 - 레코드 유형 - 커서(템플릿 커서/동적 SQL)


SQL> exec demo_pkg.process_data( 'ENAME', '%' );

PL/SQL 처리가 정상적으로 완료되었습니다.

SQL> alter table emp add x number;

테이블이 변경되었습니다.

SQL> alter package demo_pkg compile body;

패키지 본문이 변경되었습니다.

SQL> show errors package body demo_pkg;
오류가 없음.
SQL> alter table emp drop column x;

테이블이 변경되었습니다.

SQL> alter package demo_pkg compile body;

패키지 본문이 변경되었습니다.

SQL> show errors package body demo_pkg;
오류가 없음.
SQL> alter table emp modify ename varchar2(11);

테이블이 변경되었습니다.

SQL> update emp set ename = rpad( ename, 11, 'x' );

28 행이 갱신되었습니다.

SQL> exec demo_pkg.process_data( 'ENAME', '%' );

PL/SQL 처리가 정상적으로 완료되었습니다.

"구루비 데이터베이스 스터디모임" 에서 2009년에 "이펙티브 오라클" 도서를 스터디하면서 정리한 내용 입니다.

- 강좌 URL : http://www.gurubee.net/lecture/3506

- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^

- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입