[01-2] Oracle DML (SELECT) 단일행함수

함수 function

자바로 따지면 메소드와 같은 존재, 전달된 값들을 읽어서 계산한 결과 반환 FUNCTION : 어디에서든지 쓸 수 있는,, METHOD : 특정 자료형에 사용이 가능한 함수 => 본질적으로 함수나 메소드나 같음 단일행 함수 : N개의 값을 읽어서 N개의 값을 리턴 그룹함수 : N개의 값을 읽어서 1개의 결과 리턴 - 단일행 함수와 그룹 함수는 함께 사용할 수 없음  : 결과 행의 개수가 다르기 때문

단일행함수 <문자열과 관련된 함수>

-- LENGTH (STR)  : 해당 전달된 문자열의 글자 수 반환, NUMBER 타입 -- LENGTHB (STR)  : 해당 전달된 문자열의 바이트 수 반환, NUMBER 타입 숫자, 영문, 특수문자 : '!', '~', '2', 'a' => 한 글자당 1바이트 한글 : 'ㄱ', 'ㅏ', '강' => 한 글자당 3바이트 -- INSTR(STR, '특정문자', 어디부터셀건지, 앞에서부터몇번째문자열?)  : 문자열로부터 특정 문자의 위치값 반환, NUMBER 타입 찾을 위치에서 시작값과 순번은 생략 가능 1 : 앞에서부터 몇번째? 2 : 뒤에서부터 몇번째? [예시] SELECT INSTR('AABAACABBAA', 'B') FROM DUAL; -- 결과 : 3   // 찾을 위치, 순번 생략 시 기본적으로 앞에서 첫번째 글자 검색 SELECT INSTR('AABAACABBAA', 'B', 1, 3) FROM DUAL; -- 결과 : 9   // 해당 문자열의 앞에서부터 세번째 B가 앞에서부터 몇번째에 있는가 SELECT INSTR('AABAACABBAA', 'B', -1) FROM DUAL; -- 결과 : 9   // 해당 문자열 앞에서부터 첫번째 B가 뒤에서부터 몇 번째에 있는가? SELECT NAME, INSTR(EMAIL, '@') FROM EMPLOYEE; -- EMPLOYEE 테이블에서 EMAIL 컬럼의 @의 위치 -- SUBSTR (STR, POSTITION, LENGTH)  : 문자열로부터 특정 문자열 추출.subString( ) 결과값 CHARACTER 타입 STR : '문자열' 또는 문자 타입 컬럼 POSITION : 시작 위치 값 LENGTH : 추출한 문자 개수(생략시 끝까지) [예시] SELECT SUBSTR('HELLOWORLD', 3) FROM DUAL; SELECT SUBSTR('HELLOWORLD', 3, 2) -- 3번부터 2개 FROM DUAL; SELECT SUBSTR('HELLOWORLD', -8, 5) -- 뒤에서 8번부터 5개 FROM DUAL; SELECT NAME, EMAIL, SUBSTR(EMAIL, 1, INSTR(EMAIL, '@')-1)  -- EMALI의 첫번째글자 ~ @의 앞글자까지 FROM EMPLOYEE; -- 이메일에서 ID 부분만 추출해서 조회 --  LPAD (STR, 최종적으로 반환할 문자의 길이(바이트), 덧붙이고자하는 문자(앞)) [예시] SELECT LPAD('ULLEBULLE', 15, 'a') FROM DUAL;    -- aaaaaaULLEBULLE -- -RPAD (STR, 최종적으로 반환할 문자의 길이(바이트), 덧붙이고자하는 문자(뒤)) [예시] SELECT RPAD('ULLEBULLE', 15, 'a') FROM DUAL;    -- ULLEBULLEaaaaaa 모든 직원의 주민등록번호 뒤 6자리를 마스킹처리해서 표현해보자 -1단계, SUBSTR을 이용해서 주민등록번호 앞 8자리만 추출 SELECT NAME, SUBSTR(EMP_NO, 1, 8) FROM EMPLOYEE; -2단계, RPAD를 주민등록번호 뒤 6자리에 * 붙이기 SELECT NAME 이름, RPAD(SUBSTR(EMP_NO, 1, 8), 14, '*') 주민번호 FROM EMPLOYEE; -- LOWER(STR) : 전부 소문자로 변경 -- UPPER(STR) : 전부 대문자로 변경 -- INITCAP(STR) : 각 단어 앞글자만 대문자로 변경 -- CONTACT(STR1, STR2)  : 전달된 두 개의 문자열을 하나로 합친 결과 반환, CHARACTER 형태 [예시] SELECT '가나다' || 'ABC' || 'ASD' FROM DUAL; SELECT CONCAT('가나다', 'ABC') FROM DUAL;   -- CONCAT함수는 세 개 이상 합치기 불가! -- REPLACE(STR, 찾을문자, 바꿀문자)  : STR에서 찾을 문자를 찾아서 바꿀 문자로 변환 후 반환, CHARACTER형태 [예시] SELECT REPLACE('서울시 강남구 역삼동', '역삼동', '삼성동') FROM DUAL;     -- 결과 : 서울시 강남구 삼성동 -- TRIM BOTH 양쪽(기본값, 생략가능) , LEADING 앞쪽, TLAILING 뒤쪽  : 문자열 앞, 뒤, 양쪽에 있는 특정 문자를 제거한 나머지 문자열 반환 [예시] SELECT TRIM('Z' FROM 'ZZZZDJZDJZZ') FROM DUAL; -- 양쪽(기본값) : BOTH SELECT TRIM(LEADING 'Z' FROM 'ZZZZDJZDJZZ') FROM DUAL; -- 앞쪽 : LEADING SELECT TRIM(TRAILING 'Z' FROM 'ZZZZDJZDJZZ') FROM DUAL; -- 뒤쪽 : TRAILING

단일행함수 <숫자와 관련된 함수>

-- ABS (NUMBER) : 절대값을 구하는 함수 [예시] SELECT ABS(-10) FROM DUAL;   -- 10 -- MOD(NUMBER1, NUMBER2) : 두 수를 나눈 나머지값 반환 -- ROUND(NUMBER, 위치) : 반올림 처리해주는 함수     위치 생략 가능, 기본값 0     위치 : 소수점 아래 N번째 수에서 반올림 -- CEIL (NUMBER) : 소수점 아래 수를 무조건 올림 처리해주는 함수 -- FLOOR(NUMBER) : 소수점 아래 수를 무조건 버림 처리해주는 함수 [예시] SELECT EMP_NAME, CONCAT(FLOOR(SYSDATE - HIRE_DATE), '일') "근무일수" FROM EMPLOYEE; -- TRUNC(NUMBER, 위치) : 위치 지정 가능한 버림 처리해주는 함수     위치는 생략 가능 , 생략시 기본값 0 -- TO_CHAR(NUMBER/DATE) : 숫자형, 날짜형 데이터를 문자형 타입으로 변환 (CHARACTER 타입으로 반환)     NUMBER / DATE => CHARACTER [예시] SELECT TO_CHAR(1234, '00000') FROM DUAL; --> 0 : 빈칸에 0을 채워넣겠다 -> 01234 SELECT TO_CHAR(1234,'9999999999') FROM DUAL; --> 9 : 빈칸에 공백을 채워넣겠다. SELECT TO_CHAR(1234, 'L0000') FROM DUAL; --> ￦1234 -- 현재 사용되고 있는 운영체제의 화폐기호를 붙일 수 있다ㅣ. SELECT TO_CHAR(1234,'L99,999') FROM DUAL; -- 1,234 SELECT NAME, TO_CHAR(SALARY, 'L999,999,999') 급여 FROM EMPLOYEE; -- NVL(컬럼명, 반환할 결과값) : 해당 컬럼이 NULL일 경우 반환할 결과값을 넣어 줄 수 있음, NULL 처리 함수 [예시] SELECT EMP_NAME, BONUS, SALARY + ((SALARY * NVL(BONUS, 0) ) * 12) 연봉 FROM EMPLOYEE; - 사원명, 부서코드(NULL이면 '없음') SELECT  EMP_NAME, NVL(DEPT_CODE, '없음') FROM EMPLOYEE; - NVL2 SELECT EMP_NAME, NVL2(DEPT_CODE,'부서있음','부서없음') FROM EMPLOYEE;

단일행함수 (조건문)

-- DECODE (비교대상(컬럼명/산술연산/함수), 조건값, 결과값...) [예시] - 사원명, 주민등록번호로부터 성별 자리 추출해서 남/여로 변환 SELECT EMP_NAME, DECODE(SUBSTR(EMP_NO, 8, 1), 1, '남', 2, '여') FROM EMPLOYEE; - 직원들의 급여를 인상시켜서 조회 - 직급코드가 'J7'인 사원은 급여를 10% 인상해서 조회 - 직급코드가 'J6'인 사원은 급여를 15% 인상해서 조회 - 직급코드가 'J5'인 사원은 급여를 20% 인상해서 조회 - 그 외의 직급코드인 사원들은 급여를 5% 인상해서 조회 SELECT NAME         , JOB_CODE         , SALARY "인상 전 급여"         , DECODE(JOB_CODE,              'J7', (SALARY + SALARY * 0.1),              'J6', (SALARY + SALARY * 0.15),              'J5', (SALARY + SALARY * 0.2),              (SALARY + SALARY * 0.05)) "인상 후 급여"   FROM EMPLOYEE;

-- CASE WHEN THEN 구문 [표현식] CASE WHEN 조건식1 THEN 결과값1         WHEN 조건식2 THEN 결과값2         . . .         ELSE 결과값 END [예시] SELECT NAME,         CASE           WHEN SUBSTR(EMP_NO, 8, 1) = '1' THEN '남'          ELSE '여'         END "성별"   FROM EMPLOYEE;