싸미~*

SQL) 오라클 페이징

.Tech/SQL

오라클 페이징 처리에는 두가지 방법이 있다고 한다.

 

1. HINT(힌트) 이용

/*

* 추후 내용 보강하겠슴.

*/

 

2. row_number() 이용

예제1)

SQL 문

select * from

        (select tb.*, row_number() over(order by title asc) as num

                                                                          from TABLE01 tb )

where num between 10 and 20

설명

목적: TABLE01 인 tb에서 모든 field 값들을 얻어온다.

   01. tb에서 title 내림차순으로 field값을 얻어오고,

   02. 그 결과 중, 순서가 10~20 사이에 값들을 얻어온다.

예제2)

SQL 문

select * from

        (select seqno , title, name, memo, row_number() over(order by seqno desc, id)

                    as num

                   from tb where userid='samsik' )

where num between startROW and (endROW-1)

 

3. 참고

1) row_number() 는 SQL 표준이라고 한다.

  rownum 은 표준이 아니라고 나와있는데, 아래 참고하자.

  * 위키-Row_Number

  * Comparison of different SQL implementations

 

2) Larget Result Set 이 있을때, ROW_NUMBER()사용이 그리 빠르지 않다는 것

  * 여기 참조

신고

SQL)강의- 제 16 장 시스템 권한(Privileges)

.Tech/SQL

출처 : http://naver.kaist.ac.kr/oracle_sql/s16.htm

 

1. 시스템 권한

    ◈80개 이상의 권한이 있다.
    ◈DBA는 상위 레벨의 시스템 권한을 갖는다.
    ◈새로운 사용자 생성
    ◈사용자 삭제
    ◈테이블 삭제
    ◈테이블 백업
    ◈DBA는 SQL 명령인 CREATE USER를 써서 사용자를 생성한다.
    ◈시스템 권한은 대개 데이터 베이스 관리자 (DBA)가 부여한다.

    시스템 권한

    할 수 있는 일

    CREATE USER

    다른 Oracle 사용자를 생성할 수 있게 한다.

    DROP USER

    다른 사용자를 삭제 할 수 있다.

    DROP ANY TABLE

    스키마에 있는 테이블을 삭제 할 수 있다.

     

사용자 생성

    ◈DBA는 여러 시스템 권한을 사용하여 Oracle의 새로운 사용자를 생성 할 수도 있다.

      CREATE   USER  사용자    IDENTIFIED BY  암호 ;

    ◈사용자는 생성되는 사용자의 이름이고, 암호는 로그인을 할 때 사용된다.

    ♠실습 : 

    SQL> CREATE USER scott IDENTIFIED BY tiger;

     

사용자 시스템 권한

    ◈사용자를 생성하면 DBA는 GRANT 명령을 실행하여 사용자에게 권한을 부여할 수 있다.
    ◈전형적인 사용자 권한

    시스템 권한

    권한을 통해 할 수 있는 일

    CREATE SESSION

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

    CREATE TABLE

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

    CREATE SEQUENCE

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

    CREATE VIEW

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

    CREATE PROCEDURE

    사용자가 Store Procedure 또는 패키지를 생성할 수 있도록 한다.

    ◈DBA는 사용자에게 시스템 권한을 할당하기 위해 GRANT명령을 이용한다.

      GRANT 권한[,권한…] TO 사용자[,사용자…] ;

    ◈사용자가 권한을 받으면 즉시 그 권한을 사용할 수 있다.

    ♠실습 :  

    SQL> GRANT  Create table, create sequence, create view  TO scott;

     

2. Role이란 무엇인가?

Role이란?

    ◈Role은 사용자에게 허가할 수 있는 관련된 권한들의 그룹이다.
    ◈이러한 role을 이용하면 권한 부여와 회수를 쉽게 할 수 있다.
    ◈한 사용자가 여러 role을 액세스할 수 있고 다른 여러 사용자에게 같은 role을 지정할 수 있다.
    ◈DBA가 role을 생성해야 하고 다음에 권한을 role에 지정하고 사용자에게 role을 부여한다.
    ◈Syntax : CREATE ROLE Role;
                     Role; - 생성되는 role의 이름임.
    ◈Role이 생성되면 DBA는 role에 권한을 지정한 것과 마찬가지로 사용자에게 role을 지정하기 위해
       GRANT명령을 사용할 수 있다.
     

    ♠실습 : Role을 생성하고 생성된 role에 테이블과 뷰를 생성할 수 있는 권한을 설정한다.
               이 role을 Velasquez와 Ropeburn에게 준다. 

    SQL> CREATE ROLE manager;
    SQL> GRANT create table. Create view TO manager;
    SQL> GRANT manager TO velaquz. Ropeburn;

     

 3. 암호 변경

    ◈사용자가 생성되었을 때 각 사용자는 DBA가 초기화한 암호를 갖게 된다.
    ◈ALTER USER 명령을 써서 암호를 변경한다.
    ◈ Syntax  :   ALTER USER  사용자  IDENTIFIED BY  암호 ;
    ◈사용자는 사용자의이름이고, 암호는 새로운 암호임.

    ♠실습 :  

    SQL> ALTER USER scott IDENTIFIED BY lion;

     

4. 객체 권한 부여

    ◈DBA는 객체 권한을 사용자에게 부여하여 특정 테이블,뷰,Sequence,Stored Procedure에 대한
       작업을 수행할 수 있게 한다.
    ◈소유자는 객체에 대한 모든 권한을 갖는다.
    ◈권한은 객체 유형에 따라 매우 다양한다.
    ◈소유자는 소유한 객체에 대해 특정 권한을 다른 사용자에게 줄 수 있다.

    객체권한

    테이블

    Sequence

    Procedure

    Snapshot

    ALTER

     

     

     

    DELETE

     

     

     

    EXECUTE

     

     

     

     

    INDEX

     

     

     

     

    INSERT

     

     

     

    REFERENCES

     

     

     

     

    SELECT

     

    UPDATE

     

     

     

    ◈ 객체권한부여 Syntax

      GRANT  객체 권한[(Column)]
      ON  객체
      TO {사용자|Rrle|PUBLIC}
      [WITH GRANT OPTION] ;

    ◈객체권한은 허가되는 객체권한이고,
    ◈ALL은 모든 객체권한.
    ◈Column은 권한이 허가된 테이블이나 뷰의 column.
    ◈ON 객체는 권한이 허가된 객체.
    ◈TO는 권한을 허가받을 사용자.
    ◈PUBLIC은 모든 사용자에게 객체권한을 허가한다.
    WITH GRANT OPTTION은 권한을 받은 사용자가 다른 사용자에게 권한을 부여할 수 있도록 한다. 

    ♠실습 : s_emp 테이블을 select와 update를 할 수 있도록 scott와 Rich에게 권한을 주도록 하라. 

    SQL> GRANT select,update   ON    s_emp    TO    scott, rich
                 WITH GRANT OPTION;

     

    ♠실습 : scott이 시스템의 모든 사용자에게 s_dept테이블을 Query할 수 있게 하라.  

    SQL> GRANT update(first_name,last_name)  ON    s_emp   TO    PUBLIC;

     

5. 부여된 권한 확인

    ◈부여받거나 부여한 권한을 확인하기 위해 Dictionary뷰를 액세스한다.
    ◈예를 들어,DELETE 권한이 없는 테이블의 행을 삭제하는 것과 같이, 허가되지 않은 작업을
      수행 하려 한다면 Oracle7 Server는 작업을 허용하지 않을 것이다. Oracle7 Server의 오류 메시지인
      "테이블이나 뷰가 없습니다"를 받게 되면 다음 중 한 가지를 했다는 뜻이다:
    ◈않는 테이블이나 뷰의 이름 사용
    ◈적절한 권한이 없는 테이블이나 뷰에 대한 작업 시도 

    부여받은 권한은 무엇인가?

        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에 대하여 사용자에게 부여된 객체 권한

     

6. 객체에 대한 권한 회수

    ◈REVOKE 명령을 써서 다른 사용자에게 허가된 권한을 취소한다.
    ◈WITH GRANT OPTION에 의해 다른 사용자에게 허가된 권한도 취소된다.
    ◈Alice는 S_DEPT 테이블에 대해 Scott에게 준 SELECT와 INSERT권한을 회수한다.
    ◈ Syntax

      REVOKE  [권한 (, 권한...):ALL}
      ON   객체
      FROM {사용자[, 사용자...]:role |PUBLIC}  
       

    ♠실습 : 

    SQL> REVOKE  select,  insert      ON  s_dept   FROM  scott

     

7. Synonym생성

    ◈Synonym을 생성하여 객체를 객체에 대한 액세스를 간단하고 쉽게 하게 한다.
    ◈다른 사용자가 소유한 테이블에 대한 참조
    ◈긴 객체 이름을 짧게 만든다.
    ◈다른 사용자가 소유한 테이블을 참조하려면 테이블 생성자의 이름 뒤에 점을 찍고 테이블 이름을
      써야 한다.
    ◈스키마로 객체 이름을 수식할 필요가 없도록 Synonym을 만들어 객체에 대한 다른 이름을 쓴다.
    ◈뷰와 같이 긴 이름으로 된 객체에 이런 방법을 쓰면 아주 유용하다.
    ◈ Syntax

      CREATE  [PUBLIC]  동의어
          FOR  객체;

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

    Guideline : Private Synonym은 동일한 사용자가 소유한 다른 객체 명과는 달라야 한다.
     

    ♠실습 : Scott이 Alice의 S_DEPT 테이블에 대해 S_DEPT라는 Private Synonym을 만든다. 

    SQL> CREATE  SYNONYM  s_dept   FOR    alice.s_dept;

     

    ♠실습 : 빠른 참조를 위해 DEPT_SUM_VU에 대한 Synonym을 만든다.  

    SQL> CREATE  SYNONYM  d_sum    FOR    dept_sum_vu;

     

    ♠실습 : DBA는 모든 사용자가 액세스할 수 있는 Public Synonym을 만들 수 있다 

    SQL> CREATE  PUBLIC  SYNONYM  s_dept   FOR    alice.s_dept;

     

    Synonym 삭제

    Synonym을 삭제하려면 DROP SYNONYM 명령을 쓰시오. DBA만이 Public Synonym 을 삭제할 수 있다. 

    SQL> DROP  SYNONYM  s_dept;

     

신고

SQL)강의- 제 15 장 인덱스 생성

.Tech/SQL

출처 : http://naver.kaist.ac.kr/oracle_sql/s15.htm

1. 인덱스 생성 개요

    ◈인덱스는 포인터를 써서 행을 빠르게 검색할 수 있는 데이터베이스 객체이다.
    ◈인덱스는 명시적 또는 자동적으로 생성 할 수 있으며 생성후 바로 사용된다.
    ◈Column에 대한 인덱스가 없으면 한 테이블 전체를 검색하게 된다.

인덱스란?

    ◈인덱스는 테이블의 값을 빠르게 액세스 하도록 하는 데이터베이스 객체이다.
    ◈데이터를 빠르게 찾기 위한 B*트리를 써서 디스크 입출력 횟수를 줄인다.
    ◈인덱스를 만들면 사용자가 직접 조작할 필요가 없게 된다.
    ◈인덱스는 논리적으로도 물리적으로도 테이블과는 독립적이다.
    ◈언제든지 생성하거나 삭제할 수 있으며 테이블이나 다른 인덱스에 영향을 주지 않는다는 의미이다.

인덱스 생성방법

    1.자동생성

           ◈테이블 정의에 PRIMARY KEY나 UNIQUE 제약조건을 정의할 때 unique 인덱스가 자동적으로 생성
     

    2.사용자가 생성

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

인덱스의 구조(B*Tree)

    ◈각 인덱스는 페이지로 구성된 포인터(또는 ROWID)와 Column값으로 구성된다.
    ◈서버는 포인터를 갖는 값이 있는 노드에 이를 때까지 트리를 탐색한다.
    ◈B*Tree인덱스 구조를 이용한다.
    ◈이 트리는 어떤 행에 대한 액세스 횟수도 동일하게 한 이진의 균형탐색 구조이다.
    ◈행이 테이블의 시작이나 중간, 또는 끝에 있어도 거의 같은 횟수 내에 지정된 값을 액세스하는
       효율적인 방법이다.
    ◈인덱스는 트리에 정렬된 여러 개의 저장장소 페이지로 구성된다.
    ◈각 페이지는 키 값이 데이터 자체의 위치를 가리킬 때까지 구조의 아래 쪽으로 향하는 페이지에 대한
       포인터와 일련의 키값을 갖고 있다.

인덱스의 유형

    유형

    설명

    Unique인덱스

    지정된 Column의 값이 고유함을 보장

    Non_Unique인덱스

    데이터를 질의할 때 가장 빠른 결과를 보장

    단일 Column인덱스

    오직 한 개의 Column만이 인덱스에 있음

    연결 인덱스 또는 조합 인덱스

    성능이나 고유함 검사를 목적으로 인덱스에 16개까지의 Column을 포함

     

2. 인덱스 생성

    ◈Syntax

      CREATE  INDEX  인덱스명
         ON 테이블(Column[,Column]...);

인덱스 생성:Guideline

    1. 인덱스를 만드는 때

      ◈ WHERE절이나 조인 조건에서 Column을 자주 이용할 때
      ◈ Column이 넓은 범위의 값을 가질때
      ◈ 많은 NULL 값을 갖는 Column일 때
      ◈ 테이블의 Data가 많고 그 테이블에서 Query되는 행의 수가 전체의 10-15%정도 일때
       

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

      ◈ 테이블이 작을때
      ◈ Column이 질의의 조건으로 사용되는 경우가 별로 없을 때
      ◈ 대부분의 질의가 행의 10-15% 이상을 검색한다고 예상될때
      ◈ 테이블이 자주 변경될때
       

    ♠실습 : s_emp테이블의 last_name column에 Query엑세스 속도를 빠르게 하기 위한 인덱스를 만드시오.

    SQL> CREATE  INDEX  s_emp_last_name_idx    ON    s_emp(last_name);

인덱스 확인

    ◈ USER_INDEXES Dictionary 뷰는 인덱스의 이름과 Unique 여부를 가지고 있다.
    ◈ USER_IND_COLUMNS 뷰는 인덱스 명, 테이블 명과 Column 명을 가지고 있다.
    ◈ USER_INDEXES Dictionary 뷰에 인덱스가 있는지 확인하시오.
    ◈ USER_IND_COLUMNS 뷰를 Query 하여 인덱스를 갖고 있는 Column을 확인할 수도 있다.
     

    ♠실습 : S_EMP 테이블에서 이미 생성된 인덱스, 관련된 Column 명, Unique 여부를   Display 하라.

    SQL> SELECT ic.index_name, ic.column_name,  ic.column_position, ix.uniqueness
              FROM   user_indexes ix, user_ind_columns ic
              WHERE  ic.index_name = ix.index_name
                    AND ic.table_name = 'S_EMP';

     

3. 인덱스 삭제

    ◈인덱스를 수정할 수는 없다.
    ◈인덱스를 변경하려면 삭제한 다음 다시 만들어야 한다.
    ◈INDEX 명령을 써서 인덱스를 삭제하라.
    ◈인덱스를 삭제하려면 그 인덱스의 소유자이거나 DROP ANY INDEX 권한을 가지고 있어야  한다.

        DROP INDEX 인덱스명;
       

    ♠실습 : S_EMP_LAST_NAME_IDX 인덱스를 삭제하고 Display 하라.

    SQL> DROP INDEX s_emp_last_name_idx;
    SQL> SELECT ic.index_name, ic.column_name,ic.column_position, ix.uniqueness
             FROM   user_indexes ix, user_ind_columns ic
             WHERE  ic.index_name = ix.index_name
                   AND ic.table_name = 'S_EMP';

     

신고

SQL)강의- 제 14 장 View 생성

.Tech/SQL

출처 : http://naver.kaist.ac.kr/oracle_sql/s14.htm

1. 뷰?

    ◈뷰란 테이블이나 다른 뷰를 기초로 한 논리적인 테이블이다.
    ◈뷰는 자체의 데이터는 없지만 테이블의 데이터를 보거나 변경할 수 있는 창과 같다.
    ◈뷰는 실제적으로는 질의문장을 가진다.

     

♣ 뷰의 장점

    ◈뷰는 데이터베이스의 선택적인 내용을 보여줄 수 있기 때문에 데이터베이스에 대한 액세스를 제한한다.
    ◈복잡한 질의어를 통해 얻을 수 있는 결과를 간단한 질의어를 써서 구할 수 있게 한다. 예를 들면 조인 방법을 몰라도 조인을 한것처럼 여러 테이블에 대한 데이터를 뷰를 통해 볼수 있다.
    ◈한 개의 뷰로 여러 테이블에 대한 데이터를 검색할 수 있다.
    ◈특정 평가기준에 따른 사용자 별로 다른 데이터를 액세스할 수 있다. 예를 들면 10번 부서의 직원은 10번 부서 직원들로 구성된 사원 테이블을 보게 할 수도 있다.

2. 뷰 생성/변경

♣ Syntax

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

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

                   [WITH READ ONLY]

    ◈CREATE VIEW문에 Subquery를 써서 뷰를 만든다.
    ◈FORCE는 Base Table 유무에 관계없이 뷰를 만든다.
    ◈Alias는 Subquery를 통해 선택된 값에 대한 Column명이 된다.
    ◈WITH CHECK OPTION은 뷰에 의해 액세스 될수 있는 행만이 입력되거나 변경될 수 있음을 지정하고,
       WITH READ ONLY는 이뷰에서 DML이 수행될 수 없게 한다.
    ◈뷰를 정의하는 질의어는 조인, 그룹, Subquery를 포함하는 복잡한 SELECT문장으로 구성될 수 있다.
    ◈뷰를 정의하는 질의어에는 ORDER BY 절을 쓸 수 없다.
    ◈제약조건의 이름을 명시하지 않으면 시스템이 SYS_Cn 형태의 이름을 지정한다.
    ◈뷰를 재생성하지 않고 뷰의 정의를 변경하려면 OR REPLACE옵션을 쓸 수 있다.

♣ Simple뷰와 Complex뷰

    특징

    Simple뷰

    Complex뷰

    테이블의 수

    하나

    하나이상

    데이터 그룹 포함 가능여부

    가능

    가능

    뷰를 통한 DML

    가능

    불가능

♣ Simple뷰 생성

    ♠실습 : 부서번호가 45인 사번과 성, 직급을 가지는 뷰를 만들고 내용을 출력하시오.

    SQL> CREATE VIEW empview45
                  AS SELECT id, last_name, title    FROM   s_emp   WHERE  dept_id = 45;
    SQL> SELECT *   FROM empview45;

     

    ♠실습 : empview45 뷰를 사번이 ID_NUMBER로, 성이 EMPPLOYEE로, 직급이 JOB이 되도록 변경하시오.

    SQL> CREATE OR REPLACE VIEW empview45 (id_number, employee, job)
               AS SELECT id,last_name, title   ROM   s_emp   WHERE  dept_id = 45;
    SQL> SELECT * FROM empview45;
    SQL> SELECT * FROM dept_emp_view;

     

♣ Complex 생성

    ♠실습 : 부서의 이름과 최소급여, 최고급여, 평균급여에 대한 뷰를 만들고 뷰의 구조와 내용을 출력하시오.

    SQL> CREATE VIEW dept_emp_view(name, minsal, maxsal, avgsal)
              AS  SELECT d.name, min(e.salary), max(e.salary), avg(e.salary)
                      FROM   s_emp e, s_dept d  
                      WHERE  e.dept_id = d.id
                      GROUP BY d.name;

     

3. 뷰(View)에 대한 DML 문 사용

    ◈Simple 뷰에서는 DML연산을 수행.
    ◈뷰를 통하여 다음의 DML연산은 수행할 없다.

    행을 삭제할 수 없는 경우

    데이터를 수정할 수 없는 경우

    데이터를 추가할 수 없는 경우

    - 조인 조건
    - 그룹 함수
    - GROUP BY 절
    - DISTINCT 명령

    - 조인 조건
    - 그룹 함수
    - GROUP BY 절
    - DISTINCT 명령

    - 조인 조건
    - 그룹 함수
    - GROUP BY 절
    - DISTINCT 명령

     

    - 식으로 정의된 Column.

    - 뷰로 선택되지 않은NOT NULL Column.

    ◈위에 열거한 것 중 어느 것도 포함하지 않고 뷰에서 선택하지 않은 Base table에 NOT NULL
       Column이 있지 않으면 뷰를 통해 데이터를 추가할 수 있음.

     

♣ WITH CHECK OPTION 절의 이용

    ♠실습 : 부서번호 41에 대해 WITH CHECK OPTION절을 추가하여s_emp 테이블의 모든 column을
               갖는 뷰를 만드시오. 

    SQL> CREATE OR REPLACE VIEW empview41
                AS SELECT *
                     FROM s_emp
                     WHERE dept_id = 41
                     WITH CHECK OPTION CONSTRAINT empview41_ck;

     

    ♠실습 : 뷰를 통해 사원 16의 부서번호를 42로 바꾸어 보시오.

    SQL> UPDATE empview41   SET dept_id = 42  WHERE id =16;

    ◈Note : 부서번호가 42로 바뀌면 뷰는 더 이상 해당 사원을 볼 수 없기 때문에 error가 발생한다.
                따라서 WITH CHECK OPTION절을 써서 뷰는 단지 부서41의 사원만을 볼수 있고
                이 사원의 부서번호는 뷰를 통해서는 변경할 수 없게 한다.

♣ WITH READ ONLY 옵션

    ◈ 뷰 정의에 WITH READ ONLY옵션을 추가하여 DML연산이 수행될 수 없게 한다.
     

    ♠실습 : empview45 뷰를 DML연산이 수행되지 않게 수정하시오.

    SQL> CREATE OR REPLACE VIEW empview45(id_number,employee,job)
                AS SELECT  id,last_name,title FROM s_emp  WHERE dept_id = 45
                     WITH READ ONLY;

     

    ♠실습 : 위에서 변경한 empview45 뷰에서 id_number가 10인 행을 삭제해 보시오.

    SQL> DELETE FROM empview45 WHERE id_number = 10;

     

4. 뷰의 구조 및 이름 확인

    ◈USER_VIEWS Dictionary 뷰에서 해당 뷰의 query 문장과 뷰 이름을 확인할 수 있음

      SQL> SELECT  view_name, text_length, text
               FROM user_views;

     

    ♠실습 : USER_VIEWS Dictionary 뷰의 구조를 보라.

    SQL> DESC user_views;

     

    ♠실습 : 사용자가 현재 소유한 모든 뷰의 이름과 내용을 출력하라.

    SQL> SELECT * FROM user_views;

     

5. 뷰 삭제

    ◈뷰를 삭제 하려면 DROP VIEW 명령을 사용한다.
    ◈이 명령은 데이터베이스에서 뷰의 정의를 삭제한다.
    ◈뷰는 데이터베이스의 테이블을 토대로 하기 때문에, 데이터의 손실없이 뷰를 삭제할 수 있다.

    ♠실습 : empview45뷰를 삭제하시오.

    SQL> DROP VIEW empview45;

     

신고

SQL)강의- 제 13 장 Sequence 생성

.Tech/SQL

출처 : http://naver.kaist.ac.kr/oracle_sql/s13.htm

 

1. Sequenc 생성

    ◈ 자동적으로 순차적인 숫자를 생성하기 위해 Sequenc를 정의한다.

      CREATE SEQUENC NAME

      [INCREMENT BY n]
      [START WITH n]
      [{MAXVQLUE n | NOMAXVALUE}]
      [{MINVALUE n | NOMINVALUE}]
      [{CYCLE | NOCYCLE}]
      [{CACHE n | NOCACHE}]

    ◈INCREMENT BY n은 Sequenc번호간의 간격인 정수 n이며, 생략되면 Sequenc는 1씩 증가
    ◈START WITH n은 생성되는 첫 번째 Sequenc번호로 생략되면 시퀀스는 1씩 증가
    ◈MAXVALUE n은 생성가능한 Sequenc의 최대값
    ◈NOMAXVALUE는 기본 옵션으로 최대값은 1027
    ◈MINVALUE n은 생성가능한 Sequenc의 최소값
    ◈NOMINVALUE최소값1
    ◈CACHE n|NOCACHE는 Oracle Sever가 미리 지정하고 메모리에 유지할 값의 수로
       Default값은 20이다.

    ♠실습 : s_dept 테이블의 dept_id column 에 사용할 sequence인 s_dept_id를 만들라.
               sequence는 51에서 시작 한다. 캐쉬와 cycle을 하지 않게 하라.

    SQL> CREATE SEQUENCE s_dept_id
              INCREMENT BY   1
              START WITH   51
              MAXVALUE   9999999
              NOCACHE
              NOCYCLE;

    ※Sequence가 Primary Key값을 생성한다면 CYCLE 옵션을 쓰지 말라.
     

Sequence 확인

    ◈ Sequence를 생성하면 data dictionary에 기록된다.
    ◈ Sequence가 데이터베이스 객체이므로 USER_OBJECTS dictionary에서 볼 수 있다.
    ◈ 데이터 사전의 USER_SEQUENCES 테이블을 참조하면 설정된 사항을 확인할 수 있다.

    ♠실습 : 자신이 소유한 모든sequence정보를 출력하시오.

    SQL> SELECT sequence_name, min_value, max_value,  increment_by, last_number
              FROM   user_sequences;

     

2. Sequence 이용

    ◈ 시퀀스를 만들면 테이블에서 사용할 순차적인 번호를 생성하기 위해 시퀀스를 이용할 수 있다.

    ◈ NEXTVAL과 CURRVAL Pseudocolumn을 써서 Sequence 값을 참조하라.
    ♠실습 : 지역2에 "Finance"라는 입력을 삽입하라.

    SQL> INSERT INTO s_dept(id, name, region_id)
                VALUES (s_dept_id.NEXTVAL, 'Finance', 2);

     

    ♠실습 : S_DEPT_ID Sequence에 대한 현재 값을 보라.

    SQL> SELECT s_dept_id,CURRVAL FROM SYS,dual;

     

NEXTVAL과 CURRVAL 의사 Pseudocolumn

    ◈NEXTVAL은 사용 가능한 다음 시퀀스값을 반환한다. - 매번 고유한 값을 반환한다.
    ◈CURRVAL은 현재 시퀀스값을 구한다. -CURRVAL은 NEXTVAL사용 후에 사용되어야 한다.
    ◈사용규칙을 따르라.
     

NEXTVAL과 CURRVAL 사용규칙

    ◈ 다음에서 NEXTVAL과 CURRVAL을 사용할 수 있다.

      - Subquery가 아닌 SELECT 문의 SELECT List
      - INSERT 문의 Subquery SELECT List
      - INSERT 문의 VALUE 절
      - UPDATE 문의 SET 절

    ◈ 다음에서 NEXTVAL과 CURRVAL을 사용할 수 없다.

      - 뷰의 SELECT List
      - DISTINCT 키워드가 있는 SELECT 문
      - GROUP BY, HAVING, 또는 ORDER BY 절이 있는 SELECT 문
      - SELECT, DELETE, 또는 UPDATE 문의 Subquery
      - CREATE TABLE 또는 ALTER TABLE 명령의 DEFAULT 값
       

Sequence 값의 캐슁

    ◈Sequence를 메모리에 캐쉬하면 Sequence 값을 더 빨리 액세스할 수 있다. 다음 Sequence값을
       요구하면 캐쉬된 Sequence에서 return해 준다.
     

증가 없이 다음 시퀀스 값 보기

    ◈NOCACHE로 Sequence가 생성될 경우에만 USER_SEQUENCES 테이블을 query를 하여 다음
       Sequence값을 볼 수 있다.

     

3. Sequenc 변경/삭제

    ◈증가값, 최대 값, 최소 값, 순환옵션, 캐쉬 옵션을 변경한다.
    ◈Sequence에 대한 MAXVALUE 한계에 도달하면 Sequence에 대한 추가적인 값이 할당되지 않게
       되고 Sequence가 MAXVALUE를 넘었음을 알리는 오류 메시지를 받게 됩니다. 이때Sequence를
       계속 사용하려면 ALTER SEQUENCE 명령을 써서 수정할 수 있다. 
     

가. Sequenc 변경

    ♣ Syntax

      ALTER SEQUENCE NAME

      [INCREMENT BY n]

      [{MAXVALUE n │ NOMAXVALUE}]

      [{MINVALUE n │ NOMINVALUE}]

      [{CYCLE │ NOCYCLE}]

      [{CACHE n│ NOCACHE}]

     

    ◈자신의 Sequence이거나 Sequence에 대한 ALTER 권한을 가져야만 Sequence를 수정할 수 있다.
    ◈아직 생성되지 않은 시퀀스값만이 ALTER SEQUENCE명령에 영향받는다.
    ◈유효성 검사를 하게 된다. 예를 들어 새로운 MAXVALUE는 현재의 순서 번호보다 작지 않아야 한다.
    ◈START WITH 옵션은 ALTER SEQUENCE를 써서 변경할 수 없다. 다른 번호에서 다시 시작하려면
       이전 시퀀스를 삭제하고 다시 생성해야 한다.
     

나. Sequenc 삭제

    ◈DROP SEQUENCE명령을 사용하여 Sequence를 삭제한다.
    ◈Sequence가 삭제되면 더 이상 참조할 수 없다.
    ◈Sequence를 제거할 DROP ANY SEQUENCE 권한을 가지고 있거나 그 시퀀스의 소유자이어야 한다.
    ♣ Syntax : DROP SEQUENCE 시퀀스 이름;

     

다. Sequenc 요약

    ◈Sequence 사용하여 테이블의 행에 대한 primary key 값을 자동으로 생성한다.
    ◈데이터베이스 객체인 Sequence를 다른 사용자들과 공유할 수도 있다.
    ◈USER_SEQUENCES 테이블에 있는 정보를 참조할 수 있다.
    ◈Sequence.NEXTVAL을 이용하여 다음 값을 Return 받는다.
    ◈Sequence.CURRVAL을 이용하여 NEXTVAL 에 의해 Return된 마지막 값을 확인할 수 있다.

     

신고

SQL)강의- 제 12 장 테이블구조 변경과 제약조건 변경

.Tech/SQL

출처 : http://naver.kaist.ac.kr/oracle_sql/s12.htm

 

1. Column의 추가

♣ Syntax

    ADD절이 있는 ALTER TABLE 명령을 써서 테이블에 column을 추가할 수 있다.
    ◈새로운 column의 추가
    ◈새로운 column에 대한 Default 값을 정의
    ◈값을 갖고 있어야만 하는 column을 명시(NOT NULL 설정)

      ALTER TABLE 테이블 명
      ADD (새로운 column Datatype [DEFAULT 식] [NOT NULL]
      [. 새로운 column Datatype] ...):

                    DEFAULT 식은 새로운 column에 대한 default 값을 지정하며,
                    NOT NULL은 새로운 column에 NOT NULL 제약조건을 추가한다.

    ◈기존의 column을 삭제할 수는 없다.
    ◈Column을 추가하거나 수정할 수 있지만 테이블에서 삭제할 수는 없다.
    ◈Column의 위치를 저장할 수 없습니다. 새로운 column은 테이블의 마지막 column이 된다.
     

♣ Column의 추가 : 예

    ◈새로운 column은 테이블의 마지막 column이 된다.

    ♠실습 :  S_REGION 테이블에 COMMENTS column을 추가

    SQL> ALTER TABLE s_region      ADD(comments     VARCHAR2(255));

     

2. Column의 변경

♣ Syntax  :

      ALTER TABLE 테이블 명
          MODIFY (column Datatype [DEFAULT 식] [NOT NULL]
          [. Column Datatype] ...):

    ◈Column의 크기, default 값, NOT NULL column 제약조건을 변경합니다.
    ◈column의 크기를 확장
    ◈column이 널 값만 가지고 있거나 테이블에 아무 행도 없으면 column의 폭을 축소
    ◈Default 값 변경 (다음 INSERT 부터 적용)
    ◈column에 NULL 값이 없을 경우에만 NOT NULL 제약 조건을 추가
     

♣ Column의 변경 : 예


    ♠실습 :
    S_EMP 테이블의 TITLE column의 최대 길이를 50 문자로 확장.

    SQL> ALTER TABLE s_emp   MODIFY (title   VARCHAR2(50));

     

3. 제약조건 추가/삭제

♣ 제약조건 추가

    ◈Column의 크기, default 값, NOT NULL column 제약조건을 변경한다.
    ◈Syntax

      ALTER TABLE 테이블 명
      ADD [CONSTRAINT 제약조건] type (column):

    ◈제약조건을 수정하는 것이 아닌 추가나 삭제
    ◈MODIFY 절을 써서 NOT NULL 제약조건 추가

    ♠실습 : S_EMP 테이블ID column이 MANAGER_ID column을 참조하도록 S_EMP 테이블에
               foreign key 제약조건을 추가한다.

    SQL> ALTER TABLE s_emp
                 ADD CONSTRAINT s_emp_manager_id_fk    FORGIGN KEY (manager_id)
                       REFERENCES s_emp(id);

     

    ♣ 제약조건 삭제

      ◈제약조건을 삭제하기 위해서 USER_CONSTRAINTS와 USER_CONS_COLUMNS dictionary 뷰로
         부터 제약조건을 확인할 수 있다.
      ◈DROP절이 있는 ALTER TABLE명령을 사용한다.
      ◈DROP절의 CASCADE옵션은 종속적인 다른 제약조건도 삭제한다.
      ◈Syntax

        ALTER TABLE 테이블 명
           DROP PRIMARY KEY|UNIQUE(column)
             CONSTRAINT 제약조건[CASCADE]:

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

      ♠실습 : S_EMP 테이블에서 관리자 제약조건을 삭제한다.

    SQL> ALTER TABLE s_emp      DROP  PRIMARY KEY CASCADE;

     

       ♠실습 : S_DEPT 테이블의 PRIMARY KEY 제약조건을 삭제하고 S_EMP.DEPT_ID column의
                  FORREIGN KEY 제약조건을 삭제한다.

    SQL> ALTER TABLE s_region      ADD(comments VARCHAR2(255));

       

 4. 제약조건 ENABLE, DISABLE 시키기

    ◈ENABLE이나 DISABLE 절이 있는 ALTER TABLE 명령을 써서 삭제하거나 재생성하지 않고도
       제약조건을 사용가능이나 사용불가 상태로 만들 수 있다.
    ◈Syntax :
          ALTER TABLE 테이블 명
               DISABLE | ENABLE CONSTRAINT 제약조건 [CASCADE];

    ◈제약조건을 enalbe시키면 테이블의 모든 데이터에 적용된다. 테이블의 모든 데이터가 제약조건에
       맞아야 한다.
    ◈UNIQUE나 PRIMARY KEY 제약조건을 enable시키면 UNIQUE나 PRIMARY KEY 인덱스가 자동으로
       생성된다.
    ◈TABLE 명령과 ALTER TABLE 명령 모두 ENABLE과 DISABLE 절을 쓸 수 있다.
    ◈CASCAED 절은 종속적인 무결성 제약조건을 disable 상태로 만든다.
     

♣ 제약조건 ENABLE

    ◈ENABLE 절을 써서 테이블의disable된 제약조건을 enable시킬 수 있다.

    SQL> ALTER TABLE s_emp  ENABLE CONSTRAINT S_DEPT_ID_PK;

    ◈UNIQUE나 PRIMARY KEY 제약조건을 enable시키면 UNIQUE나 PRIMARY KEY Index가 자동으로 생성된다.

♣ 제약조건 DISABLE

    ◈무결성 제약조건을 disable시키기 위해 ALTER TABLE 명령의 DISABKE를 이용한다.
    ◈종속적인 무결성 제약조건을 disable시키기 위해 CASCADE 옵션을 적용한다.

    SQL> ALTER TABLE s_emp   DISABLE CONSTRAINT S_DEPT_ID_PK CASCADE;

     

5. 테이블 삭제

    ◈해당테이블의 모든 인덱스가 삭제된다.
    ◈CASCADE CONSTRAINTS 옵션은 종속 무결성 제약조건을 삭제한다.
    ◈DROP TABLE 명령은 Oracle 테이블의 정의를 삭제한다.
    ◈테이블을 삭제하면 테이블의 모든 데이터 및 관련된 모든 인덱스가 없어진다.
    ◈CASCADE CONSTRAINTS 옵션은 종속적인 참조 무결성 제약조건도 삭제할 것이다.
    ◈모든 데이터가 테이블에서 삭제된다.
    ◈테이블로 참조하는 뷰, 함수, 패키지는 남아있지만 부적합하게 된다.
    ◈트랜잭션은 자동 커밋된다.
    ◈DROP ANY TABLE권한을 가진 사용자나 테이블을 만든 사람만이 테이블을 삭제할 수 있다.
    ◈Syntax : DROP TABLE 테이블  [CASCADE CONSTRAINT];

    ◈DROP TABLE 명령은 한 번 실행하면 되돌릴 수 없다. Oracle7 Sever는 DROP TABLE명령을 쓸 때
       아무 질문도 하지 않는다. 해당 테이블의 소유자이거나 높은 레벨의 권한이 있으면 테이블이 즉시
       삭제된다. 모든 DDL명령은 자동 커밋을 한다.

    ♠실습 : TEST2 테이블을 완전히 삭제하시오.

    SQL> DROP  TABLE test2;

     

6. TRUNCATE와 테이블 이름 변경(RENAME)

테이블 이름 변경 - RENAME 명령

    ◈RENAME 명령은 케이블, 뷰, sequence, synonym 의 이름을 변경하는 데에 사용한다.
    ◈이름을 바꾼 객체의 소유자이어야 한다.
    ◈Syntax : RENAME 이전이름 TO 새로운 이름;

     

TRUNCATE 명령

    ◈TRUNCATE명령은
          -테이블의 모든 행을 삭제하고
          -테이블이 사용한 저장 공간을 사용가능하도록 하는
          -DDL명령이다.
    ◈TRUNCATE를 쓰면 행의 삭제를 롤백할 수 없다.
    ◈행을 삭제하는 데에 사용할 수 있는 명령은 DELETE명령이다.
    ◈테이블의 소유자이거나 DELETE TABLE권한이 이어야 한다.
    ◈DELETE명령은 테이블의 모든 행을 삭제할 수 있지만 저장 공간을 해제하지는 못한다.
    ◈Syntax  : TRUNCATE TABLE테이블;

     

7. 테이블에 주석 입력

    COMMENT 명령을 써서 column,테이블, 뷰, 대한 주석을 2000 바이트까지 추가할 수 있다.
    주석은 data dictionary에 저장되고 아래와 같은 dictionary뷰를 이용하여 입력된 값을 볼 수 있다.
    ◈ ALL_COL_COMMENTS
    ◈ USER_COL_COMMENTS
    ◈ALL_TAB_COMMENTS
    ◈ USER_TAB_COMMENTS
    ◈Syntax  : COMMENT ON TABLE테이블 명 COLUMN 테이블 명.column IS '텍스트'

                     여기서, 텍스트는 주석이다.


    ♠실습 :
    S_EMP테이블에 주석을 추가하십시오.

    SQL> COMMENT ON TABLE s_emp IS 'Employee Information';

     

    ♠실습 : column의 주석을 삭제하십시오.

    SQL> COMMENT ON COLUMN s_emp.last_name IS ''

     

     

신고

SQL)강의- 제 11 장 데이터 조작(DML)

.Tech/SQL

출처 : http://naver.kaist.ac.kr/oracle_sql/s11.htm

 

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

    ◈데이터 조작어(DML)는 SQL의 핵심이다.
    ◈데이터베이스에 데이터를 추가, 변경, 삭제를 하려할 때 DML문을 사용한다.
    ◈아직 commitehlwl 않은 DML문의 집합을 트랜잭션 또는 작업의 논리적 단위라 한다.

    명    령

    설    명

    INSERT

    테이블에 새로운 행 추가

    UPDATE

    테이블의 행 변경

    DELETE

    테이블의 행 삭제

    COMMIT

    저장되지 않은 모든 변경사항을 DB에 저장

    SAVEPOINT

    Savepoint 설정

    ROLLBACK

    저장되지 않은 모든 변경사항을 취소

     

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

    ◈INSERT문을 써서 테이블에 새로운 행을 추가한다.
    ◈Syntax

        INSERT  INTO  테이블명  [(column[, column…])]
            VALUES  (value_data[,value_data…]);

    ◈한 번에 한 행만 삽입된다.
    ◈모든 column에 대해 값을 갖는 새로운 행을 삽입한다.
    ◈INSERT 절의 column은 선택적으로 기입할 수 있다.
    ◈문자와 날짜 값은 단일 따옴표('')를 둘러싼다.
     

♣ 모든 column에 대해 값을 갖는 새로운 행 삽입

    ◈행에 대해 모든 값을 갖는 새로운 행을 삽입할 경우 테이블명 뒤에 column들을 표시할 필요가 없다.
       즉, Column list가 없는 경우 테이블에 정의된 순서에 따라 값을 나열해야 한다.
    ◈명확성을 위해 INSERT절에 아래와 같이 column list를 사용하는 것이 좋다.

    실습 : 부서번호 11, 부서명은 Finance, 지역번호를  2로 하는 새로운 부서를 추가한다.

    SQL> INSERT INTO s_dept   VALUES (11,'Finance', 2);

     

    SQL> INSERT INTO s_dept(id,name,region_id) VALUES (11,'Finance', 2);

     

NULL 값을 갖는 새로운 행 삽입

    방법

    설    명

    Implicit

    column list에서 column명 생략.

    Explicit

    NULL 키워드를 이용.
    문자와 날짜의 경우에만 VALUES 목록에 공문자(' ') 입력

    ◈column list에서 column명을 제외 한다. : REGION_ID
    ◈VALUES 목록에 공백표시('')나 NULL을 기입한다.  NULL 또는 ''
       즉, column이나 value_data가 정의 되어있지 않으면 NULL값이 INSERT 된다. 이때 NULL값이
       허용되지 않는 column인 경우는 ERROR가 발생하며, column 구분은 콤머로 한다.
    ◈RDBMS는 새로운 ROW를 INSERT할때 모든 column의 내용을 전부 정의할 필요가 없고 필요한
       column의 내용만 정의하면 된다. 정의가 않된 column은 NULL이 들어간다.
    ◈DATA가 INSERT될 경우 KEY순서대로 DISK에 저장되는 것이 아니라 들어가는 순서대로 저장된다.

    실습 : 지역 번호를 생략하고 새로운 부서를 입력한다. 지역 번호는 INSERT 절에 나열하지 않았기
               때문에 NULL값이 지역번호(Region_Id)에 자동적으로 입력된다.

    SQL> INSERT INTO s_dept VALUES(12,'MIS',NULL);  또는
    SQL> INSERT INTO s_dept VALUES(12,'MIS','');

    SQL> SELECT *   FROM s_dept   WHERE id = 12;

     

SQL 함수를 이용하여 특정한 값 삽입

    ◈USER 함수는 현재 사용자의 이름을 Return한다.
    ◈SYSDATE 함수는 현재 날짜와 시간을 Return한다.

    실습 : S_EMP테이블에 사원에 대한 정보를 입력한다. USERID Column에 현재 사용자의 이름을,
               Start_date Column에는 현재의 날짜와 시간을 넣는다.

    SQL> INSERT  INTO    s_emp(id,first_name, last_name, userid, salary, start_date)
                 VALUES(26,'Donna', 'Smith', USER, NULL, SYSDATE);

     

    실습 : 행이 테이블에 삽입되었는지 확인한다.

    SQL> SELECT id, last_name, first_name, userid, salary, start_date
              FROM   s_emp
              WHERE  id = 26

     

2. 테이블의 특정 행 변경(UPDATE)

    ◈UPDATE 명령어는 조건에 맞는 ROW를 전부 변경시킨다.
    ◈만일 조건문(WHERE절)이 없으면 모든 ROW를 변경시킨다.
    ◈WHRER 절을 생략하면 해당 column의 모든 data값이 변경된다.
     

Syntax

      UPDATE  {table|view}
          SET column=값[,column=값] 
          [WHERE 조건]

     

    실습 : s_dept에서 부서id가 58인 부서의 부서명을 'EDPS'로 변경하시오.

    SQL> select * from s_dept  WHERE  id = 58;

    SQL> UPDATE s_dept  SET    name = ‘EDPS’  WHERE  id = 58;

    SQL> select * from s_dept  WHERE  id = 58;

     

    실습 : s_emp 테이블에서 id가 100인 사원의 급여를 100000 더하고, 보너스율을 15로 변경하시오.

    SQL> select id, salary,commission_pct   from s_emp   where id = 100;

    SQL> UPDATE s_emp  Set  salary = salary + 100000, commission_pct = 15  WHERE id = 100;

    SQL> select id, salary,commission_pct  from s_emp   where id = 100

     

      ◈ NULL 값에 어떤수를 더하여도 값은 NULL이다. 즉, 다음과 같이 수정하여야 한다.

    SQL> UPDATE s_emp
           SET  salary = NVL(salary,0) + 100000, commission_pct = 15
           WHERE id = 100

    SQL> select id, salary,commission_pct
         from s_emp
         where id = 100

     

3. 테이블의 행 삭제(DELETE)

    ◈DELETE 명령을 이용하여 행을 삭제할 수 있다.
    ◈WHERE 절이 생략되면 테이블의 모든 행이 삭제된다.
    ◈Relational한 관계(Primary KEY와 Foreign KEY관계)로 되어있는 TABLE인 경우는
       먼저 Foreign KEY로 정의된 TABLE의 ROW를 삭제시킨후에 Primary KEY로 정의된
       TABLE의 ROW를 삭제시켜야 한다.
    ◈Syntax

      DELETE  FROM  테이블  [WHERE  조건];
       

    실습 : 부서명이 EDPS 인 부서를 삭제하시오. 

    SQL> DELETE s_dept where name = ‘EDPS’;
    SQL> SELECT * FROM s_dept WHERE name = 'EDPS';

     

무결성 제약조건 ERROR

    ◈다른 테이블에서 참조되어지는 Primary Key를 포함하고 있는 행을 삭제하려고 하면 무결성 제약 조건
       error가 발생한다.
    ◈Relational한 관계(Primary KEY와 Foreign KEY관계)로 되어있는 TABLE인 경우는
       먼저 Foreign KEY로 정의된 TABLE의 ROW를 삭제시킨후에 Primary KEY로 정의된
       TABLE의 ROW를 삭제시켜야 한다.
    실습 : 지역 번호가 1인 모든 부서를 삭제해 보십시오

    SQL> DELETE s_dept       WHERE  region_id=1;

     

4. 데이터베이스 트랜잭션

트랜잭션 처리

    ◈Oracle7 Server는 트랜잭션 기반의 데이터 일관성을 보장한다.
    ◈트랜잭션은 데이터 변경시 더 많은 유연성과 제어를 제공하고,
       프로세서나 시스템의 실패에도 데이터의 일관성을 보장
    ◈트랜잭션은 일관성있는 데이터의 변경을 위한 DML 명령으로 구성되어 있다.
    ◈예를 들면, 두 계정간의 자금 전송은 한 계정에는 차변을, 다른 계정에는 같은 액수를 대변에
       포함시켜야 한다.
    ◈두 작업은 모두 성공하거나 실패해야만 한다.
    ◈대변은 차변없이는 commit되지 않아야 한다.
     

트랜잭션 유형

    유 형

    설 명

    데이터 조작(DML)

    여러 DML 문으로 구성

    데이터 정의(DDL)

    하나의 DDL 문으로 구성

    데이터 제어(DCL)

    하나의 DCL 문으로만 구성

     

트랜잭션은 언제 시작되고 언제 끝나는가?

    최초의 실행가능한 SQL 명령을 만나면 트랜잭션이 시작되고, 아래의 경우중 하나를 만나면 끝난다.
    ◈COMMIT나 ROLLBACK 명령 실행
    ◈CREATE 같은 DDL 명령이나 DCL 명령 실행
    ◈Dead Lock 발생
    ◈사용자에 의한 SQL*Plus 종료
    ◈기계 장애나 system crash
    ◈한 트랜잭션이 끝난 후에 다음의 실행가능한 SQL 문은 다음 트랜잭션을 자동적으로 시작할 것이다.
    ◈DDL 명령이나 DCL 명령은 자동적으로 커밋되므로 트랜잭션은 Implicit하게 종료된다. 
     

Explicit 트랜잭션

    명령

    설명

    COMMIT

    아직 저장되지 않은 모든 데이터 변경 사항을 DB에 저장하고 현재의 트랜잭션을 종료

    SAVEPOINT 이름

    현재의 트랜잭션에 savepoint를 표시

    ROLLBACK (TO SAVEPOINT 이름)

    아직 저장되지 않은 모든 데이터 변경 사항을 무시(취소)하고 현재의 트랜잭션을 종료

     

Implicit 트랜잭션

    상태

    환경

    자동 커밋

    DDL 명령이나 DCL 명령 실행. COMMIT나 ROLLBACK을 명시적으로 실행하지 않고 SQL*Plus를 정상적으로 종료

    자동 롤백

    SQL*Plus의 비정상적 종료나 시스템 실패

     

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

    ◈데이터의 이전 상태는 복구될 수 있다.
    ◈현재 사용자는 SELECT 문을 써서 DML문의 결과를 확인할 수 있다.
    ◈다른 사용자는 현재 사용자가 수행한 DML문의 결과를 볼 수 없다.
    ◈변경된 내용은 Lock이 설정되어 있어 다른 USER가 변경할 수 없도록 한다.
     

COMMIT후의 데이터 상태

    ◈데이터에 대한 변경사항은 데이터베이스에 반영된다.
    ◈이전 데이터는 영원히 잃어버리게 된다.
    ◈모든 사용자는 결과를 볼 수 있다.
    ◈관련된 행에 대한 Lock 풀리고 다른 사용자들이 행을 조작할 수 있게 된다.
     

변경된 사항의 저장

    ◈ 트랜잭션 동안 생긴 모든 데이터 변경은 커밋될 때까지는 임시적인 것이다. 
     

COMMIT 결과

    ◈ 데이터에 대한 변경사항은 데이터베이스에 반영된다.
    ◈ 데이터의 이전 상태는 영원히 잃어버리게 된다.
    ◈ 모든 사용자는 트랜잭션의 결과를 볼 수 있다.
    ◈ 관련된 행에 대한 Lock이 풀리고 다른 사용자들이 데이터 변경을 할 수 있다.
    ◈ 모든 SAVEPOINT가 지워진다.
    실습 : 1 명의 사원이 있는 Educatio이라는 부서를 만들고 변경된 내용을 저장 하라.

    SQL> INSERT INTO s_dept (id, name, region_id)
              VALUES    (54, 'Education', 1);
    SQL> UPDATE s_emp    SET dept_id = 54    WHERE  id =2;
    SQL> SELECT *   FROM s_dept   WHERE id = 54;

     

SAVEPOINTK를 이용한 롤백

    ◈ SAVEPOINT명령을 써서 현재의 트랜잭션에 savepoint를 생성한다.
    ◈ ROLLBACK TO SAVEPOINT명령을 써서 특정 savepoint로 롤백
    ◈ SAVEPOINT명령을 써서 현재의 트랜잭션에 SAVEPOINT를 할수 있다.
        따라서 트랜잭션을 작게 분할할 수 있다.
    ◈ ROLLBACK TO SAVEPOINT문을 써서 표시한 곳까지의 rollback할 수 있다. 
     

    실습 : 모든 stock clerk의 급여를 10%씩 인상하고 UPDATE_DONE이라는 savepoint를 생성하여라.

    SQL> UPDATE  s_emp  SET  salary = salary *1.1
            WHERE  title = 'Stock Clerk';
    SQL> SAVEPOINT point_a;
    SQL> INSERT INTO s_region(id,name)   VALUEs (8,’Central’);
    SQL> SELECT *  FROM   s_region   WHERE  id = 8;
    SQL> ROLLBACK TO point_a;
    SQL> SELECT *    FROM   s_region   WHERE  id = 8;

     

문장 단위의 롤백

    ◈문장의 실행 중 errer가 발생하면 해당 문장만 롤백된다.
    ◈이전 DML문장에 의해 변경사항은 롤백되지 않는다.
    ◈DDL명령을 실행하기 전에 Server는 Implicit savepoint를 만들어 현재의 트랜잭션을 커밋하려한다.
    ◈DDL 명령이 수행되면 Server는 커밋을 하기 때문에 이전 내용을 롤백할 수 없다.
    ◈DDL 명령이 실패하면 트랜잭션은 커밋되지 않는다.

     

신고

SQL)강의- 제 10 장 Oracle Data Dictionary

.Tech/SQL

출처 : http://naver.kaist.ac.kr/oracle_sql/s10.htm

 

1. 오라클 데이터베이스의 테이블

개요

    ◈Oracle 데이터 사전은 데이터베이스가 만들어지면 자동으로 생성된다.
    ◈데이터베이스에 대한 작업이 있으면 Oracle7 server가 데이터 사전을 갱신하고 유지보수한다.
    ◈모든 데이터 사전 테이블은 SYS사용자가 소유한다.
       이러한 테이블의 정보는 사용자가 이해하기 어렵기 때문에 직접 엑세스하는 경우가 드물다.
    ◈따라서 데이터 사전의 정보는 사용자가 이해하기 쉽게 데이터 서전 뷰라는 형태로 제공된다.
     

Oracle7 데이터베이스 테이블

    테이블

    설    명

    사용자 테이블

    사용자가 만들고 사용하는 테이블
    사용자가 입력한 정보를 저장한다.

    Data Dictionary

    Oracle이 만들고 관리하는 테이블의 집합
    데이터베이스에 대한 정보를 저장하고 있다.
      ① Oracle Server에 Login할 수 있는 USER명
      ② 사용자에게 허가된 권한
      ③ 데이터베이스 객체명
      ④ 테이블 제약조건
      ⑤ Auditing 정보
    데이터 사전은 모든 데이터베이스 사용자가 참조하기 위한 것이다.
    데이터베이스는 데이터 사전에 따라 자체 정보를 기록하고 검사하기 때문에 데이터 사전은 Oracle7 Server의 작업에 매우 중요하다.

     

데이터 사전에 대한 Query

    ◈SQL SELECT문을 써서 데이터사전을 Query할 수 있다.
    ◈권한에 따라 여러가지 뷰를 Query를 할 수 있다.
    ◈뷰의 종류

      접두어

         

      USER_

      사용자 소유의 객체에 관한 정보를 저장하고 있다.

      ALL_

      사용자에게 액세스가 허용된 객체에 관한 정보를 저장하고 있다.

      DBA_

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

      V$

      서버의 성능과 Locking에 관한 정보등을 가지고 있다.
      일반적으로 DBA에게만 허용된다.

      : 다른 몇가지 dictionary 뷰는 위에 나열된 접두어를 쓰지 않고 synonym을 사용한다.

      뷰 이름

      설    명

      Dictionary

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

      IND

      USER_INDEXES의 synonym


    ♠ 예제 : Dictionary뷰는 사용자가 액세스할 수 있는 모든 dictionary뷰를 주석과 함께 나열한다.
                또한 뷰에대한 Synonym인 DICT을 사용할 수도 있다.

    SQL> SELECT * FROM DICTIONARY;

     

    ♠ 예제 : SQL*Plus DESCRIBE 명령을 써서 Dictionary 뷰의 구조를 Display할 수 있다.
                USER_OBJECTS의 구조를 출력하라.

    SQL> DESC user_objects

     

    ♠ 예제 : Dictionary 테이블과 뷰의 column에 대한 정보를 보고자 한다면 DICT_COLUMNS뷰를
                참조하라.

    SQL> SELECT column_name, comments
              FROM   dict_columns
              WHERE  table_name = 'USER_OBJECTS';

     

    ♠ 예제 : 사용자 소유의 객체 유형을 출력하라.

    SQL> SELECT DISTINCT object_type   FROM   user_objects;

     

    ♠ 예제 : Dictionary뷰의 COMMENTS column에 대한 query를 하여 특정 주제에 대한 Dictionary
                View를 찾을 수 있다. Grant라는 키워드를 갖고 있는 모든 Dictionary 뷰를 찾아보아라.

    SQL> SELECT * FROM dictionary
              WHERE  LOWER(comments) LIKE '%grant%';

     

2. 테이블의 제약조건 확인

    ◈모든 제약조건 정의와 이름을 보고자 한다면 USER_CONSTRAINTS를 참조하라.
    USER_CONS_COLUMNS 뷰에서 제약조건 이름과 관련된 column의 정보를 확인할 수 있다.
    ◈이 뷰는 제약조건 명을 시스템이 정한 경우에 특히 유용하다.

    실습 : 앞에서 작성한 TEST1 테이블에 대한 제약조건을 DESC 명령으로 확인하라.

    SQL> DESC test1;

     

    ◈ DESC 명령은 NOT NULL column만 확인될 뿐이다.
     

    실습 : TEST1 테이블에 대한 제약조건을 USER_CONS_COLUMNS 뷰로 확인하라.

    SQL> SELECT constraint_name, constraint_type, search_condition
             FROM user_constraints
             WHERE table_name = 'TEST1';

     

    ◈ TABLE_NAME의 값은 반드시 대문자로 입력하여야 한다.
    실습 : TEST2 테이블에 대한 제약조건을 확인하여 위 TEST1 테이블과 비교하여 보라.

    SQL> SELECT constraint_name, constraint_type, search_condition
              FROM user_constraints
              WHERE table_name = 'TEST2';

     

     

신고

SQL)강의- 제 9 장 테이블 생성

.Tech/SQL

출처 : http://naver.kaist.ac.kr/oracle_sql/s09.htm

1. 테이블 생성

    데이터 구조

    구   조

    설    명

    Table

    데이터 저장

    View

    하나 이상의 테이블에 있는 데이터 부분집합에 대한 논리적인 표현

    Index

    일부 Query에 대한 성능 향상을 위한 색인 파일

    Sequence

    Primary Key 를 일정한 값으로 생성

     

Oracle 테이블 구조의 요약

    ◈데이터베이스 설계단계에서 구조를 정의한다.
    ◈어떤 시점에서도 작성할 수 있다.
    ◈테이블의 크기를 명시할 필요가 없다.  
       크기는 데이터베이스에 할당된 공간 전체로 한정된다.
       하지만 얼마만한 공간을 테이블이 사용할 것인지 예측하는 것이 중요하다.
    ◈테이블 구조는 수정할 수 있다.

      CREATE  TABLE  [스키마.] 테이블

                ({column_name datatype[DEFAULT 식]  [column [제약조건],

                 ….

                 [테이블 제약조건]);
       

    테이블명과 column명 정의에 대한 Naming Rule

        ① 1문자이상 30문자이하의 길이를 가지며 첫번째 문자는 영문자이어야 한다
        ② 이름은 A-z, a-z, 0-9, _, $, #들로만 구성된 문자를 사용할수 있다
        ③ 테이블명은 한 USER에 같은 Object가 있어서는 안된다
        ④ 이름은 대문자와 소문자가 같은 문자로 취급된다(데이터는 구분함)
     

    스키마.테이블(다른 사용자의 테이블 참조)

        - 소유자의 이름(user name)으로써, 데이터베이스의 논리적 구조이다. 
        - 제약조건에서 참조되는 테이블은 동일한 데이터베이스에 있어야 한다.
        - 참조되는 테이블이 제약조건을 만드는 user name이 아니면 user name이 제약조건에서
           참조되는 테이블 이름 앞에 붙여져야 한다.

     

 다른 사용자의 테이블 참조

    ◈스키마는 객체의 집합이다.
    ◈스키마 데이터베이스의 논리적 구조이다.
    ◈스키마 객체에는 테이블, 뷰, synonym, stored procedure, 인덱스, 클러스터,
        데이터베이스 링크 등으로 구성된다.
    ◈제약조건에서 참조되는 테이블은 동일한 데이터베이스에 있어야 한다.
    ◈참조되는 테이블이 제약조건을 만드는 사용자의 소유가 아니라면 소유자의 이름(스키마)이
       제약조건에서 참조되는 테이블 이름 앞에 붙어져야 한다.
     

DEFAULT 옵션

    ◈insert에 사용될 COLUMN에 대한 Default 값을 명시한다.

      CREATE TABLE s_TestTable  start_date DATE DEFAULT SYSDATE, …

    ◈ default값으로는 리터럴, 식, 또는 SYSDATE와 USER와 같은 SQL함수가 될 수 있다.
    ◈ 다른 column명, 또는 NEXTVAL이나 CURRVAL과 같은 pseudo column을 가질 수 없다.
    ◈ default값은 반드시 column의 datatype과 일치해야 한다.
     

명명법(Naming Rule)

    Oracle7 데이터 객체의 표준 명명법에 따라 데이터베이스 테이블과 column명을 짓는다.
    ◈테이블명과 column명은 반드시 문자로 시작하고 최대 30자까지만 허용된다.
    ◈이름은 문자 A-Z, a-z, 0-9, _, $, #만으로 이루어져야 한다.
    ◈동일한 사용자가 소유한 다른 객체의 이름과 중복되지 않도록 해야 한다.
    ◈ Oracle7 Server의 Reserved word는 쓸 수 없다.

     

Guidelines

    ◈테이블과 다른 데이터베이스 객체를 의미할 수 있는 적절한 이름을 사용한다.
    ◈엔티티에 대해서는 다른 테이블과 일관성 있는 이름을 사용한다.
    ※ Note : 이름은 대소문자를 구분하지 않는다.
                 예를 들어, EMP는 eMP나 eMp와 동일한 것으로 취급된다.
     

오라클7 데이터 타입

    Datatype

    설              명

    VARCHAR2(s)

    1에서 2000까지의 길이를 가지는 가변길이 문자형태(s : 최대길이임.)

    CHAR(s)

    1에서 255까지의 길이를 가지는 고정길이 문자형태(s : total size 표시)

    NUMBER(p,s)

    1에서 38까지의 총길이와 소수점이하의 자리수를 표현하는 숫자형태(p는 총길이, s는 소수점이하의 길이를 표시)

    DATE

    일자와 시간을 표시할수있는 형태

    LONG

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

    RAW와 LONG RAW

    각각 VARCHAR2, LONG과 같지만 2진 데이터를 저장하는데 사용된다.

    ※주 : Column의 size는 column 값에 대한 최대 문자수를 결정한다.
             VARCHAR2는 column에 대한 크기를 지정해야 한다.
             NUMBER와 CHAR column도 크기를 지정할 수 있는데 Default값을 쓸 수 있다.

     

2. 제약조건(constraints)

    ◈테이블에 대한 행 추가, 갱신, 삭제 때마다 제약조건을 사용하고자 할 때
    ◈다른 테이블에 의해 참조되어지고 있는 경우 테이블 삭제 방지를 위해
    ◈모든 제약조건은 Data Dictionary에 저장된다.
    ◈제약조건의 이름을 생략했다면 Oracle Server는 SYS_Cn형식으로된 이름을 만들어낸다.
    ◈제약조건은 보통 테이블을 만들 때마다 함께 만든다.
    ◈제약조건은 만든 후에 테이블에 부가되고 또한 임시적으로 사용할 수 없게 할 수도 있다.
     

제약조건 정의

    ◈Column 레벨의 제약조건

        : Column별로 정의. 무결성 제약조건의 어떤 유형도 정의 가능하다.

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

    ◈테이블 레벨의 제약조건

        : NULL 값을 column에 사용할 수 없게 한다.

      Column,….

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

         (column,…),

         

데이터 무결성 제약조건

    제약 조건

    설             명

    NOT NULL

    Column이 NULL 값을 가질 수 없음을 명시
    column 레벨에서만 정의할 수 있다.
    예> CREATE TABLE  friend…
           phone   VARCHAR2(15)  NOT NULL,…

    UNIQUE

    테이블의 모든행에서 고유값을 가져야만 하는 COLUMN이나 COLUMN의 조합을 명시. column 레벨이나 테이블 레벨에서 정의할 수 있으며, 조합된 경우에는 테이블 레벨에서 정의하여야 한다.
    예>  CREATE TABLE  friend…
         phone   VARCHAR2(15) CONSTRAINT friend_phone_nn UNIQUE,….

    PRIMARY KEY

    테이블의 각 행을 유일하게 식별할 수 있는 COLUMN이나 COLUMN의 조합을 명시한다. 테이블에 대한 Prima key를 생성한다.
    NOT NULL값을 허용하지 않는다.
    column 레벨이나 테이블 레벨에서 정의할 수 있으며, 조합된 경우에는 테이블 레벨에서 정의하여야 한다.
    예>  CREATE TABLE  friend…
         phone VARCHAR2(15)

    CONSTRAINT friend_phone_pk PRIMARY KEY,….

    REFERENCES

    column과 관련된 테이블의 PRIMARY KEY사이에 FOREIGN KEY 관계를 설정
    예>  CREATE TABLE  friend…
         name  varchar2(10)
    CONSTRAINT friend_name_fk REFERENCES,….

    CHECK

    각 행이 만족해야 하는 조건을 정의
    예>CREATE TABLE  friend…
       code varchar2(2)
    CONSTRAINT code_ck CHECK (code in ('10','20')),… .

     

3. 테이블 챠트를 기초로 한 테이블 생성

    ◈CREATE TABLE 문법을 이용하여 DB 설계의 테이블 챠트를 기초로 테이블을 만든다.
    ◈문서화와 사용상의 편이 때문에 스크립트 파일을 이용하면 작성하는 데에 유용하다.
    1. 스크립트 파일을 작성한다. CREATE TABLE 문법으로 시작하고 테이블 이름을 쓴다.
    2. 테이블 챠트의 column명, datatype, 길이를 스크립트 파일에 기입한다.
    3. column 레벨 NOT NULL 제약조건을 적되 PRIMARY KEY 제약조건 column은 제외한다.
    4. 제약조건이 한 개의 column로 되어 있으면 PRIMARY KEY 제약조건을 column 제약조건으로 한다.
    5. 제약조건이 여러 개의 column으로 되어 있으면 PRIMARY KEY 제약조건을 테이블 제약조건으로 한다.
    6. UNIQUE, CHECK, FOREIGN 제약조건을 쓴다.
    7. 스크립트 파일을 저장하고 실행한다.
     

테이블 생성 : TEST1

    테이블 챠트 : TEST1

    열 이름

    Datatype

    키유형

    NULL

    UNIQUE

    FK테이블

    FK컬럼

    Id

    Number(7)

    PK

    NN

     

     

     

    Name

    Varchar2(25)

     

     

     

     

     

    Userid

    Varchar2(8)

     

    NN

    UK

     

     

    Sdate

    Date

     

     

     

     

     

    Comments

    Varchar2(25)

     

     

     

     

     

    Mgrid

    Number(7)

     

     

     

     

     

    Title

    Varchar2(25)

     

     

     

     

     

    Deptid

    Number(7)

    FK

     

     

    S_DEPT

    ID

    Salary

    Number(11,2)

     

     

     

     

     

    comm

    Number(4,2)

     

     

     

     

     

     

    제약조건의 정의

    제약 조건

    설             명

    TEST1_ID_PK

    ID column을 테이블의 P.K로 설정. 이 제약조건은 고유한 값이 입력되도록 한다

    TEST1_NAME_NN

    테이블의 LAST-NAME column이 널값을 갖지 못하도록 한다.

    TEST1_USERID_NN

    USERID column이 널값을 갖지 못하도록 한다.

    TEST1_USERID_UK

    USERID column이 값이 중복되지 않도록 한다.

    TEST1_DEPTID_FK

    S_DEPT 테이블에 없는 부서 번호가 S_EMP 테이블에 저장되지 않게 한다.

    TEST1_COMM_CK

    Commission_pct column의 허용값을 제한한다.

     

    실습

    SQL> CREATE TABLE test1
          (id       NUMBER(7)    CONSTRAINT test1_id_pk PRIMARY KEY,
           name     VARCHAR2(25) CONSTRAINT test1_name_nn NOT NULL,
           userid   VARCHAR2(8)  CONSTRAINT test1_userid_nn NOT NULL
                                 CONSTRAINT test1_userid_uk UNIQUE,
           sdate    DATE DEFAULT SYSDATE,
           comments VARCHAR2(25),
           mgr_id   NUMBER(7),
           title    VARCHAR2(25),
           deptid   NUMBER(7)    CONSTRAINT test1_deptid_fk REFERENCES s_dept(id),
           salary   NUMBER(11,2),
           comm     NUMBER(4,2)  CONSTRAINT test1_comm_ck CHECK
                                      (comm IN(10,12.5,15,17.5,20))
          ); [Enter]

     

    테이블 생성확인

    ◈SQL*Plus DESCRIBE명령을 이용하면 데이터베이스 테이블이 있는지 확인하고 column정보를
       확인할 수 있다.
     

    실습 : s_test1 테이블이 만들어졌는지 확인하라.

    SQL> DESC(RIBE)  test1

    ◈NOTE : DESCRIBE 명령으로는 NOT NULL제약조건만을 식별할 수 있다. 그 외의 다른 제약조건은
                  Data dictionary에 있다.


    실습 : 위 test1테이블과 동일한 test2 테이블 생성하시오.

    SQL> CREATE TABLE test2 AS
             (SELECT *             /* 1과 2는 같지 않으므로 조건에 맞는
               FROM test1             Row가 하나도 없으므로 구조가 *
               WHERE 1 = 2);           Create된다 */

     

    실습 : 테이블 생성확인

    SQL> DESC(RIBE)  test2

     

신고

SQL)강의- 제 8 장 변수 사용

.Tech/SQL

출처 : http://naver.kaist.ac.kr/oracle_sql/s08.htm

1. 치환 변수

    ◈명령이 실행될 때마다 사용자에게 입력값을 요구한다.
    SET VERIFY 명령 : SQL*Plus가 치환변수를 값으로 바꾸기 전후의 값을 Display한다
     

단일 앰퍼센드 치환 변수

    ◈SQL*Plus는 사용자 변수를 써서 반환되는 데이터를 동적으로 제한한다.
    ◈SQL문장에서 변수를 식별하기 위하여 앰퍼센드(&)를 사용한다.
    ◈각 변수의 값을 정의할 필요는 없다.
    ◈일시적으로 값을 저장하기 위해 SQL *Plus의 치환변수를 사용한다.

    표기법

    설     명

    &user_variable

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

    &&user_variable

    SQl *Plus가 세션 동안 또는 변수가 재설정되거나 삭제될 때까지 변수와 그 값을 갖고 있다는 것만 제외하면 위와 동일한 역할을 한다. 명령이 실행될 때마다 입력값을 묻지 않는다.


    실습 : 실행시에 사용자에게 프롬프트를 주어 부서번호를 입력할 수 있는 문장을 작성하라.
               직원의 번호, 성과 월급을 포함해야 한다.

    SQL> SELECT dept_id, id, last_name,salary
          2  FROM   s_emp
          3  WHERE  dept_id = &department_number; [Enter]

    Enter value for department_number: 10 [Enter]

    ※ 단일 앰퍼센드를 써서 명령이 실행될 때마다 사용자에게 입력값을 묻는다.
     

치환변수에 문자 및 날짜값을 명시

    ◈문자 및 날짜값은 단일따옴표(')로 에워싸야 한다.
    ◈사용자가 단일따옴표를 입력하지 않도록 앰퍼센트와 변수에 단일 인용부호를 표시한다,

    실습 : 사용자가 프롬프트에서 입력한 직급의 모든 사원의 사번, 성 및 월급여를 검색하여 보시오.

    SQL> SELECT title,id,last_name,salary
          2  FROM   s_emp
          3  WHERE  title = '&job_title';  [Enter]

    Enter value for job_title: Stock Clerk  [Enter]

     

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

    ◈다음을 입력받기 위해 치환변수를 사용할 수 있다.
         - WHERE절, ORDER BY절, columnaud, 테이블명, 전체 SELECT 문장

    실습 : s_ord 테이블에서 컬럼명을 입력받는 경우 1

    SQL> SELECT id,&column_name
          2  FROM   s_ord
          3  WHERE  &condition [Enter]

    Enter value for column_name: total [Enter]
    Enter value for condition: payment_type = 'CASH' [Enter]

     

이중 앰퍼센드 치환 변수

    매번 사용자에게 프롬프로하지 않고 변수값을 재사용하려면 이중 앰퍼센드(&&)를 사용한다.
    값을 입력하도록 요구하는 프롬프트는 한 번만 보게 된다.
    ◈SQL *Plus는 DEFINE 명령을 써서 제공된 값을 저장 한다.

    실습 :  모든 주문에 대한 주문번호와 주문합계를 출력하라. TOTAL column에 따라 정렬하고
                TOTAL column을 입력받기 위해 변수를 사용하라.

    SQL> define column_name = total
             SELECT id,&&column_name
             FROM   s_ord
             ORDER BY &column_name  [Enter]

     

2. 사용자 변수의 정의

    ◈아래 두 개의 SQL*Plus의 명령 중 하나를 써서 변수를 미리 정의한다.
        - DEFINE : CHAR 데이터 유형의 사용자 변수 생성
        - ACCEPT : 사용자 입력을 읽어 변수에 저장
    ◈SQL*Plus는 이중 엠퍼센드 치환 변수에 대해 DEFINE명령을 실행한다.
    ◈DEFINE 명령을 사용할 때 한 개의 공백이 필요하면 공백을 단일 따옴표로 묶어야 한다.
     

사용자 변수의 정의

    SELECT 문장을 실행하기 전에 사용자 변수를 미리 정의할 수 있다. SQL*Plus는 사용자 변수를 정의하고 설정하기 위한 두 가지의 명령인 DEFINE과 ACCEPT명령을 제공한다. 이 중 엠퍼센드가 있는 문장을 실행하면 DEFINE 명령은 변수를 생성하고 변수의 값을 지정한다.

    명령어

    설     명

    DEFINE 변수 = 값

    CHAR datatype의 사용자변수를 생성하고 값을 지정

    DEFINE 변수

    변수, 변수값 및 datatype출력

    DEFINE

    모든 사용자변수. 변수값과 datatype출력

    ACCEPT(아래 참조)

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

        : SQL *Plus는 이중 앰퍼센트(&&) 치환변수에 대해 DEFINE명령을 실행한다.
        : DEFINE명령을 사용할 때 1개의 공백이 필요하면 공백을 단일 따옴표로 묶어야 한다.
     

ACCEPT 명령

    ◈Syntax

      ACCEPT 변수 [datatype] [FORMAT] [PROMPT text] [HIDE]

      ① 변수    : 값을 저장하는 변수의 이름으로 존재하지 않으면 SQL *Plus가 생성한다.
      ② datetype : NUMBER, CHAR 또는 DATE이며, CHAR는 최대 240byte이다.
      ③ FORMAT   : 예를들면, A10 또는 9,999같은 형식을 명시한다.
      ④ PROMPT   : text는 사용자가 값을 입력하도록할 때 출력되는 문장이다.
      ⑤ HIDE     : 예를들면, 암호처럼 사용자가 입력되는 내용을 보이지 않게 한다.

    ◈ACCEPT명령을 치환변수를 참조할 때 SQL *Plus치환변수에 &를 붙이지 마시오.
    ◈사용자로부터 입력값을 받아들일 때 원하는 프롬프트를 작성할 경우에 사용.
    ◈NUMBER 또는 DATE datatype의 변수를 명시적으로 정의할 때 사용.
    ◈보안상의 이유로 사용자 입력사항(변수명)을 숨기고자할 때 사용.


    실습 : 입력받은 부서이름에 대한 지역번호와 지역명을 출력한다.
               Prompt.sql이란 스크립트 파일을 만들고 원하는 메시지로 프롬프트를 주기 위해
               ACCEPT명령을 사용하시오.

    SQL> ed prompt [Enter]
    SET ECHO OFF
    ACCEPT p_dname PROMPT 'Provide the department name: '
    SELECT d.name, r.id, r.name "REGION NAME"
    FROM   s_dept d, s_region r
    WHERE  d.region_id = r.id
           AND  UPPER(d.name) LIKE UPPER('%&p_dname%')
    /

    SQL> start prompt

    Provide the department name: sales [Enter]
    old   4:   AND  UPPER(d.name) LIKE UPPER('%&p_dname%')
    new   4:   AND  UPPER(d.name) LIKE UPPER('%sales%')

     

UNDFINE 명령

    ◈변수들은 아래 중 어느 하나가 발생할 때까지 정의된 채로 존재한다.
    ◈변수에 대해 UNDEFINE 명령을 실행
    ◈SQL *Plus를 빠져 나올 때까지.
    ◈DEFINE 명령을 써서 변수내용을 확인할 수 있다.
    ◈SQL *Plus를 빠져나올 때 해당 세션동안 정의된 변수는 상실 된다.
    ◈세션마다 특정변수를 정의하기 위해서는 변수가 시스템 시동시에 생성될 수 있게 login.sql 파일을
        이용해야한다.

    ♠ 질의 예제 : 부서 위치를 갖는 변수를 만들고 변수의 값과 일치하는 모든 부서이름을 출력하라.

    SQL> DEFINE dname = sales
    SQL> DEFINE dnam

    SQL> SELECT name
         FROM   s_dept
         WHERE  lower(name) = '&dname';

     

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

    변수값을 스크립트 파일에 전달하는 스크립트 파일을 만들고 실행하기 위해서는 아래의 단계를 따른다.
    ① 스크립트 파일에 SELECT문을 적는다.
    ② SELECT 문장에 각 변수를 참조하는 곳마다 &number표기법을 사용한다.
    ③ 명령행에서 파일을 구동할 때 파일 이름 다음에 변수값을 명시한다.
          각 매개변수의 위치는 중요하다.(변수값들을 구분하기 위해 공백을 사용한다.)
                 SQL> START 스크립트 파일 이름 변수1  변수2 …..


    실습 : 직급별로 일련의 보고서를 만들기 위해 param.sql이라는 스크립트 파일을 작성하시오.
               직급을 입력받는 프롬프트 대신 파일을 수행할 때 명령 행에 직급을 입력한다.

    SQL> ed param [Enter]
    SET ECHO OFF
    SELECT id, last_name, salary
    FROM   s_emp
    WHERE  title = '&1'
    /
    SET ECHO OFF

    SQL> @param
    Enter value for 1: Stock Clerk [Enter]
    old   3: WHERE  title = '&1'
    new   3: WHERE  title = 'Stock Cle

     

신고