oracle sql , pl/sql 설명

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

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

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

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

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

FROM      테이블명

[WHERE    Query조건(들)]

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

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

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

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

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

•SELECT 절

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

•FROM 절

2 FROM   s_dept;

2 FROM   s_emp;

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

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

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

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

– 우측 정렬 : 숫자 데이터

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

  있습니다.

– 더하기 +

– 빼기   –

– 곱하기 *

– 나누기 /

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

2 FROM     s_emp;

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

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

2 FROM       s_emp;

                  ・・・・・   Velasquez 2500 30100

2 FROM       s_emp;

                  ・・・・・   Velasquez 2500 31200

•특히 계산이 유용합니다.

•Column명 바로 뒤에 옵니다.

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

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

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

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

2 FROM      s_emp;

                ・・・・  CarmenVelasquez

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

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

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

3 FROM     s_emp;

  을 의미합니다

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

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

2           salary*commission_pct/100 COMM

3 FROM     s_emp;

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

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

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

– NVL(title, ‘NO Title Yet’)

– NVL(salary, 1000)

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

3 FROM     s_emp;

2 FROM   s_dept;

2 FROM   s_dept;

2 FROM    s_emp;

•ASC – 올림차순(Default)

•DESC – 내림차순

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

2 FROM      s_emp

3 ORDER BY last_name;

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

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

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

– 내림차순시 가장 먼저 옴

2 FROM       s_emp

3 ORDER BY  2;

2 FROM       s_emp

3 ORDER BY  dept_id, salary DESC;

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

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

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

– Column명, 표현식, 상수

– 비교 연산자

– 문자(Literal)

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 (널값이 아닌)

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

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

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

2 FROM   s_emp

3 WHERE  last_name=’Magee’;

2 FROM   s_emp

3 WHERE start_date BETWEEN ’09-may-91′

4 AND    ’17-jun-91′

2 FROM    s_dept

3 WHERE  region_id IN(1,3);

  하여야 합니다.

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

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

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

2 FROM   s_emp

3 WHERE last_name LIKE ‘M%’;

2 FROM   s_emp

3 WHERE start_date LIKE’%91′;

2 FROM    s_emp

3 WHERE  last_name LIKE ‘_a%’

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

2 FROM    s_customer

3 WHERE  seles_rep_id IS NULL;

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

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

2 FROM    s_emp

3 WHERE  dept_id = 41

4 AND     title = ‘Stock Clerk’;

2  FROM   s_emp

3  WHERE dept_id = 41

4  OR      title = ‘Stock Clerk’;

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

IS NULL, BETWEEN)

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

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

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

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

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

•도움말을 얻습니다.

(Column명, NOT NULL column, Datatype)

•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 명령

  – SQL 명령

  – PL/SQL 명령

  – 아이콘을 더블 클릭

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

•Command line 경우

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

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

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

•그룹의 결과 출력

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

•Column datatype을 변경할 경우

  – 문자

  – 숫자

  – 날짜

  – 변환

•복수행 함수

  – 그룹

→

→

→

•데이타 값을 조작합니다.

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

•각각의 행에 적용됩니다.

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

•Datatype을 바꾸어 줍니다.

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

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

column2\expression)

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(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(1600,300) → 100

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

•시간을 날짜에 더합니다.

(date1. date2)

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

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

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

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

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

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

•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′,’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

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

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

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

YY가 됩니다.

TO_CHAR(date, ‘fmt’)

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

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

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

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

•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

1995

2001

2001

27-OCT-17

27-OCT-17

27-OCT-95

2017

2017

1995

1917

2017

2095

  두자리 값

2           ‘fmDdspth “of” Month YYYY

3           fmHH:MI:SS AM’) HIREDATE

4 FROM    s_emp

5 WHERE  start_date LIKE ‘%91’;

9

0

$

L

.

,

MI

PR

EEEE

V

B

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

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

달러 기호를 나타냅니다.

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

소숫점을 Display 합니다.

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

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

음수를 괄호로 묶습니다.

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

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

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

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_DATE(’10 September 1992′,’dd Month YYYY’)

– Use format elements

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

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

3 FROM   s_emp

4 WHERE  manager_id IS NULL;

– Result1 = TO_CHAR(manager_id)

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

– NVL(Result1, ‘No Manager’)

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

3             ‘fmDay, Month ddth, YYYY’)

4             ‘New 6 Month Review”

5 FROM      s_ord

6 ORDER BY date_ordered;

– Result1 = ADD_MONTHS(date_ordered, 6)

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

– Result2 = NEXT_DAY(Result1, ‘FRIDAY’)

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

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

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

•조인 방법

– Equijoin

– Non-equijoin

– Outer join

– Self join

– 조인 조건을 생략한 경우

– 조인 조건이 잘못된 경우

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

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

  하십시오.

SELECT   table.column, table.column

FROM     table1, table2

WHERE   table1.column1 = table2.column2;

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

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

  사용합니다.

  로 사용합니다.

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

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

  여 구별합니다.

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

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

  error 발생을 방지합니다.

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;

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

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

2 FROM    emp e, salgrade s

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

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

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

FROM    table1, table2

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

  display 합니다.

2 FROM       s_emp e, s_customer c

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

4 ORDER BY  e.id

•Outer join을 포함하는 조건은

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

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

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

  하는 것과 같이 합니다.

2           manager.last_name

3 FROM    s_emp worker, s_emp manager

4 WHERE  worker.manager_id = manager.id;

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

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

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

SELECT     column, group_function

FROM       table

[WHERE     condition]

[GROUP BY  group_by_expression]

[HAVING     group_condition]

[ORDER BY  column]

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

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

2 FROM   s_emp

2          MIN(salary), SUN(salary)

3 FROM   s_emp

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

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

2 FROM   s_emp

3 WHERE dep_id  = 31;

2 FROM   s_emp

3 WHERE dept_id = 21;

SELECT     column, group_function

FROM       table

[WHERE     condition]

[GROUP BY  group_by_expression]

ORDER BY  column];

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

  명을 추가합니다.

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

2 FROM    s_emp

3 WHERE  dept_id = 41;

?????   ??????????????   ?????????????????

2    Ngao                     41

6    Urguhart                  41

16    Maduro                   41

17    Smith                     41

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

 ? 41은 4번 나타납니다.

SELECT     column, group_function

FROM       table

[WHERE     condition]

[GROUP BY group_by_expression]

[ORDER BY column];

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

  Column명을 추가합니다.

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

2  FROM      s_emp

3  WHERE    dept_id = 41

4  GROUP BY dept_id;

????????????   ???????????

41          4

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

2 FROM    s_customer

3 WHERE  credit_rating;

2 FROM      s_emp

3 WHERE    title NOT LIKE ‘VP%’

4 GROUP BY title

5 ORDER BY SUM(salary)

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

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

2 FROM       s_emp

3 GROUP BY  title;

  BY절에 와야 합니다.

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

2 FROM     s_dept;

SELECT region_id, COUNT(name)

*

ERROR at line 1;

ORA-00937: not a single-group grup function

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

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 순서에 의해 결과의 기본 순서가 결정됩니다.

2 FROM       s_emp

3 GROUP BY  dept_id, title;

2 FROM    s_emp

3 WHERE  last_name LIKE ‘V%’;

? WHERE절

????????????????  ??????????????

Velasquez     President

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

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 절(그룹들을 제한합니다.)

???????????????????   ?????????????????????   ???????????????????????????????????

Sales Representative       $17,712.00                              5

Stock Clerk                $11,388.0                              10

Warehouse Manager        $14,776.8                               5

SELECT    column, group_function

FROM      table

[WHERE    condition]

[GROUP BY group_by_expression]

[HAVING    group_condition]

[ORDER BY column];

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

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

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

2 FROM      s_emp

3 WHERE    title NOT LIKE ‘VP%’

4 GROUP BY title

5 HAVING    SUM(salary) > 5000

6 ORDER BY SUM(salary);

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

  니다.

2 FROM      s_emp

3 GROUP BY dept_id

4 HAVING    SUM(salary) > 4000;

SELECT…

(SELECT…

FROM…

WHERE…);

SELECT   select_list

FROM     table

WHERE   expr operator

(SELECT select_list

FROM  table);

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

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

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

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

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

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

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

Nested query

Main query

FROM    s_emp

WHERE  last_name = ‘Biri’;

FROM    s_emp

WHERE  dept_id =

FROM    s_emp

WHERE  title =

 display 하기 위한

 SQL문장을 작성합니다.

FORM    s_emp

WHERE  last_name = ‘Smith’

 위한 SQL 문장을

 작성합니다.

2  FROM    s_emp

3  WHERE  title =

4  (SELECT title

5  FROM   s_emp

6  WHERE  last_name = ‘Smith’);

SQL이 Smith의 직책을 

결정하게 합니다.

2 FROM    s_emp

3 WHERE  salay <

4          (SELECT AVG(salary)

5           FROM   s_emp);

  가 발생됩니다.

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

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

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

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

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);

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

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

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);

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

[REP[LACE]|APP[END]]

|OFF|OUT]

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

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

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

•데이타 출력형식 변경

•중복 데이터 제거

•소계와 총계의 표시

•Column의 heading 변경

  있습니다.

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

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

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

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

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

COL salary JUSTIFY LEFT FORMAT $99,990.00

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

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

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

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

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

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

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

7. 스크립트 파일 저장

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

  – 공백 추가

  – 중복값 삭제

•Query에 ORDER BY 절 사용

•하나의 BREAK 명령만 허용

OF   column…

ON   break column

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

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

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

COMPUTE SUM LABEL ‘Subtotal’ –

OF salary ON title

COMPUTE SUM LABEL ‘Grand Total’ –

OF salary ON REPORT

  – 단일 앰퍼센드(&)

  – 이중 앰퍼센드(&&)

  – DEFINE과 ACCEPT 명령

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

  – 날짜 범위의 명시

  – 특정 데이터 제한

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

  있습니다.

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

2 FROM    s_emp

3 WHERE  dept_id = &department_number ;

Enter value for department_number : 31

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

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

  합니다.

2 FROM    s_emp

3 WHERE  title = ‘&job_title’ ;

Enter value for job_title : Stock_Clerk

•WHERE 조건 •ORDER BY 절 •Column명

•테이블명 •전체 SELECT 문장

2 FROM    s_ord

3 WHERE  &condition;

Enter value for column_name : total

Enter value for condition : payment_type = ‘CASH’

  사용합니다.

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

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

  니다.

2 FROM    s_ord

3 WHERE  &column_name;

Enter value for column_name : total

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

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

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

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

  니다.

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

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

ACCEPT p_salary NUMBER PROMPT ‘Salary amount: ‘

ACCEPT pswd CHAR PROMPT ‘Password: ‘ HIDE

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

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

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

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

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

  위치는 중요합니다.

  – Table      데이터 저장

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

  – Sequence  Primary Key 값을 생성

  – Index      Query에 대한 성능 향상

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

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

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

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

CREATE TABLE [스키마.] 테이블

         (column datatype [DEFAULT 식]

         [column 제약조건],

….

         [테이블제약조건]);

•CREATE TABLE

•A storage area

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

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

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

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

•1-30자 길이로,

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

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

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

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

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

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

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

•Oracle7의 제약조건 유형

– NOT NULL  – UNIQUE

– PRIMARY KEY  – FOREIGN KEY

– CHECK

•제약조건 생성

  – 테이블 생성과 동시

  – 테이블이 생성된 후

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

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

(column, . . .),

•Column 레벨에서 정의

phone     VARCHAR2(15) NOT NULL, …

last_name  VARCHAR2(25)

CONSTRAINT friend_last_name_nn NOT NULL, …

  Column의 조합을 지정

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

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

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

CONSTRAINT s_emp_phone_uk UNIQUE,…

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

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

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

CONSTRAINT s_emp_id_pk PRIMARY KEY, …

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

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

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

CONSTRAINT s_emp_dept_id_fk

REFERENCES s_dept(id),…

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

•REFERENCES

  – 참조될 테이블 column을 정의

•허용되지 않는 식 :

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

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

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

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

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

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

  PRIMARY KEY 제약조건 column은 제외

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

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

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

31

32

33

Sales

Sales

Sales

1

2

3

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)));

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

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

. . .

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

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

•데이타 사전(Data Dictionary)

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

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

•Oracle7 Server가 관리

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

•데이타 사전에 저장된 정보

  – Oracle7 Server에 Login 할 수 있는 USER명

  – 사용자에게 허가된 권한

  – 데이터베이스 객체명

  – 테이블 제약조건

  – Auditing 정보

– USER_

– ALL_

– DBA_

– V$

•기타 뷰

– DICTIONARY

– IND

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

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

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

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

일반적으로 DBA에게만 허용

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

USER_INDEXES의 synonym

2 FROM DICTIONARY;

2 FROM   user_objects

3 WHERE  objects_type = ‘TABLE’ ;

2 FROM   user_objects;

  테이블에 대해 검색

2 FROM   dictionary

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

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

2          search_condition, r_constraint_name

3 FROM   user_constraints

4 WHERE  table_name = ‘S_EMP’;

  수 있습니다.

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

2 FROM   user_cons_columns

3 WHERE  table_name = ‘S_EMP’;

VALUES      (값 [값…]);

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

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

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

2 VALUES      (12, ‘MIS’);

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

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

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

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

2 FROM   s_emp

3 WHERE  id = 26;

 ????  ??????????????  ???????????????  ?????????  ??????????????

26  Smith         Donna        SFCL26   01-JAN-96

  – 특정 날짜와 시간 값을 삽입하기 위해 to_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));

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

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

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 PROMPT 명령은 사용자가 정의한 텍스트를 출력합니다.

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  [테이블]

SET      column = 값 [, column = 값…]

[WHERE  조건];

2 SET     dept_id = 10

3 WHERE  id = 2;

2 SET     dept_id = 32, salary = 2550

3 WHERE  id = 1;

2 SET     commission_pct = 10;

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 FROM 테이블

[WHERE   조건];

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

2 FROM   test;

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

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

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

  – CREATE 같은 DDL 명령 실행

  – GRANT 같은 DCL 명령 실행

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

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

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

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

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

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

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

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

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

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

2 SET     dept_id =54

3 WHERE  id = 2;

•이전 상태로 복구됩니다.

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

SQL>ROLLBACK;

Rollback complete.

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

SQL>SAVEPOINT update_done;

Savepoint created.

SQL>INSERT…

SQL>ROLLBACK TO update_done;

Rollback complete.

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

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

  – Column의 추가와 변경

  – 제약조건의 추가나 삭제

  – 제약조건의 enable, disable

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

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

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

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

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

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

ALTER TABLE 테이블 명

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

2 ADD    (comments VARCHAR2(255));

Table altered.

ALTER TABLE 테이블 명

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

•Column의 크기를 늘입니다.

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

  수 있습니다.

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

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

  치게 됩니다.

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

예)

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

2 MODIFY    (title VARCHAR2(50));

Table altered.

SQL>ALTER TABLE 테이블명

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

•제약조건의 Enable이나 Disable

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

2 ADD   CONSTRAINT s_emp_manager_id_fk

3 FOREIGN KEY (manager_id)

4 REFERENCES s_emp (id);

2 DROP CONSTRAINT s_emp_manager_id_fk

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

2 DROP PRIMARY KEY CASCADE;

2 ENABLE CONSTRAINT s_dept_id_fk

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

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

2 DISABLE CONSTRAINT s_dept_id_fk CASCADE;

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

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

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

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

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

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

  – DDL 명령입니다.

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

2 IS ‘Employee Information’;

ALL_COL_COMMENTS

USER_COL_COMMENTS

ALL_TAB_COMMENTS

USER_TAB_COMMENTS

•공유할 수 있는 객체

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

•응용 프로그램 코드를 대체

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

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

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

CREATE SEQUENCE NAME

[INCREMENT BY n]

[START WITH n]

[{MAXVALUE n | NOMAXVALUE}]

[{MINVALUE n | NOMINVALUE}]

[{CYCLE | NOCYCLE}]

[{CACHE n | NOCACHE}]

  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입니다.

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

2 INCREMENT BY 1

3 START WITH 51

4 MAXVALUE 9999999

5 NOCACHE

6 NOCYCLE;

2 FROM user_sequences;

  – 매번 고유한 값을 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 값

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

2 FROM   SYS.dual;

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

  음 값을 보십시요.

CREATE SEQUENCE NAME

[INCREMENT BY n]

[{MAXVALUE n | NOMAXVALUE}]

[{MINVALUE n | NOMINVALUE}]

[{CYCLE | NOCYCLE}]

[{CACHE n | NOCACHE}]

  할 수 있습니다.

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

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

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

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

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

  액세스를 제한합니다.

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

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

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

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

AS Subquery

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

[WITH READ ONLY]

  구성될 수 있습니다.

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

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

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

  이용합니다.

※ 옵션

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

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

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

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

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

2 AS SELECT id, last_name, title

3 FROM      s_emp

4 WHERE     dept_id = 45;

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

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

3 FROM      s_emp

4 WHERE     dept_id = 41;

  각 column명을 변경하십시요.

2 (id_number, employee, job)

3 AS SELECT id, last_name, title

4 FROM      s_emp

5 WHERE     dept_id = 45;

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;

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

  – 조인 조건

  – 그룹 함수

  – GROUP BY 절

  – DISTINCT 명령

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

  – 위의 조건

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

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

  – 위의 조건

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

2 AS SELECT *

3 FROM      s_emp

4 WHERE     dept_id = 41

5 WITH CHECK OPTION CONSTRAINT empvu41_ck;

  error가 발생할 것입니다.

2 AS SELECT id, last_name, title

3 FROM      s_emp

4 WHERE     dept_id = 45

5 WITH READ ONLY;

  합니다.

2 FROM   user_views;

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

•테이블과는 독립적

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

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

  자동적으로 생성

사용자 생성

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

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

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

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

※ B*Tree

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

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

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

•Non-unique 인덱스

  – Query의 속도 증가

•단일 행 인덱스

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

•연결 또는 조합 인덱스

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

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

2 ON             s_emp(last_name);

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

•많은 NULL 값을 갖는 column일 때

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

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

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

•테이블이 작을 때

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

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

•테이블이 자주 변경될 때

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

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’;

  있어야 합니다.

  – 시스템 보안

  – 데이터 보안

•시스템 권한(System Privilege)

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

•객체 권한(Object Privilege)

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

•스키마

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

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

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

  – 사용자 삭제 (DROP USER)

  – 테이블 삭제 (DROP ANY TABLE)

  – 테이블 백업 

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

2 IDENTIFIED BY tiger;

  있습니다.

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

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

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

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

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

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

                            있도록 한다.

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

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

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

ON     객체

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

[WITH GRANT OPTION];

2 ON     s_emp

3 TO     sue, rich;

2 ON     s_dept

3 TO     scott, manager;

2 ON     s_dept

3 TO     scott

4 WITH GRANT OPTION

2 ON     s_ord

3 TO     PUBLIC;

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

ON      객체

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

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

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

FROM   객체;

DELETE

DROP

TRUNCATE

RENAME

SAVEPOINT

  – Oracle7 Server의 사용자 이름

  – 사용자에게 허가된 권한

  – 데이터베이스 객체 이름

  – 테이블 제약조건

  – Auditing 정보

•자주 액세스되는 테이블

– DICTIONARY

– USER_OBJECTS

  – OBJ(USER_OBJECTS에 대한 Synonym)

– USER_CONSTRAINTS

– USER_SEQUENCES

– SET, SHOW, CLEAR

•형식 명령

– COLUMN, BTITLE, TTITLE, BREAK, COMPUTE

•파일 조작

– EDIT, GET, SAVE, SPOOL, START

•상호 작용

– ACCEPT, PROMPT

•보고서를 작성합니다.

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

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

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

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

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

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

•파일에 PL/SQL 블록을 작성

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

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

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

•VARIABLE

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

•PRINT

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

•EXECUTE

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

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

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

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

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

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

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

PRINT 변수

VARIABLE g_year_sal NUMBER

DECLARE

v_sal NUMBER := &p_sal;

BEGIN

:g_year_sal := v_sal*12;

END;

/

PRINT g_year_sal

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

  – Stored subprogram의 경우에는 USER_ERRORS data dictionary 테이블에 저장됩니다.

SQL> SELECT   *

2  FROM     user_errors;

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

-—————————————————————————————————————

   짝수를 입력했습니다.

•함수의 반환값을 global 변수에 assign합니다.

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;

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

•3가지 종류의 PL/SQL 프로그램 단위가 있음.

– 동작을 수행하는 프로시저

– 값을 계산하는 함수

– 논리적으로 연관된 프로시져와 함수로 이루어진 패키지

•응용 서브프로그램으로 개발되어졌거나 데이터베이스에 저장되어 있음.

– 서브 프로그램명, 유형, argument

DECLARATIVE – Optional

– 변수

EXECUTABLE – Mandatory

– SQL 문

– PL/SQL 제어문

EXCEPTION- Optional

– Error 발생시 수행할 동작

END; – Mandatory

[(Parameter,…)]

IS

pl/sql_block;

  [{ := |DEFAULT}식]

•어떠한 Parameter 라도 사용 가능합니다.

•IS로 PL/SQL 블록을 시작합니다.

•Local 변수 선언은 IS와 BEGIN사이에 선언하십시오.

(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;

/