:: 작업장

Oracle Background Process.

- Oracle Process 종류

1) User Process:   사용자가 작성한 SQL 문장을 Server 프로세스로 전달하고 결과를 가져오는 프로세스.

2) Server Process: User Process가 전해 준 SQL문장을 실제 실행하는 프로세스.

3) Background Process: Oracle Server가 시작되면 자동으로 시작되어 운영과 유지를 담당하는 프로세스.

1. 필수 Background Process.

 1) DBWR(Database Writer) : Database Buffer Cache에서 변경완료 후 저장되어야 하는 블록(Dirty Block)을 데이터파일로 저장하는 역할.

* DBWR이 DB Buffer Cache의 Dirty Buffer 내용을 파일에 내려쓰는 경우.

- Check Point 신호가 발생했을 때.

- Dirty Buffer가 임계값을 지났을 때

- Time out이 발생했을 때

- RAC Ping이 발생했을 때.

- Tablespace가 Read only 상태로 변경될 때.

- Tablespace가 offline될 때.

- Tablespace가 begin backup 상태가 될 때.

- Drop table이나 Truncate table될 때.

- Diret Path Read/Write가 진행될 때.

- 일부 Parallel Query 작업이 진행될 때.

* DBWR 백그라운드 프로세스는 기본적으로 하나 작동하지만, I/O가 많을 경우 여러개를 동시에 사용할 수 있다.

SQL> Show parameter process ; 명령어를 통해 확인가능.

 2)LGWR(Log Writer) : 

-  Server Process가 변경내역을 Redo Log Buffer에 기록하게된다.

-  LGWR은 Redo Log Buffer에 있는 내용을 디스크의 Redo Log File로 저장한다.

-  만약 Commit 요청이 들어왔는데 Redo Log File이 없는 경우, Alert Log 파일에 해당 내용을 기록해 두고 LGWR은 다음 Commit요청을 수행하지 않고 대기.

* LGWR이 Redo Log Buffer의 내용을 파일에 내려쓰는 경우

- Commit 발생했을 때.

- 1/3이 찼을 때

- 변경량이 1M이 되었을 때

- 3초마다

- DBWR이 내려쓰기 전에

** 오라클은 데이터가 변경되는 경우 항상 Redo Log Buffer에 변경내용을 먼저 기록한 후DB Buffer Cache내용을 변경한다.(Log Ahead Method, Write-Ahead라고 하기도함.)

** Commit을 수행하면 데이터를 저장하는 것이 아니라, Redo Log를 저장하는 것이다.

** Commit 수행 시 Redo Log Buffer내용을 먼저 쓰는 이유

      -  변경된 내용이 적을 경우라도 해당 블록을 전부 저장해야하므로 많은 시간이 소모되는데, Redo Log Buffer의 Block크기는 DB Buffer Cache의 블록 크기보다 작기 때문.

      -  DB Buffer Cache의 블록을 저장할 때 데이터파일에서 해당 블록의 위치를 찾아서 덮어써야하기 때문에 오래걸림, Redo Log Buffer에 내용을 쓸때는 그냥 순서대로 씀.

3)PMON(Process Monitor)

- PMON은 모든 프로세스를 감시하고, 비정상적으로 종료된 프로세스가 있다면, 관련 복구작업을 하는 역할.

- 인스턴스가 시작될 때 해당 인스턴스 정보를 Listener에 등록하고 관리하는 역할을 함.

4)SMON(System Monitor)

- 인스턴스가 비정상 종료 되었을 경우, Instance 인스턴스를 시작할 때 Clean Up하는 역할 (Instance Recovery라고 함.)

- Instance Recovey 과정에서 누락된 Transaction을 Recovery하는 역할.

- 비정상 종료된 Transaction Temporary Segment를 Cleanup하는 역할.

(Create 등 작업을 하던 도중 해당 Transaction이 비정상 종료되면, Temporaray segment를 Cleanup해준다)

- Dictionary Managed Tablespace에서  Free Extents를 모아주는 역할도 함.

ex) 인스턴스 비정상 종료 시 Instance Recovery 과정.

상황: Scott 사용자가 test Table에 작업을 하다 비정상 종료됬을 경우

1.  A 입력

2. B 입력

3. Commit

4. C 입력

5. Database가 비정상종료됨.

=> 

1. Prameter File을 읽어서 Nomount단계에서 Instance 생성.

2. Mount단계에서 Control file의 내용을 확인해서 Instance Crash상황임을 확인.

3. Redo Log File에서 위 1,2,3,4 단계의 작업을 다시 수행 (Roll Forward)

* COMMIT이 되지않은 4번입력까지 수행.

4. Database Open

5. Commit이 되지않은 4번 작업 취소(Roll backward)

6. Instance Recovery 완료.

 * Instance Recovery 할 때  Online Redo Log File만 읽고 진행하며, 만약 복구내용이 Archived Redo Log File에 있다면 DBA가 수동으로 Media Recovery를 수행해야한다.

5)CKPT (Checkpoint Process)

- CKPT Process는 DBWR에게 Checkpoint 신호를 전달해 주며, Control file과 Datafile Header에 해당 Check Point 정보를 기록하는 역할 수행.

- Checkpoint 정보에는 Checkpoint 위치와 SCN, 해당 내용을 담고 있는 Redo log 내용의 위치값을 담고있다.

6)MMON, MMNL (10g이후 추가됨.)

- MMON Process는 AWR과 관련된 다양한 작업을 수행

- MMNL Process는 Active Session History정보를 디스크의 파일로 기록하는 역할 수행.

7) RECO

- 분산 데이터베이스 환경에서 Transaction처리 도중에, 장애가 발생할 경우 해당 Transaction을 자동으로 복구해주는 역할 수행

* 분산 데이터베이스가 아닐 경우 큰 의미는 없다.

2. 선택적인 Background Process

1) ARCn(Archiver Processes)

- Archive Log Mode로 데이터베이스를 운영할 경우 Online Redo Log File을 Archiving해 주는 역할 수행.

2)CJQ0 & JNNN

- 오라클에서 제공하는 Job 기능을 수행하는 프로세스.

3)FBDA(FlashBack Data Archiver Process)

- Flashback 기능 중에 Undo Data를 사용하는 기능이 있는데, 이 기능들은 Undo Data를 다른 프로세스가 덮어 써버릴 경우 Flashback을 할 수 없게되는 단점이 있다.

11g부터 FBDA를 사용할 경우 Undo data를 다른 사용자가 덮어쓰기 전에 Archive해서 보관해주는 기능이 도입되었고, Undo Data를 다른곳으로 복사해주는 역할을 수행하는

프로세스가 FBDA이다.

SQL 문장의 실행원리.

 - 사용자 문장 실행 시 User Process에서 Server Process로 실행한 SQL문 전달.

 - User Process로부터 문장을 받은 Server Process는 해당 문장에 대한 문법체크, 의미체크 후 권한검사 실행

  * 권한 검사란 해당 오브젝트에 접근할 수 있는 권한이 있는지 체크.

 - Parse과정 후 Shared Pool의 Library Cache에서 공유되어있는 실행계획이 있는지 체크.

  * Library Cache에는 한번이라도 실행된 SQL, PL/SQL문장의 실행계획이 공유된다.

  - 실행계획이 있을 경우 Execution 진행. 이 과정을 Soft Parsing 이라고함.

  - 실행계획이 없을 경우 Optimizer를 통해 Data dictionary 등을 참조하여 실행계획을 생성 ( Hard Parsing)

1. Select 문장의 실행원리

1-1) Parse( 구문분석 단계.)

- User Process로부터 전달받은 SQL문장을 Server프로세스가 SQL문을 수행하기 위해 Parse Tree생성.

- Parse Tree를 만드는 과정에서 Syntax Check, Semantic Check 진행

- 데이터 딕셔너리 조회를 통해 문법이 맞는지, 해당 테이블이 있는지 확인. 

  *  자주 사용되는 데이터 딕셔너리는 Shared Pool의 Dictionary Cache에 캐싱해 두어 성능을 높힌다.

- 오류가 없을 경우 SQL문장을 Hash 함수로 Hash Value로 변경한 후 Shared Pool의 Library cache에서 Hash Value와 비교하여 동일한 값이 있는지 확인.

  * 이 과정을 커서공유 또는 Soft Parse라고함.

# Cursor :  메모리에 데이터를 저장하기 위해 만든 임시 저장공간(공유 커서, 세션커서, 어플리케이션 커서가 있다.)

1. 공유 커서 역할 

  -  한번 수행된 SQL 문장의 실행계획과 관련 정보를 보관하고있다가 재활용을 통해 Hard Parse의 부담을 줄여 SQL 문장의 수행속도를 빠르게  

  -  커서 공유를 하기 위해서는 사용자와, 옵티마이저 모드가 같아야한다.

2. 공유 커서  구성

 1)  부모 커서 : SQL문장에 대한 값을 저장.

 2) 자식 커서: 사용자 정보, Optimizer 모드 같은 정보 저장.

Hash List : 오라클은 어떤 커서에 들어가있는 데이터 정보를 리스트로 관리 하는데, 이 리스트를 Hash List라고 함.

   Hash List에 있는 정보는 Chain 구조로 다음 데이터가 연결되어있음.

Library Cache안에 원하는 SQL 문과 실행계획이 있는 커서를 찾기 위해서는 Hash List를 읽어야만한다.

- 오라클 에서는 Library cache를 탐색하기위해 Library Cache Latch를 가져야만 한다.

- 사용자가 Select를 했을 경우 Hash List를 확인한 후 원하는 SQL과 실행계획이 없는 경우 서버프로세스는 Hard Parse 과정을 수행한 후 실행계획을 받아서 Library Cache에 신규로 등록한 후 다음단계로 넘어감.

 즉,  해당 SQL을 처음 실행한 경우 Hash List를 업데이트 해야한다. ( Hash List를 update 하거나, 신규로 등록할 경우 Library Cache Latch를 획득한 후 Shared Pool Latche가 추가로 필요함.)

커서공유를 실패한다면, Optimize를 통해 새로운 실행계획을 찾아야 한다.

* 옵티마이저가 새로운 실행계획을 세우는 단계를 Hard Parse라고 한다.

 1) RBO : Rule Based Optimizer.

 2)CBO : Cost Based Optimizer.

- CBO는 실행계획을 세울 때 데이터 딕셔너리 정보를 이용하여 판단.

- 옵티마이저가 참조하는 데이터 딕셔너리 중 대부분은 Static Dictionary 즉 항상 최신의 정보를 가지고있지 않는다.

- 데이터 딕셔너리를 관리해줘야한다.

정리

 - 서버 프로세스가 SQL 문장을 실행하기 위해서는 실행계획이 필요하다.

 - 해당 SQL문장이 이미 수행되어 실행계획이 Shared Pool의 Library Cache에 캐싱 되어있는경우 Soft Parse를 진행한다.

 - 해당 SQL 문장이 처음 실행될 경우 Optimizer를 통해 실행계획을 만들고 이 과정을 Hard Parse라고한다.

1-2) Bind.

같은 테이블의 같은 컬럼의 결과를  조회할 경우 Bind 변수를 사용하면, 1번 파싱으로 같은 실행계획을 사용하므로  쿼리 수행속도가 빠를 것이다.

1-3)Execute

Parse와 Bind 단계 후 서버프로세스는 해당 데이터를 가져오기위해 데이터가 저장되어있는 데이터 블록을 찾아 DB Buffer Cache에 복사하는 과정을 Execute라고 함.

* 오라클은 작업을 하기위해 해당 블록을 데이터 파일에서 찾은 후 DB Buffer Cache로 복사해 놓아야한다. 

- 사용자가 찾는 모든 데이터는 SGA의 DB Buffer Cache에 있어야 한다.

- 사용자가 찾거나 변경하는 모든 작업은 DB Buffer Cache에서 작업이 수행된다.

- 서버프로세스는 해당블록을 찾기위해 DB Buffer Cache를 확인 후 없는 경우 데이터 파일로부터 복사해온다.

- DB Buffer Cache는 데이터파일의 블록 주소를 Hash 값으로 변경하여 리스트로 데이터파일 블록을 관리한다.

* DB Buffer Cache에 원하는 블록이 있는지 확인과정.

- 블록 주소를 Hash함수에 넣어 Hash Value를 만든다.

- 해당 Hash Value와 DB Bufffer Cache Hash List를 비교하여 동일한 Hash Value가 DB Buffer Cache에 있는지 검사.

=> 원하는 블록이 있는 경우 다음단계인 Fetch  단계 수행, 없는 경우 하드 디스크에서 해당블록을 DB Buffer Cache에 복사해옴.

1-4) Fetch

- Execute 단계까지 수행하면, 원하는 데이터가 들어있는 블록이 DB Buffer Cache에 올라오게됨.

- 데이터의 I/O 최소 단위가 Block이므로, DB Buffer Cache에 원하는 데이터만 있는것이 아닌 다른 데이터도 있으므로 

사용자가 요청한 데이터만 골라내는 과정을 Fetch라고한다.

- 정렬이 필요하거나 추가 작업을 요구하는 경우 Fetch과정에서 Sort를 하여 데이터를 보내주며, 정렬은 PGA영역(Program Global Area)에서 수행한다.

2. Update 문장의 실행원리.

- 모든 DML의 수행원리는 동일하다.

- DML의 수행단계는 Fetch과정만 없고 나머지는 동일하다.

ex)  UPDATE EMP

      SET SAL= 30000

     WHERE EMPNO=1000;

1) 서버프로세스는 Parse과정 수행

   - Shared Pool 의 Library Cache를 살핀 후 실행계획이 있는지 확인. 있으면 Soft Parse, 없으면 Hard Parse수행

2) 실행계획을 받은 서버프로세스는 Library Cache에 등록한 후 Execute 수행

   - DB Buffer Cache에 emp table의 1000번 사원이 있는지 확인, 없을 경우 데이터파일의 해당 블록을 DB Buffer Cache에 복사.

3)Execute 단계에서 원하는 데이터가 들어있는 DB Buffer Cache를 가져온 후 Server프로세스는 데이터 변경 내역을 Redo Log Buffer에 먼저 기록.

 * Redo Log Buffer: 데이터가 변경될 경우 장애를 복구하기 위해 변경 내역을 기록하는 공간.

                               기록 후 Undo Segment에 원본 이미지를 기록한 후 DB Buffer Cache의 내용을 변경.

*** 데이터 변경이 일어날 경우

Redo Log Buffer에 기록 -> Undo Segment에 기록 -> DB Buffer Cache의 실제 데이터 변경

이 과정을 오라클에서는 Transaction이라고함

1. Oracle 서버 전체구조.

- 인스턴스: 메모리 부분에 생성되는 구조 

- 데이터베이스: 데이터파일, Control file, Redo log File

2. SGA 구성요소.

1) DB Buffer Cache

  - 데이터의 조회와 변경 등의 실제 작업이 일어나는 공간.

  - 조회나 변경을 원하는 데이터들은 모두 DB Buffer cache에 내용이 존재해야하며, 없는 경우 데이터파일에서 해당 내용이 들어있는 블럭을 복사해 가져와야함.

  - 디스크 I/O 보다 메모리에서 작업속도가 빠르기 때문.

  1-1) Buffer Block 상태.

   - Pinned Buffer : 다른 사용자가 현재 사용하고있는 Buffer 블록.

   - Dirty Buffer   :  현재 작업은 진행되지 않지만, 다른 사용자가 내용을 변경한 후 아직 데이터파일에 변경된 내용을 저장하지 않은 Buffer.

   - Free Buffer    :  사용할 수 있는 버퍼로써 사용되지않은 버퍼 이거나, Dirty buffer이었다가 하드디스크에 저장이 완료되어 재사용할 수 있는 블록.

  1-2) Buffer 상태 관리방법.

    - LRU List : 가장 최근까지 많이 사용된것은 지키고 가장 사용이 안 된 것은 덮어씀.

    - LRU list는 스캔 효율성을 높이기 위해 LRU 리스트와 LRUW 리스트로 사용.

    - LRU List + LRUW List를 Working set 이라고 부름.

    * LRU List 

       - 메인리스트 : 사용된 Buffer들의 리스트. Hot 영역, Cold 영역으로 나뉨.

       - 보조리스트 : 미사용된 Buffer들이나, DBWR에 의해 기록된 버퍼들의 리스트.

    *  LRUW List

       - 메인리스트 : 변경된 Buffer들의 리스트 (Dirty List)

       - 보조리스트 : 현재 DBWR 들이나, DBWR에 의해 기록된 Buffer들의 리스트.

  1-3) 사용자가 데이터 파일을 DB Buffer Cache로 가져오는 경우.

    1 )  DB Buffer cache에서 Free 상태의 버퍼를 확보.

    2)  확보하기위해 LRU 리스트의 보조리스트에서 Free Buffer 찾는다.

    3)  보조리스트의 Free Buffer가 모두 사용된 경우 메인리스트의 Cold영역 에서 Free List를 찾는다.

    4)  cold 영역에서 Free Buffer 찾다가 10g 기준 40%만큼 찾았는데 없는 경우 Scan을 멈추고 DBWR에게 Dirty Buffer를 내려쓰도록 요청.

    5)  파일로 저장이 완료된 Dirty Buffer는 Buffer 상태가 Free Buffer로 변경되어 LRU list의 보조리스트에 추가됨.

2) Redo Log Buffer 

- 데이터의 변경사항이 생길 경우 해당 변경내용을 기록해두는 역할. (DDL, DML, TCL 같은 작업 시)

- 장애 발생 시 복구를 하기위함.

- Redo Log Buffer : 변경된 내용을 기록하는 메모리공간

- Redo Log File     :  Redo Log Buffer의 내용을 디스크에 저장해주는 파일.

3) Shared Pool

- Library cache와 Dictionary cache등으로 구성.

3-1) Library Cache : Soft Parse 할 때 사용되는 공간.

 - 이미 수행했던 SQL 문장이나, PL/SQL 문장의 Parse Code와 해당 SQL/PLSQL 문장, 실행계획 등이 저장됨.

 - LRU 알고리즘으로 관리됨.

3-2) Dictionary Cache : 구문분석이나, 옵티마이저가 실행계획 세울 때 사용되는 주요 Dictionary들이 Row 단위로 Cache 되어있음.

 - LRU 알고리즘으로 관리됨.

3-3) Server Result Cache : 결과값을 Cache해 두는 공간.

- 11g 부터 도입됨.

- SQL 수행 결과를 저장해 두었다가, 동일한 SQL이 수행될 경우 DB Buffer cache를 거치지않고 즉시 Server Result Cache에서 가져가도록 하여 속도를 높임.

3-4) Large Pool

- 필수 구성요소는 아니며, 특정 기능을 사용할 경우에만 쓰는 SGA 구성요소.

-  Shared Server모드로 Oracle Server를 운영하는 경우 UGA를 이곳에 생성.

- 병렬처리 작업을 할 경우 각 Process들간의 Message Buffer가 생성되는곳. 

- RMAN 백업이나 복구를 할 경우 RMAN이 사용하는 I/O용 Buffer가 이곳에 생성됨.

- LRU알고리즘으로 관리되지 않음.

3-5) JAVA Pool

- 필수 구성요소는 아니며, JAVA관련 코드나 JVM관련 데이터를 저장하기 위해 생성되는 선택적인 공간.

3-6) Streams Pool

- 10g 버전이후 생긴 SGA 구성요소.

- Streams 기능을 사용할 경우 사용됨.

- Streams 기능을 사용하게 되면 동적으로 Oracle Streams가 그 크기를 증가시킴.

3-7) Fixed SGA

- Oracle이 내부적으로 사용하기 위해 생성시키는 공간.

- 백그라운드 Process들이 필요한 Database의 전반적인 공유 정보나 각 Process들이 공유해야만 하는 Lock 정보 같은 내용들이 저장됨.

- Oracle 시작 시 자동으로 설정되며, 사용자나 관리자가 임의로 변경할 수 없음.

4) Dynamic SGA 기능.

- 9i 이후 도입된 개념으로 관리자의 필요에 의해 SGA 구성요소의 크기를 변경한 경우 Oracle Instance의 재기동 없이 즉시 적용이 가능해짐.(Redo Log Buffer를 포함한 몇가지 제외)

- 변경되는 크기는 그래뉼 이라는 단위를 통해 변경됨.

SQL> show sga

Total System Global Area  313159680 bytes

Fixed Size                              2212936 bytes

Variable Size                       167775160 bytes

Database Buffers               138412032 bytes

Redo Buffers                         4759552 bytes

- Total System Global Area : 전체 SGA 양을 의미.

- Fixed Size     : Background Process들이 사용하는 공간.

- Variable Size : Shared Pool, Lagre Pool, Java Pool로 사용되는 공간.

- Database Buffers: DB Buffer Cache 사용되는 공간.

- Redo buffers : Redo Log Buffer로 사용될 공간.

5) Program Global Area(PGA)

- 각 Process들이 사용하는 메모리 공간.

- Oracle Server내에서 동작하는 모든 Process는 각각 PGA를 갖고있음.

- 주로 정렬관련 작업등이 이루어짐.

- 적당한 PGA용량 계산방법.

* OLTP 환경 

  PGA_AGGREGATE_TALGET = (( 총 물리 메모리 용량 * 80%) * 20%)

* DSS 환경

* PGA_AGGREGATE_TALGET = (( 총 물리 메모리 용량 * 80%) * 50%)

 5-1) Private SQL Area

-  사용자가 SQL  문장 실행 시 User Process가 Server Process에게 해당 쿼리를 전달.

-  Server Process는 자신에게 작업을 요청한 User Process의 정보를 Session Memory 부분에 저장 후 해당 SQL Parse 작업 수행.

-  해당 SQL에 Bind 변수가 있을 경우, Bind 변수 값을  Private SQL Area에 보관하고 Query의 실행 상태정보와 query를 수행하며 임시로 정보를 저장해야하는 경우 이 공간 사용.

 - Persistent Area : Bind 변수 값을 저장해 두는 공간.

 - Runtime Area   : SQL 문장을 수행하는 도중 데이터를 임시로 저장해야 할 경우 사용하는 공간. 

                               ex) DB Buffer Cache에서 PGA로 Fetch가 다 되어야만 출력이 가능한데 100만 건의 테이블을 조회하여 데이터를 모두 출력해야 할 경우,

                                 100건 모두가 Fetch 될 때 까지 Runtime Area에서 모음. 

5-2) SQL Work Area.

- Sort 작업이나 Hash 관련 작업이 있을 경우 수행되는 공간.

- 8i 까지 수동으로 관리 했으나 9i부터 자동으로 관리하는 방법이 등장.

- PGA_AGGREGATE_TAGET의 값을 설정한 후 WORKAREA_SIZE POLICY를 AUTO로 설정하면 PGA 구성요소의 크기를 Oracle Server가 동적으로 관리.

#. tnsnames.ora 클라이언트가 서버를 찾아갈때 필요한 정보를 저장한 파일 

 cat /u01/app/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora

# tnsnames.ora Network Configuration File: /u01/app/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

LISTENERS_ORCL =

  (ADDRESS_LIST =

    (ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip)(PORT = 1521))

    (ADDRESS = (PROTOCOL = TCP)(HOST = rac2-vip)(PORT = 1521))

  )

ORCL2 =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = rac2-vip)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = orcl)

      (INSTANCE_NAME = orcl2)

    )

  )

ORCL1 =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = orcl)

      (INSTANCE_NAME = orcl1)

    )

  )

ORCL =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip)(PORT = 1521))

    (ADDRESS = (PROTOCOL = TCP)(HOST = rac2-vip)(PORT = 1521))

    (LOAD_BALANCE = yes)

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = orcl)  

    )

  )

RAC환경의 서비스는 세션의 묶음. 

업무별로 서비스를 묶고, 서비스의 이름을 가지고 들어오면 그 그룹안에 있는 인스턴스 중에 보다 여유로운 인스턴스에 연결

 * 업무의 분할이 RAC 튜닝의 포인트.

SYS@orcl1>show parameter listener

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

local_listener                       string

remote_listener                      string      LISTENERS_ORCL

PROCEDURE : 미리 정리된 로직에 따라 필요한 명령을 절차적으로 실행하는 서브프로그램

                     리턴값을 가질 수도 있음.

FUNCTION    : 미리 정리된 로직에 따라 필요한 명령을 절차적으로 실행하는 서브프로그램

                     리턴값이 반드시 있어야한다.

PACKAGE    : 서브 프로그램의 묶음.

TRIGGER     :  특정 이벤트 발생 시 자동으로 수행되는 PL/SQL 블록

# 치환 변수

BEGIN

  UPDATE EMP

  SET SAL  = 4000

  WHERE DEPTNO = &ID;

END ;

/

Enter value for id: 50

old     4:   WHERE DEPTNO = &ID;

new   4:   WHERE DEPTNO = 50;

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.25

& 치환 변수는 SQLplus/ SQLdeveloper 에서만 사용되는 변수. (SQLplus차원에서 바꾼다.)

SQLplus차원에서 바뀌어 서버에 전달된다.

BEGIN

  UPDATE EMP

  SET SAL  = 4000

  WHERE DEPTNO = 50;

END ;

/

이렇게 전달된다.

- 프로시져 생성.

CREATE PROCEDURE UPDATE_EMP                          --SPEC(PL/SQL 헤더) 시작

( P_SAL        NUMBER,   --파라미터

 P_DEPTNO      NUMBER)                                             --SPEC  끝

AS                                                                                       --BODY 시작

 V_EMP              NUMBER ;           --로컬변수

BEGIN

  UPDATE EMP

  SET SAL  = P_SAL

  WHERE DEPTNO = P_DEPTNO;

END ;

/                                                                                            --Body 끝

- 프로시져 수정 시 OR REPLACE를 써서 덮어쓸 수 있다.

- 프로시져 수행시 파라미터값을 넣어서 쓰면된다.

EXECUTE UPDATE_EMP(5000,50);

-프로시져 구조를 보기 위해선 DESC UPDATE_EMP;

# 이름지정방식

CREATE OR REPLACE PROCEDURE raise_salary

( p_id IN employees.eployee_id%TYPE DEFAULT 100

 p_percent IN NUMBER := 10)

~~~~~~~~

EXECUTE RAISE_SALARY (101,20) : 위치 지정 방식

기본값을 쓴다고 하더라도 앞에있는 파라미터부터 값을 다 주어야한다.

EXECUTE RAISE_SALARY (P_PERCENT =>20) :  이름 지정 방식

CREATE PROCEDURE P1

(

 P1     NUMBER :=1

 P2     NUMBER :=2

 P3     NUMBER :=3

 P4     NUMBER :=4)

IS

~~~~~~~~~~~~~~~~~~~~~~~

EXECUTE P1(100,200,300,400)

EXECUTE P1( 1,2,300)         --위치 지정방식

EXECUTE P1 (P=>300)            --이름 지정방식

EXECUTE P1 (100, P4=>500)  --혼합지정방식

EXECUTE P1( P4=>500, 100)  → ERROR

혼합 지정방식을 이용하면 이름지정방식이 먼저 나올경우 뒤는 계속 이름 지정방식을 써야한다.

8. 예외 처리

SQL> ALTER TABLE emp

ADD CONSTRAINT emp_ck CHECK ( sal > 0 ) ;  --제약조건 추가하고 실습시작.

SQL>

SELECT empno, ename, sal

FROM emp

WHERE deptno = 10 ;

EMPNO ENAME SAL

---------- ---------- ----------

7782 CLARK 2450

7839 KING 5000

7934 MILLER 1300

--------------------------------

BEGIN

             UPDATE emp

             SET sal = 3000

             WHERE empno = 7782 ;    --정상 수행 가능한 문장.

             UPDATE emp

             SET sal = 0

             WHERE empno = 7934 ;    --제약조건 위배, Error문장.

END ;

/

ERROR at line 1:

ORA-02290: check constraint (ORA1.EMP_CK) violated

ORA-06512: at line 6

→ PL/ SQL 블럭중에서 하나라도 실패하는게 있다면, 정상 수행되는 문장도 수행되지 않는다.

SQL> SELECT empno, ename, sal

FROM emp

WHERE deptno = 10 ;

EMPNO ENAME SAL

---------- ---------- ----------

7782 CLARK 2450

7839 KING 5000

7934 MILLER 1300

#TX-0 OPEN

             UPDATE emp

             SET sal = 9999

             WHERE empno = 7782 ;

BEGIN

             UPDATE emp

             SET sal = 3000

             WHERE empno = 7782 ;    --정상 수행 가능한 문장.

             UPDATE emp

             SET sal = 0

             WHERE empno = 7934 ;    --제약조건 위배, Error문장.

END ;

/

SQL> SELECT empno, ename, sal

FROM emp

WHERE deptno = 10 ;

EMPNO ENAME SAL

---------- ---------- ----------

7782 CLARK 9999

7839 KING 5000

7934 MILLER 1300

→ PL/SQL블럭이 성공이 되든 실패가 되든 사용자가 COMMIT/ ROLLBACK 하지 않으면

트랜잭션은 종료되어있지 않다. update문은 성공하고 PL/SQL블럭은 실패한 결과를 보여준다.

Ex1) 모든 ERROR상황은 OTHERS를 통해 잡을 수 있다.

SET SERVEROUTPUT ON

BEGIN

 UPDATE EMP

 SET SAL = 3000

 WHERE EMPNO = 7782;

 UPDATE EMP

 SET SAL = 0

 WHERE EMP = 7934 ;

EXCEPTION

 WHEN OTHERS THEN

   DBMS_OUTPUT.PUT_LINE('ERROR');

END;

/

EX2)

SET SERVEROUTPUT ON

BEGIN          --암시적으로 세이브 포인트가 생긴다.

 UPDATE EMP

 SET SAL = 3000

 WHERE EMPNO = 7782;       --실행됨.

 UPDATE EMP

 SET SAL = 0

 WHERE EMP = 7934 ;           --실행안됨 Exception 절로넘어가서 필요한 처리기로간다.

 UPDATE EMP

 SET SAL = 3000

 WHERE EMPNO = 7782;      --시도조차 하지 않는다.

EXCEPTION

 WHEN NO_DATA_FOUND THEN

  DMBS_OUTPUT.PUT_LINE('NO DATA');

 WHEN OTHERS THEN

  DBMS_OUTPUT.PUT_LINE('ERROR');   --에러처리를 한다.

END;

/

#에러를 핸들링 하지 못한다면 해당 PL/SQL블럭은 취소가 된다.

EX)오라클 서버로부터 만들어진 암시적 에러

SET SERVEROUTPUT ON

DECLARE

 V_SAL NUMBER ;

BEGIN

 SELECT SAL INTO V_SAL

 FROM EMP

 WHERE EMPNO= 1234 ;

END ;

/

ERROR at line 1:

ORA-01403: no data found

ORA-06512: at line 4

Ex) SQLERRMM 을 이용한 에러 확인.

SQL> SET SERVEROUTPUT ON

BEGIN

UPDATE emp

SET sal = 3000

WHERE empno = 7782 ;

UPDATE emp

SET sal = 0

WHERE empno = 7934 ;

EXCEPTION

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE ( SQLERRM ) ;

END ;

/

ORA-02290: check constraint (ORA1.EMP_CK) violated

PL/SQL procedure successfully completed.

※ 미리 정의된 예외 처리

ex)

SET SERVEROUTPUT ON

DECLARE

  emp_rec emp%ROWTYPE ;

BEGIN

  SELECT * INTO emp_rec FROM emp WHERE ename = UPPER('&name') ;

  DBMS_OUTPUT.PUT_LINE ( emp_rec.sal ) ;

EXCEPTION

WHEN NO_DATA_FOUND THEN

  DBMS_OUTPUT.PUT_LINE ('NO_DATA_FOUND') ;

WHEN TOO_MANY_ROWS THEN

  DBMS_OUTPUT.PUT_LINE ('TOO_MANY_ROWS') ;

WHEN OTHERS THEN

  DBMS_OUTPUT.PUT_LINE ('OTHERS') ;

END ;

/

Enter value FOR name: RYU OLD 6: WHERE ename = UPPER('&name') ;

NEW 6: WHERE ename                           = UPPER('RYU') ;

NO_DATA_FOUND PL/SQL

PROCEDURE successfully completed.

※ 미리 정의되지 않은 예외 처리

ex)

SET SERVEROUTPUT ON

DECLARE

  emp_rec emp%ROWTYPE ;

  e_ck EXCEPTION ;

  PRAGMA EXCEPTION_INIT (e_ck , -2290) ;  --예외생성.

BEGIN

  SELECT * INTO emp_rec FROM emp WHERE ename = UPPER('&name') ;

  IF emp_rec.sal < 2000 THEN

    UPDATE emp SET sal = &salary WHERE empno = emp_rec.empno ;

  END IF ;

EXCEPTION

WHEN NO_DATA_FOUND THEN

  DBMS_OUTPUT.PUT_LINE ('NO DATA') ;

WHEN E_CK THEN

  DBMS_OUTPUT.PUT_LINE ('Invalid Salary') ;

WHEN OTHERS THEN

  DBMS_OUTPUT.PUT_LINE (SQLERRM) ;

END ;

/

※ 사용자 정의 예외

SET SERVEROUTPUT ON

DECLARE

  v_deptno             NUMBER       := 50 ;

  v_name               VARCHAR2(20) := 'Testing' ;

  e_invalid_department EXCEPTION;

BEGIN

  UPDATE dept SET dname = v_name WHERE deptno = v_deptno ;

  IF SQL%NOTFOUND THEN

    RAISE e_invalid_department ;

  END IF;

  COMMIT;

EXCEPTION

WHEN e_invalid_department THEN

  DBMS_OUTPUT.PUT_LINE('No such department id.');

END;

/

No such department id. PL/SQL

PROCEDURE successfully completed.

※ RASE_APPLICATION_ERROR 사용

– 마치 오라클 서버에서 만들어진것 처럼 ERROR코드와 ERROR메세지가 나온다.

– 비정상종료를 강제로 유발시키고자할때 사용한다(암시적인 에러와 동일하다)

– 이전에 정상실행된 SQL문이 있더라도 ROLLBACK된다.

BEGIN

  UPDATE dept SET dname = 'Testing' WHERE deptno = 50 ;

  IF SQL%NOTFOUND THEN

    RAISE_APPLICATION_ERROR ( -20001, 'No such department id.' ) ;

  END IF;

END;

/

ERROR at line 1:

ORA-20001: No such department id.

ORA-06512: at line 7

ex2) 사용자 정의 에러문장은 보여주지만, 정상종료된다.

BEGIN

  UPDATE dept SET dname = 'Testing' WHERE deptno = 50 ;

  IF SQL%NOTFOUND THEN

    RAISE_APPLICATION_ERROR ( -20001, 'No such department id.' ) ;

  END IF;

EXCEPTION

WHEN OTHERS THEN

  DBMS_OUTPUT.PUT_LINE(SQLERRM) ;

END;

   /

ORA-20001: No such department id. PL/SQL

PROCEDURE successfully completed.

Ex3)

DECLARE

  emp_rec emp%ROWTYPE ;

BEGIN

  SELECT * INTO emp_rec FROM emp WHERE deptno = 10 ;

EXCEPTION

WHEN TOO_MANY_ROWS THEN

  RAISE_APPLICATION_ERROR ( -20001, 'Too Many Rows', TRUE ) ;

                                                                                      --FALSE 로 변경하면, 원래에러메시지만 나온다.

END ;

                    /

ERROR at line 1: ORA-20001: Too Many Rows ORA-06512: at line 9 ORA-01422: exact

FETCH returns more than requested NUMBER OF rows

※ 예외 전달

ex1)

BEGIN

  UPDATE emp SET sal = 7777 WHERE empno = 7788 ;

  BEGIN

    UPDATE emp SET sal = 9999 WHERE empno = 7566 ;

    UPDATE emp SET sal = 0 WHERE empno = 7839 ;

    UPDATE emp SET sal = 9999 WHERE empno = 7499 ;

  END ;        – ERROR1

  UPDATE emp SET sal = 7777 WHERE empno = 7369 ; 

END ;

/

ERROR1은 그 호출한 바깥으로 에러를 전달한다, 바깥쪽 블럭에도 EXCEPTION이 없기때문에 예외처리 되지 않으며, 어떠한 결과도 변경되지 않는다.

ERROR at line 1:

ORA-02290: check constraint (ORA1.EMP_CK) violated

ORA-06512: at line 11

SQL> SELECT empno, ename, sal

FROM emp

WHERE empno IN (7788, 7566, 7839, 7369, 7499) ;

 EMPNO ENAME SAL

---------- ---------- ----------

7369 SMITH 800

7499 ALLEN 1600

7566 JONES 2975

7788 SCOTT 3000

7839 KING 5000

EX2)

SQL> SET SERVEROUTPUT ON

BEGIN

  UPDATE emp SET sal = 7777 WHERE empno = 7788 ;

  BEGIN

    UPDATE emp SET sal = 9999 WHERE empno = 7566 ;

    UPDATE emp SET sal = 0 WHERE empno = 7839 ;        → Exception절로 이동한다.

    UPDATE emp SET sal = 9999 WHERE empno = 7499 ;  → 시도하지 않는다.

  EXCEPTION

  WHEN OTHERS THEN

    DBMS_OUTPUT.PUT_LINE (SQLERRM) ;

  END ;

  UPDATE emp SET sal = 7777 WHERE empno = 7369 ;  → 예외가 처리되었으므로 실행된다.

END ;

   /

ORA-02290: CHECK CONSTRAINT (ORA1.EMP_CK) violated PL/SQL

PROCEDURE successfully completed. SQL>

SELECT empno,

    ename,

    sal

FROM emp

WHERE empno IN (7788, 7566, 7839, 7369,7499) ;

  EMPNO ENAME SAL

  ---------- ---------- ----------

  7369 SMITH 7777 7499 ALLEN 1600 7566 JONES 9999 7788 SCOTT 7777 7839 KING 5000 SQL> ROLLBACK ;

EX3)

SQL>

SET SERVEROUTPUT ON

BEGIN

  UPDATE emp SET sal = 7777 WHERE empno = 7788 ;

  BEGIN

    UPDATE emp SET sal = 9999 WHERE empno = 7566 ;

    UPDATE emp SET sal = 0 WHERE empno = 7839 ;       → 에러 발생.

    UPDATE emp SET sal = 9999 WHERE empno = 7499 ;

  END ;

  UPDATE emp SET sal = 7777 WHERE empno = 7369 ;

EXCEPTION

WHEN OTHERS THEN

  DBMS_OUTPUT.PUT_LINE (SQLERRM) ;        → 에러처리.

END ;

/

 ORA-02290: check constraint (ORA1.EMP_CK) violated

PL/SQL procedure successfully completed.

SQL> SELECT empno, ename, sal

FROM emp

WHERE empno IN (7788, 7566, 7839, 7369,7499) ;

 EMPNO ENAME SAL

---------- ---------- ----------

7369 SMITH 800

7499 ALLEN 1600

7566 JONES 9999

7788 SCOTT 7777

7839 KING 5000

#정리다시해야함.

중첩된 데이터의 피엘에스큐엘을 사용하면 정상 적으로 되다가 서브블록이 호출되면

자기불럭의 익셉션 절을 찾고, 핸들링했다면 메인블럭으로 돌아와서 나머지명령을 실행한다.

서브블록에서 에러를 핸들링 하지못했다면(익셉션절이 없거나, 있지만 처리기가 제대로 되지않을경우)

메인절의 익셉션 절로 넘어가고 그 사이에있는 것은 실행조차 되지 않는다.

메인절에서 처리기를 정상 처리해줬다면 서브블럭은 정상처리된다.

BEGIN

   SQL 1

   SQL 2   - ERROR  EXCEPTION절로 넘어간다.

   SQL 3   -실행조차 하지않는다.

EXCEPTION

END ;

Q> 만약  SQL 3번을 꼭 사용해야한다면. 중첩된 블록을 이용한다.

BEGIN

   SQL 1

   BEGIN

            SQL 2 

   EXCEPTION

   END ;

   SQL 3                 

EXCEPTION

END ;

7. 명시적 커서 사용

ex1)많은 행을 가져오므로 ERROR

SQL>

SET SERVEROUTPUT ON

DECLARE

  emp_rec emp%ROWTYPE ;

BEGIN

  SELECT * INTO emp_rec FROM emp WHERE deptno = 10 ;

  DBMS_OUTPUT.PUT_LINE ( emp_rec.empno || ' ' || emp_rec.ename ) ;

END ;

                    /

ERROR at line 1: ORA-01422: exact

FETCH returns more than requested NUMBER OF rows ORA-06512: at line 4

EX2) 커서 생성을 통한 여러 행 불러오기.

DECLARE

 CURSOR emp_cur   --Cursor 생성.

  IS

    SELECT * FROM emp WHERE deptno = 10 ;

  emp_rec emp%ROWTYPE ;

BEGIN

  OPEN emp_cur ;   --open이 되어야 문장이 실행된다.

  FETCH emp_cur INTO emp_rec ;  --첫번째 행만 FETCH.

  DBMS_OUTPUT.PUT_LINE ( emp_rec.empno || ' ' || emp_rec.ename ) ;

  FETCH emp_cur INTO emp_rec ; --그 다음행 선별하여 꺼내온다.

  DBMS_OUTPUT.PUT_LINE ( emp_rec.empno || ' ' || emp_rec.ename ) ;

  CLOSE emp_cur ;  --메모리 할당 해제.

END ;

                       /

7782 CLARK 7839 KING PL/SQL

PROCEDURE successfully completed.

Ex3) LOOP를 통한 반복.

DECLARE

  CURSOR emp_cur

  IS

    SELECT * FROM emp WHERE deptno = 10 ;

  emp_rec emp%ROWTYPE ;

BEGIN

  OPEN emp_cur ;

  LOOP

    FETCH emp_cur INTO emp_rec ;

    EXIT

  WHEN emp_cur%NOTFOUND ;

    DBMS_OUTPUT.PUT_LINE ( emp_rec.empno || ' ' || emp_rec.ename ) ;

  END LOOP ;

  CLOSE emp_cur ;

END ;

                                   /

7782 CLARK 7839 KING 7934 MILLER PL/SQL

PROCEDURE successfully completed.

Ex3) For Loop문과 결합된 Cursor :  암시적으로 OPEN / FETCH / CLOSE가 수행된다.

DECLARE

  CURSOR emp_cur

  IS

    SELECT * FROM emp WHERE deptno = 10 ;

BEGIN

  FOR emp_rec IN emp_cur   --emp_rec선언 하지않아도 자동적으로 선언됨.

  LOOP

    DBMS_OUTPUT.PUT_LINE ( emp_rec.empno || ' ' || emp_rec.ename ) ;

  END LOOP ;

END ;

                                   /

7782 CLARK 7839 KING 7934 MILLER PL/SQL

PROCEDURE successfully completed.

Ex4)

BEGIN

  FOR emp_rec IN

  ( SELECT * FROM emp WHERE deptno = 10

  )

  LOOP

    DBMS_OUTPUT.PUT_LINE ( emp_rec.empno || ' ' || emp_rec.ename ) ;

  END LOOP ;

END ;

/

7782 CLARK

7839 KING

7934 MILLER

PL/SQL procedure successfully completed.

Ex4) Parameter가 포함된 커서.

DECLARE

  CURSOR emp_cur ( p_deptno NUMBER )

  IS

    SELECT * FROM emp WHERE deptno = p_deptno ;

BEGIN

  FOR emp_rec IN emp_cur (10)

  LOOP

    DBMS_OUTPUT.PUT_LINE ( emp_rec.deptno || ' : ' || emp_rec.empno || ' ' || emp_rec.ename ) ;

  END LOOP ;

  FOR emp_rec IN emp_cur (20)

  LOOP

    DBMS_OUTPUT.PUT_LINE ( emp_rec.deptno || ' : ' || emp_rec.empno || ' ' || emp_rec.ename ) ;

  END LOOP ;

END ;

/

SELECT empno, ename, sal, deptno FROM emp

WHERE deptno = 10 FOR UPDATE ; --검색된 행에대해 LOCK이 걸린다.

※ WHERE CURRENT OF 절 사용

Ex1)현재 내가 FETCH 해온 그 행에대해 직접적인 UPDATE를 한다.

DECLARE

  CURSOR emp_cur

  IS

    SELECT * FROM emp WHERE deptno = 10 FOR UPDATE ;

BEGIN

  FOR emp_rec IN emp_cur

  LOOP

    IF emp_rec.sal < 2000 THEN

      UPDATE emp SET sal = sal * 1.1 WHERE CURRENT OF emp_cur ; 

                                                                  /* WHERE empno = emp_rec.empno */         --최선이아니다!.   

    END IF ;

  END LOOP ;

END ;

/

전재 조건으로 반드시 해당 커서는 선언될 때 FOR UPDATE 절이 있어야한다.

LOCK이 많이 발생해 자주 쓸 순 없다.

* 해결방안. #음성178 확인50분 부터

DECLARE

  CURSOR emp_cur

  IS

    SELECT a.*, rowid rid from a  FROM emp a WHERE deptno = 10 FOR UPDATE ;

BEGIN

  FOR emp_rec IN emp_cur

  LOOP

    IF emp_rec.sal < 2000 THEN

      UPDATE emp SET sal = sal * 1.1 WHERE emp_rec.rid;

    END IF ;

  END LOOP ;

END ;

/

6. 조합 데이터 유형

※ PL/SQL Record

DECLARE

 TYPE emp_rec_typ IS RECORD              --레코드 구조체. 필드는 둘 이상.

 ( ename VARCHAR2(10),

      sal emp.sal%TYPE,

      job emp.job%TYPE := 'NONE' ) ;

  emp_rec   EMP_REC_TYP ;

BEGIN

 SELECT ename, sal, job INTO emp_rec

 FROM emp

 WHERE empno = 7788 ;

END ;

/

PL/SQL procedure successfully completed.

※ %ROWTYPE 사용

DECLARE

  emp_rec emp%ROWTYPE ; --테이블 emp의 이름 및 유형을 가져온다.

BEGIN

  SELECT * INTO emp_rec FROM emp WHERE empno = 7788 ;

END ;

/

PL/SQL procedure successfully completed.

DECLARE

  emp_rec emp%ROWTYPE ;  --테이블,뷰이름%ROWTYPE

BEGIN

  SELECT * INTO emp_rec FROM emp WHERE empno = 7788 ;

  INSERT INTO copy_emp VALUES emp_rec ;

  SELECT * INTO emp_rec FROM emp WHERE empno = 7782 ;

  emp_rec.sal      := emp_rec.sal * 1.2 ;

  emp_rec.hiredate := SYSDATE ;

  UPDATE copy_emp SET ROW = emp_rec WHERE empno = 7782 ;

END ;

  /

PL/SQL

PROCEDURE successfully completed.

#추가해야함.

1.IF 문

ex1)

SET SERVEROUTPUT ON

DECLARE

             v_myage NUMBER := 10 ;

BEGIN

             IF v_myage < 11 THEN

              DBMS_OUTPUT.PUT_LINE(' I am a child ');

             END IF;

END;

/

 I am a child

PL/SQL procedure successfully completed.

ex2)

DECLARE

             v_myage NUMBER := 31 ;

BEGIN

             IF v_myage < 11 THEN

              DBMS_OUTPUT.PUT_LINE(' I am a child ');

             ELSE

              DBMS_OUTPUT.PUT_LINE(' I am not a child ');

             END IF;

END;

/

I am not a child

PL/SQL procedure successfully completed.

Ex3) 초기화가 되지 않은 변수에대한 IF문

DECLARE

             v_myage NUMBER ;

BEGIN

             IF v_myage < 11 THEN

              DBMS_OUTPUT.PUT_LINE(' I am a child ');

             ELSE

              DBMS_OUTPUT.PUT_LINE(' I am not a child ');

             END IF;

END;

/

I am not a child

PL/SQL procedure successfully completed.

-> NULL은 비교가 불가 하므로 ELSE로 넘어가서 I am not a child 출력.

Ex4) ELSIF

DECLARE

             v_myage NUMBER := 31 ;

BEGIN

             IF v_myage < 11 THEN

              DBMS_OUTPUT.PUT_LINE(' I am a child ');

             ELSIF v_myage < 20 THEN

              DBMS_OUTPUT.PUT_LINE(' I am young ');

             ELSIF v_myage < 30 THEN

              DBMS_OUTPUT.PUT_LINE(' I am in my twenties');

             ELSIF v_myage < 40 THEN

               DBMS_OUTPUT.PUT_LINE(' I am in my thirties');

             ELSE

               DBMS_OUTPUT.PUT_LINE(' I am always young ');

END IF;

END;

/

I am in my thirties

PL/SQL procedure successfully completed.

2. CASE 표현식 :  값을 할당할때, 또 다른 명령을 실행시킬때 사용

ex1)값을 할당할 때 

DECLARE

             v_grade CHAR(1) := UPPER('&grade') ;

             v_appraisal VARCHAR2(20) ;

BEGIN

             v_appraisal := CASE v_grade WHEN 'A' THEN 'Excellent'

                                                                   WHEN 'B' THEN 'Very Good'

                                                                   WHEN 'C' THEN 'Good'

                                                                   ELSE 'No such grade'

                                                                   END;

             DBMS_OUTPUT.PUT_LINE ('Grade: '|| v_grade || ' Appraisal ' || v_appraisal);

END;

/

 Enter value for grade: B

old 2: v_grade CHAR(1) := UPPER('&grade') ;

new 2: v_grade CHAR(1) := UPPER('B') ;

 Grade: B Appraisal Very Good

PL/SQL procedure successfully completed.

 ex2)또 다른 명령어를 실행하기 위한 CASE문

DECLARE

             v_sum NUMBER ;

             v_deptno NUMBER := &deptid ;

BEGIN

             CASE v_deptno

             WHEN 10 THEN

                           SELECT SUM(sal) INTO v_sum

                           FROM emp

                           WHERE deptno = 10 ;

             WHEN 20 THEN

                           SELECT SUM(sal) INTO v_sum

                           FROM emp

                           WHERE deptno = 20 ;

             WHEN 30 THEN

                           SELECT SUM(sal) INTO v_sum

                           FROM emp

                           WHERE deptno = 30 ;

             ELSE

                           SELECT SUM(sal) INTO v_sum FROM emp ;

             END CASE ;

DBMS_OUTPUT.PUT_LINE ( v_sum ) ;

END ;

/

 Enter value for deptid: 30

old 3: v_deptno NUMBER := &deptid ;

new 3: v_deptno NUMBER := 30 ;

 9400

PL/SQL procedure successfully completed.

3. Loop 문 : 반복수행

EX1) Basic Loop

SQL> SET SERVEROUTPUT ON

DECLARE

             v_count NUMBER(2) := 1 ;

BEGIN

             LOOP

                           DBMS_OUTPUT.PUT_LINE ('count: '||to_char(v_count)) ;

                           v_count := v_count + 1 ;

                           EXIT WHEN v_count = 4 ;

             END LOOP ;

END;

/

count: 1

count: 2

count: 3

PL/SQL procedure successfully completed.

Ex2) While Loop

BEGIN

 WHILE v_count <= 3 

 LOOP

  DBMS_OUTPUT.PUT_LINE ('count: '||to_char(v_count)) ;

  v_count := v_count + 1 ;

 END LOOP ;

END ;

/

count: 1

count: 2

count: 3

PL/SQL procedure successfully completed.

Ex3) For Loop, 반복 횟수를 알고있을 때 사용

BEGIN

 FOR i IN 1..3 

 LOOP  

  DBMS_OUTPUT.PUT_LINE ('count: '||to_char(i)) ;

 END LOOP ;

END ;

/

count: 1

count: 2

count: 3

PL/SQL procedure successfully completed

Ex4)For  Loop는 암시적으로 카운터가 생긴다.

BEGIN

 FOR i IN 1..3 LOOP

  DBMS_OUTPUT.PUT_LINE ('count: '||to_char(i)) ;

 END LOOP ;

END ;

/

count: 1

count: 2

count: 3

PL/SQL procedure successfully completed

Ex5)  Reverse For Loop 거꾸로연산.

BEGIN

 FOR i IN REVERSE 1..3 

  LOOP

   DBMS_OUTPUT.PUT_LINE ('count: '||to_char(i)) ;

  END LOOP ;

END ;

/

count: 3

count: 2

count: 1

PL/SQL procedure successfully completed.

※ Nested Loops  중첩된 Loop를 빠져나갈때 Exit

DECLARE

  x NUMBER := 3 ;

  y NUMBER ;

BEGIN

  <<OUTER_LOOP>>

  LOOP

    y := 1 ;

    EXIT WHEN x > 5 ;

       <<INNER_LOOP>>

             LOOP

                DBMS_OUTPUT.PUT_LINE ( x || ' * ' || y || ' = ' || x * y ) ;

                y := y + 1 ;

                EXIT WHEN y > 5 ;

             END LOOP INNER_LOOP ;

    x := x + 1 ;

 END LOOP OUTER_LOOP ;

END ;

/

3 * 1 = 3

3 * 2 = 6

3 * 3 = 9

3 * 4 = 12

3 * 5 = 15

4 * 1 = 4

4 * 2 = 8

4 * 3 = 12

4 * 4 = 16

4 * 5 = 20

5 * 1 = 5

5 * 2 = 10

5 * 3 = 15

5 * 4 = 20

5 * 5 = 25

PL/SQL procedure successfully completed.

 DECLARE

x NUMBER := 3 ;

y NUMBER ;

BEGIN

<<OUTER_LOOP>>

LOOP

y := 1 ;

EXIT WHEN x > 5 ;

<<INNER_LOOP>>

LOOP

DBMS_OUTPUT.PUT_LINE ( x || ' * ' || y || ' = ' || x * y ) ;

EXIT OUTER_LOOP WHEN x*y > 15 ;

y := y + 1 ;

EXIT WHEN y > 5 ;

END LOOP INNER_LOOP ;

x := x + 1 ;

END LOOP OUTER_LOOP ;

END ;

/

4. CONTINUE 문 :

11g부터 생긴것. Continue에 만족하는 사항이 생기면, 밑에 있는 것은 싫행하지말고첫번째 시퀀스부터 다시 시작함.

ex1)

SQL> SET SERVEROUTPUT ON

DECLARE

             v_total SIMPLE_INTEGER := 0;

BEGIN

             FOR i IN 1..5 LOOP

              v_total := v_total + i;

              DBMS_OUTPUT.PUT_LINE ('Total is: '|| v_total) ;

              CONTINUE WHEN i > 3 ;

              v_total := v_total + i;

              DBMS_OUTPUT.PUT_LINE ('Out of Loop Total is: '|| v_total);

             END LOOP;

END;

/

 Total is: 1 <= 0 + 1 (i)

Out of Loop Total is: 2 <= 1 + 1 (i)

Total is: 4 <= 2 + 2 (i)

Out of Loop Total is: 6 <= 4 + 2 (i)

Total is: 9 <= 6 + 3 (i)

Out of Loop Total is: 12 <= 9 + 3 (i)

Total is: 16 <= 12 + 4 (i)

Total is: 21 <= 16 + 5 (i)

PL/SQL procedure successfully

ex2)

SQL> SET SERVEROUTPUT ON

DECLARE

  v_total NUMBER := 0;

BEGIN

  <<BeforeTopLoop>>

  FOR i IN 1..5

  LOOP

    v_total := v_total + 1;

    DBMS_OUTPUT.PUT_LINE ('Outer Total is: ' || v_total) ;

    FOR j IN 1..5

    LOOP

      CONTINUE BeforeTopLoop

    WHEN i               + j > 5 ;

      v_total := v_total + 1;

      DBMS_OUTPUT.PUT_LINE ('Inner Total is: ' || v_total) ;

    END LOOP;

  END LOOP;

END ;

/

Outer Total is: 1 <= 0 + 1 (i=1)

Inner Total is: 2 <= 1 + 1 (i=1 , j=1)

Inner Total is: 3 <= 2 + 1 (i=1 , j=2)

Inner Total is: 4 <= 3 + 1 (i=1 , j=3)

Inner Total is: 5 <= 4 + 1 (i=1 , j=4)

Outer Total is: 6 <= 5 + 1 (i=2)

Inner Total is: 7 <= 6 + 1 (i=2 , j=1)

Inner Total is: 8 <= 7 + 1 (i=2 , j=2)

Inner Total is: 9 <= 8 + 1 (i=2 , j=3)

Outer Total is: 10 <= 9 + 1 (i=3)

Inner Total is: 11 <= 10 + 1 (i=3 , j=1)

Inner Total is: 12 <= 11 + 1 (i=3 , j=2)

Outer Total is: 13 <= 12 + 1 (i=4)

Inner Total is: 14 <= 13 + 1 (i=4 , j=1)

Outer Total is: 15 <= 14 + 1 (i=5)

PL/SQL procedure successfully completed.