Midannii's

Chap 3) 오라클 (oracle)

1. Oracle

Oracle
- 오라클 사가 개발한 관계 DBMS
- 가장 높은 시장 점유율과 신뢰성을 지닌 데이터베이스 관리 시스템
- 유닉스, 리눅스, 윈도우 등 대부분의 운영체제를 지원
- PC에서 대형 기종에 이르기까지 다양한 하드웨어와 운영체제 플랫폼을 지원
- 표준 에디션 1(Standard Edition One), 표준 에디션, 엔터프라이즈 에디션(Enterprise Edition) 등 3가지 에디션으로 판매됨
- 개인용 에디션(Personal Edition)은 윈도우 OS 전용으로서, 단일 사용자 개발 및 배포 환경만 지원

개요
- 전자상거래와 데이터 웨어하우징을 위한 고성능의 인터넷 플랫폼을 제공
- 사용자가 객체 관계 데이터베이스는 물론이고, 워드 프로세서 문서, 스프레드 시트 문서, 파워포인트로 작성한 발표 자료, XML, 그래픽스, 비디오 등과 같은 멀티미디어 데이터 타입 등을 관리할 수 있음
- 온라인 트랜잭션 처리(OLTP:Online Transaction Processing), 데이터 웨어하우스, OLAP(Online Analytical Processing), 전자 상거래 등 최근에 등장하고 있는 데이터베이스의 중요한 응용 분야에도 활용할 수 있음

오라클 서버와 인스턴스
- 오라클 서버는 한 오라클 인스턴스와 한 오라클 데이터베이스로 구성됨
- 오라클 인스턴스는 백그라운드 프로세스들과 메모리 구조의 조합
- 사용자가 오라클 서버에 SQL 문을 입력하기 전에 반드시 오라클 인스턴스에 연결되어 있어야 함
- 접속(connection) : 사용자 프로세스와 서버 프로세스 간의 통신 경로
- 세션(session) : 사용자가 오라클 서버로부터 인증될 때부터 시작하여 사용자가 로그아웃을 하거나 비정상적으로 종료될 때까지 지속
특징
- 클라이언트/서버 환경(분산 처리)
- 다양한 플랫폼
- 대규모 데이터베이스
- 다중 동시 데이타베이스 사용자
- 높은 가용성
- 산업 표준
- 높은 보안 관리
- 자동 데이터베이스 회복 및 자동 에러 정정
- XML 등 다양한 데이터 타입 지원

저장구조

오라클 데이터베이스의 물리적인 저장 구조
- 데이터베이스의 물리적인 구조는 데이터베이스 내의 운영체제 파일 (데이터파일(datafile))들의 집합
- 각 데이터파일은 운영체제 블록들로 이루어짐
오라클 데이터베이스의 논리적인 저장 구조
- 오라클 데이터베이스는 적어도 하나의 테이블스페이스를 포함
- 테이블스페이스는 데이터파일이라고 부르는 하나 이상의 파일로 구성됨 테이블스페이스는 시스템 테이블스페이스, 임시 테이블스페이스, 사용자 테이블스페이스 등으로 구분
- 테이블스페이스는 하나 이상의 세그먼트를 포함
- 세그먼트는 특정한 유형의 데이터 구조를 저장하기 위해 할당되는 익스텐트들의 집합
- 익스텐트는 오라클 데이터 블록(운영체제의 데이터 블록이 아님)들로 이루어짐
- 오라클 데이터 블록은 읽기와 쓰기 연산의 가장 작은 단위 데이터파일 내의 데이터를 오라클 데이터 블록들로 관리

2. Oracle 설치 및 실행

3. Oracle 환경설정

Oracle SQL Developer
- 데이터베이스 개발을 위한 그래픽 인터페이스
- SQL과 PL/SQL 구문 실행, SQL 스크립트 실행, PL/SQL 구문 편집 및 디버깅 기능
- 자바를 기반으로 동작하는데, 먼저 JDK가 컴퓨터에 설치되어 있어야 함
- [시작] → [모든 프로그램] → [Oracle – OraDb11g_home1] → [응용 프로그램 개발] → [SQL Developer]를 차례로 클릭
- Oracle SQL Developer 화면
  - 접속 이름: 사용자가 입력하는 로그인 정보를 저장하는 프로파일의 이름
  - 사용자 이름: 접속하려는 데이터베이스에 대한 사용자 계정
  - 비밀번호: 사용자 계정의 비밀번호
  - 롤: SYSDBA(데이터베이스 관리자)를 선택
  - 호스트 이름: 접속하려는 데이터베이스가 동작하고 있는 컴퓨터의 이름
  - 포트: 오라클 리스너 서비스의 포트(기본 1521)

4. PL/SQL

PL/SQL(Procedural Language/SQL)
- PL/SQL은 오라클에서 관계 데이터베이스를 위한 표준 데이터 언어인 SQL에 절차적인 언어의 기능(예를 들어, IF-THEN, WHILE 루프 등)을 추가하여 확장한 언어
- 블록 위주의 언어
- 기본적인 단위는 블록, 프로시저, 함수
기본적인PL/SQL 블록 구조
- 세 개의 섹션으로 구성됨
- begin으로 시작하는 실행절 만 필수이고 나머지는 옵션
- 선언절에서는 변수와 객체들을 선언
- 실행절에서는 변수들을 처리(조작)
- 예외 사항 처리절에서는 실행 중에 발생한 예외나 에러를 처리
- END 및 다른 PL/SQL문에는 문장 끝을 표시하기 위해 세미콜론을 써야 함
- 주석은 여러 행에 걸쳐 쓰려면 /* 과 * / 사이에 입력하고, 한 행에 쓰는 주석은 시작 부분에 –을 표시
```
[DECLARE
  <상수>
  <변수>
  <커서>
  <사용자가 정의하는 예외 사항>]
BEGIN
  <SQL 문 또는 PL/SQL문>
  [<EXCEPTION>
    <예외 사항 처리>]
END;
```
PL/SQL의 제어 구조
- 조건문의 예
```
if <조건> then <PL/SQL문> else <PL/SQL문> end if;
```
- for 반복문의 예
```
for <인덱스 변수> in <범위> loop <PL/SQL문> end loop;
```
- while 반복문의 예 ``` while <조건> loop
<PL/SQL문> end loop; ```
커서(cursor)
- 질의가 다수의 행들을 결과로 반환할 때 커서를 선언해서 질의의 결과의 각 행을 처리하고, 현재 어떤 행을 처리하고 있는지 유지해야 함
- 질의의 결과로 반환되는 행들은 활성 집합(active set)이라고 부르는 영역에 저장됨
- 커서가 가리키는 행이 현재 행

커서를 사용하기 위해 필요한 네 단계
- 블록의 선언절에서 커서를 선언 CURSOR 커서_이름 IS SELECT문;
- 커서를 사용하기 전에 실행절에서 커서를 연다 OPEN 커서_이름;
- 활성 집합에 있는 행을 한 번에 하나씩 차례대로 검색 FETCH 커서_이름 INTO 변수_리스트;
- 질의 수행 결과에 대한 처리를 마치기 위해 커서를 닫음 CLOSE 커서_이름;
갱신할 투플들에 대한 커서 정의
- 커서 선언부에 FOR UPDATE OF절을 포함시키고, 수정할 애트리뷰트들을 함께 명시
  - 예: 아래와 같이 커서를 선언하면 EMPLOYEE 테이블의 TITLE 애트리뷰트를 수정할 수 있음
    EXEC SQL DECLARE title_cursor CURSOR FOR SELECT title FROM employee FOR UPDATE OF title;
- 이제 커설르 열면 투플의 애트리뷰트를 수정할 수 있음
```
UPDATE employee
SET title = :newtitle
WHERE CURRENT OF title_cursor;
```
PL/SQL의 예
- IF문과 FOR 반복문을 포함한 질의의 예
  - 다음 PL/SQL코드는 3번 부서의 사원들의 평균 급여가 2800000원 이상이면 3번 부서에 속한 사원들의 이름과 직책과 급여를 검색하고, 그렇지 않으면 “3번 부서의 평균 급여가 2800000 미만입니다.”라는 메시지를 인쇄
  - dbms_output.put_line은 값을 화면에 출력하는 오라클의 프로시저

set serveroutput on;

DECLARE
  avg_salary NUMBER;
  l_empname VARCHAR2(10);
  l_title VARCHAR2(10);
  l_salary NUMBER;

  -- 커서를 이용하여 employee 정보를 가져온다.
  CURSOR get_employee_rec IS;
    SELECT empname, title, salary
    FROM employee
    WHERE dno = 3;

BEGIN
  SELECT AVG(salary)
  INTO avg_salary
  FROM employee
  WHERE dno = 3;

  IF avg_salary >= 2800000 THEN
    FOR emp_rec IN get_employee_rec --묵시적으로 커서를 open하고 fetch 한다

    LOOP
      l_empname := emp_rec.empname;
      l_title := emp_rec.title;
      l_salary := emp_rec.salary;
    END LOOP --묵시적으로 커서를 close 한다

  ELSE
    dbms_output.put_line('3번 부서의 평균 급여가 2800000 미만입니다.');
  END IF;
END;