2008. 8. 29. 16:02

Chap 14. 뷰

뷰(view)는 물리적인 테이블에 근거한 논리적인 가상테이블이다.
실질적으로 데이터를 저장하고 있지 않지만 실제 테이블을 사용하는 것과 동일하게 뷰를 사용하여 테이블을 관리할 수 있다.
뷰는 반복적으로 쿼리를 날리는 것을 방지하기 위해서 사용된다.

뷰의 기본형식
create view view_name
as
select_statement

=========== 30번 부서에 소속된 사원들의 사번 및 이름 출력==================
create view emp_view30
as
select empno, ename, deptno
from emp_cp01
where deptno=30;

desc emp_view30; // 뷰의 구조 보기
select * from emp_view30; // emp_view30 내용 보기
======================================================================
================= insert 문으로 뷰에 행을 추가하기 =========================
insert into emp_view30
values(1111, 'AAAA',30);
select * from emp_view30; // view에 데이터가 추가된 것을 확인할 수 있다.
select * from emp_cp01; // emp_cp01에 데이터가 추가된 것을 확인할 수 있다.
=====================================================================

뷰의 종류
단순 뷰 : 하나의 테이블에 의해 정의한 뷰
새로 생성되는 뷰에 대해 컬럼명을 제시하지 않으면 기본테이블의 컬럼명을 상속 받는다.
================= insert 문으로 뷰에 행을 추가하기 ====================
create view dept_sum
as
select deptno, sum(sal) sum_sal
// 함수를 사용할 경우 반드시 별칭을 반드시 지정해야 한다.
from emp_cp01
group by deptno;
==================================================================

복합 뷰 : 두 개 이상의 기본 테이블에 의해 정의한 뷰
================= 사번과 이름 부서명을 복합뷰로 검색 =================
create view emp_view_join
as
select e.empno, e.ename, d.dname
from emp e, dept d
where e.deptno=d.deptno;
select * from emp_view_join;
=====================================================================

뷰의 제거
drop view view_name
뷰의 변경
create or replace view view_name
select_statement
2008. 8. 27. 17:55

Chap 13. 무결성 제약 조건

데이터 무결성 제약 조건(data integrity constraint rule) 이란 테이블에 부적절한 자료가 입력되는 것을 방지하기 위해서 테이블을 생성할 때 각 컬럼에 대해서 정의하는 여러가지 규칙을 말합니다.
데이터 무결성 제약 조건은 데이터베이스 설계 단계에서 데이터의 정확성을 유지하기 위해서 다양한 규칙을 고려하여 테이블을 생성할 때 표현하게 됩니다.

무결성 제약 조건

역할

NOT NULL

해당 컬럼 값으로 NULL을 허용하지 않음

UNIQUE

테이블 내에서 해당 컬럼 값은 항상 유일무이한 값을 가질 것

PRIMARY KEY

해당 컬럼 값은 반드시 존재해야 하고 유일해야 한다는 조건
즉, NOT NULL 조건과 UNIQUE 조건을 결합한 형태

FOREIGN KEY

해당 컬럼의 값이 타컬럼의 값을 참조해야만 함
즉, 참조되는 컬럼에 없는 값은 입력 불가함

CHECK

해당 컬럼에 저장 가능한 데이터 값의 범위나 사용자 조건을 지정


기본 키(PRIMARY KEY) 제약 조건 : 기본 키는 테이블 내의 해당 행을 다른 행과 구분할 수 있도록 하는 식별 기능을 가진 가장 대표적인 컬럼으로 NULL 값이나 중복되는 값을 가질 수 없습니다.

무결성 제약 조건에는 컬럼 레벨 정의 방법과 테이블 레벨 정의 방법이 있습니다.
컬럼 레벨 정의 방법은 아래에서 보듯이 테이블을 정의할 때 컬럼내에서 제약조건(기본키)을 정의하는 것을 말합니다.
create table dept02
(deptno number(2) constraint dept02_deptno_pk primary key,
dname varchar2(15),
loc varchar2(15));
일반적으로 제약조건을 지정할 때는 constraint dept02_deptno_pk 제약조건 테이블명_컬럼명_제약조건 유형 의 형태로 기술합니다.

테이블 레벨 정의 방법은 주로 하나의 컬럼에 여러개의 제약조건을 부여할 경우 사용되며
아래에서 보듯이 컬럼 정의를 먼저 내리고 따로 생성된 컬럼에 대한 제약조건을 지정하는 것을 말합니다.
이때 주의하실 사항은 not null 조건은 테이블 레벨 정의 방법으로는 지정할 수 없습니다. 
create table dept02
(deptno number(02),
dname varchar2(15),
loc varchar(15),
constraint dept02_deptno_pk primary key(deptno));

제약 조건 확인하기
select constraint_name, constraint_table from user_constraints;
// 해당 유저의 제약조건을 전부 검색

제약 조건 변경하기
조건 변경은 이미 생성된 테이블을 변경하는 것이므로 alter table 문을 이용해야 합니다.

제약조건 추가 시
alter table dept02
add constraint dept03_deptno_fk foreign key(deptno);
제약 조건 제거 시
alter table dept02
drop constraint dept03_deptno_fk ;

제약 조건 타입

constraint_type

의미

P

PRIMARY KEY

R

FOREIGN KEY

U

UNIQUE

C

CHECK, NOT NULL


부모 테이블과 자식 테이블
주체가 되는 테이블을 부모 테이블이라 하며 관계가 능동형으로 표현된다
주체 관계가 모호한 경우에는 어느 테이블의 데이터가 먼저 정의되어야 하는가를 기준으로 부모테이블과 자식테이블을 나눈다.

부자관계의 테이블에서는 부모테이블의 기본키가 자식테이블의 외래키가 되어야한다.
2008. 8. 27. 16:39

Chap 11. 트랜잭션 관리

트랜잭션(transaction)이란 SQL 명령문들을 하나의 논리적인 작업 단위로 처리하는 것을 말하며, 데이터의 일관성을 유지하면서 안정적으로 데이터를 복구시키기 위해 도입되었다.
트랜잭션은 하나 또는 여러개의 DML(Data Manipulation Language)명령 (INSERT, UPDATE, DELETE)을 모두 묶어 놓은 하나의 논리적인 작업 단위를 의미합니다.

트랜잭션 제어를 위한 명령어에는
COMMIT : 트랜잭션이 발생하는 경우 새롭게 생성되거나 갱신된 데이터들이 물리적으로 영구히 저장되는 것을 의미합니다.
일단 COMMIT이 실행되면 다시 복구할 수 있는 방법은 없습니다.
ROLLBACK : 이전 상태로 다시 돌아가도록 함으로서 지금까지 수행했던 데이터베이스의 변경을 모두 무효화하겠다는 의미를 갖습니다.
즉, DML 트랜잭션이 실행되고 나서 DML 트랜잭션이 COMMIT 되기 전에는 언제든지 트랜잭션이 발생하기 전 상태로 되돌아갈 수 있습니다.
SAVEPOINT : 여러 개의 SQL문의 실행을 수반하는 트랜잭션의 경우, 사용자가 트랜잭션 중간 단계에서 세이브포인트를 지정할 수 있으며, 세이브포인트는 롤백과 함께 사용해서 현재 트랜잭션 내의 특정 세이브포인트까지 롤백합니다.

자동 커밋 : DDL(Data Definition Language - CREATE, ALTER, DROP, RENAME, TRUNCATE)문은 자동으로 COMMIT이 발생합니다.
DDL문은 실행 중에 에러가 발생하여 제대로 실행되지 않았어도 자동으로 COMMIT이 실행됩니다.

특정 세이브포인트까지 작업을 되돌릴 때의 유의 사항
(세이브포인트)
 A           B           C           D
--------------------------------------------
| IUD        |    IU     |    IUN    |     IU     |
--------------------------------------------
                           <---------------------

C 지점으로 롤백하기 위해서 다음과 같은 문장을 실행합니다.
SQL>ROLLBACK TO  C;
그 후에 D지점으로 롤백하기 위해서 다음과 같은 문장을 실행한다면 어떻게 될까요?
SQL>ROLLBACK TO  D;
D지점으로 롤백 하는 명령어는 실행할 수 없습니다. ROLLBACK TO  C;를 수행함으로써 그 다음 세이브포인트인 D의 위치도 사라지기 때문입니다.

2008. 8. 21. 17:57

Chap 10. 테이블에 데이터 추가, 수정, 삭제하기

데이터를 입력하기 위해 사용하는 insert 문
형식 insert into 테이블명            insert into dept07          // dept테이블을 그대로 복사한
컬럼명 values(값);                    (deptno, dname, loc)    // dept07 테이블
                                              values (50,'TEST','YEOSU');
insert 문에서 컬럼명을 생략해도 정상적인 실행이 가능
insert into dept07
values (50,'TEST','YEOSU');

오류가 발생되는 예
1. 컬럼명에 기술된 항목보다 values 값이 적거나 많을때
values ( 50, 'TEST') OR values (50, 'TEST','YEOSU','TEST1');
2. 컬럼명이 잘못 기술되어 있을 때
(deptn, dname, loc)
3. 컬럼의 데이터 타입과 값이 서로 맞지 않을 때
values (50, 'TEST', YEOSU); // loc 컬럼은 문자형이므로 단일 인용부호('')를 사용해야함

서브쿼리로 로우 추가하기
create table dept01  // 테이블 구조만 복사하고 내용은 들어가지 않는다.
select * from dept
where 1=0;

create table dept01  // 테이블 구조와 내용이 전부 복사됨.
select * from dept;

update 문 : 테이블에 저장된 테이터를 수정
update 테이블명
set 컬럼명=변경할 값
where 조건절

'수업 > 오라클 + PL/SQL' 카테고리의 다른 글

Chap 13. 무결성 제약 조건  (0) 2008.08.27
Chap 11. 트랜잭션 관리  (0) 2008.08.27
Chap 9. 테이블 생성 및 변경, 삭제하기  (0) 2008.08.20
Chap3,4 SQLPLUS 명령어 & 연산자  (1) 2008.08.20
Chap 2. SQL의 기본  (2) 2008.08.20
2008. 8. 20. 18:40

Chap 9. 테이블 생성 및 변경, 삭제하기

오라클에서 제공하는 자료형

자료형의 종류

의미

CHAR(N)

주어진 크기만큼 고정 길이의 문자 저장
1바이트~2000바이트

VARCHAR2(N)

주어진 크기만큼 가변 길이의 문자 저장
1바이트~4000바이트

NVARCHAR2(N)

국가별 국자 집합에 따른 크기의 문자 또는 바이트의 가변 길이 문자
 1바이트~4000바이트

NUMBER(p, s)

정밀도와 스케일로 표현되는 숫자

DATE

날짜 형식을 저장

ROWID

테이블내 행의 고유 주소를 가지는 64진수 문자
해당 6바이트(제한된 ROWID) 또는 10바이트(확장된 ROWID)

BLOB

대용량의 바이너리 데이터를 저장
최대 4GB

CLOB

대용량의 텍스트 데이터를 저장
최대 4GB

BFILE

대용량의 바이너리 데이터를 파일 형태로 저장
최대 4GB

TIMESTAMP(n)

DATE 형의 확장된 형태

INTERVAL YEAR TO MONTH

년과 월을 이용하여 기간을 저장

INTERVAL DAY TO SECOND

일, 시, 분, 초를 이용하여 기간을 저장
두 날짜 값의 정확한 차이를 표현한는데 유용


1. 테이블 생성
create table 테이블명
(컬럼명 자료형)
=======사번, 이름, 급여로 테이블 구성=============================================
CREATE TABLE em01
 (empno NUMBER(4),
 ename varchar2(20),
 sal NUMBER(7, 2));
====================================================
식별자의 선언
컬럼명이나 테이블명을 선언하기 위해서는 다음과 같은 원칙을 지켜야 한다.
1. 시작은 반드시 영문자
2. 예약어(SELECT 등)는 쓸 수 없다.
3. 유일한 단어이어야 한다.
4. _,$는 사용가능하다.

서브쿼리를 이용한 테이블 생성
테이블의 구조와 데이터까지 복사하는 경우
create table emp01
as
select * from emp;
원하는 컬럼만 복사하는 경우
create table emp01
as
select empno, ename, sal from emp;
테이블의 구조만 복사
create table emp01
as
select * from emp
where 1=0;      // where 조건 절이 항상 거짓이 되므로 빈 테이블이 생성된다.

2. 테이블의 구조 변경
컬럼 추가
alter table 테이블명                   alter table emp01
add (컬럼명 자료형);                add (email varchar2(10));
컬럼 변경
alter table emp01
modify (email varchar2(40));
컬럼 삭제
alter table emp01
drop column email;
테이블 제거
drop table emp01;
================================================================================
EMP 테이블의 사원번호, 이름, 직급, 매니저 칼럼과 동일한 칼럼명과 유형을 갖는 테이블을 EMP01란 이름으로 생성하라.
create table emp01
as
select empno, ename, job, mgr from emp;
이미 존재하는 EMP01 테이블에 EMP 테이블과 동일한 구조로 입사일자 칼럼을 추가하라.
alter table emp01
add(hiredate date);
EMP01 테이블의 직급 칼럼의 크기를 20으로 변경하라.(기존 job varchar2(9))
alter table emp01
modify (job varchar2(20));
EMP01 테이블에서 매니저 칼럼을 삭제하라.
alter table emp01
drop column mgr;
================================================================================
테이블 명을 변경하는 RENAME 문
형식 : RENAME old_name TO new_name
EMP01 테이블의 이름을 EMP02로 변경하라.
rename emp01 to emp02;

'수업 > 오라클 + PL/SQL' 카테고리의 다른 글

Chap 11. 트랜잭션 관리  (0) 2008.08.27
Chap 10. 테이블에 데이터 추가, 수정, 삭제하기  (0) 2008.08.21
Chap3,4 SQLPLUS 명령어 & 연산자  (1) 2008.08.20
Chap 2. SQL의 기본  (2) 2008.08.20
Chap 8. 서브쿼리  (0) 2008.08.20