20_PL/SQL 기초

[ PL/SQL 기초 목차 ]

1. PL/SQL의 구조

2. 변수 선언과 대입문

   1) 대입문으로 변수에 값 지정하기

   2) 스칼라 변수/레퍼런스 변수

   3) PL/SQL에서 SELECT INTO 문

   4) PL/SQL 테이블 TYPE

   5) PL/SQL RECORD TYPE

3. PL/SQL의 제어문

   1) IF ~ THEN ~ END IF

   2) IF ~ THEN ~ ELDE ~ END IF

   3) IF ~ THEN ~ ELSEIF ~ ELSE ~ END IF

   4) BASIC LOOP문

   5) FOR LOOP문

   6) WHILE LOOP문

 

 

 

 

[ 1 ] PL/SQL의 구조

* 절차적 언어 : C, CORBOL, FORTRAN,...

   함수적 언어 : LIST, PYTHON

   객체지향 언어 : JAVA, C++, PYTHON

 

- PL/SQL (Oracle's Procedural Language extension to SQL)

  SQL 문장에서 변수정의, 조건처리(IF), 반복처리(LOOP, WHILE, FOR)등을

  지원하며, 오라클 자체에 내장되어 있는 절차적 언어 (Procedure Language)로서

  SQL의 단점을 보완해 준다.

 

 

- PL/SQL 은 다음과 같은 SQL에 없는 기능들이 제공된다.

   - 변수 선언을 할 수 있다.

   - 비교 처리를 할 수 있다.

   - 반복 처리를 할 수 있다.

 

- PL/SQL은 블록(BLOCK) 구조의 언어로서 크게 다음과 같이 세 부분으로 나눌 수 있다.

   - 선언부 (DECLARE SECTION)

     : PL/SQL에서 사용하는 모든 변수나 상수를 선언하는 부분으로서

       DECLARE로 시작한다.

   - 실행부 (EXECUTABLE SECTION)

     : 절차적 형식으로 SQL문을 실행할 수 있도록 절차적 언어의 요소인

       제어문, 반복문, 함수 정의 등 로직을 기술할 수 있는 부분으로

       BEGIN으로 시작한다.

   - 예외처리 (EXCEPTION SECTION)

     : PL/SQL문이 실행되는 중에 에러가 발생할 수 있는데 이를 예외 사항이라고 한다.

       이러한 예외 사항이 발생했을 때 이를 해결하기 위한 문장을 기술할 수 있는 부분으로

       EXCEPTION으로 시작한다.

 

- PL/SQL 프로그램의 작성 요령은 다음과 같다.

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

   - END 뒤에 ; 을 사용하여 하나의 블록이 끝났다는 것을 명시한다.

   - PL/SQL 블록의 작성은 편집기를 통해 파일로 작성할 수도 있고,

     프롬프트에서 바로 작성할 수도 있다.

   - SQL*PLUS환경에서는 DECLARE나 BEGIN이라는 키워드로

      PL/SQL블록이 시작하는 것을 알 수 있다.

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

   - 쿼리문을 수행하기 위해서 / 가 반드시 입력되어야 하며,

      PL/SQL블록은 행에 / 가 있으면 종결된 것으로 간주한다.

 

* PL/SQL 코드 끝에 / 이 있으면 종결했다는 것을 의미한다.

-- 형식
DECLARE
  변수 선언;
BEGIN
  실행문;
END;
/

-- 예시
SET SERVEROUTPUT ON  -- 출력해주는 내용을 화면에 보여주도록 설정하는 것
BEGIN
    DBMS_OUTPUT.PUT_LINE('HELLO WORLD'); -- OUTPUT 얘는 패키지로 보면되고 PUT_LINE 얘는 PRINT 함수 같은걸(출력해주는 함수)로 생각하면 조금 이해하기 쉽다,,,
END;
/

 

 

 

 

---

 

[ 2 ] 변수 선언과 대입문

- PL/SQL의 선언부에서는 실행부에서 사용할 변수를 선언한다.

  변수를 선언할 때 변수명 다음에 자료형을 기술해야 한다.

- PL/SQL에서 변수 선언할 때 사용되는 자료형은

  SQL에서 사용하던 자료형과 거의 유사하다.

 

-- 형식
identifier [CONSTANT] datatype [NOT NULL]
[:=| DEFAULT expression];

-- identifier : 변수의 이름
-- CONSTANT : 변수의 값을 변경할 수 없도록 제약한다.
-- datatype : 자료형을 기술한다.
-- NOT NULL : 값을 반드시 포함하도록 하기 위해 변수를 제약한다.
-- Expression : Literal, 다른 변수, 연산자나 함수를 포함하는 표현식

-- 예시
VEMPNO NUMBER(4);
VENAME VARCHAR2(10);

 

 

 

 

1. 대입문으로 변수에 값 지정하기

- PL/SQL에서는 변수의 값을 지정하거나 재지정하기 위해서

  :=를 사용한다.

  :=의 좌측

   --> 새 값을 받기 위한 변수를 기술한다.

  :=의 우측

   --> 저장할 값을 기술한다.

 

 

[ 실습 : 변수 사용하기 ]

- 변수의 선언 및 할당을 하고 그 변수 값을 출력해 보자.

 

Q1. ED EXAM01을 입력하여 새로 생긴 EXAM01.sql 파일에 다음과 같이 입력하라.

A1.

SET SERVEROUTPUT ON
DECLARE
  VEMPNO NUMBER(4);
  VENAME VARCHAR2(10);
BEGIN
  VEMPNO := 7788;
  VENAME := 'SCOTT';
  DBMS_OUTPUT.PUT_LINE('사번 / 이름');
  DBMS_OUTPUT.PUT_LINE('-------------');
  DBMS_OUTPUT.PUT_LINE(VEMPNO || ' / ' || VENAME);
END;
/

-- 아래 쉅 내용 코드 --
-- 변수 사용하기
SET SERVEROUTPUT ON
DECLARE -- 변수 선언
    -- 선언부에는 변수를 선언한다.
    VEMPNO NUMBER(4);  -- , 아니고 ; 사용해야한다.
    VENAME VARCHAR2(10);
BEGIN -- 실행문 작성
    -- 실행부에서는 실행문을 작성한다.
    VEMPNO := 7788;
    VENAME := 'SCOTT';
    DBMS_OUTPUT.PUT_LINE('사번 / 이름');
    DBMS_OUTPUT.PUT_LINE('-------------');
    DBMS_OUTPUT.PUT_LINE(VEMPNO || ' / ' || VENAME);
END;
/

 

[ 실습 코드 실행한 결과 출력 ]

 

 

 

 

 

 

Q2. 작성이 완료한 후에 파일을 저장한다.

        SQL> 프롬프트에 @EXAM01을 입력하면 EXAM01.sql 파일 내부에 기술한

        PL/SQL 이 실행된 후 결과가 출력된다.

 

A2.

-- cmd 창에서의 코드 --

SQL> ED EXAM01

SQL> @EXAM01
사번 / 이름
-------------
7788 / SCOTT

PL/SQL 처리가 정상적으로 완료되었습니다.

SQL>

 

 

 

 

2. 스칼라 변수/레퍼런스 변수

- PL/SQL에서 변수를 선언하기 위해 사용할 수 있는 데이터형은

  크게 스칼라 (Scalar)와 레퍼런스 (Reference)로 나눌 수 있다.

* 스칼라 변수 / 레퍼런스 변수는

  자바에서 기본형 / 참조형과 같은 애들이다.

 

- 스칼라

  : PL/SQL에서 변수를 선언할 때 사용되는 자료형은 SQL에서

    사용하던 자료형과 거의 유사하다.

    숫자를 저장하려면 NUMBER를 사용하고

    문자를 저장하려면 VARCHAR2를 사용해서 선언한다.

VEMPNO NUMBER(4);
VENAME VARCHAR2(10);

- 레퍼런스

  : 이전에 선언된 다른 변수 또는 데이터베이스 칼럼에 맞추어

    변수를 선언하기 위해 %TYPE 속성을 사용할 수 있다.

VEMPNO EMP.EMPNO%TYPE;
-- EMP 테이블에 있는 EMPNO라는 칼럼의 데이터타입을 가져와서 VEMPNO의 데이터타입으로 사용하겠다.
VENAME EMP.ENAME%TYPE;

 

 

 

 

3. PL/SQL에서 SELECT INTO문

- 데이터베이스에서 정보를 추출할 필요가 있을 때

  또는 데이터베이스로 변경된 내용을 적용할 필요가 있을 때

  SQL을 사용한다.

- PL/SQL은 SQL에 있는 DML 명령을 지원한다.

  테이블의 행에서 질의된 값을 변수에 할당시키기 위해

  SELECT문을 사용한다.

- PL/SQL의 SELECT문은 INTO절이 필요한데,

  INTO절에는 데이터를 저장할 변수를 기술한다.

- SELECT절에 있는 칼럼은 INTO절에 있는 변수와

  1대 1 대응을 하기에 개수와 데이터의 형, 길이가

  일치해야 한다.

- SELECT문은 INTO절에 의해 하나의 행만 저장할 수 있다.

 

* SQL에서의 SELECT문과 비슷함.

  IN SQL,

  SELECT 칼럼명 FROM TABLE_NAME;

 

  IN PL/SQL,

  SELECT 칼럼명 INTO 칼럼명 대체할 변수이름들

  FROM TABLE_NAME

  

 

 

-- 형식
SELECT select_list
INTO {variable_name1[, variable_name2, ...] | record_name}
FROM table_name
WHERE condition;

-- 예시
SELECT EMPNO, ENAME INTO VEMPNO, VENAME
FROM EMP
WHERE ENAME='SCOTT';
-- EMP 테이블에 있는 ENAME 칼럼이 SCOTT인 EMPNO와 ENAME을 가져와서 걔네들을 각각 VEMPNO, VENAME으로 칼럼명을 바꿔서 출력해라.

 

 

 

[ 실습 : 사번과 이름 검색하기 ]

- PL/SQL의 SELECT문으로 EMP 테이블에서 사원번호와 이름을 조회해 보자.

 

 

Q1. ED 다음에 파일이름을 입력하여 새로 생긴 SQL 파일에 다음과 같이 입력하라.

A1.

SET SERVEROUTPUT ON
DECLARE
-- %TYPE 속성으로 칼럼 단위 레퍼런스 변수 선언
  VEMPNO EMP.EMPNO%TYPE;
  VENAME EMP.ENAME%TYPE;
BEGIN
  DBMS_OUTPUT.PUT_LINE('사번 / 이름');
  DBMS_OUTPUT.PUT_LINE('------------');
  
  SELECT EMPNO, ENAME INTO VEMPNO, VENAME
  FROM EMP
  WHERE ENAME='SCOTT';
  
-- 레퍼런스 변수에 저장된 값을 출력한다.
  DBMS_OUTPUT.PUT_LINE(VEMPNO || ' / ' || VENAME);
END;
/

-- 아래 쉅 내용 코드 --
-- 사번과 이름 검색하기
DECLARE
    VEMPNO EMP.EMPNO%TYPE;
    VENAME EMP.ENAME%TYPE;
BEGIN
    DBMS_OUTPUT.PUT_LINE('사번 / 이름');
    DBMS_OUTPUT.PUT_LINE('--------------');
    
    SELECT EMPNO, ENAME INTO VEMPNO, VENAME  -- INTO 안적어주면 오류발생함
    FROM EMP
    WHERE ENAME = 'SCOTT';
    
    DBMS_OUTPUT.PUT_LINE(VEMPNO || ' / ' || VENAME);
END;
/

 

 

 

 

 

Q2. 작성이 완료한 후에 파일을 저장한다.

        SQL> 프롬프트에 @파일명을 입력하면 SQL 파일 내부에 기술한

        PL/SQL 이 실행된 후 결과가 출력된다.

 

A2.

C:\Temp>SQLPLUS SCOTT/TIGER

SQL> ed EXAM02.SQL

SQL> @EXAM02.SQL
사번 / 이름
------------
7788 / SCOTT

PL/SQL 처리가 정상적으로 완료되었습니다.

SQL> quit

 

 

 

 

4. PL/SQL 테이블 TYPE

- PL/SQL 테이블은 로우에 대해 배열처럼 액세스 하기 위해

  기본키를 사용한다.

- 배열과 유사하고 PL/SQL 테이블을 액세스 하기 위해

  BINARY_INTEGER 데이터형의 기본키와

  PL/SQL 테이블 요소를 저장하는 스칼라

  또는 레코드 데이터형의 칼럼을 포함해야 한다.

 

* 테이블 TYPE이란,

   배열처럼 사용하고자 할 때 사용하는 아이..

   배열 타입이라고 생각하면 된다

TYPE table_type_name IS TABLE OF
{column_type | variable%TYPE | table.column%TYPE} [NOT NULL]
[INDEX BY BINARY_INTEGER];
identifier table_type_name;

 

 

 

 

[ 실습 : TABLE 변수 사용하기 ]

- TABLE 변수를 사용하여 EMP 테이블에서 이름과 업무를 출력해 보자.

 

 

Q1. ED 다음에 파일이름을 입력하여 새로 생긴 SQL 파일에 다음과 같이 입력하라.

A1.

SET SERVEROUTPUT ON
DECLARE
  -- 테이블 타입을 정의
  TYPE ENAME_TABLE_TYPE IS TABLE OF EMP.ENAME%TYPE
    INDEX BY BINARY_INTEGER;
  TYPE JOB_TABLE_TYPE IS TABLE OF EMP.JOB%TYPE
    INDEX BY BINARY_INTEGER;
    
  -- 테이블 타입으로 변수 선언
  ENAME_TABLE ENAME_TABLE_TYPE;
  JOB_TABLE JOB_TABLE_TYPE;
  
  I BINARY_INTEGER := 0;
  
 BEGIN
   -- EMP 테이블에서 사원이름과 직급을 얻어옴
   FOR K IN (SELECT ENAME, JOB FROM EMP) LOOP
     I := I + 1;
     ENAME_TABLE(I) := K.ENAME;
     JOB_TABLE(I) := K.JOB;
   END LOOP;
   
   -- 테이블에 저장된 내용을 출력
   FOR J IN 1.... I LOOP
     DBMS_OUTPUT.PUT_LINE(RPAD(ENAME_TABLE(J),12)
       || ' / ' || RPAD(JOB_TABLE(J),9));
   END LOOP;
 END;
  /
  
  -- 아래 쉅 내용 코드 --
  -- 테이블 TYPE 변수 사용하기
SET SERVEROUTPUT ON
DECLARE
    -- 테이블 타입을 정의
    TYPE ENAME_TABLE_TYPE IS TABLE OF EMP.ENAME%TYPE  -- 테이블 타입 이름은 ENAME_TABLE_TYPE이고 , EMP.ENAME의 데이터타입으로 테이블 타입을 결정한다.
        INDEX BY BINARY_INTEGER; -- 옵션
    TYPE JOB_TABLE_TYPE IS TABLE OF EMP.JOB%TYPE
        INDEX BY BINARY_INTEGER;
        
    -- 테이블 타입으로 변수 선언하기
    ENAME_TABLE ENAME_TABLE_TYPE;  -- ENAME_TABLE은 ENAME_TABLE의 타입으로 데이터타입을 한다.
    JOB_TABLE JOB_TABLE_TYPE;  -- JOB_TABLE은 JOB_TABLE의 타입으로 데이터타입을 가진다.
    
    I BINARY_INTEGER := 0;  -- I 변수는 BINARY_INTEGER(NUMBER타입이랑 다름)타입이고 0을 대입한다(0으로 초기화).
BEGIN
    FOR K IN (SELECT ENAME, JOB FROM EMP) LOOP -- SELECT ENAME, JOB FROM EMP의 결과를 K 변수에 담는 것이다,,
        I := I + 1; -- I는 1부터 시작한다
        ENAME_TABLE(I) := K.ENAME; -- ENAME_TABLE의 I 인덱스에 K의 ENAME 값을 넣어라.
        JOB_TABLE(I) := K.JOB;
    END LOOP;
        
         FOR J IN 1.. I LOOP -- ... 3개 적으면 출력 안된다! .. 2개만 적어줘야한다!
            DBMS_OUTPUT.PUT_LINE(RPAD(ENAME_TABLE(J), 12)  || ' / ' || RPAD(JOB_TABLE(J),9));
    END LOOP;
END;
/

 

 

 

 

 

 

Q2. 작성을 완료한 후에 파일을 저장한다.

       SQL> 프롬프트에 @파일명을 입력하면 SQL 파일 내부에

       기술한 PL/SQL이 실행된 후 결과가 출력된다.

A2.

 

C:\Temp>SQLPLUS SCOTT/TIGER

SQL> ed EXAM03.SQL

SQL> @EXAM03.SQL
SMITH        / CLERK
ALLEN        / SALESMAN
WARD         / SALESMAN
JONES        / MANAGER
MARTIN       / SALESMAN
BLAKE        / MANAGER
CLARK        / MANAGER
SCOTT        / ANALYST
KING         / PRESIDENT
TURNER       / SALESMAN
ADAMS        / CLERK
JAMES        / CLERK
FORD         / ANALYST
MILLER       / CLERK

PL/SQL 처리가 정상적으로 완료되었습니다.

SQL>

 

 

 

 

5. PL/SQL RECORD TYPE

- PL/SQL RECORD TYPE은 프로그램 언어의 구조체와 유사하다.

- PL/SQL RECORD는 FIELD(ITEM)들의 집합을 하나의 논리적 단위로

  처리할 수 있게 해 주므로 테이블의 ROW를 읽어올 때 편리하다.

 

* RECORD TYPE 은

  RECODE TYPE이 참조하고 있는 데이터타입들이

  여러 개다.

  자바의 클래스 타입(다른 타입의 필드를 정의)과 같은 개념..!

  앞선, 테이블 TYPE은

  모두 다 같은 데이터타입이 배열 안에 들어있는 것..!!

  

 

[ 실습 : RECORD TYPE 사용하기 ]

- EMP 테이블에서 SCOTT 사원의 정보를 출력해 보자.

 

Q1. ED 다음에 파일이름을 입력하면 새로 생긴 SQL 파일에 다음과 같이 입력하라.

A1.

-- 레코드 타입 사용하기
DECLARE
    -- 레코드 타입을 정의하기 : 자바의 클래스 타입(다른 타입의 필드를 정의)과 같은 개념
    TYPE EMP_RECORD_TYPE IS RECORD(
        V_EMPNO EMP.EMPNO%TYPE,
        V_ENAME  EMP.ENAME%TYPE,
        V_JOB      EMP.JOB%TYPE,
        V_DEPTNO EMP.DEPTNO%TYPE);
        
    -- 레코드 타입으로 변수 선언하기
    EMP_RECORD EMP_RECORD_TYPE;  -- EMP_RECORD 변수는 EMP_RECORD 타입이다.
BEGIN
    -- SCOTT 사원의 정보를 레코드 변수에 저장
    SELECT EMPNO, ENAME, JOB, DEPTNO INTO EMP_RECORD -- INTO EMP_RECORD 코드에 안적어도 출력이 됨...
    FROM EMP
    WHERE ENAME = UPPER('SCOTT');
    
    -- 레코드 변수에 저장된 사원 정보를 출력
    DBMS_OUTPUT.PUT_LINE('사원번호 : ' || TO_CHAR(EMP_RECORD.V_EMPNO) ); -- V.EMPNO는 EMP.EMPNO의 타입이다. 즉, NUMBER 타입이라서 그걸 TO_CHAR 타입으로 변환시켜준 것
    DBMS_OUTPUT.PUT_LINE('이     름 : ' || EMP_RECORD.V_ENAME);
    DBMS_OUTPUT.PUT_LINE('담당업무 : ' || EMP_RECORD.V_JOB);
    DBMS_OUTPUT.PUT_LINE('부서번호 : ' || EMP_RECORD.V_DEPTNO);
END;
/

 

 

 

[ BEGIN 안에 있는 실행문만 코드 실행했을 때 결과물 ]

 

 

 

 

 

 

 

Q2. 작성을 완료한 후에 파일을 저장한다.

       SQL> 프롬프트에 @파일명을 입력하면 SQL 파일 내부에

       기술한 PL/SQL이 실행된 후 결과가 출력된다.

A2.

C:\Temp>SQLPLUS SCOTT/TIGER

SQL> ed EXAM04.SQL

SQL> @EXAM04.SQL
사원번호 : 7788
이    름 : SCOTT
담당업무 : ANALYST
부서번호 : 20

PL/SQL 처리가 정상적으로 완료되었습니다.

SQL>

 

 

 

 

 

---

 

[ 3 ] PL/SQL의 제어문

- PL/SQL의 제어문

  : 어떤 조건에서 어떤 코드가 실행되어야 하는지를

    제어하기 위한 문법,

    절차적 언어의 구성요소를 포함한다.

 

구문	의미	문법
BEGIN=END	PL/SQL문을 블럭화 시킨다. 중첩 가능하다.	BEGIN {SQL문} END
IF-ELSE	조건의 검사 결과에 따라 문장을 선택적으로 수행한다.	IF <조건> SQL문 [ ELSE SQL문 ]   END IF;
FOR	counter 값이 범위 내에 있을 경우 FOR문의 블럭을 실행한다.	FOR counter IN <범위> {SQL문} END LOOP
WHILE	조건이 참일 경우 WHILE문의 블럭을 실행한다.	WHILE <조건> {SQL문 | BREAK | CONTINUE} END LOOP
RETURN	프로시저를 종료, 상태값을 정수로 반환 가능하다.	RETURN [ <정수> ]

 

 

 

 

1. IF~THEN~END IF

IF condition THEN ..... 조건문
statements; ........... 조건에 만족할 경우 실행되는 문자
END IF

 

 

 

[ 실습 : 부서 번호로 부서명 알아내기 ]

- 다음은 사원 번호가 7788인 사원의 부서 번호를 얻어 와서 부서 번호에 따른 부서명을 구하는 예제이다.

IF문이 끝났을 때에는 반드시 END IF를 기술해야 한다는 점을 주의해야 한다.

 

Q1. ED 다음에 파일이름을 입력하여 새로 생긴 SQL 파일에 다음과 같이 입력하라.

A1.

SET SERVEROUTPUT ON
DECLARE
  VEMPNO        NUMBER(4);
  VENAME        VARCHAR2(20);
  VDEPTNO    EMP.DEPTNO%TYPE;
  VDNAME        VARCHAR2(20) := NULL;
BEGIN
  SELECT EMPNO, ENAME, DEPTNO INTO VEMPNO, VENAME, VDEPTNO
  FROM EMP
  WHERE EMPNO=7788;
  
  IF (VDEPTNO = 10) THEN
    VDNAME := 'ACCOUNTING';
  END IF;
  IF (VDEPTNO = 20) THEN
    VDNAME := 'RESEARCH';
  END IF;
  IF (VDEPTNO = 30) THEN
    VDNAME := ' SALES';
  END IF;
  IF (VDEPTNO = 40) THEN
    VDNAME := 'OPERATIONS';
  END IF;
  
  DBMS_OUTPUT.PUT_LINE('사번   이름   부서명');
  DBMS_OUTPUT.PUT_LINE(VEMPNO||'   '||VENAME||'   'VDNAME);
END;
/

-- 아래 쉅 내용 코드 --
-- IF ~ THEN ~ END IF
DECLARE
       VEMPNO NUMBER(4);
       VENAME VARCHAR2(20);
       VDEPTNO EMP.DEPTNO%TYPE;
       VDNAME VARCHAR2(20) := NULL; -- 변수 선언과 동시에 NULL 값 넣어주기
BEGIN
       SELECT EMPNO, ENAME, DEPTNO INTO VEMPNO, VENAME, VDEPTNO
       FROM EMP
       WHERE EMPNO=7788;
       
       IF (VDEPTNO = 10) THEN
           VDNAME := 'ACCOUNTING';
       END IF;
       IF (VDEPTNO = 20) THEN
           VDNAME := 'RESEARCH';
       END IF;
       IF (VDEPTNO = 30) THEN
           VDNAME := 'SALES';
       END IF;
       IF (VDEPTNO = 40) THEN
           VDNAME := 'OPERATIONS';
       END IF;
       
       DBMS_OUTPUT.PUT_LINE('사번      이름       부서명');
       DBMS_OUTPUT.PUT_LINE(VEMPNO || '     ' || VENAME || '    ' || VDNAME);
       
END;
/

 

[ BEGIN 안에 있는 실행문만 돌려본 결과 ]

 

 

 

 

 

Q2. 작성을 완료한 후에 파일을 저장한다.

       SQL> 프롬트트에 @파일명을 입력하면 SQL 파일 내부에 기술한

       PL/SQL이 실행된 후 결과가 출력된다.

A2.

C:\Temp>SQLPLUS SCOTT/TIGER

SQL> ed EXAM05.SQL

SQL> @EXAM05.SQL
사번 이름 부서명
7788 SCOTT RESEARCH

PL/SQL 처리가 정상적으로 완료되었습니다.

SQL>

 

 

 

 

2. IF ~ THEN ~ ELSE ~ END IF ( 넘어가심 )

[문장1]
IF condition THEN ..... 조건문
statements;   ......... 조건에 만족할 경우 실행되는 문장 [문장2]
ELSE
statements;   ......... 조건에 만족하지 않을 경우 실행되는 문장 [문장3]
END IF
[문장4]

 

 

 

[ 실습 : 직원의 연봉 구하기 ]

- 다음은 연봉을 구하는 예제이다. 커미션을 받는 직원은 급여에 12를 곱한 후 커미션과 합산하여 연봉을 구하고

커미션을 받지 않는 직원은 급여에 12를 곱한 것으로만 연봉을 구한다.

 

Q1. ED 다음에 파일이름을 입력하여 새로 생긴 SQL 파일에 다음과 같이 입력하라.

A1.

SET SERVEROUTPUT ON
DECLARE
  VEMP EMP%ROWTYPE;
  ANNSAL NUMBER(7,2);
BEGIN
 -- SCOTT 사원의 전체 정보를 로우 단위로 얻어와 VEMP에 저장한다.
  SELECT * INTO VEMP
  FROM EMP
  WHERE ENAME = 'SCOTT';
  
  IF (VEMP.COMM IS NULL) THEN        -- 커미션이 NULL이면
    ANNSAL := VEMP.SAL*12;           -- 급여에 12를 곱한다.
  ELSE                               -- 커미션이 NULL이 아니면
    ANNSAL := VEMP.SAL*12+VEMP.COMM; -- 급여에 12를 곱한 후 커미션과 합산
  END IF;
  
  DBMS_OUTPUT.PUT_LINE('사번 / 이름 / 연봉');
  DBMS_OUTPUT.PUT_LINE('-------------------------------------');
  DBMS_OUTPUT.PUT_LINE(VEMP.EMPNO||'/'||VEMP.ENAME||'/'||ANNSAL);
END;
/

 

 

 

 

 

 

Q2. 작성을 완료한 후에 파일을 저장한다.

       SQL> 프롬트트에 @파일명을 입력하면 SQL 파일 내부에 기술한

       PL/SQL이 실행된 후 결과가 출력된다.

A2.

C:\Temp>SQLPLUS SCOTT/TIGER

SQL> ed EXAM06.SQL

SQL> @EXAM06.SQL
사번 / 이름 / 연봉
-------------------------------------
7788/SCOTT/36000

PL/SQL 처리가 정상적으로 완료되었습니다.

SQL>

 

 

 

 

3. IF ~ THEN ~ ELSIF ~ ELSE ~ END IF

IF condition THEN
statements;
ELSIF condition THEN
statements;
ELSIF condition THEN
statements;
ELSE
statements;
END IF;

 

 

 

[ 실습 : 부서 번호로 부서명 알아내기 ]

 

- SQL 함수에서 선택을 위한 DECODE 함수를 학습하면서 부서번호에 대해서 부서명을 지정해 보았다.

 

- 이곳 PL/SQL에서는 DECODE 함수 대신 IF ~ THEN ~ ELSIF ~ ELSE ~ END IF 구문으로

  부서번호에 대한 부서명을 구해보자.

 

 

Q1. ED 다음에 파일이름을 입력하여 새로 생긴 SQL 파일에 다음과 같이 입력하라.

A1.

-- IF ~ THEN ~ ELSIF~ ELSE ~ END IF
SET SERVEROUTPUT ON
DECLARE
    VEMP EMP%ROWTYPE;  -- 레코드 타입 (하나의 ROW를 가져와서 그것들의 각각의 칼럼들의 데이터타입들 VEMP의 데이터타입으로 사용하겠다.
    VDNAME VARCHAR2(14);
BEGIN
    DBMS_OUTPUT.PUT_LINE('사번 / 이름 / 부서명');
    DBMS_OUTPUT.PUT_LINE('------------------------------');
    
    SELECT * INTO VEMP
    FROM EMP
    WHERE ENAME='SCOTT';
    
    IF (VEMP.DEPTNO = 10) THEN
        VDNAME := 'ACCOUNTING';
    ELSIF (VEMP.DEPTNO = 20) THEN
        VDNAME := 'RESEARCH';
    ELSIF (VEMP.DEPTNO = 30) THEN
        VDNAME := 'SALES';
    ELSE
        VDNAME := 'OPERATIONS';
    END IF;
    
    DBMS_OUTPUT.PUT_LINE(VEMP.EMPNO || ' / ' || VEMP.ENAME || ' / ' || VDNAME);
    
END;
/

 

 

* EMP%ROWTYPE 이 무엇이냐

  EMP 테이블을 출력해보았을 때 여러 개의 ROW들이 있다.

  이때 하나의 ROW 전체를 가져와서

  그 각각의 칼럼들의 데이터타입들을 그대로 가져다가

  사용하겠다는 의미

 

 

* 즉, 아래의 사진과 같이 ROW 하나 전체를 가져와서

  EMPNO의 데이터타입 NUMBER, ENAME의 데이터타입 VARCHAR2,

  JOB의 데이터타입 VARCHAR2, MGR의 데이터타입 NUMBER,

  HIREDATE의 데이터타입 DATE, SAL의 데이터타입 NUMBER,

  COMM의 데이터타입 NUMBER, DEPTNO의 데이터타입 NUMBER

  이 모두의 데이터타입을 가져와서 사용하려고 하는 것이다. 

 

 

 

 

[ EMP 테이블에 있는 칼럼들의 데이터타입 출력한 결과 ]

 

 

 

 

 

 

 

 

Q2. 작성을 완료한 후에 파일을 저장한다.

       SQL> 프롬트트에 @파일명을 입력하면 SQL 파일 내부에 기술한

       PL/SQL이 실행된 후 결과가 출력된다.

A2.

C:\Temp>SQLPLUS SCOTT/TIGER

SQL> ed EXAM07.SQL

SQL> @EXAM07.SQL
사번 / 이름 / 부서명
-------------------------------------
7788/SCOTT/RESEARCH

PL/SQL 처리가 정상적으로 완료되었습니다.

SQL>

 

 

 

 

4. BASIC LOOP문

- 지금 소개할 구문은 가장 간단한 루프로 구분 문자로

  LOOP와 END LOOP가 사용된다.

- 실행 상의 흐름이 END LOOP에 도달할 때마다

  그와 짝을 이루는 LOOP문으로 제어가 되돌아간다.

- 이러한 루프를 무한 루프라고 하며,

  여기서 빠져나가려면 EXIT문을 사용한다.

- 기본 LOOP는 LOOP에 들어갈 때

  조건이 이미 일치했다 할지라도 적어도 한 번은

  문장이 실행된다.

 

LOOP
statement1;
statement2;
...........
EXIT [WHERE condition];
END LOOP

 

 

 

[ 실습 : BASIC LOOP문으로 1부터 5까지 출력하기 ]

- 다음은 BASIC LOOP문으로 1부터 5까지 출력하는 예제이다.

 

Q1. ED 다음에 파일이름을 입력하여 새로 생긴 SQL 파일에 다음과 같이 입력한다.

A1.

-- BASIC LOOP문 : LOOP ~ END LOOP
SET SERVEROUTPUT ON
DECLARE
    N NUMBER := 1;
BEGIN
    LOOP
        DBMS_OUTPUT.PUT_LINE(N);
        N := N + 1;
        IF N > 5 THEN
            EXIT;
        END IF;
    END LOOP;
END;
/

 

 

 

 

Q2. 작성을 완료한 후에 파일을 저장한다.

        SQL> 프롬트트에 @파일명을 입력하면 SQL 파일 내부에

       기술한 PL/SQL이 실행된 후 결과가 출력된다.

A2.

C:\Temp>SQLPLUS SCOTT/TIGER

SQL> ed EXAM08.SQL

SQL> @EXAM08.SQL
1
2
3
4
5

PL/SQL 처리가 정상적으로 완료되었습니다.

SQL>

 

 

 

 

5. FOR LOOP문

-- 형식
FOR index_counter 
IN [REVERSE] lower_bound..upper_bound LOOP
statement1;
statement2;
. . . . . .
END LOOP

 

 

 

[ 실습 : FOR LOOP문으로 1부터 5까지 출력하기 ]

- 다음은 FOR LOOP문으로 1부터 5까지 출력하는 예제이다.

 

Q1. ED 다음에 파일이름을 입력하여 새로 생긴 SQL 파일에 다음과 같이 입력한다.

A1.

-- FOR LOOP문
SET SERVEROUTPUT ON
DECLARE
BEGIN
    FOR N IN 1..5 LOOP
        DBMS_OUTPUT.PUT_LINE(N);
    END LOOP;
END;
/

 

 

 

 

 

Q2. 작성을 완료한 후에 파일을 저장한다.

        SQL> 프롬트트에 @파일명을 입력하면 SQL 파일 내부에

       기술한 PL/SQL이 실행된 후 결과가 출력된다.

A2.

C:\Temp>SQLPLUS SCOTT/TIGER

SQL> ed EXAM09.SQL

SQL> @EXAM09.SQL
1
2
3
4
5

PL/SQL 처리가 정상적으로 완료되었습니다.

SQL>

 

 

 

 

6. WHILE LOOP문

WHILE condition LOOP
statement1;
statement2;
...........
END LOOP

 

 

 

[ 실습 : WHILE LOOP문으로 1부터 5까지 출력하기 ]

- 다음은 WHILE LOOP문으로 1부터 5까지 출력하는 예제이다.

 

Q1. ED 다음에 파일이름을 입력하여 새로 생긴 SQL 파일에 다음과 같이 입력한다.

A1.

-- WHILE LOOP문
SET SERVEROUTPUT ON
DECLARE
    N NUMBER := 1;
BEGIN
    WHILE N <= 5 LOOP
        DBMS_OUTPUT.PUT_LINE(N);
        N := N + 1;
    END LOOP;
END;
/

 

 

 

 

 

Q2. 작성을 완료한 후에 파일을 저장한다.

        SQL> 프롬트트에 @파일명을 입력하면 SQL 파일 내부에

       기술한 PL/SQL이 실행된 후 결과가 출력된다.

A2.

C:\Temp>SQLPLUS SCOTT/TIGER

SQL> ed EXAM010.SQL

SQL> @EXAM10.SQL
1
2
3
4
5

PL/SQL 처리가 정상적으로 완료되었습니다.

SQL>