[Oracle] 오라클 기본정리(2)

l 테이블 생성

 

- ORACLE 데이터 타입

VARCHAR2(size) : 최대 길이가 size인 가변길이 문자값으로 최소길이는 1,최대길이는 2000

CHAR(size) : 길이가 size인 고정길이 문자값으로 기본길이는 1, 최대길이는 255

NUMBER : 38자리까지 유효한 부동 소수점 숫자

NUMBER(p,s) : 38의 범위 중에서 p의 자릿수까지 유효한 숫자값으로 전체 자릿수 p, 소수점 자릿수 s

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

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

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

 

LOB : Text, 그래픽, 이미지 비디오, 사운드등 구조화되지 않은 대형 데이터를 저장한다.

Long, Long Raw 데이터 유형은 과거에 주로 사용된 부분이며,

현재는 대부분 LOB 데이터 유형을 사용한다.

o LOB Type

> CLOB : 문자 대형 객체(Character),

Oracle Server 는 CLOB과 VarChar2 사이에 암시적 변환을 수행한다.

> BLOB : 이진 대형 객체 (Binary), 이미지, 동영상, MP3등...

> NCLOB : 내셔널 문자 대행 객체(National),

오라클에서 정의되는 National Character Set을 따르는 문자

> BFILE : OS에 저장되는 이진 파일의 이름과 위치를 저장, 읽기 전용 모드로만 엑세스 가능.

* 하나의 테이블에 여러개의 LOB 열(Column) 가능

* 최고 4GB까지 저장

* Select로 위치자 반환

* 순서대로 또는 순서없이 데이터 저장

* 임의적 데이터 엑세스

* LOB 구성

- LOB 값 : 저장될 실제 객체를 구성하는 데이터

- LOB 위치자 : 데이터 베이스에 저장된 LOB값의 위치에 대한 포인터

- LOB열에는 데이터가 없고 LOB위치자만 들어있음.

 

 

 

- 테이블 생성

CREATE　TABLE　[스키마]테이블명

(칼럼명1 DATATYPE [DEFAULT 형식] ,

칼럼명2 DATATYPE [DEFAULT 형식] ,

..... ) ;

스키마 : 객체의 집합으로서 테이블의 소유자를 의미

테이블명 : 새로 생성하고자 하는 테이블의 이름

칼럼명 : 테이블에 포함된 칼럼의 이름

DataType : 칼럼이 가질 데이터의 타입과 크기

DEFAULT : 데이터 입력(INSERT)시에 기본 값을 명시

 

- Constraint

제약조건이란 사용자가 원하는 조건의 데이터만 유지하기 위한

즉, 데이터의 무결성을 유지하기 위한 가장 보편적인 방법으로 테이블의 특정 칼럼에 설정하는 성질입니다.

테이블을 생성할 때 제약 조건을 반드시 줄 필요는 없습니다.

 

- Constraint 성질

Constraint란 데이터 무결성 유지를 위하여 사용자가 지정할 수 있는 성질

* 모든 Constraint은 데이터 사전(Data Dictionary)에 저장됨

* 의미있는 이름을 부여했다면 Constraint을 참조하기 쉬움

* Constraint의 이름은 표준 객체 명명법을 따르는게 좋음

* Constraint의 이름은 한 사용자 내에서 중복될 수 없음

* Constraint의 이름을 생략했다면 ORACLE은 SYS_Cn형식의 이름을 생성하게 된다

 

- Constraint 정의 방법

+ 컬럼레벨 제약조건

각 컬럼별로 제약조건을 정의

제약조건의 어떤 유형도 정의 가능

 

문형

컬럼 [CONSTRAINT 제약조건 이름] 제한조건 유형

 

+ 테이블레벨 제약조건

컬럼에 대한 정의와는 별개로 정의

하나 이상의 컬럼(composite column)에 대하여 정의할때 사용

NOT NULL을 제외한 어떤 제약조건도 정의 가능

 

문형

컬럼 , ...

[CONSTRAINT 제약조건 이름] 제한조건 유형 (컬럼1,컬럼2..)

 

 

 

 

 

+ Constraint 종류

o NOT NULL 제약 조건

NOT NULL 제약 조건은 NULL값을 컬럼에 사용할 수 없게 합니다.

NOT NULL 제약 조건이 없는 컬럼은 NULL값을 가질 수 있습니다.

NOT NULL 제약 조건은 컬럼 레벨로만 정의할 수 있습니다.

 

o UNIQUE 제약 조건

UNIQUE 제약조건은 컬럼이나 컬럼의 조합을 고유 키로 지정합니다.

테이블에는 이 키에 대해 같은 값을 갖는 행이 하나 밖에 없습니다.

UNIQUE 키가 한 컬럼에 대한 것이라면 NULL 값을 쓸 수 있습니다.

컬럼 레벨이나 테이블 레벨에서 모두 정의할 수 있습니다.

UNIQUE 컬럼에 대해 UNIQUE 인덱스가 자동으로 생성됩니다.

 

o PRIMARY KEY 제약 조건

테이블에 단 하나의 Primary key만 허용합니다.

여러 컬럼일지라도 NULL값을 허용하지 않습니다.

테이블이나 컬럼 레벨에서 정의할 수 있습니다.

UNIQUE 인덱스를 자동으로 생성합니다.

 

o FOREIGN KEY 제약 조건

foreign key는 자식(child) 테이블에 정의합니다.

부모(Parent) 테이블의 값과 일치하거나 NULL이어야 합니다.

테이블이나 컬럼 레벨에서 정의할 수 있습니다.

Foreign Key 제약조건을 줄 때 주의사항

1. 참조하고자 하는 테이블이 먼저 생성되어 있어야 합니다.

2. 참조하고자 하는 컬럼이 PRIMARY KEY 또는 UNIQUE 제약조건이 있어야 합니다.

3. 참조하고자 하는 컬럼의 데이터 타입이 일치해야 합니다.

* ON DELETE CASCDE옵션

FOREIGN KEY제약조건을 줄 때 ON DELETE CASCDE옵션을 줄 수 있습니다.

참조하는(child) 테이블쪽에 이 옵션을 주면 참조되는(parent) 테이블의 행을 삭제 가능하게

할 뿐만 아니라 참조하는 행들도 자동으로 삭제해 주는 옵션입니다.

 

o CHECK 제약 조건

CHECK 제약 조건은 각 행이 만족해야 하는 조건을 정의합니다.

* CHECK 제약 조건에서 사용할 수 없는 것은 다음과 같습니다.

CURRVAL, NEXTVAL, LEVEL, ROWNUM에 대한 참조

SYSDATE, UID, USER, USERENV 함수에 대한 호출

다른 행의 값을 참조

Oracle Server의 예약어는 쓸 수 없다.

컬럼이나 테이블 레벨에서 모두 정의할 수 있다.

 

SQL> CREATE TABLE c_emp

(id　　　　 NUMBER(5)

CONSTRAINT c_emp_id_pk PRIMARY KEY ,

name VARCHAR2(25)

CONSTRAINT c_emp_name_nn NOT NULL,

salary NUMBER(7,2) ,

phone VARCHAR2(15) CONSTRAINT c_emp_phone_ck CHECK (phone like '3429-%') ,

dept_id NUMBER(7) CONSTRAINT c_emp_dept_id REFERENCES S_DEPT(ID) ) ;

[출처] [기초강좌] Oracle 개발자 입장에서 사용하기...(4) (파워빌더 커뮤니티(PBExpertClub)) |작성자 또치

 

 

l 데이터 조작어

 

- INSERT

INSERT INTO 테이블명[(컬럼명...)]

VALUE (값...)

 

 

 

- UPDATE

UPDATE 테이블명

SET 컬럼명 = 값, ....

[WHERE 조건식]

 

 

 

- DELETE

DELETE FROM 테이블명

[WHERE 조건식];

 

 

 

- SUBQUERY 이용

INSERT INTO 테이블명[(컬러명...)]

VALUE (값 = (서브커리)...)

 

UPDATE 테이블명

SET 컬럼명 = (서브쿼리)....

[WHERE 조건식=(서브쿼리)]

 

 

 

l TRANSACTION CONTROL 과 DICTIONARY

 

 

 

- 명시적인 (Explicit) 트랜잭션 제어 명령어

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

SAVEPOINT 이름 : 현재의 트랜잭션에 savepoint를 지정

ROLLBACK [TO SAVEPOINT 이름] : 아직 저장되지 않은 모든 데이터 변경 사항을 취소하고

현재의 트랜잭션을 종료

 

 

 

- 암시적인 (Implicit) 트랜잭션 제어 명령어

자동 COMMIT : DDL 명령이나 DCL 명령 실행

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

ROLLBACK [TO SAVEPOINT 이름] : SQL*Plus의 비정상적 종료나 시스템 실패

 

 

 

- COMMIT나 ROLLBACK 이전 데이터 상태

단지 buffer에만 영향을 받았기 때문에 데이터의 이전 상태는 복구 가능

현재 사용자는 SELECT 문으로 DML의 결과를 확인 가능

다른 사용자는 현재 사용자가 수행한 DML문의 결과를 볼 수 없음

변경된 행은 Lock이 설정되어서 다른 USER가 변경할 수 없음

 

 

 

- SAVEPOINT를 이용한 ROLLBACK

SAVEPOINT명령어로 현재의 트랜잭션중에 위치를 지정할 수 있음

ROLLBACK TO SAVEPOINT문을 써서 특정 SAVEPOINT까지 ROLLBACK 할 수 있음

SQL>INSERT...

SQL> SAVEPOINT in_point;

Savepoint created.

SQL> UPDATE..

SQL> ROLLBACK TO point;

Rollback complete.

 

 

 

 

l DICTIONARY

 

 

 

- DICTIONARY의 개념

데이터베이스가 생성될 때 ORACLE SERVER에 의해서 자동으로 생성되며 그 내용도 자동으로 갱신,

유지보수됩니다.

DATA Dictionary는 주로 읽기 전용의 테이블들의 집합으로써 사용자가 직접 access하여

데이터를 조작할 수 없고 단지 사용자의 DDL에 연관한 작업이 자동으로 DATA Dictionary에 반영됩니다

ORACLE Server 사용자명, 사용자에게 허가된 권한, 데이터베이스 객체명(TABLE, SEQUENCE, VIEW, INDEX 등)

테이블 제약조건 감사(Auditing) 정보 등이 기록되어 있습니다.

 

 

 

- DICTIONARY의 종류

1) View의 종류

2) 접두어(prefix) 설 명

3) USER_ 사용자가 소유한 객체에 관한 정보를 저장

4) ALL_ 사용자에게 액세스가 허용된 객체에 관한 정보를 저장

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

6) V$ 서버의 성능과 Locking에 관한 정보를 저장. 일반적으로 DBA에게만 허용됨.

7) 기타 뷰

8) 뷰 이름 설 명

9) DICTIONARY 모든 데이터 사전 테이블, 뷰, 동의어 저장

10) TABLE_PRIVILEGES 사용자가 권한을 부여했거나(GRANTOR) 부여받은(GRANTEE) 오브젝트에 대한 권한

11) IND USER_INDEXES 의 동의어(SYNONYMS)

 

 

 

- DICTIONARY 검색

SQL> DESC dictionary

SQL> SELECT * FROM dictionary ;

사용자가 엑세스할 수 있는 모든 데이터 사전 뷰를 검색

SQL> SELECT * FROM dictionary WHERE table_name LIKE 'USER%';

사용자가 소유한 모든 데이터 사전 뷰의 이름을 검색

SQL> SELECT object_name FROM user_objects WHERE object_type = 'TABLE';

사용자가 소유한 모든 테이블을 조회

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

DICTIONARY 테이블의 COMMENTS 칼럼에서 제약조건과 관련된 데이터 사전 테이블 조회

 

[출처] [기초강좌] Oracle 개발자 입장에서 사용하기...(5) (파워빌더 커뮤니티(PBExpertClub)) |작성자 또치

 

 

l 데이터 정의어(DDL)

 

- 명령어의 종류

CREATE TABLE : 새로운 테이블을 생성

DROP TABLE : 기존 테이블의 구조 및 모든 행을 삭제

ALTER TABLE : 기존 테이블을 변경, 컬럼의 추가 , 수정, 제약조건 추가,삭제,활성화,비활성화

TRUNCATE : 기존 테이블의 구조는 남기고 모든 행을 삭제

RENAME : Object의 이름을 바꿈

COMMENT : 테이블이나 컬럼에 주석문 달기

 

이러한 명령어는 모두 데이터 정의 명령어(DDL)로서 실행시 자동 COMMIT되고

ROLLBACK될 수 없으므로 주의해서 실행해야 합니다.

 

- 테이블 삭제

DROP TABLE 테이블명 [CASCADE CONSTRAINTS ] ;

모든 데이터가 테이블에서 삭제됨

트랜잭션은 자동 COMMIT되므로 ROLLBACK할 수 없음.

해당테이블의 모든 인덱스가 삭제됨

CASCADE CONSTRAINTS옵션은 종속된 제약조건을 삭제함

 

- 테이블 구조 변경

o 칼럼의 변경

- 칼럼의 추가

ALTER TABLE 테이블명

ADD (컬럼 datatype [DEFAULT 형식][CONSTRAINT 정의][, 컬럼 datatype] ...) ;

SQL> ALTER TABLE test1

ADD (col1 VARCHAR2(25)

CONSTRAINT c_col1_ck CHECK (col1 IN ('사장','부장','과장','대리','사원' ) ),

col2 date DEFAULT SYSDATE );

 

- 칼럼의 수정

ALTER TABLE 테이블명MODIFY (컬럼 datatype [DEFAULT 형식][NOT NULL제약조건][, 컬럼 datatype] ...) ;

* 컬럼의 크기, 기본값 (DEFAULT), NOT NULL 제약조건을 변경가능

* 컬럼의 크기를 확장

* 컬럼이 NULL이거나 테이블에 데이터가 없을때

컬럼의 크기를 축소하거나 데이터 타입을 변경 가능

* DEFAULT를 변경하면 다음 입력할 때부터 적용됨

* 컬럼에 NULL값이 없을 경우에만 NOT NULL 제약 조건을 추가 가능

SQL> ALTER TABLE Test1 MODIFY (col1 VARCHAR2(25));

 

o CONSTRAINT의 변경

- 제약조건의 추가

ALTER TABLE 테이블명

ADD [CONSTRAINT 제약 조건명] 제약조건유형 (칼럼명) ;

* NOT NULL제약조건은 테이블 레벨 제약조건으로 기술할 수 없으므로,칼럼의 MODIFY절에서 가능

SQL> ALTER TABLE Test1 ADD CONSTRAINT c_test1_uk UNIQUE(col3) ;

 

- 제약조건의 삭제

ALTER TABLE 테이블명

DROP CONSTRAINT 제약 조건명 [CASCADE] ;

SQL> ALTER TABLE Test1 DROP CONSTRAINT c_test1_uk ;

 

 

 

- 제약조건의 활성화/비활성화

> ALTER TABLE 테이블명ENABLE CONSTRAINT 제약 조건명 ;

> ALTER TABLE 테이블명DISABLE CONSTRAINT 제약 조건명 [CASCADE];

* CASCADE는 참조 조건과 함께 수정

SQL> ALTER TABLE test1 DISABLE CONSTRAINT s_test1_pk CASCADE;

SQL> ALTER TABLE test1 ENABLE CONSTRAINT s_test1_pk ;

 

 

 

o 그 외의 DDL

- RENAME

* OBJECT의 이름 변경

* 테이블, VIEW, SEQUENCE, SYNONYM의 이름 변경

* 객체의 소유자만이 바꿀 수 있음

> RENAME 이전이름 TO 새로운 이름 ;

SQL> RENAME test1 TO test_1_old:

 

- TRUNACTE

* 테이블의 구조를 남기고 모든 데이터 삭제하기

* 테이블의 모든 행을 삭제

* 테이블이 사용한 저장 공간을 반환

* TRUNCATE 명령이 행의 삭제를 Rollback할 수 없는데 비해,

DELETE 명령은 삭제된 행을 Rollback가능

* TRUNCATE 명령의 경우 증가된 Table의 사이즈를 초기화 시키는데 비해,

DELETE 명령은 Table의 사이즈를 유지

> TRUNCATE TABLE 테이블명 ;

SQL> TRUNCATE TABLE Test1;

 

- COMMENT

* 테이블이나 칼럼에 주석문 달기

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

ALL_COL_COMMENTS

USER_COL_COMMENTS

ALL_TAB_COMMENTS

USER_TAB_COMMENTS

> COMMENT ON TABLE 테이블명 IS '주석문장' ;

> COMMENT ON COLUMN 테이블명.칼럼명 IS '주석문장' ;

SQL> COMMENT ON TABLE test1 IS '테스트 테이블' ;

SQL> COMMENT ON COLUMN test1.col1 IS '테스트 컬럼1' ;

[출처] [기초강좌] Oracle 개발자 입장에서 사용하기...(6) (파워빌더 커뮤니티(PBExpertClub)) |작성자 또치

 

 

l 데이터 제어어

 

 

 

- DATABASE SECURITY

o Privileges

* Privileges란 특정한 SQL문장을 수행하거나 특정 Object에 허가된 작업을 의미합니다.

* Privileges에는 특정한 SQL 문장을 실행할 수 있는 권한인 시스템 권한(system privilege)과

특정한 오브젝트에 대한 접근을 제어하는 권한인 오브젝트 권한(object privilege)로

나눌 수 있습니다.

 

 

 

o Role

* 권한 부여와 접근제어 관리를 손쉽게 하기 위해 연관된 권한들의 집합을 묶어서

정의해 놓은 것을 역할(role)이라고 합니다.

 

 

 

o Schema

* Schema란 Table, View, Sequence, Procedure, Function 등의 오브젝트들의 집합으로서

Schema는 데이터베이스 사용자에 의하여 소유되어 있고 사용자의 이름과 같은 이름을 가지고

있습니다.

* 자신의 스키마에 속한 오브젝트가 아닌 것을 액세스하려고 할 때는 그 오브젝트에 대한

액세스 권한을 부여받아야 합니다.

 

 

 

- PRIVILEGES

o SYSTEM PRIVILEGES

* DBA이 갖는 상위 레벨의 시스템 권한

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

> 사용자 삭제(DROP USER)

> 테이블 삭제(DROP ANY TABLE) 　

> 테이블 백업(BACKUP ANY TABLE) 등

 

 

 

o 새로운 사용자 생성 및 삭제 문형

> CREATE USER 사용자명 IDENTIFIED BY 암호 :

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

부여할 수 있으며 사용자는 다음과 같은 시스템 권한을 부여받을 수 있습니다.

CREATE SESSION : 데이터베이스에 접속가능한 권한

CREATE TABLE : 테이블 생성 가능 권한

CREATE SEQUENCE : 시퀀스 생성 가능 권한

CREATE VIEW : 뷰 생성 가능 권한

CREATE PROCEDURE.. 등 : 프로시져 생성 권한

SQL> CREATE USER NSC IDENTIFIED BY NSC_PWD ;

 

> DROP USER 사용자명 [CASCADE] ;

CASCADE옵션은 사용자가 포함한 object까지 같이 drop하는 옵션입니다

 

 

 

o 사용자 암호 변경 문형

> ALTER USER 사용자명 IDENTIFIED BY 암호 :

SQL> ALTER USER NSC IDENTIFIED BY NSC_NEW ;

 

 

 

o 시스템 권한 부여 및 박탈

> GRANT 권한 [, 권한...] TO 사용자 [, 사용자 ...] ;

> REVOKE 권한 [, 권한...] FROM 사용자 [, 사용자 ...] ;

* DBA는 특정 시스템 권한을 사용자에게 허가할 수 있습니다.

* 사용자가 권한을 받으면 즉시 그 권한을 쓸 수 있습니다.

SQL> GRANT create table, create view, create sequence TO NSC ;

SQL> REVOKE create sequence FROM NSC ;

 

 

- OBJECT PRIVILEGES

DBA는 특정한 오브젝트에 대해서 사용자에게 작업을 수행하도록 허가할 수 있습니다.

오브젝트 권한은 오브젝트 유형에 따라서 다양하고,

오브젝트의 소유자는 다른 사용자에게 특정 오브젝트 권한을 부여할 수 있습니다.

> GRANT 권한 [(컬럼명)] [ 권한 ...|ALL ] ON Object명

TO 사용자 | ROLE | PUBLIC [, 사용자...」[WITH GRANT OPTION ] ;

> REVOKE 권한 [(컬럼명)] [ 권한 ...|ALL ] ON Object명 FROM 사용자 | ROLE | PUBLIC [, 사용자...」 ;

 

 

 

o 각 Object에 따른 PRIVILEGES 유형

 

* WITH GRANT OPTION으로 부여된 권한을 회수하면 그 권한을 받은 다른 사용자에게서도 권한이 회수됩니다.

SQL> GRANT SELECT , INSERT (col1, col2) ON Test1 TO nsc_A, nsc_B ;

SQL> REVOKE INSERT ON Test1 FROM nsc_b ;

nsc_A.SQL> GRANT select, insert ON Test1 TO nsc_C WITH GRANT OPTION ;

 

 

 

- ROLE

ROLE이란 사용자에게 허가할 수 있는 관련된 PRIVILEGES의 그룹을 말합니다.

ROLE을 이용하면 권한부여와 회수를 쉽게 할 수 있음

한 사용자가 여러 ROLE을 access할 수 있고, 여러 사용자에게 같은 ROLE을 부여할 수 있음

사용자는 ROLE에 ROLE을 부여할 수도 있음

 

 

 

o ROLL의 생성

> CREATE ROLE role이름 ;

* ROLE의 생성 및 부여는 다음과 같은 순서

ROLE의 생성 --> ROLE에 권한 부여 --> ROLE을 사용자 또는 ROLE에게 부여

SQL> CREATE ROLE create_obj ;

SQL> GRANT create table, create view TO create_obj ;

SQL> GRANT create_obj TO nsc_A, nsc_B ;

 

 

 

- PRIVILEGES와 ROLE에 관한 DICTIONARY 정보

* PRIVILEGES와 ROLE에 관련된 정보를 가지고 있는 데이터 사전 뷰의 종류

 

 

SQL> SELECT username, granted_role FROM user_role_privs;

 

[출처] [기초강좌] Oracle 개발자 입장에서 사용하기...(7) (파워빌더 커뮤니티(PBExpertClub)) |작성자 또치