Oracle SQL Basic (SQL 문법 및 기본함수)

Oracle 자료형

Integer, NUMBER, VARCHAR2, CHAR, DATE 은 Oracle에서 주로 사용하는 자료형이다.

 

JAVA 자료형과 ORACLE자료형 비교

JAVA	ORACLE
int, double	NUMBER
String	VARCHAR2, CHAR
Date	DATE

Oracle에서 NUMBER는 정수와 실수 둘다 사용이 가능하다. 즉, Java에서 int와 double을 합쳐 둔것과 같은역할을한다.

하지만 Oracle에서도 INTEGER가 있으며 정수만 사용가능하고, Java에서의 int와 같은 자료형을 갖는다.

Java에서 String과 같은 자료형을 갖는 Oracle 자료형은 VARCHAR2와 CHAR가 있으며, CHAR는 크기와 단위를 지정하더라도, 영문자(1Byte) 한글문자(3byte)를 섞어서 사용할 시, 지정한 크기를 넘어가기도 하며, 영문자(1byte)만 사용할 경우에도 지정한 크기에서 남은 크기도 같이 사용하므로, 지정한 크기를 넘어가거나 낭비가 되기 때문에 VARCHAR2 자료형을 더 선호한다.

VARCHAR2 자료형은 지정크기를 넘어설 수 없으며, 지정크기보다 작으면 남은 크기는 낭비하지 않는다. 아래 두가지 Structured Query Language 로 확인이 가능하며, 문법과 기본 함수는 그 아래에 정리 되어있다.

CREATE TABLE TB_CHAR(
	COL1 CHAR(10 BYTE),
    COL2 CHAR(10 CHAR),
    COL3 CHAR(10)
);

INSERT INTO TB_CHAR(COL1, COL2, COL3)
VALUES('ABC', 'ABC', 'ABC'); -- 자료등록 가능

INSERT INTO TB_CHAR(COL1, COL2, COL3)
VALUES('가나다', '가나다', '가나다'); -- 자료등록 가능

SELECT COL1, COL2, COL3
	LENGTHB(COL1), LENGTHB(COL2), LENGTHB(COL3)
FROM TB_CHAR; -- 들어있는 자료의 BYTE 확인가능. ['ABC'의 LENGTHB result 10]

CREATE TABLE TB_VARCHAR2(
	COL1 VARCHAR2(10 BYTE),
    COL2 VARCHAR2(10 CHAR),
    COL3 VARCHAR2(10)
);

INSERT INTO TB_VARCHAR2(COL1, COL2, COL3)
VALUES('ABC', 'ABC', 'ABC'); -- 자료등록가능

INSERT INTO TB_VARCHAR2(COL1, COL2, COL3)
VALUES('가나다라', '가나다라', '가나다라'); -- 자료등록 불가능

SELECT COL1, COL2, COL3
	LENGTHB(COL1), LENGTHB(COL2), LENGTHB(COL3)
FROM TB_VARCHAR2; -- 들어있는 자료의 BYTE 확인가능 ['ABC'의 LENGTHB result 3]

SQL의 자료형의 선언

CREATE TABLE 테이블명(

  변수명 자료형 (크기 [단위])

);

SQL의 자료형의 자료 등록

INSERT INTO 테이블명 (변수명 [, 변수명... ])

VALUES(값 [, 값...]);

SQL의 자료형의 자료 조회

SELECT 변수명 [, 변수명 ...]

FROM 테이블명;

-- TB_CHAR자료를 CHAR형으로 선언
CREATE TABLE TB_CHAR(
	COL_CHAR1 CHAR(10 BYTE)
);

-- TB_CHAR자료 등록
INSERT INTO TB_CHAR(COL_CHAR1)
VALUES('ABCDEFGTHI')

-- TB_CHAR자료 조회
SELECT * FROM TB_CHAR;

SQL Standard Function

DUAL : 가상테이블 - 결과확인용 임시 테이블

SELECT 1 FROM DUAL; -- result 1 
SELECT 'A' FROM DUAL; -- result A
SELECT '가' FROM DUAL; -- result 가
SELECT 32*24 FROM DUAL; -- result 768

내점수는 A 입니다. 출력시 JAVA에서 "내 점수는 " + CHR(65) + "입니다" Oracle 에서는?

SELECT '내 점수는 ' || CHR(65) || '입니다' FROM DUAL;
-- 두가지 컬럼을 연결할때 사용함 ||

CHR(N) : ASCII 값을 문자로 변환 하는 함수

SELECT CHR(65) FROM DUAL; -- result A
SELECT CHR(97) FROM DUAL; -- result a

ASCII(CH) : CHR(N)과 반대로 숫자로 변환하는 함수

SELECT ASCII('A') FROM DUAL; -- result 65

LPAD(RPAD) : 나머지를 빈칸(특정문자)으로 채운다

SELECT LPAD('BBB', 10) FROM DUAL; -- result 		BBB
SELECT RPAD('BBB', 10) FROM DUAL; -- result BBB
SELECT LPAD('BBB', 10, '7') FROM DUAL; --- result 7777777BBB

INSTR : JAVA indexOf();와 동일하며 0부터 시작하만 DATABASE에서는 1부터 시작한다

SELECT INSTR('123A456B678C', 'A') FROM DUAL; -- result 4 : 4번째에 A 가 있다.
SELECT INSTR('123A456B678CB', 'B', 8) FROM DUAL; -- result 8 : 8번째 부터 B를 찾아라.
SELECT INSTR('123A456B678CB', 'B', 4, 1) FROM DUAL; -- result 8 : 4번째부터 시작해서 첫번째 B를 찾아라.
SELECT INSTR('123A456B678CB', 'B', 4, 2) FROM DUAL; -- result 13 : 4번째부터 시작해서 두번째 B를 찾아라.

REPLACE : 문자열 치환함수

 SELECT REPLACE('AAAAABCD', 'A') FROM DUAL; -- result BCD : A를 빈공간으로 치환해라.
 SELECT REPLACE('AAAAABCD', 'A', 'a') FROM DUAL; -- result aaaaaBCD : A를 a로 치환해라.
 SELECT REPLACE('AAAAABCD', 'AB', 'a') FROM DUAL; -- result aaABCD : AA를 a로 치환해라.
 SELECT REPLACE('AAAAABCD', 'AB', 'a') FROM DUAL; -- result AAAAaCD : AB를 a로 치환해라.

TRANSLATE : 문자 치환

 SELECT TRANSLATE ( 'AAAAABCD', 'A', 'a') FROM DUAL; -- result aaaaaBCD :  A를 a로 치환.
 SELECT TRANSLATE ( 'AAAAABCD', 'AA', 'a') FROM DUAL; -- result aaaaaBCD : A를 a로 치환.
 SELECT TRANSLATE ( 'AAAAABCD', 'AB', 'a') FROM DUAL; -- result aaaaaCD : A또는 AB를 a로 치환.

CEIL : 숫자 올림

 SELECT CEIL(13.1) FROM DUAL; -- result 14

FLOOR : 숫자 내림

 SELECT FLOOR(13.9) FROM DUAL; -- result 13

ROUND : 숫자 반올림

 SELECT ROUND(13.5) From DUAL; -- result 14
 SELECT ROUND(13.4) From DUAL; -- result 13

TRUNC : 숫자 버림

 SELECT TRUNC(12.3456) FROM DUAL; -- result 12
 SELECT TRUNC(12.3456, 2) FROM DUAL; -- result 12.34
 SELECT TRUNC(12.3456, -1) FROM DUAL; -- reuslt 10

MOD : 나눈 나머지 값

 SELECT MOD(3, 2) FROM DUAL; -- result 1 : 3을 2로 나누고 나머지 값 1

POWER : n승

 SELECT POWER(3, 2) FROM DUAL; -- result 9 : 3의 2제곱 

SIGN : 양수, 음수, 0 반환

 SELECT SIGN(13.4) FROM DUAL; -- result 1
 SELECT SIGN(0) FROM DUAL; -- result 0
 SELECT SIGN(-3.4) FROM DUAL; -- result -1

TO_CHAR : 문자를 숫자로 변환하는 함수

 SELECT TO_CHAR(SYSDATE)
 FROM DUAL;
 
 SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD-HH-MI-SS')
 FROM DUAL;
 
 SELECT TO_CHAR(100000000000, '$999,999,999,999')
 FROM DUAL; -- 응용

TO_DATE : 숫자를 문자로 변환하는 함수

 SELECT TO_DATE('20191225')
 FROM DUAL;
 
 SELECT TO_DATE('20191225', 'YYYYMMDD')
 FROM DUAL; -- 응용
 
 SELECT TO_DATE('12252019', 'MMDDYYYY')
 FROM DUAL; -- 응용

TO_NUMBER : 문자를 숫자로 변환 하는 함수 JAVA에서 Integer.parseInt();와 같음.

 SELECT TO_NUMBER('112') + 24
 FROM DUAL; -- result 136

LAST_DAY : 해당 년월의 마지막날로 반환

SELECT LAST_DAY('19/11/01')
 FROM DUAL; -- result 19/11/30
 
SELECT LAST_DAY(TO_DATE('20191101', 'YYYYMMDD'))
 FROM DUAL; -- result 19/11/30

SUBSTR : 문자 선택

 SELECT SUBSTR('ABCDE', 3)
 FROM DUAL; -- result CDE : 3번째 부터 뒤로 선택해라.
 
 SELECT SUBSTR('ABCDE', 3,2)
 FROM DUAL; -- result CD : 3번째 부터 2개를 선택해라.

LENGTH : 문자의 길이표현 / LENGTHB : 문자의 길이 BYTE 표현

 SELECT LENGTH('ABCDE') FROM DUAL; -- result 5