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;
/

데모

테이블

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에서

커서

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)

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 처리가 정상적으로 완료되었습니다.