2021.06.01

SQL 처리 순서

1. ANSI-SQL or 익명 프로시저
   : 클라이언트 구문 작성(select) -> 실행(Ctrl+Enter) -> 명령어가 오라클 서버에 전달
   -> 서버에서 명령어를 수신 -> 구문 분석(파싱) -> 컴파일 -> 실행 -> 결과 도출
   -> 결과셋을 클라이언트에게 반환
   : 한번 실행했던 명령어를 다시 실행 -> 위의 과정을 다시 처음부터 끝까지 모든 과정을 재실행한다.
   : 첫번째 실행 비용 = 다시 실행 비용

2. 저장 프로시저(PL/SQL)
   : 클라이언트 구문 작성(select) -> 실행(Ctrl+Enter) -> 명령어가 오라클 서버에 전달
   -> 서버에서 명령어를 수신 -> 구문 분석(파싱) -> 컴파일 -> 실행 -> 결과 도출
   -> 결과셋을 클라이언트에게 반환
   : 한번 실행했던 명령어를 다시 실행 -> "구문 분석(파싱) -> 컴파일" 과정이 생략된다. : 이부분 비용 감소
   : 첫번째 실행 비용 > 다시 실행 비용

질의 실행

1. ANSI-SQL(Text SQL)
2. 익명 프로시저 = ANSI-SQL + PL/SQL = 개발자용(테스트용)
3. 저장 프로시저 = ANSI-SQL + PL/SQL = 코드 저장 = 영구적 재사용

-- ANSI-SQL
select name from tblInsa; -- 구문분석, 파싱


create or replace procedure procInsa
is
    cursor vcursor is select name from tblInsa;
    vname tblInsa.name%type;
begin
    
    open vcursor;
        loop
            fetch vcursor into vname;
            exit when vcursor%notfound;
            
            -- 클라이언트에게 반환하는 작업이라고 가정(return)
            dbms_output.put_line(vname);
            
        end loop;
    close vcursor;
    
end;

set serveroutput on;

begin
    procInsa;
end;

-- 프로시저 == 메소드
-- 1. 매개변수
-- 2. 반환값

-- 매개변수
create or replace procedure procTest(pnum number) -- 매개변수 리스트
is
    vsum number := 0; -- 로컬 변수
begin
    
    vsum := pnum + 100;
    dbms_output.put_line(vsum);
    
end procTest;

-- 출력
begin
    procTest(100); -- 200
    procTest(200); -- 300
    procTest(300); -- 400
end;

create or replace procedure procTest (
    -- 앞에만 default 사용하고 뒤에는 사용 안하면 에러... ex) width number default 10, height number 사용불가X
    width number,  
    height number default 10 -- 매개변수에 default 활용 -> 메소드 오버로딩 유사 구현
)
is
    vresult number;
begin

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

end procTest;

-- 출력
begin
    procTest(100, 200);
    procTest(100); 
    -- procTest(default, 100);
end;

매개변수 모드(****)

• 매개변수의 값을 전달하는 방식

1. in 모드(기본 모드) : 생략해도 in모드로 기본동작함
2. out 모드 - 값을 반환함.
3. in out 모드(사용 안함) - 값을 넘길수도 있고 값을 받아 올 수도 있음.

-- 프로시저 생성
create or replace procedure procTest(
    vnum1 in number, -- in mode(= 값 전달 변수)
    vnum2 in number,
    vresult out number, -- out mode(= 주소값 참조 변수) -> 반환값 역할, vsum의 주소값이 들어감.
    vresult2 out number, -- 장점 : 여러개를 반환할 수 있음 -> 가용범위 넓어짐.
    vresult3 out number
)
is
begin
    vresult := vnum1 + vnum2;
    vresult2 := vnum1 * vnum2;
    vresult3 := vnum1 / vnum2;
end; -- procTest


-- 호출
declare
    vsum number;
    vsum2 number;
    vsum3 number;
begin
    procTest(10, 20, vsum, vsum2, vsum3);
    dbms_output.put_line(vsum); -- 30
    dbms_output.put_line(vsum2); -- 200
    dbms_output.put_line(vsum3); -- 0.5
end;

-- 프로시저로 제작
-- 요구사항] 부서 지정 -> 부서내에서 급여를 가장 많이 받는 직원의 이름을 반환
create or replace procedure procTest(
    pbuseo in varchar2, -- 매개변수에서는 길이 명시 불가, varchar2(15), tblInsa.buseo%type 사용 안함
    pname out varchar2
)
is
    vsalary number; -- 로컬변수
begin
    
    -- ANSI-SQL 방법 1.
    -- select name into pname from tblInsa 
    --    where basicpay = (select max(basicpay) from tblInsa where buseo = pbuseo);
    
    -- 방법 2.
    select max(basicpay) into vsalary from tblInsa where buseo = pbuseo;
    select name into pname from tblInsa where basicpay = vsalary;

end procTest;


-- 호출
declare
    vname varchar2(30); -- 길이 명시 필요
begin
    procTest('개발부', vname);
    dbms_output.put_line(vname);
end;

-- 요구사항] 직원번호 -> 같은 지역의 직원수, 같은 직위의 직원수, 같은 부서의 직원수 반환

create or replace procedure procTest(
    pnum number,        -- 직원번호(in)
    pcnt1 out number,   -- 같은 지역
    pcnt2 out number,   -- 같은 직위
    pcnt3 out number    -- 같은 부서
)
is
begin

    -- 같은 지역
    select count(*) into pcnt1 from tblInsa
        where city = (select city from tblInsa where num = pnum);
    
    -- 같은 직위
    select count(*) into pcnt2 from tblInsa
        where jikwi = (select jikwi from tblInsa where num = pnum);
    
    -- 같은 부서
    select count(*) into pcnt3 from tblInsa
        where buseo = (select buseo from tblInsa where num = pnum);

end procTest;


-- 호출
declare
    vcnt1 number;
    vcnt2 number;
    vcnt3 number;
begin
    procTest(1001, vcnt1, vcnt2, vcnt3);
    dbms_output.put_line('같은 지역: ' || vcnt1);
    dbms_output.put_line('같은 직위: ' || vcnt2);
    dbms_output.put_line('같은 부서: ' || vcnt3);
end;