모방은 창조의 어머니

ORACLE 함수(Function)

1. 함수(Function) 란

데이터베이스에서 이야기 하는 함수는 수학에서 일반적으로 이야기하는 함수와 동일합니다. 매개변수를 받아 특정 계산(작업)을 수행하고 결과를 반환하는 것을 말합니다.

함수(函數, function)는 수학에서 두 집합 사이의 관계를 설명하는 논리적 개념으로, 간단하게 정의역의 각 원소마다 공역의 원소를 오직 하나씩 대응되는 관계를 말한다.(나무위키)

2. Oracle 함수의 종류

오라클 함수는 함수를 제작하는 주체를 기준으로 오라클에서 기본으로 제공하는 내장함수(Built-in Function)와 사용자가 필요시 직접 정의한 사용자 정의 함수(User-definde Function)으로 나뉩니다.

내장함수는 입력 방식에 따라 데이터를 한 행씩 입력하고 처리하는 단일행 함수(Single-row Function)와 여러행을 입력받아 하나의 결로고 반환하는 다중행 함수(Multiple-row Function)로 나뉩니다.

SQL 연산자 - 집합 연산자

두 개 이상의 SELECT문으로 조회한 결과를 하나의 결과로 연결하여 보여줄 때 사용 합니다. 이때 두 SELECT 문의 컬럼 갯수와 각 컬럼의 자료형, 순서가 일치해야 합니다. 서로 다른 타이블이나 컬럼에 대해서 수행이 가능 합니다. 이때 결과로 출력되는 컬럼명은 앞에 오는 SELECT문의 컬럼명이 사용 됩니다.

집합연산자들은 집합관계를 생각하시면 쉽게 이해하실 수 있습니다. 다음은 집합연산자 들에 대한 간략한 설명입니다. 

1. UNION 연산자

사원정보(EMP) 테이블에서 부서 번호(DEPTNO)가 10인 결과를 출력하는 SELECT문과 30인 결과를 출력하는 SELECT 문을 이용하여, 부서번호가 10 또는 30인 사원정보를 출력해 봅시다. EMP 테이블의 구조 예제로 사용된 TABLE의 정보는 'SCOTT 계정 예제 Table(emp, dept, salgrade)'을 참고하시면 됩니다.

SELECT ENAME, SAL, COMM, DEPTNO
FROM EMP
WHERE DEPTNO = 10
UNION
SELECT ENAME, SAL, COMM, DEPTNO
FROM EMP
WHERE DEPTNO = 30;

이때 출력할 컬럼의 숫자가 다르거나, 출력할 열의 자료형이 다른 경우에는 오류가 발생하여 실행되지 않습니다.

데이터 형만 같다면, 컬럼명에 영향을 받지 않음도 테스트 해 봅시다. 사원정보 테이블에서 급여(SAL)와 급여외 수당(COMM)은 NUMBER로 유형이 동일합니다. 이 컬럼의 위치를 두번째 SELECT 문에서 바꾸어 보면 어떻게 될까요? 

SELECT ENAME, SAL, COMM, DEPTNO
FROM EMP
WHERE DEPTNO = 10
UNION
SELECT ENAME, COMM, SAL, DEPTNO
FROM EMP
WHERE DEPTNO = 30;

아래 보시는 결과과 같이 컬럼명은 첫번째 SELECT 문에 사용된 것이 그대로 사용 되었고, 결과 자료는 각 SELECT 문의 수행 결과가 그대로 연결되었습니다.

실 사용시에도 이런 경우에는 오류가 발생하지 않으니, 작성시 유의 하시기 바랍니다.

2. UNION ALL 연산자

사원정보(EMP) 테이블에서 부서 번호(DEPTNO)가 10인 SELECT 문 2개를 연결해 봅시다. 이때 UNION을 사용하면, 중복이 제거되어 SELECT 문 두개를 연결한 의미가 없습니다. 중복해서 2번 표현 되게 하려면 어떻게 해야 할까요? 

SELECT ENAME, SAL, COMM, DEPTNO
FROM EMP
WHERE DEPTNO = 10
UNION ALL
SELECT ENAME, SAL, COMM, DEPTNO
FROM EMP
WHERE DEPTNO = 10;

3. MINUS 연산자

집합연산자를 이용하여 사원정보(EMP) 테이블에서 부서 번호(DEPTNO)가 10인 정보만 제외하고 출력하는 쿼리를 만들어봅시다. 

SELECT ENAME, SAL, COMM, DEPTNO
FROM EMP
MINUS
SELECT ENAME, SAL, COMM, DEPTNO
FROM EMP
WHERE DEPTNO = 10;

결과와 같이 첫 번째 SELECT문의 결과값에서 두 SELECT문의 결과값이 같은 데이터를 제외한 값들이 출력됩니다.

4. INTERSECT 연산자

두개의 SELECT 문에서 공통적인 결과 값을 갖는 데이터만 출력하려면 어떻게 하면 될까요? 

SELECT ENAME, SAL, COMM, DEPTNO
FROM EMP
INTERSECT
SELECT ENAME, SAL, COMM, DEPTNO
FROM EMP
WHERE DEPTNO = 10;

결과와 같이 첫 번째 SELECT문의 결과값과 두 SELECT문의 결과값이 같은 데이터들이 출력됩니다.

LIKE 연산자

LIKE 연산자는 특정한 내용을 포함 하고 있는 데이터 값을 조회하고 싶을때 사용하실수 있습니다.
예를 들어 이름(ENAME)이 알파벳 'A'로 시작하는 사원 데이터를 출력 하고 싶은 경우 아래와 같이 조회 하시면 됩니다. 예제로 사용된 TABLE의 정보는 'SCOTT 계정 예제 Table(emp, dept, salgrade)'을 참고하시면 됩니다.

SELECT *
FROM EMP
WHERE ENAME LIKE 'A%'; 

조건문에 'A%'는 알파벳 'A'로 시작하는 모든 것 이라는 뜻을 담고 있습니다. 이때 사용 하는 '%' 기호를 와일드 카드(wild card)라고 합니다. 와일드 카드는 특정 문자 또는 문자열을 대체하거나, 문자열 데이터의 패턴을 표시하는 특수문자로 LIKE문에서 사용하는 와일드 카드로는 '%' 이외에 '_'가 있습니다. 

• '_' :   어떤 값이든 상관없이 한 개의 문자열 데이터를 의미
• '%'  : 길이와 상관없이 모든 물자열 데이터를 의미

두개의 와일드 카드를 모두 이용해서 데이터를 찾을 수도 있습니다. 예를 들어 이름(ENAME)의 두번째 알파벳이 'A'인 사원 데이터를 출력 하고 싶은 경우 아래와 같이 조회 하시면 됩니다.

SELECT *
FROM EMP
WHERE ENAME LIKE '_A%';

이와 같이 와일드 카드를 이용하면, 자료의 일부만 알아도 해당 자료를 조회 하실 수 있습니다.

이번에는 이름(ENAME)의 알파벳이 'A'인 사원 데이터를 출력 하고 싶은 경우 어떻게 하면 될까요? 와이들 카드 '%'를 앞, 뒤 두번 사용하여 아래와 같이 조회 하시면 됩니다.

SELECT *
FROM EMP
WHERE ENAME LIKE '%A%';

BETWEEN ~ AND ~ 연산자

BETWEEN ~ AND ~ 연산자는 일정 범위 안의 데이터를 조회할 때 사용하는 연산자 입니다.

SELECT    [조회할 열 이름 1], [조회할 열 이름 2], ..., [조회할 열 이름 n], 
FROM       [조회할 테이블 이름]
WHERE    열 이름 BETWEEN 최소값 AND 최대값;

예를들어 급여(SAL)가 2,000 ~ 3,000 사이인 사원 데이터를 출력하고 싶은 경우 아래와 같이 조회 하시면 됩니다. 

예제로 사용된 TABLE의 정보는 'SCOTT 계정 예제 Table(emp, dept, salgrade)'을 참고하시면 됩니다.

SELECT *
FROM EMP
WHERE SAL BETWEEN 2000 AND 3000;

위와 같은 결과가 출력되도록 비교연산자 '>=', '<=' 와 논리연산자 'AND'를 사용하여 쿼리를 작성하실수도 있습니다.

SELECT *
FROM EMP
WHERE JOB = 'CLERK' OR JOB = 'SALESMAN' OR JOB = 'MANAGER';

이렇게 표현 하는 것도 하나의 방법이지만, ' BETWEEN ~ AND ~'을 이용하는 것이 훨씬 간단해 보입니다.   

이번에는 반대의 경우를 생각해 봅시다. 급여(SAL)가 2,000 ~ 3,000 사이 외인 사원 데이터를 출력 하고 싶을 때는 어떻게 하면 될까요? 아래와 같이 조회 하시면 됩니다.

SELECT *
FROM EMP
WHERE SAL NOT BETWEEN 2000 AND 3000;

IN 앞에 NOT를 붙여 반대의 상황을 조회 할 수 있도록 합니다. '논리 연산자 (Logical Operators)' 부분에서 설명한 바와 같이 NOT 연산자를 적용하면 TRUE/FALSE로 나오는 결과 값의 반대 값을 반환 합니다.

SQL 연산자 - IN 연산자

IN 연산자는 특정 컬럼의 여러가지 데이터 값을 조회하고 싶을때 사용하실수 있습니다.

SELECT    [조회할 열 이름 1], [조회할 열 이름 2], ..., [조회할 열 이름 n], 
FROM       [조회할 테이블 이름]
WHERE    열 이름 IN (조건 데이터 1, 조건 데이터 2, ..., 조건 데이터 n);

예를 들어 직책(JOB)이, CLERK, SALESMAN, MANAGER 인 사원 데이터를 출력 하고 싶은 경우 아래와 같이 조회 하시면 됩니다. 예제로 사용된 TABLE의 정보는 'SCOTT 계정 예제 Table(emp, dept, salgrade)'을 참고하시면 됩니다.

SELECT *
FROM EMP
WHERE JOB IN ('CLERK', 'SALESMAN', 'MANAGER');

위와 같은 결과가 출력되도록 비교연산자 '=' 와 논리연산자 'OR'를 사용하여 쿼리를 작성하실수도 있습니다.

SELECT *
FROM EMP
WHERE JOB = 'CLERK' OR JOB = 'SALESMAN' OR JOB = 'MANAGER';

이렇게 표현 하는 것도 하나의 방법이지만, 조건이 늘어 날 수록 조건식이 많아져 번거로울 수 있습니다. 

이번에는 반대의 경우를 생각해 봅시다. 직책(JOB)이, CLERK, SALESMAN, MANAGER 이 아닌 경우를 출력 하고 싶을 때는 어떻게 하면 될까요? 아래와 같이 조회 하시면 됩니다.

SELECT *
FROM EMP
WHERE JOB NOT IN ('CLERK', 'SALESMAN', 'MANAGER');

IN 앞에 NOT를 붙여 반대의 상황을 조회 할 수 있도록 합니다. '논리 연산자 (Logical Operators)' 부분에서 설명한 바와 같이 NOT 연산자를 적용하면 TRUE/FALSE로 나오는 결과 값의 반대 값을 반환 합니다.

SQL 연산자 - 논리 연산자 (Logical Operators)

AND 연산자

OR 연산자

NOT 연산자

제시된 조건의 결과와 반대되는 값을 얻고 싶을때 사용하는 연산자 입니다.   NOT 연산자를 적용하면 TRUE/FALSE로 나오는 결과 값의 반대 값을 반환 합니다. 예를 들어 사원정보(EMP) 테이블에 있는 부서번호(DEPTNO)가 10이 아닌 직원의 정보를 알고 싶다면 아래와 같이 조회 하시면 됩니다. 

SELECT *
FROM EMP
WHERE NOT DEPTNO = 10;

비교 연산자 (Comparison Operators)

1. 대소비교 연산자

두 값의 크기를 비교합니다.

사원정보(EMP) 테이블에 있는 월급여(SAL)가 2,000 이상인 직원의 정보를 알고 싶다면 아래와 같이 조회 하시면 됩니다. 예제로 사용된 TABLE의 정보는 'SCOTT 계정 예제 Table(emp, dept, salgrade)'을 참고하시면 됩니다.

SELECT *
FROM EMP
WHERE SAL > 2000;

숫자 이외에 문자의 대소 비교도 가능합니다. 이때 기준은 알파벳 순서 입니다. 사원정보(EMP) 테이블서 이름(ENAME)이 알파벳 F보다 뒤에 있는 직원의 정보를 알고 싶다면 아래와 같이 조회 하시면 됩니다. 

SELECT *
FROM EMP
WHERE ENAME >= 'F';

2. 등가비교연산자

연산자 얀쪽 항목의 값이 같은지 비교합니다. 등가비교 연산자는 양쪽 항목에 대한 조건식이 참 일때 TRUE값을, 거짓일때 FALSE값을 반환합니다.

사원정보(EMP) 테이블에 있는 부서번호(DEPTNO)가 10인 직원의 정보를 알고 싶다면 아래와 같이 조회 하시면 됩니다. 

SELECT *
FROM EMP
WHERE DEPTNO = 10;

사원정보(EMP) 테이블에 있는 부서번호(DEPTNO)가 10이 아닌 직원의 정보를 알고 싶다면 아래와 같이 조회 하시면 됩니다. 

SELECT *
FROM EMP
WHERE DEPTNO ^= 10;

DEPTNO ^= 10 이외에 DEPTNO <> 10나 DEPTNO != 10로 조회 하셔도 결과는 아래와 같습니다.

참고로 논리 부정 연산자인 NOT를 사용하여도 같은 결과를 얻으실 수 있습니다.( '논리 연산자 (Logical Operators)'  참고)

SELECT *
FROM EMP
WHERE NOT DEPTNO = 10;

SQL 연산자 - 산술 연산자(Arithmetic Operators)

더하기, 빼기, 곱하기, 나누기 등 산술 계산시 사용합니다.

EMP 테이블에 있는 이름(ENAME), 월급여(SAL), 수당(COMM) 이외에 연수입총액구 하여 표현해 주고 싶으면 직접 계산식을 추가하여 값을 표시할 수 있습니다. 연수입총액은 SAL*12+COMM 로 구하실수 있으며 아래와 같이 조회 하시면 됩니다. 예제로 사용된 TABLE의 정보는 'SCOTT 계정 예제 Table(emp, dept, salgrade)'을 참고하시면 됩니다.

SELECT ENAME, SAL, COMM, SAL*12+COMM
FROM EMP;

SQL 연산자 한눈에 보기

SQL에서 데이터를 다룰때 여러가지 연산자를 사용 할 수 있습니다. 

1. 산술 연산자 (Arithmetic Operators)

더하기, 빼기, 곱하기, 나누기 등 산술 계산시 사용합니다.

2. 비교 연산자 (Comparison Operators)

두개의 값을 비교할 때 사용 합니다.

3. 논리 연산자 (Logical Operators)

4. 기타 연산자

SQL 기본 문법 - 출력 순서(ORDER BY 절)

SELECT문을 사용하여 데이터를 조회할때 특정 컬럼을 기준으로 정렬하여 데이터를 출력해야 할 필요가 있을 때 ORDER BY 절을 사용합니다. ORDER BY절은 SELECT문 가장 마지막에 사용 되며, 여러 컬럼을 지정할 수도 있습니다.

SELECT 컬럼명1, 컬럼명2, ...
FROM 테이블명
WHERE 조건
ORDER BY 정렬하고자 하는 컬럼명 [ASC|DESC];

정렬기준에는 오름차순(ASC, Ascendung)과 내림차순(DESC, Descendung )가 있으며, 지정하지 않으면 오름차순(ASC)으로 정렬됩니다. 

1. EMP 테이블의 모든 정보를 SAL(급여) 기준으로 오름차순 정리

EMP 테이블의 구조 예제로 사용된 TABLE의 정보는 'SCOTT 계정 예제 Table(emp, dept, salgrade)'을 참고하시면 됩니다.

SELECT *
FROM EMP
ORDER BY SAL ASC;

이때 ASC는 생략해도 결과는 같습니다.

2. EMP 테이블의 모든 정보를 SAL(급여) 기준으로 내림차순 정리

ORDER BY 구문 뒤에 정렬 기준을 ASC 에서 DESC로만 변경해 주시면 원하는 결과를 얻으실 수 있습니다.

SELECT *
FROM EMP
ORDER BY SAL DESC;

3. EMP 테이블의 여러 컬럼의 정보를 기준으로 정렬

EMP 테이블의 모든 정보를 부서 번호(DEPTNO)를 오름차순으로 급여(SAL)는 내림차순으로 정렬해 봅시다.

SELECT *
FROM EMP
ORDER BY DEPTNO ASC, SAL DESC;

4. ORDER BY 절 사용시 주의 사항

SELECT 절을 통해 자료를 추출할때, ORDER BY절을 사용하는 것보다, 사용하지 않는 것이 출력 속도가 빠릅니다. 그 이유는 SELECT 문에서 결과 출력은, 먼저 출력할 자료들을 가져오고 그것을 정렬하는 작업을 진행한 후 출력하게 됨으로 정렬을 수행하지 않으면 그만큼 결과를 빠르게 출력해 볼 수 있습니다. 

자료 조회시 특정 컬럼으로 정렬이 필요한 경우, 조회 속도 향상을 위하여 자료를 저장하는 시점에서 정렬하여 저장하는 방법을 사용합니다.