IT 지식 쌓기

테스트 환경

CREATE TABLE STUDENT (

"SID" NUMBER,

"NAME" VARCHAR2(32),

"EMAIL" VARCHAR2(32)

);

INSERT INTO STUDENT VALUES (582812, '한놈', 'a@a.a');

INSERT INTO STUDENT VALUES (582812, '한놈', 'hannom@example.com');

INSERT INTO STUDENT VALUES (582812, '한놈', 'b@b.b');

INSERT INTO STUDENT VALUES (241234, '두식이', 'dusigi@example.com');

INSERT INTO STUDENT VALUES (241234, '두식이', '');

INSERT INTO STUDENT VALUES (372342, '석삼', 'seoksam@example.com');

문제 상황

기본키가 없는 테이블 STUDENT 에서 SID 를 기본키로 하고 싶다.

ALTER TABLE STUDENT ADD CONSTRAINT SID_PK PRIMARY KEY (SID);

SQL 오류: ORA-02437: (SCOTT.SID_PK)을 검증할 수 없습니다 - 잘못된 기본 키입니다.

중복값 확인

SELECT * 

FROM (SELECT SID, COUNT(SID) CNT FROM STUDENT GROUP BY SID)

A WHERE CNT >1;

  SID       CNT

582812      3

241234      2

* SID 에 중복 값이 있다.

중복값 제거

방법 1) 최근 것을 남김

SID 별 입력 역순 조회

SELECT ROWID, 

RANK() OVER(PARTITION BY SID ORDER BY ROWID DESC) RNK,

STUDENT.* 

FROM STUDENT;

      ROWID                RNK      SID       NAME           EMAIL

AAASSdAAEAAAAKtAAE 1 241234 두식이 NULL

AAASSdAAEAAAAKtAAD 2 241234 두식이 dusigi@example.com

AAASSdAAEAAAAKtAAF 1 372342 석삼         seoksam@example.com

AAASSdAAEAAAAKtAAC 1 582812 한놈        b@b.b

AAASSdAAEAAAAKtAAB 2 582812 한놈        hannom@example.com

AAASSdAAEAAAAKtAAA 3 582812 한놈        a@a.a

RNK 가 2이상인 것듯을 삭제할 것이다.

삭제 대상 확인

SELECT * FROM STUDENT

WHERE ROWID IN 

(SELECT ROWID FROM(SELECT ROWID, 

  RANK() OVER (PARTITION BY SID ORDER BY ROWID DESC)RNK, 

  STUDENT.* FROM STUDENT)

  WHERE RNK > 1);

   SID      NAME            EMAIL

241234 두식이 dusigi@example.com

582812 한놈      hannom@example.com

582812 한놈       a@a.a

삭제 후 결과 확인

DELETE FROM STUDENT

WHERE ROWID IN 

(SELECT ROWID FROM(SELECT ROWID, 

  RANK() OVER(PARTITION BY SID ORDER BY ROWID DESC) RNK,

  STUDENT.* FROM STUDENT)

  WHERE RNK > 1);

* 3개행이 삭제 되었습니다.

SELECT * FROM STUDENT;

  SID       NAME         EMAIL

582812 한놈       b@b.b

241234 두식이      NULL

372342 석삼       seoksam@example.com

방법2) 잘  입력된 것을 남김

EMAIL 이 잘 입력된 순으로 조회

SELECT ROWID,

RANK() OVER(PARTITION BY SID ORDER BY LENGTH(EMAIL) DESC NULLS LAST,

ROWID DESC) RNK, STUDENT.* FROM STUDENT;

      ROWID                RNK      SID       NAME           EMAIL

AAASSfAAEAAAAK1AAD 1 241234 두식이 dusigi@example.com

AAASSfAAEAAAAK1AAE 2 241234 두식이 NULL

AAASSfAAEAAAAK1AAF 1 372342 석삼    seoksam@example.com

AAASSfAAEAAAAK1AAB 1 582812 한놈       hannom@example.com

AAASSfAAEAAAAK1AAC 2 582812 한놈       b@b.b

AAASSfAAEAAAAK1AAA 3 582812 한놈       a@a.a

RNK 가 2이상인 것들을 삭제할 것이다.

삭제 대상 확인

SELECT * FROM STUDENT

WHERE ROWID IN (SELECT ROWID FROM(SELECT ROWID,

  RANK() OVER(PARTITION BY SID ORDER BY LENGTH(EMAIL) DESC NULLS LAST,

  ROWID DESC) RNK, STUDENT.* FROM STUDENT)

  WHERE RNK > 1);

  SID       NAME       EMAIL

582812 한놈        a@a.a

241234 두식이      NULL

582812 한놈        b@b.b

삭제 후 결과 확인

DELETE FROM STUDENT

WHERE ROWID IN (SELECT ROWID FROM(SELECT ROWID,

  RANK() OVER(PARTITION BY SID ORDER BY LENGTH(EMAIL) DESC NULLS LAST,

  ROWID DESC) RNK, STUDENT.* FROM STUDENT)

  WHERE RNK >1 );

*3개 행이 삭제 되었습니다.

SELECT * FROM STUDENT;

  SID       NAME         EMAIL

582812 한놈    hannom@example.com

241234 두식이 dusigi@example.com

372342 석삼       seoksam@example.com

PK 제약조건 생성 테스트

이제 중복값이 없어 아래 SQL 문을 오류없이 실행할수 있다.

ALTER TABLE STUDENT ADD CONSTRAINT SID_PK PRIMARY KEY (SID);

* table STUDENT이(가) 변경되었습니다.

출처 : http://zetawiki.com/

DDL (Data Definition Language) : 데이터와 그 구조 정의 = 테이터 정의어

TABLE 정의

행과 열로 구성된 기본적인 저장매체의 단위

TABLE CREATE 주의사항

1. 테이블 이름을 지정하고 각 컬럼들은 괄호 ( ) 로 묶어 지정합니다.

2. 컬럼뒤에 데이터 타입은 꼭 지정되어야 합니다.

3. 각 컬럼들은 콤마  , 로 구분되고, 항상 끝은 세미콜론 ; 으로 끝납니다.

4. 한 테이블 안에서 컬럼이름은 같을 수  없을며 다른 테이블에서의 컬럼 이름과는 같을수 있습니다.

5. 테이블명을 쌍따옴표 " " 로 감싸지 않으면 기본적으로 대문자로 생성되며 

감싸면 입력한 그대로 생성이 된다.

TABLE CREATE 특이사항

1. 오라클에서는 테이블 생성시 컬럼을 1000개까지 생성이 가능하다.

2. 오라클에서는 테이블 생성 후 커밋 커리된다.

TABLE CREATE 명령

CHECK 제약 조건 : 컬럼에 입력되는 값을 체크함

컬럼 CHAR(1) CHECK (컬럼 = '0' OR 컬럼 = '1') ;

-- 데이터 삽입시 값을 체크해서 거짓이면 에러 발생

예제1)

CREATE TABLE TEST1

(AA CHAR(1) CHECK (AA = '0' OR AA = '1'));

-- 테이블 생성후 AA 컬럼에 문자열 2를 입력하면 에러 발생

예제2)

A CHAR(13) CHECK (컬럼 LIKE '____-__-__')

-- A 컬럼에 'YYYY-MM-DD' 형식으로만 입력가능

DEFAULT 제약조건 : 컬럼에 값이 입력 안될떄 자동으로 입력되는 값

예제1)

CREATE TABLE TEST3

(

A DATE DEFAULT SYSDATE,

B CHAR(10)

);

COMMIT;

INSERT INTO TEST3 (B) VALUES ('A') --날짜, A 입력됨

INSERT INTO TEST3 (A, B) VALUES (NULL, 'A') -- NULL 값, A 입력됨

INSERT INTO TEST3 (A, B) VALUES ('', 'A') -- NULL값, A 입력됨

TABLE CREATE EXAMPLE

예제1) PRIMARY KEY 생성 하지 않을시

CREATE TABLE A(

ID NUMBER(10),

NAME VARCHAR2(30)

);

예제2) PRIMARY KEY 생성 

CREATE TABLE B(

ID NUMBER(10) CONSTRAINT B_ID PRIMARY KEY,

NAME VARCHAR(30)

);

-- CONSTRAINT 이용해서 PRIMARY KEY 이름을 정할수 있다.

이름을 임으로 정하고 싶다면 적지 않아도 된다.

예제3) 제약 조건을 이용하지 않고 PRIMARY KEY 생성

CREATE TABLE C(

ID NUMBER(10),

NAME VARCHAR(30),

CONSTRAINT C_ID PRIMARY KEY (ID)

);

TABLE COPY

같은 계정에서의 TABLE COPY

TABLE A 가 있고 TABLE  B 를 생성하면서 복사하는 방법, 

하지만 제약조건(PRIMARY KEY, UNIQUE) 등은 별도로 해줘야 한다.

CREATE TABLE B AS SELECT * FROM A;

* B TABLE 을 만든 후 A TABLE 의 모든 데이터를 B TABLE 에 입력한다.

같은 서버에서 다른 계정으로 TABLE COPY (SYSTEM 계정에서 실행해야 한다.)

제약조건(PRIMARY KEY, UNIQUE) 등은 별도로 해줘야 한다.

CREATE TABLE SCOTT.B AS SELECT * FROM SC.A;

My SQL과 다르게 오라클에서는 Auto_Increment 명령어가 없다

그러므로 이 기능을 사용하기 위해서는 시퀀스를 생성해야한다.

Auto_Increment(인서트 자동증가) 시퀀스

CREATE SEQUENCE AAA START WITH 1 INCREMENT BY 1 MAXVALUE 100 CYCLE NOCACHE;

 밑줄 그어진 부분은 시퀀스 명이니 자유롭게 사용해도 된다.

사용방법

CREATE TABLE A (

id number(10),

name VARCHAR2(200)

);

라는 테이블을 만들었고 

이 테이블의 id라는 컬럼이 인서트가 될떄마다 자동 증가 되게 하고 싶다면

INSERT INTO board(id, name) values(AAA.NEXTVAL, 'aaa');

INSERT INTO board(id, name) values(AAA.NEXTVAL, 'bbb');

INSERT 문을 이렇게 구성하면 된다.

* 주의사항 :  시퀀스를 사용할 테이블은 시퀀스 보다 먼저 생성되어 있어야한다.

(시퀀스 생성 후의 시퀀스 사용 테이블을 생성하면 INSERT 시 카운트가 2부터 잡히는 경우가 발생)

초기화 방법

1) 시퀀스의 현재값

SELECT LAST_NUMBER FROM USER_SEQUENCES WHERE SEQUENCE_NAME = 'AAA';

결과값 : 7

2) 시퀀스의 INCREMENT 를 현재 값만큼 빼도록 설정 (시퀀스의 맥스 값 을 만들어 줘야한다.)

(최대값이 100일 경우 현재 값이 7이 라면 -7을 하여 최대값으로 바꿔준다)

ALTER SEQUENCE AAA INCREMENT BY -7;

3) 시퀀스에서 다음 값을 가져 온다

SELECT AAA.NEXTVAL FROM DUAL;

결과값 : 100

4) 시퀀스 INCREMENT 값을 1로 설정한다.

ALTER SEQUENCE AAA INCREMENT BY 1;