201014 Oracle 함수 (FUNCTION)

- 사용자가 만든게 아님. 오라클사용자들이 공통적으로 사용하는 기능들을 미리 프로그래밍-컴파일 해놓은 것

- 1) 미리 제공되어지는 함수, 2) 함수에서 제공하지 못하는, 사용자 정의 함수(나중에 PL/SQL에서 배움)

- 오라클 사용자들이 공통적으로 사용하는 기능을 구현한 모듈을 작성하여 컴파일한 실행 가능한 서브프로그램

- 함수는 반드시 반환 값이 존재한다.

- 문자열함수, 숫자함수, 날짜함수, 변환함수(TOCHAR,TONUM,TODATE), NULL처리, 집계함수(주어진 자료를 값이 같은 것끼리 모으기위함(GROUP BY)(COUNT SUM AVG MIN MAX)  등이 제공

- 자바의 메서드가 서브프로그램 개념 (필요할때마다 불러쓰기) (*하나의 메소드는 하나의 결과값을 내야한다)

- 서브프로그램은 하나의 결과를 내기위한 명령문들의 집합 -> 메소드 -> 서브프로그램

- 왜 1메소드당 하나의 결과 한 메소드에 합계평균등수 다 구하면 셋중 하나 필요없는 테이블은 애매해짐

 

-저장 프로시저와 함수의 차이점 : 함주는 반환값이 있어야함 (함수가 쓰인 위치에 결과를 되돌려줌)

  저장프로시저는 반환값이 없음

 

1. 문자열 함수

(1) INITCAP

- 주어진 문자열의 시작 글자만 대문자로 표현

- 주로 영문 이름 등을 표현하는 경우 사용

 

*사용형식

INITCAP(c)             <<c니까 문자열이나 문자열로 선언되어진 컬럼명을 써라

 

()안에 있는건 매개변수, agumanet, parament. 외부에서 자료를 전달했을때 initicap실행위해 자료를 받아들이는 통로

함수는 매개변수가 없어도 ()를 써줘야함 변수랑 구분이 안가니까...

주어진 문자열 'c'의 값의 시작 글자만 대문자로 표현

 

EX

사원테이블에서 이메일 컬럼값에 INITCAP함수를 적용하시오

SELECT EMPLOYEE_ID AS 사원번호,
         EMP_NAME AS 사원명,
         EMAIL AS "이메일(변경전)",
         INITCAP(EMAIL) AS "이메일(변경후)"
FROM EMPLOYEES;

오류**별칭에 ()가 들어가니가 ""로 묶어줘야함

 

(2) UPPER(c) / LOWER(c)

 

- 주어진 문자열 c 의 값을 대문자 또는 소문자로 변경할 때 쓴다.

- 자바에서 내용비교를 위해서는 String CLASS의 EQUALS를 쓴다. 이때에는 대소문자 구별말고 비교하자는 뜻으로

  스트링 클래스의equals ignore class를 쓴다. 

 

*사용형식

UPPER (c) OR LOWER(c)

EX

회원테이블에서 회원번호 'D001' 회원정보를 조회하시오

Alias 는 회원번호, 회원명, 전화번호이다.

 

한사람만 찾는거니까 조건이 부여됨

SELECT MEM_ID 회원번호,
        MEM_NAME 회원명,
        MEM_HP 전화번호
FROM MEMBER
WHERE LOWER(MEM_ID)='d001';

'' 안에 들어간 문자열은 반드시 대소문자 구분한다!

 

(3) CONCAT(c1,c2)

- 문자열 두개 합치는 || 연산자의 기능을 담당해줌

- 주어진 문자열 'c1'과 'c2'를 결합하여 새로운 문자열 반환

- ||는 n개 이을수있는 CONCAT는 매개변수 딱 두개쓸수있음

 

EX

회원테이블에서 대전에 거주하는 회원정보를 조회하시오

Alias 는 회원번호, 회원명, 주민번호, 주소이다.

단 주민번호는 xxxxxx-xxxxx형식으로, 주소는 기본주소와 상세주소 사이에 공백을 넣어 출력하시오

 

SELECT  MEM_ID 회원번호,
        MEM_NAME 회원명,
        CONCAT(MEM_REGNO1,CONCAT('-',MEM_REGNO2)) 주민번호,
        CONCAT(MEM_ADD1,CONCAT(' ',MEM_ADD2)) 주소
FROM    MEMBER
WHERE   MEM_ADD1 LIKE '대전%';

주번1이랑 주번2 , - 결합해야하는데 ,CONCAT은 두개만 쓸수있음

함수는 중첩할수있다.

 

 

 

(4) SUBSTR(c,n,n) 

- 주어진 문자열에서 정의된 값 만큼 부분 문자열을 추출하여 반환

***문자열에서 잘라낸 문자열도 문자열이라는 사실을 잊으면 안된다
자른 문자열과 비교되어지는 대상도 문자열이 되어야한다.
비교전엔 반드시 타입을 일치시켜야 함

 

 

*사용형식

SUBSTR(c,pos[,len})
****len 생략가능

- 주어진 문자열 'c'에서 'pos'번째 글자부터 'len'갯수 만큼의 문자열 추출 

- len이 생략되면 pos번째부터 나머지 모든 문자열 반환

- pos이 음수가 되어지면 역순으로 반환해줌

 

POS시작위치값, LEN갯수

**자바에도 똑같은 섭스트링이 있어서 헷갈리면 안된다.

자바는 길이(갯수)대신 END위치를 말함

 

자바는 위치값이 0부터 카운트되어짐

h	e	l	l	o
0	1	2	3	4

 

오라클은 0을 안쓴다 항상 1부터 카운팅됨

h	e	l	l	o
1	2	3	4	5

 

Ex

SELECT SUBSTR('무궁화 꽃이 피었습니다',2,5),
SUBSTR('무궁화 꽃이 피었습니다',2),
SUBSTR('무궁화 꽃이 피었습니다',-10,2)
FROM DUAL;

 

SUBSTR('무궁화 꽃이 피었습니다',-10,2) < 거꾸로 10번째 글자니까 화, 거기서 두개니까 화(공백)

 

EX

상품테이블에서 상품명에 '삼성'이 들어간 제품을 조회하시오

Alias는 상품코드, 상품명, 거래처코드, 매입가격

단, LIKE연산자 쓰지 말것

 

SELECT  PROD_ID 상품코드, 
        PROD_NAME 상품명, 
        PROD_BUYER 거래처코드, 
        PROD_COST 매입가격
FROM    PROD
WHERE   SUBSTR(PROD_NAME,1,2)='삼성';

 

 

EX

장바구니 테이블에서 매월 1일 판매된 판매정보를 조회하시오

SELECT  TO_DATE(SUBSTR(CART_NO,1,8)) 날짜,
        CART_PROD 상품코드,
        CART_QTY 판매수량
FROM    CART
WHERE   SUBSTR(CART_NO,7,2)='01';

**'01'이 아닌 01로 쓰면 안됨! 문자열에서 추출한 문자열이기때문에

숫자 01로 쓰면 1로 인식해버림 

자바에선 "7"+1은 71인데

오라클은 숫자가 먼저

01로 써도 결과는 나옴 (자동형변환) 그래도 ''로 문자열로 맞춰줘야함

 

**

SELECT  SUBSTR(A.CART_NO,1,8) 날짜,
        SUM(A.CART_QTY*B.PROD_PRICE) 판매금액
FROM    CART A, PROD B
WHERE    A.CART_PROD=B.PROD_ID
GROUP BY SUBSTR(A.CART_NO,1,8)
ORDER BY 1;

 

(5) LTRIM, RTRIM

- 주어진 문자열에서 정의된 문자열을 오른쪽 또는 왼쪽에서 잘라낸 후 결과가 반환

 

정의된 문자열을 생략하면(주로 생략한다) 공백을 잘라냄

 

*사용형식

LTRIM(c[,'str']), RTRIM(c,'str'])

 

- 문자열 'c'에서 왼쪽(또는 오른쪽)에 존재하는 'str'로 정의된 문자열을 삭제한 후 결과값 반환

- '[,'str']'이 생략되면 공백을 제거함

 

EX

SELECT LTRIM('무궁화 꽃이 피었습니다','무궁')
FROM DUAL;

SELECT LTRIM('무궁화 꽃이 피었습니다','궁화')
FROM DUAL;

왼쪽부터 찾는데 무로 시작하므로 궁화를 못찾음

 

SELECT LTRIM('무궁화 꽃이 피었습니다','무구')
FROM DUAL;

무는 찾았지만 구는 못찾아서 못지움

SELECT RTRIM('무궁화 꽃이 피었습니다','니다')
FROM DUAL;

 

 

ALTER TABLE EMPLOYEES
MODIFY (EMP_NAME CHAR(80));

VARCHAR2타입을 CHAR80으로 바꿨더니 뒤에 공백이 들어있어서 ...으로 뜬다.

이떄 EMP_NAME='홍길동' 이렇게 하면 원래는 '홍길동 vvvvv공백많아서 ' '홍길동'=/='홍길동     '은 다르므로 다르다

이떄 TRIM을 이용하면 된

 

EX 

사원 테이블에서 사원명과 입사일을 조회하시오

SELECT  EMP_NAME 사원명,
        HIRE_DATE 입사일
FROM EMPLOYEES;

 

너무길다... 오른쪽의 공백을 지우자

SELECT  RTRIM(EMP_NAME) 사원명,
        HIRE_DATE 입사일
FROM EMPLOYEES;

공백이 싹 지워짐

SELECT  RTRIM(EMP_NAME) 사원명,
        HIRE_DATE 입사일
FROM    EMPLOYEES
WHERE   EMP_NAME = 'Douglas Grant';

원래는 이거 못찾아야함 EMP_NAME은 80BYTE인데 더글라스글란트는 13바이트라 ...

근데 알아서 잘라서 해줌

WHERE   RTRIM(EMP_NAME) = 'Douglas Grant ';

이렇게 하면

못찾음

ALTER TABLE EMPLOYEES
MODIFY (EMP_NAME VARCHAR2(80));

 

이렇게 다시 바꿔줘도 이미 공백이 유효글자가 되어버려서 공백도 포함되서 변경됨

 

UPDATE해줘야함

 

UPDATE  EMPLOYEES
    SET EMP_NAME=RTRIM(EMP_NAME);

오른쪽 공백을 잘라서 자기자신에 다시 집어넣어라

 

(6) TRIM

 - 데이터 양쪽에 존재하는 공백을 삭제함

 - LTRIM, RTRIM,TRIM 은 단어 내부의 공백을 제거하지 못함

 - 문자열 내부의 공백이나 특정 문자열 제거는 REPLACE()함수 사용 (치환하다)

 

*사용형식

TRIM(c)

 

 

(7) REPLACE

 - 문자열 내부의 특정 문자열을 다른 문자열로 치환

EX 대전시 > 대전광역시 

공백을 찾아서 NULL로 치환하면 공백이 없어짐

 

*사용형식

REPLACE(c1,c2[,c3])

 

 - 주어진 문자열 'c1'에서 'c2'문자열을 'c3' 문자열로 치환

 - 'c3'가 생략되면 'c2' 문자열만 제거

 - 'c3'를 생략하고 'c2'를 공백으로 설정하면 단어 내부의 공백 제거

 

EX

상품테이블에서 상품명 중 '삼성'을 모두 'LG'로 바꾸시오

SELECT  PROD_ID 상품코드,
        PROD_NAME "상품명(구)",
        REPLACE(PROD_NAME,'삼성','LG') "상품명(신)"
FROM PROD;

 

SUBSTR과 REPLACE를 가장 많이씀

REPLACE는 공백을 지울때도 쓴다.

 

EX ) 상품테이블에서 상품명에 있는 모든 공백을 제거하시오

SELECT  PROD_ID 상품코드,
        PROD_NAME "상품명(구)",
        REPLACE(PROD_NAME,' ')
FROM    PROD;