oracle PL/SQL_서브프로그램_프로시저(procedure)

 

 

PL/SQL 서브프로그램: 데이터베이스에 저장된 PL/SQL

   - 서브프로그램:  내장된 함수, 프로시저

   

< 익명 블록의 특징 >

1) 이름이 없는 PL/SQL 블록

2) 사용할 때 마다 컴파일이 필요

3) 데이터베이스에 저장되어있지 않다

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

5) 값을 반환할 수 없다

6) 매개변수 사용할 수 없다

 

< 서브프로그램의 특징 >

1) 이름이 있는 PL/SQL 블록

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

3) 데이터베이스에 저장되어 있다

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

5) 함수일 경우 값을 반환할 수 있다

6) 매개변수를 사용할 수 있다

 

< 프로시저 >

1) 특정 처리를 위한 서브 프로그램의 한 유형

2) 단독으로 실행 가능하며 다른 응용프로그램에서 호출되어 실행 가능

 

   *구문형식*

create procedure 프로시저명

(  파라미터1[ in, out, in out ]  데이터타입,

   파라미터2[ in, out, in out ]  데이터타입,

   ... )

 

is [as]

   변수 선언부;

begin

   프로시저 본문 처리부;

exception

   예외처리부;

end;

 

   *파라미터 모드 [ in, out, in out ] *

1) in : 기본모드, 모드설정을 하지않으면 기본으로 in모드로 설정된다, 서브프로그램 내에서 사용하겠다는 것을 의미     

2) out : 값을 반환할 때 (다른 응용프로그램에서 호출할 시) 사용하겠다 의미      

3) in out : 값이 서브프로그램에서도 사용되고 호출하는 응용프로그램에서도 사용된다

 

기본형태

-- create or replace : 프로시저를 수정해거나, 수정하면서 새롭게 생성할 때 사용하는 키워드
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;

 

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;
/
 
SELECT salary from employees where employee_id = 130;
 
exec up_sal(130);
 
 
create or replace procedure emp_name_sal
    (emp_id employees.employee_id%type)
is
    emp_name employees.first_name%type;
    emp_sal employees.salary%type;
begin
    select last_name, salary into emp_name, emp_sal
    from employees
    where employee_id = emp_id;
    
    dbms_output.put_line('사원명 : ' || emp_name);
    dbms_output.put_line('월급 : ' || emp_sal);
end;
/
 
set serveroutput on;
 
exec emp_name_sal(124); -- 매개변수 124가 서브프로그램 안으로 들어와서 사용된다 (in 모드)

 

out 모드

create or replace procedure emp_info
    (emp_id in 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;
/
 
declare 
    emp_name employees.last_name%type;
    salary employees.salary%type;
begin
    emp_info(124, emp_name, salary);
    dbms_output.put_line(emp_name || '사원의 급여는 ' || salary ||'입니다' );   
    
end;
 
/
 

 

out 모드_2

-- variable : 변수를 선언하기위한 키워드 (바인드변수)
-- out 모드 파라미터에 매칭되는 매개변수는 앞에 : 를 붙여준다
variable emp_name varchar2(10)
variable salary number
execute emp_info(124, :emp_name, :salary); 
 
print emp_name salary
 

** 바인드 변수를 선언하고 프로시저를 실행했더니 오류가 뜸

SP2-0552: 바인드 변수 "SALARY"이(가) 선언되지 않았습니다.

==>바인드 변수 행도 실행시키고 프로시저도 실행시켜야함

** 변수선언 행도 실행시키고 프로시저도 실행시켰는데 데이터값이 출력안됨

처음에는 데이터값이 출력안되다가 아래 에러가 떴다

ORA-06550: line 1, column 222:
PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following

==>​This issue occurs when the SQL procedure/query is not properly ended

실행 중간에 주석을 달아놨는데 위로 올리니까 데이터가 잘 나왔다

 

연산자 => 를 이용하여 파라미터의 값을 지정

declare 
    emp_name employees.last_name%type;
    salary employees.salary%type;
begin
    emp_info(emp_id=> 124 , emp_name => emp_name, emp_salary => salary);
    dbms_output.put_line(emp_name || '사원의 급여는 ' || salary ||'입니다' );       
end;
/