[Database] Oracle SQL문 정리

*[ 목차 ]

• Sequence

• Table & Column

• INSERT

• SELECT

• UPDATE

• DELETE

• JOIN

 

*[ Sequence ]

- Sequence 생성

CREATE SEQUENCE (시퀀스 이름)  
    [INCREMENT BY n1]  
    [START WITH n2]  
    [MAXVALUE n3 or NOMAXVALUE]  
    [MINVALUE n4 or NOMINVALUE]  
    [CYCLE or NOCYCLE]  
    [CACHE or NOCACHE];

-> increment by n : 증가값 (default 1)
-> start with n : 시작값 (default 1)
-> maxvalue n | nomaxvalue : 최댓값 (nomaxvalue default 10*E27)
-> minvalue n | nominvalue : 최솟값 (nominvalue default 1)
-> cycle | nocycle : 최솟값, 최대값 이후의 값 생성 유무 (default nocycle)
-> cache | nocache :메모리에 유지할 값의 수 (default 20)

ex) CREATE SEQUENCE user_seq_increment INCREMENT BY 1 START WITH 0 MINVALUE 0;

-> Sequence 이름은 user_seq_increment, 1씩 증가, 0부터 시작, 최솟값 0
-> Sequence를 생성하면 기본키와 같이 순차적으로 증가하는 컬럼을 자동적으로 생성

 

- Sequence 조회

SELECT  * FROM user_sequences;

 

- Sequence 수정

ALTER SEQUENCE (시퀀스 이름)
    [INCREMENT BY n1]
    [START WITH n2]
    [MAXVALUE n3 or NOMAXVALUE]
    [MINVALUE n4 or NOMINVALUE]
    [CYCLE or NOCYCLE]
    [CACHE or NOCACHE];

ex) ALTER SEQUENCE user_seq INCREMENT BY 2;

 

- Sequence 삭제

DROP SEQUENCE (시퀀스 이름);

ex) DROP SEQUENCE user_seq;

 

*[ Table & Column ]

- Table 생성(PK, FK, UNIQUE 포함)

CREATE TABLE (테이블 이름)  
( column1 datatype [ NULL | NOT NULL ],    
  column2 datatype [ NULL | NOT NULL ],    
  ...    
  column_n datatype [ NULL | NOT NULL ]  
);

(*출처: https://www.techonthenet.com/oracle/tables/create_table.php)

ex) CREATE TABLE tb_user  
    ( user_seq number NOT NULL ENABLE,  
      user_id varchar2(30 byte) NOT NULL ENABLE,  
      user_name varchar2(20 byte) NOT NULL ENABLE,  
      age number,  
      department_id number,  
      CONSTRAINT user_pk PRIMARY KEY (user_seq),  
      CONSTRAINT fk_departments  
      FOREIGN KEY (department_id)  
      REFERENCES departments(department_id) ON DELETE CASCADE ENABLE,  
      CONSTRAINT unique_user_id UNIQUE (user_id)
    );

-> 만약, 기본키를 복합키 형태로 만들려면 PRIMARY KEY (user_seq, user_name)와 같이 덧붙여 작성

-> Oracle에서는 ON UPDATE CASCADE를 따로 지원하지 않아서, 이를 trigger로 따로 구현해야 한다

-> UNIQUE 속성은 말 그대로 그 테이블에서 유일한 값이어야하는 제약 조건

 

- Table 조회

SELECT * FROM tab;

-> 현재 계정의 테이블 목록을 보여준다

SELECT * FROM user_tables;

-> 모든 계정의 테이블 목록을 보여준다

DESC (테이블명);

-> 해당 테이블의 스키마를 보여준다

 

- Table 수정

ㄴTable명 변경

RENAME (변경전 테이블명) TO (변경후 테이블명);

ex) RENAME TABLE tb_user TO tb_user2;

ㄴ Table Column 추가

ALTER TABLE (테이블명) ADD (컬럼명 데이타 타입(사이즈));

ex) ALTER TABLE tb_user ADD (create_dt TIMESTAMP);

ㄴTable Column 수정

ALTER TABLE (테이블명) MODIFY (컬럼명 테이타 타입(사이즈));

ex) ALTER TABLE tb_user MODIFY (user_id varchar2(10));

ㄴTable Column명 수정

ALTER TABLE (테이블명) RENAME COLUMN (이전 컬럼명) TO (새 컬럼명);

ex) ALTER TABLE tb_user RENAME COLUMN user_name TO user_nickname;

ㄴTable Column 삭제

ALTER TABLE (테이블명) DROP COLUMN (컬럼명);

ex) ALTER TABLE tb_user DROP COLUMN create_dt;

 

- Table 삭제

DROP TABLE (테이블 이름);

ex) DROP TABLE tb_user;

 

*[ INSERT ]

ex) INSERT INTO tb_user(user_seq, user_id, user_name) VALUES (user_seq_increment.nextval, '아이디', '이름');

-> Oracle에서는 시퀀스를 적용할 때 (시퀀스).nextval 값을 직접 Insert 해줘야 한다

 

*[ SELECT ]

ex) SELECT * FROM tb_user WHERE user_name='이름';

 

*[ UPDATE ]

ex) UPDATE tb_user SET user_name='이름2' WHERE user_id='아이디';

 

*[ DELETE ]

ex) DELETE FROM tb_user WHERE user_name='이름';

 

*[ JOIN ]

ex) SELECT u.id, s.class FROM tb_user u, tb_student s WHERE u.user_name = s.name;

 

다음은 업무를 하다가 테스트하는 부분이 필요해서 만든 '모든 속성을 가지는 Table'입니다.
Oracle에는 어떤 속성이 있고, 어떤 형태로 INSERT 되는지 확인해보면 좋을 것 같습니다.

- 모든 속성을 가지는 Table 생성

ex) CREATE TABLE TB_ALL_TYPES_OF_ORACLEDB  
    ( COL_VARCHAR2 VARCHAR2(20),
      COL_INT INT NOT NULL,
      COL_INTEGER INTEGER,
      COL_INTERVAL_DAY INTERVAL DAY TO SECOND, 
      COL_INTERVAL_YEAR INTERVAL YEAR TO MONTH, 
      COL_LONG LONG,
      COL_NATIONAL_CHAR NATIONAL CHAR, 
      COL_NATIONAL_CHAR_VARYING NATIONAL CHAR VARYING(20), 
      COL_NATIONAL_CHARACTER NATIONAL CHARACTER, 
      COL_NUMBER NUMBER, 
      COL_DATE DATE, 
      COL_CLOB CLOB, 
      COL_BLOB BLOB, 
      COL_BFILE BFILE,  
      COL_BINARY_DOUBLE BINARY_DOUBLE, 
      COL_BINARY_FLOAT BINARY_FLOAT,  
      COL_CHAR CHAR,  
      COL_CHAR_VARYING CHAR VARYING(20),  
      COL_CHARACTER CHARACTER,  
      COL_CHARACTER_VARYING CHARACTER VARYING(20),  
      COL_DEC DEC,  
      COL_DECIMAL DECIMAL,  
      COL_DOUBLE_PRECISION DOUBLE PRECISION,  
      COL_FLOAT FLOAT,  
      COL_NATIONAL_CHARACTER_VARYING NATIONAL CHARACTER VARYING(20), 
      COL_NCHAR NCHAR,  
      COL_NCHAR_VARYING NCHAR VARYING(20),  
      COL_NCLOB NCLOB,
      COL_NUMERIC NUMERIC,  
      COL_NVARCHAR2 NVARCHAR2(20),  
      COL_RAW RAW(20), 
      COL_REAL REAL,
      COL_ROWID ROWID,
      COL_SMALLINT SMALLINT,
      COL_TIMESTAMP TIMESTAMP,
      COL_UROWID UROWID, 
      COL_VARCHAR VARCHAR(20), 
      CONSTRAINT ALL_TYPES_OF_ORACLEDB_PK PRIMARY KEY(COL_INT)  
    );

 

- 모든 속성 한 개씩 INSERT 예시

INSERT INTO tb_all_types_of_oracledb(col_int, col_varchar2) VALUES (all_types_tb_increment.nextval, 'varchar2');

INSERT INTO tb_all_types_of_oracledb(col_int) VALUES (all_types_tb_increment.nextval);

INSERT INTO tb_all_types_of_oracledb(col_int, col_integer) VALUES (all_types_tb_increment.nextval, 4562456);

INSERT INTO tb_all_types_of_oracledb(col_int, col_interval_day) VALUES (all_types_tb_increment.nextval, '90 00:00:00');

INSERT INTO tb_all_types_of_oracledb(col_int, col_interval_year) VALUES (all_types_tb_increment.nextval, '2-6');

INSERT INTO tb_all_types_of_oracledb(col_int, col_long) VALUES (all_types_tb_increment.nextval, 12.32456);

INSERT INTO tb_all_types_of_oracledb(col_int, col_national_char) VALUES (all_types_tb_increment.nextval, 'n');

INSERT INTO tb_all_types_of_oracledb(col_int, col_national_char_varying) VALUES (all_types_tb_increment.nextval, 'ncharacter');

INSERT INTO tb_all_types_of_oracledb(col_int, col_national_character) VALUES (all_types_tb_increment.nextval, 'c');

INSERT INTO tb_all_types_of_oracledb(col_int, col_number) VALUES (all_types_tb_increment.nextval, 123456456);

INSERT INTO tb_all_types_of_oracledb(col_int, col_date) VALUES (all_types_tb_increment.nextval, '2020-01-14');

INSERT INTO tb_all_types_of_oracledb(col_int, col_clob) VALUES (all_types_tb_increment.nextval, 12345);

INSERT INTO tb_all_types_of_oracledb(col_int, col_blob) VALUES (all_types_tb_increment.nextval, '12345');

INSERT INTO tb_all_types_of_oracledb(col_int, col_bfile) VALUES (all_types_tb_increment.nextval, BFILENAME('STUFF', 'WD.pdf'));

INSERT INTO tb_all_types_of_oracledb(col_int, col_binary_double) VALUES (all_types_tb_increment.nextval, '123.84893');

INSERT INTO tb_all_types_of_oracledb(col_int, col_char) VALUES (all_types_tb_increment.nextval, 'c');

INSERT INTO tb_all_types_of_oracledb(col_int, col_char_varying) VALUES (all_types_tb_increment.nextval, 'casdasd');

INSERT INTO tb_all_types_of_oracledb(col_int, col_character) VALUES (all_types_tb_increment.nextval, 'c');

INSERT INTO tb_all_types_of_oracledb(col_int, col_character_varying) VALUES (all_types_tb_increment.nextval, 'casdasd');

INSERT INTO tb_all_types_of_oracledb(col_int, col_dec) VALUES (all_types_tb_increment.nextval, 12345);

INSERT INTO tb_all_types_of_oracledb(col_int, col_decimal) VALUES (all_types_tb_increment.nextval, 12345);

INSERT INTO tb_all_types_of_oracledb(col_int, col_double_precision) VALUES (all_types_tb_increment.nextval, 12345.1231);

INSERT INTO tb_all_types_of_oracledb(col_int, col_float) VALUES (all_types_tb_increment.nextval, 12345.1231);

INSERT INTO tb_all_types_of_oracledb(col_int, col_national_character_varying) VALUES (all_types_tb_increment.nextval, 12345.1231);

INSERT INTO tb_all_types_of_oracledb(col_int, col_nchar) VALUES (all_types_tb_increment.nextval, 'c');

INSERT INTO tb_all_types_of_oracledb(col_int, col_nchar_varying) VALUES (all_types_tb_increment.nextval, 'abc');

INSERT INTO tb_all_types_of_oracledb(col_int, col_nclob) VALUES (all_types_tb_increment.nextval, 'abc');

INSERT INTO tb_all_types_of_oracledb(col_int, col_numeric) VALUES (all_types_tb_increment.nextval, 12345);

INSERT INTO tb_all_types_of_oracledb(col_int, col_nvarchar2) VALUES (all_types_tb_increment.nextval, 'abc');

INSERT INTO tb_all_types_of_oracledb(col_int, col_raw) VALUES (all_types_tb_increment.nextval, 'abc');

INSERT INTO tb_all_types_of_oracledb(col_int, col_real) VALUES (all_types_tb_increment.nextval, 123.4564);

INSERT INTO tb_all_types_of_oracledb(col_int, col_rowid) VALUES (all_types_tb_increment.nextval, 'OOOOOOFFFBBBBBBRRR');

INSERT INTO tb_all_types_of_oracledb(col_int, col_smallint) VALUES (all_types_tb_increment.nextval, 10);

INSERT INTO tb_all_types_of_oracledb(col_int, col_timestamp) VALUES (all_types_tb_increment.nextval, '2020-01-14 15:10:00');

INSERT INTO tb_all_types_of_oracledb(col_int, col_UROWID) VALUES (all_types_tb_increment.nextval, 'OOOOOOFFFBBBBBBRRR');

INSERT INTO tb_all_types_of_oracledb(col_int, col_varchar) VALUES (all_types_tb_increment.nextval, 'varchar');

INSERT INTO tb_all_types_of_oracledb(col_int, col_binary_float) VALUES (all_types_tb_increment.nextval, '123.84893');

 

- Oracle Database IDE(SQL Developer)

https://www.oracle.com/tools/downloads/sqldev-v192-downloads.html

Oracle SQL Developer Downloads

다음 포스팅에서는 MSSQL에서 사용한 SQL문을 정리하도록 하겠습니다!