(1)PL/SQL 기초

PL/SQL과 익명블록, 제어문

---

시작하기 전에

사용 교재 : 오라클SQL과 PL/SQL을 다루는 기술 (https://thebook.io/006696/)

예제 코드 : https://github.com/gilbutITbook/006696

SERVEROUTPUT 설정

PL/SQL 결과물을 보기 위해 아래의 실행문을 실행

SET SERVEROUTPUT ON;

---

PL/SQL이란

• Procedure Language / Structure Query Language 약자
• 오라클에서 SQL언어를 확장하기 위해 사용한 언어
• PL/SQL종류는 크게 익명블록과 저장블록으로 나뉨

• 익명블록 (anonymous PL/SQL Block)
  • 일회성으로 사용할 경우 사용되는 블록
• 저장블록(stored PL/SQL Block)
  • 서버에 저장해 놓고 주기적으로 반복해서 사용할 경우 사용하는 블록
  • 종류
    • 함수(Funtion)
    • 프로시저(Procedural)
    • 트리거(Trigger)

---

익명블록(anonymous PL/SQL Block)

사실 익명 블록은 다른 함수나 프로시저를 실행해 테스트할 때 사용하는 것이 대부분이다.

한 번 사용하고 나면 없어져 버리는 휘발성 블록이다.

익명블록의 기본 구조

-- 기본 구조
DECLARE
	-- 선언부
BEGIN
	-- 구현부
END;
/

익명블록 예제

• 예제

  • 사원 ID가 100인 사원의 이름명, 부서명을 조회

      DECLARE
          vs_emp_name VARCHAR2(80); -- 사원 변수
          vs_dep_name VARCHAR2(80); -- 부서명 변수
            
      BEGIN
            
          SELECT A.EMP_NAME, B.DEPARTMENT_NAME
          INTO vs_emp_name, vs_dep_name
          FROM EMPLOYEES A, DEPARTMENTS B
          WHERE A.DEPARTMENT_ID = B.DEPARTMENT_ID
              AND A.EMPLOYEE_ID = 100;
                    
          dbms_output.put_line(vs_emp_name || ' - ' || vs_dep_name);
                
      END;
      /
    

    

• 예제2
  • 임의의 사원번호를 입력하여 이름과 급여를 확인하는 예제

ACCEPT p_id prompt '사원 번호를 입력하시오'

DECLARE
    v_empsal number;
    v_name varchar2(30);
BEGIN
    SELECT salary , emp_name
    INTO v_empsal, v_name
    FROM employees 
    WHERE employee_id = &p_id;

    dbms_output.put_line('사원 이름 : ' || v_name || ' / 사원 급여 ' || to_char(v_empsal));
END;
/

• 예제3

  • 제일 큰 사원번호를 찾고, 그 번호에 1을 더해주어 신규 사원을 입력하는 예제

      DECLARE
          vn_max_empno employees.employee_id%TYPE;
            
      BEGIN
          SELECT max(employee_id) 
          INTO vn_max_empno
          FROM employees;
                
          INSERT INTO employees (employee_id,
                                      emp_name,
                                      email,
                                      hire_date,
                                      department_id)
          VALUES(vn_max_empno + 1, 'Harrison Ford', 'HARRIS', SYSDATE, 50);
                    
          COMMIT;
      END;
      /
    

    • vn_max_empno employees.employee_id%TYPE; 의미
      • employees테이블의 employees_id 칼럼의 자료형을 따라간다

---

PL/SQL 제어문

IF

• IF 기본구조

DECLARE
	/*......*/
BEGIN
	/*......*/
	
	IF /*... 조건식 ...*/ THEN
	    -- ... 표현식 ...
	ELSIF /*... 조건식2 ...*/ THEN
		-- ... 표현식2 ...
				.
				.
				.
	ELSE
		-- ... 표현식n ...
	END IF;
	
END;
/

• 예제

  • 사원번호를 입력받아, 고소득자, 중간 소득자, 저소득자를 구분

      accept p_num prompt '사원번호 입력'
            
      DECLARE
          v_empsal employees.salary%TYPE;
          v_name employees.emp_name%TYPE;
            
      BEGIN
          SELECT salary , emp_name
          INTO v_empsal, v_name
          FROM employees 
          WHERE employee_id = &p_num;
                
          IF v_empsal > 5000 then
              dbms_output.put_line
              ('사원 이름 : ' || v_name || ' / 사원 급여 : ' || to_char(v_empsal) || '/ 구분 : 고소득자');
          ELSIF v_empsal >= 3000 then
              dbms_output.put_line
              ('사원 이름 : ' || v_name || ' / 사원 급여 : ' || to_char(v_empsal) || '/ 구분 : 중간 소득자');
          ELSE 
              dbms_output.put_line
              ('사원 이름 : ' || v_name || ' / 사원 급여 : ' || to_char(v_empsal) || '/ 구분 : 저소득자');
          END IF;
            
      END;
      /
    

    

    

반복문 - LOOP

• LOOP - 구구단 3단

DECLARE
    vn_base_num number := 3;
    vn_cnt number := 1;
BEGIN
    LOOP
        dbms_output.put_line
        (vn_base_num || ' * ' || vn_cnt || ' = ' || vn_base_num * vn_cnt);
        vn_cnt := vn_cnt + 1;
        
        EXIT WHEN vn_cnt>9;
    END LOOP;
END;
/

반복문 - WHILE LOOP

• WHILE LOOP - 구구단 4단

DECLARE
    vn_base_num number := 4;
    vn_cnt number := 1;
BEGIN
    WHILE vn_cnt <= 9
    LOOP
        dbms_output.put_line
        (vn_base_num || ' * ' || vn_cnt || ' = ' || vn_base_num * vn_cnt);
        vn_cnt := vn_cnt + 1;

    END LOOP;
END;
/

반복문 - FOR LOOP

• FOR LOOP - 구구단 5단

DECLARE
    vn_base_num NUMBER := 5;
BEGIN
    FOR i IN 1..9
    LOOP
        dbms_output.put_line
        (vn_base_num || ' * ' || i || ' = ' || vn_base_num * i);
    END LOOP;
end;
/

• FOR LOOP - 구구단

BEGIN
    dbms_output.put_line('-------------');
    
    FOR i IN 2..9
        LOOP
        
        FOR j IN 1..9
            loop
            dbms_output.put_line(i || ' * ' || j || ' = ' || i * j);
            END LOOP;
            
        dbms_output.put_line('-------------');
    END LOOP;
    
END;
/