ORACLE 서브프로그램의 이해와 프로시저

스터디/DB(ORACLE)

ORACLE 서브프로그램의 이해와 프로시저 - (9)

고무곰(GomuGom) 2017. 9. 18. 15:44

[ 서브프로그램의 이해, 프로시저 ]

: PL/SQL의 서브프로그램이란?

-> <PL/SQL의 익명 블럭의 특징>

: . 이름이 없는 PL/SQL 블럭

. 저장해서 사용되지 않는다.

. 사용할 때마다 컴파일이 필요하다.

. 다른 응용프로그램에서 사용할 수 없다.

. 값을 반환할 수 없다.

. 매개변수를 사용할 수 없다.

-> <서브프로그램의 특징>

: . 이름이 있는 PL/SQL 블럭

. DB에 저장해 반복적으로 사용할 수 있다.

. 최초 실행될 때 한번만 컴파일 한다.

. 다른 응용프로그램에서 사용할 수 있다.

. 함수일 경우 값을 리턴(반환)할 수 있다.

. 매개변수를 사용할 수 있다.

** "서브 프로그램"은 내장된 함수와 프로시저가 있다.

- 프로시저

- 함수

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

- 프로시저란??

: 특정 처리를 위한 서브 프로그램의 한 유형이다.

: 단독으로 실행 가능하며, 다른 응용 프로그램에서도 호출되어 실행 가능하다.

- 프로시저 구문 형식)

create procedure 프로시저명

파라미터1(in,out,in out) 데이터타입, -- default는 in모드

-- out : 값을 반환하겠다, in : 서브프로그램 내에 값 전달 in out : 서브프로그램 내에도 전달되고, 외부 응용프로그램에도 반환하기도 함

파라미터2(in,out,in out) 데이터타입

변수 선언부;

begin

프로시저 본문 처리부;

exception

예외처리부;

end;

EX)

-- 프로시저 EX)
create or replace procedure update_emp -- 매개변수가 없는 프로시저 
is
  -- 변수 선언부...
begin
  update employees set first_name = '마이클'
  where department_id = 20 and job_id = 'MK_MAN';
end;
/
/* 프로시저를 실행시키기 위한 구문 
  exec 또는 execute 
*/
execute update_emp; -- 프로시저 실행

select * from employees where department_id = 20 and job_id = 'MK_MAN';

-- 매개변수에 모드 지정안하면 default로 in모드로 지정됨.
create or replace procedure up_sal
(emp_id employees.employee_id%type)
IS
BEGIN
  update employees set salary = 3500 where employee_id = emp_id; -- 프로시저 매개변수로 전달받은 값으로 처리
END;
/

-- 매개변수가 있는 프로시저 호출
exec up_sal(130);

create or replace procedure emp_name_sal
  (emp_id employees.employee_id%type)
IS
  emp_name employees.last_name%type;
  emp_salary employees.salary%type;
BEGIN
  select last_name, salary into emp_name, emp_salary
  from employees
  where employee_id = emp_id;
  
  DBMS_OUTPUT.put_line('사원명 : '||emp_name);
  DBMS_OUTPUT.put_line('급여 : '||emp_salary);
END;
/
-- 매개변수를 전달해 프로시저 수행
exec emp_name_sal(124);

-- out 모드 사용의 예
      --  emp_id는 in모드로 emp_name,emp_salary는 out모드로
      -- 즉, emp_id는 값을 프로시저 내로 가져올 때 사용하는 변수고
      -- out모드 변수 두개는 리턴할 때 사용하는 변수다.
create or replace procedure emp_info
  (emp_id employees.employee_id%type,
   emp_name out employees.last_name%type,
   emp_salary out employees.salary%type)
IS
BEGIN
  select last_name, salary into emp_name, emp_salary
  from employees
  where employee_id = emp_id; -- 입력받은 사원번호와 일치하는 것 검색
END;
/

-- 다른 PL/SQL내에서 프로시저 사용해 보기
declare
  emp_name employees.last_name%type;
  salary employees.salary%type;
begin
  -- 위에서 작성해 컴파일된 프로시저를 다른 PL/SQL 블럭에서 사용하기
  emp_info(124, emp_name, salary); -- 124는 in으로 프로시저 내에 전달하고 out변수 값들은
  -- 각각 emp_name과 salary변수를 써주면 해당 변수에 데이터가 리턴되어 저장되게 된다.
  
  DBMS_OUTPUT.put_line(emp_name||' '||salary);
end;
/

create or replace procedure emp_info
  (emp_id employees.employee_id%type,
   emp_name out employees.last_name%type,
   emp_salary out employees.salary%type)
IS
BEGIN
  select last_name, salary into emp_name, emp_salary
  from employees
  where employee_id = emp_id; -- 입력받은 사원번호와 일치하는 것 검색
END;

-- 익명블럭 외에서 프로시저를 호출하는 예)
variable emp_n varchar2(10); -- 블럭 밖에서 변수 선언 방법
variable sal number;
-- 바인드 변수를 통해 프로시저 호출하는 방법
execute emp_info(124,:emp_n,:sal);

print emp_n sal

set serveroutput on;

declare
  emp_name employees.last_name%type;
  salary employees.salary%type;
begin
  -- 연산자 =>를 이용해서 파라미터의 값을 지정하는 방식
    -- emp_id는 124로 지정 out타입인 emp_name은 emp_name으로 지정
  emp_info(emp_id => 124,
           emp_name => emp_name,
           emp_salary => salary); 
  dbms_output.put_line(emp_name ||' '||salary);
end;
/

[ 외부 변수 선언 및 전달과 out 타입을 통한 결과값 받기 예제 ]

저작자표시 비영리