🌈 함수
- 미리작성되어 컴파일한 후 저장된 프로그램 모듈
- 사용자의 호출에 의하여 실행되고 결과를 호출문의 위치에 반환
단일행함수
- 하나의 결과를 반환하는 함수
- 문자열, 숫자, 날짜, 형변환, NULL처리함수 등이 있음
- 함수는 중첩(Nested) 사용이 가능
- SELECT절, WHERE절, ORDER BY절 등에서 사용
복수행 함수
- 그룹화된 자료에 대하여 그룹별로 처리하여 결과 반환
- GROUP BY절 사용
- SUM, AVG, COUNT, MAX, MIN
1. 🌈 문자열 함수
1) 🌈 ’||’
- 문자열 연산자
- 두 문자열을 결합하여 새로운 문자열을 반환
- JAVA의 문자열 연산자 ‘+’와 동일 기능
2) 🌈 CONCAT
- 문자열 결합함수
- ‘||’와 같은 기능 제공
(사용형식)
CANCAT (c1,c2)
- 주어진 문자열 c1과c2를 결합하여 새로운 문자열 반환
(EX1) MEMBER테이블의 자료를 다음과 같이 수정하시오
회원번호 : ‘h001’
주민번호 : ‘740728’ ⇒ ‘020728’
주민번호2 : ‘1455822’ ⇒ ‘3455822’
생년월일 : 1967/03/01 ⇒ 2002/03/01
UPDATE MEMBER
SET MEM_REGNO1 = '020728',
MEM_REGNO2 = '3455822',
MEM_BIR = TO_DATE('20020301')
WHERE MEM_ID = 'h001';회원번호 : ‘m001’
주민번호 : ‘750315’ ⇒ ‘000315’
주민번호2 : ‘2555555’ ⇒ ‘4555622’
생년월일 : 1975/03/15 ⇒ 2000/03/15
UPDATE MEMBER
SET MEM_REGNO1 = '000315',
MEM_REGNO2 = '4555622',
MEM_BIR = TO_DATE('20000315')
WHERE MEM_ID = 'm001';회원번호 : ‘r001’
주민번호 : ‘770120’ ⇒ ‘030120’
주민번호2 : ‘2382532’ ⇒ ‘4382432’
생년월일 : 1967/03/01 ⇒ 2003/11/26
UPDATE MEMBER
SET MEM_REGNO1 = '030120',
MEM_REGNO2 = '4382432',
MEM_BIR = TO_DATE('20031126')
WHERE MEM_ID = 'r001';
(EX2) 회원테이블에서 1995년이후 출생된 회원정보를 조회하시오.
Alias는 회원번호, 회원명, 주민등록번호, 직업이다
단, 주민등록번호는 XXXXXX-XXXXXXX형식으로 출력하시오
SELECT 회원번호, 회원명, 주민등록번호, 직업
FROM MEMBER
WHERE SUBSTR(MEM_REGNO1,1,2) // 복잡해지네SELECT MEM_ID AS 회원번호,
MEM_NAME AS 회원명,
MEM_REGNO1||'-'||MEM_REGNO2 AS 주민등록번호,
MEM_JOB AS 직업
FROM MEMBER
WHERE EXTRACT(YEAR FROM MEM_BIR)>=1995;
CONCAT함수를 사용해보자면
SELECT MEM_ID AS 회원번호,
MEM_NAME AS 회원명,
CONCAT(CONCAT(MEM_REGNO1,'-'),MEM_REGNO2) AS 주민등록번호,
MEM_JOB AS 직업
FROM MEMBER
WHERE EXTRACT(YEAR FROM MEM_BIR)>=1995;3) 🌈 LOWER, UPPER, INITCAP
- LOWER(c1) : c1에 포함된 대문자를 소문자로 변환
- UPPER(c2) : c2에 포함된 소문자를 대문자로 변환
- INITCAP(c3) : c3에 포함된 단어의 시작글자만 대문자로 변환
(EX1) 상품테이블에서 분류코드 ‘p202’에 속한 상품정보를 조회하시오
Alias는 상품코드, 상품명, 분류코드, 거래처코드
SELECT PROD_ID AS 상품코드,
PROD_NAME AS 상품명,
PROD_LGU AS 분류코드,
PROD_BUYER AS 거래처코드
FROM PROD
WHERE PROD_LGU = 'p202';
‘’안의 문자열은 대/소문자를 구분하는데, 현재 분류코드가 대/소문자인지 모를때, 내 쿼리가 소문자 형태이므로 테이블의 분류코드를 소문자로 바꾼채로 검색하면 안뜨는거 해결
WHERE LOWER(PROD_LGU) = 'p202';
(EX2) 2005년 회원’A001’의 구매정보를 조회하시오
Alias는 회원명, 상품명, 구매수량, 구매금액합계
CART테이블의 컬럼은 위와같다. 회원명과 상품명이 없고 단가가 없어 합계 못구함 참조필요
SELECT (SELECT MEM_NAME
FROM MEMBER
WHERE UPPER(MEM_ID)='A001') AS 회원명,
C.PNAME AS 상품명,
C.SQTY AS 구매수량,
C.SAMT AS 구매금액합계
FROM(SELECT A.CART_PROD AS PID,
B.PROD_NAME AS PNAME,
SUM(A.CART_QTY) AS SQTY,
SUM(A.CART_QTY*B.PROD_PRICE) AS SAMT
FROM CART A, PROD B
WHERE A.CART_NO LIKE '2005%'
AND UPPER(A.CART_MEMBER)='A001'
AND A.CART_PROD=B.PROD_ID
GROUP BY A.CART_PROD, B.PROD_NAME) C;4) 🌈 LPAD, RPAD
- 주어진 크기의 공간 왼쪽(오른쪽)부터 자료를 저장한 후 남는 기억공간 오른쪽(왼쪽)에 정의된 특정 문자열을 삽입하여 출력
(사용형식)
LPAD(c1, n[,c2]), RPAD(c1, n[,c2])LPAD(c1, n[,c2]) : 주어진 문자열 c1을 n크기의 기억공간에 왼쪽부터 저장한 후 남는 오른쪽에 c2 문자열을 삽입, c2가 생략되면 공백이 삽입, 수표보호문자 등으로 이용되었음
(EX1) 상품테이블에서 상품의 판매가격을 규칙대로 출력하시오.
출력은 상품코드, 상품명, 판매가 이며 판매가는 9자리에 출력하되, 남는 왼쪽공간은 ‘*’ 출력할것
SELECT PROD_ID AS 상품코드,
PROD_NAME AS 상품명,
LPAD(TO_CHAR(PROD_PRICE),9,'*') AS 판매가 --숫자를 TOCHAR로 문자열로 바꿔
FROM PROD;
SELECT LPAD(PROD_ID,15)||RPAD(PROD_NAME,30)||LPAD(TO_CHAR(PROD_PRICE),9,'*')
FROM PROD;
빈공간 공백 주목
맨오른쪽 * 줄맞춰 출력은 왜 안됐음?? 한글이라서 ㅡㅡ
5) 🌈 LTRIM, RTRIM
- 왼쪽(오른쪽)에 존재하는 특정 문자열(또는 공백)을 제거하기 위한 함수
LTRIM(c1[,c2]), RTRIM(c1[,c2]- LTRIM(c1[,c2]) : 주어진 문자열 c1에서 왼쪽 시작이 c2로 시작되면 c2부분을 제거, c2가 생략되면 왼쪽 공백을 제거
- RTRIM(c1[,c2]) : 주어진 문자열 c1에서 오른쪽 시작이 c2로 시작되면 c2부분을 제거, c2가 생략되면 오른쪽 공백을 제거
- 내부 가운데 공백 지우기는 X.. 이건 REPLACE로 해야함
ALTER TABLE MEMBER MODIFY(MEM_NAME CHAR(80));
공백 쩐다
SELECT *
FROM MEMBER
WHERE MEM_NAME='김은대'공백은 아스키코드40, 위 테이블과는 공백(71개)때문에 서로 다르다고 판단하는게 맞음
근데 실행되는 이유 ? : TRIM/RTRIM을 시스템 내부에서 알아서 붙여서 실행했기 때문.
WHERE RTRIM(MEM_NAME)='김은대'
WHERE TRIM(MEM_NAME)='김은대'그러니 정확하게 하려면 이렇게 하면된다
COMMIT;
ALTER TABLE MEMBER MODIFY(MEM_NAME VARCHAR2(80));커밋 후 자료형을 다시 VARCHAR2로 바꾸면 공백이 사라질까?
쓰고남은 빈공간은 반납하는게 VARCHAR2인데 왜 안돌아가져?
얘는 이미 공백71개도 DATA로 취급되었기 때문이다.
UPDATE MEMBER
WHERE TRIM(MEM_NAME) = '김은대'
SELECT *
FROM MEMBER
COMMIT;
//안돌아갈지도?UPDATE MEMBER
SET MEM_NAME = RTRIM(MEM_NAME) = '김은대'
SELECT *
FROM MEMBER
COMMIT;
SELECT LTRIM ('APAPALE PERSIMMON','A'),
LTRIM ('APAPALE PERSIMMON','APP'),
LTRIM ('APAPALE PERSIMMON','PA'),
RTRIM ('APAPALE PERSIMMON','MON'),
RTRIM ('APAPALE PERSIMMON','O')
FROM DUAL;
1번은 A삭제후 P만나서 삭제 종료
2번은 APP에도 AP가 있으니 AP먼저 삭제, 또 AP가 남으니 삭제 ALE중 A는 포함되니 또 삭제
3번 왜지?
4번 오른쪽부터 보면 N과O와M있으니 삭제후 끝
5번 O가 맨처음에 없기 때문에 삭제 없음
6) 🌈 TRIM
- 단어 왼쪽과 오른쪽에 존재하는 모든 공백 제거
- 단어 내부 공백은 제거하지 못함
(사용형식)
TRIM(c1)(EX1) 오늘이 2005년 7월28일인 경우 장바구니 번호를 생성하시오.
SELECT SYSDATE FROM DUAL; -- 듀얼은 시스템이 제공, 안에 아무것도 없음.. SYSDATE가 특정 테이블에 존재하는것이 아니므로 가상 테이블을 지정한것
SELECT * FROM CART
WHERE CART_NO LIKE '20050728%'
ORDER BY 2;
SELECT MAX (TO_NUMBER(SUBSTR(CART_NO,9)))
FROM CART
WHERE CART_NO LIKE '20050728%';
뒷자리 다섯자리를 끊어서 네자리 숫자를 만든다음 제일 큰 값
근데 코드가 매년 바꿔줘야되는 코드이므로
SELECT MAX (TO_NUMBER(SUBSTR(CART_NO,9)))+1
FROM CART
WHERE SUBSTR(CART_NO,1,8) = TO_CHAR(SYSDATE,'YYYYMMDD');이렇게 하면 매년 바꿔줄 필요가 없어진다
SELECT MAX (TO_NUMBER(SUBSTR(CART_NO,9)))+1
FROM CART
WHERE SUBSTR(CART_NO,1,8) = TO_CHAR(SYSDATE,'YYYYMMDD');
SELECT TO_CHAR(SYSDATE,'YYYYMMDD')|| TO_CHAR(MAX(TO_NUMBER(SUBSTR(CART_NO,9)))+1,'00000')
FROM CART
WHERE SUBSTR(CART_NO,1,8) = TO_CHAR(SYSDATE,'YYYYMMDD');
//이러면 오류를 범한대 장바구니 번호 가운데가 띄워져있음
//해결 : 약간의 편법 (자동형변환)
SELECT MAX(CART_NO)+1
FROM CART
WHERE SUBSTR(CART_NO,1,8) = TO_CHAR(SYSDATE,'YYYYNNDD');
//못알아들음7) 🌈 SUBSTR
- 부분문자열을 추출 (결과의 데이터 타입도 문자열)
- 가장널리 사용되는 문자열함수
(사용형식)
SUBSTR(c1,m[,n])- 주어진 문자열 c1에서 m번째 문자부터 n개의 문자를 추출하여 반환 (왼쪽부터1번)
- n이 생략되면 m번째부터 나머지 모든 문자열 추출
- m이 0이면 1로 간주
- m이 음수이면 오른쪽부터 처리
(EX1)
SELECT SUBSTR('대한민국은 민주공화국이다', 2,5),
SUBSTR('대한민국은 민주공화국이다', 2),
SUBSTR('대한민국은 민주공화국이다', 2,30),
SUBSTR('대한민국은 민주공화국이다', -2,5)
FROM DUAL;
(EX2) 회원테이블에서 회원들의 성씨를 조회하시오(중복하지 말것)
SELECT DISTINCT SUBSTR(MEM_NAME,1,1)
FROM MEMBER(EX3) 장바구니 테이블에서 5월 판매현황을 조회하시오
Alias는 날짜, 제품코드, 판매수량
SELECT SUBSTR(CART_NO,1,8) AS 날짜,
CART_PROD AS 제품코드,
CART_QTY AS 판매수량
FROM CART
WHERE SUBSTR(CART_NO,5,2) = 05;