2023/09/06📝 오라클의 함수를 배워보자
👇 정리한 SQL파일 및 예제/과제 데이터들은 하단 git 링크에 오시면 있어용 👇
GitHub - gayulz/StudyHistory: 나의 공부 기록 commit
나의 공부 기록 commit . Contribute to gayulz/StudyHistory development by creating an account on GitHub.
github.com
Function , 함수
해당 그림을 보자.
중학교때 배웠던 그런 함수와 같다.
x 매개변수에 3을 집어 넣었더니 y 반환값이 2로 나눈 몫인 1이 나왔다는 가정을 해보자
그렇다면 ?
매개변수 : 3
함수기능 : 매개변수의 정수에 0.5를 곱센 계산하여 나머지를 반환
반환값 : 1
또 가정을 해보자
x 매개변수에 3, 4를 입력했더니 7 이라는 반환값이 나왔다.
그렇다면 ?
매개변수 : 3, 4
함수기능 : 두개의 매개 변수를 인식하고, 덧셈을 하는 수식으로 덧셈의 결과 값을 반환
반환값 : 7
함수를 만들 때 함수의 기능이 '어떤 처리'를 하느냐에 따라 반환값이 달라진다.
그리고 매개 변수는 여러개가 될 수 있지만, 반환값은 1개이다
오라클에 함수의 모형은
함수명 ( 매개변수 ) or 함수명( 매개변수1, 매개변수2 ) ...
특징은 매개 변수는 복수개 일 수 있다.
하지만 반환 값은 1개이다 (단 파이썬은 여러개 가능)
함수를 정의한다면?
많은 수식을 쓰지 않더라도 정확하고 간결하게 값을 구할 수 있도록 도와주는 기능 이라고 기억하자
예제로 공부해보자
SELECT max(point) FROM MEMBER;이 예제의 함수명은 MAX(매개변수) 이다.
MAX함수는 매개변수로 받는 컬럼의 데이터중 최고값을 반환해준다.
SELECT LENGTH(addr) FROM MEMBER;이 예제의 함수명은 LENGTH(매개변수) 이다.
LENGTH 함수는 매개변수로 받은 커리의 모든 문자 데이터 값의 길이를 반환한다.
두 함수의 차이점은 ?
MAX 함수는 튜플 전체를 그룹화 시키고, LENGTH 함수는 그룹화를 시키지 않는다.
함수중 그룹화 하는 함수를 집계함수라 한다
📌 오라클 집계함수 종류
SUM / MAX / AVG / COUNT
집계함수는 한 컬럼에 일정 구간 데이터들을 그룹화 시켜 갯수나 글자길이 등 통계를 해주는 함수이다.
함수를 쓰는 목적 및 적용시 주의점
목적
여러 수식 없이도 내장된 함수를 이용해 쿼리를 간결하게 표현 가능하다.
주의점
가장 중요한건 적절한 함수 선택이다.
함수 검색 시 오라클 내장함수 라고 먼저 검색을 하고 난 뒤 속성을 이해 하고 사용해야 한다.
함수 사용법은 블로그나 구글링 했을때 타인들의 예제를 보고 참고한다.
함수의 종류
오라클 내장함수는 크게 문자함수 / 숫자함수 / 날짜함수 가 있다.
문자 함수란 ?
문자 값을 반환한다. 문자 값을 반환 하는 함수는 별도의 문서가 없는 한 다음 데이터 유형의 값을 반환한다
입력 인수가 CHAR 또는 VARCHAR2 인 경우 반환 되는 값은 VARCHAR2 이다.
입력 인수가 NCHAR 또는 NVARCHAR2인 경우 반환 되는 값은 NVARCHAR2 이다.
함수가 반환하는 값의 길이는 반환되는 데이터 유형의 최대 길이에 의해 제한된다.
주로 쓰는 함수는
UPPER , LOWER , SUBSTR , LENGTH, REPLACE, CONCAT 이 있다
숫자 함수란 ?
숫자 함수는 숫자 입력을 받아 숫자 값을 반환한다.
대부분의 숫자 함수는 소수점 아래 38자리 까지 정확한 Number 값을 반환한다.
소수점36자리 까지 : COS , COSH, EXP, LN, LOG, SIN, SINH, SQRT, TAN, ANH
소수점이하 30자리까지 : ACOS, ASIN, ATAN
날짜 함수란 ?
날짜/시간 함수이다.
주로 TIMESTAMP : 시간대가 포함된 타임스탬프, 현재 시간대가 포함된 타임 스탬프 및 간격(간격 일~초, 간격 년~월)값에 대해 작동한다.
주로 쓰는 함수는
ADD_MONTHS , MONTHS_BETWEEN , SYSDATE, SYSTEMSTAMP,
TO_CHAR(datetime) , CURRENT_DATE, CURRENT_TIMESTAMP 이다
간단한 함수 문제
문제1 이름의 첫 글자만 출력하시오
SELECT SUBSTR(name, 1,1) AS 이
FROM MEMBER ;
SELECT * FROM MEMBER;
문제2 서울시를 서울특별시로 치환하시오
SELECT REPLACE(addr, '서울시', '서울특별시') AS 도시
FROM MEMBER ;
문제3 이름 뒤에 님을 붙혀서 출력하시오
SELECT concat(name,'님')
FROM MEMBER;
문제4 나이가 짝수인 사람만 출력하시오
SELECT name 이름 , age 나이
FROM MEMBER
WHERE MOD(age, 2)=0;
과제를 위한 데이터입력
drop table mymember;
drop sequence member_no;
create table mymember(
no int primary key,
name varchar(10),
addr varchar(10),
gender varchar(1) default 'w',
grade int default 1,
point int default 100,
w_date timestamp);
create sequence member_no
increment by 1
start with 10;
insert into mymember values(member_no.nextval,'kim min','suwon city','m',2,300,'19780201');
insert into mymember values(member_no.nextval,'Lee soo','seoul','m',1,200,'19810201');
insert into mymember values(member_no.nextval,'kim jin','cheonan','w',1,150,'19880401');
insert into mymember values(member_no.nextval,'Park su','daejeon','w',2,100,'19780601');
insert into mymember values(member_no.nextval,'kim hyun','busan','m',2,300,'19780201');
insert into mymember values(member_no.nextval,'Ryu min','cheonan','w',3,400,'19780201');
insert into mymember values(member_no.nextval,'kim mee','suwon','m',4,300,'19880201');
insert into mymember values(member_no.nextval,'kim sun','seoul','w',4,290,'19720201');
insert into mymember values(member_no.nextval,'choi min','suwon','w',2,220,'19850701');
insert into mymember values(member_no.nextval,'choi sun','cheonan','m',2,200,'19810801');
insert into mymember values(member_no.nextval,'park min','seoul','w',3,340,'19800501');
insert into mymember values(member_no.nextval,'kim min','suwon','m',3,370,'19841101');
insert into mymember values(member_no.nextval,'choi min','cheonan','w',2,300,'19901101');
insert into mymember values(member_no.nextval,'Song ban','busan','w',4,390,'19921201');
insert into mymember values(member_no.nextval,'Lee kyu','suwon','w',2,500,'19920901');
insert into mymember values(member_no.nextval,'kim suk','suwon','m',1,400,'19800801');
insert into mymember values(member_no.nextval,'park suk','daejeon','w',1,250,'19780501');
insert into mymember values(member_no.nextval,'kim wan','cheonan','m',2,210,'19790201');
insert into mymember values(member_no.nextval,'James','suwon','w',1,240,'19790301');
insert into mymember values(member_no.nextval,'Han me','seoul','m',4,210,'19731201');
insert into mymember values(member_no.nextval,'Jeon su','suwon','w',3,320,'19820401');
insert into mymember values(member_no.nextval,'Gong dul','daejeon','w',3,410,'19770411');
insert into mymember (no,name,gender,grade,point,w_date) values(member_no.nextval,'Ha ha','m',2,250,'19820201');
insert into mymember (no,name,gender,grade,point,w_date) values(member_no.nextval,'lee ha','w',3,350,'19870601');
insert into mymember (no,name,gender,grade,point,w_date) values(member_no.nextval,'choi ha','m',1,450,'19900201');
오늘의 과제
1. 남학생의 이름과 성별, 포인트를 출력하시오.
SELECT name 이름 , gender 성별, point 포인트
FROM mymember
WHERE gender 'm';
2. 남학생은 모두 몇명입니까?
SELECT count(gender) AS gender
FROM mymember
WHERE gender 'm';
3. 여학생의 평균 포인트를 출력하시오.
SELECT avg(point) AS 여학생평균포인트
FROM mymember
WHERE gender 'w';
4. 4학년중 최고점은 몇점입니까?
SELECT max(point)
FROM mymember
WHERE GRADE '4';
5. 이름을 모두 대문자로 바꿔서 출력하시오.
SELECT UPPER(name) AS 대문자출력
FROM mymember;
6. 이름은 모두 몇글자인지 출력하시오. (공백도 이름에 포함)
SELECT name, LENGTH(name) AS 이름글자수
FROM mymember;
7. 이름뒤에 성별도 같이 출력하되 하나의 컬럼에서 출력하시오.
SELECT CONCAT(name, gender) AS 이름성별
FROM mymember;
SELECT CONCAT(name, concat(' 성별 : ',gender)) AS 이름성별
FROM mymember;
8. 이름을 출력하되 이름의 첫글짜 부터 4번째 글자까지 출력하시오. (공백포함)
SELECT SUBSTR(name, 1, 4) AS 이름
FROM mymember;
9. 이름을 출력하되 이름의 2번째 글자 부터 6번째 글자까지 출력하시오.(공백포함)
SELECT SUBSTR(name, 2, 5) AS 이름
FROM mymember;
10. 주소에서 알파벳 o를 *로 치환하시오.
SELECT REPLACE(addr, 'o', '*')
FROM mymember;
11. 주소에서 공백을 모두 제거하시오
SELECT REPLACE (addr,' ')
FROM mymember;
12. 2학년의 포인트 평균을 구하시오.
SELECT avg(point)
FROM mymember
WHERE grade '2' ;
13. 학년이 짝수인 학생을 모두 출력하시오.
SELECT *
FROM mymember
WHERE MOD(grade, 2)0;
14. w_date는 등록일이다. 모든 학생의 등록년도를 출력하시오.
SELECT EXTRACT(year FROM w_date)
FROM mymember;
SELECT * FROM mymember;
15<어려움> 남자들의 포인트를 출력하시오. 포인트 출력시 백단위에서 절삭하시오
SELECT point, TRUNC(point, 2)
FROM mymember
WHERE gender 'm';
16.<어려움> 모든 학생의 포인트의 평균을 구하고 소숫점 2째 자리까지 표현하시오.
SELECT to_char(sum(point)/count(point), 'fm9990.00') AS 평균포인트
FROM mymember;
SELECT TRUNC(avg(point),2)
FROM mymember;
17.<어려움> 이름의 맨 뒤부터 시작하여 3개의 문자를 출력하시오.
SELECT substr(name, 3)
FROM mymember;
18 <아주많이어려움> 이름의 맨 뒤부터 시작하여 공백전까지의 문자를 출력하시오.
SELECT name , SUBSTR(name, instr(name,' ', 1)) 마지막공백이후
FROM mymember;
19<많이어려움> 이름은 모두 소문자 이다. 대문자로 검색어 KIM을 입력하였을때도
정상적으로 이름에 kim이 포함된 사람의 정보를 출력하시오.
SELECT name
FROM mymember
WHERE upper(name) LIKE '%KIM';
20.<어려움> w_date를 생년월일로 가정한다. 모두 2000년도 미만의 출생자이다
위 조건을 참고하여 3학년의 나이를 구하시오.
SELECT name 이름, grade 학년
, substr(w_date,1,8) 출생일
, extract(YEAR FROM sysdate)EXTRACT(YEAR FROM w_date) 나이
FROM MYMEMBER;
21. 이름에 빈칸이 없는 사람의 정보를 출력하시오.
SELECT *
FROM mymember
WHERE instr(name,' ') 0 ;
22 모든 사람의 정보를 출력하되 남자일 경우 한글로 남, 여자일 경우 한글로 여 라고 출력하시오
SELECT no, name, addr
, REPLACE(REPLACE(gender,'m', '남'), 'w', '여') AS gender, grade, point, w_date
FROM mymember;
select no, name, addr, gender, case gender when 'm' then ' 남' else '여' end
from mymember;