한국어

tc_backup

sqlplus 기본 설정 명령

show user; // 현재 접속한 유저명이 나옴
show linesize; // 한줄에 출력하는 라인 수
set linesize 140; // linesize를 140으로 변경하기.
show pagesize; // 한 페이지에 출력되는 라인수.(한 페이지 사이즈보다 넘으면 각 컬럼 명 다시 한번 출력하여 보기 쉽게 하여준다)
set pagesize; // 페이지사이즈 변경
set heading off; // 컬럼명들이 출력되지 않고 데이터만 출력이 되게 된다.
set timing on; // 명령문을 실행하고 결과를 얻어온 시간을 표시하여 준다.
spool d:\20090515001.txt; // spool을 한 다음부터 명령어를 치거나 출력결과를 지정된 파일에 저장하게 된다.
spool off; // spool을 끈다.
conn system/비밀번호; // connect의 약자로 다른 계정으로 로그인이 가능하다.
host; // 잠시 cmd 창을 실행하게 된다. cmd 창을 종료하면 다시 sql 창을 사용할 수 있게 된다.
password; // 암호 변경

/////////////////////////////////////////////////////////////////////
참고 사항

- sql 명령문은 대문자를 쓰고, 테이블명이나 컬럼은 소문자를 쓰는 것을 권장한다. 하지만, 대소문자를 구별하지 않으므로 반드시 지켜야 하는 것은 아니다.
- 오라클에서는 alias만 ""을 사용하고 모든 문자열은 ''을 사용한다.
- having에는 allias(별칭)을 사용할수 없다.
- sam.sql 파일을 만들어놓고 @d:\1111_Java_S\sam.sql 명령을 입력하면 해당 명령을 한번에 실행에 가능하다.
- sysdate 칼럼은 서버의 현재 시간을 date 형으로 돌려준다.

desc emp; // describe의 약자로 emp 테이블의 구조를 보여준다.(컬럼명 출력)
desc user_constraints; - 생성한 제약 조건 확인 가능한 테이블.
desc user_tables; - 현재 유저가 사용할 수 있는 테이블 목록
    select table_name from user_tables;
    table_name 컬럼에 들어간 테이블 목록은 모두 대문자로 변환되어 입력되어지게 된다.
desc tab; - user_tables의 table_name view를 의미함.

select CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME from user_constraints;
로 봤을때 C는 type 부분이다.
P : Primary key
U : Unique
C : Check
R : Foreign Key

/////////////////////////////////////////////////////////////////////

비교 연산자

= : 같다
> : 보다 크다
>= : 보다 크거나 같다
< : 보다 작다
<= : 보다 작거나 같다
<>, !=, ^= : 같지 않다
NOT column_name = : 같지 않다
NOT column_name > : 보다 크지 않다
BETWEEN a AND b : a와 b 사이에 있다(a,b값 포함)
IN (value, value) : list 값 중 어느 하나와 일치한다.
LIKE 'aaa' : 'aaa' 형태와 일치한다
IS NULL : NULL 값을 가졌다

/////////////////////////////////////////////////////////////////////
그룹 함수 사용 기본 구조

SELECT        
  [column,]
    group_function(column) [,group_function(column),.]
FROM        table_name
[WHERE       condition]
[GROUP BY    group_by_expr1[,group_by_expr2, . . . .]]
[HAVING      condition]
 [ORDER BY    column];

/////////////////////////////////////////////////////////////////////
자주 사용하는 함수

AVG(DISTINCT|ALL|n) : NULL값을 제외한 n개 행의 평균값
COUNT(DISTINCT|ALL|expr|*) : NULL이 아닌 행의 개수
MAX(DISTINCT|ALL|expr) : 최대값
MIN(DISTINCT|ALL|expr) : 최소값
SUM(DISTINCT|ALL|n) : 합계
nvl(column, 0) : column의 값이 null인 경우 0으로 대체함

/////////////////////////////////////////////////////////////////////
조인 기본 구조

하나 이상의 테이블로부터 자료를 검색하기 위하여 조인을 사용
일반적으로Primary Key(이후 PK로 사용)와 Foreign Key(이후 FK로 사용)을 사용하여 Join하는 경우가 대부분

Cartesian Product
 - 모든 가능한 행들의 Join
Equijoin
 - Join조건이 정확히 일치하는 경우 사용
Non-Equijoin
 - Join조건이 정확히 일치하지 않는 경우
Outer Join
 - Join조건이 정확히 일치하지 않는 경우에도 모든 행들을 출력
Self Join
 - 하나의 테이블에서 행들을 Join하고자 할 경우
Set Operators
 - 여러 개의 SELECT문장을 연결하여 작성

/////////////////////////////////////////////////////////////////////
1. Outer-Join
두 테이블을 조인할 때 비록 조인조건을 만족하지 않더라도 조인결과에 나오고 싶게 하고 싶은 경우가 있다. 이럴때 아우터조인을 사용할 수 있는데, 항상 기준 테이블을 주고, 기준 테이블에 있는 건들은 조인조건에 의해 버려지지 않도록 한다. 아우터조인의 대상이 되는 테이블의 조건에는 (+) 기호를 붙인다. 그러면 아우터조인의 대상이 되는 테이블의 컬럼값들은 조인조건에 만족되면 있는 값들이 출력되고, 만족되지 않는 경우에는 NULL로 출력된다.
예를 들어, 부서명과 사원명을 출력하되 사원이 하나도 없는 부서명도 출력하라.
SELECT d.dname, e.ename
FROM dept d, emp e
WHERE d.deptno = e.deptno(+);

만약, 아우터조인의 대상이 테이블에 조건을 주어야 하는 경우에는, 그 조건에 있는 컬럼에도 (+) 기호를 붙여야 하며, 이 의미는 조인조건을 만족치 못하지만 아우터조인에 의해서 살아난 경우에는 그 조건을 검사하지 말라는 의미이다. 그 조건에 (+) 기호를 붙이지 않는 경우에는 그 조건에 의해서 아우터조인의 효과가 무력화된다.
예를 들어, 모든 부서를 나열하되, 매니저가 있는 경우에 매니저이름을 보여라.
SELECT d.dname, e.ename
FROM dept d, emp e
WHERE d.deptno = e.deptno(+)
AND e.job(+) = 'MANAGER';


2. Self-Join
자기 자신 테이블과 조인하는 것을 말하는 것으로, 대개 Self Relationship을 가지는 설계에서 많이 사용된다.
예를 들어, 사원번호 7902번의 사원의 이름과 그의 관리자명을 구하라는 쿼리에서
SELECT e.ename, m.ename
FROM emp e, emp m
WHERE e.empno = 7902
AND e.mgr = m.empno;

만약 위의 예제에서 관리자들을 최상위까지 나열하라. 단, 최상위까지는 3명의 관리자가 있을 수 있다. 라고 한다면 아래와 같이 할 수 있다.
SELECT e.ename, m1.ename, m2.ename, m3.ename
FROM emp e, emp m1, emp m2, emp m3
WHERE e.empno = 7902
AND e.mgr = m1.empno
AND m1.mgr = m2.empno
AND m2.mgr = m3.empno;

그러나 몇 명의 관리자가 있는지 SQL 개발단계에서 알 수 없다면 셀프조인으로 해결할 수는 없고, CONNECT BY로서 해결해야 한다.
SELECT LEVEL, e.ename
FROM emp e
START WITH e.empno = 7902
CONNECT BY PRIOR mgr = empno;


3. Non-equijoin
조인은 항상 FK에 의해 참조되는 컬럼과의 = 로서만 조인되는 것은 아니다. 때로는 크기비교 연산자나 LIKE 등에 의해 데이터가 연결될 수도 있다.
예를 들어, 사원들의 직급과 연봉수준을 출력하라는 쿼리가 있을때에 SALGRADE 테이블과 EMP 테이블은 BETWEEN으로 연결될 것이다.
SELECT e.ename, e.job, g.grade
FROM emp e, salgrade g
WHERE e.sal BETWEEN g.losal AND g.hisal;

때로 조인을 하다보면 조인조건이 SUBSTR()에 의해 조인되는 컬럼의 변형(Suppress)를 가하게 되어 인덱스를 사용하지 못하는 경우가 생길 수 있다. 이러한 경우에는 더 유리한 플랜으로 유도하기 위해서는 SUBSTR()으로 잘라서 비교하는 것이 아니고 반대편을 ‘%’를 붙여서 LIKE로 비교하면 변형이 발생하지 않고 인덱스를 사용하는 조인으로 유도할 수 있다.

아래 사이트에서 참조함.
http://iclickyou.com/1341


Set Operator의 종류

UNION : 각 결과의 합(합집합:중복되는 값은 한번 출력)
UNION ALL : 각 결과의 합(합집합)
INTERSET : 각 결과의 중복되는 부분만 출력(교집합)
MINUS : 첫번째 결과에서 두번째 결과를 뺌(차집합)

/////////////////////////////////////////////////////////////////////
SUBQUERY(서브쿼리)

다른 SELECT 문장의 절에 내장된 SELECT 문장
NESTED SUBQUERY는 MAIN QUERY이전에 한번만 수행되며 SUBQUERY의 결과를 MAIN QUERY에 의해 조건으로 사용
단일 행 서브쿼리
다중 행 서브쿼리
다중 열 서브쿼리

/////////////////////////////////////////////////////////////////////
테이블 생성

기본 문법
CREATE  TABLE        [schema.]table_name  
(
  column    datatype      [DEFAULT  expr]
               [column_constraint],   . . . . . . . .
  [table_constraint]
);

1. EMP 테이블에서 30부서에 근무하는 사원의 정보만 추출하여 EMP_30 ㅌ이블을 생성하여라. 단 열은 사원번번호,이름,업무,입사일자,급여,보너스를 포함한다.
create table emp_30
as
(select empno, ename, job, hiredate, sal, comm
from emp
where deptno = 30);
똑같은 복사를 하기 위해서는 select *하면 된다.
데이터는 제외하고 컬럼 구조만 생성하고 싶을 때에는 where 1 != 1로 하여 조건을 무조건 거짓으로 만들면 된다.

/////////////////////////////////////////////////////////////////////
Data Type

VARCHAR2(n) : 가변 길이 문자 데이터 형(1~4000Byte)
CHAR(n) : 고정 길이 문자 데이터 형(1~2000Byte)
NUMBER(p, s) : 전체 p 자리 중 소수점 2자리 까지 표현하는 숫자 데이터 형
DATE : 7Byte(BC 4712년 1월 1일부터 AD 9999년 12월 31일) 날짜 데이터 형
LONG : 가변 길이 문자 데이터 형(1~2Gbyte)
CLOB : 단일 바이트 가변 길이 문자 데이터 형(1~4Gbyte)
RAW(n) : n Byte의 원시 이진 데이터 형(1~2000)
LONG RAW : 가변 길이 원시 이진 데이터 형(1~2Gbyte)
BLOB : 가변 길이 이진 데이터(1~4Gbyte)
BFILE : 가변 길이 외부 파일에 저장된 이진 데이터(1~4Gbyte)

/////////////////////////////////////////////////////////////////////
제약조건(Constraint)

열 단위 제약조건, 테이블 단위 제약조건
    Not null
    Default
    Primary key
    Foreign key
    Unique key
    Check

CREATE TABLE column_constraint(
--     Not null 연산자
    name varchar(10) not null,
--    check( 컬럼명 연산자 조건 )
    age NUMBER(3) CHECK (age > 19 and age <50),
--    default hiredate 에 아무런 값이 없을 경우 sysdate 를 입력
    hiredate DATE default sysdate,
    addr varchar2(50),
--    Unique 값은 입력되지만 그 값은 해당 컬럼에서 유일한 데이터이여만 한다.
    phone varchar2(14) unique,
--    직접 제약사항    
    ssn char(14) not null PRIMARY KEY
);
--[출처] 오라클 제약조건 테스트|작성자 엔피카

-- primary key
CREATE TABLE DEPT_TAB (
     DEPTNO          NUMBER(2),
     DNAME           CHAR(14),
     LOC             CHAR(13),
     CONSTRAINT DEPT_DEPTNO_PK PRIMARY KEY (DEPTNO)
);

-- foreign key
CREATE TABLE emp_teb (
     empno          NUMBER(4),
     ename          VARCHAR2(10),
     job            VARCHAR2(9),
     mgr            NUMBER(4) CONSTRAINT EMP_SELF_KEY REFERENCES EMP (EMPNO),
     hiredate       DATE,
     sal            NUMBER(7,2),
     comm           NUMBER(7,2),
     deptno         NUMBER(2) NOT NULL,
     CONSTRAINT EMP_DEPTNO_FK FOREIGN KEY (DEPTNO) REFERENCES DEPT (DEPTNO),
     CONSTRAINT EMP_EMPNO_PK PRIMARY KEY (EMPNO)
);

/////////////////////////////////////////////////////////////////////
Oracle에서 사용되는 객체
TABLE : 행과 열로 구성된 기본적인 저장 구조
VIEW : 하나 이상의 테이블에서 데이터의 부분집합을 논리적으로 표현
SEQUENCE : 고유한 번호를 자동으로 발생시키는 객체로 주로 PK 값 생성에 사용
INDEX : 질의(SELECT) 성능을 향상시키기 위하여 사용하는 물리적인 저장 구조
SYNOYN : 객체에 대한 이름을 부여(별칭allias와 유사)

/////////////////////////////////////////////////////////////////////
테이블 생성 예제)
1. EMP 테이블에서 30부서에 근무하는 사원의 정보만 추출하여 EMP_30 테이블을 생성하여라. 단 열은 사원번번호,이름,업무,입사일자,급여,보너스를 포함한다.
create table emp_30
as
(select empno, ename, job, hiredate, sal, comm
from emp
where deptno = 30);
똑같은 복사를 하기 위해서는 select *하면 된다.
데이터는 제외하고 컬럼 구조만 생성하고 싶을 때에는 where 1 != 1로 하여 조건을 무조건 거짓으로 만들면 된다.

//////////////////////////////////////////////////////////////
desc user_constraints; - 생성한 제약 조건 확인 가능한 테이블.
desc user_tables; - 현재 유저가 사용할 수 있는 테이블 목록
    select table_name from user_tables;
    table_name 컬럼에 들어간 테이블 목록은 모두 대문자로 변환되어 입력되어지게 된다.
desc tab; - user_tables의 table_name view를 의미함.

select CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME from user_constraints;
로 봤을때 C는 type 부분이다.
P : Primary key
U : Unique
C : Check
R : Foreign Key

//////////////////////////////////////////////////////////////
테이블 수정

(테이블의 구조를 수정하는 것. column의 타입 변경)

테이블 수정 예제)
create table sama(
a number;
b varchar2(20)
);

alter table sama
add constraint sama_a_pk primary key(a);
--sama 테이블을 생성한 이후에 a칼럼을 primary key로 새롭게 constraint(제약조건)을 추가한다.

//////////////////////////////////////////////////////////////
객체 이름 변경

RENAME table TO new_table;

테이블 안의 레코드만 삭제(컬럼만 유지)
TRUNCATE TABLE table_name;

테이블 삭제(컬럼과 레코드 모두 삭제)
DROP TABLE table_name CASCADE CONSTRAINT
--만일 참조하는 컬럼이 있을 경우(외래키가 있는 경우, 즉 PK인경우임) 삭제가 되지 않는다.
--CASCADE CONSTRAINT 옵션을 주면 강제로 삭제가 가능하다.

//////////////////////////////////////////////////////////////
DML(Data Manipulation Language) 데이터 조작 언어

INSERT, UPDATE, DELETE가 대표적
UPDATE나 DELETE는 기본적으로 모든 레코드가 대상이 된다. 즉 조건이 없을 경우 모든 레코드가 삭제 또는 수정되어버리게 되므로 무조건 조건을 주는 습관을 갖어라!

//////////////////////////////////////////////////////////////
레코드 삽입 방법(INSERT)

기본 문법
INSERT INTO table_name (컬럼명, 컬럼명) VALUES (값, 값);
INSERT INTO table_name (컬럼명, 컬럼명) VALUES (값, 값) subquery; // 다중 삽입

레코드 삽입 기본 예제)
create table emps as select * from emp;
desc emps;
insert into emps values(9999, 'KKK', 'SUPERMAN', 7566, sysdate, 9999, 9999, 30);
// emp 테이블과 동일한 emps 테이블을 생성하고, 테이블 구조를 보여주고, 레코드를 추가한다.

EMP 테이블에서 empno, ename, sal, hiredate 의 구조를 empt로 생성하여, 10번 부서만 선택하여 empt 테이블에 삽입하라.
create table empt as select empno, ename, sal, hiredate
from emp
where 1 !=1;
insert into empt (select empno, ename, sal, hiredate from emp where deptno = 10);
위와 같이 해도 되고 아래와 같이 해줘도 됨.
create table empt1 as (select empno, ename, sal, hiredate from emp where deptno = 10);

//////////////////////////////////////////////////////////////
레코드 수정 방법
UPDATE

기본 문법
UPDATE table_name SET column1 = value, column2 = value WHERE condition;
UPDATE table_name SET column1, column2 = (SELECT column1, column2 FROM table_name WHERE condition) value WHERE condition;
// 첫번째꺼는 컬럼 = 벨류 스타일이고 두번째는 서브쿼리로 여러가지 레코드를 수정할 때

레코드 수정 예제)
1. EMP 테이블을 EMPS로 복사 생성하고 사원 번호가 7788인 사원의 부서를 10번으로 번경하라.
select deptno from emps where empno = 7788;
update emps set deptno = 10 where empno = 7788;
    까지만 하게 되면 변경된 것처럼 보이지만 새로운 세션으로 접속해서 확인해보면 변경되어있지 않음을 알 수 있다. 이유는 오라클은 auto commit이 되지 않기 때문.

2. EMPS 테이블에서 사원 번호가 7788인 사원의 부서를 20, 급여를 3500으로 변경하여라.
update emps set deptno = 20, sal = 3500 where empno = 7788;
select deptno, sal from emps where empno = 7788;
또는 update emps set detpno,sal(20,3500) where empno = 7788;

4. EMP 테이블에서 scott의 업무와 급여가 일치하도록 JONES 업무와 급여를 개인하여라
select job, sal from emps where ename = 'JONES';
update emps set (job, sal) = (select job, sal from emps where ename = 'SCOTT') where ename = 'JONES';

직원의 급여를 20% 인상하라
select ename, sal from emps;
update emps set sal = sal+(sal*0.2)  where 1 = 1;

scott의 급여를 부서코드가 20인 부서의 평균 급여로 수정하라
select ename, sal from emps where ename = 'SCOTT';
update emps set sal = (select avg(sal) from emp where deptno = 20) where ename = 'SCOTT';

급여가 3000 초과인 사람만 삭제하여라.
select ename, sal from emps where sal > 3000;
delete emps where sal > 3000;

emps 테이블의 모든 레코드를 삭제하라
delete emps where 1 = 1;

//////////////////////////////////////////////////////////////
Transaction(트랜젝션)

DML(INSERT, UPDATE, DELETE) : 작업의 논리적인 단위로 취급하는 임의의 수의 DML문장으로 구성
DDL(CREATE, ALTER, DROP) : 오직 하나의 DDL 문장으로 구성
DCL(GRANT, REVOKE) : 죅하나의 DCL 누장으로 구성

Transaction의 시작
    SQL문이 제일 처음 실행될 때
Transaction 종료
    Commit이나 Rollback(취소, 또는 되돌리기?)
    DDL(CREATE, ALTER, DROP), DCL 실행
    기계장애, 정상 종료
    Deadlock 발생

sql 세션을 2개 띄워서 1번째 세션에서 아래 명령을 실행하고 2번째 세션에서 select로 확인하여 보자
update emps set deptno = 30 where deptno = 10;
그러면 두 번째 세션에서는 아직 변경이 완료되지 않고 있음을 알 수있다
create table xxx(
a number
);
그러한 상황에서 create처럼 transaction의 종료를 의미하게 되면 바로 세션2에서도 변경이 된 것을 확인할 수 있다.

//////////////////////////////////////////////////////////////
롤백(Rollback)과 커밋(commit)

delete emps where deptno = 20;
savepoint aa;
update emps set job  = 'aaa' where 1 = 1;
rollback to savepoint aa;
// 이렇게 할 경우 그냥 rollback; 하면 delete 전까지 롤백되지만 rollback to savepoint aa; 하게 되면 해당 지점까지만 rollback이 된다.

한 컬럼에 대하여 commit이 되기 전 상황에 변경된 세션이 있을 경우 다른 세션에서 동일한 컬럼에서의 변경을 하게 되면 다른 세션은 변경 명령이 적용되지 않고 그대로 멈춰있게 된다.(break 대기 모드랄까?) 이럴때 원래 세션에서 commit이 되면 break가 풀리게 되고 0행의 갱신이 되었다고 나오게 된다.

//////////////////////////////////////////////////////////////
Sequence(시퀀스)

- Primary key나 Unique 제약조건에서 주로 사용하는 객체
- 자동적으로 유일 번호를 발생시키는 일련번호 발행기
- 공유 가능

CURRVAL
- 가장 최근에 입력한 번호 확인
- 다른 사용자에게도 유일한 값 반환

NEXTVAL
- 다음에 입력될 번호
- 다른 사용자에게도 유일한 값 반환

값의 간격 발생 상황
- Rollback(롤백하게 되면 생성된 값은 사라지지만, sequence의 nextval의 값은 증가한 것이 그대로 유지된다)
- System Crash
- 다른 테이블에 의해 사용될 때

기본 문법
CREATE  SEQUENCE  sequence_name
    [INCREMENT  BY  n] --- 몇씩 증가할 것인가(생략시 1)
    [START  WITH  n] -- 맨 처음 시작값을 얼마부터(생략시 1)
    [{MAXVALUE n | NOMAXVALUE}] -- 최대값
    [{MINVALUE n | NOMINVALUE}] -- 최소값
    [{CYCLE | NOCYCLE}] -- 맥스벨류가 되면 다시 START 값으로 돌아갈 것인가
    [{CACHE | NOCACHE}];

ALTER  SEQUENCE  sequence_name
    [INCREMENT  BY  n]
    [{MAXVALUE n | NOMAXVALUE}]
    [{MINVALUE n | NOMINVALUE}]
    [{CYCLE | NOCYCLE}]
    [{CACHE | NOCACHE}];

DROP  SEQUENCE  sequence_name;

//////////////////////////////////////////////////////////////
Sequence 기본 예제)
create table it(
a number primary key,
b varchar2(10)
);

create sequence it_seq increment by 1 start with 1;

insert into it values(it_seq.nextval, 'aaa');
-- nextval의 기본적인 사용법 예제

select it_seq.currval from dual;

//////////////////////////////////////////////////////////////
View

테이블이나 다른 View를 기초로 한 논리적인 테이블
테이블에 대한 보안 강화(원하는 컬럼만 선태적으로 보여줄 수 있음.권한 강화)
복잡한 질의를 간단하게 이용 가능(define같은 의미?)
동일한 데이터를 다른 view로 표현 가능
Simple view
- 하나의 테이블에서만 데이터 유래
- 그룹이나 함수를 포함하지 않음
Complex view
- 다중 테이블로부터 유래
- 그룹이나 함수 포함
With Check Option
- View에서 insert, update 불가능(Simple view만 가능함)
With Read Only
- 읽기 전용

기본 문법
CREATE  [OR  REPLACE]  [FORCE | NOFORCE]  VIEW view_name [(alias[,alias,...])] -- 만드는데 or replace가 있으면 기존에 뷰가 있으면 변경
AS Subquery
[WITH  CHECK  OPTION  [CONSTRAINT  constraint ]] -- 주어진 조건에 위배되지 않게 검사를 함
[WITH  READ  ONLY] -- 읽기 전용

DROP  VIEW  view_name;

계정에 create view 권한 주기
system 계정으로 로그인 grant create view to 계정명;

//////////////////////////////////////////////////////////////
View 생성 기본 예제 1)
emps 테이블에서 job이 SALESMAN인 사람들만 볼 수 있는 뷰를 만드시오
(emps는 emp 테이블을 복사하여 만든다)

create table emps as select * from emp;

create or replace view view_sales
as
select empno, job, sal, deptno
from emps
where job = 'SALESMAN';

select * from view_sales;

update emps set sal = 600 where empno = 7499;
update view_sales set sal = 7000 where empno = 7521;

select empno, sal from emps;
-- [WITH  READ  ONLY]가 주어져 있지 않다면 view를 통해서도 원래의 레코드 수정과 추가가 가능함.

create or replace view view_sales
as
select empno, job, sal, deptno
from emps
where job = 'SALESMAN'
with check option;

update view_sales set sal = 4000 where empno = 7499;

update view_sales set job = 'aaaa' where empno = 7654;
-- job이 SALESMAN인 경우만 해당이 되는데 그 조건을 aaaa로 변경하는 것은 조건에 위배된다. 오류 발생

//////////////////////////////////////////////////////////////
뷰가 필요
emps 테이블의 번호, 이름, 급여
dept 테이블 부서, 부서명, 부서위치
job이 SALESMAN
위와 같은 작업을 하는 쿼리문이 여러번 반복된다면, 매번 길게 쓸 필요 없이 view를 이용하면 된다.

View 생성 기본 예제 2)
select empno, ename, sal, e.deptno, dname, loc
from emps e, dept d
where e.deptno = d.deptno and job = 'SALESMAN';

create or replace view view_s
as
select empno, ename, sal, e.deptno, dname, loc
from emps e, dept d
where e.deptno = d.deptno and job = 'SALESMAN';
하여 view를 생성하면 간단하게 view_s라고 하여 사용할 수 있다.
select * from view_s;

//////////////////////////////////////////////////////////////
Synonym(동의어)

객체의 원래 이름을 숨기고 다른 사용자 소유의 객체에 대한 엑세스가 쉽도록 하기위해 사용

기본 문법
CREATE  [PUBLIC]  SYNONYM  synonym_name
FOR  object_name; -- 생성한 유저만 사용 가능하지만 PUBLIC을 붙이면 모든 계정에서 사용이 가능함. 그러나 ----PUBLIC  권한이 없는 경우는 생성이 불가능함.

DROP  [PUBLIC]  SYNONYM  synonym_name;

//////////////////////////////////////////////////////////////

create synonym sg for salgrade;

select * from salgrade;
select * from sg;

connt system/비밀번호;
select * from scott.emp;

create synonym semp for scott.emp;

//////////////////////////////////////////////////////////////

DECODE(컬럼명, 비교값1, 대신값1, 비교값2, 대신값2, 대신값3);

create table testa(
    a varchar2(10)
);

insert into testa values('A');
insert into testa values('C');
insert into testa values('A');
insert into testa values('B');
insert into testa values('B');
insert into testa values('F');
insert into testa values('A');

select a, decode(a, 'A', '사과', 'B', '바나나', 'C', '배', '몰라') as "등급"
from testa;

select a, case a
    when 'A', then '사과'
    when 'A', then '바나나'
    when 'A', then '배'
    else '몰라'
    end "등급"
from testa;

위와 같은 것을 아래로도 사용 가능함.

case
    when d between 90 and 100 then '90-100'
    when d between 80 and 89 then '80-89'
    when d between 70 and 79 then '70-79'
    when d between 60 and 69 then '60-69'
    else '0-69'
end

//////////////////////////////////////////////////////////////
DCL(Data Control Language)
테이터베이스를 사용하게 될 계정을 생성하거나 삭제하는 명령어

기본 문법
CREATE USER user_name
IDENTIFIED [BY  password | EXTERNALLY ]
 [ DEFAULT TABLESPACE tablespace ]

 - user_name : 생성될 사용자 이름
 - BY password : 사용자가 데이터베이스에 의해 인증되도록 지정하며, 데이터베이스 유저 로그온시 사용하는 비밀번호
 - EXTERNALLY : 사용자가 운영 체제에 의해서 인증되도록 지정
 - DEFAULT TABLESPACE : 사용자 스키마를 위한 기본 테이블 스페이스를  지정

SQL> CONN TEST/TEST
SQL> CONN SYSTEM/MANAGER
SQL> GRANT connect, resource TO TEST ;
SQL> CONN TEST/TEST

비밀번호 변경 기본 문법
 ALTER USER user_name
 [ IDENTIFIED {BY password | EXTERNALLY } ]
 [ DEFAULT TABLESPACE tablespace ]
 [ PASSWORD EXPIRE ]

DROP USER user_name [CASCADE];
-- CASECADE는 강제로 삭제하는 것

시스템 권한
 - 시스템 권한은 사용자가 데이터베이스에서 특정 작업을 수행 할 수 있도록 함
 - 약 126개의 시스템 권한이 있으며 그 수는 계속 증가 중임
 - 권한의 ANY 키워드는 사용자가 모든 스키마에서 권한을 가짐을 의미
 - GRANT 명령은 사용자 또는 Role에 대해서 권한을 부여
 - REVOKE 명령은 권한을 삭제

시스템 권한의 종류 몇 가지   
 -  CREATE SESSION : 데이터 베이스를 연결할 수 있는 권한
 -  CREATE ROLE : 오라클 데이터베이스 역할을 생성할 수 있는 권한
 -  CREATE VIEW : 뷰의 생성 권한
 -  ALTER USER : 생성한 사용자의 정의를 변경할 수 있는 권한
 -  DROP USER : 생성한 사용자를 삭제 시키는 권한

create table testa(
a number
);

//////////////////////////////////////////////////////////////
테이블스페이스 생성 방법

CREATE TABLESPACE prj2
    DATAFILE 'c:\prj04.dbf'  SIZE  10M
    online;
-- user 생성하기
CREATE USER jsp1 IDENTIFIED BY aaaa
DEFAULT TABLESPACE prj2
QUOTA unlimited ON prj2 ;  

-- user 권한 부여하기
grant create session, create table to jsp1;

-- 새로운 사용자로 접속하기
 conn jsp1/aaaa;

-- 테이블 생성하기
CREATE TABLE test(
    a number
);

-- view 생성하기
create or replace view v_t as select * from testa;

-- 권한 오류 발생하면 system 계정으로 create view 권한 주기
grant create view to jsp1;


//////////////////////////////////////////////////////////////
GRANT와 REVOKE 권한

GRANT [system_privilege | role] TO [user | role | PUBLIC]
[WITH ADMIN OPTION];
-- WITH ADMIN OPTION을 주면 부여받은 권한을 다른 사용자에게 줄 수 있다.(권한 재부여)

[문법설명]
 - system_privilege : 부여할 시스템 권한의 이름
 - role : 부여할 데이터베이스 역할의 이름
 - user, role : 부여할 사용자 이름과 다른 데이터 베이스 역할 이름
 - PUBLIC : 시스템 권한, 또는 데이터베이스 역할을 모든 사용자에게
             부여 가능
 - WITH ADMIN OPTION : 권한을 부여 받은 사용자도 부여 받은 권한을
                          다른 사용자 또는 역할로 부여할 수 있게 되며,
                          만약 사용자가 WITH ADMIN OPTION과 같이
                          역할을 부여 받는다면 부여된 역할은
                          그 사용자에 의해 변경 또는 삭제 가능함

REVOKE [system_privilege | role] FROM [user | role | PUBLIC];

//////////////////////////////////////////////////////////////
Object Privileges(객체 권한)

   객체 권한은 유저가 소유하고 있는 특정한 객체를 다른 사용자들이
   액세스 하거나 조작 할 수 있게 하기 위해서 생성을 함

  - 테이블이나 뷰, 시퀀스, 프로시저, 함수, 또는 패키지 중 지정된 한
    오브젝트에 특별한 작업을 수행 할 수 있게 함.   
  - Object 소유자는 다른 사용자에게 특정 Object Privileges를 부여 가능
  - PUBLIC으로 권한을 부여하면 회수할 때도 PUBLIC으로 해야 함
  - 기본적으로 유저가 소유한 오브젝트에 대한 모든 권한은 자동적으로
    획득 됨
  - WITH GRANT OPTION 옵션은 롤 에 권한을 부여할 때는
    사용할 수 없음

//////////////////////////////////////////////////////////////
GRANT 기본 문법

GRANT object_privilege [column] ON object
TO {user[,user] | role | PUBLIC]
[WITH GRANT OPTION]

문법 설명
 - object_privilege : 부여할 객체 권한의 이름
 - object : 객체명
 - user, role : 부여할 사용자 이름과 다른 데이터 베이스 역할 이름
 - PUBLIC : 오브젝 권한, 또는 데이터베이스 역할을 모든 사용자에게
             부여할 수 있음
 - WITH GRANT OPTION : 권한을 부여 받은 사용자도 부여 받은 권한을
다른 사용자 또는 역할로 부여할 수 있게 됨

//////////////////////////////////////////////////////////////
REVOKE 기본 문법

REVOKE {privilege[,privilege,..] | ALL} ON object
FROM {user[,user] | role | PUBLIC]
[CASCADE CONSTRAINTS]

문법 설명
 - 객체 권한의 철회는 그 권한을 부여한 부여자만이 수행 할 수 있다.
 - CASCADE CONSTRAINTS : 이 명령어의 사용으로 REFERENCES객체
   권한에서 사용된 참조 무결성 제한을 같이 삭제 할 수 있다.
 - WITH GRANT OPTION으로 객체 권한을 부여한 사용자의 객체 권한을
   철회하면, 권한을 부여 받은 사용자가 부여한 객체 권한 또한 같이
   철회되는 종속철회가 발생

REVOKE 기본 예제)
scott/tiger 계정으로 접속하여
grant select on emp to jsp1;

jsp1/aaaa 계정으로 접속하여
select * from scott.emp;
jps1에 select 권한만 주어서 select만 가능해짐

grant all on emp to jps1; 하게 되면 모든 권한을 주게 됨.

//////////////////////////////////////////////////////////////
ROLE

ROLE 이란 사용자에게 허가할 수 있는 권한들의 집합
  - ROLE을 이용하면 권한 부여와 회수를 쉽게 할 수 있다.
  - ROLE은 Create Role권한을 가진 User에 의해서 생성 됨.
  - 한 사용자가 여러개의 ROLL을 ACCESS할 수 있고,  
    여러 사용자에게 같은 ROLE을 부여할 수 있다.
  - 시스템 권한을 부여하고, 취소할 때와 동일한 명령을
    사용하여 사용자에게 부여하고, 취소 함.
  - 사용자는 ROLE에 ROLE을 부여할 수 있다.
  - 오라클 데이터베이스를 설치하면 기본적으로
    CONNECT(create session 등), RESOURCE(create view 등), DBA ROLE이 제공

기본 문법
CREATE  ROLE role_name

ROLE의  부여 순서
 ① ROLE의 생성  :  CREATE ROLE manager
 ② ROLE에 권한 부여 : GRANT create session, create table
                        TO manager
 ③ ROLE을 사용자 또는 ROLE에게 부여 : GRANT manager
                                         TO scott, test;

SQL> CREATE ROLE manager;
SQL>GRANT create session, create table TO manager;
SQL>GRANT manager TO scott, test;

//////////////////////////////////////////////////////////////
tablespace, grant, role 설정 예제)

새로운 테이블스페이스를 생성하고, jsp2/bbbb 계정을 생성하고 itrole 룰을 적용한다.

create tablespace prj3
datafile 'c:\prj03.dbf' size 10m
online;

create user jsp2 identified by bbbb
default tablespace prj3
quota unlimited on prj3;

create role itrole;
grant create session, create table to itrole;
grant itrole to jsp3;

//////////////////////////////////////////////////////////////

오라클 데이터베이스를 생성하면 기본적으로 몇 가지의 Role이 생성
DBA_ROLES 데이터 사전을 통하여 미리 정의된 Role을 조회 할 수 있다.
 
SQL>SELECT * FROM DBA_ROLES;

//////////////////////////////////////////////////////////////
RESOURCE

 - Store Procedure나 Trigger와 같은 PL/SQL을 사용할 수
  있는 권한 들로 이루어져 있다
 - PL/SQL을 사용하려면 RESOURCE Role을 부여해야 함.
 - 유저를 생성하면 일반적으로 CONNECT, RESOURCE 롤 을
   부여

SQL>SELECT grantee, privilege
        FROM DBA_SYS_PRIVS
        WHERE grantee = 'RESOURCE';

//////////////////////////////////////////////////////////////
CONNECT
 
- 오라클에 접속 할 수 있는 세션 생성 및 테이블을 생성하거나
 조회 할 수 있는  가장 일반적인 권한들로 이루어져 있다.
- CONNECT Role이 없으면 유저를 생성하고서도 Oracle에
  접속 할 수가 없다.
- 아래의 명령어로 CONNECT Role이 어떤 권한으로 이루어져
  있는지 확인 할 수 있다.

 SQL>SELECT grantee, privilege
        FROM DBA_SYS_PRIVS
        WHERE grantee = 'CONNECT';

//////////////////////////////////////////////////////////////
우편번호 db 받는 방법
http://www.zipfinder.co.kr/
우편번호원본파일 메뉴에서- * Type 3  다운로드

우편번호 db가 들어가게 될 테이블 생성
create table post(
    ZIPCODE varchar2(7),
    SIDO varchar(4),
    GUGUN varchar2(15),
    DONG varchar2(26),
    RI varchar2(45),
    ST_BUNJI varchar(11),
    ED_BUNJI varchar(9),
    SEQ number
);

우편번호 db 받은 것이 dbf 파일인데 엑셀로 로드하여 csv로 다시 저장한다

csv 파일을 db에 추가시키는 방법

SQL*Loder를 사용하여 csv 파일을 디비에 추가시키는 방법

sqlldr.exe 명령어를 사용한다.
입력에 사용될 파일 : 데이터 파일 'c:\post.csv'
파일을 삽입하게 될 명령 스크립트 파일 : 제어파일 'd:\post.ctl'

post.ctl 파일 내용 ///////////////////////////////////////////////////
LOAD DATA
INFILE 'c:\post.csv'
APPEND / REPLACE    //- 둘중 하나 선택. APPEND는 기존 데이터에 추가, REPLACE는 변경
INTO TABLE POST
FIELDS TERMINATED BY ','
    (
        zipcode,
        sido NULLIF sido = blanks,
        GUGUN NULLIF GUGUN = blanks,
        DONG NULLIF DONG = blanks,
        RI NULLIF RI = blanks,
        ST_BUNJI NULLIF ST_BUNJI = blanks,
        ED_BUNJI NULLIF ED_BUNJI = blanks,
        SEQ
    )

//////////////////////////////////////////////////////////////////
위 작업까지 하면 준비 작업 완료. 아래 명령으로 디비 삽입.
삽입이 되지 않는 데이터는 bad에 저장이 되게 된다.
sqlldr userid=scott/tiger control='c:\post.ctl' bad='c:\post.bad'

5만여건의 데이터가 입력이 된다. 그러나 insert 방식으로 하게 되면 시간이 상당히 오래 소요되게 된다.

입력된 우편번호 데이터 확인하기
select * from post where dong like '%안양2동%';

//////////////////////////////////////////////////////////////////

분류 :
DBMS
태그 :
조회 수 :
20501
등록일 :
2010.07.08
00:03:41 (*.234.246.12)
엮인글 :
http://www.rain9.com/xe/tc_backup/3946/38c/trackback
게시글 주소 :
http://www.rain9.com/xe/tc_backup/3946
List of Articles
번호 제목 글쓴이 날짜 조회 수
220 [iphone] decrypt 도구 file 엔신 2012-06-11 16582
219 udp tunnel 엔신 2012-06-11 11357
218 ubuntu APM(apache2 php mysql) 설치 방법 엔신 2012-06-11 17275
217 [perl] AES 암호화/복호화 엔신 2012-06-11 20346
216 iPhone code sign 엔신 2012-06-11 9902
215 [excel] 최대 행과 열 엔신 2012-06-11 21596
214 [WMI,VBS] IIS FTP 서버 IPSecurity 설정 확인 엔신 2012-06-11 21390
213 [WMI,VBS] WMI를 이용한 vbs 작성시 Windows 2008에서 오류 해결 엔신 2012-06-11 18611
212 Windows [VBS] xml parsing file 엔신 2011-01-11 19768
211 Windows WMI 엔신 2011-01-07 14115
210 이란과 아랍간의 사이가 나쁜 이유가 뭘까요? [2] 엔신 2010-11-28 12879
209 ETC 메모 엔신 2010-10-30 9847
208 Linux 패키지 파일 만들기 엔신 2010-10-30 11744
207 Programming [JSP] getParameter() 사용시 파라미터가 있고 없고 엔신 2010-07-08 13622
» DBMS Oracle 요약 엔신 2010-07-08 20501
205 DBMS PL-SQL 엔신 2010-07-08 7546
204 Programming [JSP] JSP 요약 엔신 2010-07-07 33418
203 Programming [JSP] JDBC를 이용한 데이터베이스 활용법 엔신 2010-07-07 10629
202 Programming perl http post 엔신 2010-06-07 10817
201 Ambiguous 인터넷 엔지니어링 태스크 포스 file 엔신 2010-04-27 11483