PL/SQL 기초 문법

PL/SQL(Procedural Language Extension to SQL)

PL/SQL은 기존의 SQL에 있는 조인, 서브 쿼리 등만을 이용해서 처리하기 힘든 비즈니스 로직을 처리하기 위해 사용한다. 이를 위해 PL/SQL은 집합적 언어인 SQL을 일반 프로그래밍 언어처럼 절차적으로 사용한다는 특징이 있다.

--매번 켤 때마다 설정
set serveroutput on;

---

DECLARE --선언부
--변수 선언
v_no NUMBER := 10;
v_hireDate VARCHAR2(30) := TO_CHAR(SYSDATE, 'YYYY/MM/DD');

--상수 선언
c_message CONSTANT VARCHAR2(50) := '메세지';


BEGIN --실행부
--DMS_OUTPUT패키지 안의 PUT_LINE 프로시저 이용해서 결과를 출력
DBMS_OUTPUT.PUT_LINE('출력하고 싶은 내용');
DBMS_OUTPUT.PUT_LINE(c_message);
DBMS_OUTPUT.PUT_LINE(v_hireDate);

END;

DECLARE
  v_name VARCHAR2(20);
  v_salary NUMBER;
  v_hiredate VARCHAR2(30);

BEGIN
  SELECT first_name, salary, TO_CHAR(hire_date, 'yyyy-MM-dd')
    INTO v_name, v_salary, v_hiredate
    FROM employees
    WHERE first_name = 'Ellen';

  DBMS_OUTPUT.PUT_LINE('검색된 사원 정보');
  DBMS_OUTPUT.PUT_LINE(v_name || ' ' || v_salary || ' ' || v_hiredate);

END;

---

데이터 유형

기본형 데이터형

v_search VARCHAR2(30) := 'Lisa';

참조형 데이터형

기본형 데이터형의 경우, 테이블에서 데이터를 가져오기 전에, 해당 컬럼의 데이터형을 미리 알고 선언해주어야한다. 그러나 레퍼런스형의 경우, 해당 컬럼과 같은 데이터 타입을 갖겠다는 것만 명시해준다.

v_name employees.last_name%TYPE;
v_salary employees.salary%TYPE;

ROWTYPE

1개 로우의 모든 데이터들의 타입을 의미한다.

DECLARE
	employee_record employees%ROWTYPE;

BEGIN
	-- *로 하지 않고 일부 열만 가져오는 것도 가능
	SELECT * INTO employee_record
		FROM employees
        WHERE first_name = 'Lisa';
	
    dbms_output.put_line(employee_record.first_name || ' ' || employee_record.employee_id);

---

제어문(조건문)

DECLARE
	v_no NUMBER := 7;
    v_score NUMBER := 80;

BEGIN
	--if ~ end if
    IF v_no = 7 THEN
   	 dbms_output.put_line('7이다.');
    END IF;
    
    --if ~ else ~ end if
    IF v_no = 5 THEN
    	dbms_output.put_line('5이다.');
    ELSE
    	dbms_output.put_line('5가 아니다.');
    END IF;
    
   --if ~ elsif ~ end if
   IF v_score >= 90 THEN
   	dbms_output.put_line('A');
   ELSIF v_score >= 80 THEN
   	dbms_output.put_line('B');
   ELSE
   	dbms_output.put_line('C');
   END IF;
   
END;

• 예시

// 랜덤값으로 얻은 부서 번호로 해당 부서의 평균 임금 구하기
DECLARE
  v_rand NUMBER := ROUND(DBMS_RANDOM.VALUE(10, 30), -1);
  v_avgSal NUMBER := 0;

BEGIN
  select round(avg(sal), 0)
    into v_avgSal
    from emp
    group by deptno
    having deptno = v_rand;

  -- 방법1
  if v_avgSal between 0 and 2000 then
    dbms_output.put_line('낮음');
  elsif v_avgSal between 2000 and 2500 then
    dbms_output.put_line('보통');
  else
    dbms_output.put_line('높음');
  end if;

  -- 방법2
  case when v_avgSal BETWEEN 0 and 2000 then
     dbms_output.put_line('낮음');
  when v_avgSal between 2000 and 2500 then
    dbms_output.put_line('보통');
  else
    dbms_output.put_line('높음');
  end case;

END;

참고
group by로 그룹을 나눈 후 having을 통해 한 그룹을 집어서 해당 그룹에 대해 집계함수를 쓰는 것과
애초부터 where 절을 통해 한 그룹을 집어서 집계함수를 쓰는 것의 결과는 동일하다.

---

반복문

LOOP 문

DECLARE
  i number := 0;

BEGIN
  loop
    i := i+2;
    exit when i > 10;
    dbms_output.put_line(i);
  end loop;
  
END;

WHILE 문

DECLARE
  i number := 0;

BEGIN 
  while i < 10 loop
    i := i+1;
    dbms_output.put_line(i);
  end loop;

END;

FOR 문

DECLARE
  i number := 0;
  
BEGIN
  for i in 1..10 loop
    dbms_output.put_line(i);
  end loop;

END;