23.10.06. Oracle SQL 정렬(SORT), 함수(function), 문자함수

■ 정렬(SORT)
- ORDER BY 절을 이용해서 정렬한다. 
- ORDER BY 절은 SELECT문의 가장 마지막절에 기술한다. 
- 기본값으로 오름차순으로 정렬한다. asc(acsending)
- 내림차순 정렬 desc(descending)

SELECT employee_id, salary, job_id, department_id
FROM hr.employees
ORDER BY department_id asc;

SELECT employee_id, salary, job_id, department_id
FROM hr.employees
ORDER BY department_id desc;

-- 표현식 사용
SELECT employee_id, salary * 12 
FROM hr.employees
ORDER BY salary*12 desc;

SELECT employee_id, salary * 12 ann_sal
FROM hr.employees
ORDER BY ann_sal desc; -- 별칭으로 정렬 설정 가능

-- 열 별칭 사용
SELECT employee_id, salary * 12 "ann_sal"
FROM hr.employees
ORDER BY "ann_sal" desc; -- 별칭에서 큰 따옴표를 사용했다면 정렬에서도 큰따옴표를 써야 한다.

       /*     1 컬럼,       2 컬럼      */
SELECT employee_id, salary * 12 "ann_sal"
FROM hr.employees
ORDER BY 2 desc; -- 위치표기법 사용

SELECT employee_id, department_id, salary * 12 "ann_sal"
FROM hr.employees
ORDER BY 2 asc, 3 desc;

 

■ 함수(function)
- 기능의 프로그램
- 단일행 함수 
    1. 행당 조작하는 함수
    2. 입력값으로 한 행의 값이 들어가 출력값은 하나가 return 한다.
- 단일행 함수는 여러번 중첩할 수 있다.

■ 문자함수(사용할 때 주의할 것!!)
- upper : 대문자로 변환하는 함수
- lower : 소문자로 변환하는 함수
- initcap : 첫글자는 대문자 나머지 글자는 소문자로 변환하는 함수

SELECT last_name, upper(last_name), lower(last_name), initcap(last_name)
FROM hr.employees;

-- index table : range scan
SELECT * 
FROM hr.employees
WHERE last_name = 'KING';

-- 악성 쿼리문(형변환이 되는 타입) : tabel full scan
SELECT * 
FROM hr.employees
WHERE upper(last_name) = 'KING';

- concat : 연결연산자와 동일한 함수, 최대 2개
SELECT 
    last_name || first_Name, 
    concat(last_name, first_name)
FROM hr.employees;

SELECT 
    last_name || first_Name, 
    concat(last_name, first_name),
    upper( last_name || ' ' || first_name || ' ' || job_id ),
    concat( concat( concat(last_name, ' ' ),first_name ),job_id ) -- 필요할 때마다 concat()을 써줘야 한다.
FROM hr.employees;

 

- length : 문자의 길이를 리턴하는 함수 
- lengthb : 문자의 바이트(byte)값을 리턴하는 함수

SELECT last_name, length(last_name), lengthb(last_name)
FROM hr.employees;

-- 영문과 한글의 차이

SELECT length('bigdata'), lengthb('bigdata'), length('빅데이터'), lengthb('빅데이터')
FROM dual;

- instr(컬럼명, '찾는 문자') : 문자의 위치를 리턴하는 함수
- instr(컬럼명, 찾는 문자열, 시작 위치, 몇번째로 찾는 위치)

SELECT last_name, instr(last_name, 'a'), instr(last_name, 'a', 1, 1), instr(last_name, 'a', 1, 2)
FROM hr.employees;

-- 'a'가 2번 이상 나오는 last_name 쿼리문

SELECT last_name, instr(last_name, 'a'), instr(last_name, 'a', 1, 1), instr(last_name, 'a', 1, 2)
FROM hr.employees
instr(last_name, 'a', 1, 2) > 0;

- substr : 문자를 추출하는 함수 ex)주민번호 추출할때 이용
- substr(컬럼(문자열), 시작점, 추출 갯수)
시작점
1, 2, 3, 4, 5
-5, -4, -3, -2, -1

SELECT last_name, substr(last_name, 1, 1), substr(last_name, 1, 2)
FROM hr.employees;

-- 전체를 추출해야 할때
SELECT last_name, substr(last_name, 2, length(last_name)), substr(last_name, 2)
FROM hr.employees;

SELECT last_name, 
	substr(last_name, length(last_name)), substr(last_name, -1, 1), /* 제일 뒤에서 한 글자를 추출 할때 */
        substr(last_name, -2, 1), /* 제일 뒤에서 두번째 글자 */
        substr(last_name, -2, 2)
FROM hr.employees;

- trim : 왼쪽(접두), 오른쪽(접미) 부분에 연속되는 문자를 제거하는 함수
- ltrim : 왼쪽(접두) 부분에 연속되는 문자를 제거하는 함수
- rtrim : 오른쪽(접미) 부분에 연속되는 문자를 제거하는 함수

SELECT 
    trim('a' from 'aaababcaa') "trim",
    ltrim('aaababcaa', 'a') "ltrim",
    rtrim('aaababcaa', 'a') "rtrim"
FROM dual;

SELECT 
    '  KING  ', 
    trim(' ' from '  KING  '), 
    trim('  KING  '),
    ltrim('  KING  ', ' '),
    ltrim('  KING  '),
    rtrim('  KING  ', ' '),
    rtrim('  KING  ')
FROM dual;

- replace : 문자를 다른 문자로 치환하는 함수 
- replace(컬럼(문자열), 이전 문자, 새로운 문자) 

SELECT
    replace('100-100', '-', '%'),
    replace('100-100', '-', ' '),
    replace('  K  i  n g  ',' ','')
FROM dual;

- lpad : 문자의 자리를 고정시킨 후 문자값을 '오른쪽 기준'으로 채우고 '빈 왼쪽 공백'을 다른 값으로 채우는 함수
- rpad : 문자의 자리를 고정시킨 후 문자값을 '왼쪽 기준'으로 채우고 '빈 오른쪽 공백'을 다른 값으로 채우는 함수
- lpad(필수 값(입력X -> null로 출력, 공백으로 입력할 때, 자릿수에 +1), 자릿수(고정), 채움 문자)

SELECT 
    salary, 
    '****'||salary, 
    lpad(salary, 10, '*'), 
    rpad(salary, 10, '*')
FROM hr.employees;

[문제] salary 에 있는 값을 1000당 * 출력해주세요
SALARY  STAR
------  -----
5000    *****
1000    *
6000    ******

SELECT 5000, lpad(' ', 5000/1000 +1, '*')
FROM dual;

SELECT salary, lpad('*', salary/1000, '*')
FROM hr.employees;