[📚 sql 문법을 공부하자 📚] 오라클의 자료형과 함수

오라클의 자료형과 함수

1. 오라클의 자료형

• VARCHAR2(바이트단위의 길이) : 4000byte만큼의 가변 길이 문자열 데이터를 저장할 수 있다.
• CHAR(길이) : 1byte 문자로 사용을 주로 하며 플래그로 주로 사용된다.(탈퇴여부(Y/N), 승인여부(Y/N),...)
• NUMBER(전체자리수)- 정수, NUMBER(전체자리수, 소수점뒤자리수)-소수 : 최대 38자리까지 지정가능한 숫자타입, 전체자리수로 숫자의 전체자리수가 정해지고 소수점 뒤 자리수에 따라 소수점뒤의 숫자의 개수가 정해진다.
• DATE : 날짜 형식을 저장하기 위해 사용하는 자료형, 연,월,일,시,분,초 저장이 가능하다. SYSDATE(현재의 날짜와 시간정보를 제공해주는 상수)
• BLOB : 최대 4GB의 대용량 이진 데이터를 저장할 수 있다.
• CLOB : 최대 4GB의 대용량 텍스트를 저장할 수 있다.
• - BLOB, CLOB은 예전에 파일업로드 시 파일자체를 이진데이터나 텍스트로 변환하여 저장을 했었는데 DB 서버가 해킹당하거나 불의의사고로 데이터 손실이 발생하면 파일들도 다 삭제되기 때문에 요즘은 파일은 파일서버에 따로 관리하고 DB에는 그 파일의 경로, 파일명을 저장해서 함께 불러오는 방식을 사용한다.
• BFILE : 최대 4GB의 대용량 이진 데이터 파일(.bin)을 저장할 수 있다.

2. 오라클의 객체

• 테이블(table) : 데이터가 저장되는 장소.
• 인덱스(index) : 테이블의 검색속도를 높이기 위해서 사용하는 객체. 인덱스로 지정된 컬럼 기준으로 검색이 되기 때문에 속도가 높아진다.
• 뷰(view) : 하나나 여러개의 테이블의 데이터들을 논리적으로 연결해서 하나의 테이블처럼 만들어주는 객체. 테이블의 데이터를 참조해서 논리적인 가상테이블을 DB에 생성해준다.
• 시퀀스(sequence) : 일련번호를 만들어주는 객체. 주로 일련번호를 만들어서 PK(Primary Key)로 사용된다.
• 시노님(synonym) : 오라클 객체 별칭을 붙여주는 객체.
• 프로시저(procedure) : 프로그래밍 연산 및 기능 수행이 가능한 객체(반환 값 없음)
• 함수(function) : 프로그래밍 연산 및 기능 수행이 가능한 객체(반환 값 있음)
• 패키지(package) : 관련있는 프로시저와 함수의 집합
• 트리거(trigger) : 데이터 관련 작업의 연결 및 방지 관련 기능을 제공하는 객체

3. PL/SQL

1.DBMS에서는 데이터를 관리하기 위한 별도의 프로그래밍 언어를 제공하는데 그것을 PL/SQL(Procdural Lanaguage extension to SQL)이라고 한다.

2.PL/SQL을 사용하면 DBMS에서도 변수, 조건문, 반복문 등 프로그램밍 언어에서 제공하는 문법들을 사용하여 데이터 관리를 할 수 있게 된다. 

(PL/SQL에 대해서는 뒤에 자세하게 포스팅 하겠습니다.)

4. 함수

1.오라클에서는 SQL 쿼리문을 효율적으로 작성할 수 있도록 도움을 주는 함수들을 제공하고 있다.

 

2.오라클의 함수는 단일행함수, 그룹함수로 분류 할 수 있다.

 

3.단일행 함수는 데이터가 한 행 입력될 때 한 행당 결과가 하나씩 나오는 함수. TO_DATE, LOWER, CONCAT, SUBSTR....

 

4.그룹함수는 특정컬럼을 그룹화하여 계산을 할 수 있게 제공되는 함수. SUM, COUNT, AVG....

5. 단일행 함수

1.문자 함수, 숫자 함수, 날짜 함수, 변환 함수, 일반 함수 등이 존재한다.

 

2.단일행 함수는 SELECT절, WHERE절, HAVING절 어디서든 사용가능

 

3.단일행 함수는 입력 값 하나당 하나의 결과를 리턴

  

문자 함수

• LOWER(컬럼명이나 문자열) : 입력받은 데이터를 소문자로 변환 후 반환
• UPPER(컬럼명이나 문자열) : 입력받은 데이터를 대문자로 변환 후 반환
• INITCAP(컬럼명이나 문자열) : 입력받은 데이터의 첫 글자는 대문자로 나머지 글자는 소문자로 변환 후 반환

문자 연산 함수

• -CONCAT(문자열1, 문자열2) : 문자열 결합 함수. 오라클에는 || 문자열 결합 연산자때문에 활용도가 떨어진다. 두 개의 문자열만 매개변수로 입력 가능하다.
• -SUBSTR(문자열, 시작위치, 개수) : 문자열을 시작위치부터 개수만큼 자른 새로운 문자열을 반환
• -LENGTH(문자열) : 문자열의 길이를 반환
• -LENGTHB(문자열) : 문자열의 길이를 byte단위로 반환
• -INSTR(문자열, 지정문자) : 문자열에서 지정한 문자의 위치를 반환
• -TRIM(leading FROM 문자열) : 접두어를 잘라서 반환

• TRIM(trailing FROM 문자열) : 접미사를 잘라서 반환
• TRIM(both FROM 문자열) : 접두어와 접미사를 잘라서 반환
• TRIM(문자열) : 공백이 제거된 새로운 문자열 반환

• -LPAD(문자열, 길이, 채울문자) : 지정된 길이만큼 문자열에서 빈공간에 지정한 문자로 앞에서부터 채워서 반환
• -RPAD(문자열, 길이, 채울문자) : 지정된 길이만큼 문자열에서 빈공간에 지정한 문자로 뒤에서부터 채워서 반환

문자 치환 함수

• -TRANSLATE(문자열, 치환될 문자, 치환할 문자) : 문자열에 치환될 문자를 치환할 문자로 치환한 새로운 문자열 반환
• -REPLACE(문자열, 치환될 문자열, 치환할 문자열) : 문자열에서 치환될 문자열을 치환할 문자열로 치환한 후 새로운 문자열을 반환

 

숫자 함수

• ROUND(실수, 표시할 소수점 자리수) : 표시할 소수점 자리수까지 반올림한 수를 반환
• TRUNC(실수, 표시할 소수점 자리수) : 표시할 소수점 자리수 이후의 숫자를 버린 수를 반환
• MOD(m, n) : m을 n으로 나눈 나머지 값 반환
• POWER(m, n) : m의 n승 값을 반환
• CEIL(m) : m보다 큰 제일 작은 정수를 반환
• FLOOR(m) : m보다 작은 제일 큰 정수를 반환
• ABS(m) : m의 절대 값 반환(무조건 양수 반환)
• SQRT(m) : m의 제곱근 값 반환
• SIGN(m) : m이 음수면 -1, 양수면 1, 0이면 0을 반환. 부호 판단 함수

날짜 데이터 연산

- 오라클에서는 날짜를 6byte로 연산

- DATE 자료형은 기본적으로 'YYYY/MM/DD HH24:mi:ss'의 형식을 가지고 있다.

- 출력할 날짜의 형식 변경은 ALTER SESSION SET NLS_DATE_FORMAT = '변경할 날짜형식'

- ALTER SESSION : 현재 DB에 연결된 세션의 정보를 변경.

- NLS_DATE_FORMAT : 날짜 출력 형식

 

날짜 연산

- 날짜 + 숫자 : 숫자 일만큼이 날짜에 더해진 새로운 날짜 반환. 숫자 일만큼 이후의 날짜.

- 날짜 - 숫자 : 숫자 일만큼이 날짜에서 뺴진 새로운 날짜 반환. 숫자 일만큼 이전의 날짜.

- 날짜 + 숫자 / 24 : 숫자 시간만큼이 날짜에 더해진 새로운 날짜 반환. 숫자 시간만큼 이후의 날짜.

- 날짜 - 숫자 / 24 : 숫자 시간만큼이 날짜에 빼진 새로운 날짜 반환. 숫자 시간만큼 이전의 날짜.

- 날짜 - 날짜 : 두 날짜간의 차이가 일수로 반환

 

날짜 함수

-ROUND(날짜, 날짜단위(년도, 월, 일, 시, 분, 초)) : 지정한 단위까지의 날짜를 빼서 가져오고 나머지 단위들은 초기화된 상태로 출력. 날짜의 상태에 따라서 반올림(시간이 정오가 지났으면 다음일의 날짜, 일이 15일 이상 지났을 때는 다음 월이 나온다.)

ex) ROUND(SYSDATE, 'yyyy') -> 20230101 00:00:00

ROUND(SYSDATE, 'mi') -> 20230413 16:00:00

-TRUNC(날짜, 날짜단위(년도, 월, 일, 시, 분, 초)) : 지정한 단위까지의 날짜를 빼서 가져오고 나머지 단위들은 초기화된 상태로 출력. 날짜의 상태에 따라서 버림.

ex) ROUND(SYSDATE, 'yyyy') -> 20230101 00:00:00

ROUND(SYSDATE, 'mi') -> 20230413 16:00:00

• MONTHS_BETWEEN(날짜1, 날짜2) : 날짜1에서 날짜2를 뺀 만큼의 일수를 개월수로 치환하여 반환
• ADD_MONTHS(날짜, 숫자) : 날짜에 숫자 개월만큼 더한 날짜를 반환. 숫자 개월 만큼의 후의 날짜
• NEXT_DAY(날짜, 요일) : 날짜 이후의 지정된 요일에 해당하는 날짜를 반환
• LAST_DAY(날짜) : 날짜를 포함하고 있는 달의 마지막 날짜 반환

 

변환 함수

변환 함수는 문자를 숫자나 날짜로, 숫자나 날짜 문자로 변환해주는 함수. 

-TO_CHAR, TO_DATE, TO_NUMBER가 존재한다.

-TO_CHAR(숫자나 날짜, 출력형식) : 숫자나 날짜를 지정한 출력형식의 문자로 변환.

-TO_CHAR에서 숫자가 매개변수로 입력됐을 때 지정할 수 있는 형식지정자

-9 : 숫자의 출력 폭 지정

-0 : 선행 0 표시

-$ : 화폐 표시

-L : 지역 화폐 표시

-, : 쉼표의 위치 지정

-. : 마침표의 위치 지정

-MI : 음수표시

-EEEE : 실수표시(e의 지수표기법)

-TO_CHAR에서 날짜가 매개변수로 입력됐을 때 지정할 수 있는 형식지정자

-YYYY : 년도를 4자리로 표기

-YY : 년도를 2자리로 표기, 년도의 뒷 두자리가 출력되고 앞 두자리는 현재 년도의 앞 두자리와 동일하게 설정됨.

-RR : 년도를 2자리로 표기, 앞 두자리는 가까운 년도의 앞 두자리로 설정됨. 99 -> RR -> 1999

-MM : 월을 2자리로 표기

-MONTH : 월을 한글이나 영문으로 표기(APRILE, 4월...)

-MON : 월을 한글이나 영문의 약어로 표기(APR, 4월 ...)

-DD : 일을 2자리로 표기

-DAY : 요일을 한글이나 영문으로 표기(SUNDAY, 일요일)

-DY : 요일을 한글이나 영문의 약어로 표기(SUN, 일,....)

-HH24 : 시간을 1 ~ 24까지 표기

-HH : 시간을 1 ~ 12까지 표기, AM/PM 함께 사용하여 정확한 시간을 표시할 수 있도록 권장.

-MI : 분을 2자리로 표기

-SS : 초를 2자리로 표기

-AM/PM : 오전/오후 표기

-TO_DATE(문자열, 출력형식) : 매개변수로 입력된 문자열을 지정한 출력형식에 맞는 DATE타입으로 변환, 출력형식은 TO_CHAR에서 사용하는 형식과 동일.

-TO_NUMBER(문자열, 출력형식) : 매개변수로 입력된 문자열을 지정한 출력형식에 맞는 NUMBER타입으로 변환, TO_CHAR의 형식지정 방식과 동일한 형식지정방식을 사용한다.

 

NULL 값 처리를 해주는 NVL

특정한 컬럼의 데이터가 NULL일 때 NULL 대신에 다른 값을 조회하고 싶을 때 NVL함수를 사용해서 NULL처리를 해준다.

- NVL(컬럼명, NULL값인 데이터를 처리해줄 내용) : NULL값인 데이터를 처리해줄 내용으로 변경해서 조회.

 

조건을 처리해주는 DECODE, CASE ~ WHEN

DBMS에서는 IF조건절은 FUNCTION이나 PROCEDURE같은 PL/SQL에서만 사용할 수 있어서 일반 쿼리문에서는 DECODE, CASE~WHEN을 사용해서 조건 처리를 해줘야한다.

-DECODE

-DECODE(컬럼명 or 조회데이터, 조건1, 조건1이 true일 때 실행될 내용, 조건1이 false 일 때 실행될 내용) : 컬럼이나 조회해온 데이터의 내용이 조건1과 동일할 경우 true일 때 실행될 내용이 실행, 동일하지 않을 경우는 false일 때 실행될 내용 실행.

DECODE(컬럼명 or 조회데이터,

조건1, 

조건1이 true일 때 실행될 내용,

조건2,

조건2이 true일 때 실행될 내용,

조건3,

조건3이 true일 때 실행될 내용,

.....

조건n,

조건n이 true일 때 실행될 내용,

조건n이 false일 때 실행될 내용)

CASE ~ WHEN

CASE 컬럼명 or 조회데이터

WHEN 조건1 THEN 조건1이 true인 데이터 처리내용

WHEN 조건2 THEN 조건2가 true인 데이터 처리내용

.....

WHEN 조건n THEN 조건n이 true인 데이터 처리내용

ELSE 조건1~조건n까지 모두 false인 데이터 처리내용

END AS 별칭

DECODE와 CASE절의 차이점

DECODE는 컬럼의 값으로 비교가 진행되기 때문에 값을 비교해주는 조건문을 사용할 수 없는 반면

 

CASE절은 WHEN절이 true, false를 리턴하기 때문에 CASE 옆은 비어두고 WHEN절에 조건식을 넣어도 사용할 수 있다.

 

WHEN절에서는 IS NULL로 NULL 값을 비교해줘야되고 DECODE에서는 NULL 값 자체로 비교가능하다.

5. 다중행 함수(그룹 함수)

단일행 함수는 입력되는 행 하나에 결과가 하나가 나오는 함수였는데 다중행 함수는 여러개 행의 입력되어 하나의 결과가 나오는 함수.

 

만약 학년별 평점의 평균을 구한다고 생각하면 학년별로 그룹을 만들어줘야 되고 그 그룹별로 연산이 일어나야 되기 때문에 그룹함수라고 부른다.

 

그룹함수는 여러 행에 대한 통계를 계산하고 싶을 때 주로 사용된다.

 

왠만한 그룹함수들은 GROUP BY와 함께 사용한다. GROUP BY의 역할은 그룹으로 묶어줄 컬럼을 지정하는 역할을 한다.

 

GROUP BY 사용할 때 주의점은 SELECT절에 포함된 컬럼(통계함수 제외)들은 모두 GROUP BY절에 명시되어야 한다.

 

그룹함수의 종류

• -MAX : 값들 중 최대 값을 반환
• -MIN : 값들 중 최소 값을 반환
• -AVG : 지정된 값들의 평균 값을 반환
• -COUNT : 지정된 컬럼의 행의 개수를 반환
• -SUM : 지정된 값들의 합계를 반환

GROUP BY 조건을 만들어 주는 HAVING

- GROUP BY로 명시된 컬럼에 대한 조건은 HAVING으로도 만들 수 있다.

- 굳이 HAVING을 사용하지 않고 WHERE절을 사용해도 된다.

 

 SELECT 구문의 순서

-SELECT 컬럼1

, 컬럼2

, 컬럼3

, ...

, 컬럼n

FROM 테이블명

JOIN 테이블명

ON 조인조건1

AND/OR 조인조건2

WHERE 조회조건1

AND/OR 조회조건2

GROUP BY 그룹화될 컬럼1, 그룹화될 컬럼2 ....

HAVING 그룹화된 컬럼 조건

ORDER BY 순서;

 

오늘은 이렇게 오라클의 자료형과 함수 에 대해 알아보았습니다! 

다음 Sql 문법 공부도 열심히 해봐요! 🧐🧐