PL/SQL (2)

○ 목차

• 실명 프로시저(저장 프로시저)
• sql의 처리 순서
• 프로시저 생성과 호출 예

실명 프로시저

- 프로시저는 익명 프로시저와 실명 프로시저로 나눌 수 있다.

- 익명 프로시저는 1회용이지만 실명프로시저는 재사용할 수 있고 오라클에 저장된다.  

- 저장 프로시저(Stored Procedure) 라고도 한다.

 

    1. 저장 프로시저, Stored Procedure
        - 매개변수 구성 / 반환값 구성 > 자유

        - 저장 프로시저는 ANSI-SQL에서는 사용이 불가능하지만 함수는 가능하다.
        - 반환값을 돌려주는 방식의 차이 > 프로시저와 함수는 사용하는 장소가 다르다.

    2. 저장 함수, Stored Function
        - 매개변수 필수 / 반환값 필수 > 고정

        - 저장 함수는 주로 ANSI-SQL의 반복되는 일을 줄이기 위해서 사용한다.

        - 반환값이 반드시 존재 > out 파라미터를 말하는게 아니라 > return 문을 사용한다.
        - out 파라미터를 사용하면 안된다. > return문만 사용
        - int 파라미터는 사용한다. 

 

-- 익명 프로시저 선언    
    [declare
        변수 선언;
        커서 선언;]
    begin
        구현부;
    [exception
        처리부;]
    end;
    
    
-- 저장 프로시저 선언    
    create [or replace] procedure 프로시저명
    is(as)
        [변수 선언;
        커서 선언;]
    begin
        구현부;
    [exception
        처리부;]
    end;
    
-- 저장 프로시저 호출
begin
    procTest; --프로시저 실행(호출)
end;

 

 

SQL 처리 순서

   
1. ANSI-SQL or 익명 프로시저

• 클라이언트 > 구문 작성(select) > 실행(Ctrl+Enter) > 명령어를 오라클 서버에 전달 > 서버가 명령어를 수신 > 구문 분석(파싱) + 문법 검사 > 컴파일 실행 > 실행 > 결과 도출 > 서버가 결과셋을 클라이언트에게 반환 > 결과셋을 화면에 출력
• 한번 실행했던 명령어를 다시 실행하면 위의 과정을 다시 처음부터 끝까지 모든 과정을 재실행한다.
• 첫번째로 실행 비용과 다시 실행 비용

    2. 저장 프로시저

• 클라이언트 > 구문 작성(create) > 실행(Ctrl+Enter) > 명령어를 오라클 서버에 전달 > 서버가 명령어를 수신 > 구분 분석(파싱) + 문법 검사 > 컴파일 실행 > 실행 > 컴파일된 프로시저를 서버에 저장
• 클라이언트 > 구문 작성(호출) > 실행(Ctrl+Enter) > 명령어를 오라클 서버에 전달 > 서버가 명령어를 수신 > 구분 분석(파싱) + 문법 검사 > 컴파일 실행 > 실행 > 프로시저 실행
• 한번 실행했던 명령어를 다시 실행하면 구문 분석 + 컴파일하는 작업이 생략되기 때문에 비용이 절감된다.
• 첫번째 실행하는 비용이 다시 실행 비용보다 많이 든다.

declare
    vnum number;
begin
    vnum := 100;
    dbms_output.put_line(vnum);
    -- 1000라인
end;


create or replace procedure procTest
is
    vnum number;
begin
    vnum := 100;
    dbms_output.put_line(vnum);
    -- 1000라인
end;


begin
    procTest; --프로시저 실행(호출)
end;

 

 

프로시저 생성과 호출 예시

-- 1. 부서 지정 > 해당 부서 직원 중 급여 가장 많이 받는 사람의 번호 반환
--      in 1개 > out 1개

-- 2. 직원 번호 지정 > 같이 지역에 사는 직원 수, 같은 직위의 직원 수, 해당 직원보다 급여를 더 많이 받은 직원 수를 반환
--      in 1개 > out 3개

--procTest1
--procTest2

create or replace procedure procTest1(
    pbuseo in varchar2,
    pnum out number
)
is
begin
    
    select num into pnum from tblInsa where basicpay = (select max(basicpay) from tblInsa where buseo = pbuseo);
    
end procTest1;



declare
    vnum number;
begin
    procTest1('기획부', vnum);
    dbms_output.put_line(vnum);
end;