DATABASE STEP 32 - PROCEDURE

★ 프로시저(PROCEDURE)

• 1. 익명 프로시저
  • 1회용 프로시저
• 2. 실명 프로시저
  • 재사용
  • 오라클에 저장
  • 저장 프로시저(Stored Procedure)
    • 매개변수 구성 / 반환값 구성 > 자유
  • 저장 함수(Stored Function)
    • 매개변수 필수/반환값 필수 > 고정
• 익명 프로시저 선언 방법

[declare
        변수 선언;
        커서 선언;]
    begin
        구현부;
    [exception
        처리부;]
end;

• 저장 프로시저 선언 방법

create [or replace] procedure 프로시저명
    is(as)
    [변수 선언;
        커서 선언;]
    begin
        구현부;
    [exception
        처리부;]
end;

 

■ 프로시저 ex)

• 1. 익명 프로시저

declare
    vnum number;
begin
    vnum := 100;
    dbms_output.put_line(vnum);
end;

• 2. 저장 프로시저

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

• 저장 프로시저 호출하는 방법(메소드 실행하는 방법)
• 현재 코딩하는 영역 > ANSI-SQL 영역

procTest; -- 올바르지 않은 방법

-- PL/SQL을 영역을 만든 뒤 > 그안에서 프로시저를 호출할 수 있다.
begin
    -- 이 안이 PL/SQL 영역
    procTest;
end;

• 1. 매개변수가 있는 프로시저(1개)

create or replace procedure procTest(pnum number) --매개변수 
is
    vnum number; --일반변수(지역 변수)
begin

    vnum := pnum * 2;
    dbms_output.put_line(vnum);

end procTest;

begin
    procTest(100);
    procTest(200);
    procTest(300);
end;

• 2. 매개변수가 있는 프로시저(2개)

create or replace procedure procTest(
    width number, 
    height number
)
is
    vnum number;
begin

    vnum := width * height;
    dbms_output.put_line(vnum);

end procTest;

begin
    procTest(10,20);
end;

• 3. 프로시저 매개변수는 길이와 not null 표현이 불가능하다.

create or replace procedure procTest(
    pname varchar2
)
is --declare 대신하는 is(as)는 변수 선언이 없어도 반드시 기재
begin
    dbms_output.put_line('안녕하세요. ' || pname || '님');
end procTest;

begin
    procTest('홍길동');
end;

create or replace procedure procTest(
    width number, 
    height number default 10
)
is
    vnum number;
begin

    vnum := width * height;
    dbms_output.put_line(vnum);

end procTest;

begin
    -- 오버로딩..(x) > 매개변수 기본값(O)
    procTest(10,20);
    procTest(10);
end;

 

■ 매개변수 모드

• 매개변수가 값을 전달하는 방식
• call by value
• call by reference

1. in 모드 > 기본 모드
2. out 모드 
3. in out 모드 > X

• ex)

create or replace procedure procTest (
    pnum1 in number,    -- 우리가 알고있는 기존의 매개변수(호출할 때 넘기는 데이터)
    pnum2 in number,    --
    presult1 out number, -- 변수 자체가 전달, 변수의 주소값 전달 > 반환값 역할
    presult2 out number,
    presult3 out number
)
is
begin
    dbms_output.put_line(pnum1+pnum2);
    presult1 := pnum1 + pnum2;
    presult2 := pnum1 * pnum2;
    presult3 := pnum1 / pnum2;
end procTest;

• ex2)

declare
    vresult number;
begin
    procTest(10,20,vresult);
    dbms_output.put_line(vresult);
end;

declare
    vresult1 number;
    vresult2 number;
    vresult3 number;
begin
    procTest(10,20,vresult1,vresult2,vresult3);
    dbms_output.put_line(vresult1);
    dbms_output.put_line(vresult2);
    dbms_output.put_line(vresult3);
end;

 

■ 요구사항에 따른 ex)

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

select * from tblInsa;
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;

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

select * from tblInsa;
select count(*) from tblInsa where city = (select city from tblInsa where num = 1001);
select count(*) from tblInsa where jikwi = (select jikwi from tblInsa where num = 1001);
select count(*) from tblInsa where basicpay > (select basicpay from tblInsa where num = 1001);

create or replace procedure procTest2 (
    pnum in number,    -- 우리가 알고있는 기존의 매개변수(호출할 때 넘기는 데이터)
    pcnt1 out number,
    pcnt2 out number,
    pcnt3 out number
)
is
    vcity tblInsa.city%type;
    vjikwi tblInsa.jikwi%type;
    vbasicpay tblInsa.basicpay%type;
begin
    select city, jikwi, basicpay into vcity, vjikwi, vbasicpay from tblInsa where num = pnum;
    
    select count(*) into pcnt1 from tblInsa where city = vcity;
    select count(*) into pcnt2 from tblInsa where jikwi = vjikwi;
    select count(*) into pcnt3 from tblInsa where basicpay > vbasicpay;
    
end procTest2;
declare
    vcnt1 number; vcnt2 number; vcnt3 number;
begin
    procTest2(1001, vcnt1, vcnt2, vcnt3);
    dbms_output.put_line(vcnt1);
    dbms_output.put_line(vcnt2);
    dbms_output.put_line(vcnt3);
end;