[64일차] PL/SQL(익명블록, 변수, 상수, DML, 제어문, 함수) / 프로시저

20221025(화)

• 목차 
  - PL/SQL 기본 구조 (익명블록)
  - PL/SQL 구성요소 (변수, 상수, DML)
  - PL/SQL 제어문 (IF문, CASE문)
  - PL/SQL의 사용자 정의 함수
  - 프로시저

 

---

< PL/SQL 기본 구조 >

• 실제 복잡한 비즈니스 로직을 만들때 사용
• 일반 프로그래밍 언어에서 제공하는 많은 기능이 탑재
• DB에 직접 탑재, 컴파일되고 실행 ➡ 성능 면에서 우수
  
  

---

• 익명블록

SET SERVEROUTPUT ON;
SET SERVEROUTPUT OFF;

DECLARE   // 변수, 상수선언
	vi_num NUMBER;
BEGIN    // 실행문장
	vi_num := 100;   // 변수에 값 할당(대입)
	DBMS_OUTPUT.PUT_LINE(vi_num); // JAVA. System.out.println() 과 유사
END;

 

• 앞에 SET SERVEROUTPUT ON; 실행해줘야 DBMS 구문 돌아감.
• DBMS_OUTPUT.PUT_LINE(vi_num)명령어에 의해 100이라는 값이 출력된다.
• SET SERVEROUTPUT OFF; 일 경우 100이라는 값은 나오지 않는다. 

 

 

---

< PL/SQL 구성요소 >

(1) 변수

• 선언부에서 변수 선언을 하고 실행부에서 사용

    변수명 데이터타입 := 초깃값;

 

(2) 상수

• 한 번 값을 할당하면 변하지 X

상수명 CONSTANT 데이터타입 := 상수값;

 

(3) DML문

• PL/SQL에서 DDL문은 직접 사용할수가 없다.
  (DDL?  CREATE, ALTER, DROP 명령어)
  
  
• DML문 사용가능.
  (DML? SELECT, INSERT, DELETE, UPDATE, MERGE 명령어)

    DECLARE // 변수, 상수 선언이 필요하지 않아도, DECLARE 구조는 사용해야 한다.
      vs_emp_name    VARCHAR2(80); -- 사원명 변수
      vs_dep_name    VARCHAR2(80); -- 부서명 변수
    BEGIN  // DDL을 직접 코딩해서 사용 불가능.
      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;

 

 

---

< PL/SQL 제어문 >

 

(1) IF문

<조건이 1개일 경우>
    IF 조건 THEN
       조건 처리;
    END IF;
     
     
     
<조건이 2개일 경우>
    IF 조건 THEN
       조건 처리 1;
    ELSE
      조건 처리2;
    END IF;
     
     
     
<조건이 n개일 경우>
    IF 조건1 THEN
       조건 처리1;
    ELSIF 조건2 THEN
      조건 처리2;
      ...
    ELSE
       조건 처리n;
    END IF;

 

 

(2) CASE문 (2가지 종류)

<유형 1>
    CASE 표현식
        WHEN 결과1 THEN
             처리문1;
        WHEN 결과2 THEN
             처리문2;
        ...
        ELSE
             기타 처리문;
    END CASE;
     
     
     
<유형 2>
    CASE WHEN 표현식1 THEN
             처리문1;
        WHEN 표현식2 THEN
             처리문2;
        ...
        ELSE
             기타 처리문;
    END CASE;

 

---

< PL/SQL의 사용자 정의 함수 >

• 함수 생성
  - SQL 함수 : 오라클에서 제공하는 내장 함수(빌트인 함수) 
  - PL/SQL 함수 : 사용자가 직접 로직을 구현하는 사용자 정의 함수를 말한다.

    CREATE OR REPLACE FUNCTION 함수 이름 (매개변수1, 매개변수2, ...)
    RETURN 데이터타입;
    IS[AS]
      변수, 상수 등 선언
    BEGIN
      실행부
    　
      RETURN 반환값;
    [EXCEPTION
      예외 처리부]
    END [함수 이름];

 

(1) CREATE OR REPLACE FUNCTION: CREATE OR REPLACE 함수 생성

     : 이 구문을 사용해 계속 컴파일할 수 있고 마지막으로 수정된 최종본이 반영됨

(2) 매개변수

     : 함수로 전달되는 매개변수로, “매개변수명 데이터 타입” 형태로 명시한다. 매개변수는 생략할 수 있다.

(3) RETURN 데이터 타입

     : 함수가 반환할 데이터 타입을 지정한다.

(4) RETURN 반환값

     : 매개변수를 받아 특정 연산을 수행한 후 반환할 값을 명시한다.

---

< 프로시저 >

• 함수는 특정 연산을 수행한 뒤 결과 값을 반환하지만
• 프로시저는 특정한 로직을 처리하기만 하고 결과 값을 반환하지는 않는 서브 프로그램이다.
   
• 프로시저를 생성
  : CREATE OR REPLACE PROCEDURE
  
  
• 매개변수
  : IN은 입력 & 디폴트 / OUT은 출력 / IN OUT은 입ㆍ출력.

    CREATE OR REPLACE PROCEDURE 프로시저 이름
        (매개변수명1[IN |OUT | IN OUT] 데이터타입[:= 디폴트 값],
         매개변수명2[IN |OUT | IN OUT] 데이터타입[:= 디폴트 값],
         ...
        )
    IS[AS]
      변수, 상수 등 선언
    BEGIN
      실행부
    　
    [EXCEPTION
      예외 처리부]
    END [프로시저 이름];