함수정리

함수 정리(oracle)  

 

 

문자 함수
CHR(N) CHR 함수는 이진 N 값에 해당하는 문자 즉 ascii N 값을 반환한다

SQL> select chr(66) from dual;

C
-
B

concat(char1,char2) 주워진 두 문자열을 합친다 || 연산자와 같다

SQL> select concat(name,'은 성실하다') name   from wowbbs;

name
----------------
홍길동은 성실하다

length 문자열 길이 구하기

SQL> SELECT length('123456789')  from dual ;

LENGTH('123456789')
-------------------
                 9

substr(문자열,시작위치,문자수) 문자열 구하기

SQL> SELECT substr('123456789',2,3)  from dual ;

SUB
---
234

initcap(char)  문자의 첫번째 를 대문자로 변환시킨다

SQL> SELECT init('wowschool')  name FROM DUAL;

name
--------------
Wow school

lower(char)  모든 문자열을 소문자로 변환시킨다

SQL> SELECT lower('WOWSCHOOL')  name FROM DUAL;

name
--------------
wow school

UPPER(CHAR)  모든 문자열을 대문자로  변환시킨다

SQL> SELECT lower('wowschoool')  name FROM DUAL;

name
--------------
WOWSCHOOL

replace(문자열,교체대상 문자열, 교체할문자열) 문자열 중 일부 교체 하기
[ ex : web에서 엔터 처리 -> REPLACE(x.addText,chr(13),'<br>') ]

SQL> SELECT REPLACE('ok wow school' ,'ok','www') " wowschool" FROM DUAL;

wowschool
--------------
www wow school

 

rtrim 문자열 트리밍 하기  오른쪽 space 제거

SQL> SELECT Rtrim('  ok wow school     ' ) ,length(Rtrim('  ok wow school     '))   FROM DUAL;

RTRIM('OKWOWSCH LENGTH(RTRIM('OKWOWSCHOOL'))
--------------- ----------------------------
 ok wow school                           15

 

 

ltrim 문자열 트리밍 하기  왼쪽 space 제거

SQL> SELECT ltrim('  ok wow school     ' ) ,length(ltrim('  ok wow school     ') )   FROM DUAL;

LTRIM('OKWOWSCHOOL LENGTH(LTRIM('OKWOWSCHOOL'))
------------------ ----------------------------
ok wow school                                18

 

 

RPAD 자열 Rpad 하기 
SQL>  SELECT RPAD('1234', 10, '0') NAME FROM dual ;

NAME
----------
1234000000

SQL> SELECT RPAD('school', 12, '*') NAME FROM dual ;

NAME
------------
school******

 

lpad 문자열 Lpad 하기 
SQL> SELECT LPAD('1234', 10, '0') NAME FROM dual ;

NAME
----------
0000001234

SQL> SELECT LPAD('school', 12, '*') NAME FROM dual ;

NAME
------------
******school

NLS_INITCAP() initcap 동일 하고 다국어지원 
SELECT NLS_INITCAP  (’ijsland’) "InitCap" FROM DUAL;
InitCap
-------
Ijsland

SELECT NLS_INITCAP(’ijsland’, ’NLS_SORT = XDutch’) "InitCap" FROM DUAL;
InitCap
-------
IJsland

 

NLS_LOWER() LOWER 동일 하고 다국어지원 
SELECT NLS_LOWER (’CITTA’’’, ’NLS_SORT = XGerman’) "Lowercase" FROM DUAL;
Lower
-----
citta

 

NLS_UPPER() UPPER 동일 하고 다국어지원 
SELECT NLS_UPPER (’große’, ’NLS_SORT = XGerman’) "Uppercase" FROM DUAL;
Upper
-----
GROSS

 

DECODE decode 함수를 이용하여  문자열을 리턴 한다
DECODE(expression,value1,return1,value2,return2..............
              valueN,returnN,default_return_valeu)

select decode(name,'홍길동','의적','홍수환','참피언','차두리','축구선수','사람');

 

함수명 설명
initcap 각단어의 첫번째 문자를 대문자로 변환시킨다
lower 모든 문자를 소문자로 변환시킨다
upper 모든 문자를 대문자로 변환시킨다 
ASCII 문자열을 ASCII 수치값으로 변환 시킨다
SQL> select ASCII('a') from dual;
ASCII('A')
----------
       97

instr 문자열에서 패턴의 어커런스를 찾아낸다
SQL> select instr('wowschool 죽인다','ch') from dual;

INSTR('WOWSCHOOL죽인다','CH')
-----------------------------
                           5
INSTR('WOWSCHOOL죽인다','oo')
-----------------------------
                           7                          
INSTR('WOWSCHOOL죽인다','o')
-----------------------------
                           2                          

concat 두 문자열을  결합한다 || 같은 기능을 한다
SQL> select concat('wow','school') from dual;

CONCAT('W
---------
wowschool

 

 

 

==== 산술관련 함수  ==========================================================
산술 함수
 

ABS(n) : 절대값 |n|
sin() cos() tan() sinH() cosH() tanH() : radian값을 이용
ex> 30' 일 경우 sin(30* (3.14.../180));
exp(n) : 지수값 e를 밑으로 하는 e의 n승 값;
LN(n):자연 log값 밑수 e 지수 n
SQRT(n): 루트n
power(m,n): m의 n승
MOD(m.n) : m을  n 으로 나눈 나머지
floor(n): n보다 작은 정수값들중에서 최대값
ceil(n): n보다 큰 정수값들중에서 최소값
 
ex> floor(3.5)   -->  3   ,    ceil (3.5)  -->  4
       floor(-3.2) --> -4  ,     ceil (-3.5) --> -3
greatest(m,n): 두 수중에서 큰것
least(m,n);두 수중에서 작은 것
TO_NUMBER('숫자형 문자열'):문자를 숫자로 변환
round(m,n):반올림
trunc(m,n):절삭한다 즉 반올림을 허용하지 않는다.
 
ex>  Round(2.4678, 2)   -->  2.47   ,    trunc(2.4687, 2)    -->  2.46
     Round(3654.26, -2) --> 3700  ,   trunc (3654.26, -2 ) --> 3600
NVL(col명,값): col의 값이 null를 가질때 값을 가진다.
ex> 100+NULL은 NULL이 되기 때문에 이러한 문제점을 해결할 때 사용한다.
select ename,sal,comm,sal+nvl(comm,0) from emp;
           -->sal값과 comm값을 더하는데 comm값이 null값이면 0값을 취한다...

문자열 함수

lower():소문자로 변환시켜주는 함수
upper():대문자로 변환시켜주는 함수
Initcap():단어의 첫자만 대문자로 나머지는 소문자로
 
Initcap('i LOVE you') ---->  I Love You
 

length():문자열의 길이
substr(문자열,위치,갯수): 해당위치에서 갯수만큼의 문자열을 추출한다.
 
substr ('KOREA', 3, 2) ---->  'RE'
-- 3번째인 R부터 2글자 출력
instr(문자열,찾을 문자열[,위치,찾을위치])
 :문자열에서 이 문자를 찾아라. return값은 찾은 위치
 

Instr("abcdabkdoerabjdlfjdg","ab") -----> 1
-- 1번째에서 시작해서 처음  ab가 나타나는 1출력
Instr("abcdabkdoerabjdlfjdg","ab",4,2) ---->  12
-- 4번째부터 시작해서 두번째 나타나는  ab 출력
 

user:현재 접속된 사용자를 보여준다.
decode(col명,비교값,취할값,비교값,취할값,....기본값)
 :col값이 비교값이면 취할값으로 대체.
 

decode(deptno,10,'총무부',20,'영업부',30,'전산부','관리부'):나머지는 관리부
-- deptno가 10이면 총무부 , 20이면 영업부, 30이면 전산부 나머지 는 관리부 출력
날짜 함수

TO_CHAR(날짜,'포맷'): 날짜를 문자열로 바꾼다.
TO_DATE(문자열,'포맷'):문자열을 날짜로 ex> TO_DATE('97-10-14',' -----')
ADD_MONTHS(날짜,n):현재 날짜에 n개월수를 더한다.
MONTHS_BETWEEN(날짜1,날짜2):두 날짜의 개월수의 차를 구한다.
LAST_DAY(날짜):그 달의 마지막 날짜를 구한다.
NEXT_DAY(날짜,'요일'):날짜로 부터 다음에 나오는 요일은 며칠인가
next_day(sysdate,'금요일')
날짜 + n : 날짜에다 n 일을 더한다.
포맷:
yy : 년도를 2자리로
yyyy : 년도를 4자리로
year : 영어 철자로 표시
mm :월을 표시(01-12)
mon :월을 표시(DEC....):약자로 나온다.
month :월을 표시(1월,2월,....혹은 ,DECEMBER)
d :일을 표시(주에 대한 일)
dd :일을 표시 (월에 대한 일)
ddd :일을 표시 (년에 대한 일)
Q : 분기를 구한다.
DAY :요일 (월요일)
DY  :요일(월)
HH & HH12 :시간을 12시간제로
HH24 :24시간제로
MI:분
SS:초
AM & PM & A.M & P.M:12시간제 일때 오전 오후를 표시
접미사로 사용하는 포맷
TH:서수로 표시 ex> 4 -> DD ->4
                              4-> DDTH -> 4TH
SP:철자로 표시 ex> 4 ->DDSP -> FOUR
SPTH & THSP  ex> 4 -> DDSPTH -> FOURTH
 근무한 년월 수 ex> column "근무년수" Format a 14;
그룹 함수

 group함수 :
NULL값은 계산되지 않는다.
여러개의 데이타에 대한 결과 이므로 그룹함수라 한다
단일 필드와 함께 사용할 수 없다.
만약 단일 필드와 사용되면 group by절에 반드시 나와야 한다.
sum() : 합
avg() : 평균
count() : 갯수  count(*): 리턴되는 레코드의 수를 계산
ex> select deptno,sum(sapay) from sawon;  에러
 -->단위 필드이므로 따라서 마지막에 group by deptno;라고 지정해주면 사용가능
max() : 최대값
min() : 최소값
stddev : 표준편차
variance : 분산   

==== 숫자 날짜관련 함수 ======================================================
3. 함수

함수는 SQL의 매우 강력한 기능으로 아래와 같은 일을 할 경우 사용합니다.

·데이터에 관한 계산을 수행할 경우

·각각의 데이터 항목을 변경할 경우

·그룹의 결과 출력

·디스플레이할 날짜형식을 변경할 경우

·Column datatype을 변경할 경우

 

(가) 단일행 함수

·문자

·숫자

·날짜

·변환

단일행 함수는 데이터 항목을 조작하기 위해 사용됩니다. 한 개 이상의 인수(argument)를 받아들여 한 개의 행 당 하나의 값을 돌려줍니다. 인수(argument)는 아래 중 하나가 될 수 있습니다.

·상수

·가변적인 값

·변수

·표현식

 

(나) 단일행 함수의 기능

·Query시 각 행에 대해 수행합니다.

·행 당 한 개의 결과를 돌려줄 수 있습니다.

·참조시 사용한 자료형과 다른 자료형의 데이터 값을 돌려줄 수 있습니다.

·한 개 이상의 인수(argument)를 필요로 합니다.

·함수를 중첩하여 사용할 수 있습니다.

·SELECT, WHERE 및 ORDER BYW절에도 함수를 사용 할 수 있습니다.

function_name (cloumn｜expression, [arg1, arg2,...])

※ function_name 함수의 이름입니다.

column 데이터베이스에 존재하는 column 명

expression 어떤 문자스트링 또는 계산된 표현식입니다.

arg1, arg2 함수에 의해 사용될 인수입니다.

 

 

(다) 복수행 함수

이 함수들은 복수의 행을 조작하여 복수의 행에 대해 하나의 결과를 돌려줍니다.

(1)문자함수

단일행 문자 함수들은 문자 데이타를 입력으로 받아들여 문자나 숫자결과 값을 돌려줍니다.

①LOWER(column＼expression) : 알파벳 문자를 소문자로 바꾸어 줍니다.

②UPPER (column＼expression) : 알파벳 문자를 대문자로 바꾸어 줍니다.

③INITCAP (column＼expression) :알파벳 문자 값을 각 단어의 첫 자는 대문자로, 모든 다른 문자는 소문자로 바꾸어 줍니다.

④CONCAT(column1＼expression1,column2＼expression 2) : 첫 번째의 문자값과 두 번째의 문자값을 연결합니다. 연결연산자(∥)와 동일함.

⑤SUBSTR (column＼expression,m[n]) : 문자 값 중에서 m 위치에서 n 문자길이에 해당되는 문자를 돌려줍니다.

⑥LENGTH(column＼expression) : 문자 갯수를 값으로 돌려줍니다.

⑦NVL(cloumn＼expression1, column＼expression2) : 첫 번째 값이 널이면 두 번째 값으로 바꾸어 줍니다.

 

name에 '님'을 추가 입력 후 code가 101이면 수영부, 102이면 테니스부,103이면 볼링부......등으로 출력.

SQL> select name||'님' "성명",

2 decode(code,101,'수영부',102,'테니스부',

3 103,'볼링부',104,'탁구부',105,'농구부') "종목부서"

4 from member;

성명 종목부서

------------ --------

이과생님 볼링부

강호동님 탁구부

..........................

김길쭉님 수영부

이차석님 테니스부

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

 

 

회원의 addr column의 주소를 가지고 시별로 나누어 거주지항목을 만들어 출력하고 종목별로 code가 101이면 수영부, 102이면 테니스부,103이면 볼링부......등으로 출력.

SQL> select no "번호", name "회원이름", addr "주 소",

2 substr(addr,1,2)||'시' "거주지",

3 decode(code,101,'수영부',102,'테니스부',103,'볼링부',104,'탁구부',

4 105,'농구부') "종목부서" from member;

번호 회원이름 주 소 거주지 종목부서

-- ------------------------------------- ------ --------

1 이과생 서울 종로구 창동 11-1 서울시 볼링부

2 강호동 서울 관악구 신림동 33-3 서울시 탁구부

..........

15 이차석 성남시 분당구 내정동 4-5 성남시 테니스부

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

 

(라). 숫자 함수

숫자 함수는 숫자 값을 받아들여 숫자 값을 되돌려주는데, 이러한 몇가지 숫자 함수들을 살펴봅니다.

①ROUND (column/expression, n) : Column, Expression의 값을 소수점 n자기까지 반올림한다. 만약 n이 없다면 소숫점은 없어진다. 또한 n값이 음수라면 소숫점의 왼쪽 자리 수만큼 반올림됩니다.

②TRUNC(column/expression, n) : Column, Expression의 값을 소수점 n자리까지 절삭한다. 만약 N이 없다면 소숫점은 나타나지 않는다. 또한 N값이 음수라면 소숫점의 왼쪽 자리 수만큼 반올림된다.

③MOD(m, n) ; m값을 n값으로 나누고 남은 나머지를 Return한다.

 

소숫점 2자리, 0, -1자리까지 반올림한 값을 Display하시오.

SQL> select round(95.735,2), round(75.736,0), round(36.534,-1)

2 from sys.dual;

ROUND(95.735,2) ROUND(75.736,0) ROUND(36.534,-1)

--------------- --------------- ----------------

95.74 76 40

 

 

점수가 70보다 큰 모든 회원에 대해 점수를 6으로 나누고 난 나머지를 계산하십시오.

SQL> select no, name, mod(jumsu,6),code

2 from member

3 where jumsu > 70;

NO NAME MOD(JUMSU,6) CODE

-------- ---------- ------------ ---------

1 이과생 2 103

3 이순딩 5 101

7 김의욕 0 105

.....................................

15 이차석 3 102

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

 

(마). 날짜 함수

날짜 함수는 오라클의 날짜에 대해 연산을 합니다. 모든 날짜 함수는 DATE 자료형의 값을 돌려주는데 MONTHE_BETWEEN은 예외적으로 숫자 값을 돌려줍니다.

①MONTHS_BETWEEN(date1, date2) : date1과 date2 사이의 달수를 찾아줍니다. 결과는 양수 또는 음수가 가능합니다. date1이 date2보다 빠른 날짜면 결과는 양수지만 date2가 date1보다 빠른 날짜면 결과는 음수입니다. 결과의 비정 수 부분은 월의 부분을 나타냅니다.

②ADD_MONTHS(date, n) : 날짜에 n달을 추가합니다. n은 정수여야 하며 음수가 가능합니다.

③NEXT_DAY(date,'char') : date 다음의 해당주('cher')의 다음 요일를 찾아줍니다. char은 날 또는 문자 스크린을 나타내는 요일입니다.

④ROUND(date[, 'fmt']) : 어떤 형식 모델 (fmt) 명시되지 않았을 시 자정까지 시간 성정과 함께 날짜를 돌려줍니다. 다른 시간을 갖고 있는 날짜 비교시 유용합니다.

⑤TRUNC(date[,'fmt']) : 어떤 형식 모델(fmt)가 명시되지 않을시 date에 포함되어 있는 그날의 자정을 찾아줍니다. 만약 fmt=YEAR이면 DATE포함한 해의 첫 번째 날을 찾아줍니다. 이런 함수는 날짜의 시간 부분을 제거하기를 원할시 유용합니다.

(1)오라클의 날짜 저장

오라클은 다음을 나타내기 위해 날짜를 내부적인 숫자 형식으로 저장합니다.

·Century

·Year

·Month

·Day

·Hours

·Minutes

·Seconds

 

(2) SYSDATE

SYSDATE는 현재의 날짜와 시간을 돌려주는 날짜 함수입니다. 여러분은 column을 사용하는 것과 마찬가지로 SYSDATE를 사용할 수 있습니다. 예를 들자면 테이블에서 SYSDATE를 선택함으로 현재 날짜를 Display 할 수 있습니다. Dummy 테이블인 여미에서 SYSDATE를 선택하는게 일반적입니다.

 

(3) DUAL

DUAL 테이블은 SYS User가 소유하고 있으며 모든 사용자가 액세스할 수 있습니다. 이 테이블은 한 개의 column, DUMMY를 갖고 있으며 "X"라는 값을 가진 한 개의 행으로 되어 있습니다. DUAL 테이블은 사용자 데이타가 있는 테이블에서 유래하지 않은 상수값, 의사 열(pseudo-column), 표현식등의 값을 단 한번만 돌려 받기를 원할시 유용합니다.

 

DUAL 테이블 사용하여 현재 날짜를 Display 합니다.

SQL> select sysdate from sys.dual;

SYSDATE

---------

15-SEP-99

 

회원가입한지 5개월 미만인 모든 회원들의 번호, 가입일, 수업월수 및 입사 3개월 되는 날짜를 Display 하십시오

SQL> select no, name, gaip,

2 months_between (sysdate, gaip)tenure,

3 add_months(gaip, 3) review

4 from member

5 where months_between (sysdate,gaip)<5;

NO NAME GAIP TENURE REVIEW

--------- ---------- -------- --------- --------

1 이과생 99/09/01 .45840614 99/12/01

8 남자만 99/05/20 3.8455029 99/08/20

10 이수박 99/07/25 1.6842126 99/10/25

 

 

※ROUND와 TRUNC 함수들은 숫자 및 날짜 값에 대해서 사용할 수 있습니다. 이 함수들을 날짜에 대해 사용시 명시한 형식의 모델로 반올림하거나 절삭합니다. 따라서 여러분은 날짜를 가장 가까운 년 또는 월로 반올림 할수 있습니다.

 

1997년에 가입한 모든 회원들에 대한 가입일자를 ROUND 및 TRUNC 함수를 사용하여 번호, 이름, 전화번호, 가입일, 가입 월을 Display 하고 이를 비교해 보십시오.

SQL> select no, name, tel, gaip,

2 round(gaip,'month'),

3 trunc(gaip,'month')

4 from member

5 where gaip like '%97';

NO NAME TEL GAIP ROUND(GAI TRUNC(GAI

3 이순딩 000-0000 30-APR-97 01-MAY-97 01-APR-97

4 황백수 391-9192 20-MAR-97 01-APR-97 01-MAR-97

6 이민자 888-1212 20-AUG-97 01-SEP-97 01-AUG-97

 

 

①TO_CHAR(number\date,['fmt']) : 숫자 또는 날짜값을 fmt 형식 모델을 사용하여 VARCHAR2 문다스트링으로 바꾸어 줍니다.

②TO_NUMBER(char) : 숫자를 포함하고 있는 문자 스트링을 숫자로 전환합니다.

③TO_DATE(char,['fmt']) : 날짜를 나타내는 문자스트링을 명시한 fmt에 따라 날짜값으로 바꾸어 줍니다. fmt를 생략시 형식은 DD-MON-YY가 됩니다.