2023/09/06📝 오라클의 함수를 배워보자

 

 

오늘도 열심히 공부하는 개-린이 입니다

👇 정리한 SQL파일 및 예제/과제 데이터들은 하단 git 링크에 오시면 있어용 👇

GitHub - gayulz/StudyHistory: 나의 공부 기록 commit

 

 

 

 

 

 

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;