(SQL) 서브프로그램 및 프로시저에 대해 알아보자!

▣ PL/SQL 서브프로그램 : 데이터베이스의 저장된 PL/SQL(프로시저, 함수)

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

◈ 익명블럭의 특징

   ▷ 이름이 없는 PL/SQL블록(문)

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

   ▷ 데이터베이스에 저장되어 있지 않다.

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

   ▷ 값을 반환할 수 없다.

   ▷ 매개변수(파라미터)를 사용할 수 없다.

◈ 서브프로그램의 특징

   ▷ 이름이 있는 PL/SQL블록(문)

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

   ▷ 데이터베이스에 저장되어 있다.

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

   ▷ 함수일 경우 값을 반환할 수 있다.

   ▷ 매개변수(파라미터)를 사용할 수 있다.

▣ 프로시저란??

   ▷ 특정 처리를 위한 서브 프로그램의 한 유형으로, 단독으로 실행 가능하며, 다른 응용프로그램에서 호출 되어 실행 가능하다.

▣ 구문 형식

   ▷ CREATE PROCEDURE 프로시저명

       파라미터1[mode] 데이터타입,

       파라미터2[in(default), out, in out] 데이터타입, ...

       ※ in : 입력안할 시 default값으로 지정, 내부에만 값을 반환

          out : 변수값을 초기화하지 않고, 자신을 호출한 곳에 값을 반환한다.

          in out : 변수값이 초기화되어있고, 값이 서브프램과, 자신을 호출한 곳 둘다 반환된다.

       is [as]

        변수 선언부;

       begin

        프로시저 본문 처리부;

       exception

         예외처리부;

       end;

       /

실행 예제)

create or replace procedure update_emp --테이블을 수정하거나 수정을 하면서 새롭게 생성할 때 사용
is
begin
   update emptest2
   set ename = '마이클'
   where deptno = 20
   and job = 'CLERK';
end;
/

1) create문을 사용, 프로시저 생성

2) 변수선언하지 않고 넘어감.

3) begin

    emptest 테이블의 deptno가 20인 ename값을 마이클로 바꾸겠다.

4) end;

5) /

6) 실행하면 프로시저 블록이 생성된다.

execute update_emp;
exec update_emp;

1) 프로시저를 사용하기 위해 execute 프로시저명; 또는 exec 프로시저명; 을 실행

OUT모드 실행 예제)

/*out모드 사용 예*/
 
create or replace procedure emp_info
(emp_id emptest2.empno%type,
emp_name out emptest2.ename%type,
emp_sal out emptest2.sal%type) --emp_name과 emp_sal은 프로시저 안으로 안들어옴
                          --(밖으로 나감[다른 응용프로그램으로])
is
begin
   select ename, sal into emp_name, emp_sal
   from emptest2
   where emp_id = empno;
   
end;
 /

1) 프로시저를 생성하고 매개변수를 만드는데 emp_name과 emp_sal은 OUT으로 설정한다.

2) begin에서 emp_id와 empno가 같은 ename, sal을 emp_name과 emp_sal에 넣어준다

--타 익명 SQL문(응용프로그램) 
declare
 emp_name emptest2.ename%type;
 emp_sal emptest2.sal%type;
 begin
  emp_info(7369, emp_name, emp_sal); --프로시저명
  dbms_output.put_line(emp_name||'사원의 급여는'||emp_sal||'입니다');
  end;
  /

1) 타 익명 SQL문을 만들어준다.

2) begin문 안에서 프로시저를 넣어준다.

3) 프로시저에서 id를 받아 데이터들을 뽑아온다.

VARIABLE 실행 예제)

--variable = 변수를 선언하기 위한 예약어
variable emp_name varchar2(10)
variable salary number
execute emp_info(7369, :emp_name, :salary);
 
print emp_name salary;
 
 
declare
 emp_name emptest2.ename%type;
 salary emptest2.sal%type;
begin
   emp_info(emp_id => 7369, --in모드
   emp_name => emp_name, emp_sal => salary); -- out 모드
   --연산자 =>를 이용해서 파라미터 값을 지정 
   dbms_output.put_line(emp_name||'사원의 급여는'||salary||'입니다');
   end;
   /

1) => 을 이용해 파라미터 값을 지정해 주는 것.