2021.05.27

ex29_plsql.sql

프로시저 생성 + ANSI-SQL(select) + 결과(PL/SQL 변수에 저장) -> 이전 시간복습

set serveroutput on;

declare
    vname varchar2(15);
begin
    
    -- tblinsa에서 급여를 가장 많이 받는 사람?
    select name into vname from tblInsa
        where basicpay = (select max(basicpay) from tblInsa);
    
    dbms_output.put_line(vname);
    
end;

declare
    vname varchar2(15);
    vbasicpay number;
begin
    
    select name, basicpay into vname, vbasicpay from tblInsa
        where basicpay = (select max(basicpay) from tblInsa);
    
    dbms_output.put_line(vname); -- 허경운
    dbms_output.put_line(vbasicpay); -- 2650000
    
end;

-- 임시 테이블 생성
create table tblTemp
as
select name, buseo, jikwi, city from tblInsa where city = '서울';

select * from tblTemp;


-- tblInsa -> 연봉 1위(select) -> tblTemp 추가(insert)
declare
    vname varchar2(15);
    vbuseo varchar2(15);
    vjikwi varchar2(15);
    vcity varchar2(15);
begin
    
    -- 1. 연봉 1위 정보 가져오기
    select name, buseo, jikwi, city into vname, vbuseo, vjikwi, vcity from tblInsa
        where basicpay = (select max(basicpay) from tblInsa);
    
    -- 2. insert할때 변수 사용.
    insert into tblTemp (name, buseo, jikwi, city) values (vname, vbuseo, vjikwi, vcity);
    
end;

select * from tblTemp;

-- tblInsa. 연봉 꼴찌 -> 같은 부서 직원을 tblTemp에서 삭제
declare
    vbuseo varchar2(15);
begin

    -- 1. 연봉꼴찌 구하기 -> 인사부
    select buseo into vbuseo from tblInsa
        where basicpay = (select min(basicpay) from tblInsa);
        
    -- 2. 인사부 직원 삭제
    delete from tblTemp where buseo = vbuseo;
end;

---

참조 자료형

• 테이블로부터 직접 자료형을 알아내는 방법
• 생산성 + 유지보수성 좋음

1. %type

• 사용하는 테이블의 특정 컬럼 자료형을 그대로 참조해서 변수에 적용, 컬럼 참조
  a. 자료형
  b. 길이
  c. not null

declare
    vname varchar2(20);
    -- shift + F4 : 해당테이블의 정보를 알려주는 단축키(툴기능)
    -- 변수명 테이블명.컬럼명%type
    vssn tblInsa.ssn%type; -- 실제 테이블 컬럼을 참조해서 자료형, 길이,not null제약을 복사해 온다
    vsudang tblInsa.sudang%type;
begin
    select name, ssn, sudang into vname, vssn, vsudang from tblInsa where name = '홍길동';
    dbms_output.put_line(vname);
    dbms_output.put_line(vssn);
    dbms_output.put_line(vsudang);
end;

1. %type

• 컬럼 참조

2. %rowtype

• 행 참조
• %type의 집합

-- 홍길동의 모든 것 보기
declare
    -- 변수 10개
    vnum tblInsa.num%type;
    vname tblInsa.name%type;
    vssn tblInsa.ssn%type;
    vibsadate tblInsa.ibsadate%type;
    vcity tblInsa.city%type;
    vtel tblInsa.tel%type;
    vbuseo tblInsa.buseo%type;
    vjikwi tblInsa.jikwi%type;
    vbasicpay tblInsa.basicpay%type;
    vsudang tblInsa.sudang%type;
begin
    select * into vnum, vname, vssn, vibsadate, vcity, vtel, vbuseo, vjikwi, vbasicpay, vsudang from tblInsa where name = '홍길동';
    
    dbms_output.put_line(vnum);
    dbms_output.put_line(vname);
    dbms_output.put_line(vtel);
end;

declare
    vrow tblInsa%rowtype; -- 테이블의 모든 컬럼의 집합 참조, 레코드 참조
begin
    select * into vrow from tblInsa where name = '홍길동';
    
    -- dbms_output.put_line(vrow); -- 복합값이라 한번에 출력은 불가
    dbms_output.put_line(vrow.name);
    dbms_output.put_line(vrow.buseo);
    dbms_output.put_line(vrow.city);
    
end;

-- '이순신'이 남자인지 여자인지 확인
declare
    vname tblInsa.name%type; -- 컬럼값을 그대로 가져올 때(행 전체를 참조)
    vgender varchar2(1); -- 가공,연산된 값을 가져올 때(직접 자료형을 정해줌)
begin
    select substr(ssn, 8, 1) into vgender from tblInsa where name = '이순신';
    
    dbms_output.put_line(vgender);
    
end;

제어문(조건문)

-- 제어문(조건문)
declare
    vnum number := 10; -- 1. 변수 생성 + 초기화
begin
    -- 양수인지 음수인지 확인
    if vnum > 0 then
       dbms_output.put_line('양수');
    end if;
    
end;

declare
    vnum number := -10;
begin
    
    if vnum > 0 then
        dbms_output.put_line('양수');
    else 
        dbms_output.put_line('양수아님');
    end if;
    
end;

declare
    vnum number := -10;
begin
    
    if vnum > 0 then
        dbms_output.put_line('양수');
    elsif vnum < 0 then -- 언어마다 else if, elsif, elseif 등.. 쓰는 방식이 다름.
        dbms_output.put_line('양수아님');
    else 
        dbms_output.put_line('영');
    end if;
    
end;

-- 특별 상여금

-- 테이블 생성
create table tblBonus (
    seq number primary key,                         -- 지급내역번호(PK)
    pnum number not null references tblInsa(num),   -- 직원번호(FK)
    bonus number not null,                          -- 지급액
    regdate date default sysdate not null           -- 지급날짜    
);

create sequence seqBonus; -- 시퀀스

-- 특정 1명 -> 보너스 지급(직위 따라 다름)
declare
    vjikwi tblInsa.jikwi%type;
    vnum tblInsa.num%type;
    vbasicpay tblInsa.basicpay%type;
    vbonus number;
begin
    
    -- 1. 홍길동의 직위와 직원번호, 급여를 알아낸다. 
    select jikwi, num, basicpay into vjikwi, vnum, vbasicpay from tblInsa where name = '홍길동';
    
    -- 2. 직위에 따른 보너스 금액을 산정한다.
    if vjikwi in ('부장', '과장') then -- 부장, 과장이면 보너스를 급여 * 2를 한다.
        vbonus := vbasicpay * 2;
    elsif vjikwi = '대리' or vjikwi = '사원' then -- 대리, 사원이면 보너스를 급여 * 1.5를 한다.
        vbonus := vbasicpay * 1.5;
    end if;
    
    -- 3. 보너스 지급
    insert into tblBonus (seq, pnum, bonus, regdate) 
        values (seqBonus.nextVal, vnum, vbonus, default);
    
end;

select * from tblInsa;
select * from tblBonus;

-- 보너스 받은 사람만 조인
select
    i.name as 직원명,
    i.jikwi as 직위,
    i.basicpay as 급여,
    b.bonus as 보너스
from tblInsa i
    inner join tblBonus b
        on i.num = b.pnum;

case문

-- case문
-- ANSI-SQL(case문)과는 다른 구문
-- 1. ANSI-SQL의 case : 표현식(데이터 하나정도를 반환할 수 있는 구문, 단순함) 정도 수준만 사용 가능
-- 2. PL/SQL의 case : 문장 단위 실행도 가능

-- ANSI-SQL case
select
    name,
    case
        when substr(ssn, 8, 1) = '1' then '남자'
        when substr(ssn, 8, 1) = '2' then '여자'
    end as gender
from tblInsa;

-- tblCountry. continent
declare
    
    vname tblCountry.name%type;
    vcontinent tblCountry.continent%type;
    vresult varchar2(30); -- 결과 저장할 변수
    
begin

    select name, continent into vname, vcontinent from tblCountry where name = '대한민국';
    
    if vcontinent = 'AS' then
        vresult := '아시아';
    elsif vcontinent = 'EU' then
        vresult := '유럽';
    elsif vcontinent = 'AF' then
        vresult := '아프리카';
    else
        vresult := '기타';
    end if;
    
    dbms_output.put_line(vname || '-' || vresult);
    
    case vcontinent -- 자바의 switch case와 비슷함
        when 'AS' then vresult := '아시아';
        when 'EU' then vresult := '유럽';
        when 'AF' then vresult := '아프리카';
        else vresult := '기타';
    end case;
    
    dbms_output.put_line(vname || '-' || vresult);

end;