PL/SQL - Package 생성 예제

1. 만들 것

# 패키지 생성

  create or replace package pack_t2
  is
     
    procedure insert();
    procedure update_sal();
    procedure update_deptno();
    procedure delete();
    procedure delete();
  end;
  /

  create or replace package body pack_t2
  is
   
    procedure insert
    is   
    begin
      insert into t2 values (8100, 3000, 20);
      commit;
    end;

    procedure update_sal()
    is   
    begin
      update t2 set sal = sal + 100 where empno = 7900;
      commit;
    end;

    procedure update_deptno()
    is   
      update t2 set deptno = 10 where empno = 7900;
      commit;
    end;

    procedure delete()
    is   
      delete from t2 where empno = 7900;
      commit;
    end;

  end;
  /

 

2. 완성

 

# 패키지 생성

  create or replace package pack_t2
  is
    procedure insert_t2
    (p_empno  t2.empno%type,
     p_sal    t2.sal%type,
     p_deptno t2.deptno%type);

    procedure update_sal
    (p_empno  t2.empno%type,
     p_sal    t2.sal%type);

    procedure update_deptno
    (p_empno  t2.empno%type,
     p_deptno t2.deptno%type);

    procedure delete_t2
    (p_empno  t2.empno%type);
  end;
  /

  create or replace package body pack_t2
  is
   
    procedure insert_t2
    (p_empno  t2.empno%type,
     p_sal    t2.sal%type,
     p_deptno t2.deptno%type)
    is   
    begin
      insert into t2 
      values (p_empno, p_sal, p_deptno);
      commit;
    end;

    procedure update_sal
    (p_empno  t2.empno%type,
     p_sal    t2.sal%type)
    is   
    begin
      update t2 
      set sal = p_sal
      where empno = p_empno;
      commit;
    end;

    procedure update_deptno
    (p_empno  t2.empno%type,
     p_deptno t2.deptno%type)
    is   
    begin
      update t2 
      set deptno = p_deptno 
      where empno = p_empno;
      commit;
    end;

    procedure delete_t2
    (p_empno  t2.empno%type)
    is   
    begin
      delete from t2
      where empno = 7900;
      commit;
    end;

  end;
  /

  select * from t1;
  select * from t2;

  exec pack_t2.insert_t2(8200, 3000, 20)
  select * from t1;
  select * from t2;

  var sal number

  begin
    select sal into :sal
    from t2
    where empno = 7788;
  end;
  /

  print sal

  exec pack_t2.update_sal(7788, :sal + 100)
  select * from t2 where empno = 7788;
  select * from t1;

  exec pack_t2.update_deptno(7788, 10)
  select * from t2 where empno = 7788;
  select * from t1;

  delete from t2 where empno = 7788;
  select * from t1;
  select * from t2;