[Database] 19장 PL/SQL 1

 

PL/SQL (Procedural Language/SQL)

 

: 서버에서 절차적인 처리를 위해 표준 SQL을 확장한 절차적 언어

블록(block) 구조로 여러 SQL문을 한번에 실행할 수 있다.

 

SQL과 PL/SQL의 장단점

 

 

Anonymous block은 하단의 변수선언, if, case, while 문의 예제를 확인하면 된다.

 

PL/SQL로 할 수 있는 것

 

 

PL/SQL 블록 구조

 

 

확장 요소

 

 

변수의 종류

 

 

변수의 데이터 타입1

 

 

변수의 데이터 타입2

 

 

 

변수 선언

 

-- 환경설정 변수 변경. 매 세션마다 초기화 되므로 매번 재설정 해야 함
set serveroutput on

declare
    v_i     number(9,2) := 0;
    v_name  varchar2(20);
    c_pi    constant number(8,6) := 93.141592;
    v_flag  boolean not null := true;
    v_date  varchar2(10) := to_char(sysdate, 'YYYY-MM-DD');
begin
    v_name := '홍길동';
    dbms_output.enable;
    dbms_output.put_line('v_i:' || v_i);
    dbms_output.put_line('v_name:' || v_name);
    dbms_output.put_line('c_pi:' || c_pi);
    dbms_output.put_line('v_date:' || v_date);
end;
/

 

결과 화면1

 

 

 

IF 문

 

declare
    v_num number := 37;
begin
    dbms_output.enable;
    
    if mod(v_num, 2) = 0 then
        dbms_output.put_line(v_num || '는 짝수');
    else
        dbms_output.put_line(v_num || '는 홀수');
    end if;
end;
/

 

결과 화면2

 

 

declare
    v_num   number := 67;
begin
    dbms_output.enable;
    if v_num > 90 then
        dbms_output.put_line('수');
    elsif v_num > 80 then
        dbms_output.put_line('우');
    elsif v_num > 70 then
        dbms_output.put_line('미');
    else
        dbms_output.put_line('분발합시다.');
    end if;
end;
/

 

결과 화면3

 

 

declare
-- 테이블.컬럼명%type : 테이블의 컬럼의 타입과 동일하게 지정
    v_avg_sale  prod.prod_sale%type; -- number(10, 0)
    v_sale  number := 500000;
begin
    dbms_output.enable;
    
    select avg(prod_sale) into v_avg_sale from prod; -- avg(prod_sale)의 값을 v_avg_sale에 값을 넣음
    
    if  v_sale < v_avg_sale then
        dbms_output.put_line('평균 단가가 500000 초과입니다.');
    else
        dbms_output.put_line('평균 단가가 500000 이하입니다.');
    end if;
end;
/

 

결과 화면4

 

 

declare
    v_mem_id    varchar2(10) := 'e001';
    v_mem_mileage   member.mem_mileage%type;
    v_mem_name  member.mem_name%type;
    v_mile  number := 5000;
begin
    dbms_output.enable;
    
    select mem_mileage, mem_name
    into v_mem_mileage, v_mem_name
    from member
    where mem_id = v_mem_id;
    
    if v_mile < v_mem_mileage then
        dbms_output.put_line('VIP 회원(' || v_mem_name || ', ' || v_mem_mileage || ')');
    else
        dbms_output.put_line('일반 회원(' || v_mem_name || ', ' || v_mem_mileage || ')');
    end if;
end;
/

 

결과 화면5

 

 

 

CASE 문

 

declare
    v_num   number := 77;
begin
    v_num := trunc(v_num / 10);
    
    case v_num
        when 10 then
            dbms_output.put_line('수' || '(' || v_num || ')');
        when 9 then
            dbms_output.put_line('우' || '(' || v_num || ')');
        when 8 then
            dbms_output.put_line('미' || '(' || v_num || ')');
        when 7 then
            dbms_output.put_line('양' || '(' || v_num || ')');
        else
            dbms_output.put_line('분발합시다.');
    end case;
end;
/

 

결과 화면6

 

 

 

WHILE 문

 

declare
    v_sum   number := 0;
    v_var   number := 1;
begin
    while v_var <= 10 loop
        v_sum := v_sum + v_var;
        v_var := v_var + 1;
    end loop;
    dbms_output.put_line('1부터 10까지의 합 = ' || v_sum);
end;
/

 

결과 화면7

 

 

declare
    v_id    number := 1;
begin
    while v_id < 20 loop
        dbms_output.put_line(rpad('a', v_id, '*'));
        v_id := v_id + 2;
    end loop;
end;
/

 

결과 화면8

-- lpad : 왼쪽에 블랭크를 부가하는 함수
select lpad('A', 2) from dual;
select lpad('A', 3, 'B') from dual;

-- rpad : 오른쪽에 블랭크를 부가하는 함수
select rpad('A', 2) from dual;
select rpad('A', 3, 'B') from dual;

 

 

declare
    v_id    number := 1;
    v_id2   number := 10;
begin
    dbms_output.put_line('');
    while v_id < 20 loop
        dbms_output.put(rpad('a', v_id2, ' '));
        dbms_output.put_line(rpad('b', v_id, '*'));
        v_id := v_id + 2;
        v_id2 := v_id2 - 1;
    end loop;
end;
/

 

결과 화면9

 

 

declare -- 선언문
    v_id    number := 1; -- v_id 숫자변수 선언후 초기값 1저장
    v_id2   number := 10; -- v_id2 숫자변수 선언후 초기값 10저장
begin -- PL/SQL 실행문 시작
    dbms_output.put_line(''); -- dbms_output.put_line 초기인쇄 (에러방지)
    while v_id < 20 loop -- v_id가 초기값 1로 시작해서 20보다 작은 동안 while문을 수행한다.
        dbms_output.put(rpad(' ', v_id2, ' ')); -- v_id2 크기의 공간에서 왼쪽 ' '의 1자리를 제외한 자리수 만큼 오른쪽으로 공백(' ')을 채운다음 output한다.
        dbms_output.put_line(rpad('*', v_id, '*')); -- v_id 크기의 공간에서 왼쪽 '*'의 1자리를 제외한 자리수 만큼 오른쪽으로 공백('*')을 채운다음 output한다.
        v_id := v_id + 2; -- v_id의 초기값은 1이고 2씩 증가한다.
        v_id2 := v_id2 - 1; -- v_id2의 초기값은 10이고 1씩 감소한다.
    end loop; --while 종료문
end; -- PL/SQL 실행문 종료
/

 

결과 화면10

 

 

-- 환경설정 변수 변경. 매 세션마다 초기화 되므로 매번 재설정 해야 함
set serveroutput on

-- 구구단 만들기
declare
    v_i number := 2;
    v_j number := 1;
begin
    while v_i < 10 loop
        dbms_output.put_line('===========');
        dbms_output.put_line(v_i || ' 단 ');
        dbms_output.put_line('===========');
        
        v_j := 1;
        
        while v_j < 10 loop
            dbms_output.put_line(v_i || ' X ' || v_j || ' = ' || v_i*v_j);
            
            v_j := v_j +1;
        end loop;
        
        v_i := v_i+1;
    end loop;
end;
/

 

결과 화면11 : 2단부터 9단까지 출력된다.

 

 

 

커서

 

커서 설명

 

 

declare
    v_nm varchar2(20);
begin
    select lprod_nm into v_nm from lprod where lprod_gu = 'P201';
    if sql%found then
        dbms_output.put_line('받은 값 = ' || v_nm);
        dbms_output.put_line('행 수 = ' || sql%rowcount);
    end if;
end;
/

 

결과 화면12

 

 

lprod의 lprod_id가 9기까지 있어야 아래 코드를 실행할 수 있다.

declare
    v_add   number(5) := 1000;
    v_code  char(4) := ' ';
    v_id    number(5);
begin
    select max(lprod_id) into v_id from lprod;
    while v_add <= 1005 loop
        v_add := v_add + 1;
        v_id := v_id + 1;
        v_code := 'T' || substr(to_char(v_add), -3);
        insert into lprod(lprod_id, lprod_gu, lprod_nm)
            values(v_id, v_code, 'loop test');
        if sql%found then
            dbms_output.put_line('신규코드' || v_code || '가 추가 되었음');
        end if;
    end loop;
end;
/

 

결과 화면13

 

결과 화면14 : lprod 테이블 조회 화면

 

 

begin
    select max(lprod_id) into v_id from lprod;
    
    while v_add >= 1002 loop
        delete from lprod where lprod_id = v_id; -- v_id : 15, 14, 13, 12, 11,10
        
        v_add := v_add-1;
        v_id := v_id-1;
        v_code := 'T' || substr(to_char(v_add), -3);
        
        if sql%found then
            dbms_output.put_line('기존코드' || v_code || '가 삭제되었습니다.');
        end if;
    end loop;
end;
/

 

결과 화면15

 

 

 

GOTO 문

 

: 실행처리를 임의의 지정으로 이동하는 문

 

declare
    v_sum   int := 0;
    v_var   int := 1;
    
begin
    <<mylabel>>
    v_sum := v_sum + v_var;
    v_var := v_var + 1;
    if v_var <= 10 then
        goto mylabel;
    end if;
    dbms_output.put_line(v_sum);
    dbms_output.put_line(v_var);
end;
/

 

결과 화면16

 

 

 

LOOP 문

 

: 조건이 없는 단순한 무한 반복문

exit 문을 사용하여 반복문을 빠져나가게 해야 한다.

 

declare
    v_sum   number := 0;
    v_var   number := 1;
begin
    loop
        v_sum := v_sum + v_var;
        v_var := v_var + 1;
        if v_var > 10 then
            exit;
        end if;
    end loop;
    dbms_output.put_line('1부터 10 까지의 합 = ' || v_sum);
end;
/

 

결과 화면17

 

 

 

EXIT 문

 

: 반복문을 빠져나가는 문.

when을 사용하여 조건에 따라서 빠져나간다.

 

declare
    v_sum   number := 0;
    v_var   number := 1;
begin
    loop
        v_sum := v_sum + v_var;
        v_var := v_var + 1;
        exit when v_var > 10;
    end loop;
    dbms_output.put_line('1부터 10까지의 합 = ' || v_sum);
end;
/

 

결과 화면18

 

 

 

FOR 문

 

-- index는 1씩 증가
-- reverse 가 사용될 경우 1씩 감소
-- in 다음에 select 문, cursor 문이 올 수 있음

for index in [reverse] 최소값..최대값 loop
	처리문장들;
end loop;

 

 

begin
    for i in 1..10 loop
        dbms_output.put_line('i = ' || i);
    end loop;
end;
/

 

결과 화면19

 

 

 

composite 데이터 타입

 

형태에 따라 콜렉션을 사용할 지 레코드를 사용할지 나뉜다.

 

콜렉션은 배열 형태의 타입을 말하며 varray 와 table로 구분된다.

varray : 고정길이를 가진 배열로, 첨자를 이용해 접근한다.

table : 가변길이를 가진 배열로, 첨자 또는 키로 접근한다.

 

레코드는 테이블 형태의 타입을 말하며 record나 %rowtype로 구분된다.

record : 여러 데이터 형이 조합된 구조체 형식이다.

%rowtype : 기존 테이블의 이름과 타입을 사용한다.

 

 

1. varray 사용

-- varray 의 사용
type 타입명 is {varray | varying array} (사이즈) of 데이터 타입 [not null];

 

 

declare
    type starcraft is varray(20) of varchar2(10);
    v_star starcraft;
begin
    v_star := starcraft('Terran', 'Protos');
    v_star.extend;
    v_star(3) := 'Zerg';
    dbms_output.put_line('스타크래프트 종족 : ' || v_star.count);
    for i in v_star.first..v_star.last loop
        dbms_output.put_line(i || '번째 종족 : ' || v_star(i));
    end loop;
end;
/

 

결과 화면20

 

 

2. table 사용

-- table의 사용 : 가변적이기에 사이즈를 지정하지 않음.
-- 첨자 기반
type 타입명 is table of 데이터 타입 [not null];

-- 키 기반
type 타입명 is table of 데이터 타입 [not null]
index by [pls_integer | binary_integer | varchar2(n)];

 

 

declare
    type lprod_nm_table is table of varchar2(40)
        index by pls_integer;
    t_lprod_nm lprod_nm_table; -- t_lprod_nm 이름으로 사용할 수 있게 재정의함. 사용하기 위해서는 필수
begin
    for l_list in (select lprod_id, lprod_nm from lprod) loop -- l_list는 위에 선언하지 않았지만 for 안에 있을 때는 자동적으로 정의해줌.
        t_lprod_nm(l_list.lprod_id) := l_list.lprod_nm; -- lprod_id를 키로 lprod_nm를 value로 매칭함.
        dbms_output.put_line(l_list.lprod_id || '=' || l_list.lprod_nm);
    end loop;
    
    dbms_output.put_line('갯수 = ' || t_lprod_nm.count);
    for i in t_lprod_nm.first..t_lprod_nm.last loop
        if t_lprod_nm.exists(i) then -- 첫번째부터 마지막 까지 데이터가 있는지 확인
            dbms_output.put_line (i || ' ' || t_lprod_nm(i));
        end if;
    end loop;
end;
/

 

결과 화면21

 

 

 

exception 처리

 

: error가 발생하면 exception을 발생시켜 해당 블록을 중지하고 예외처리 부분으로 이동하게 한다.

 

- 예외 유형

1. 정의된 Oracle Server Error

2. 정의 되지 않은 Oracle Server Error

3. 사용자 정의 Error

 

 

---

 

1. 정의된 Oracle Server Error

예외 처리 종류

 

 

declare
    v_name  varchar2(20);
begin
    select lprod_nm into v_name from lprod where lprod_gu = 'P201';
    dbms_output.put_line('분류명 = ' || v_name);
exception
    when no_data_found then
        dbms_output.put_line('해당 정보가 없습니다.');
    when too_many_rows then
        dbms_output.put_line('한개 이상의 값이 나왔습니다.');
    when others then
        dbms_output.put_line('기타 에러 : ' || sqlerrm);
end;
/

 

결과 화면22

 

 

 

2. 정의 되지 않은 Oracle Server Error

 

- 순서

2-1. 선언부에 예외의 이름을 지정한다.

2-2. pragma를 기술하고 exception_init으로 예외 이름과 에러번호를 등혹한다. (pragma는 실행될 때 처리되지 않는 명령문임을 알려주는 예약어)

2-3. exception 영역에 해당 예외 처리를 한다.

 

-- 정의되지 않은 예외
declare
    exp_reference exception;
    pragma exception_init(exp_reference, -2292); -- 예외 이름 exp_reference
begin
    delete from lprod where lprod_gu = 'P101';
    dbms_output.put_line('분류 삭제');
exception
    when exp_reference then
        dbms_output.put_line('자식 테이블에 데이터가 존재하여 삭제 불가 : ' || sqlerrm);
    when others then
        dbms_output.put_line(sqlcode || ' ' || sqlerrm);
end;
/

 

결과 화면23

 

 

 

3. 사용자 정의 Error

 

- 순서

3-1. 선언부에 예외 이름을 지정한다.

3-2. 실행부에서 raise 문장을 사용한다.

3-3. exception 영역에 해당 예외 처리를 한다.

 

-- 사용자 정의 예외인 경우
accept p_lgu prompt '등록하려는 분류코드 입력 : '
declare
    exp_lprod_gu exception;
    v_lgu varchar2(10) := upper('&p_lgu');
begin
    if v_lgu in ('P101', 'P102', 'P201', 'P202' ) then raise exp_lprod_gu;
    end if;
    dbms_output.put_line(v_lgu || '는 등록 가능');
exception
    when exp_lprod_gu then
        dbms_outpur.put_line(v_lgu || '는 이미 등록된 코드 입니다.');
end;
/

 

결과 화면24

 

결과 화면25