📌Today I Learned

오라클(plsql)

ex30_plsql - 익명 프로시저

PL/SQL

• Oracle's Procedural Language extension to SQL
• 기존의 ANSI-SQL + 절차 지향 언어 기능 추가
• ANSI-SQL + 확장팩(변수, 제어 흐름(제어문), 객체(메서드) 정의)
  

프로시저, Procedure

• 메서드, 함수 등...
• 순서가 있는 명령어의 집합
• 모든 PL/SQL 구문은 프로시저 내에서만 작성/동작이 가능하다.
• 프로시저 아닌 영역 > ANSI-SQL

1. 익명 프로시저

- 1회용 코드 작성용

2. 실명 프로시저

- 데이터베이스 객체
- 저장용
- 재호출

PL/SQL 프로시저 구조

1. 4개의 블럭(키워드)으로 구성
   • DECLARE
   • BEGIN
   • EXCEPTION
   • END

2. DECLARE
   • 선언부
   • 프로시저 내에서 사용할 변수, 객체 등을 선언하는 영역
   • 생략 가능
     
3. BEGIN ~ END
   • 실행부, 구현부
   • 구현된 코드를 가지는 영역(메서드의 바디부분)
   • 생략 불가능
   • 구현된 코드 > ANSI-SQL + PL/SQL(연산, 제어 등)
     
4. EXCEPTION
   • 예외처리부
   • catch 역할, 3번 영역 try 역할
   • 생략 가능
     
     

자료형 + 변수

PL/SQL 자료형

• ANSI/SQL과 동일

변수 선언하기

• 변수형 자료형 [not null][default 값];
  
  

PL/SQL 연산자

• ANSI/SQL과 동일

대입 연산자

• ANSI/SQL 대입 연산자
  ex) update table set column = '값';
• PL/SQL 대입 연산자
  ex) 변수 := '값';
  
  

변수 > 어떤 용도로 사용?
- select 결과를 담는 용도(매우중요)
- select into 절(PL/SQL)

PL/SQL 프로시저 안에서는 순수한 SELECT문은 올 수 없다.(절대)
PL/SQL 프로시저 안에서는 SELECT INTO문만 사용한다.

into는 한번만.
into 사용 시
1. 컬럼의 개수와 변수의 개수 일치
2. 컬럼의 순서와 변수의 순서 일치
3. 컬럼과 변수의 자료형 일치

타입 참조

%type

• 사용하는 테이블의 특정 컬럼값의 스키마를 알아내서 변수에 적용
• 복사되는 정보
  • a. 자료형
  • b. 길이
• 컬럼 1개 참조
  
  

%rowtype

• 행 전체 참조(여러 개의 컬럼을 한번에 참조)
• %type의 집합형
• 레코드 전체(여러 개 컬럼)를 하나의 변수에 저장
  
  

제어문
1. 조건문
2. 반복문
3. 분기문

case문

• ANSI-SQL의 case문과 거의 유사
• 자바의 switch문, 다중 if문
  
  

반복문

1. loop

• 단순 반복

declare
    vnum number := 1;
begin
    loop
        dbms_output.put_line(vnum);
        vnum := vnum + 1;
        
        exit when vnum > 10; -- 조건부 break
        
    end loop;
end;

2. for loop

• 횟수 반복(자바 for)
• loop 기반

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;

3. while loop

• 조건 반복(자바 while)
• loop 기반

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

select > 결과셋 > PL/SQL 변수 대입

1. select into
   - 결과셋의 레코드가 1개일 때만 사용이 가능하다.
   
2. cursor
   - 결과셋의 레코드가 N개일 때 사용한다.
   - 루프 사용
   

declare
    변수 선언;
    커서 선언;
begin
    커서 열기;
        loop
            데이터 접근(루프 1회전 > 레코드 1개) <- 커서 사용
        end loop;
    커서 닫기;
end;
--------------------------------------------------------
-- 걍 외워.
declare
    cursor vcursor
    is
    select * from tblInsa;
    vrow tblInsa%rowtype;
begin
    open vcursor;
        loop
            fetch into;
            exit when;
        end loop;
    close vcursor;
end;


-- 정석
declare
    cursor vcursor
    is
    select * from tblInsa;
    vrow tblInsa%rowtype;
begin
    open vcursor;
        loop
            fetch vcursor into vrow;
            exit when vcursor%notfound;
            
            dbms_output.put_line(vrow.name);
            
        end loop;
    close vcursor;
end;


-- 간결
declare
    cursor vcursor
    is
    select * from tblInsa;
    vrow tblInsa%rowtype;
begin
    for vrow in vcursor loop  -- loop + fetch into + vrow + exit when  (for 루프변수 in 집합 loop)
        dbms_output.put_line(vrow.name);
    end loop;
end;

예외처리
: 실행부(begin-end)에서 발생하는 예외를 처리하는 블럭 > exception 블럭

-- 예외 발생 > DB 저장
create table tblLog(
    seq number primary key,                 -- PK
    code varchar2(7) not null check (code in ('A001', 'B001', 'B002', 'C001')), --에러 상태 코드
    message varchar2(1000) not null,        -- 에러 메시지
    regdate date default sysdate not null   -- 에러 발생 시각
);

create sequence seqLog;

select * from tblLog;

declare
    vcnt number;
    vname tblInsa.name%type;
begin
    select count(*) into vcnt from tblCountry where name = '태국'; -- ORA-01476: divisor is equal to zero
    dbms_output.put_line(100 / vcnt);
    
    select name into vname from tblInsa where num = 1000; -- ORA-01403: no data found
    dbms_output.put_line(vname);

exception
    when ZERO_DIVIDE then
        dbms_output.put_line('0으로 나누기');
        insert into tblLog values (seqLog.nextVal, 'B001', '가져온 레코드가 없습니다.', default);
        
    when NO_DATA_FOUND then
        dbms_output.put_line('데이터 없음');        
        insert into tblLog values (seqLog.nextVal, 'A001', '직원이 존재하지 않습니다.', default);
        
    when others then
        dbms_output.put_line('나머지 예외 ');
        insert into tblLog values (seqLog.nextVal, 'C001', '기타 예외가 발생했습니다.', default);
end;