Software Architect/Oracle

PL/SQL은 무엇인가?

- PL/SQL(Procedural Language/SQL)의 약어
- 오라클 DB 환경에서 실행되는 절차적인 데이터베이스 프로그래밍 언어
- 표준 SQL과 3세대 언어의 강력한 일부 기능을 포함한 SQL의 확장 언어
- PL/SQL에서는 프로그램 단위를 블록(block)이라 부르며, 애플리케이션 로직들을 작성

그림 8.1 SQL문과 PL/SQL 블록의 실행 비교




■ 오라클 환경에서 PL/SQL을 학습하는 이유

▪ 오라클 개발 도구를 수행하는 모든 프로그래밍의 기초
▪ 클라이언트가 아닌 서버 상에서 프로세스를 수행하는데 PL/SQL을 사용함
▪ PL/SQL을 사용하면 업무 규칙이나 복잡한 로직을 캡슐화(Encapsulation)할 수 있어, 모듈화(Modularity)와 추상화(Abstraction)가 가능
▪ 데이터베이스 트리거를 통하여 데이터베이스 무결성을 제약하는 복잡한 규칙의 코딩과 변경 내역, 데이터를 복사
▪ PL/SQL은 독립적인 플랫폼 수준을 제공

■ PL/SQL에서 제공하는 명령문은
    ▪ 모든 SQL문
    ▪ 변수 및 상수 등의 선언문
    ▪ 대입문
    ▪ 조건 판단문
    ▪ 제어 흐름문
    ▪ 반복 처리문

■ PL/SQL로 작성할 수 있는 것은?

   ▪ SQL*Plus 스크립트(scripts)
   ▪ 프로시저(Procedure), 함수(Function) 서브프로그램
   ▪ 패키지(Package)
   ▪ 데이터베이스 트리거 (Database Trigger)
   ▪ 애플리케이션 로직 (Application Logic)

8.2 PL/SQL의 기본 블록 구조
    - PL/SQL의 프로그램 단위는 블록(Block)


    DECLARE    선택       변수와 상수, 프로시저와 함수 서브프로그램,
                                   커서 등을 선언
          선언문
           .....

     -----------------------------------------------
     BEGIN          필수     처리할 명령문들을 절차적으로 기술
                                    ▪ 모든 SQL문
          실행문                 ▪ 대입문, 반복 처리문, 조건 판단문, 제어 흐름문
          .....                     ▪ 커서 처리문

     -----------------------------------------------
     EXCEPTION   선택      오류 처리에 관한 예외처리 명령문을 기술
     
         예외처리문
         .....

    -----------------------------------------------
     END;              필수

/


그림 8.2  PL/SQL의 기본 블록 구조


8.3 PL/SQL의 블록 작성 및 실행

8.3.1 PL/SQL 블록의 작성

     ▪ ‘무엇을 어떻게 처리할 것인가?’를 분석
  ▪ DECLARE를 기술하고, 블록에서 필요한 변수나 상수, 서브 프로그램, 커서 등을 문법에 맞추어 한 줄에 하나씩 기술
  ▪ BEGIN을 기술하고, 절차적인 실행 순서에 의하여 명령문을 기술
  ▪ EXCEPTION을 기술하고, 실행절에 기술된 명령문이 실행될 때 발생될 수 있는 오류에 대한 예외처리문을 기술
  ▪ 마지막으로 END;을 기술
  ▪ PL/SQL 블록에서 한 문장이 종료할 때마다 세미콜론(;)을 쓴다.

【예제 】
  SG_Scores 테이블에서 학번이 ‘C9901'인 학생의 평균점수를 계산하여 출력하는 익명의 블록을 작성하라.

SQL> SET SERVEROUTPUT ON
SQL> get ex8_01.sql
  1  DECLARE
  2     v_avg           NUMBER(3)      := 0;
  3     v_student_id    VARCHAR2(5)    := 'C9901';
  4  BEGIN
  5     SELECT   AVG(Score)
  6       INTO   v_avg
  7       FROM   SG_Scores
  8       WHERE  student_id = v_student_id
  9       GROUP  BY student_id;
10       DBMS_OUTPUT.PUT_LINE (v_student_id || '의 평균점수는 [' 
11   || v_avg || ']점 입니다.');
12* END;
SQL> /
C9901의 평균점수는 [80]점 입니다.

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


8.3.2  PL/SQL 블록의 실행
☞ SQL*Plus를 이용하는 방법
▪ 편집기(editor)로 PL/SQL 블록을 입력한 후, *.sql 파일로 저장

▪ @ 또는 start 명령어로 *.sql 파일을 호출하여 실행

Netterm이나 텔넷(Telnet)을 이용하여 SQL*Plus상에서 블록을 실행

☞ Oracle Developer의 Procedure Builder를 이용하는 방법
▪  Program  PL/SQL Interpreter  메뉴를 선택
▪ PL/SQL Interpreter 화면에 작성한 블록을 입력
▪ END;이 입력되면 자동 실행


8.4 선언절에 변수, 상수 선언

상수는 프로그램이 시작하여 종료될 때까지 일정한 값을 가지는 수,
어떤 값을 대입 불가
12, 87과 같은 숫자형 리터럴과 ‘Computer', '홍길동’, ‘T' 와 같은 문자형 리터럴

변수는 프로그램이 시작하여 종료될 때까지 변하는 수,
어떤 값을 대입 가능. i, Course_id 등과 같이 문자로 지정

8.4.1 PL/SQL의 데이터타입

• SQL에서 사용 가능한 모든 데이터타입과  추가적인 데이터타입

데이터타입                                            설 명
-------------------------------------------------------------------------
BOOLEAN              논리적인 데이터(True, False, Unknown)를 저장할 때 사용
BINARY_INTEGER   부호가 있는 정수를 저장할 때 사용
                             데이터 범위는 -2147483647부터 2147483647까지
   ꋯ NATURAL        정수 중에서 0부터 2147483647까지 저장할 때 사용
   ꋯ POSITIVE        정수 중에서 1부터 2147483647까지 저장할 때 사용
%TYPE                  기존 테이블의 한 칼럼에 정의된 데이터타입을 참조
%ROWTYPE           기존 테이블의 모든 칼럼에 정의된 데이터타입을 참조

8.4.2 변수와 상수 선언

변수명 데이터타입(크기)                    [ := 초기값 ] ;
상수명       CONSTAT   데이터타입(크기)     := 상수값 ;


■ 변수명이나 상수명을 정의할 때는

∙ SQL 객체명과 동일한 규칙으로 정의하고
∙ 상수명은 데이터타입과 일치하는 상수 값을 반드시 기술하고
∙ 한 줄에 한 개의 변수나 상수를 정의하며
∙ 초기 값이 Null이나 0인 경우 생략할 수 있다.

【예제】변수와 상수의 선언 예

P_count NUMBER(3) := 0;
Zip_Code VARCHAR2(9);
I BINARY_INTEGER := 0;
k   CONSTANT POSITIVE := 100; 



8.4.3 테이블을 참조하여 변수 선언
     - 기존 테이블에 정의된 데이터타입과 크기를 참조하여 변수를 선언

■ %TYPE

형식=>   변수명  테이블명.칼럼명%TYPE ;

- 기존 테이블의 칼럼에 선언된 데이터타입과 크기를 참조하여 변수를 선언
- 테이블 칼럼의 제약조건은 적용되지 않음.


        v_student_id    SG_Scores.student_id%TYPE := 'C9901';
        v_avg           SG_Scores.Score%TYPE      := 0;
        v_grade         SG_Scores.Grade%TYPE;  

■ %ROWTYPE

형식=> 변수명   테이블명%ROWTYPE ;

    - 기존 테이블의 각 칼럼에 정의된 데이터타입과 크기를 참조
    - 칼럼 수와 동일한 복수 개의 변수가 선언, 
    - 각 기억 장소의 구분은 “변수명.칼럼명”으로 구분


【예제】SG_Scores 테이블을 참조하여 학번과 평균 점수를 저장하기 위한 변수를 %ROWTYPE을 이용하여 선언하라.


SQL> get ex8_04.sql
  1  DECLARE
  2     v               SG_Scores%ROWTYPE;
  3     v_cnt           NUMBER(2)       := 0;
  4  BEGIN
  5     v.student_id    := 'C9901';
  6     SELECT Count(*), AVG(SCORE)
  7       INTO   v_cnt, v.Score
  8       FROM   SG_Scores
  9       WHERE  student_id = v.student_id
10       GROUP  BY student_id;
11      IF v.score >= 90 
12         THEN  v.grade := 'A';
13         ELSIF v.Score >= 80 
14               THEN v.grade := 'B';
15               ELSIF v.Score >= 70 
16                     THEN v.grade := 'C';
17                     ELSIF v.Score >= 60 
18                           THEN v.grade := 'D';
19         ELSE  v.grade := 'F';
20      END IF;
21      DBMS_OUTPUT.PUT_LINE (v.student_id || '의 과목수는 [' ||
22                  v_cnt || ']이고 평균점수는 [' || v.Score ||
23                  ']점 [' || v.grade || '] 등급입니다.');
24* END;

8.4.4 PL/SQL 테이블과 사용자 정의 레코드 선언

■ PL/SQL 테이블 선언
   데이터타입과 크기가 동일한 기억장소가 동적으로 복수 개 선언

형식=> 
   TYPE 테이블타입명 IS TABLE OF
                데이터타입(크기)
    INDEX BY BINARY_INTEGER;

    테이블명   테이블타입명;


- 각 기억장소 구분은 “테이블명(첨자명)”, 
- 첨자명은 정수로 된 첨자명이거나, 양의 정수
- 테이블의 크기는 사용할 때 동적으로 결정

【예제】PL/SQL 테이블 선언 및 사용 예


DECLARE
TYPE Table_type IS TABLE OF
      VARCHAR2(20)
INDEX BY BINARY_INTEGER;

v_Dept_Name    Table_type;

BEGIN
...
v_Dept_Name(2) := '컴퓨터정보기술계열‘;
...
END;


■ 사용자 정의 레코드 선언
    - 데이터타입과 크기가 다른 기억장소가 복수 개 선언

형식=>  TYPE 레코드타입명 IS RECORD
         ( 필드명1  데이터타입(크기) [NOT NULL] [초기값],
            . . .
           필드명n  데이터타입(크기) [NOT NULL] [초기값] ) ;

          레코드명 레코드타입명;

       - 각 기억 장소의 구분은 “레코드명.필드명“으로 구분

【예제 】사용자 정의 레코드 선언 및 사용 예


DECLARE
TYPE  Record_Type IS RECORD
      Dept_ID   Department.Dept_ID%type,
      Dept_Name Department.Dept_Name%type;

Dept_Rec   Record_Type;
...
BEGIN
...
Dept_Rec.Dept_ID := '컴정‘;
Dept_Rec.Dept_Name := '컴퓨터정보기술계열‘;
...
END;

8.5 조건 판단문

문법>
       IF 조건1
       THEN 명령문1; . . . ; 명령문N;
       [  ELSIF 조건2 
       [         THEN 명령문1; . . . ; 명령문N;   ]
       [      ELSIF 조건3
   . . . 
       [  ELSE  명령문1; . . . ; 명령문N; ]
        END IF;

- 조건의 결과가 참이면 THEN 이하의 명령문을 처리하고,
  거짓이면 ELSIF절을 실행
- 조건이 모두 거짓이면 ELSE절
- ELSIF문은 16개까지 반복하여 사용


IF문은

① IF 조건 THEN 명령문_1
   END IF;

② IF 조건 THEN 명령문_1
           ELSE 명령문_2 
   END IF;

③ IF 조건_1
     THEN  명령문_1 
     ELSIF  조건_2
            THEN  명령문_2
            ELSIF 조건_3  THEN 명령문_3
                    ...
       ELSE 명령문_n
   END IF;

【예제 】 IF ~ THEN ~ ELSIF문 예

IF v.score >= 90 
       THEN  v.grade := 'A';
       ELSIF v.Score >= 80 
             THEN v.grade := 'B';
             ELSIF v.Score >= 70 
                   THEN v.grade := 'C';
                   ELSIF v.Score >= 60 
                         THEN v.grade := 'D';
       ELSE  v.grade := 'F';
    END IF;



   
8.6 반복문

8.6.1 LOOP문

문법> LOOP
명령문1;
. . .  ;
명령문N;
END LOOP;

- EXIT문은 무한 루프로부터 탈출하는 명령문

■ EXIT문

문법> ① EXIT;
② EXIT [레이블명] WHEN 조건;

∙ EXIT문은 무한 루프로부터 무조건 탈출
∙ EXIT WHEN 조건 명령문은 조건이 참(True)일 때 탈출


【예제 】1에서 10까지 반복하여 TEMP 테이블에 저장하라. (LOOP문 사용)

SQL> CREATE TABLE Temp (
    2  Col1 NUMBER(3),
    3  Col2   DATE);

SQL> DECLARE
  2  Max_No  CONSTANT POSITIVE := 10;
  3  I                NATURAL  := 0;
  4  BEGIN
  5     LOOP
  6        I := I + 1;
  7        EXIT WHEN I > Max_No;
  8        INSERT INTO TEMP
  9           VALUES (I, SYSDATE);
10     END LOOP;
11* END;

SQL> SELECT * FROM Temp;

8.6.2 WHILE ~ LOOP문

조건이 참(True)일 때, LOOP ~ END LOOP내의 명령문들을 반복 처리

문법> WHILE 조건 LOOP
명령문1;
. . . ;
명령문N;
END LOOP;


【예제】1에서 10까지 반복하여 TEMP 테이블에 저장하라. (WHILE문 사용)

SQL> DECLARE
  2  Max_No  CONSTANT POSITIVE := 10;
  3  I                NATURAL := 0;
  4  BEGIN
  5     WHILE I < MAX_NO LOOP
  6        I := I + 1;
  7        INSERT INTO TEMP
  8           VALUES (I, SYSDATE);
  9     END LOOP;
10* END;

8.6.3 FOR ~ LOOP문

  - 변수가 초기 값부터 1씩 증가하여 최종 값이 될 때까지 반복 처리

문법> FOR 변수 IN [REVERSE] 초기값..최종값 LOOP
명령문1;
. . . ;
                명령문N;
          END LOOP; 

【예제】1에서 10까지 반복하여 TEMP 테이블에 저장하라. (FOR문 사용)


SQL> DECLARE
  2  Max_No  CONSTANT POSITIVE := 10;
  3  I                NATURAL := 0;
  4  BEGIN
  5     FOR I IN 1..MAX_NO LOOP
  6         INSERT INTO TEMP
  7           VALUES (I, SYSDATE);
  8     END LOOP;
  9* END;


8.7 NULL문

   - 어떤 처리도 하지 않음. 문법 형식을 갖추기 위해 사용

문법>NULL;


【예제 】변수 a가 0보다 크지 않으면 i 값을 1 증가시켜라.

  IF a > 0 THEN   NULL;
           ELSE   i = i + 1;
  END IF;

8.8 대입문

문법> expr1 := expr2

우변(칼럼명, 변수, 리터럴, 수식, 함수)의 값을 좌변(칼럼명, 변수)에 저장



8.9 제어문

   GOTO문은 무조건 레이블명으로 이동

문법> GOTO  레이블명


【예제 】GOTO문을 이용한 제어문 사용 예

                       ....
GOTO label_1;
                       ....
    <>
                    ...


8.10 주석 달기

☞ --  문자열
    - 단일 줄에 주석


☞ /*  문자열  */
    - 여러 줄로 주석

8.11 실행절에 SQL문 사용

8.11.1  PL/SQL에서의 SELECT문

- 검색된 행의 수가 한 행이 되어야 하고, 검색한 값을 INTO절의 변수에 저장
- 검색된 행의 수가 0행(NO_DATA_FOUND)이거나 복수 행(TOO_MANY_ROWS)일 때 오류가 발생
- ORDER BY절은 사용하지 않음

문법>

SELECT 칼럼명1, 칼럼명2, 리터럴, 함수, 수식, ...
INTO         변수명, . . .
FROM 테이블명1, 테이블명2, 뷰명1, ...
WHERE 검색조건1 ...
GROUP BY   칼럼명1, 칼럼명2, ...
HAVING 검색조건2 ; 

■ SQL과 PL/SQL에서 SELECT문의 차이점

  ∙ SELECT 구문이 다름
  ∙ 결과 행
  ∙ 결과를 출력

【예제 】SG_Scores 테이블을 참조하여 ‘C9901'학번의 과목수, 평균 점수를 계산하여 출력하라.

SQL> DECLARE
  2     -- 변수 선언
  3     v_avg        NUMBER(3)    := 0;   -- 평균점수 저장
  4     v_cnt        NUMBER(2)    := 0;   /* 과목수 저장 */
  5     v_student_id VARCHAR2(5)  := 'C9901';    /* 검색할 학번 */
  6  BEGIN
  7     -- 학번의 데이터를 검색하여 과목수와 평균점수 계산
  8     SELECT   COUNT(Course_Id), AVG(Score)
  9       INTO   v_cnt, v_avg
10       FROM   SG_Scores
11       WHERE  student_id = v_student_id
12       GROUP  BY student_id;
13     -- 화면에 표시
14       DBMS_OUTPUT.PUT_LINE (v_student_id || '의 [' || v_cnt || 
15      ']과목에 대한 평균점수는 [' || v_avg || ']점 입니다.');
16* END;


☞ SET SERVEROUTPUT ON
【예제 】SG_Scores 테이블을 참조하여 ‘A9901'학번의 과목수, 평균점수를 계산하여 출력하라.


SQL> DECLARE
  2     v_avg        NUMBER(3)     := 0; -- 평균점수 저장
  3     v_cnt        NUMBER(2)     := 0; /* 과목수 저장 */
  4     v_student_id VARCHAR2(5)   := 'A9901';  /* 검색할 학번 */
  5  BEGIN
  6     SELECT   COUNT(Course_Id), AVG(Score)
  7       INTO   v_cnt, v_avg
  8       FROM   SG_Scores
  9       WHERE  student_id = v_student_id
10       GROUP  BY student_id;
11      DBMS_OUTPUT.PUT_LINE (v_student_id || '의 [' || v_cnt || 
12     ']과목에 대한 평균점수는 [' || v_avg || ']점 입니다.');
13* END;
SQL> /
DECLARE
*
1행에 오류:
ORA-01403: no data found
ORA-06512: at line 8 

8.12  PL/SQL에서 화면에 출력하기

▪ DBMS_OUTPUT 내장프로시저 사용하기 위한 SQL*Plus 명령어
문법> SET SERVEROUTPUT ON


▪DBMS_OUTPUT 프로시저의 활성화와 비활성화
문법>  DBMS_OUTPUT.ENABLE;
          DBMS_OUTPUT.DISABLE;

▪문자열을 화면에 출력하는 DBMS_OUTPUT.PUT_LINE
문법>  DBMS_OUTPUT.PUT_LINE(‘출력할 내용’)


【예제】‘HELLO WORLD' 문자열을 DBMS_OUTPUT 프로시저를 비활성화하여 출력하고, 활성화한 후 출력하라.

SQL> SET SERVEROUTPUT ON
SQL> BEGIN
  2     DBMS_OUTPUT.DISABLE;
  3     DBMS_OUTPUT.PUT_LINE('1. HELLO WORLD.');
  4     DBMS_OUTPUT.ENABLE;
  5     DBMS_OUTPUT.PUT_LINE('2. HELLO WORLD.');
  6* END;
SQL> /
2. HELLO WORLD. 

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

SQL>

출처 : [기타] 인터넷 : http://www.it-sesang.com 오라클공인센터

0 0