oracle sql , pl/sql 설명


 

1. 데이터베이스로부터 자료 검색

2. 단일 행에 대한 함수들

3. 복수 테이블로부터 데이터 조회

4. Group 함수

5. Subqueries

6. SQL*Plus 명령어

7. 변수 사용

8. 테이블 생성

9. Oracle Data Dictionary

10. 데이터 조작(DML)

11. 테이블과 제약조건 변경

12. Sequence 생성

13. 뷰(View) 생성

14. 인덱스 생성

15. Privileges(DCL)

16. SQL과 SQL*Plus 요약

17. SQL*Plus를 이용한 PL/SQL 블록 작성

18. 서브 프로그램을 이용한 모듈화 프로그래밍

19. 간단한 PL/SQL 블록 생성

20. 오라클 데이터베이스와의 인터페이스

21. PL/SQL 블록의 Control Structure

22. Explicit 커서를 이용한 Query 처리

23. Error 처리

24. PL/SQL 요약

 

1. 데이터베이스로부터 자료 검색

 

기본적인 Query문

네 개의 기본적인 절로 구성

•SELECT에는 column명을 적습니다.

•FROM에는 대상 테이블을 적습니다.

•WHERE는 검색조건에 만족하는 행들을 RETURN 하도록 행을 제한합니다.

•ORER BY는 행들을 정렬합니다.

 Syntax

 

SELECT    [DISTINCT] {*,column [alias]….}

FROM      테이블명

[WHERE    Query조건(들)]

[ORDER BY {column,표현식} [ASC|DESC]];

 

 

SQL 명령 작성

명령은 한 개 이상의 라인에 코딩 가능합니다.

판독성을 위해 탭과 인덴트(indent)가 사용될 수 있습니다.

약식표기 및 단어의 분할은 허용되지 않습니다.

명령사용시 대소문자 구별이 불필요합니다.

명령은 SQL 버퍼에 입력됩니다.

 

 

모든 Column, 모든 행 선택

가장 단순한 SELECT 문장은 아래 두 개의 절을 포함하고 있습니다.

•SELECT

asterisk(*)는 모든 column을 지칭한다.

•FROM

SQL>SELECT *

2 FROM   s_dept;

 

 

특정 Column 선택

SQL>SELECT dept_id, last_name, manager_id

2 FROM   s_emp;

•DESCRIBE 명령을 사용하여 column 명을 확인할 수 있습니다.

•SELECT 절에 column 명들을 적습니다.

콤마를 사용하여 column들을 분리합니다.

보기 원하는 순서대로 column 명을 적습니다.

 

 

Default column 레이블(Heading)

레이블 정렬

좌측 정렬 : 날짜 및 문자 데이터

우측 정렬 : 숫자 데이터

레이블은 기본적으로 대문자로 보여집니다.

 

 

산술 표현식

•NUMBER와 DATE 자료형에 대해서 아래 연산자를 사용하여 연산식을 작성 할 수

  있습니다.

더하기 +

빼기   –

곱하기 *

나누기 /

괄호( )를 사용하여 우선 순위 규칙을 변경할 수 있습니다.

 

 

산술 표현식

모든 사원들의 연봉을 Display 합니다.
SQL>SELECT   last_name, salary*12, commission_pct

2 FROM     s_emp;

새로운 column인 (salary * 12)는 단지 Display 용도로만 사용되고 있습니다.

연산자 우선 순위

곱셈 및 나눗셈이 덧셈 및 뺄셈보다 우선합니다.

괄호는 우선 순위 평가를 변경시 또는 문자들을 명확하게 하기 위해 사용가능 합니다.

같은 우선 순위의 연산자들은 왼쪽에서 오른쪽으로 수행됩니다.

 

 

연산자 우선 순위

괄호는 문장이 수행되어지는 순서를 변경합니다.
SQL>SELECT     last_name, salary, 12*salary + 100

2 FROM       s_emp;

                  ・・・・・   Velasquez 2500 30100

SQL>SELECT     last_name, salary, 12*(salary + 100)

2 FROM       s_emp;

                  ・・・・・   Velasquez 2500 31200

 

 

Column Aliases

Column alias는 column의 heading을 새로이 변경합니다.

특히 계산이 유용합니다.

•Column명 바로 뒤에 옵니다.

  – Column명과 alias 사이에 AS 키워드를 사용할 수도 있습니다.

이중 인용부호(Double quotation)는 alias가 공백, 특수문자를 포함할 경우와 대소문자    구분이 필요한 경우 사용됩니다.

 

 

합성(concatenation)연산자

•2개의 수직바(||)에 의해 이루어집니다.

•Column과 문자 또는 다른 column과 연결시킵니다.

문자 표현식의 결과에 의해 새로운 column을 생성합니다.

SQL>SELECT    first_name||last_name

2 FROM      s_emp;

                ・・・・  CarmenVelasquez

 

리터럴 문자(literal character string)

리터럴이란 SELECT 리스트에 포함된 문자, 표현식 또는 숫자입니다.

날짜 및 문자값은 단일 인용부호(singel quotation)로 에워쌉니다.

각 문자 column은 되돌려지는 각 행당 하나씩 출력됩니다.

SQL>SELECT   first_name ||”|| last_name

2           ||’,’|| title “Employees”

3 FROM     s_emp;

 

 

널값 관리

•NULL이란 이용할 수 없는, 할당되지 않은, 알려져 있지 않은 또는 적용 불가한 값

  을 의미합니다

•NULL이란 0나 공백(space)과 같은 것이 아닙니다.

널값을 포함한 산술 표현식 결과는 NULL이 됩니다.

SQL>SELECT   first_name, title

2           salary*commission_pct/100 COMM

3 FROM     s_emp;

 

 

NVL Function

•NULL은 NVL Function을 이용하여 다른 값으로 대치될 수 있습니다.

사용가능한 datatype은 날짜, 문자 및 숫자형입니다.

다음과 같이 쓰여져야 합니다.

– NVL(start_date, ’01-JAN-95′)

– NVL(title, ‘NO Title Yet’)

– NVL(salary, 1000)

SQL>SELECT   first_name, title

2           salary*NVL(commission_pct,0)/100 COMM

3 FROM     s_emp;

 

 

 

중복행

•Oracle7 Server는 Query시에 기본적으로 중복된 행을 모두 Display합니다.
SQL>SELECT name

2 FROM   s_dept;

•SELECT절에서 DISTINCT를 사용하여 중복행을 제거합니다.
SQL>SELECT DISTINCT name

2 FROM   s_dept;

 

 

Multi-Column에 대한 DISTINCT 사용

•DISTINCT는 SELECT 리스트의 모든 column에 적용됩니다.
SQL>SELECT  DISTINCT dept_id, title

2 FROM    s_emp;

•DISTINCT가 Multi-Column에 적용시 결과는 distinct한 column의 조합을 보여줍니다.

 

 

ORDER BY

ORDER BY절을 사용하여 행들을 정렬할 수 있습니다.

•ASC – 올림차순(Default)

•DESC – 내림차순

•ORDER BY절은 SELECT 명령에서 마지막에 위치합니다.

SQL>SELECT    last_name, dept_id, start_date

2 FROM      s_emp

3 ORDER BY last_name;

 

 

ORDER BY

기본적인 정렬 순서는 올림차순입니다.

사용자는 표현식 또는 alias에 의하여 정렬할 수도 있습니다.

널값은 다음과 같이 Display 됩니다.

올림차순시 가장 마지막에 옴

내림차순시 가장 먼저 옴

Multiple Column에 의한 정렬

사용자는 시간절약 위해 나열한 column의 위치를 이용하여 결과를 정렬 할수 있습니다.
SQL>SELECT     last_name, salary*12

2 FROM       s_emp

3 ORDER BY  2;

사용자는 multiple column에 의해 정렬할 수도 있습니다.
SQL>SELECT     last_name, dept_id, salary

2 FROM       s_emp

3 ORDER BY  dept_id, salary DESC;

•ORDER BY 리스트상의 순서는 정렬순서입니다.

•SELECT 리스트상에 없는 column에 의해서도 정렬이 가능합니다.

 

 

선택할 행의 제한(WHERE 조건)

WHERE절을 사용하여 행들을 제한할 수 있습니다.

•WHERE절은 FORM절 다음에 옵니다.

조건은 아래의 것으로 구성됩니다.

– Column명, 표현식, 상수

비교 연산자

문자(Literal)

SQL>SELECT last_name, dept_id, start_date

2 FROM   s_emp

3 WHERE dept_id=42;

 

 

비교 및 논리 연산자

논리 비교 연산자

•SQL 비교 연산자

 

 

 

논리 연산자

 

 

=(같다) >(보다 큰) >=(같거나 큰) <(보다 작은) <=(같거나 작은)

– BETWEEN…AND… (두값 사이)

– IN(list) (리스트의 값중 어느 하나라도 일치)

– LIKE (문자 형태와 일치)

– IS NULL (널값인 경우)

– AND (다중조건 모두가 TRUE시 돌려지는 결과도 TRUE)

– OR (다중조건중 하나의 값이 TRUE시 돌려지는 결과도 TRUE)

– NOT (조건에 반대되는 결과를 되돌려 줌)

부정 표현식

가끔 윈치 않는 행들을 제외하는 것이 더 쉽습니다.

논리 연산자

!=(같지 않는(VAX, UNIX, PC)) ^=(같지 않는(IBM)) <>(같지 않는(모든 운영체제))

NOT colname = (같지 않는)  NOT colname > (~보다 크지 않는)

•SQL연산자

– NOT BETWEEN…AND… (두개의 명시된 값 사이가 아닌)

– NOT IN(list) (명시된 값의 리스트에 없는)

– NOT LIKE  (비교 스트링과 같지 않는)

– IS NOT NULL (널값이 아닌)

 

 

문자 스트링과 날짜

문자 스트링과 날짜는 단일 인용부호(Single quotation)에 의해 둘러 쌓입니다.

숫자값은 인용부호가 필요 없습니다.

문자값은 대소문자 구분이 필요합니다.

기본 날짜 형식은 ‘DD-MON-YY’입니다.

SQL>SELET   firs_name, last_name, title

2 FROM   s_emp

3 WHERE  last_name=’Magee’;

 

 

어떤 범위내의 값에 포함되었는지를 CHECK하기 위해 BETWEEN 연산자를 사용합니다.
SQL>SELECT first_name, last_name, start_date

2 FROM   s_emp

3 WHERE start_date BETWEEN ’09-may-91′

4 AND    ’17-jun-91′

리스트상의 값에 포함되었는지를 CHECK하기 위해 IN을 사용합니다.
SQL>SELECT  id, name, region_id

2 FROM    s_dept

3 WHERE  region_id IN(1,3);

BETWEEN과 IN SQL 연산자 

 

LIKE SQL연산자

여러분은 유효한 Query 스트링값으로 와일드카드를 사용할 때 LIKE 연산기를 사용

  하여야 합니다.

•Query 조건은 리터럴 문자 또는 숫자를 포함할 수 있습니다.

  – “%”는 0개이상의 문자를 의미

  – “_”는 한 개의 문자를 의미

SQL>SELECT last_name

2 FROM   s_emp

3 WHERE last_name LIKE ‘M%’;

 

 

LIKE SQL 연산자

•LIKE 연산자는 BETWEEN 비교연산자와 유사하게 이용됩니다.
SQL>SELECT last_name, start_date

2 FROM   s_emp

3 WHERE start_date LIKE’%91′;

사용자는 “_”, “%”를 합성하여 사용 할 수 있습니다.
SQL>SELECT  last_naem

2 FROM    s_emp

3 WHERE  last_name LIKE ‘_a%’

사용자가 “%” 또는 “_”문자를 검색하고자 할 경우 ESCAPE명령어를 이용합니다.

 

 

IS NULL SQL 연산자

•IS NULL 연산자를 사용해 널값을 Check합니다.

•= 연산자를 사용하지 마십시오.

SQL>SELECT  id, name, credit_rating

2 FROM    s_customer

3 WHERE  seles_rep_id IS NULL;

 

 

 

 

다중 조건

복잡한 Query기준을 사용할 경우

•AND 또는 OR 연산자로 조건들을 합성합니다.

•AND는 두 개의 조건이 모두 참임을 요구합니다.

SQL>SELECT  last_name, salary, dept_id, tile

2 FROM    s_emp

3 WHERE  dept_id = 41

4 AND     title = ‘Stock Clerk’;

•OR는 어느 한조건이라도 참임을 요구합니다.
SQL>SELECT  last_name, salary, dept_id, title

2  FROM   s_emp

3  WHERE dept_id = 41

4  OR      title = ‘Stock Clerk’;

 

 

우선 순위 규칙

 괄호를 사용 하여 우선 순위를 변경 할수 있습니다.

우선순위   연산자

1   모든 비교 연산자

(=, <>, >, >=, <, <=, IN, LIKE,

IS NULL, BETWEEN)

2   AND

3   OR

 

 

SQL*Plus : 개요

테이블 구조를 표시합니다.

•SQL*Plus로부터 SQL을 실행합니다.

•SQL문장들을 편집합니다.

•SQL문장들을 파일에 저장합니다.

저장된 파일을 실행합니다.

파일로부터 명령들을 편집하기 위해 버퍼로 옮깁니다.

도움말을 얻습니다.

테이블 구조 조회

SQL*Plus의 DESCRIBE 명령은 테이블의 구조를 표시 합니다.

(Column명, NOT NULL column, Datatype)

SQL>DESCRIBE s_dpt
•NOT NULL인 column은 반드시 값을 가져야 합니다.

•column Datatype 및 길이(length)

  – NUMBER(p,s) – 최대 P자리와 소숫점 S자리를 갖는 숫자

  – VARCHAR2(s) – 최대 크기가 S인 가변길이 데이터 값

  – DATE – January 1,4712 B.C에서 December 31, 4712 A.D 사이의 날짜와 시간

  – CHAR(s) – 크기가 S인 고정 길이 데이터 값

 

 

SQL*Plus의 편집 명령어

   령    명
A[PPEND]text 텍스트를 현재행 끝에 추가합니다.
C[HANGE]/old/now 현재행의 기존 텍스트를 새 텍스트로 변경합니다.
C[HANGE]/text/ 현재행으로부터 텍스트를 삭제합니다.
CL[EAR]BUFF[ER] SQL 버퍼로부터 모든 라인을 삭제합니다.
DEL 현재 라인을 삭제합니다.
I[NPUT] 라인을 삽입합니다.
L[IST] SQL 버퍼에 있는 모든 내용을 보여줍니다.
L[IST] n n라인의 내용을 보여 줍니다.(n으로 명시)
L[IST] m n m에서 n라인의 내용을 보여줍니다.
R[UN] 버퍼내의 현재SQL 명령을 보여주고 실행합니다.
n n라인으로 현재 위치를 변경시켜줍니다.
n text 라인 n내용을 새로운 텍스트로 대치합니다.
0 text 라인 1전에 하나의 라인을 삽입합니다.

 

 

온라인 도움말

아래 명령에 대한 온라인 도움말을 얻을수 있습니다.

  – SQL*Plus 명령

  – SQL 명령

  – PL/SQL 명령

SQL>HELP SELECT

SQL*Plus의 파일 명령어

   령    명
SAVE filename SQL 버퍼에 있는 현재의 내용을 파일로 저장합니다.
GET filename 이전에 저장된 파일의 내용을 버퍼로 불러냅니다.
START filename 이전에 저장한 명령 파일을 실행합니다.
@filename 이전에 저장한 명령 파일을 실행합니다.( START와 동일)
EDIT 편집기를 실행시켜 버퍼내용을 afiedt.buf로 불리는 파일을 저장합니다.
ED[IT} filename 저장된 파일의 내용을 편집하기 위해 편집기를 실행합니다.
EXIT SQL*Plus를 중단합니다.

 

 

SQL*Plus에 로그인

윈도우 환경인 경우

  – 아이콘을 더블 클릭

  – 사용자 이름과 패스워드를 입력

•Command line 경우

– sqlplus[username[/password[@database]]]

 

 

2. 단일 행에 대한 함수들

 

SQL 함수의 개요

다음과 같은 경우 함수를 이용하십시요.

데이타에 대해 계산을 수행할 경우

각각의 데이터 항목을 변경할 경우

그룹의 결과 출력

•Display 할 날짜형식을 변경할 경우

•Column datatype을 변경할 경우

 

 

2가지의 SQL 함수

단일행 함수

  – 문자

  – 숫자

  – 날짜

  – 변환

복수행 함수

  – 그룹

FUNCTION

SINGLE ROW

MULTI ROW

 

 

단일행 함수

단일행 함수

데이타 값을 조작합니다.

인수(argument)들을 받아들여 한 개의 값을 Return합니다.

각각의 행에 적용됩니다.

행당 하나의 결과를 돌려줍니다.

•Datatype을 바꾸어 줍니다.

중첩(nested)이 가능합니다.

Syntax

 

function_name (column|expression, [arg1, arg2,…])

문자 함수

    수     적
LOWER(column\expression) 알파벳 문자를 소문자로 바꾸어 줍니다.
UPPER(column\expression) 알파벳 문자를 대문자로 바꾸어 줍니다.
INITCAP(column\expression)

 

알파벳 문자값을 각 단어의 첫 자는 대문자로, 모든 다른 문자는 소문자로 바꾸어 줍니다.
CONCAT(column1\expression,

column2\expression)

 번째의 문자값과 두 번째의 문자값을 연결합니다. 연결연산자(||)와 동일함.
SUBSTR(column\expression,m[,n])

 

문자값중에서 m위치에서 n문자길이에 해당되는 문자를 돌려 줍니다.
LENGTH(column\expression) 문자갯수를 값으로 돌려 줍니다.
NVL(column\expression1,

column\expression2)

첫번째 값이 널이면 두번째 값으로 바꾸어 줍니다.

 

 

 

대소문자 전환 함수

문자 스트링에 대한 대소문자를 전환해 줍니다.

•LOWER(‘SQL Course’) → sql course

•UPPER(‘SQL Course’) → SQL COURSE

•INITCAP(‘SQL Course’) → Sql Course

 

 

문자 조작 함수

문자 스트링을 조작합니다.

•CONCAT(‘Good’, ‘String’) → GoodString

•SUBSTR(‘String’, 1, 3) → Str

•LENGTH(‘String’) → 6

 

 

숫자 함수

    수     적
ROUND(column/expression,n)

 

 

Column, Expression의 값을 소수점 n자리까지 반올림한다. 만약 n이 없다면 소숫점은 없어진다. 또한 n값이 음수라면 소숫점의 왼쪽자리수만큼 반올림된다.
TRUNC(column/expression,n)

 

 

Column, Expression의 값을 소수점 n자리까지 절삭한다. 만약 n이 없다면 소숫점은 나타나지 않는다. 또한 n값이 음수라면 소숫점의 왼쪽자리수만큼 절삭한다.
MOD(m,n) m값을 n값으로 나누고 남은 나머지를 Return한다.

ROUND와 TRUNC 함수

•ROUND(45.923,2)  → 45.92

•ROUND(45.923,0)  → 46

•ROUND(45.923,-1) → 50

•TRUNC(45.923,2)  → 45.92

•TRUNC(45.923)   → 45

•TRUNC(45.923,-1) → 40

 

 

MOD 함수

어떠한 값을 나누고 남은 나머지를 구하고자 한다면 MOD함수를 이용한다.

•MOD(1600,300) → 100

 

 

날짜 산술 연산

결과로 날짜 값이 나오게 날짜에 숫자를 더하거나 날짜로부터 숫자를 뺍니다.

두개의 날짜 사이의 일수를 알기 위해 날짜에서 날짜를 뺍니다.

시간을 날짜에 더합니다.

  산   과    명
data + number date 날수를 날짜에 더합니다.
date – number date 날짜에서 날수를 뺍니다.
date – date number of days 다른 하나의 날짜에서 하나의 날짜를 뺍니다.
date + number/24 date 시간을 날짜에 더합니다.

 

 

날짜 함수

    수     적
MONTHS_BETWEEN

(date1. date2)

 

 

date1과 date2 사이의 달수를 찾아줍니다. 결과는 양수 또는 음수가 가능합니다. date1이 date2보다 빠른 날짜면 결과는 양수지만 date2가 date1보다 빠른 날짜면 결과는 음수입니다. 결과의 비정수부분은 월의 부분을 나타냅니다.
ADD_MONTHS(date, n)

 

날짜에 n달을 추가합니다.

n은 정수여야하며 음수가 가능합니다.

    수     적
NEXT_DAY(date, ‘char’)

 

date다음의 해당 주(‘char’)의 다음 요일을 찾아줍니다.

char은 날 또는 문자스트링을 나타내는 요일입니다.

LAST_DAY(date) date를 포함하고 있는 달의 마지막날을 찾아줍니다.
ROUND(date[, ‘fmt’])

 

 

어떤 형식 모델 (fmt) 명시되지 않았을시 자정까지 시간설정과 함께 날짜를 돌려줍니다. 다른 시간을 갖고 있는 날짜 비교시 유용합니다.
TRUNC(date[, ‘fmt’])

 

 

 

어떤 형식 모델 (fmt) 명시되지 않았을시 date에 포함되어 있는 그날의 자정을 찾아줍니다. 만약 fmt=YEAR 이면 date 포함한 해의 첫 번째 날을 찾아줍니다. 이런 함수는 날짜의 시간 부분을 제거하기를 원할시 유용합니다.

 

 

오라클의 날짜 형식

오라클은 날짜를 내부적인 숫자 형식으로 저장합니다.

– Century, year, month, daty, hours, minutes, seconds

•Default Display 형식은 DD-MON-YY입니다.

•SYSDATE는 현재의 날짜와 시간을 돌려주는 함수입니다.

•DUAL은 SYSDATE를 보기 위해 사용하는 Dummy 테이블입니다.

 

 

날짜 함수

•MONTHS_BETWEEN(’01-SEP-95′, ’11-JAN-94′) → 16.66774194

•ADD_MONTHS(’11-JAN-94′,6)                 → ’11-JUL-94′

•NEXT_DAY(’01-SEP-95′,’FRIDAY’)             → ’08-SEP-95′

•LAST_DAY(’01-SEP-95′)                      → ’30-SEP-95′

 

 

날짜 함수

•ROUND(’25-MAY-95′,’MONTH’)         → 01-JUN-95

•ROUND(’25-MAY-95′,’YEAR’)           → 01-JAN-95

•ROUND(’25-MAY-95′)                  → 25-MAY-95

-Rounded to the nearest day

•TRUNC(’25-MAY-95′,’MONTH’)          → 01-MAY-95

•TRUNC(’25-MAY-95′,’YEAR’)            → 01-JAN-95

전환 함수(Conversion Function)

•TO_CHAR은 숫자 및 날짜 스트링을 문자스트링으로 바꾸어 줍니다.

•TO_NUMBER은 숫자를 포함하고 있는 문자스트링을 숫자로 바꾸어 줍니다.

•TO_DATE는 날짜의 문자 스트링을 날짜값으로 바꾸어 줍니다.

전환함수는 많은 요소들로 구성된 형식 모델(FORMAT)을 사용할 수 있습니다.

  수   적
TO_CHAR(number\date,[‘fmt’])

 

숫자 또는 날짜값을 fmt 형식 모델을 사용하여 VARCHAR2 문자 스트링으로 바꾸어 줍니다.
TO_NUMBER(char) 숫자를 포함하고 있는 문자 스트링을 숫자로 전환합니다.
TO_DATE(char,[‘fmt’])

 

 

날짜를 나타내는 문자스트링을 명시한 fmt에 따라 날짜값으로 바꾸어 줍니다. fmt를 생략시 형식은 DD-MON-

YY가 됩니다.

 

 

TO_CHAR함수

Syntax

 

TO_CHAR(date, ‘fmt’)

 형식 모델(FORMAT)은

단일 인용 부호(single quotation)로 에워싸야 하며 대소문자 구분이 있습니다.

어떤 유효한 날짜 형식 요소도 포함 가능합니다.

덧붙여진 공백이나 선행제로를 없애기 위한 fm 형식을 갖고 있습니다.

콤마에 의해 날짜 값과 구분합니다.

 

 

날짜 형식 모델 요소

•YYYY는 숫자로 완전한 년도를 나타냅니다.

•YEAR는 명시한 년도를 문자로 나타냅니다.

•MM은 2자리 숫자 월을 나타냅니다.

•MONTH는 월의 완전한 이름을 나타냅니다.

•DY는 요일의 3자리 약자를 나타냅니다.

•DAY는 완전한 일자를 나타냅니다.

 

날짜 형식 모델 요소

시간 요소는 그 날의 시간 부분을 형식화 합니다.

– HH24:MI:SS AM            →15:45:32 PM

문자 스트링에 이중 인용 부호(Double quotatin)를 에워쌈으로 문자스트링을 추가함니다.

– DD “of” MONTH           →12 of OCTOBER

숫자 접미사는 숫자를 명기합니다.

– ddspth                     →fourteenth

 

 

RR날짜 형식

Current Year

Specified Date RR Format

YY Format
1995

1995

2001

2001

27-COT-95

27-OCT-17

27-OCT-17

27-OCT-95

1995

2017

2017

1995

1995

1917

2017

2095

명시한 두자리 년도

0-49

50-99

  현재 년도의

  두자리 값

0-49 되돌려 받는 날짜는 현재의 세기입니다.

되돌려 받는 날짜는 현재 세기 이전의 세기입니다.

50-99 되돌려 받는 날짜는 현재 세기 이후의 세기입니다.

되돌려 받는 날짜는 현재의 세기입니다.

 

 

날짜형식 사용할 때 TO_CHAR함수

SQL>SELECT  last_name, TO_CHAR(start_date,

2           ‘fmDdspth “of” Month YYYY

3           fmHH:MI:SS AM’) HIREDATE

4 FROM    s_emp

5 WHERE  start_date LIKE ‘%91’;

 

 

 

숫자 사용할 때 TO_CHAR함수

TO_CHAR(number, ‘fmt‘)

TO_CHAR

9

0

$

L

.

,

MI

PR

EEEE

V

B

수를 사용하여 숫자값을 문자로 보여주기 위하여 위의 형식을 사용하십시오.

숫자 위치(9의 수는 display 폭을 결정합니다.)

강제로 영을 Dispaly하게 합니다.

달러 기호를 나타냅니다.

지역 화폐 기호를 나타냅니다.

소숫점을 Display 합니다.

1000단위 표시 기호를 Display 합니다.

오른쪽에 마이너스 기호(음수값)

음수를 괄호로 묶습니다.

과학적 부호표시(형식은 4개의 E를 명시해야 합니다.)

10을 n번 곱합니다. (n=V뒤의 9의 수)

영의 값을 0이 아닌 공백으로 display합니다

 

 

숫자 사용할 때 TO_CHAR함수

SQL>SELECT  ‘order’||TO_CHAR(id)||

2           ‘was filled for a total of’

3           ||TO_CHAR(total, ‘fm$9,999,999′)NOTE

4 FROM    s_ord

5 WHERE   ship_date = ’21-SEP-92’;

결과 출력시 파운드 기호(#)가 있는 스트링이 나타나면 충분한 자릿수를 필요로 한

  다는 의미입니다.

오라클7 서버는 저장된 소숫값을 형식 모델에 명시된 자릿수로 반올림 시켜줍니다.

 

 

TO_NUMBER와 TO_DATE함수

문자 스트링을 TO_NUMBER 함수를 사용하여 숫자 형식으로 전환합니다.
TO_NUMBER(char)
문자 스트링을 TO_DATE 함수를 사용하여 날짜 형식으로 전환합니다.

– TO_DATE(’10 September 1992′,’dd Month YYYY’)

– Use format elements

TO_DATE(char[, ‘fmt’])

단일행 함수의 중첩

단일행 함수들은 여러 레벨에 걸쳐 중첩 사용할 때 가능합니다.

중첩된 함수들은 가장 하위 레벨에서 가장 상위 레벨 순으로 순행됩니다.

 F3        (F2         (F1          (co1, arg1),      arg2),        arg3)

Step1=Reselut1
Step2=Reselut2
Step3=Reselut3

 

 

중첩 함수(Nesting Function)

SQL>SELECT  last_name,

2          NVL(TO_CHAR(manager_id), ‘No Manager’)

3 FROM   s_emp

4 WHERE  manager_id IS NULL;

1. 숫자값을 문자스트링으로 전환하기 위한 내부 함수를 수행합니다.

– Result1 = TO_CHAR(manager_id)

2. 널값을 문자스트링으로 대치하기 위한 외부함수를 수행합니다.

– NVL(Result1, ‘No Manager’)

 

 

중첩 함수(Nesting Function)

SQL>SELECT    TO_CHAR(NEXT_DAY(ADD_MONTHS

2             (date_ordered,6),  ‘FRIDAY’),

3             ‘fmDay, Month ddth, YYYY’)

4             ‘New 6 Month Review”

5 FROM      s_ord

6 ORDER BY date_ordered;

1. 내부 함수를 수행합니다.

– Result1 = ADD_MONTHS(date_ordered, 6)

2. 그 다음 함수를 평가합니다.

– Result2 = NEXT_DAY(Result1, ‘FRIDAY’)

3. 외부 함수를 평가합니다.

– Result3 = TO_CHAR(Result2, ‘fmDay, Month ddth, YYYY’)

3. 복수 테이블로부터 데이터 조회

 

조인 (Join)이란?

조인은 한 개 이상의 테이블에서부터 데이터를 조회시 사용합니다.

행들은 공통된 값, 일반적으로 Primary Key 및 Foreign Key 값을 사용하여 조인합니다.

조인 방법

– Equijoin

– Non-equijoin

– Outer join

– Self join

 

 

Cartesian Product

•Cartesian product는 다음과 같은 경우에 발생됩니다.

조인 조건을 생략한 경우

조인 조건이 잘못된 경우

첫 번째 테이블의 모든 행들이 두 번째 테이블의 모든 행과 조인이 되는 경우

•Cartesian product를 피하기 위해서는 WHERE절에 항상 정확한 조인 조건을 쓰도록 

  하십시오.

 

 

단순한 조인 Query

Syntax

 

SELECT   table.column, table.column

FROM     table1, table2

WHERE   table1.column1 = table2.column2;

•WHERE 절에 조인 조건을 작성합니다.

명확하게 하기 위해 column명 앞에 테이블명을 적습니다.

한 개 이상의 테이블에 같은 column 명이 있을시 테이블 명을 column명 앞에 붙여 

  사용합니다.

 

 

 

테이블명 사용

여러개의 테이블에 존재하는 동일한 column을 선택하기 위해서는 테이블명을 접두사

  로 사용합니다.

테이블명을 접두사로 사용함으로써 성능을 향상시킵니다.

같은 이름을 갖고 있으며, 다른 테이블에 존재하는 column들은 column alias를 사용하

  여 구별합니다.

 

 

테이블 Alias

테이블 alias로 column을 명확히 할 수 있습니다.

현재의 SELECT 문장에 대해서만 유효합니다.

•Alias를 작성한 경우 테이블 이름 대신에 테이블 alias를 사용하여 column 참조시 

  error 발생을 방지합니다.

SQL>SELECT  c.name “Customer Name”,

2           c.region_id “Region ID”,

3           r.name “Region Name”

4 FROM    s_customer c, s_region r

5 WHERE  c.region_id = r.id;

 

 

Non-Equijoin

•Non-equijoin은 테이블의 어떤 column도 join할 테이블의 한 column에 직접적으로 일

  치하지 않을시 사용합니다.

조인조건은 동등( = )이외의 연산자를 갖습니다.

SQL>SELECT  e.ename, e.job, e.sal, s.grade

2 FROM    emp e, salgrade s

3 WHERE  e.sal BETWEEN s.losal AND s.hisal;

 

 

 

 

 

 

Outer Join

정상적으로 조인 조건을 만족하지 못하는 행들을 보기위해 outer join을 사용합니다.

•Outer join 연산자 “( + )”입니다.

조인시킬 값이 없는 조인측에 “( + )”를 위치 시킵니다.

SELECT  table.column, table.column

FROM    table1, table2

WHERE  table1.column( + ) = table2.column;

 

 

Outer join :

모든 고객에 대해 담당 영업사원이 없다할지라도 영업사원 이름과 고객 이름을 

  display 합니다.

SQL>SELECT     e.last_name, e.id, c.name

2 FROM       s_emp e, s_customer c

3 WHERE     e.id( + ) + c.sales_rep_id

4 ORDER BY  e.id

•Outer join 연산자는 표현식의 한 편에만 올 수 있습니다.

•Outer join을 포함하는 조건은

– IN 연산자를 사용할 수 없습니다.

– OR 연산자에 의해 다른 하나의 조건에 연결될 수 없습니다.

 

 

Self Join :

•Self join을 사용하여 한 테이블의 행들을 같은 테이블의 행들과 조인합니다.

같은 테이블에 대해 두 개의 alias를 작성함으로 FROM절에 두 개의 테이블을 사용

  하는 것과 같이 합니다.

SQL>SELECT  worker.last_name||’ works for ‘||

2           manager.last_name

3 FROM    s_emp worker, s_emp manager

4 WHERE  worker.manager_id = manager.id;

 

 

 

 

4. Group 함수

 

그룹 함수

그룹 함수는 그룹당 하나의 결과를 return 합니다.

그룹 함수들은 SELECT 및 HAVING 절에 쓸수 있습니다.

•SELECT 문장상의 GROUP BY절은 행들을 그룹화 합니다.

•HAVING 절은 그룹을 제한합니다.

 

 

SELECT 문장상의 GROUP BY 절과 HAVING절

Syntax

 

SELECT     column, group_function

FROM       table

[WHERE     condition]

[GROUP BY  group_by_expression]

[HAVING     group_condition]

[ORDER BY  column]

•GROUP BY는 행들을 그룹으로 나누어 줍니다.

•HAVING 절은 그룹을 제한해줍니다.

 

 

Group 함수

  수   명
AVG(DISTINCT|ALL|n) 널값을 제외한 n개 행의 평균값
COUNT(DISTINCT|ALL|exper|*)

 

 

행의 개수, expr은 널값이 아닌 것을 평가합니다.

COUNT는 *를 사용하여 중복 및 널값을 갖는 행들도 포함하여 헤아립니다.

MAX(DISTINCT|ALL|expr) 최대값
MAX(DISTINCT|ALL|expr) 최소값
STDDEV(DISTINCT|ALL|n) 널값을 제외한 n의 표준 편차
SUM(DISTINCT|ALL|n) 널값을 제외한 n의 합계
VARIANCE(DISTINCT|ALL|n) 널값을 제외한 n의 분산

Group함수 : 예

SQL>SELECT MIN(last_name), MAX(last_name)

2 FROM   s_emp

SQL>SELECT AVG(salary), MAX(salary),

2          MIN(salary), SUN(salary)

3 FROM   s_emp

4 WHERE UPPER(title) LIKE ‘SALES%’;

숫자 데이터를 저장하고 있는 column에 대해 AVG나 SUM을 사용 할 수 있습니다.

어떤 datatype이라도 MAX은 사용 가능합니다.

 

 

COUNT 함수 : 예

•COUNT( * )는 한 테이블의 행의 갯수를 돌려줍니다.
SQL>SELECT COUNT( * )

2 FROM   s_emp

3 WHERE dep_id  = 31;

•COUNT(expr)는 널값이 아닌 행들의 갯수를 돌려줍니다.
SQL>SELECT COUNT(commission_pct)

2 FROM   s_emp

3 WHERE dept_id = 21;

 

 

Group BY

Syntax

 

SELECT     column, group_function

FROM       table

[WHERE     condition]

[GROUP BY  group_by_expression]

ORDER BY  column];

•Group BY절을 사용하여 한 테이블의 행들을 원하는 그룹으로 나눕니다.

•Column명을 Group함수와 SELECT절에 사용하고자 하는 경우 Group BY뒤에 Column

  명을 추가합니다.

•ORDER BY절을 사용하여 기본 정렬 순서를 바꿉니다.

 

Group BY절이 없는 경우

SQL>SELECT  id, last_name, dept_id DEPARTMENT

2 FROM    s_emp

3 WHERE  dept_id = 41;

 ID    LAST_NAME   DEPARTMENT

󰠏󰠏󰠏󰠏󰠏   󰠏󰠏󰠏󰠏󰠏󰠏󰠏󰠏󰠏󰠏󰠏󰠏󰠏󰠏   󰠏󰠏󰠏󰠏󰠏󰠏󰠏󰠏󰠏󰠏󰠏󰠏󰠏󰠏󰠏󰠏󰠏

2    Ngao                     41

6    Urguhart                  41

16    Maduro                   41

17    Smith                     41

 󰀹 4명의 사원이 부서번호로 

 󰀹 출력되기 때문에 부서번호

 󰀹 41은 4번 나타납니다.

 

 

Group BY

Syntax

 

SELECT     column, group_function

FROM       table

[WHERE     condition]

[GROUP BY group_by_expression]

[ORDER BY column];

•Group By절을 사용하여 한 테이블의 행들을 원하는 그룹으로 나눕니다.

•Column명을 Group함수와 SELECT절에 사용하고자 하는 경우 Group BY뒤에 

  Column명을 추가합니다.

•ORDER BY절을 사용하여 기본 정렬 순서를 바꿉니다.

 

 

Group BY 절의 사용

SQL>SELECT    dept_id, COUNT(*) “Number”

2  FROM      s_emp

3  WHERE    dept_id = 41

4  GROUP BY dept_id;

DEPT_ID   NUMBER

󰠏󰠏󰠏󰠏󰠏󰠏󰠏󰠏󰠏󰠏󰠏󰠏   󰠏󰠏󰠏󰠏󰠏󰠏󰠏󰠏󰠏󰠏󰠏

41          4

 

 

 

󰀹 Group BY절은 WHERE절에서 검색된 각 부서에 대해 하나의 데이터를 display합니다. 그리고 COUNT(*)는 display된 각 부서(그룹)의 직원수를 display합니다.

 

Group BY 절 : 예

각 신용도별 고객수
SQL>SELECT  credit_rating, COUNT( * ) “#Cust”

2 FROM    s_customer

3 WHERE  credit_rating;

직급명과 각 직급당 월급여 학계액
SQL>SELECT    title, SUM(salary) PAYROLL

2 FROM      s_emp

3 WHERE    title NOT LIKE ‘VP%’

4 GROUP BY title

5 ORDER BY SUM(salary)

 

 

Group BY 절 : 예

•Group BY뒤에 오는 column이 반드시 SELECT절에 있어야 되는 것은 아닙니다.

SELECT문 상에서 그룹 함수가 아닌 모든 column들은 Group BY절에 있어야 합니다.

결과는 Group BY 다음의 column이 SELECT절에 쓰일 경우 더욱 더 의미가 있습니다.

SQL>SELECT     title, MAX(salary)

2 FROM       s_emp

3 GROUP BY  title;

 

 

Group함수를 잘못 사용한 Query

•SELECT list상에서 어떠한 column이나 표현식도 그룹 함수가 아닌 경우에는 Group

  BY절에 와야 합니다.

•Group BY절을 올바르게 작성하지 않을시 다음과 같은 error가 발생합니다.

SQL>SELECT   region_id, COUNT(name)

2 FROM     s_dept;

SELECT region_id, COUNT(name)

*

ERROR at line 1;

ORA-00937: not a single-group grup function

 

Group함수를 잘못 사용한 Query

그룹을 제한하기 위해 WHERE절을 사용할 수 없다.

그룹을 제한하기 위해 HAVING절을 사용합니다.

SQL>SELECT     DEPT_ID, avg(salary)

2 FROM       s_emp;

3 WHERE     AVG(salary) > 2000

4 GROUP BY  dept_id

WHERE AVG(salary) > 2000

*

ERROR at line 3;

ORA-00934: group function is not allowed here

 

 

그룹내의 그룹 : 예

•Group BY 다음에 한 개 이상의 column을 나열함으로써 그룹 또는 부그룹에 대한 

  결과를 얻을 수 있습니다.

•Group BY절상의 column 순서에 의해 결과의 기본 순서가 결정됩니다.

SQL>SELECT     dept_id, title, COUNT( * )

2 FROM       s_emp

3 GROUP BY  dept_id, title;

 

 

WHERE절을 사용한 특정행들의 검색

SQL>SELECT  last_name, title

2 FROM    s_emp

3 WHERE  last_name LIKE ‘V%’;

 

 

󰀹 WHERE절

   (행들을 제한합니다.)
LAST_NAME  TITLE

󰠏󰠏󰠏󰠏󰠏󰠏󰠏󰠏󰠏󰠏󰠏󰠏󰠏󰠏󰠏󰠏  󰠏󰠏󰠏󰠏󰠏󰠏󰠏󰠏󰠏󰠏󰠏󰠏󰠏󰠏

Velasquez     President

 

󰀹 WHERE절에서 제한된 특정 사원을    

   display 합니다.

 

 

HAVING절을 사용한 특정 그룹의 검색

SQL>COLUMN   “ANNUAL SALARY” FORMAT $9,999.99

SQL>SELECT    title, 12*AVG(salary) “ANNUAL SALARY”,

2            COUNT(*) “NUMBER OF EMPLOYEES”

3 FROM      s_emp

4 GROUP BY title

   5 HAVING    COUNT(*) > 2;  ← HAVING 절(그룹들을 제한합니다.)

TITLE              ANNUAL SALARY      NUMBER OF EMPLOYEES

󰠏󰠏󰠏󰠏󰠏󰠏󰠏󰠏󰠏󰠏󰠏󰠏󰠏󰠏󰠏󰠏󰠏󰠏󰠏   󰠏󰠏󰠏󰠏󰠏󰠏󰠏󰠏󰠏󰠏󰠏󰠏󰠏󰠏󰠏󰠏󰠏󰠏󰠏󰠏󰠏   󰠏󰠏󰠏󰠏󰠏󰠏󰠏󰠏󰠏󰠏󰠏󰠏󰠏󰠏󰠏󰠏󰠏󰠏󰠏󰠏󰠏󰠏󰠏󰠏󰠏󰠏󰠏󰠏󰠏󰠏󰠏󰠏󰠏󰠏󰠏

Sales Representative       $17,712.00                              5

Stock Clerk                $11,388.0                              10

Warehouse Manager        $14,776.8                               5

HAVING절에서 제한된대로 특정 그룹의 직급들을 display 합니다. 󰀦

 

 

 

HAVING

Syntax

 

SELECT    column, group_function

FROM      table

[WHERE    condition]

[GROUP BY group_by_expression]

[HAVING    group_condition]

[ORDER BY column];

그룹을 제한시 HAVING절을 사용합니다.

– STEP 1 : 행들이 그룹핑됩니다.

– STEP 2 : 그룹함수가 그룹에 적용됩니다.

– STEP 3 : HAVING 조건을 만족하는 그룹들이 display 됩니다.

 

 

 

 

HAVING 절 : 예

“President” 그룹은 조건에 만족하지 않으므로 결과에 나타나지는 않습니다.
SQL>SELECT    title, SUM(salary) PAYROLL

2 FROM      s_emp

3 WHERE    title NOT LIKE ‘VP%’

4 GROUP BY title

5 HAVING    SUM(salary) > 5000

6 ORDER BY SUM(salary);

 

 

HAVING 절 : 예

•SELECT list 상에서 그룹 함수 사용없이 GROUP BY절을 사용할 수 있습니다.

그룹 함수의 결과에 근거한 그룹 제한시 GROUP BY절 및 HAVING절을 사용해야 합

  니다.

SQL>SELECT    dept_id

2 FROM      s_emp

3 GROUP BY dept_id

4 HAVING    SUM(salary) > 4000;

 

5. Subqueries

 

Subquery란?

SELECT Syntax

SELECT…

Main Query  FROM…
 WHERE…

 

 

 

SELECT Syntax

(SELECT…

FROM…

WHERE…);

󰀹 Subquery

 

 

Subqueries

Syntax

 

SELECT   select_list

FROM     table

WHERE   expr operator

(SELECT select_list

FROM  table);

•Subquery는 다른한의 SQL 문장의 절에 nested 된 SELECT 문장입니다.

•Subquery는 main query 이전에 한 번 실행합니다.

•Subquery의 결과는 main outer query에 의해 사용됩니다.

 

 

Subquery Guidelines

•Subquery는 괄호로 묶어야 합니다.

두 종류의 비교 연산자들이 Subquery에 사용됩니다 : 단일 및 복수행

•Subquery는 연산자의 오른쪽에 나타나야 합니다.

•Subquery는 많은 SQL 명령에서 사용 가능합니다.

•Subquery는 ORDER BY절을 포함할 수 없습니다.

 

Nested Subquery의 처리 방법

1. Nested SELECT 문장이 먼저 실행됩니다.

2. 결과가 main query의 조건으로 전달됩니다

Nested query

 

 

Main query

SELECT  dept_id

FROM    s_emp

WHERE  last_name = ‘Biri’;

SELECT  last_name, title

FROM    s_emp

WHERE  dept_id =

43

 

 

단일행 Subquery

SELECT  last_name, title

FROM    s_emp

WHERE  title =

󰀹 사원의 성을 

 display 하기 위한

 SQL문장을 작성합니다.

SELECT  title

FORM    s_emp

WHERE  last_name = ‘Smith’

󰀹 Smith의 직책을 알기

 위한 SQL 문장을

 작성합니다.

SQL> SELECT  last_name, title

2  FROM    s_emp

3  WHERE  title =

4  (SELECT title

5  FROM   s_emp

6  WHERE  last_name = ‘Smith’);

󰀹 두 개의 문장들을 합쳐

SQL이 Smith의 직책을 

결정하게 합니다.

 

 

 

 

 

Subquery의 그룹함수 : 예

AVG 그룹함수는 단일값을 돌려줍니다.
SQL>SELECT  last_name, title, salary

2 FROM    s_emp

3 WHERE  salay <

4          (SELECT AVG(salary)

5           FROM   s_emp);

Subquery 사용시 Error

만약 한 개 이상의 값을 돌려주는 subquery를 단일행 비교 연산자와 사용할 때 error

  가 발생됩니다.

•Error를 정정하기 위해 비교연산자를 복수행 연산자인 IN으로 변경합니다.

SQL>SELECT  last_name, title, dept_id

2 FROM    s_emp

3 WHERE  last_name =

4          (SELECT MIN(last_name)

5           FROM  s_emp

6           GROUP BY dept_id);

ORA-1427 : single-row subquery returns more than one row

 

 

Multiple Row Subquery :

•Multiple Row subquery는 여러개의 행값들을 돌려줍니다.

여러분은 WHERE절에 복수행 연산자, 예를 들면 IN 연산자를 사용해야 합니다.

아래의 경우 subquery의 그룹 함수는 여러개의 값을 돌려줍니다.

SQL>SELECT  last_name, title, dept_id

2 FROM    s_emp

3 WHERE  (dept_id, salary) IN

4          (SELECT dept_id, MIN(salary)

5           FROM  s_emp

6           GROUP BY dept_id);

 

 

Subquery시 HAVING절

•HAVING절에 subquery 사용이 가능합니다.

•Oracle7 Server는 subquery를 먼저 수행합니다.

서버는 main query의 HAVING절에 subquery의 결과를 돌려줍니다.

SQL>SELECT    dept_id, AVG(salary)

2 FROM      s_emp

3 GROUP BY dept_id

4 HAVING    AVG(salary) >

5            (SELECT AVG(salary)

6             FROM  s_emp

7             WHERE dept_id = 32);

6. SQL*Plus 명령어

 

개요

  주    적
Environment SQL 문장의 일반적인 작동에 영향을 줌
Format Query 결과를 형식화
File manipulation 스크립트 파일의 저장, 실행
Execution 버퍼로부터 Oracle7서버로 SQL이나 PL/SQL 명령어를 전송
Edit 버퍼 내의 SQL 명령어를 수정
Interaction 사용자에게 SQL 문장을 생성하고 변수를 전달, 변수값 출력, 화면에 메시지를 출력하게 함
Miscellaneous 데이터베이스 접속, SQL*Plus 환경 조작, column정의 표시를 위한 다양한 명령

 

 

SQL과 SQL*Plus 명령어의 비교

SQL SQL*Plus
데이터를 액세스하기 위해서 오라클 서버와 통신하는 언어 SQL 명령어를 인식하여 서버에 전송

 

미국 국가 표준위원회

(ANSI:American National Standards Institute)의 표준 SQL을 기초로 함

SQL 명령어를 실행하기 위한 오라클 자체의 인터페이스

 

데이터베이스의 데이터와 테이블 정의를 조작 데이터베이스의 값을 조작할 수 없음
하나이상의 행이 SQL*Buffer로 들어감

 

한번에 한 행만 입력: SQL 버퍼에 저장되지 않음
축약될 수 없음 축약 가능
필요한 명령을 실행하기 위해서 종료 문자를 사용 종료 문자가 필요치 않음; 명령어는 즉시 실행됨
포맷팅을 수행하는 함수 사용 데이터를 형식화하는 명령어 사용

 

 

 

 

SQL과 SQL*Plus 명령어의 비교

명령어   명
EDIT [filename[.ext]]

 

지정된 파일의 내용이나 버퍼의 내용을 호스트 운영체제의 문자 편집기로 불러옵니다.
SAV[E] [filename[.ext]]

[REP[LACE]|APP[END]]

 

SQL 버퍼의 내용을 파일에 저장합니다. 기존 파일에 추가하기 위해서 APPEND를 사용합니다. 기존 파일에 중복해서 쓰려면 REPLACE를 사용합니다. 기본적인 파일 확장자는 sql입니다.
STA[RT] [filename[.ext]]

 

 

지정된 파일을 수행합니다. START라는 명령 대신에 @를 사용할 수 있습니다. 파일 확장자가 .sql이 아니면, 파일 확장자를 써야 합니다.
GET filename[.ext]

 

SQL 버퍼에 파일의 내용을 기록합니다. 파일명의 기본적인 확장자는 사용 환경에 따라 .lis이거나 .lst이 될 수 있습니다.
SPO[OL] [filename[.ext]

|OFF|OUT]

파일에 query 결과를 저장합니다. OFF는 스풀 파일을 닫습니다.

 

HOST SQL*Plus를 종료하지 않고도 호스트 운영체제의 명령어를 실행합니다.

 

 

SET 환경 명령어

SET 시스템 변수 값
•SET 명령어는 현재의 세션 환경을 제어합니다.

Login.sql에는 로그인시에 실행되는 표준 SET 명령과 그 외의 SQL*Plus 명령이 들어 있습니다.

•Login.sql을 수정하여 부가적인 SET 명령을 쓸 수 있습니다.

 

 

SET 명령어 변수

SET 변수와 값   명
ARRAY[SIZE] {20|n} 데이터베이스의 데이터 인출 크기 설정
COLSEP {_|text} Column 사이에 출력될 문자를 설정, 기본값은 공백문자 하나임
ECHO OFF|ON 명령어 파일이 수행될 때 SQL*Plus 명령어의 표시 여부를 결정
FEED[BACK] {6|n|OFF|ON} Feedback 설정
HEAD[ING] {OFF|ON} Column의 Heading 출력 여부를 결정
LINE[SIZE] {80|n} 보고서의 라인당 문자의 수를 설정
LONG {80|n} LONG 값을 표시하는 최대 폭을 설정
PAGES[IZE] {24|n} 출력 페이지당 라인의 수를 명시
PAU[SE] {OFF|ON|text} 단말기의 화면 이동을 제어함

보고서 작성

머리글과 바닥글 생성

그룹 사이에 페이지 구분과 라인 넘김 추가

데이타 출력형식 변경

중복 데이터 제거

소계와 총계의 표시

•Column의 heading 변경

 

 

명령어 파일

스크립트 파일(명령어 파일)은 SQL*Plus 명령어와 SQL SELECT 문장으로 되어 

  있습니다.

•SQL*Plus 명령은 명령어의 값을 포맷하고 재설정하기도 합니다.

명령어 파일은 각 SELECT 문장에 대한 특정 SQL*Plus 명령어로 구성됩니다.

 

 

SQL*Plus 포맷 명령어

명령어   적
COL[UMN] [Column 옵션] Column의 형식을 설정
TTI[TLE] [text|OFF|ON] 보고서의 각 페이지 상단에 나타나는 머리글을 명시
BTI[TLE] [text|OFF|ON] 보고서의 각 페이지 하단에 나타나는 바닥글을 명시
BRE[AK] [ON Report-list] 라인 넘김을 써서 중복값을 배제
COMP[UTE] [함수 OF {Column} ON {Column}] 합계를 계산함

 

 

 

COLUMN 명령어

COL[UMN] [{Column|alias} [옵션]]
•CLE[AR] – column의 형식을 지움

•FOR[MAT] 형식 – 형식모델을 이용하여 column의 표시 형식을 변경

•HEA[DING] text – column의 heading 설정

•JUS[TIFY] {정렬} – column의 heading을 왼쪽, 중앙, 또는 오른쪽에 정렬

 

 

COLUMN 명령어 : 예

•Column의 Heading 생성
COL last_name HEADING ‘Employee|Name’ FORMAT A15

COL salary JUSTIFY LEFT FORMAT $99,990.00

•LAST_NAME column에 대한 현재의 설정값을 출력
COLUMN last_name
•LAST_NAME column에 대한 설정값을 지움(설정 해제)
COLUMN last_name CLEAE

 

 

COLUMN 명령 설정값의 출력 및 지움(설정 해제)

명령어   명
COL Column 명시된 column에 대한 현재의 설정값을 출력
COL 모든 column에 대한 현재의 설정값을 출력
COL Column CLE 명시된 column에 대한 설정값을 해제
CLE COL 모든 column에 대한 설정값을 해제

 

 

COLUMN 형식 모델

요소   명 결과
An 문자와 날짜 column에 대해 n만큼의 폭으로 출력 설정 N/A N/A
9 자리수 표현 999999 1234
0 앞에 0을 붙임 909999 01234
$ 달러 부호의 표시 $9999 $1234
L 국내 통화 표시 L9999 L1234
. 소숫점의 위치를 표현 9999.99 1234.00
,  단위 구분자 표시 9,999 1,234

 

 

 

 

TTITLE과 BTITLE 명령어

TTI[TLE]  [[text|변수]] [OFF|ON]
TTITLE의 기본 설정값은

텍스트는 중앙에, 날짜는 왼쪽 끝에, 페이지 번호는 오른쪽 끝에,

수직 막대( | )를 써서 여러 행에 걸쳐 텍스트를 쓸 수 있습니다.

TTITLE  ‘Salary|Report’

 

 

보고서를 출력할 스크립트 파일의 생성

1. SQL SELECT문 생성

2. SELECT문을 스크립트 파일에 저장

3. 스크립트 파일을 편집기에서 OPEn

4. SELECT문 앞에 포맷팅 명령을 추가

5. SELECT문 다음에 “/” 입력

6. SELECT문 이후의 포맷팅 명령 해제

7. 스크립트 파일 저장

8. 스크립트를 실행하기 위해 “START 파일명”을 입력

 

 

BREAK 명령어

BRE[AK]  [ON element [action]]
•BREAK 명령을 써서 보고서를 보기 좋게 함.

  – 공백 추가

  – 중복값 삭제

•Query에 ORDER BY 절 사용

하나의 BREAK 명령만 허용

 

 

COMPUTE 명령어

COMP[UTE] 함수 [LAB[EL] text]

OF   column…

ON   break column

•COMPUTE 명령을 써서 합계와 같은 함수를 수행할 수 있습니다.

계산한 값을 인쇄하기 위해 레이블 정의

COMPUTE 명령 함수

  수   명
AVG 평균값
COU[NT] 널이 아닌 값의 갯수
MAX[IMUM] 최대값
MIN[IMUM] 최소값
STD 표준편차
SUM 값의 합계
VAR[IANCE] 분산값

 

 

COMPUTE 명령 : 예

•REPORT를 포함하여 break column을 COMPUTE 명령 전에 설정해야 합니다.

하나의 COMPUTE 명령에 여러 개의 함수 및 break column을 나열 합니다.

여러 개의 COMPUTE 명령을 작성할 수도 있습니다.

BREAK ON title SKIP 1 ON REPORT

COMPUTE SUM LABEL ‘Subtotal’ –

OF salary ON title

COMPUTE SUM LABEL ‘Grand Total’ –

OF salary ON REPORT

 

 

 

7. 변수 사용

 

치환 변수(Substitution Variable)

일시적으로 값을 저장하기 위해 SQL*Plus의 치환 변수를 사용합니다.

  – 단일 앰퍼센드(&)

  – 이중 앰퍼센드(&&)

  – DEFINE과 ACCEPT 명령

아래와 같은 경우에 변수를 사용합니다.

  – 날짜 범위의 명시

  – 특정 데이터 제한

동적으로 머리글과 바닥글 변경

 

 

단일 앰퍼센드 치환 변수

사용자는 단일 앰퍼센드가 앞에 붙은 치환변수를 써서 동적으로 행을 제한할 수 

  있습니다.

명령이 실행될 때마다 사용자에게 입력값을 요구합니다.

SQL>SELECT  id, last_name, salary

2 FROM    s_emp

3 WHERE  dept_id = &department_number ;

 

Enter value for department_number : 31

 

 

단일 앰퍼센드 치환 변수 표기법

 기 법   명
&user_variable

 

SQL 문장의 변수. 변수가 없으면 SQL*Plus는 사용자가 변수값을 쓰도록 프롬프트를 냅니다. 명령이 실행될때마다 입력값을 요구합니다.
&&user_variable

 

 

SQL*Plus가 세션동안 또는 변수가 재설정되거나 삭제될 때까지 변수와 그 값을 갖고 있다는 것만 제외하면 단일 앰퍼센드와 같은 역할을 합니다. 즉 명령이 실행될 때마다 입력값을 묻지 않습니다.

 

 

 

SET VERIFY 명령

SQL*Plus가 치환변수를 값으로 바꾸기 전후의 값을 출력합니다.
SQL>SET VERIFY ON

SQL>SELECT  id, last_name, salary

2 FROM    s_emp

3 WHERE  dept_id = &department_number ;

 

Enter value for department_number : 31

 

old 3 : WHERE dept_id = &department_number

new 3 : WHERE dept_id = 31

 

 

치환 변수를 이용한 문자 및 날짜값

문자 및 날짜값은 단일 따옴표로 에워싸야 합니다.

사용자가 단일 따옴표를 입력하지 않도록 앰퍼센드와 변수에 단일 인용부호를 표시

  합니다.

SQL>SELECT  id, last_name, salary

2 FROM    s_emp

3 WHERE  title = ‘&job_title’ ;

 

Enter value for job_title : Stock_Clerk

 

 

실행시에 Column명 및 조건절 입력받기

다음을 입력받기 위해 치환변수를 사용합니다.

•WHERE 조건 •ORDER BY 절 •Column명

테이블명 •전체 SELECT 문장

SQL>SELECT  id, &column_name

2 FROM    s_ord

3 WHERE  &condition;

 

Enter value for column_name : total

Enter value for condition : payment_type = ‘CASH’

이중 앰퍼센드 치환 변수

매번 사용자에게 프롬프트하지 않고 변수값을 재사용하려면 이중 앰퍼센드(&&)를 

  사용합니다.

값을 입력하도록 요구하는 프롬프트는 한 번만 보게 됩지다.

•SQL*Plus는 입력된 값을 저장하고 해당 변수 이름을 참조할 때마다 값을 재사용합

  니다.

SQL>SELECT  id, &&column_name

2 FROM    s_ord

3 WHERE  &column_name;

 

Enter value for column_name : total

 

 

사용자 변수 정의

아래 두 개의 SQL*Plus의 명령 중 하나를 써서 변수를 미리 정의합니다.

  – DEFINE : CHAR 데이터 유형의 사용자 변수 생성

  – ACCEPT : 사용자 입력을 읽어 변수에 저장

•SQL*Plus는 이중 앰퍼센드 치환 변수에 대해 DEFINE 명령을 실행합니다.

•DEFINE 명령을 사용할 때 한 개의 공백이 필요하면 공백을 단일 따옴표로 묶어야 합

  니다.

 

 

ACCEPT 명령

사용자로부터 입력값을 받아들일 때 여러분이 원하는 프롬프트를 작성합니다.

•NUMBER 또는 DATE datatype의 변수를 명시적으로 정의합니다.

보안상의 이유로 사용자 입력사항을 숨깁니다.

ACCEPT p_dname PROMPT ‘Provide the department name: ‘

ACCEPT p_salary NUMBER PROMPT ‘Salary amount: ‘

ACCEPT pswd CHAR PROMPT ‘Password: ‘ HIDE

 

 

 

 

UNDELETE 명령

변수는 다음 시점까지 정의된 상태로 존재합니다.

  – UNDELETE 명령을 사용할때 까지.

  – SQL*Plus를 빠져 나올때 까지.

•DEFINE 명령을 써서 변수를 정의합니다.

 

 

변수 값을 스크립트 파일에 전달

•SELECT 문장을 스크립트 파일에 작성합니다.

•SELECT 문장에 &number 표기법을 사용합니다.

명령행에서 파일을 구동할 때 파일이름 다음에 변수값을 명시합니다. 각 매개변수의 

  위치는 중요합니다.

SQL>START my_file value1 value2

 

 

 

8. 테이블 생성

 

데이터 구조

•Oracle7 데이터베이스는 여러 형태의 데이터 구조를 저장할 수 있습니다.

  – Table      데이터 저장

  – View      하나 이상의 테이블에 있는 데이터의 부분집합(가상의 Table)

  – Sequence  Primary Key 값을 생성

  – Index      Query에 대한 성능 향상

데이타베이스 설계단계에서 구조를 정의합니다.

어떤 시점에서도 작성할 수 있습니다.

크기를 미리 정할 필요가 없습니다.

온라인으로 수정할 수 있습니다.

 

 

테이블 생성

Syntax

 

CREATE TABLE [스키마.] 테이블

         (column datatype [DEFAULT 식]

         [column 제약조건],

….

         [테이블제약조건]);

특정 권한이 있어야 합니다.

•CREATE TABLE

•A storage area

 

 

다른 사용자의 테이블 참조(REFERENCE)

제약조건은 동일 데이터베이스의 테이블을 참조해야 합니다.

다른 사용자에게 속하는 테이블은 현 사용자의 스키마에 있지 않습니다.

테이블 이름에 접두어로 소유자의 이름을 사용해야 합니다.

 

 

 

DEFAULT 옵션

•INSERT에 사용될 column에 대한 default 값을 명시합니다.
. . .start_date DATE DEFAULT SYSDATE, . . .
유효 값은 리터럴 값, 식, 또는 SYSDATE나 USER 같은 SQL 함수입니다.

유효하지 않은 값은 다른 테이블의 column 명이나 pseudo column 입니다.

•Default 값의 datatype은 column의 datatype과 일치해야 합니다.

 

 

명명법(Naming Rule)

문자로 시작해야만 하고,

•1-30자 길이로,

이름은 문자 A-Z, a-z, 0-9, _, $, #만을 포함할 수 있습니다.

동일한 사용자가 소유한 다른 객체의 이름과 중복되지 않도록 해야 합니다.

•Oracle7 Server의 Reserved Word는 쓸 수 없습니다.

 

 

Oracle7 Datatype

Datatype   명
VARCHAR2(size)

 

최대길이가 size인 가변길이 문자값

최소길이가 1, 최대길이는 2000

CHAR(size)

 

길이가 size인 고정길이 문자값

기본길이는 1, 최대길이는 255

NUMBER 38자리까지 유효한 부동 소수점 숫자
NUMBER(p, s)

 

1에서 38의 범위 중 p의 자릿수까지 유효한 숫자값

전체 자릿수 p, 소숫점 자릿수 s

DATE B.C. 4712년 1월에서 A.D. 4712년 12월 31일 사이의 일자와 시간
LONG

 

2GB까지의 가변길이 문자값으로 테이블당 한 개의 LONG column만 허용
RAW와 LONG RAW

 

각각 VARCHAR2, LONG과 같지만 이진 데이터를 저장하는 데에 사용

 

 

 

제약 조건(Constraints)

테이블 레벨에 제약조건을 줄 수 있습니다.

참조되어지고 있는 테이블 삭제를 방지

•Oracle7의 제약조건 유형

– NOT NULL  – UNIQUE

– PRIMARY KEY  – FOREIGN KEY

– CHECK

 

 

데이터 무결성 제약조건

제약조건   명
NOT NULL Column이 NULL 값을 가질수 없음을 명시
UNIQUE

 

테이블의 모든 행에서 고유한 값을 가져야만 하는 column이나 column의 조합을 명시
PRIMARY KEY

 

테이블의 각 행을 유일하게 식별지을수 있는 column이나 column 조합을 명시
FOREIGN KEY column과 참조하는 테이블의 column간의 참조 관계를 설정
CHECK 반드시 참이어야 하는 조건을 명시

 

 

제약 조건 Guidelines

제약조건의 이름을 쓰거나 SYS_Cn 형식을 써서 서버가 이름을 생성

제약조건 생성

  – 테이블 생성과 동시

  – 테이블이 생성된 후

•column이나 테이블 레벨에서 제약조건 정의

 

 

제약 조건

•Column 레벨
column [CONSTRAINT 제약조건이름] 제약조건 유형,
테이블 레벨
column, . . .

   [CONSTRAINT 제약조건이름] 제약조건 유형

(column, . . .),

NOT NULL 제약 조건

•Column에 대해 널 값이 허용되지 않도록 보장

•Column 레벨에서 정의

) CREATE TABLE friend…

phone     VARCHAR2(15) NOT NULL, …

last_name  VARCHAR2(25)

CONSTRAINT friend_last_name_nn NOT NULL, …

 

 

UNIQUE 제약 조건

테이블에서 이 키에 대해 같은 값을 갖는 행이 하나만 존재하도록 column이나

  Column의 조합을 지정

•Unique Key가 단일 Column을 기초로 한다면 null 값을 허용

테이블이나 Column 레벨에서 정의

•UNIQUE 인덱스를 자동으로 생성

…phone   VARCHAR2(10)

CONSTRAINT s_emp_phone_uk UNIQUE,…

 

 

PRIMARY KEY 제약 조건

테이블에 대한 Primary Key를 생성 ; 테이블에 단 하나의 primary key만 허용

•NULL 값을 허용하지 않음(composite 일지라도)

테이블이나 Column 레벨에서 정의

•UNIQUE 인덱스를 자동으로 생성

…id       NUMBER(7)

CONSTRAINT s_emp_id_pk PRIMARY KEY, …

 

 

FOREIGN KEY 제약 조건

•Column이나 Column의 조합을 Foreign Key로 지정

동일 테이블의 primary key나 unique Key 간의 또는 테이블 간의 관계를 설정

테이블이나 Column 레벨에서 정의

•Parent 테이블에 있는 값과 일치하거나 null이 되어야 합니다.

…dept_id   NUMBER(7)

CONSTRAINT s_emp_dept_id_fk

REFERENCES s_dept(id),…

FOREIGN KEY 제약 조건 키워드

•FOREIGN KEY

  – 테이블 레벨에서 child 테이블에 column을 정의(참조할 column)

•REFERENCES

  – 참조될 테이블 column을 정의

 

 

CHECK 제약 조건

각 행이 만족해야 하는 조건을 정의

허용되지 않는 식 :

  – CURRVAL, NEXTVAL, LEVEL, ROWNUM pseudo column에 대한 참조

  – SYSDATE, UID, USER, USERENV 함수 이용

  – 다른 행의 다른 값을 참조하는 query

테이블이나 column 레벨에서 정의

 

 

테이블 챠트를 기초로한 테이블 작성

1. 스크립트 파일을 작성. CREATE TABLE 테이블 이름을 적습니다.

2. Column명, Datatype, 길이를 적습니다.

3. Column 레벨 제약조건에 NOT NULL 제약조건을 적습니다.

  PRIMARY KEY 제약조건 column은 제외

4. PRIMARY KEY 제약조건을 적습니다.

5. UNIQUE, CHECK, FOREIGN KEY 제약조건을 적습니다.

6. 스크립트 파일을 저장하고 실행합니다.

 

 

테이블 챠트 : S_DEPT

Column ID NAME REGION_ID
 유형 PK FK
NN/UK NN, U NN, U2 U2
FK 테이블 REGION
FK column ID
Datatype NUMBER VARCHAR NUMBER
길이 7 25 7
Sample Data

 

 

 

10

31

32

33

Finance

Sales

Sales

Sales

1

1

2

3

테이블 생성 : 예

SQL>CREATE TABLE s_emp

2 (id           NUMBER(7)

3 CONSTRAINT s_emp_id_pk PRIMARY KEY,

4 last_name     VARCHAR(25)

5 CONSTRAINT s_emp_name_nn NOT NULL,

   6 first_name     VARCHAR2(25),   

7 userid         VARCHAR2(8)

8 CONSTRAINT s_emp_userid_nn NOT NULL

9 CONSTRAINT s_emp_userid_uk UNIQUE,

10 start_date     DATE DEFAULT SYSDATE,

11 comments     VARCHAR2(25),

12 manager_id    NUMBER(7),

13 title           VARCHAR2(25),

14 dept_id        NUMBER(7)

15 CONSTRAINT s_emp_dept_id_fk REFERENCES s_dept (id),

16 salary         NUMBER(11,2),

17 commission_pct NUMBER(4,2)

18 CONSTRAINT s_emp_commission_pct_ck CHECK

19 (commission_pct IN(10,12.5,15,17.5,20)));

 

테이블 챠트 : S_EMP

Column ID LAST_NAME USERID START_DATE COMMISSION_PCT DEPT_ID
 유형 PK FK
NN/UK NN, U NN NN, U
FK 테이블 S_DEPT
FK column ID
Datatype NUMBER VARCHAR VARCHAR DATE NUMBER NUMBER
길이 7 25 8 6 7

 

테이블 제약 조건

제약조건   명
S_EMP_ID_PK ID column을 테이블의 P.K로 설정

 제약조건은 고유한 값이 입력되도록 합니다.

S_EMP_LAST_NAME_NN LAST_NAME column이 null값을 갖지 못하도록 합니다.
S_EMP_USERID_NN USERID column이 null값을 갖지 못하도록 합니다.
S_EMP_USERID_UK USERID column의 값이 중복되지 않도록 합니다.
S_EMP_DEPTID_UK S_DEPT 테이블에 없는 부서 번호가 S_EMP 테이블에 저장되지 않게 합니다.
S_EMP_COMMISSION_PCT_CK commission_pct column의 허용값을 제한합니다.

테이블 생성 확인

SQL*Plus DESCRIBE 명령을 이용하여 테이블이 잘 생성되었는지 확인합니다.
SQL>DESCRIBE s_emp

 

Name            Null?              Type

  󰠏󰠏󰠏󰠏󰠏󰠏󰠏󰠏󰠏󰠏󰠏󰠏󰠏󰠏        󰠏󰠏󰠏󰠏󰠏󰠏󰠏󰠏󰠏󰠏󰠏󰠏󰠏󰠏󰠏      󰠏󰠏󰠏󰠏󰠏󰠏󰠏󰠏󰠏󰠏󰠏󰠏󰠏󰠏󰠏󰠏󰠏󰠏󰠏

ID                NOT NULL        NUMBER(7)

LAST_NAME       NOT NULL        VARCHAR(25)

FIRST_NAME                        VARCHAR(25)

USERID           NOT NULL        VARCHAR(8)

START_DATE                        DATE

. . .

 

 

 

 

 

 

 

 

 

9. Oracle Data Dictionary

 

Oracle7 데이터베이스의 테이블

사용자 테이블

  – 사용자가 만들고 사용하는 테이블

  – 사용자가 입력한 정보 저장

데이타 사전(Data Dictionary)

  – Oracle7 Server가 만들고 관리하는 테이블들의 집합

  – 데이터베이스에 대한 정보를 저장

 

 

데이터 사전(Data Dictionary)

데이타베이스가 생성될 때에 생성

•Oracle7 Server가 관리

데이타 사전 뷰에 대한 query를 이용하여 정보 획득

데이타 사전에 저장된 정보

  – Oracle7 Server에 Login 할 수 있는 USER명

  – 사용자에게 허가된 권한

  – 데이터베이스 객체명

  – 테이블 제약조건

  – Auditing 정보

 

 

데이터 사전에 대한 Query

•4가지 범주의 뷰

– USER_

– ALL_

– DBA_

– V$

 

기타 뷰

– DICTIONARY

– IND

 

사용자 소유의 객체에 관한 정보를 저장

사용자에게 액세스가 허용된 객체에 관한 정보

DBA 권한을 가진 사용자가 액세스 할수 있는 정보를 저장

서버의 성능과 Locking에 관한 정보

일반적으로 DBA에게만 허용

 

모든 데이터 사전의 테이블, 뷰, synonym을 display

USER_INDEXES의 synonym

 

데이터 사전에 대한 Query : 예

사용자가 액세스할 수 있는 모든 dictionary 뷰를 display
SQL>SELECT *

2 FROM DICTIONARY;

•USER_OBJECT 뷰의 구조를 display
SQL>DESC user_objects
소유한 모든 테이블을 display
SQL>SELECT objects_name

2 FROM   user_objects

3 WHERE  objects_type = ‘TABLE’ ;

•DISTINCT 키워드를 써서 사용자 소유의 객체 유형(object type) 보기
SQL>SELECT DISTINCT objects_type

2 FROM   user_objects;

•DICTIONARY 테이블의 COMMENTS column에서 특정 주제에 대한 데이터 사전 

  테이블에 대해 검색

SQL>SELECT *

2 FROM   dictionary

3 WHERE  LOWER(comments) LIKE ‘%grant%’;

 

 

제약조건 관련 뷰

모든 제약조건 정의와 이름을 보고자 한다면 USER_CONSTRAINTS를 참조하십시요.

) •S_EMP 테이블의 제약조건 검사

SQL>SELECT constraint_name, constraint_type,

2          search_condition, r_constraint_name

3 FROM   user_constraints

4 WHERE  table_name = ‘S_EMP’;

 

 

제약조건 관련된 Column에 대한 뷰

•USER_CONS_COLUMNS 뷰에서 제약조건 이름과 관련된 column의 정보를 확인 할 

  수 있습니다.

이 뷰는 제약조건 명을 시스템이 정한 경우에 특히 유용합니다.

SQL>SELECT constraint_name, column_name

2 FROM   user_cons_columns

3 WHERE  table_name = ‘S_EMP’;

10. 데이터 조작(DML)

 

데이터 조작 명령어와 트랜잭션 제어 명령어

  령   명
INSERT 테이블에 새로운 행 추가
UPDATE 테이블의 행 변경
DELETE 테이블의 행 삭제
COMMIT 저장되지 않은 모든 변경사항을 DB에 저장
SAVEPOINT Savepoint 설정
ROLLBACK 저장되지 않은 모든 변경사항을 취소

 

 

테이블에 새로운 행 삽입(INSERT)

•INSERT문을 써서 테이블에 새로운 행을 추가합니다.
INSERT INTO 테이블 [(column [, column…])]

VALUES      (값 [값…]);

한 번에 한 행만 삽입됩니다.

 

 

새로운 행 삽입 : 예

모든 column에 대해 값을 갖는 새로운 행을 삽입합니다.

•INSERT 절의 column은 선택적으로 기입할 수 있습니다.

SQL>INSERT INTO s_dept

2 VALUES      (11, ‘Finance’, 2);

문자와 날짜 값은 단일 따옴표(‘ ‘)로 둘러쌉니다.

 

 

NULL 값이 있는 새로운 행 삽입

Implicit한 방법

•column list에서 column명을 제외합니다.

SQL>INSERT INTO s_dept(id, name)

2 VALUES      (12, ‘MIS’);

Explicit한 방법

•VALUES 목록에 공 문자열(‘ ‘)이나 NULL 키워드를 기입합니다.

SQL>INSERT INTO s_dept

2 VALUES      (13, ‘Administration’, NULL);

특정한 값 삽입

•USER 함수는 현재 사용자의 이름을 return합니다.

•SYSDATE 함수는 현재 날짜와 시간을 return합니다.

SQL>INSERT INTO s_emp(id, first_name, last_name, userid, salary, start_date)

2 VALUES      (26, ‘Donna’, ‘Smith’, USER, NULL, SYSDATE);

 

 

삽입 내용 확인

테이블에 행이 추가되었는지 확인하십시요.
SQL>SELECT id, last_name, first_name, userid, start_date

2 FROM   s_emp

3 WHERE  id = 26;

ID  LAST_NAME  FIRST_NAME  USERID  START_DAT

 󰠏󰠏󰠏󰠏  󰠏󰠏󰠏󰠏󰠏󰠏󰠏󰠏󰠏󰠏󰠏󰠏󰠏󰠏  󰠏󰠏󰠏󰠏󰠏󰠏󰠏󰠏󰠏󰠏󰠏󰠏󰠏󰠏󰠏  󰠏󰠏󰠏󰠏󰠏󰠏󰠏󰠏󰠏  󰠏󰠏󰠏󰠏󰠏󰠏󰠏󰠏󰠏󰠏󰠏󰠏󰠏󰠏

26  Smith         Donna        SFCL26   01-JAN-96

 

 

특정 날짜와 시간 값 입력

•TO_DATE 함수

  – 특정 날짜와 시간 값을 삽입하기 위해 to_date함수를 사용합니다.

SQL>INSERT INTO s_emp(id, first_name, last_name, userid, salary, start_date)

2 VALUES (26, ‘Donna’, ‘Smith’, USER, NULL, TO_DATE(’01-JAN-96 08:00′,

3 TO_DATE(’01-JAN-96 08:00′, ‘DD-MON-YY HH:MI));

•Default

  – 세기는 현재의 세기가 입력됩니다.

  – 시간은 자정으로 입력됩니다.

 

 

치환 변수를 이용한 행 입력

SQL*Plus 치환 변수를 이용한 행 입력
SQL>INSERT INTO s_emp(id, first_name, last_name, userid, salary, start_date)

2 VALUES      (&employee_number, ‘&&first_name’, ‘&last_name’,

3 LOWER(CONCAT(SUBSTR(‘&first_name’,1,1)

4 SUBSTR(‘&last_name’,1,7))), NULL, SYSDATE);

사용자 정의 프롬프트

SQL*Plus ACCEPT 명령은 변수에 값을 저장합니다.

SQL*Plus PROMPT 명령은 사용자가 정의한 텍스트를 출력합니다.

SET ECHO OFF

ACCEPT employee_number-

PROMPT ‘Please provide the employee number: ‘

ACCEPT first_name-

PROMPT ‘Please enter the first name: ‘

ACCEPT last_name-

PROMPT ‘Please enter the employee last name: ‘

INSERT …

VALUES    (&employee_number, ‘&&first_name’, …

/

SET ECHO ON

 

 

테이블의 행 변경(UPDATE)

•UPDATE 명령을 써서 행의 값을 변경 할 수 있습니다.
Syntax

 

UPDATE  [테이블]

SET      column = 값 [, column = 값…]

[WHERE  조건];

•WHERE 절을 생략하면 해당 column의 모든 data값이 변경됩니다.

 

 

행의 변경 : 예

사원 번호가 2인 직원의 부서를 10번 부서로 변경합니다.
SQL>UPDATE s_emp

2 SET     dept_id = 10

3 WHERE  id = 2;

사원 번호가 1인 직원의 부서를 32번 부서로 변경하고 급여를 2550으로 변경합니다.
SQL>UPDATE s_emp

2 SET     dept_id = 32, salary = 2550

3 WHERE  id = 1;

 

테이블의 모든 행 변경

WHERE 절을 추가하지 않으면 테이블의 모든 행이 변경됩니다.
SQL>UPDATE s_emp

2 SET     commission_pct = 10;

 

 

행의 변경 : 무결성 제약조건 ERROR

부서 번호 60은 부서 테이블에 없기 때문에 error가 발생합니다.
SQL>UPDATE s_emp

2 SET     dept_id = 60

3 WHERE  dept_id = 10;

 

update s_emp

*

ERROR at line 1:

ORA-02291:integrity constraint

(USR.S_EMP_DEPT_ID_FK) violated – parent key not found

 

 

테이블의 행 삭제(DELETE)

•DELETE 명령을 이용하여 행을 삭제합니다.
Syntax

 

DELETE FROM 테이블

[WHERE   조건];

•1996년 1월 이후에 입사한 사원에 대한 모든 정보를 삭제하십시오.
SQL>DELETE FROM s_emp

2 WHERE        start_date > TO_DATE(‘01.01.1996′,’DD.MM.YYYY’);

 

 

 삭제 : 예

•WHERE 절을 배제하여 테이블의 모든 행을 삭제합니다.
SQL>DELETE FROM  test;
삭제된 사항을 확인하십시요.
SQL>SELECT *

2 FROM   test;

 삭제 : 무결성 제약조건 ERROR

다른 테이블에서 참조되어지는 primary key를 포함하고 있는 행을 삭제하려고 하면 무결성 제약조건 error가 발생할 것입니다.
SQL>DELETE FROM s_dept

2 WHERE  region_id = 1;

 

ERROR at line 1:

ORA-02292:integrity constraint

(USR.S_EMP_DEPT_ID_FK) violated – child record found

 

 

데이터베이스 트랜잭션

다음 중 하나를 포함

  – 데이타조작(DML) 명령어들

  – 하나의 데이타정의(DDL) 명령

  – 하나의 데이타제어(DCL) 명령

최초의 실행가능한 SQL 명령이 실행되면 시작

다음 사건 중 하나로 종료

  – COMMIT 이나 ROLLBACK

  – DDL이나 DCL 명령 실행(Automatic Commit)

  – Error, 종료 또는 System Crash

 

 

COMMIT과 ROLLBACK의 장점

데이타 무결성 보장

영구적인 변경 전에 데이터의 변경사항 확인

논리적으로 연관된 작업을 그룹핑

 

 

Implicit 트랜잭션 처리

자동 COMMIT은 다음 상황 하에서 발생합니다.

  – CREATE 같은 DDL 명령 실행

  – GRANT 같은 DCL 명령 실행

  – 명시적으로 COMMIT이나 ROLLBACK을 하지 않고 SQL*Plus에서 정상적인 종료

자동 ROLLBACK은 SQL*Plus에서 비정상적인 종료를 하거나 시스템 실패시 발생합니다.

COMMIT이나 ROLLBACK 이전의 데이터 상태

데이타의 이전 상태는 복구될 수 있습니다.

현재 사용자는 테이블에 대한 query를 통해 데이터 조작의 결과를 볼 수 있습니다.

다른 사용자는 현재 사용자의 데이터 조작 결과를 볼 수 없습니다. Oracle7은 각 사용

  자가 최종 COMMIT시 있던 데이터를 볼 수 있도록 Read Consistency를 제공합니다.

변경된 내용은 Lock이 설정되어 있어 다른 USER가 변경할 수 없도록 합니다.

 

 

COMMIT 후의 데이터 상태

데이타에 대한 변경사항은 데이터베이스에 반영됩니다.

이전 데이터는 영원히 잃버리게 됩니다.

모든 사용자는 결과를 볼 수 있습니다.

관련된 행에 대한 Lock은 풀리고 다른 사용자들이 행을 조작할 수 있게 됩니다.

 

 

데이터의 커밋 : 예

•Education이라는 부서를 만드십시요.
SQL>INSERT INTO s_dept(id, name, region_id)

2 VALUES      (54, ‘Education’, 1);

한 명의 사원을 추가사힙시요.
SQL>UPDATE s_emp

2 SET     dept_id =54

3 WHERE  id = 2;

변경된 사항을 커밋하십시요.
SQL>COMMIT;

 

 

ROLLBACK 후의 데이터 상태

미결 중인 모든 변경사항이 무시(취소)됩니다.

이전 상태로 복구됩니다.

관련된 행에 대한 Lock이 풀립니다.

SQL>DELETE FROM test;

 

SQL>ROLLBACK;

Rollback complete.

SAVEPOINT를 이용한 ROLLBACK

•SAVEPOINT 명령을 써서 현재의 트랜잭션에 savepoint를 생성합니다.

•ROLLBACK TO SAVEPOINT 명령을 써서 특정 savepoint로 ROLLBACK 합니다.

SQL>UPDATE…

SQL>SAVEPOINT update_done;

Savepoint created.

SQL>INSERT…

SQL>ROLLBACK TO update_done;

Rollback complete.

 

 

문장 레벨의 롤백

단일 DML문이 실행 중에 실패하면 실패한 문장을 롤백됩니다.

다른 모든 변경사항은 그대로 유지됩니다.

•COMMIT이나 ROLLBACK문을 실행하여 명시적으로 트랜잭션을 종료해야 합니다.

11. 테이블과 제약조건 변경

 

개요

•ALTER TABLE 명령으로 할 수 있는 일

  – Column의 추가와 변경

  – 제약조건의 추가나 삭제

  – 제약조건의 enable, disable

•DROP TABLE 명령은 행과 테이블을 삭제합니다.

테이블에 영향을 주는 다른 명령은 RENAME, TRUNCATE, COMMENT입니다.

•DDL 명령을 사용하면 자동 커밋이 발생합니다.

 

 

Column 추가

새로운 column의 추가

새로운 column에 대한 default 값을 정의

값을 갖고 있어야만 하는 column을 명시 (NOT NULL 설정)

•ADD절이 있는 ALTER TABLE 명령을 써서 테이블에 column을 추가

Syntax

 

ALTER TABLE 테이블 명

ADD (column datatype [DEFAULT 식] [NOT NULL] [, column datatype] …);

기존의 column을 추가하거나 수정할 수 있지만 테이블에서 삭제할 수는 없습니다.

 

 

Column 추가 : 예

•S_REGION 테이블에 COMMENTS column을 추가
SQL>ALTER TABLE s_region

2 ADD    (comments VARCHAR2(255));

 

Table altered.

새로운 column은 테이블의 마지막 column이 됩니다.

 

 

 

Column 변경

•Column의 datatype, 크기, default 값, NOT NULL column 제약조건을 변경합니다.
Syntax

 

ALTER TABLE 테이블 명

MODIFY (column datatype [DEFAULT 식] [NOT NULL] [, column datatype] …);

Guidelines

•Column의 크기를 늘입니다.

•Column이 null 값만 가지고 있거나 테이블에 아무 행도 없으면 column의 폭을 줄일

  수 있습니다.

•Column의 null 값만을 가지고 있으면 datatype을 변경할 수 있습니다.

•Column의 default 값을 바꾸면 테이블에 대한 이후 발생하는 행 삽입에만 영향을 미

  치게 됩니다.

•Column에 null 값이 없을 경우에만 NOT NULL 제약조건을 추가할 수 있습니다.

 

 

Column 변경

•Column이 null 값만을 가지고 있으면 datatype을 변경

)

•S_EMP 테이블의 TITLE column의 최대 길이를 50 문자로 확장

SQL>ALTER TABLE s_emp

2 MODIFY    (title VARCHAR2(50));

 

Table altered.

 

 

제약조건 추가

Syntax

 

SQL>ALTER TABLE 테이블명

   2 ADD   [(CONSTRAINT 제약조건] type (column);

제약조건을 수정하는 것이 아닌 추가나 삭제

제약조건의 Enable이나 Disable

•MODIFY절을 써서 NOT NULL 제약조건 추가

제약조건 추가 : 예

S_EMP 테이블 ID column이 MANAGER_ID column을 참조하도록 S_EMP 테이블에 foreign key 제약조건을 추가합니다.
SQL>ALTER TABLE s_emp

2 ADD   CONSTRAINT s_emp_manager_id_fk

3 FOREIGN KEY (manager_id)

4 REFERENCES s_emp (id);

 

 

제약조건 삭제 : 예

•S_EMP 테이블에서 관리자 제약조건을 삭제합니다.
SQL>ALTER TABLE s_emp

2 DROP CONSTRAINT s_emp_manager_id_fk

•S_DEPT 테이블의 PRIMARY KEY 제약조건을 삭제하고 S_EMP.DEPT_ID column의 

  FOREIGN KEY 제약조건을 삭제합니다.

SQL>ALTER TABLE s_dept

2 DROP PRIMARY KEY CASCADE;

 

 

제약조건 ENABLE

•ENABLE 절을 써서 테이블의 disable된 제약조건을 enable시킬 수 있습니다.
SQL>ALTER TABLE s_dept

2 ENABLE CONSTRAINT s_dept_id_fk

•UNIQUE나 PRIMARY KEY 제약조건을 enable 시키면 UNIQUE나 PRIMARY KEY 인

  덱스를 자동으로 생성됩니다.

 

 

제약조건 DISABLE

무결성 제약조건을 disable 시키기 위해 ALTER TABLE 명령의 DISABLE를 이용합니다.

종속적인 무결성 제약조건을 disable 시키기 위해 CASCADE 옵션을 적용합니다.

SQL>ALTER TABLE s_dept

2 DISABLE CONSTRAINT s_dept_id_fk CASCADE;

 

테이블 삭제

DROP TABLE 테이블 명 [CASCADE CONSTRAINT];
모든 데이터 테이블에서 삭제됩니다.

트랜잭션은 자동 커밋됩니다.

해당 테이블의 모든 인덱스가 삭제됩니다.

•CASCADE CONSTRAINTS 옵션은 종속 무결성 제약조건을 삭제합니다.

이 명령은 ROLLBACK할 수 없습니다.

 

 

객체의 이름 변경

테이블, 뷰, Sequence, Synonym의 이름을 바꾸려면 RENAME 명령을 실행합니다.
SQL>RENAME s_ord TO s_order;
객체의 소유자이어야 합니다.

 

 

TRUNCATE

•TRUNCATE 명령은

  – 테이블의 모든 행을 삭제하고

  – 테이블이 사용한 저장 공간을 사용가능하도록 하는

  – DDL 명령입니다.

SQL>TRUNCATE TABLE s_item;
•TRUNCATE를 쓰면 행의 삭제를 롤백할 수 없습니다.

행을 삭제하는데에 대신 사용할 수 있는 명령은 DELETE 명령입니다.

 

 

테이블에 주석 입력

•COMMENT 명령을 써서 테이블이나 column에 주석을 입력할 수 있습니다.
SQL>COMMENT ON TABLE s_emp

2 IS ‘Employee Information’;

입력된 주석은 다음의 dictionary 뷰를 통해 볼 수 있습니다.

ALL_COL_COMMENTS

USER_COL_COMMENTS

ALL_TAB_COMMENTS

USER_TAB_COMMENTS

12. Sequence 생성

 

Sequence란 무엇인가?

•Primary Key값으로 사용될 숫자 값을 생성

공유할 수 있는 객체

대개 primary key 값을 생성하기 위해 사용

응용 프로그램 코드를 대체

메모리에 Cache되었을 때 Sequence 값의 액세스 효율 증가

•Sequence는 테이블과는 독립적으로 저장되고 생성됩니다. 따라서 하나의 sequence를 

  여러 테이블에서 쓸 수 있습니다.

 

 

Sequence 생성

자동적으로 순차적인 숫자를 생성하기 위해 Sequence를 정의합니다.
Syntax

 

CREATE SEQUENCE NAME

[INCREMENT BY n]

[START WITH n]

[{MAXVALUE n | NOMAXVALUE}]

[{MINVALUE n | NOMINVALUE}]

[{CYCLE | NOCYCLE}]

[{CACHE n | NOCACHE}]

– INCREMENT BY n은 sequence 번호간의 간격인 정수 n이며, 생략하면 sequence는 

  1씩 증가

START WITH n은 생성되는 첫 번째 sequence 번호로 생략되면 sequence는 1씩 증가

– MAXVALUE n은 생성가능한 sequence의 최대값,

  NOMAXVALUE는 기본 옵션으로 최대값은 1027

– MINVALUE n은 생성가능한 sequence의 최소값,

  NOMINVALUE는 최소값 1

– CYCLE의 default값은 NOCYCLE입니다.

– CACHE n | NOCACHE는 Oracle7 Server가 미리 지정하고 메모리에 유지할 값의 수

  로 default값은 20입니다.

 

 

Sequence 생성 : 예

S_DEPT 테이블의 Primary Key 값에 사용되는 S_DEPT_ID라는 sequence를 생성합니다.

•CYCLE 옵션을 사용하지 마십시요.

SQL>CREATE SEQUENCE s_dept_id

2 INCREMENT BY 1

3 START WITH 51

4 MAXVALUE 9999999

5 NOCACHE

6 NOCYCLE;

 

 

Sequence 확인

•USER_SEQUENCE 데이터 사전 dictionary 뷰를 확인해 보십시요.
SQL>SELECT sequence_name, min_value, max_value, increment_by, last_number

2 FROM user_sequences;

•LAST_NUMBER column은 사용가능한 다음 sequence값을 출력합니다.

 

 

NEXTVAL과 CURRVAL pseudocolumn

•NEXTVAL은 사용가능한 다음 시퀸스 값을 return합니다.

  – 매번 고유한 값을 return합니다.

•CURRVAL은 현재 시퀸스 값을 구합니다.

  – CURRVAL은 NEXTVAL 사용후에 사용되어야 합니다.

사용 규칙

  ※ 사용함

  – Subquery가 아닌 SELECT 문의 Select List

  – INSERT 문의 Subquery Select List

  – INSERT 문의 VALUES 절

  – UPDATE 문의 SET 절

  ※ 사용않됨

  – 뷰의 SELECT List

  – DISTINCT 키워드가 있는 SELECT

  – GROUP BY, HAVING 또는 ORDER BY 절이 있는 SELECT 문

  – CREATE TABLE 또는 ALTER TABLE 명령의 DEFAULT 값

Sequence 이용 : 예

지역 2에 “Finance”라는 부서를 삽입하십시요.
SQL>INSERT INTO s_dept(id, name, region_id)

2 VALUES      (s_dept_id, NEXTVAL, ‘Finance’, 2);

•S_DEPT_ID sequence에 대한 현재 값을 보십시요.
SQL>SELECT s_dept_id.CURRVAL

2 FROM   SYS.dual;

 

 

Sequence 이용

•Sequence 값을 메모리에 캐쉬하면 시퀸스 값을 더 빨리 액세스 할 수 있습니다.

•NOCACHE로 sequence가 생성되었다면 USER_SEQUENCES 테이블을 query 하여 다

  음 값을 보십시요.

 

 

Sequence 변경

증가 값, 최대 값, 최소 값, 순환 옵션, 캐쉬 옵션을 변경합니다.
Syntax

 

CREATE SEQUENCE NAME

[INCREMENT BY n]

[{MAXVALUE n | NOMAXVALUE}]

[{MINVALUE n | NOMINVALUE}]

[{CYCLE | NOCYCLE}]

[{CACHE n | NOCACHE}]

 

 

Sequence 변경 : Guidlines

자신의 sequence이거나 sequence에 대한 ALTER 권한을 가져야만 sequence를 수정 

  할 수 있습니다.

아직 생성되지 않은 sequence 값만이 ALTER SEQUENCE 명령에 영향받습니다.

유효성 검사를 하게 됩니다.

•START WITH 옵션은 ALTER SEQUENCE를 써서 변경할 수 없습니다. 다른 번호에서 

  다시 시작하려면 이전 sequence를 삭제하고 다시 생성해야 합니다.

Sequence 삭제

•DROP SEQUENCE 명령을 사용하여 sequence를 삭제합니다.

•Sequence가 삭제되면 더 이상 참조할 수 없습니다.

DROP SEQUENCE 시퀸스 이름;

 

13. 뷰(View) 생성

 

뷰의 장점

뷰는 데이터베이스의 선택적인 내용을 보여줄 수 있기 때문에 데이터베이스에 대한 

  액세스를 제한합니다.

복잡한 query를 통해 얻을 수 있는 결과를 간단한 query를 써서 구할 수 있게 합니다.

한개의 뷰를 여러 테이블에 대한 데이터를 검색할 수 있습니다.

특정 평가기준에 따른 사용자 별로 다른 데이터를 액세스할 수 있도록 합니다.

 

 

 생성

•CREATE VIEW 문에 Subquery를 포함합니다.
Syntax

 

CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW 뷰명 [(alias[, alias]…)]

AS Subquery

  [WITH CHECK OPTION [CONSTRAINT 제약조건]]

[WITH READ ONLY]

뷰를 정의하는 query는 조인, 그룹, Subquery를 포함하는 복잡한 SELECT 문장으로 

  구성될 수 있습니다.

•Subquery는 ORDER BY절을 포함할 수 없습니다.

제약조건의 이름을 명시하지 않으면 시스템이 SYS_Cn 형태의 이름을 지정합니다.

뷰를 삭제하거나 재생성하지 않고 뷰의 정의를 변경하려면 OR REPLACE 옵션을 

  이용합니다.

옵션

  – FORCE는 base table 유․무에 관계없이 뷰를 만듭니다.

  – Alias는 Subquery를 통해 선택된 값에 대한 column명이 됩니다.

  – WITH CHECK OPTION은 뷰에 의해 액세스 될 수 있는 행만이 입력되거나 변경 

    될 수 있음을 지정하고, 제약조건은 CHECK OPTION 제약조건에 지정된 이름입니다.

  – WITH READ ONLY는 이 뷰에서 DML이 수행 될 수 없게 합니다.

 

 

 

 

 

 생성

 징 Simple Complex
테이블의 수 하나 하나 이상
데이터 그룹 포함 가능 여부 가능 가능
뷰를 통한 DML 가능 불가능

 

 

뷰의 생성 : 예

부서 번호가 45인 사원의 사번과 성, 직급을 가지는 뷰를 만드십시요.
SQL>CREATE VIEW empvu45

2 AS SELECT id, last_name, title

3 FROM      s_emp

4 WHERE     dept_id = 45;

•SQL*Plus DESCRIBE 명령을 써서 뷰의 구조를 확인합니다.

뷰에 대해 SELECT 문을 써서 데이터를 확인합니다.

 

 

뷰의 생성 : 예

•Subquery의 column alias를 써서 뷰를 만듭니다.
SQL>CREATE VIEW salvu41

2 AS SELECT id, first_name FIRST, last_name LAST, salary MONTHLY_SALARY

3 FROM      s_emp

4 WHERE     dept_id = 41;

•Alias를 이용하여 뷰의 column명을 설정할 수 있습니다.

 

 

 변경 : 예

•CREATE OR REPLACE를 써서 EMPVU45 뷰를 변경합니다.

  각 column명을 변경하십시요.

SQL>CREATE OR REPLACE VIEW empvu45

2 (id_number, employee, job)

3 AS SELECT id, last_name, title

4 FROM      s_emp

5 WHERE     dept_id = 45;

•CREATE VIEW절의 column alias는 subquery의 column과 같은 순서로 나열됩니다.

Complex 뷰 생성 : 예

두 테이블로부터 값을 출력하는 그룹 함수가 있는 복잡한 뷰를 만드십시요.
SQL>CREATE VIEW dept_sum_vu

2 (name, minsal, maxsal, avgsal)

3 AS SELECT d.name, MIN(e.salary), MAX(e.salary), AVG(e.salary)

4 FROM      s_emp e, s_dept d

5 WHERE     d.dept_id = d.id

6 GROUP BY d.name;

 

 

뷰에 대한 DML 문 사용 규칙

•Simple 뷰에서는 DML 연산을 수행할 수 있습니다.

뷰가 다음 사항을 포함하는 경우 행을 삭제할 수 없습니다.

  – 조인 조건

  – 그룹 함수

  – GROUP BY

  – DISTINCT 명령

부가 다음 사항을 포함하는 경우 데이터를 수정할 수 없습니다.

  – 위의 조건

  – 식으로 정의된 column (SALARY * 12)

뷰가 다음 사항을 포함하는 경우 데이터를 추가할 수 없습니다.

  – 위의 조건

  – 뷰로 선택되지 않은 NOT NULL column

 

 

WITH CHECK OPTION 절의 이용

뷰에 대한 DML 연산이 뷰의 조건을 만족 할 때만 수행 되도록 합니다.
SQL>CREATE OR REPLACE VIEW empvu41

2 AS SELECT *

3 FROM      s_emp

4 WHERE     dept_id = 41

5 WITH CHECK OPTION CONSTRAINT empvu41_ck;

뷰의 부서 번호를 변경하려 한다면 CHECK OPTION 제약조건을 위반하기 때문에 

  error가 발생할 것입니다.

DML 문장 사용 방지

뷰 정의에 WITH READ ONLY 옵션을 추가하여 DML 연산이 수행 될 수 없게 합니다.
SQL>CREATE OR REPLACE VIEW empvu45 (id_number, employee, job)

2 AS SELECT id, last_name, title

3 FROM      s_emp

4 WHERE     dept_id = 45

5 WITH READ ONLY;

뷰의 행에 대한 DML을 수행하려 한다면 Oracle7 Server error인 ORA-01732가 발생

  합니다.

 

 

뷰의 확인

•USER_VIEWS data dictionary에 뷰의 이름과 정의가 있습니다.
SQL>SELECT view_name, text

2 FROM   user_views;

 

 

 삭제

뷰는 데이터베이스의 테이블을 토대로 하기 때문에, 데이터 손실없이 뷰를 삭제할 수 있습니다.
DROP VIEW 뷰이름;

 

14. 인덱스 생성

 

인덱스란 무엇인가?

데이터베이스의 객체

행의 검색속도를 빠르게 하기 위해 Oracle7 Server가 사용

테이블과는 독립적

자동적으로 사용되고 Oracle7 Server가 유지 관리

 

 

인덱스를 어떻게 만드는가?

자동 생성

테이블 정의에 PRIMARY KEY나 UNIQUE 제약조건을 정의할 때 unique 인덱스가 

  자동적으로 생성

사용자 생성

행에 대한 액세스 속도를 빠르게 하기 위해 Column에 non_unique 인덱스를 생성

 

 

언제 인덱스를 사용하는가?

Oracle7 Server는 테이블의 행에 대한 액세스를 빠르게 하기 위해 인덱스를 사용합니다.

사용자는 개입할 필요가 없습니다.

 

 

인덱스의 구조

•Oracle7 Server는 인덱스에 대해 B*Tree 구조를 이용합니다.

각 인덱스는 페이지로 구성된 포인터(또는 ROWID)와 column 값으로 구성됩니다.

서버는 포인터 값이 있는 노드에 이를 때까지 트리를 탐색합니다.

 

※ B*Tree

 Oracle7 Server는 B*Tree 인덱스를 구조를 이용합니다. 이 트리는 어떤 행에 대한 액세스 횟수도 동일하게 한 이진의 균형 탁색 구조입니다. 행이 테이블의 시작이나 중간, 또는 끝에 있어도 거의 같은 횟수 내에 지정된 값을 액세스하는 효율적인 방법입니다.

 Oracle7 Server가 만드는 인덱스는 트리에 정렬된 여러 개의 저장장소 (페이지)로 구성됩니다. 각 페이지는 키 값이 데이터 자체의 위치를 가리킬 때까지 구조의 아래 쪽으로 향하는 페이지에 대한 포인터와 일련의 키 값을 갖고 있습니다.

인덱스의 유형

•Unique 인덱스

  – Column이 고유한 값을 갖도록 함

•Non-unique 인덱스

  – Query의 속도 증가

단일 행 인덱스

  – 단지 한 개의 column만 인덱스에 사용

연결 또는 조합 인덱스

  – 여러 개의 column을 인덱스에 사용

 

 

인덱스 생성 : Syntax

하나 또는 하나 이상의 column에 인덱스를 생성
CREATE INDEX 인덱스

ON 테이블(Column[, Column]…);

•S_EMP 테이블의 LAST_NAME column에 대한 query 속도를 빠르게 합니다.
SQL>CREATE INDEX s_emp_last_idx

2 ON             s_emp(last_name);

 

 

인덱스 생성 : Guidelines

•WHERE 절이나 조인 조건에서 column을 자주 이용할 때

•Column이 넓은 범위의 값을 가질 때

많은 NULL 값을 갖는 column일 때

테이블의 data가 많고 그 테이블에서 query되는 행의 수가 전체의 10∼15%정도 일때

여러 개의 인덱스가 있다고 해서 항상 query 속도가 빨라지는 것은 아닙니다.

 

인덱스를 만들지 않아야 할 때

테이블이 작을 때

•Column이 query의 조건으로 사용되는 경우가 별로 없을 때

대부분의 query가 행의 10∼50% 이상을 검색한다고 예상될 때

테이블이 자주 변경될 때

 

인덱스 확인

•USER_INDEXS dictionary 뷰는 인덱스의 이름과 unique 여부를 가지고 있습니다.

•USER_IND_COLUMNS 뷰는 인덱스 명, 테이블 명과 column 명을 가지고 있습니다.

SQL>SELECT ic.index_name, ic.column_name, ic.column_position,

2          ix.uniquenss

3 FROM   user_indexes ix, user_ind_columns ic

4 WHERE  ic.index_name = ix.index_name

5 AND     ic.table_name = ‘S_EMP’;

 

 

인덱스 삭제

인덱스를 삭제합니다.
SQL>DROP INDEX s_emp_last_name_idx;
인덱스를 삭제하려면 그 인덱스의 소유자이거나 DROP ANY INDEX 권한을 가지고 

  있어야 합니다.

 

 

15. Privileges (DCL)

 

권한

데이타베이스 보안

  – 시스템 보안

  – 데이터 보안

시스템 권한(System Privilege)

  – 데이터베이스에 대한 액세스 획득

객체 권한(Object Privilege)

  – 데이터베이스 객체에 대한 액세스 획득

스키마

  – 테이블, 뷰, Sequence와 같은 객체들의 집합

 

 

시스템 권한

•80개 이상의 권한이 있습니다.

•DBA는 상위 레벨의 시스템 권한을 갖습니다.

  – 새로운 사용자 생성 (CREATE USER)

  – 사용자 삭제 (DROP USER)

  – 테이블 삭제 (DROP ANY TABLE)

  – 테이블 백업 

•DBA는 SQL 명령인 CREATE USER를 써서 사용자를 생성합니다.

SQL>CREATE USER scott

2 IDENTIFIED BY tiger;

 

 

사용자 시스템 권한

사용자를 생성하면 DBA는 GRANT 명령을 실행하여 사용자에게 권한을 부여할 수 

  있습니다.

대개 사용자는 다음과 같은 시스템 권한을 갖습니다.

  – CREATE SESSION : 데이터베이스에 접속 할 수 있도록 한다.

  – CREATE TABLE : 사용자가 테이블을 생성 할 수 있도록 한다.

  – CREATE SEQUENCE : 사용자가 Sequence를 생성 할 수 있도록 한다.

  – CREATE VIEW : 사용자가 View를 생성 할 수 있도록 한다.

  – CREATE PROCEDURE : 사용자가 Stored Procedure 또는 패키지를 생성 할 수 

                            있도록 한다.

시스템 권한 허가

DBA는 특정 시스템 권한을 사용자에게 허가할 수 있습니다.
GRANT 권한 [, 권한…] TO 사용자 [, 사용자…];
권한은 허가되는 시스템 권한이고, 사용자는 사용자의 이름입니다.

 

 

Role이란?

Role은 사용자에게 허가 할 수 있는 관련된 권한들의 그룹입니다.
CREATE ROLE Role;
Role은 생성되는 role의 이름입니다.

 

 

암호 변경

사용자는 ALTER USER 명령을 써서 자신의 암호를 변경할 수 있습니다.
ALTER USER 사용자 IDENTIFIED BY 암호;
사용자는 사용자의 이름이고, 암호는 새로운 암호입니다.

 

 

객체 권한

객체권한 테이블 Sequence Procedure Snapshot
ALTER
DELETE
EXECUTE
INDEX
INSERT
REFERENCES
SELECT
UPDATE

 

 

 

 

객체 권한 부여 : Syntax

•DBA는 사용자가 특정 객체에 대한 작업을 수행할 수 있게 합니다.

객체 권한은 객체 유형에 따라 매우 다양합니다.

소유자는 객체에 대한 모든 권한을 갖고 있습니다.

소유자는 소유한 객체에 대해 특정 권한을 다른 사용자에게 줄 수 있습니다.

GRANT [객체권한(, 객체권한…) | ALL][(Column)]

ON     객체

TO     [사용자[, 사용자…] | Role | PUBLIC]

[WITH GRANT OPTION];

객체권한은 허가되는 객체권한이고, ALL은 모든 객체 권한, Column은 권한이 허가된 테이블이나 뷰의 column, ON 객체는 권한이 허가된 객체, TO는 권한을 허가 받을 사용자, PUBLIC은 모든 사용자에게 객체 권한을 허가 합니다.

 

 

객체 권한 부여 : 예

•S_EMP 테이블을 query 할 수 있도록 Sue와 Rich에게 Select 권한을 부여 합니다.
SQL>GRANT select

2 ON     s_emp

3 TO     sue, rich;

특정 column에 대한 UPDATE 권한을 사용자와 Role에 부여합니다.
SQL>GRANT update (name, region_id)

2 ON     s_dept

3 TO     scott, manager;

 

 

WITH GRANT OPTION 과 PUBLIC 키워드 : 예

권한을 받은 사용자가 다른 사용자에게 권한을 부여할 수 있도록 합니다.
SQL>GRANT select, insert

2 ON     s_dept

3 TO     scott

4 WITH GRANT OPTION

모든 사용자에게 권한을 부여합니다.
SQL>GRANT select

2 ON     s_ord

3 TO     PUBLIC;

부여된 권한 확인

Dictionary   명
ROLE_SYS_PRIVS Role에 부여된 시스템 권한
ROLE_TAB_PRIVS Role에 부여된 테이블 권한
USER_ROLE_PRIVS 사용자가 액세스할 수 있는 role
USER_TAB_PRIVS_MADE 사용자가 부여한 객체 권한
USER_TAB_PRIVS_RECD 사용자에게 부여된 객체 권한
USER_COL_PRIVS_RECD 특정 column에 대해 사용자에게 부여된 객체 권한

 

 

객체에 대한 권한 회수

•REVOKE 명령을 사용합니다.

•WITH GRANT OPTION에 의해 다른 사용자에게 허가된 권한도 회수됩니다.

REVOKE [권한 (, 권한…):ALL]

ON      객체

FROM   [사용자[, 사용자…]:role | PUBLIC]

 

 

Synonym 생성

Synonym을 생성하여 객체에 대한 액세스를 간단하고 쉽게 하게 합니다.

다른 사용자가 소유한 테이블에 대한 참조

긴 객체 이름을 짧게 만듭니다.

CREATE [PUBLIC] SYNONYM 동의어

FROM   객체;

PUBLIC은 모든 사용자가 액세스할 수 있는 Synonym을 만들게하고, 동의어는 생성되는 Synonym의 이름, 객체는 생성된 동의어에 대한 객체입니다.

 

 

 

 

 

 

 

 

 

16. SQL과 SQL*Plus 요약

 

데이터베이스 용어

  념   명
테이블

 

테이블은 한 개 이상의 column과 행으로 된 RDBMS의 기본적인 저장 구조입니다.
행은 테이블에 있는 column 값의 조합입니다.
Column Column은 테이블의 데이터를 표현합니다.
필드

 

행과 column이 교차되는 곳이 필드입니다. 필드는 데이터를 가질 수 있으며 데이터가 없다면 null 값을 가지고 있다고 말합니다.
Primary Key

 

Primary Key는 테이블의 각 행을 고유하게 식별하는 column이나 column의 조합입니다.
Foreign Key

 

Foreign Key는 동일 테이블이나 다른 테이블의 Primary Key를 참조하는 column이나 column의 조합입니다.

 

 

SQL*Plus, SQL, PL/SQL

언어나 Tool   명
SQL Tool이나 응용 프로그램에서 Oracle7 Server와 통신하기 위한 명령어
SQL*Plus

 

자체 명령어를 가지고 있으며 SQL과 PL/SQL 문을 인식하고 실행하는 Oracle Tool
PL/SQL

 

데이터베이스 데이터를 조작하고 응용 프로그램의 논리를 표현하기 위한 Oracle의 절차적 언어

 

 

SQL 명령

명령어

  명
SELECT

 

데이터베이스에서 데이터를 검색하며 가장 널리 쓰이는 명령입니다.
INSERT

DELETE

UPDATE

 

데이터베이스에 각각 새로운 행을 입력, 변경, 필요없는 행을 삭제합니다. 이들을 데이터 조작어(DML)라 합니다.
CREATE

DROP

TRUNCATE

ALTER

RENAME

 

테이블 생성, 변경, 삭제를 하며, 데이터 정의어(DDL)라 합니다.

 

COMMIT

SAVEPOINT

ROLLBACK

 

DML 문이 행한 변경사항을 관리합니다. 데이터에 대한 변경사항은 논리적인 트랜잭션으로 그룹핑할 수 있습니다.
GRANT

 

REVOKE

 

Oracle 데이터베이스와 그 구조에 대한 액세스 권한 부여와 회수를 하며, 데이터 제어어(DCL)라 합니다.

Oracle Data Dictionary

데이타 사전에 저장된 정보는 다음과 같습니다.

  – Oracle7 Server의 사용자 이름

  – 사용자에게 허가된 권한

  – 데이터베이스 객체 이름

  – 테이블 제약조건

  – Auditing 정보

자주 액세스되는 테이블

– DICTIONARY

– USER_OBJECTS

  – OBJ(USER_OBJECTS에 대한 Synonym)

– USER_CONSTRAINTS

– USER_SEQUENCES

 

 

SQL*Plus 명령

환경 명령

– SET, SHOW, CLEAR

형식 명령

– COLUMN, BTITLE, TTITLE, BREAK, COMPUTE

파일 조작

– EDIT, GET, SAVE, SPOOL, START

상호 작용

– ACCEPT, PROMPT

17. SQL*Plus를 이용한 PL/SQL 블록 작성

 

 

SQL*Plus환경

•SQL 문을 실행합니다.

보고서를 작성합니다.

•PL/SQL 블록을 작성하고 실행합니다.

 

 

SQL*Plus에서 명령어 입력

데이타와 데이터베이스 구조를 조작하는 SQL 명령어

정형화된 query 결과를 만들고, 명령어를 편집하고, 변수를 생성하는 SQL*Plus 명령어

절차문을 실행하기 위한 PL/SQL 블록

 

 

PL/SQL 블록의 선언과 작성

•SQL 버퍼내에 PL/SQL 블록을 선언

– PL/SQL 블록은 연속되는 한 문장으로 취급

– SQL*Plus는 키워드인 DECLARE나 BEGIN을 파일의 시작으로 간주합니다.

마침표가 나오면 버퍼를 닫습니다.

파일에 PL/SQL 블록을 작성

– SQL*Plus 명령은 블록 안에서는 쓸 수 없습니다.

블록 안에서 SQL*Plus 치환(Substitution)변수를 참조합니다.

 

 

SQL*Plus명령

•ACCEPT

사용자가 입력하는 내용(값)을 읽어 변수에 저장

•VARIABLE

앞에 콜론(:)을 써서 PL/SQL에서 참조할 수 있는 변수를 선언

•PRINT

변수의 현재 값을 표시(출력)

•EXECUTE

하나의 PL/SQL 문(Procedure, Function)을 실행

치환 값(substitution) : Syntax

값은 치환된 값으로 블록에 전달됩니다.

치환 값은 ACCEPT 명령을 써서 블록 외부에서 입력하도록 합니다.

앰퍼샌드(&)는 블록 안의 치환 변수 앞에 씁니다.

  ACCEPT 변수 PROMPT 메시지

 

 

Global 변수 : Syntax

•Global 변수를 이용하여 PL/SQL 블록의 값을 출력할 수 있습니다.

•SQL*Plus의 VARIABLE 명령을 써서 블록 외부에 Global 변수를 선언합니다.

블록 내부에서 Global 변수 앞에 콜론(:)을 사용합니다.

•SQL*Plus의 PRINT 명령을 써서 블록 외부에서 값을 출력합니다.

VARIABLE 변수

[NUMBER|CHAR|CHAR(n)|VARCHAR2(n)]

PRINT 변수

 

 

입출력 값 전달 : 예

월급을 입력하여 계산된 연봉을 출력하는 예입니다.
ACCEPT p_sal PROMPT ‘Enter the salary: ‘

VARIABLE g_year_sal NUMBER

DECLARE

v_sal NUMBER := &p_sal;

BEGIN

:g_year_sal := v_sal*12;

END;

/

PRINT g_year_sal

 

 

 

 

SQL*Plus에서 디버깅

컴파일 오류를 점검합니다.

  – Anonymous 블록 오류는 화면에 표시됩니다.

  – Stored subprogram의 경우에는 USER_ERRORS data dictionary 테이블에 저장됩니다.

 SQL> SHOW ERRORS

SQL> SELECT   *

2  FROM     user_errors;

•Exception 처리를 합니다. (Trap or Propagate)

 

 

GLOBAL 변수 사용시의 디버깅 : 예

 …

BEGIN

IF MOD (v_num,2) = 0 THEN

   :g_debug := ‘짝수를 입력했습니다.’;

ELSE

   :g_debug := ‘홀수를 입력했습니다.’;

END IF;

/

SQL>START test

   숫자를 입력하시오 : 10

PL/SQL procedure successfully completed

SQL>PRINT g_debug

G_DEBUG

-—————————————————————————————————————

   짝수를 입력했습니다.

 

 

Stored Procedure 실행 : Syntax

EXECUTE 명령을 이용하여 Stored Procedure를 실행시킵니다.
 EXECUTE 프로시저명 [(argument_list)]

 

 

Stored Function 실행 : Syntax

반드시 값을 반환해야 하므로 식의 일부분으로서 함수를 사용합니다.

함수의 반환값을 global 변수에 assign합니다.

 EXECUTE 변수 := 함수명 [(argument_list)]

 

 

Stored Procedure 실행 : 예

SQL>CREATE TABLE b_test (coll NUMBER);

SQL>CREATE PROCEDURE testproc IS

2 BEGIN

3    FOR i IN 1..3 LOOP

4        INSERT INTO b_test VALUES(i);

5    END LOOP;

6  END;

7  /

SQL>EXECUTE testproc;

PL/SQL procedure successfully completed.

SQL>SELECT *

2 FROM   b_test;

 

 

Stored Function 실행 : 예

SQL>CREATE FUNCTION testfunc

2    (v_num NUMBER)

3 RETURN NUMBER IS

4 BEGIN

5    RETURN (v_num/2);

6 END;

7 /

SQL>VARIABLE g_num NUMBER

SQL>EXECUTE :g_num := testfunc(12);

PL/SQL procedure successfully completed.

SQL>print g_num

G_NUM

—————————————————————

6

18. 서브 프로그램을 이용한 모듈화 프로그래밍

 

PL/SQL 단위 프로그램이란 무엇인가?

•Object명이 있는 PL/SQL 블록(Named PL/SQL Block)

•3가지 종류의 PL/SQL 프로그램 단위가 있음.

동작을 수행하는 프로시저

값을 계산하는 함수

논리적으로 연관된 프로시져와 함수로 이루어진 패키지

응용 서브프로그램으로 개발되어졌거나 데이터베이스에 저장되어 있음.

 

 

서브 프로그램 구성 요소

HEADER – Mandatory

서브 프로그램명, 유형, argument

DECLARATIVE – Optional

변수

EXECUTABLE – Mandatory

– SQL

– PL/SQL 제어문

EXCEPTION- Optional

– Error 발생시 수행할 동작

END; – Mandatory

 

 

서브 프로그램 작성

환경 선택
서브 프로그램 작성
 Debug
코드 컴파일
 행

프로시저 작성 – Syntax

CREATE OR REPLACE PROCEDURE 이름

[(Parameter,…)]

IS

pl/sql_block;

Parameter Syntax :
Parameter 명[IN | OUT | IN OUT] data type

  [{ := |DEFAULT}식]

datatype에 제약조건을 쓰지 마십시오.

 

 

프로시저 작성 – Guidelines

•SQL*Plus에서 프로시져를 작성할 때 CREATE OR REPLACE를 사용하십시오.

어떠한 Parameter 라도 사용 가능합니다.

•IS로 PL/SQL 블록을 시작합니다.

•Local 변수 선언은 IS와 BEGIN사이에 선언하십시오.

 

 

Procedural Parameter Modes

프로시저
IN argument
실행
OUT argument
환경
IN OUT argument
(DECLARTE)
BEGIN
EXCEPTION
END;

 

프로시저 작성 : 예

CREATE OR REPLACE PROCEDURE change_salary

(v_emp_id IN NUMBER,

v_new_salary IN NUMBER)

IS

BEGIN

UPDATE s_emp

SET salary = v_new_salary

WHERE id = v_emp_id;

COMMIT;

END change_salary;

/

 

 

프로시저와

 함수

프로시저 함수
 IN 인수  IN 인수
실행 실행
 OUT 인수
환경 환경
 IN OUT 인수
((DECLARE) (DECLARE)
BEGIN BEGIN
EXCEPTION EXCEPTION
END; END;

 

함수와 프로시저 비교

프로시저   수
PL/SQL 문으로서 실행 식의 일부로서 사용
RETURN datatype이 없음 RETURE datatype이 필수
값을 Return할 수 있음 값을 Return하는 것이 필수

함수 작성

실행환경에 값을 return하기 위해 PL/SQL 함수를 작성합니다.

함수 선언에서 datatype이 있는 RETURN 절을 추가합니다.

•PL/SQL 블록에 적어도 한 개의 RETURN 문을 포함합니다.

 

 

함수 작성 : Syntax

CREATE OR REPLACE FUNCTION 이름

[(argument…)]

RETURN datatype

IS

pl/sql_block;

•PL/SQL 블록에는 적어도 한 개의 RETURN 문이 있어야 합니다.

•Function의 이름은 표준 Oracle 명명법에 따른 함수명이고, argument “프로시저” 절에

 소개한 argument syntax를 참고하십시오. Datatype은 반환되는 값의 datatype이며, pl/sql_block은 함수가 수행할 내용을 정의한 몸체부분입니다.

 

 

함수 작성 : 예

입력 값에 근거하여 tax를 반환합니다.
 CREATE OR REPLACE FUNCTION tax

(v_value IN NUMBER)

RETURN NUMBER

IS

BEGIN

RETURN(v_value*.07);

END tax;

/

 

 

SQL*Plus에서 프로시저 실행 : 예

SQL*Plus 명령 프롬프트에서 argument 값과 함께 프로시저 이름을 입력하십시오.
SQL>EXECUTE change_salary(17,1000);

SQL*Plus에서 함수 실행 : 예

지정된 값에 근거하여 tax를 출력하십시오
SQL>VARIBLE x NUMBER

SQL>EXECUTE :x: + tax(100);

SQL>PRINT x

x

– – – – – – – – – – – – – – – – – – – –

7

 

 

SQL 문에서 함수 실행

장점

– SQL 에서는 쉽게 쓸 수 없는 계산이 가능

– Query 효율 증가

•Guidelines

함수만 가능

단일 행 함수(Single Row Function)

– DMA 문 사용불가

모든 argument는 IN만 허용

– PL/SQL의 datatype이 아닌 Orecle7 Server의 내부 datatype만 허용

– EXECUTE 권한이 필요

 

 

SQL 문에서 함수 실행

함수 사용이 가능한 SQL 절은 다음과 같습니다.

•SELECT 명령의 select_list

•WHERE와 HAVING 절의 조건

•CONNECT BY, START WITH, ORDER BY, GROUP BY

•INSERT 명령의 VALUES 절

•UPDATAE 명령의 SET 절

 

 

 

 

19. 간단한 PL/SQL 블록 생성

 

PL/SQL 블록의 구조

DECLARE      – Optional

               – 변수, 상수, 커서, user-defind exception

BEGIN         – mandatory

               – SQL

               – PL/SQL 제어문

EXCEPTION    – Optional

               – 오류 발생시 수행할 작업

END;          – mandatory

 

 

PL/SQL에서 변수 처리

선언부에서 변수 선언과 초기화

실행부에서 변수에 새로운 값 지정

•parameter를 통해 PL/SQL 블록으로 값 전달

출력 변수를 이용하여 결과 출력

 

 

변수와 상수 선언 : syntax

Identifier [CONSTANT] datatype [NOT NULL]

        [ := | DEFAULT 수식];

Guidelines

•Naming Rule을 따르십시오.

•SQL object 에 사용된 규칙에 따라 Identifier의 이름을 정하고,

예를들어, 변수는 v_name, 상수는 c_name을 쓰는 식의 naming convention을 따르며,

•NOT NULL 조건을 갖지 않은 변수라면 반드시 초기화할 필요는 없습니다.

변수를 초기화하기 위해 지정 연산자(:=) 또는 DEFAULT를 사용하며 변수는 기본적

  으로 NULL로 초기화됩니다.

한 행에는 하나의 변수만 선언할 수 있습니다.

 

 

 

스칼라(Scalar)변수 선언 

내부 구성요소가 없고

한 가지 값만 가집니다.

기본적인 유형

  – BINARY_INTEGER : -2147483647에서 2147483647 사이의 정수

  – NUMBER[(P, S)] : 고정 및 부동 소숫점 수에 대한 기본 유형

  – CHAR[(최대길이)] : 32767바이트까지의 고정길이 문자 데이터에 대한 기본 유형으

    로 최대길이를 명시하지 않으면 기본적으로 길이는 1로 설정

  – LONG : 32760바이트까지의 가변길이 문자 데이타

– LONG RAW

  – VARCHAR2(최대길이) : 32767바이트까지의 가변길이 문자 데이타

  – DATE : 날짜와 시각에 대한 기본 유형

  – BOOLEAN : 논리연산에 사용되는 세 가지 값(TRUE, FALSE, NULL) 중 하나를 저

    장하는 기본 유형

 

 

스칼라 변수 선언 : 예

v_gender     CHAR(1);

v_count      BINARY_INTEGER := 0;

v_total_sal    NUMBER(9,2) := 0;

v_order_date DATE := SYSDATE + 7;

v_tax_rate    CONSTANT NUMBER(3,2) := 8.25;

v_valid       BOOLEAN NOT NULL := TRUE;

 

 

%TYPE 속성

아래 사항에 따라 변수를 선언하게 해 줍니다.

  – 이미 선언된 다른 변수

  – 데이터베이스 column definition

아래 항목 뒤에 %TYPE을 붙입니다.

  – 데이터베이스 테이블과 column

  – 이미 선언한 변수명

•PL/SQL은 변수의 datatype과 크기를 결정

 

%TYPE :

 DECLARE

v_last_name           s_emp.last_name%TYPE;

v_fist_name            s_emp.first_name%TYPE;

v_balance             NUMBER(7,2);

v_minimum_balance    v_balance%TYPE := 10;

%TYPE 속성을 이용하여 얻을 수 있는 장점

기술한 데이터베이스 column definition을 정확히 알지 못하는 경우에도 쓸수 있다.

기술한 데이터베이스 column definition이 변경되어도 다시 PL/SQL을 고칠 필요가 없다.

 

 

Composite Datatype

복합(Composite)Datatype

– PL/SQL TABLES

– PL/SQL RECORDS

  – 내부 구성요소 포함(internal component)

  – 재사용 가능

•PL/SQL TABLES

  – 두 가지 요소로 구성

  – Datatype이 BINARY_INTEGER인 primary key

  – 스칼라 datatype의 column

  – 제한되어 있지 않으므로 동적으로 크기 증가

 

 

PL/SQL TABLE 구조

Primary Key Column
1 Jones
2 Smith
3 Maduro
BINARY_INTEGER 스칼라

 

PL/SQL TABLE의 선언

•TABLE datatype을 선언합니다.

해당 datatype을 갖는 변수를 선언합니다.

Syntax

 

DECLARE

TYPE 유형이름 IS TABLE OF scalar datatype

[NOT NULL] INDEX BY BINARY_INTEGER;

Identifier 유형이름;

유형이름은 TABLE 유형의 이름이고, Scalar Datatype은 PL/SQL TABLE 요소의

datatype으로 %TYPE 속성을 사용할 수 있습니다.

 

)

 DECLARE

TYPE name_table_type IS TABLE OF VARCHAR2(25)

INDEX BY BINARY_INTEGER;

first_name_table          name_table_type;

last_name_table          name_table_type;

 

 

PL/SQL RECORDS

스칼라, RECORD, 또는 PL/SQL TABLE datatype중 하나 이상의 요소로 구성됩니다.

•3GL의 레코드와 구조가 유사합니다.

데이타베이스 테이블의 행과 동일하지 않습니다.

논리적 단위로서 필드 집합을 처리할 수 있도록 해 줍니다.

테이블의 행을 읽어올 때 편리합니다.

 

 

PL/SQL RECORDS의 구조

필드1(datatype)  필드2(datatype)  필드3(datatype)

PL/SQL RECORD 선언

 Syntax

 

DECLARE

TYPE 유형이름 IS RECORD

(필드이름1 필드유형

[NOT NULL { := |DEFAULT} 식],

(필드이름2 필드유형

[NOT NULL { := |DEFAULT} 식], …);

Identifier   유형이름;

유형이름은 RECORD 유형의 이름이고, 필드유형은 필드의 datatype으로 %TYPE과 %ROWTYPE 속성을 사용할 수 있습니다. 식은 문자, 변수 또는 연산자와 함수로 된 식입니다.

)

 DECLARE

TYPE emp_record_type IS RECORD

(last_name VARCHAR2(25),

first_name VARCHAR2(25),

gender    char(1));

employee_record  emp_record_type;

 

 

%ROWTYPE 속성

데이타베이스 테이블이나 뷰의 일련의 column들에 대해 변수 선언

데이타베이스 테이블명 접두어로 표기

•RECORD에 있는 필드는 테이블이나 뷰의 column명과 datatype를 취함

 

 

%ROWTYPE 속성의 장점

중요한 데이터베이스 column의 갯수와 datatype을 모르게 할 수 있습니다.

SELECT문을 사용하여 행을 검색하거나 Explicit 커서로 여러 행을 검색할 때 유용합니다.

)

 DECLARE

dept_record    s_dept%ROWTYPE;

emp_record    s_emp%ROWTYPE;

PL/SQL 블록 : Syntax

문장은 여러 행에 걸쳐 쓸 수 있습니다.

어휘단위는 공백으로 분리할 수 있습니다.

•Identifiers(변수)

– 30자까지 쓸 수 있습니다.

반드시 영문자로 시작해야 합니다.

데이터베이스 테이블의 column명과 같은 이름은 사용하지 마십시오.

문자와 날짜 리터럴에는 따옴표(”)로 표시해야 합니다.

숫자는 일반적인 수치나 과학적 표기법으로 표현할 수 있습니다.

주석은

여러 행에 걸쳐 쓰려면 /* 과 */ 기호를 사이에 쓰고

한 행에 쓰는 주석은 라인 처음에 –을 표시합니다.

 

 

내장된 블록(Nested Block)과 변수의 영역

실행문을 쓸 수 있는 곳이면 어디든 문장을 내장할 수 있습니다.

•Nested Block은 문장 처럼 취급 됩니다.

•Exeption section에도 nested block을 쓸 수 있습니다.

•Object의 통용 범위는 프로그램내에서 Object를 참조할 수 있는 영역안입니다.

선언하지 않은 블록에서 Identifiers를 참조하면 상위 블록에서 찾습니다.

자신을 내포한 블록으로 찾아 올라 갑니다.

자신이 내포한 블록으로 찾아 내려가지 않습니다.

 

 

내장된 블록과 변수의 영역 : 예

DECLARE

x INTEGER;                              x의 영역

BEGIN

DECLARE

y NUMBER;              y의 영역

BEGIN

END;

END;

변수에 값 할당 : Syntax

Identifiers := 식;

plsql_table_name(primary_key_value) := 식;

plsql_record_name.field_name := 식;

plsql_table_name은 PL/SQL TABLE 이름, primary_key_value은 PL/SQL TABLE의 index,

plsql_record_name은 PL/SQL RECORD 이름, field_name은 PL/SQL RECORD의 필드입니다.

 

 

변수에 값을 할당(Assign)정하는 방법 : 예

최대 급여를 현재 급여로 할당합니다.
v_max_sal  : = v_sal;
•PL/SQL TABLE의 3번째 Identifiers에 “Maduro”를 저장합니다.
last_name_table (3) : = ‘Maduro’;
•PL/SQL RECORD에 새로운 사원에 대한 기본 정보를 저장합니다.
emp_record.last_name         : = ‘Maduro’;

emp_record.first_name         : = ‘Elena’;

emp_record.gender            : = ‘F’;

 

 

변수에 값을 할당(Assign)하는 방법 : 예

DECLARE

v_weight           NUMBER(3) := 600;

m_message        VARCHAR2(255) := ‘Product 10012’;

BEGIN

서브 블록  DECLARE

v_weight               NUMBER(3) := 1;

v_message             VARCHAR2(255) := ‘Product 10011’;

v_new_locn             VARCHAR2(50) := ‘Europe’;

BEGIN

v_weight := v_weight + 1;

v_new_locn : = ‘Western’ || v_new_locn;

END;

v_weight := v_weight + 1;

v_message := v_message || ‘is in stock’;

v_new_locn := ‘Western’ || v_new_locn;

END;

PL/SQL의 연산자

논리 연산자

산술 연산자                               SQL과 마찬가지

연결 연산자(concatenation)

지수 연산자(**)

괄호를 써서 수행될 연산순서를 조정하십시오.

 

루프의 인덱스를 증가시키십시오.

 v_count  := v_count + 1;
•Boolean 변수의 값을 설정하십시오.
 v_equal  := (v_n1 = v_n2);
값을 가지고 있는지 사번에 대해 검증하십시오.
 v_valid  := (v_emp_id IS NOT NULL);

 

 

PL/SQL의 함수

사용가능한 함수

단일 행 숫자 함수

단일 행 문자 함수                  SQL과 마찬가지 

– Datatype 변환 함수

날짜 함수

사용할 수 없는 함수

– GREATEST

– LEAST

그룹 함수(SQL statement 에서는 가능)

 

 

우편발송 주소를 만드십시오.
 v_mailing_address := v_name || CHR(10) ||

v_addess || CHR(10) || v_country || CHR(10) || v_zip_code;

이름을 대문자로 바꾸십시오.
 v_last_name := UPPER(v_last_name);

 

Datatype 변환

데이타를 다른 datatype으로 변환

식에서 다른 datatype을 같이 쓰게 되면 오류가 발생하거나 성능이 저하됩니다.

변환 함수:

– TO_CHAR

_ TO_DATE

_ TO_NUMBER

 

 

데이터유형 변환 : 예

이 문장은 컴파일 오류가 발생합니다.
 v_comment   := USER || ‘ : ‘ || SYSDATE;
오류를 정정하려면 TO_CHAR 변환 함수를 사용합니다.
 v_comment   := USER || ‘ : ‘ || TO_CHAR(SYSDATE);

 

 

NON-PL/SQL 변수 참조

호스트 변수인 NON-PL/SQL 변수 참조

콜론(:)을 접두어로 써서 참조

•SQL*Plus Global변수에 연봉을 저장합니다.

 :g_annual_salary  := v_salary*12;

 

 

프로그래밍(Guidelines)

유지보수를 쉽게 해야 합니다.

코드에 주석을 달아 문서화 해야 합니다.

대소문자 표기법을 따라 코드를 작성 해야 합니다.

표기법에 따라 Identifiers와 객체를 사용합니다.

읽기 쉽도록 들여쓰기를 합니다.

 

 

 

 

코드에 주석 달기

두 개의 dash( – – )를 주석 앞에 쓰거나,

•/* 과 */사이에 주석을 달면 됩니다.

)

DECLARE

v_sal NUMBER(9,2);

BEGIN

/* 입력하는 월급을 이용하여 연봉을 계산합니다. */

v_sal := v_sal*12;

END;

 

 

코드 규약(Code Convention)

  류 대소문자 표기법
SQL 명령 대문자 SELECT, INSERT
PL/SQL 키워드 대문자 DECLARE, BEGIN, IF
Datatype 대문자 VARCHAR2, BOOLEAN
식별자, 매개변수 소문자 v_sal, emp_cursor, g_sal
데이터베이스 테이블, Column 소문자 s_emp, order_date, id

 

 

코드 명명법(Code Naming Convention)

식별자 이름 지정 규칙
변수 v_name v_sal
상수 c_name c_company_name
커서 name_cusor emp_cursor
Exception e_name e_too_many
Table type name_table_type amount_table_type
테이블 name_table order_total_table
Record type name_record_type emp_record_type
Record name_record customer_record
SQL*Plus Substitution Parameter p_name p_sal
SQL*Plus Global 변수 g_name g_year_sal

들여쓰기 : 예

알아보기 쉽도록 코드의 각 레벨별로 들여 쓰기를 합니다.

)

BEGIN

IF x=0 THEN

y=1;

END IF;

END;

 

 

20. 오라클 데이터 베이스와의 인터페이스

 

PL/SQL에서 SQL 명령

•SELECT 문을 이용하여 데이터베이스에서 한 행의 데이터 추출

•DML 명령을 이용하여 데이터베이스의 행 변경

•COMMIT이나 ROLLBACK 명령으로 트랜잭션 제어

•DBMS_SQL 패키지를 이용한 DDL과 DCL문 사용

 

 

데이터 검색 : Syntax

SELECT를 사용하여 데이터베이스에서 데이터 검색
SELECT   select_list

INTO      variable_name : record_name

FROM     table

WHERE   condition;

•INTO 절 필요

한 행만이 return 됨

모든 SELECT syntax 사용이 가능

 

 

데이터 검색 : 예

특정 주문에 대한 주문일과 발송일을 검색하십시오.
CREATE OR REPLACE PROCEDURE ship_date

(v_ord_id IN NUMBER)

IS

v_date_ordered s_ord.date_ordered%TYPE;

v_date_shipped s_ord.date_shipped%TYPE;

BEGIN

SELECT  date_ordered, date_shipped

INTO    v_date_ordered, v_date_shipped

FROM   s_ord

WHERE  id = v_ord_id;

END ship_date;

데이터 검색 : 예

특정 부서의 모든 사원에 대한 총 급여를 검색하시오.
CREATE OR REPLACE FUNCTION sum_emp

(v_ord_id IN NUMBER)

RETURN NUMBER

IS

v_sum_salary s_emp.salary%TYPE;

BEGIN

SELECT  SUM(salary)    — group function

INTO    v_sum_salary

FROM   s_emp

WHERE  dept_id = y_dept_id;

RETURN (v_sum_salary);

END sum_emp;

 

 

데이터 검색 : 예

명시한 부서에 대한 모든 정보를 검색하십시오.
CREATE OR REPLACE PROCEDURE all_dept

(v_dept_id NUMBER)

IS

dept_record s_dept%ROWTYPE;

BEGIN

SELECT    *

INTO     dept_record    —- PL/SQL RECORD

FROM    s_dept

WHERE   id = v_dept_id;

END all_dept;

 

 

명명법(Naming Conventions)

•WHERE에서 쓰인 조건이 애매모호하지 않도록 명명 규칙을 사용하십시오.

데이타베이스 column명과 identifier명은 달라야 합니다.

•PL/SQL은 먼저 데이터베이스에서 테이블의 column명을 검사하기 때문에 syntax error

  가 발생할 수 있습니다.

명명법 : 예

특정 주문에 대한 주문일과 방송일을 검색하십시오.
CREATE OR REPLACE PROCEDURE order_info

(id IN NUMBER)

IS

date_ordered s_ord.date_ordered%TYPE;

date_shipped s_ord.date_shipped%TYPE;

BEGIN

SELECT   date_ordered, date_shipped

INTO     date_ordered, date_shipped

FROM    s_ord

WHERE   id = id;   —— unhandled exception

– – TOO_MANY_ROWS

END ship_date

 

 

SELECT EXCEPTIONS

•PL/SQL에서 SELECT 문은 하나의 행만을 검색하도록 해야 합니다.

한 개 이상의 행 return되거나 아무 행도 return되지 않으면 exception이 발생합니다.

•SELECT 관련 EXCEPTIONS:

– TOO_MANY_ROWS

– NO_DATA_FOUND

 

 

TOO_MANY_ROWS exception :

고객 번호 208에 대한 주문 id를 추출할 경우.
CREATE OR REPLACE PROCEDURE my_order

IS

v_customer_id  s_ord.customer_id%Type := 208;

v_ord_id    s_ord.id%TYPE;

BEGIN

_ _ _Customer 208 has several orders.

SELECT    id

INTO    v_ord_id

FROM   s_ord

WHERE  customer_id = v_customer_id;

…           – – TOO_MANY_ROWS

END my_order;

NO_DATA_FOUND exception :

고객 번호 999에 대한 주문 id을 추출할 경우.
CREATE OR REPLACE PROCEDURE my_order

IS

v_customer_id s_ord.customer_id%TYPE := 999;

v_ord_id    s_ord.id%TYPE;

BEGIN

_ _ _Custormer 999 is non-existent.

SELECT    id

INTO     v_ord_id

FROM    s_ord

WHERE  customer_id = v_customer_id;

_ _ NO_DATA_FOUND

END my_order;

 

 

데이터 조작

•DMA 명령을 이용하여 데이터베이스 테이블 내용을 변경합니다.

– INSERT

– UPDATE

– DELETE

 

 

데이터 삽입의 예

특정 고객에 대한 S_ORD 테이블에 새로운 주문 내용을 추가하십시오.
CREATE OR REPLACE PROCEDURE cust_order

(v_customer_id s_ord.customer_id%TYPE)

IS

v_date_ordered   s_ord.date_ordered%Type := SYSDATE;

v_sales_rep_id    s_ord.sales_rep_id%Type := 11;

v_payment_type   s_ord.payment_type%Type := ‘CASH’;

v_order_filled      s_ord.order_filled%TYPE := ‘N’;

BEGIN

INSERT INTO s_ord(id,customer_id, date_ordered, date_shipped, sales_rep_id,

total, payment_type, order_filled)

VALUES     (s_ord_id.NEXTVAL, v_customer_id, v_date_ordered, NULL,

v_sales_rep_id, 0, v_payment_type, v_order_filled);

END cust_order;

데이터 변경 : 예

특정 주무에 대한 발송일을 변경하십시오.
CREATE OR REPLACE PROCEDURE  new_ship_date

(v_ord_id   s_ord.id%TYPE,

v_ship_date s_ord.date_shipped%TYPE)

IS

BEGIN

UPDATE    s_ord

SET     date_shipped = v_ship_date

WHERE  id = v_ord_id;

END new_ship_date;

 

 

데이터 삭제 : 예

특정 주문을 삭제하십시오.
CREATE OR REPLACE PROCEDURE  del_order

(v_ord_id   s_ord.id%TYPE)

IS

BEGIN

DELETE FROM s_ord

WHERE  id = v_ord_id;

END del_order;

 

 

SQL 커서 속성(Cursor Attribute)

•Oracle7 Server는 SQL 명령을 parse하고 실행하기 위해 Implicit Cursor를 사용합니다.

커서 속성은

– SQL 명령의 결과를 확인해 볼 수 있게 하고,

– PL/SQL에서 함수처럼 사용할 수 있습니다.

 

 

커서 속성(Cursor Attribute)

•SQL%ROWCOUNT – 영향을 받은 행의 갯수(Number type)

•SQL%FOUND – Boolean Indicator

•SQL%NOTFOUND – Boolean Indicator

•SQL%ISOPEN – 항상 FALSE

SQL 커서 속성 : 예

S_ITEM테이블에서 지정된 주문 번호를 갖는 행을 삭제하고 삭제된 행의 갯수를 출력하십시오.
CREATE OR REPLACE PROCEDURE del_rows

(v_ord_id IN NUMBER,

v_rows_deleted OUT VARCHAR2)

IS

BEGIN

DELETE FROM s_item

WHERE  ord_id = v_ord_id;

v_rows_deleted ; = TO_CHAR(SQL%ROWCOUNT)

|| ‘rows deleted, ‘;

END del_rows;

 

 

COMMIT와 ROLLBACK 명령

COMMIT이나 ROLLBACK 다음에 사용하는 첫번째 DMA 명령으로 트랜잭션을 시작합니다.

•COMMIT이나 ROLLBACK SQL 명령을 사용하여 Explicit하게 트랜잭션을 종료합니다.

추가적인 LOCKING 명령은 SELECT….FOR UPDATE와 LOCK TABLE이 있습니다.

 

 

트랜잭션 제어

COMMIT
INSERT
 

UPDATE

Savepoint

MARKER A

 

INSERT

Savepoint

MARKER B

DELETE
ROLLBACK ROLLBACK A로 ROLLBACK B로 ROLLBACK

트랜잭션 제어 : 예

다음 PL/SQL 블록에 대하여 트랜잭션들을 식별해 보십시오.
BEGIN

INSERT INTO temp(num_col1, num_col2, char_col)

VALUES (1, 1, ‘ROW 1’);

SAVEPOINT a;

INSERT INTO temp(num_col1, num_col2, char_col)

VALUES(2, 1, ‘ROW 2’);

SAVEPOINT b;

INSERT INTO temp(num_col1, num_col2, char_col)

VALUES(3, 3, ‘ROW 3’);

SAVEPOINT c;

ROLLBACK TO SAVEPOINT b;

COMMIT;

END;

 

21. PL/SQL 블록의  Control Structure

 

PL/SQL의 실행 흐름 제어

제어 구조를 사용하여 문장의 논리적인 흐름을 변경합니다.

조건 제어 구조(IF문)

루프 제어 구조

  – BASIC 루프 : 조건 없이 반복

  – FOR 루프 : 계수를 이용하여 반복

  – WHILE 루프 : 문장이 TRUE인 동안에 반복

  – EXIT 루프 : 루프를 종료

 

 

IF 문 : Syntax

일치하는 조건에 따라 선택적으로 작업을 수행할 수 있습니다.
IF 조건 THEN

   문장;

[ELSIF 조건 THEN

   문장;]

[ELSE

    문장;]

END IF;

•ELSIF는 한 단어

•END IF는 두 단어

하나의 ELSE 절만 허용

 

 

간단한 IF 문 : 예

사원의 성이 Dumas이면 지역 번호를 35로 하고, 직급은 Sales Representative로 설정하십시오.

IF v_last_name = ‘Dumas’ THEN

v_job := ‘Sales Representative’;

v_region_id := 35;

END IF;

IF-THEN-ELSE 문 : 예

주문 일자로부터 발송 일자간의 일수가 5일 미만인 주문에 대한 플래그를 설정하십시오.

IF (v_date_shipped – v_date_orderd) < 5 THEN

v_ship_flag := ‘Acceptable’;

ELSE

v_ship_flag := ‘Unacceptabl’;

END IF;

 

 

IF-THEN-ELSE 문 : 예

입력된 값에 대해 계산한 결과를 반환하십시오.

IF v_start > 100 THEN

RETURN (2 * v_start);

ELSIF v_start >= 50 THEN

RETURN (.5 * v_start);

ELSE

RETURN(.1 * v_start);

END IF;

 

 

논리적 조건 설정

•IS NULL 연산자로 널 값을 처리할 수 있습니다.

널 값을 갖는 식은 NULL값을 return 합니다.

 

 

논리표

AND TRUE FALSE NULL OR TRUE FALSE NULL NOT
TRUE TRUE FALSE NULL TRUE TRUE TRUE TRUE TRUE FALSE
FALSE FALSE FALSE FALSE FALSE TRUE FALSE NULL FALSE TRUE
NULL NULL FALSE NULL NULL TRUE NULL NULL NULL NULL

Boolean Condition

 경우에 V_FLAG의 값은 무엇입니까?

v_flag := v_reorder_flag AND v_available_flag;

V_REORDER_FLAG V_AVAILABLE_FLAG VALUE
TRUE TRUE TRUE
TRUE FALSE FALSE
NULL TRUE NULL
NULL FALSE FALSE

 

 

LOOP

루프는 한 문장이나 일련의 문장을 여러 번 반복합니다.

•3 가지 유형의 루프

  – BASIC 루프 : 가장 간단한 루프는 구분 문자인 LOOP와 END LOOP 사이에 반복

되는 문장 부분들로 이루어져 있습니다. 실행상의 흐름이 END LOOP에 도달할 때마다 그와 짝을 이루는 LOOP문으로 제어가 되돌아갑니다. 이러한 루프를 무한 루프라 하며, 여기서 빠져나가려면 EXIT문을 사용합니다.

EXIT 문 : EXIT 문을 이용하면 END LOOP 문 다음 문으로 제어를 보내기 때문

 루프를 종료할 수 있습니다. EXIT는 IF문 내의 처리작업으로서 또는 루프내의 독립적인 문장으로서도 사용할 수 있습니다. 조건에 따라 루프를 종료할 수 있도록 WHEN 절을 덧붙일 수 있습니다.

  – FOR 루프

  – WHILE 루프 

 

 

BASIC 루프 : Syntax

기본 루프를 써서 문장을 반복하십시오.
LOOP

 문장1;        — 문장

. . .

EXIT [WHEN 조건]; — EXIT 문

END LOOP;

•BASIC 루프안에 EXIT문이 없으면 무한히 반복하게 됩니다.

 

BASIC 루프 : 예

주문 번호 101에 대해 10개의 새로운 항목을 삽입하십시오.
. . .

v_ord_id s_item.ord_id%TYPE := 101;

v_counter NUMBER(2) := 1;

BEGIN

. . .

LOOP

INSERT INTO s_item(ord_id, item_id)

VALUES(v_ord_id, v_counter);

v_counter := v_counter + 1;

EXIT WHEN  v_counter > 10;

END LOOP;

. . .

 

 

FOR 루프 : Syntax

반복 횟수를 정의하여 사용하려면 FOR 루프를 쓰십시오.
FOR 인덱스 IN [REVERSE] 하한.. 상한 LOOP

  문장1;

  문장2;

. . .

END LOOP;

인덱스는 자동적으로 선언되니 여러분이 선언하지 마십시오.

 

 

FOR 루프 : 예

인덱스의 최종 값과 루프가 실행된 횟수를 출력하십시오.

 

Guidelines

인덱스는 루프 밖에서는 정의되지 않았으므로 루프안에서만 참조하십시오.

인덱스의 현재 값을 참조하려면 식(expression)을 사용하십시오.

인덱스에 값을 지정하기 위해 인덱스를 참조하지 마십시오.

    (i := i+1 ⇒ 허용안됨)

WHILE 루프 : Syntax

조건이 TRUE인 동안에만 문장을 반복하려면 WHILE 루프를 사용하십시오.
WHILE 조건 LOOP               조건은 각 반복의 시작에서 평가합니다.

  문장1;                                       

  문장2;

. . .

END LOOP;

 

 

WHILE 루프 : 예

주문 번호 101에 대해 10개의 새로운 항목을 삽입하십시오.
 . . .

v_ord_id  s_item.ord_id%TYPE := 101;

v_counter NUMBER(2) := 1;

BEGIN

. . .

WHILE v_counter <= 10 LOOP

INSERT INTO s_item(ord_id, item_id)

VALUE (v_ord_id, v_counter);

v_counter := v_counter + 1;

END LOOP;

. . .

 

 

Nested 루프와 레이블

여러 레벨에 루프를 내장시킵니다.

블록과 루프를 구분할 수 있도록 레이블을 사용합니다.

레이블을 참조하는 EXIT문을 써서 외부 루프를 빠져나갑니다.

<<label_name>>

LOOP

. . .

END LOOP label name;

 

Nested 루프와 레이블 : 예

내부 블록의 값에 근거하여 외부 루프를 종료하십시오.
<<outer_loop>>

WHILE a>b LOOP

b := b + 1;

<<inner_loop>>

WHILE b>c LOOP

c := c + 2;

EXIT outer_loop WHEN c > 200;

END LOOP inner_loop;

END LOOP outer_loop;

22. EXPLICIT 커서를 이용한 Query 처리

 

커서란 무엇인가?

커서는 private SQL의 작업 영역입니다.

Oracle7 Server가 실행한 SQL문은 해당 문장에 관련된 개별적인 커서를 갖고 있습니다.

커서의 두 유형:

  -Implicit 커서 : 모든 DML과 PL/SQL SELECT 문에 선언

  -Explicit 커서 : 프로그래머가 선언

 

 

Explicit 커서 기능

•Query에 의해 return 된 행을 한 행씩 처리

현재 행을 처리

•PL/SQL 블록에서 커서 제어를 가능케 함

 

 

Explicit 커서 흐름도

        NO
DECLARE OPEN FETCH EMPTY? CLOSE
•Named

SQL

  영역생성

 

 

 

•Cursor

  활성화

 

 

 

 

현재 Record

  을 변수에

assign

 

 

 

•Record가 더 

  있는지 검사

•Record가 더

  이상 없으면 

  Fetch하 하지 

  않음

YES

 

 

 

 

 

•Cursor의 

CLOSE

 

 

 

 

 

 

커서 선언 : Syntax

커서 내부에서 사용될 값을 가지고 있을 수 있는 변수를 선언한다.
DECLARE

        CURSOR 커서이름 IS

select_list;

커서 선언내에는 INTO 절을 쓰지 않습니다.

커서 선언 : 예

어떤 주문의 항목들을 한 항목씩 추출합니다.
DECLARE

. . .

v_ord_id      s_item.ord_id%TYPE;

v_product_id  s_item.product_id%TYPE;

v_item_total   NUMBER(11,2);

CURSOR item_cursor IS

SELECT product_id, price*quantity

FROM   s_item

WHERE ord_id = v_ord_id;

BEGIN

. . .

 

 

커서 열기 : Syntax

•Query를 실행하기 위해 커서를 열고 active set설정
OPEN 커서이름;
•Query 수행후 return 되는 record가 없더라도 error가 발생하지 않습니다.

•Cursor attribute를 이용하여 추출한 후에 수행 결과를 확인하십시오.

 

 

커서에서 데이타 추출 : Syntax

현재 행의 값을 출력 변수로 assign합니다.
FETCH 커서이름 INTO 변수1, 변수2, …;
•Column과 변수의 갯수가 같도록 하십시오.

•Column의 위치에 대응되도록 각 변수를 맞추십시오.

커서가 return 할 record를 갖고 있는지 테스트하십시오.

 

 

커서에서 데이타 추출 : 예

특정한 주문의 라인 항목을 하나씩 검색하십시오.
FETCH item_cursor

INTO  v_product_id, v_item_total;

커서 닫기

행들의 처리가 끝난 다음에 커서를 닫으십시오.
CLOSE 커서이름;
필요하다면 커서를 다시 여십시오.

일단 닫혀진 커서에서 데이타를 추출하려 하지 마십시오.

 

 

Explicit 커서 속성

커서 속성을 이용하여 커서에 대한 상태 정보를 구합니다.

속성 유형 설명
%ISOPEN Boolean 커서가 open되어 있으면 TRUE return
%NOTFOUND Boolean 더이상 fetch할 record가 없으면 TRUE return
%FOUND Boolean 더이상 fetch할 record가 있으면 TRUE return
%ROWCOUNT Number 이제까지 fetch된 전체 행의 수

 

 

Multiple Fetch

루프를 이용하여 Explicit 커서로부터 여러 개의 행을 처리합니다.

반복 때마다 한 행씩 추출합니다.

•%NOTFOUND 속성을 이용하여 불필요한 fetch가 발생되지 않도록 합니다.

•Explicit 커서 속성을 이용하여 fetch가 잘 되었는지를 확인합니다.

 

 

%ISOPEN 속성 : 예

커서가 열려있는 때에만 행을 추출해야 합니다.

추출을 하기 전에 %ISOPEN 속성을 이용하여 커서가 열렸는지 테스트하십시오.

IF item_cursor%ISOPEN THEN

FETCH item_cursor INTO v_product_id, v_item_total;

ELSE

OPEN item_cursor;

END IF;

 

 

%NOTFOUND와 %ROWCOUNT 속성 : 예

•%ROWCOUNT 커서 속성을 이용하여 정확한 숫자만큼의 행을 추출합니다.

•%NOTFOUND 커서 속성을 이용하여 루프를 종료할 시점을 정합니다.

LOOP

FETCH item_cursor

INTO v_product_id, v_item_total;

EXIT WHEN item_cursor%ROWCOUNT > 5

OR item_cursor%NOTFOUND;

v_order_total := v_order_total + v_item_total;

. . .

END LOOP

 

 

커서와 레코드 : 예

•PL/SQL RECORD를 이용하여 active set의 record를 좀더 쉽게 추출할 수 있습니다.
CURSOR emp_cursor IS

SELECT id, salary, start_date, rowid

FROM   s_emp

WHERE dept_id = 41;

emp_record emp_cursor%ROWTYPE;

BEGIN

OPEN emp_cursor;

. . .

FETCH emp_cursor INTO emp_record;

 

 

WHERE CURRENT OF

커서를 이용하여 현재의 행을 갱신하거나 삭제합니다.

커서 선언시 FOR UPDATE 절을 써서 행에 lock을 설정합니다.

•WHERE CURRENT OF 절을 이용하여 explicit 커서에서 현재 행을 참조합니다.

•FOR UPDATE를 썼다면 explicit 커서에서 추출한 후에 바로 commit하지 마십시오.

 

 

WHERE CURRENT OF 절 : 예

커서안의 조건에 따라 행을 갱신하십시오.
 . . .

CURSOR emp_cursor IS

SELECT …

FOR UPDATE;

BEGIN

. . .

FOR emp_record IN emp_cursor LOOP

UPDATE. . .

WHERE CURRENT OF emp_cursor;

. . .

END LOOP;

COMMIT;

END;

 

 

Parameter가 있는 커서 : Syntax

커서가 열리고 query가 실행되면 매개변수 값을 커서에 전달합니다.
CURSOR 커서이름 [(Parameter_name datatype, …)]

IS

select_statement;

다른 active set을 원할때 마다 explicit 커서를 선언 합니다.

 

 

Parameter가 있는 커서 : 예

부서 번호와 직급을 WHERE절로 전달하십시오.
CURSOR emp_cursor

(v_dept NUMBER, v_job VARCHAR2) IS

SELECT last_name, salary, start_date

FROM   s_emp

WHERE dept_id = v_dept

AND title = v_job;

Parameter가 있는 커서 : 예

모든 주문에 대해 총 주문가격을 구입하십시오.
DECLARE

v_current_ord_id             s_ord.id%TYPE;

v_product_id                s_item.product_id%TYPE;

v_item_total                 NUMBER(11,2);

v_order_total                NUMBER(11,2) :=0;

CURSOR ord_cursor         IS

SELECT id

FROM s_ord

ORDER BY id;

CURSOR item_cursor (v_ord_id NUMBER) IS

SELECT product_id, price*quantity

FROM s_item

WHERE ord_id = v_ord_id;

BEGIN

OPEN ord_cursor;

LOOP

FETCH ord_cursor INTO v_current_ord_id;

EXIT WHEN ord_cursor%NOTFOUND;

IF item_cursor%ISOPEN THEN

CLOSE item_cursor;

END IF;

OPEN item_cursor(v_current_ord_id);

LOOP

FETCH item cursor INTO v_product_id, v_item_total;

EXIT WHEN item cursor%NOTFOUND;

v_order_total := v_order_total + v_item_total;

INSERT INTO temp (product_id, cumulative_total, ord_id)

VALUES (v_product_id, v_order_total, v_current_ord<id);

v_current_ord_id);

END LOOP;

v_order_total :=0;

END LOOP;

IF item_cursor%ISOPEN THEN

CLOSE item_cursor;

END IF;

CLOSE ord cursor;

COMMIT WORK;

END;

/

 

CURSOR FOR 루프 : Syntax

•Explicit 커서를 처리하는 가장 좋은 방법입니다.

•OPEN, FETCH, CLOSE가 자동 수행됩니다.

FOR 레코드이름 IN 커서이름 LOOP

     문장1;

     문장2;

. . .

END LOOP;

레코드는 자동적으로 선언되므로 여러분이 레코드를 선언하지는 마십시오.

 

 

CURSOR FOR 루프 : Syntax

남은 항목이 없을 때까지 하나씩 순서대로 특정 주문 항목을 검색하십시오.
FOR item_record IN item_cursor LOOP

          — implicit하게 OPEN, FETCH 발생

v_order_total := v_order_total +

(item_record.price * item_record.quantity);

I := i + 1;

product_id_table (i) := item_record.product_id;

order_total_table (i) := v_order_total;

END LOOP;             — implicit하게 CLOSE 발생

 

 

23. Error 처리

 

 요

예외란 무엇인가?

   – PL/SQL을 실행 동안에 발생하는 error처리를 의미합니다.

어떻게 발생되는가?

   – Oracle 오류가 발생할 때

   – 여러분이 직접 발생시킬 수 있습니다.

처리하는 방법은 무엇인가?

   – 처리기를 이용합니다.

   – 실행환경에 전달합니다.

 

 

예외 처리

해당 예외가 있는 경우 해당 예외가 없는 경우
(DECLARE) (DECLARE)
BEGIN BEGIN Error발생
Error발생 (Exception발생)
(Exception발생) EXCEPTION   󰀻 EXCEPTION  󰀻 해당 예외가 
없는 경우
해당 예외를 통한 END; END;        󰀻
Error를 처리 실행환경으로 Error 전달

 

 

예외 유형

•Predefined Oracle7 Server 예외       — 자동 발생

•Non-Predefined Oracle7 Server 예외   — 자동 발생

•User-defined   — 사용자가 발생시킴

예외 설명 처리 지침
미리 정의된 Oracle7

Server 오류

PL/SQL에서 자주 발생하는 약 20가지 오류 선언할 수 없고 Oracle7 Server가 발생시키도록 함
미리 정의되지 않은

Oracle7 Server 오류

기타 표준 Oracle7

Server Error 처리

선언부에 선언하고 Oracle7 Server가 발생시키도록 함
사용자 정의 오류

 

개발자가 정한 조건이 만족되지 않을 경우 선언부에 선언하고 명시적으로 발생시키도록 함

예외 처리부 : Syntax

EXCEPTION

       WHEN 예외1 [OR 예외2…] THEN

         문장1;

         문장2;

. . .

      [WHEN 예외3 [OR 예외4… THEN

         문장1;

         문장2;

. . . ]

[WHEN OTHERS THEN

        문장1;

        문장2;

. . . ]

 

 

예외 처리부 : Guidelines

맨 마지막에 쓰는 것이 WHEN OTHERS 절입니다.

예외 처리부는 키워드인 EXCEPTION으로 시작합니다.

복수의 예외 처리기를 사용할 수 있습니다.

하나의 예외 처리기만 수행됩니다.

 

 

Predefined Oracle7 Server Error처리

예외 처리 루틴에서 미리 정의된 이름을 참조합니다.

– NO_DATA_FOUND

– TOO_MANY_ROWS

– INVALID_CURSOR

– ZERO_DIVIDE

– DUP_VAL_ON_INDEX

 

 

 

Predefined Exception의 예

EXCEPTION

WHEN NO_DATA_FOUND THEN

v_message := TO_CHAR(v_product_id) || ‘is invalid.’;

WHEN TOO_MANY_ROWS THEN

v_message := ‘Data corruption in S_PRODUCT’;

WHEN OTHERS THEN

v_message := ‘Other error occurred.’;

END;

 

 

Non-predefined Oracle7 Server Error 처리

선언 —-→ 연관 —— —- —-→ 참조
선언부 예외 처리부
예외 선언

 

•PRAGMA EXCEPTION_INIT 입력

발생한 

  예외 처리

 

 

Non-Predefined Error처리 : 예

Oracle7 Server error처리 번호가-2292인 무결성 제약 조건 위반 error를 처리하십시오.
[DECLARE]

e_products_remaining   EXCEPTION;

PRAGMA EXCEPTION_INIT(e_products_remaining, -2292);

. . .

BEGIN

. . .

EXCEPTION

WHEN e_products_remaining THEN

v_message := ‘Referential integrity constraint violated.’;

. . .

END;

 

 

User-defined Exception

선언 연관 —- 참조
선언부 실행부 예외 처리부
예외 선언

 

•RAISE문을 써서

  명시적으로 예외 발행

발생한 예외 처리

 

 

 

User-defined Exception :

재고가 있으면 처리를 중지하고 메시지를 출력하십시오.
[DECLARE]

e_amount_remaining   EXCEPTION;

. . .

BEGIN

. . .

RAISE e_amount_remaining;

. . .

EXCEPTION

WHEN e_amount_remaining THEN

v_message := ‘There is still an amount is stcok.’;

. . .

END;

 

 

예외 처리부에서 사용 가능한 함수

•WHEN OTHERS 예외 처리기

  – 아직 처리되지 않은 모든 예외를 처리

  – 최종 처리기

•SQLCODE

  – Error 번호를 return

•SQLERRM

  – Error 메시지를 return

 

 

SQLCODE 값의 예

SQLCODE   명
0 Error가 없다는 의미입니다.
1 사용자 정의 예외입니다.
+100 NO_DATA_FOUND 예외입니다.
음수 Oracle7 Server error 번호입니다.

 

 

예외 처리부에서 사용가능한 함수 : 예

예상하지 못한 예외에 대한 error 코드와 error 메시지를 저장하십시오.
 . . .

v_error_code     NUMBER;

v_error_message  VARCHAR2(255);

BEGIN

. . .

EXCEPTION

. . .

WHEN OTHERS THEN

ROLLBACK;

v_error_code := SQLCODE;

v_error_message := SQLERRM;

v_output := TO_CHAR(v_error_code)

|| ‘; ‘|| v_error_message;

END;

 

 

실행환경

•SQL*Plus      Error 처리 번호와 메시지를 화면에 출력합니다.

•Developer/2000 트리거에서 error 번호와 메시지를 확인할 수 있습니다.

Forms

•Precompiler    SQLCA를 통해 error 처리 번호를 확인할 수 있습니다.

  응용프로그램

 

예외 전달

DECLARE

. . .

e_no_rows         exception;

e_integrity          exception;

PRAGMA EXCEPTION_INIT(e_integrity, -2292);

BEGIN

FOR c_record IN emp_cursor LOOP

서브 블록은 예외를 자체적으로 처리하거나 서브블록을 내포하고 있는 블록에 예외를 전달할 수 있습니다.

 

 

 

 

 

BEGIN

SELECT . . .

UPDATE . . .

IF SQL%NOTFOUND THEN

RAISE e_no_rows;

END IF;

EXCEPTION

WHEN e_integrity THEN. . .

WHEN e_no_rows THEN . . .

END;

END LOOP;

EXCEPTION

WHEN NO_DATA_FOUND THEN. . .

WHEN TOO_MANY_ROWS THEN. . .

END;

24. PL/SQL 요약

 

PL/SQL의 장점

PL/SQL의 장점

모듈화

변수 선언

절차적 언어 구조를 갖는 프로그램

예외 처리의 수행

•Stored Subprogram과 응용 서브 프로그램의 통합

성능 향상

 

 

PL/SQL 블록

•4개의 부로 구성

헤더부(Header)

선언부(Declarative)

실생부(Executable)

예외부(Execption)

과정에서 다룬 두 가지 유형의 블록

– Anonymous Block

서부 프로그램

  – 일련의 작업을 저장하는 프로시저

  – 값을 반환하는 함수

 

 

PL/SQL에서의 DML

•SELECT문은 INTO 절이 있으며 한 개의 행을 반환해야 합니다.

•INSERT는 새로운 행을 추가합니다.

•UPDATE는 행을 변경합니다.

•DELETE는 행을 제거합니다.

•COMMIT은 모든 미결 중인 변경 사항을 영구적인 것으로 만들고 현재의 트랜잭션을 

  종료합니다.

•ROLLBACK은 모든 미결 중인 변경 사항을 취소하고 현재의 트랜잭션을 종료합니다.

 

PL/SQL 제어문

•IF조건문

  – 조건 값에 따라 선택적으로 작업 수행

루프 문

  – BASIC 루프

  – FOR 루프

  – WHILE 루프

커서와 CURSOR FOR 루프

•Oracle7 Server Error나 사용자 정의 예외를 처리하는 예외 처리부

 

 

SQL*Plus

형식화, 계산 수행, 보고서의 형태로 query 결과를 출력합니다.

입력, 편집, 저장, 검색 및 SQL 명령과 PL/SQL 요약 블록을 실행합니다.


Comments

comments

haisins

오라클 DBA 박용석 입니다. haisins@gmail.com 으로 문의 주세요.

댓글 남기기

이메일은 공개되지 않습니다. 필수 입력창은 * 로 표시되어 있습니다