DATABASE STEP 31 - PL/SQL

★  PL/SQL

• Oracle's Procedural Language Extension to SQL
• ANSI-SQL 표준 SQL에 절차 지향 언어의 기능을 추가한 SQL
• ANSI-SQL + 자바같은 프로그래밍 언어의 기능 = PL/SQL

 

★  ANSI-SQL

• 비절차 지향 언어
• 순서가 없고 문장간의 행동이 서로 독립적이다.
• 문장 단위 구조 

 

★ 프로시저

• 메소드, 함수 등...
• 순서가 있는 명령어의 집합(ANSI-SQL + PL/SQL)의 집합
• 1. 익명 프로시저
  • - 1회용 코드 작성용
• 2. 실명 프로시저
  
  • 저장(데이터베이스) > DB object
  • 재사용 가능
  • 계정간 공유 가능
• PL/SQL 프로시저 블럭 구조
  
  • 4개의 키워드(블럭)으로 구성
            a. declare
            b. begin
            c. exception
            d. end 
  • a. declare
            - 선언부
            - 프로시저 내에서 사용할 변수, 객체 등을 선언하는 영역
            - 생략 가능 
            
  •  b. begin
            - 실행부, 구현부
            - begin ~ end
            - begin(구현부 시작) ~ end(구현부 끝)
            - 생략 불가능
            - 핵심 파트
            - 구현 코드 > ANSI-SQL + PL/SQL
            
  • c. exception
            - 예외처리부
            - catch 역할
            - 예외 처리 코드를 작성
            - 생략 가능
            
  • d. end
            - begin 블럭의 종료 역할
            - 생략 불가능
• 양식

declare
        변수 선언
        객체 선언
    begin 
        구현할 코드(SQL)
    exception
        예외처리 코드
end;

• 변수 선언하기
  
  • 변수명 자료형 [not null] [default 값];
  • 주로 질의(select)의 결과값을 저장하는 용도
  • 일반적인 데이터를 저장하는 용도
• 대입 연산자
      - ANSI-SQL
          ex) update table set column = 값;
      - PL/SQL 
          ex) 변수 := 값; 
• PL/SQL 시작

set serveroutput on;
set serverout on;

• PL/SQL 종료

set serveroutput off;
set serverout off;

 

■ 사용 ex)

• 메소드 : 선언(저장) > 호출
• 익명프로시저 : 호출

begin
    -- 자바의 System.out.println(); 과 같음
    dbms_output.put_line(100);
    dbms_output.put_line('홍길동');
end;

declare
    num number;
    name varchar2(30);
    today date;
begin
    num := 10;
    dbms_output.put_line(num);
    
    name := '홍길동';
    dbms_output.put_line(name);
    
    today := sysdate;
    dbms_output.put_line(today);
end;

 

■ select into 절 사용 ex)

• PL/SQL의 변수에 ANSI-SQL의 결과값을 저장하는 구문
• into 주의점
  • 1. 컬럼의 개수와 변수의 개수의 동일!!
  • 2. 값을 동일 하게 넣지 않으면 , 해당 에러 발생 : PL/SQL: ORA-00947: not enough values
  • 3. 컬럼 갯수를 동일하게 넣지 않으면, 해당 에러 발생 : PL/SQL: ORA-00913: too many values
  • 4. 컬럼의 순서와 변수의 순서가 일치!!
  • 5. 컬럼과 변수의 자료형이 일치!!

declare

    vname varchar2(30);
    buseo varchar2(30);

begin
	-- vname := select name from tblInsa where num = 1001;
    
    -- PLS-00428: an INTO clause is expected in this SELECT statement
    -- ***** PL/SQL 블럭안에서는 ANSI-SQL의 select문을 사용할 수 없다. > Select into 사용 
    -- ***** PL/SQL 블럭안에서는 select문을 제외한 ANSI-SQL은 그대로 사용 가능하다.(Insert,Update,delete)
    
    -- 대입 연산자 역할 
    -- select 컬럼 into 변수 
    select name into vname from tblInsa where num = 1001;
    dbms_output.put_line(vname);
    
    --      컬럼         변수
    select buseo into buseo from tblInsa where num = 1001;
    dbms_output.put_line(buseo);
    
end;

• ex2) 회사 > 프로젝트 > 직원 중

create table tblTeam (
    num number primary key,
    name varchar2(15) not null,
    buseo varchar2(15) not null,
    jikwi varchar2(15) not null
);

-- 개발부 + 부장
-- 1. ANSI-SQL
-- 2. PL/SQL

-- 1.1 노가다 > 비추천 
select * from tblInsa where buseo = '개발부' and jikwi = '부장';
insert into tblTeam values (1003, '이순애', '개발부', '부장');

-- 1.2 서브쿼리
insert into tblTeam values ((select num from tblInsa where buseo = '개발부' and jikwi = '부장'), 
                                  (select name from tblInsa where buseo = '개발부' and jikwi = '부장'), 
                                  '개발부', '부장');
-- 2. PL/SQL 사용
declare
    vnum number;
    vname varchar2(15);
    vbuseo varchar2(15);
    vjikwi varchar2(15);
begin
    
    -- ***** Select의 결과는 반드시 PL/SQL에 넣어야 한다. > select into 사용
    select num into vnum from tblInsa where buseo = '개발부' and jikwi = '부장';
    select name into vname from tblInsa where buseo = '개발부' and jikwi = '부장';
    select buseo into vbuseo from tblInsa where buseo = '개발부' and jikwi = '부장';
    select jikwi into vjikwi from tblInsa where buseo = '개발부' and jikwi = '부장';
    
    -- vnum, vname, vbuseo, vjikwi 확보
    insert into tblTeam values (vnum, vname, vbuseo, vjikwi);
    
end;

 

■ 타입 참조 ex)

• 변수를 선언할 때 같이 사용
• 1. %type
          - 컬럼 1개 참조 
          - 사용하는 테이블의 특정 컬럼의 스키마를 알아내서 변수에 적용
          - 복사되는 정보
              a. 자료형
              b. 길이
• 2. %rowtype
          - 레코드 전체 참조(모든 컬럼 참조)
          - 와일드 카드의 성질
• 1. %type ex)

declare
    vname tblInsa.name%type;
    vbuseo tblInsa.buseo%type;
    vjikwi tblInsa.jikwi%type;
begin
    select name, buseo, jikwi into vname, vbuseo, vjikwi from tblInsa where num = 1002;
    
    dbms_output.put_line(vname);
    dbms_output.put_line(vbuseo);
    dbms_output.put_line(vjikwi);
end;

select * from tblTeam;

-- 특정 직원에게 보너스 지급 > 내역 저장
-- 보너스 = basicpay * 1.5

create table tblBonus (
    seq number primary key,
    num number(5) not null references tblInsa(num),
    bonus number not null
);

create sequence seqBonus;

-- 1.
select * from tblInsa where buseo ='총무부' and jikwi = '부장';
insert into tblBonus values (seqBonus.nextVal, 1046, 2650000*1.5);

-- 2.
insert into tblBonus values (seqBonus.nextVal, 
                                    (select num from tblInsa where buseo ='총무부' and jikwi = '부장'), 
                                    (select basicpay from tblInsa where buseo ='총무부' and jikwi = '부장') * 1.5);

select * from tblBonus;

-- 3.
declare
    vnum        tblInsa.num%type;
    vbasicpay   tblInsa.basicpay%type;
begin
    select num, basicpay into vnum, vbasicpay from tblInsa where buseo ='총무부' and jikwi = '부장';
    
    insert into tblBonus values (seqBonus.nextval, vnum, vbasicpay * 1.5);
    

    
end;

select * from tblBonus;

• 2. %rowtype
• ex)

declare 
    vnum        tblInsa.num%type;
    vname       tblInsa.name%type;
    vbuseo      tblInsa.buseo%type;
    vjikwi         tblInsa.jikwi%type;
    vssn          tblInsa.ssn%type;
    vcity          tblInsa.city%type;
    vbasicpay    tblInsa.basicpay%type;
    vsudang      tblInsa.sudang%type;
    vibsadate     tblInsa.ibsadate%type;
    vtel            tblInsa.tel%type;

begin
    select num, name, buseo, jikwi, ssn, city, basicpay, sudang, ibsadate, tel
            into vnum, vname, vbuseo, vjikwi, vssn, vcity, vbasicpay, vsudang, vibsadate, vtel
        from tblInsa where name = '홍길동';
end;

-- 좋은 예
declare 
    vrow tblInsa%rowtype; --총 10개의 컬럼 참조
    vrow2 tblInsa%rowtype; --총 10개의 컬럼 참조
begin
    
    -- ex) 덜 좋은 예
    select 
        num, name, ssn, ibsadate, city, tel, buseo, jikwi, basicpay, sudang
        into vrow
    from tblInsa where name = '이순신';
    
    -- ex) 가장 좋은 예
    select 
        * into vrow2
    from tblInsa where name = '이순애';
    
    --dbms_output.put_line(vrow);
    dbms_output.put_line(vrow.name);
    dbms_output.put_line(vrow2.name);
    
end;

• '하하하' > 성전환 > tblWomen 이동
  
  • 1. tblMen > select > 정보
  • 2. tblWomen > insert(1번) > 복사
  • 3. tblMen > delete
• ex)

declare
    vrow tblMen%rowtype;
begin
    --1.
    select * into vrow from tblMen where name = '하하하';
    
    --2. 
    insert into tblWomen values(vrow.name, vrow.age, vrow.height, vrow.weight, vrow.couple);
    
    --3.
    delete from tblMen where name = '하하하';
    
end;

select * from tblMen;
select * from tblWomen;

 

■ 제어문

• 1. 조건문
• if 문 ex)

declare
    vnum number := 10;
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('양수');
    elsif vnum < 0 then
        dbms_output.put_line('음수');
    else
        --dbms_output.put_line('양수 아님');
        null; -- 빈블럭 만들 때 사용
    end if;
    
end;

-- 특정 직원 선택 > 보너스 지급 > 간부(basicpay * 1.5), 대리,사원(baiscpay * 2)
declare
    vnum        tblInsa.num%type;
    vbasicpay   tblInsa.basicpay%type;
    vjikwi        tblInsa.jikwi%type;
    vbonus      number;
begin
    --1.
    select num, basicpay, jikwi into vnum, vbasicpay, vjikwi from tblInsa where name = '이순신';
    
    --2.
    if vjikwi = '부장' or vjikwi = '과장' then
        vbonus := vbasicpay * 1.5;
    elsif vjikwi in ('대리','사원') then
        vbonus := vbasicpay * 2;
    end if;
    
    --3.
    insert into tblBonus values (seqBonus.nextVal, vnum, vbonus);
    
end;

• if문 ex2)

declare
    vcontinent tblCountry.continent%type;
    vresult varchar2(30);
begin
    select continent into 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(vresult);

• case문 ex1)

case
        when vcontinent = 'AS' then vresult := '아시아';
        when vcontinent = 'EU' then vresult := '유럽';
        when vcontinent = 'AF' then vresult := '아프리카';
        else vresult := '기타';
end case;
    
    dbms_output.put_line(vresult);
    
case vcontinent
        when 'AS' then vresult := '아시아';
        when 'EU' then vresult := '유럽';
        when 'AF' then vresult := '아프리카';
        else vresult := '기타';
end case;

 

■ 반복문

• 1. loop 문
  • 단순 반복
• 2. for loop
  • 횟수 반복
  • loop 기반
• 3. while loop
  • 조건 반복
  • loop 기반
• 1. loop 문 ex)

loop
        -- 실행문;
        dbms_output.put_line(to_char(sysdate, 'hh24:mi:ss'));
        dbms_output.put_line(vnum);
        
        vnum := vnum+1;
        
        exit when vnum > 10; -- 조건 만족 > loop 탈출
        
    end loop;

end;

• 1. loop 문 ex2) 더미데이터 생성

-- 더미 데이터 추가 x 1000 건
create table tblLoop (
    seq number primary key,
    data varchar2(30) not null
);

create sequence seqLoop;

insert into tblLoop values (seqLoop.nextVal, '데이터1');
insert into tblLoop values (seqLoop.nextVal, '데이터2');
insert into tblLoop values (seqLoop.nextVal, '데이터3');

declare
    vnum number := 4;
begin
    
    loop
        insert into tblLoop values (seqLoop.nextVal, '데이터' || vnum);
        vnum := vnum +1;
        
        exit when vnum > 1000;
        
    end loop;
    
end;

select * from tblLoop;
select count(*) from tblLoop;

• 2. for loop ex)

begin
    
    for i in 1..10 loop
        
        dbms_output.put_line(i);
        
    end loop;

end;

• 2. for loop ex2) 구구단 만들기

create table tblGugudan (
    dan number,
    num number,
    result number not null,
    
    constraint tblgugudan_dan_num_pk primary key(dan,num)
);

begin
    for dan in 2..9 loop
        for num in 1..9 loop
            insert into tblGugudan (dan, num, result) values (dan, num, dan*num);
        end loop;
    end loop;

end;

select * from tblGugudan;

• 3. while loop ex)

declare
    vnum number := 1;
begin
    while vnum <= 10 loop
    
        dbms_output.put_line(vnum);
        vnum := vnum +1;
    
    end loop;
end;

 

■ cursor + loop

• 결과셋의 레코드가 N개일때 사용한다.
• 사용양식

사용양식
    
    declare
        변수 선언;
        커서 선언;
    begin
        
        커서 열기;
            loop
                커서 사용 > 데이터 접근 > 조작        
            end loop;
        커서 닫기;
        
    end;

• ex)

declare
    cursor vcursor 
    is 
    select name from tblInsa;
    vname tblInsa.name%type;
begin
    open vcursor; -- select문 실행 > 결과셋에 커서 연결(참조) > 탐색 > 자바의 Iterator

• ex2) 주로 사용하는 cursor 문

declare
    cursor vcursor is select name from tblInsa;
    vname tblInsa.name%type;
begin
    open vcursor; 
        
        loop
            fetch vcursor into vname;
            exit when vcursor%notfound; 
            dbms_output.put_line(vname);
        end loop;
        
    close vcursor;
    
end;