• Procedural Language extension to SQL의 약자이다. 오라클 자체에 내장되어 있는 절차적 언어로써, SQL의 단점을 보완하여 SQL 문장 내에서 변수의 정의, 조건처리, 반복처리 등을 지원한다.

  declare
  	[선언부] --선택
  begin
  	[실행부]
  exception
  	[예외처리부]  --선택
  end;
  /
  
  begint [실행부] end; /  --만으로 실행이 가능하다
  

print 하기

SET SERVEROUTPUT ON;  --defualt가 off상태이기 때문에 on으로 꼭 바꿔줘야 함

BEGIN
DBMS_OUTPUT.PUT_LINE(‘HELLO WORLD’);
END;
/

• HELLO WORLD가 출력된다.

• PL/SQL 블록 내에서는 한 문장을 종료할 때 마다 세미콜론(;)을 사용함

• END 뒤에 세미콜론(;)을 사용하여 하나의 블록이 끝났다는 것을 명시

• PL/SQL 블록의 작성은 메모장과 같은 편집기를 통해 sql 파일로 저장할 수도 있고, SQL> 프롬프트에서 바로 작성할 수도 있음

• SQL*PLUS 환경에서는 DECLARE나 BEGIN 이라는 키워드로 PL/SQL블록이 시작하는 것을 알 수 있음

• 단일행 주석은 -- 이고, 여러행 주석은 /* */ 이다.

• 쿼리문을 수행하기 위해서 /가 입력되어야 하며, PL/SQL 블록은 행에 /가 있으면 종결된 것으로 간주함

변수선언

• 변수명 [constant] 자료형(바이트크기) [not null][:=초기값]

DECLARE
EMP_ID NUMBER;
EMP_NAME VARCHAR2(30);
BEGIN
EMP_ID := 888;
EMP_NAME := ‘배장남’;
DBMS_OUTPUT.PUT_LINE(‘EMP_ID : ’ || EMP_ID);
DBMS_OUTPUT.PUT_LINE(‘EMP_NAME : ’ || EMP_NAME);
END;
/

• 변수의 자료형
  • 문자형 : varchar2, blob, clob
  • 숫자형 : number
  • 날짜 : date
  • 부울형(Boolean) : boolean := true,false,null
  • 상수
    • 일반변수와 유사하나 constant라는 키워드가 자료형 앞에 붙는다
    • 선언시 반드시 값을 할당해주어야 한다.
• [복합 자료형]
  • 레코드(Record)
  • 컬렉션(Collection)

예제 실행하기

--예제 2)
--사원번호를 입력 받아서 사원의 사원번호, 이름, 급여, 입사일을 출력하시오

declare
    vemp_id employee.emp_id%type;
    vemp_name employee.emp_name%type;
    vsalary employee.salary%type;
    vhire_date employee.hire_date%type;
begin
    select emp_id, emp_name, salary, hire_date
    into vemp_id, vemp_name, vsalary, vhire_date
    from employee
    where emp_id = **&emp_id; --1**
    dbms_output.put_line('사번 : ' || vemp_id);
    dbms_output.put_line('이름 : ' || vemp_name);
    dbms_output.put_line('급여 : ' || vsalary);
    dbms_output.put_line('입사일 : ' || vhire_date);
    end;
    /

1. &입력받을 메세지 명을 해주면 직접 입력받는 창이 떠서 변수의 값을 그때끄때 입력할수있다.

PL/SQL의 선택문

모든 문장을 기술한 순서대로 수행됨

문장을 선택적으로 수행하려면 if문을 사용하면됨

if - then - end if문

declare
    inputn number;
begin
    inputn := '&입력숫자';
    case inputn
    when 1 then dbms_output.put_line(inputn);
    when 2 then dbms_output.put_line(inputn);
    when 3 then dbms_output.put_line(inputn);
    else  dbms_output.put_line('입력오류!');
    end case;
    end;
    /

예제

--## 실습 문제 ##
--사번을 입력 받은 후 급여에 따라 등급을 나누어 출력하도록 하시오 
--그때 출력 값은 사번,이름,급여,급여등급을 출력하시오

--0만원 ~ 99만원 : F
--100만원 ~ 199만원 : E
--200만원 ~ 299만원 : D
--300만원 ~ 399만원 : C
--400만원 ~ 499만원 : B
--500만원 이상(그외) : A

declare
    vemp_id employee.emp_id%type;
    vemp_name employee.emp_name%type;
    vsalary employee.salary%type;
    vsal_level char(1);
    
begin
    select emp_id, emp_name, salary 
    into vemp_id, vemp_name, vsalary
    from employee
    where emp_id = &사번;
  if (vsalary/10000 between 0 and 99)
  then vsal_level := 'f';
  elsif (vsalary/10000 between 100 and 199)
  then vsal_level := 'e';  
  elsif (vsalary/10000 between 200 and 299)
  then vsal_level := 'd'; 
   elsif (vsalary/10000 between 300 and 399)
  then vsal_level := 'c';
   elsif (vsalary/10000 between 400 and 499)
  then vsal_level := 'b';
  else vsal_level := 'a';
  end if;
  
  dbms_output.put_line('사번 : ' ||vemp_id);
  dbms_output.put_line('이름 : ' ||vemp_name);
  dbms_output.put_line('월급 : ' ||vsalary);
  dbms_output.put_line('등급 : ' ||vsal_level);
  end;
  /
  
  
  
  declare
    vemp_id employee.emp_id%type;
    vemp_name employee.emp_name%type;
    vsalary employee.salary%type;
    vsal_level char(1);
  begin
    select emp_id, emp_name, salary 
    into vemp_id, vemp_name, vsalary
    from employee
    where emp_id = &사번;
  case floor(vsalary/1000000)
  when 1
  then vsal_level := 'f';
   when 2
  then vsal_level := 'e';
   when 3
  then vsal_level := 'd';
   when 4
  then vsal_level := 'c';
   when 5
  then vsal_level := 'b';
    else vsal_level := 'a';
   end case;
  
  dbms_output.put_line('사번 : ' ||vemp_id);
  dbms_output.put_line('이름 : ' ||vemp_name);
  dbms_output.put_line('월급 : ' ||vsalary);
  dbms_output.put_line('등급 : ' ||vsal_level);
  end;
  /

Loop

• 카운트용 변수가 자동으로 선택됨, 따로 변수 선언할 필요가 없음

begin
    for n in 1..5 loop
        dbms_output.put_line(n);
        end loop;
        end;
        /

begin
    for n in **reverse** 1..5 loop
        dbms_output.put_line(n);
        end loop;
        end;
        /

--출력값
1
2
3
4
5

---
5
4
3
2
1

for반복문

DECLARE
BEGIN
FOR N IN 1..5 LOOP
DBMS_OUTPUT.PUT_LINE(N);
END LOOP;
END;
/

while 반복문으로 구구단 홀수단만 출력하기

DECLARE
    DAN NUMBER;
    N NUMBER := 1;
BEGIN
    DAN := '&단';
    IF DAN BETWEEN 2 AND 9 THEN
        WHILE N <= 9 LOOP  --1
            IF N = 1 THEN  --2
                DBMS_OUTPUT.PUT_LINE(DAN || ' * ' || N || ' = ' || DAN*N);       
            END IF; --3
            N := N + 1; --4
            CONTINUE WHEN MOD(N,2) = 0; --5
            DBMS_OUTPUT.PUT_LINE(DAN || ' * ' || N || ' = ' || DAN*N);   
        END LOOP;    
    ELSE DBMS_OUTPUT.PUT_LINE('2 ~ 9 사이의 숫자를 입력하세요. bye~');
    END IF;    
END;
/

1. while은 n이 9가 될때까지 반복한다
   1. while문에서 중요한점은 for문과 다르게 미리 n을 선언하고 그 값을 초기화 해야 한다는 것이다.
2. if문으로 n이 1일때 입력값 *n을 무조건 한다
3. 1단만 입력하고 if종료
4. n은 1씩 증가한다
5. 4에서 1증가한 n이 mod(n,2) → n을 2로 나눈 나머지 값 구하는 함수 자바의 %와 같다
   1. 그 값이 0일때 짝수이다
   2. countinue 반복문을 뛰어넘는다,
   3. CONTINUE WHEN MOD(N,2) = 0
      1. 뛰어넘는다 mod(n,2)=0일때
   4. 짝수 문일때는 곱셈식을 계산하지 않는다

예외처리

declare
begin
exceptiopn  -예외처리
end;

exception의 종류

1. access_info_null
2. case_not_found
3. collection_is_null
4. cursor_already_open
5. login_denide
6. no_data_found

처리방법

exception
	when 예외이름1 then 처리문장1
	when 예외이름2 then 처리문장2
end;
/

PL/SQL과 관련된 오라클 객체

• function