! 프로시저#

가장 기본적인 프로시저#

SQL> create procedure test_proc as
  2  begin
  3  null;
  4  end test_proc;
  5  /

프로시저가 생성되었습니다.

SQL> create or replace procedure test_proc as
  2  begin
  3  dbms_output.put_line('Hello world');
  4  end test_proc;
  5  /

프로시저가 생성되었습니다.

SQL> set serveroutput on
SQL> exec test_proc;
Hello world

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

프로시저를 만들기 위해서는 create 명령어를 사용하지만 해당 이름의 프로시저가 존재한다면 replace명령을 붙여서 교체를 하면 된다.

<div class="information"> 프로시저를 실행하기 위해서는 execute권한을 가지고 있어야 한다. </div>

프로시저 파라미터 타입#

IN 파라미터#

SQL> create table test(
  2  num number
  3  );

테이블이 생성되었습니다.

SQL> create or replace
  2  procedure insert_into_test(p_param in numberis
  3  begin
  4  insert into test values(p_param);
  5  end insert_into_test;
  6  /

프로시저가 생성되었습니다.

SQL> select * from test;

선택된 레코드가 없습니다.

SQL> exec insert_into_test(p_param=>100);

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

SQL> select * from test;

       NUM
----------
       100

SQL> create or replace
  2  procedure insert_into_test(p_param1 in number, p_param2 in numberis
  3  begin
  4  dbms_output.put_line(p_param1);
  5  insert into test values(p_param2);
  6  end insert_into_test;
  7  /

프로시저가 생성되었습니다.

SQL> exec insert_into_test(p_param1=>100, p_param2=>200);
100

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

파라미터 전달 방법

  • 이름 표기
SQL> exec insert_into_test(p_param1=>100, p_param2=>200);
100

PL/SQL 처리가 정상적으로 완료되었습니다.
  • 위치 표기
SQL> exec insert_into_test(100200);
100

PL/SQL 처리가 정상적으로 완료되었습니다.
  • 혼합 표기
SQL> exec insert_into_test(100, p_param2=>200);
100

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

OUT 파라미터#

SQL> create or replace procedure
  2  emp_look(
  3  p_empno in number,
  4  o_ename out emp.ename%type,
  5  o_sal out emp.sal%typeas
  6  begin
  7  select ename, sal
  8  into o_ename, o_sal
  9  from emp
 10  where empno=p_empno;
 11  exception
 12  when NO_DATA_FOUND then
 13  o_ename := 'NULL';
 14  o_sal := -1;
 15  end emp_look;
 16  /

프로시저가 생성되었습니다.

SQL> variable name varchar2(10);
SQL> variable sal number;
SQL> exec emp_look('7782', :name, :sal);

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

SQL> print name;

NAME
--------------------------------
CLARK

SQL> print sal;

       SAL
----------
      2450

SQL> select :name, :sal
  2  from dual
  3  ;

:NAME                                  :SAL
-------------------------------- ----------
CLARK                                  2450

IN OUT 파라미터#

SQL> create or replace procedure
  2  swap(
  3  p_param1 in out number,
  4  p_param2 in out numberas
  5  l_temp number;
  6  begin
  7  l_temp := p_param1;
  8  p_param1 := p_param2;
  9  p_param2 := l_temp;
 10  end swap;
 11  /

프로시저가 생성되었습니다.

SQL> declare
  2  l_num1 number := 100;
  3  l_num2 number := 101;
  4  begin
  5  swap(l_num1, l_num2);
  6  dbms_output.put_line('l_num1='||l_num1);
  7  dbms_output.put_line('l_num2='||l_num2);
  8  end;
  9  /
l_num1=101
l_num2=100

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

swap 프로시저에서 사용된 l_temp number; 는 일종의 지역변수다.

AUTHID#

프로시저의 AUTHID 지시문은 이 프로시저를 실행하는 권한이 누구의 것인지를 오라클에게 알려준다. 정의자값을 가진다. 호출자를 기준으로 할때는 아래의 예제와는 달리 authid current_user 를 사용하면 된다.

SQL> create table numbers(
  2  n number,
  3  username varchar2(30));

테이블이 생성되었습니다.

SQL> create or replace
  2  procedure insert_numbers(p_num numberauthid definer as
  3  begin
  4  insert into numbers values(p_num, user);
  5  end insert_numbers;
  6  /

프로시저가 생성되었습니다.

SQL> exec insert_numbers(12345);

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

SQL> select * from numbers;

         N USERNAME
---------- ------------------------------
     12345 SCOTT

pragma autonomous_transaction#

하위 트랜잭션을 사용해서 일부 트랜잭션을 관리하고자 할때 사용한다.

SQL> create table log_table(
  2  username varchar2(30),
  3  date_time timestamp,
  4  message varchar2(4000));

테이블이 생성되었습니다.

SQL> create table temp_table(
  2  n number);

테이블이 생성되었습니다.

SQL> create or replace
  2  procedure log_message(p_message varchar2as
  3  pragma autonomous_transaction;
  4  begin
  5  insert into log_table(username, date_time, message)
  6  values(user, current_date, p_message);
  7  commit;
  8  end log_message;
  9  /

프로시저가 생성되었습니다.

SQL> select * from log_table;

선택된 레코드가 없습니다.

SQL> select * from temp_table;

선택된 레코드가 없습니다.

SQL> begin
  2  log_message('Test Messgae');
  3  insert into temp_table(n)
  4  values(123);
  5  log_message('rollback');
  6  rollback;
  7  end;
  8  /

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

SQL> select * from log_table;

USERNAME                       DATE_TIME                         MESSAGE
------------------------------ --------------------------------- -----------------------------------------
SCOTT                             08/01/11 19:40:08.000000   Test Messgae

SCOTT                             08/01/11 19:40:08.000000   rollback

SQL> select * from temp_table;

선택된 레코드가 없습니다.

결과를 보면 가장 상위 트랜잭션에 속해있는 temp_table테이블 데이터 입력은 롤백이 되었지만 하위 프로젝트내에 있는 log_message 내의 처리는 정상적으로 커밋이 되어 있다.

! 함수#

기본적인 생성 문법은 프로시저와 비슷하나 반드시 반환값을 정의해야 하는 것은 다르다.

SQL> create or replace
  2  function first_func return varchar2 as
  3  begin
  4  return 'Hello World';
  5  end first_func;
  6  /

함수가 생성되었습니다.

SQL> set serverout on
SQL> declare
  2     l_str varchar2(100:= null;
  3  begin
  4     l_str := first_func;
  5     dbms_output.put_line(l_str);
  6  end;
  7  /
Hello World

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

SQL> exec dbms_output.put_line(first_func);
Hello World

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

! 패키지#

프로시저, 함수, 객체 타입, 그리고 항목들을 하나의 논리적인 데이터베이스 객체로 그룹화하는 구조

SQL> create or replace
  2  package employee_pkg as
  3     procedure print_ename(p_empno number);
  4     procedure print_sal(p_empno number);
  5  end employee_pkg;
  6  /

패키지가 생성되었습니다.

SQL> exec employee_pkg.print_ename(1234);
BEGIN employee_pkg.print_ename(1234); END;

*
1행에 오류:
ORA-04067: 실행 불가, package body "SCOTT.EMPLOYEE_PKG"(존재하지
않습니다.
ORA-06508: PL/SQL: 호출 중인 프로그램 단위를 찾을 수 없습니다(:
"SCOTT.EMPLOYEE_PKG").
ORA-06512: 줄 1에서

SQL> create or replace
  2  package body employee_pkg as
  3  procedure print_ename(p_empno numberis
  4  l_ename emp.ename%type;
  5  begin
  6  select ename into l_ename
  7  from emp
  8  where empno=p_empno;
  9  dbms_output.put_line(l_ename);
 10  exception
 11  when NO_DATA_FOUND then
 12  dbms_output.put_line('Invalid employee number');
 13  end print_ename;
 14
 15  procedure print_sal(p_empno numberis
 16     l_sal emp.sal%type;
 17  begin
 18     select sal into l_sal
 19     from emp
 20     where empno=p_empno;
 21
 22     dbms_output.put_line(l_sal);
 23  exception
 24     when NO_DATA_FOUND then
 25     dbms_output.put_line('Invalid employee number');
 26  end print_sal;
 27
 28  end employee_pkg;
 29  /

패키지 본문이 생성되었습니다.

SQL> execute employee_pkg.print_ename(1234);
Invalid employee number

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

SQL> execute employee_pkg.print_ename(7782);
CLARK

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

SQL> execute employee_pkg.print_sal(7782);
2450

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

Add new attachment

Only authorized users are allowed to upload new attachments.
« This page (revision-2) was last changed on 11-Jan-2008 20:11 by DongGukLee