[Oracle] 기초 (MySQL과의 소소한 차이)

MySQL과 Oracle은 비슷한데 다른 부분이 몇군데 있죵. 

 

Oracle에 대해 알아봅시다. MySQL 기초 정리 해놔서 똑같은건 뺄거고 헷갈리거나 다른것만 추가할게요.

 

1. 사용자 계정 생성 및 권한 부여 과정

 

처음에 SYS 또는 SYSTEM과 같은 관리자 계정으로 새로운 사용자 계정을 생성합니다.

CREATE UWER username IDENTIFIED BY passweord;

 

사용자에게 필요한 권한을 부여합니다. 

CONNECT - 사용자가 DB에 접속할 수 있게 합니다. DB 세션을 시작할 수 있습니다.

RESOURCE - DB 내에서 특정 리소스를 생성할 수 있는 권한입니다. 주로 객체 생성과 관련된 작업을 합니다.

                     - 테이블 생성, 인덱스 생성, 트리거 생성, 시퀀스 생성 등

GRANT CONNECT, RESOURCE TO username;
GRNAT SELECT, INSERT, UPDATE, DELETE ON schema_name.table_naem TO username; // 특정 테이블

 

참고로 사용자에게 부여된 권한 확인은 다음과 같습니다.

 

* 참고로 오라클은 DBA_SYS_PRIVS 같이 딕셔너리 뷰를 제공합니다. 

 

딕셔너리 뷰 

• DB 시스템에서 메타데이터를 제공하는 시스템 테이블 및 뷰
• DB 객체에 대한 정보
• DB구성 요소에 대한 다양한 정보 제공
• 일반적으로 DBA_ , ALL_ , USER_ 로 시작

• DBA_USERS - DB에 정의된 모든 사용자에 대한 정보
• DBA_TABLES - DB에 정의된 모든 테이블에 대한 정보
• DBA_TAB_COLUMNS - 모든 테이블의 열에 대한 정보
• DBA_INDEXES - DB에 저장된 모든 인덱스에 대한 정보
• DBA_SYS_PRIVS - 사용자에게 부여된 시스템 권한에 대한 정보
• DBA_ROLE_PRIVS - 사용자에게 부여된 역할에 대한 정보
• DBA_TAB_PRIVS - 사용자에게 부여된 테이블 권한에 대한 정보
• ALL_USERS - 현재 사용자가 접근할 수 있는 모든 사용자에 대한 정보
• ALL_TABLES - 현재 사용자가 접근할 수 있는 모든 테이블에 대한 정보
• ALL_TAB_COLUMNS - 현재 사용자가 접근할 수 있는 모든 테이블 열에 대한 정보
• ALL_INDEXES - 현재 사용자가 접근할 수 있는 모든 인덱스에 대한 정보
• ALL_TAB_PRIVS - 현재 사용자가 접근할 수 있는 모든 테이블 권한에 대한 정보
• USER_USERS - 현재 사용자가 소유한 사용자에 대한 정보 
• USER_TABLES - 현재 사용자가 소유한 테이블에 대한 정보
• USER_TAB_COLUMNS - 현재 사용자가 소유한 테이블 열에 대한 정보
• USER_INDEXES - 현재 사용자가 소유한 인덱스에 대한 정보
• USER_TAB_PRIVS - 현재 사용자가 소유한 테이블 권한에 대한 정보

SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE = 'username';

 

* 권한 철회

REVOKE RESOURCE FROM username;

 

2. 테이블 생성 관련

 

음 MySQL에서는 자동 증가값을 AUTO_INCREMENT기능이 있었죠? 하지만 Oracle에는 없고 대신 SEQUENCE를 사용하게 됩니다. 

 

아래와 같은 테이블을 만들었다 가정하고, user_id를 자동 증가값으로 설정하고 싶다고 합시다.

CREATE TABLE table(
   user_id NUMBER(5) PRIMARY KEY,
   name VARCHAR2(50),
   salary NUMBER
);

 

먼저 user_seq라는 이름을 가진 시퀀스를 만들어 줍니다. 

CREATE SEQUENCE user_seq
START WITH 1
INCREMENT BY 1
NOCACHE // 캐싱 비활성화
NOCYCLE; // 최대값 도달 시 종료

 

음 시퀀스 더 많은 옵션이 있는데 따로 정리하던가 나중에 추가하던가 해볼게용.

 

이 시퀀스를 적용하는 방법은 3가지가 있어요. 먼저 INSERT 문으로 하는 방법입니다. 

INSERT INTO table (User_id, user_name) 
VALUES (user_seq.NEXTVAL, 'name');

 

이 방법은 추가할 때마다 시퀀스를 명시적으로 삽입해줘야 합니다.. 자동으로 할당되게는 못하나..? 트리거를 이용하면 됩니다.

CREATE OR REPLACE TRIGGER table_bir
BEFORE INSERT ON table 
FOR EACH ROW // 각 행에 대해 실행
BEGIN
   SELECT user_seq.NEXRVAL // 시퀀스에서 다음 값을 가져옴
   INTO :new.user_id // 새 행의 user_id 컬럼에
   FROM dual
END;

 

이렇게 하면 table에 삽입하기 전에 자동으로 실행되어 자동 증가값을 넣을 수 있습니다. 

* DUAL 테이블은 실제로는 아무 데이터도 저장하지 않지만 데이터를 반환할 수 있는 Oracle DB에서 제공하는 특별한 한 행, 한 열의 테이블 입니다. 주로 간단한 SELECT 문의 테스트나 계산을 위해 사용됩니다.

마지막으로는 Oracle 12c 이후 버전에서 가능한 IDENTITY 컬럼을 사용하는 방법입니다. 기본적으로 1부터 시작해서 1씩 증가합니다. 이 방법을 사용하면 시퀀스와 트리거 모두 필요 없습니다!

CREATE TABLE table(
   user_id NUMBER(5) GENERATED BY DEFALUT AS IDENTITY PRIMARY KEY,
   name VARCHAR2(50),
   salary NUMBER
);

 

* GENERATED BY DEFAULT AS IDENTITY - 명시적으로 값을 삽입하지 않았을 경우에만 자동 증가값이 적용됩니다. 

  GENERATED ALWAYS AS IDENTITY - 명시적으로 값을 삽입하려 할 때 오류가 발생합니다. 

  BY DEFAULT AS IDENTITY (START WITH 100 INCREMENT BY 10)으로 시작 값과 증가 값을 설정할 수 있습니다.

 

3. 데이터 타입

 

MySQL에는 INT, VARCHAR를 쓰지만 Oracle은 NUMBER, VARCHAR(2)를 씁니다. 

 

NUMBER - 정수, 실수 모두 저장하며 NUMBER(5, 2)는 5자리 숫자 중에서 소수점 아래 2자리를 포함하는 숫자를 저장합니다.

INT - 정수만 저장합니다.

 

* VARCHAR와 VARCHAR2는 동일하지만 Oracle에서는 미래 SQL 표준이 변경될 경우 VARCHAR의 동작이 변경될 수 있으므로 VARCHAR2를 권장한다고 합니다.

 

* CHAR - 고정 길이로 남는 공간은 공백으로 채운다.

* VARCHAR2 - 가변 길이로 남는 공간을 사용하지 않는다.

 

4. 문법

• DDL

오라클에서 ALTER TABLE 사용 시 여러 열을 추가할 때에는 괄호를 써야 합니다. (MySQL은 생략 가능)

ALTER TABLE employees ADD (address VARCHAR2(100), phone VARCHAR2(20));

 

• 날짜 

// MySQL
SELECT CURRENT_DATE; // YYYY-MM-DD
SELECT CURRENT_TIME; // HH:MI:SS
SELECT CURRENT_TIMESTEMP; = SELECT NOW(); // YYYY-MM-DD HH:MI:SS

SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s'); // 형식 지정 출력

SELECT DATE_ADD('2023-01-01', INTERVAL 1 DAY); // 날짜 더하기
SELECT DATE_SUB('2023-01-01', INTERVAL 1 DAY); // 날짜 빼기
SELECT DATEDIFF('2023-01-01', '2022-12-01'); // 날짜 차이 계산

// Oracle
SELECT SYSDATE FROM dual; // 'YYYY-MM-DD HH24:MI:SS'
SELECT CURRENT_DATE FROM dual; // YYYY-MM-DD
SELECT CURRENT_TIMESTAMP FROM dual; // 'YYYY-MM-DD HH24:MI:SS.FF'

SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') FROM dual; // 형식 지정 출력

SELECT SYSDATE + 1 FROM dual; // 날짜 더하기
SELECT SYSDATE - 1 FROM dual; // 날짜 빼기
SELECT SYSDATE - TO_DATE('2023-01-01', 'YYYY-MM-DD') FROM dual; // 날짜 차이 계산

 

MySQL은 요일을 0~6으로, Oracle은 1~7으로 사용합니다.

 

• NULL 확인

// MySQL
SELECT IFNULL(name, '엾음') FROME user;

// Oracle
SELECT NVL(name, '없음') FROM user;

SELECT NVL2(name, '있음', '없음') FORM user;

 

IFNULL과 NVL은 대상이 NULL이면 치환하는 같은 기능입니다. 

 

오라클에는 NVL2로 대상의 값이 있을 때와 없을 때 모두 치환할 수 있습니다.

 

• 형 변환

// MySQL
SELECT CAST(1234.56 AS CHAR); // 숫자 -> 문자열
SELECT CONVERT(1234.56, CHAR);
SELECT CAST('1234.56' AS DECIMAL(10,2)); // 문자열 -> 숫자
SELECT CONVERT('1234.56', DECIMAL(10,2));
SELECT CAST('2023-06-07' AS DATE); // 문자열 -> DATE
SELECT CONVERT('2023-06-07', DATE);

// Oracle
SELECT TO_CHAR(1234.56, '9999.99') FROM dual; // 숫자 -> 문자열
SELECT TO_NUMBER('1234.56', '9999.99') FROM dual; // 문자열 -> 숫자
SELECT TO_DATE('2023-06-07', 'YYYY-MM-DD') FROM dual; // 문자열 -> DATE

 

• 문자 합치기

// MySQL
SELECT CONCAT('Hello', ' ', 'World'); // Hello World
SELECT CONCAT_WS(' ', 'Hello', 'World');

// Oracle
SELECT 'Hello' || ' ' || 'World' FROM dual; // Hello World
SELECT CONCAT('Hello', ' ', 'World') FROM dual;
SELECT CONCAT(CONCAT('Hello', ' '), 'World') FROM dual;

 

* 참고로 MySQL에서는 CONCAT으로 여러개의 문자열을 합칠 수 있지만 Oracle에서는 두 개의 문자열만 가능합니다. 그래서 여러개의 문자열을 합칠 떄는 || 를 사용하는게 편리하며 CONCAT은 중첩해서 써야합니다.

 

• 페이징 처리

// MySQL
SELECT * FROM user LIMIT 0, 10; // 0번 인덱스부터 10개의 행

// Oracle 12c 이전
SELECT * FROM (
   SELECT u.*, ROWNUM rnum
   FROM (
      SELECT * FROM user
      ORDER BY user_id
   ) u
   WHERE ROWNUM <= 10
)
WHERE rnum > 0; // 0번 인덱스부터 10개의 행

// Oracle 12c 이후
SELECT * FROM user
ORDER BY user_id
OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY; // 0번 인덱스부터 10개의 행

 

---

 

참고 출처

[SQL] Oracle과 MySQL의 문법 차이 정리 (tistory.com)

[SQL] Oracle과 MySQL의 문법 차이 정리