[Oracle] SQL Class 5 - [단일행] round, trunc, ceil, floor, [문자열] length, lengthbm, trim, ltrim, rtrim, [변환] lower, upper, initcap, [대체함수] replace, [채우기함수] lpad, rpad, [문자열 추출] substr, instr, [조건] decode, cas..

2023.10.30

## 단일행 함수: 각 값을 별도의 함수로 추출하는 명령어

: DB에서는 반드시 리턴값이 1개 존재함.

- 단일행: 각 행별로 결과값을 추출(함수의 종류가 많음)

  다중행: 행이 여러개 들어가서 결과값을 1개 추출(함수의 종류가 정해져 있음)

 

기존 수학 함수도 이용할 수 있으나, 현업에선 사용할 일  거의 없음.

1. 반올림: round는 자리수 지정한 반올림(절상)

2. 반올림: trunc는 자리수 지정한 이하 버림

 

3. ceil: 소수이하 무조건 올림

    floor: 소수이하 무조건 버림  --> 개월수 구할때 주로 사용

 

연습4)

사원명, 급여, 월급여(급여/12), 세금(급여의 3.3%)를 추출

단, 월급은 십단위에서 반올림 하고, 세금은 일단위에서 절삭

4. 문자열 함수

length: 문자수 확인

lengthbm: 바이트수 확인

trim: 문자 앞자리 공백제거

ltrim: 문자 왼쪽 공백제거

rtrim: 문자 오른쪽 공백제거

5. 변환함수

lower:전체 소문자 변환

upper: 전체 대분자 변환

initcap: 앞 문자만 대문자 변환

6. 대체함수

replace : 특정 문자를 대체함수로 변경

 

7. 채우기 함수

lpad & rpad(데이터, 전체 크기, '채울 문자') --> lpad는 왼쪽에 채움    / rpad는 오른쪽에 채움.

연습5) 

사원명, 급여, 급여현황(급여 100단위에 * 하나) 함수를 만드시오.

박길동 1500 ***************

이길동 1000 **********

왼쪽부터 빈공간 채우는 형태

## 문자열 추출 함수

substr(문자열, N번째, N개)

 

연습6)

고객명, 주민번호1(******-3182565), 주민번호2(041201-********)를 추출

 

검색문자의 위치값 

instr(문자열, '찾을문자', 1 혹은 -1(뒤), N번째)

 

## 조건함수

decode함수: decode(데이터, 조건1, 결과1, 조건2, 결과2,........, 그외 결과) "컬럼명"

case문 : case [데이터] when 조건1 then 결과1 when 조건2 then 결과2 ....... else 그외 결과 end "컬럼명"

연습7)

고객명, 전화번호, 성별을 추춘

 

(decode)

 

(case)

 

2023.10.31

## 변환 함수

 

to_char('날짜' | '숫자', '형식') : 날짜 또는 숫자를 지정한 형식의 문자*로 변환

to_date('날짜 형태의 문자열' , '형식') : 날짜 형태의 문자열을 날짜로 변환                 // 시간표현***

to_number('숫자 형태의 문자열'): 숫자 형태의 문자열을 숫자로 변환

 

 

 

 

현재 세션의 날짜관련 형식정보

SQL> col parameter format a50
SQL> col value format a50
SQL> select * from nls_session_parameters;

 

## SQL에 저장된 함수형식

PARAMETER                                          VALUE
-------------------------------------------------- ---------------------------------------------
NLS_LANGUAGE                                       KOREAN   --***
NLS_TERRITORY                                      KOREA
NLS_CURRENCY                                       ￦   --***
NLS_ISO_CURRENCY                                   KOREA
NLS_NUMERIC_CHARACTERS                             .,
NLS_CALENDAR                                       GREGORIAN
NLS_DATE_FORMAT                                    RR/MM/DD   --***
NLS_DATE_LANGUAGE                                  KOREAN   --***
NLS_SORT                                                    BINARY
NLS_TIME_FORMAT                                        HH24:MI:SSXFF
NLS_TIMESTAMP_FORMAT                               RR/MM/DD HH24:MI:SSXFF

PARAMETER                                          VALUE
-------------------------------------------------- ---------------------------------------------
NLS_TIME_TZ_FORMAT                                 HH24:MI:SSXFF TZR
NLS_TIMESTAMP_TZ_FORMAT                            RR/MM/DD HH24:MI:SSXFF TZR
NLS_DUAL_CURRENCY                                  ￦
NLS_COMP                                           BINARY
NLS_LENGTH_SEMANTICS                               BYTE
NLS_NCHAR_CONV_EXCP                                FALSE

17 개의 행이 선택되었습니다.

 ㅁ

## 세션의 형식변경

SQL> alter session set nls_date_format='yyyy.mm.dd hh24:mi:ss';                // 변수=값

연연/월월/일일 로 변경하는 방법

## 현재날짜,시간 확인.

##날짜 관련 형식 종류들
SQL> select sysdate, to_char(sysdate, 'year') from dual;

year로 선언하면, twenty twenty-three로 표현됨( 2023 )

               'yy'
               'month'
               'mon'
               'mm'
               'q'   -- 4(분기)   
               'd'   -- 3(요일, 1-일요일) : 요일순서대로 정렬하기 위함
               'day'
               'dy'
               'dd'
               'ddd'   -- 304(365 기준)
               'hh'
               'hh24'
               'miss'
               'am' | 'pm'

## 날짜 함수
SQL> select last_day(sysdate), next_day(sysdate, '월'), add_months(sysdate, 3) from dual;

## 개월수 함수: 탄생일 기준, 총 개월수
SQL> select months_between(sysdate, '80/1/1) from dual;

 

## 두 날짜 사이의 개월수를 반환하는 함수
months_between(sysdate,     )
SQL> select saname, month_between(sysdate, sahire) from sawon;

 

(연습1)

사원명, 입사일, 근무기간(XX년 XX개월)을 추출
SQL> col 근무기간 format a20
SQL> select saname, sahire, trunc(

## 날짜와 연산되는 함수 

SQL>  select saname, sahire, sahire+to_yminterval('01-06')from sawon;                   // '년-개월'

 

 

SQL> select sysdate, round(sysdate, 'month') from dual;
--> 현재날짜 기준으로 round

SQL> select sysdate, trunc(sysdate, 'year') from dual;

SQL> select sysdate, trunc(sysdate, 'month') from dual;

 

6. 통계함수(rank 함수)

형식: rank()  over( [partition by 컬럼명-삭제 가능하며, 특정컬럼 넣을때 입력]      
           order by 컬럼명 (asc)[desc] "석차"                        //rank()로 시작함.

 

(연습2)

입사일 순서대로 석차를 추출.
SQL> select saname, sahire, rank() over(order by sahire), "석차" from sawon;

(주의사항)
DB에서는 null은 큰 값이다.
한글 > 영어         //한글이 영어보다 큰 데이터임

 

(연습3)

null 값을 제외하고 석차를 구하세요.

(연습4)

부서별로 급여를 많이 받는 순서대로 석차를 추출(단, 급여가 null인 사람을 제외)

남교수님 단일행 함수 정리건 포함

단일행함수-마무리.txt

0.01MB