1. 함수 생성 및 활용
- 커스텀 함수
- 리턴값이 있음 (프로시저와 다름)
- 함수 사용 테이블은 DUAL
:
1. 현재 시간, 날짜 확인
- 함수 : FUNC_TODAY
CREATE OR REPLACE FUNCTION FUNC_TODAY RETURN VARCHAR2
IS
V_DATE VARCHAR2(40);
BEGIN
SELECT TO_CHAR(CURRENT_DATE, 'YYYY-MM-DD HH24:MI:SS')
INTO V_DATE
FROM DUAL;
RETURN V_DATE; -- 프로시저와 달리 리턴이 있음!!!
END;
/
-- 함수 확인
SELECT FUNC_TODAY FROM DUAL;2. 1~n 까지 합계
- 함수 : FUNC_SUM
CREATE OR REPLACE FUNCTION FUNC_SUM(TNUM NUMBER) RETURN NUMBER
IS
V_SUM NUMBER := 0;
BEGIN
FOR I IN 1..TNUM LOOP
V_SUM := V_SUM + I;
END LOOP;
RETURN V_SUM;
END;
/
-- 함수 확인
SELECT FUNC_SUM(10) FROM DUAL;
-- 적용 예
SELECT M.*, FUNC_SUM(USERAGE) FROM MEMBER1 M;3. 일괄추가 시퀀스 함수
- 일괄추가 할때 시퀀스를 함수로 사용
- 제약조건 : WRITER는 MEMBER3 아이디만 가능
3-1. 기존 일괄추가
- 안됨
- 시퀀스 숫자를 동시에 가져오기때문 무결성 제약조건이 위배
INSERT ALL
INTO BOARD3(NO, TITLE, CONTENT, WRITER, HIT, REGDATE)
VALUES(SEQ_BOARD3_NO.NEXTVAL, 'AA1', 'BB1', 'C', 1, CURRENT_DATE)
INTO BOARD3(NO, TITLE, CONTENT, WRITER, HIT, REGDATE)
VALUES(SEQ_BOARD3_NO.NEXTVAL, 'AA2', 'BB2', 'B', 1, CURRENT_DATE)
SELECT * FROM DUAL;
SELECT * FROM BOARD3;
SELECT * FROM MEMBER3;3-2. 시퀀스 함수 사용
- 함수사용
- 함수 : FUNC_BOARD_SEQ => 현재의 시퀀스값을 가져오는 함수
CREATE OR REPLACE FUNCTION FUNC_BOARD_SEQ RETURN NUMBER
IS
V_SEQ NUMBER := 0;
BEGIN
-- 시퀀스의 다음숫자 가져오기
SELECT SEQ_BOARD3_NO.NEXTVAL INTO V_SEQ FROM DUAL;
RETURN V_SEQ;
END;
/
--함수 확인
SELECT FUNC_BOARD_SEQ FROM DUAL;- 함수를 사용해서 시퀀스값 입력하는 일괄추가
-- 함수 사용 일괄추가
INSERT ALL
INTO BOARD3(NO, TITLE, CONTENT, WRITER, HIT, REGDATE)
VALUES(FUNC_BOARD_SEQ, 'AA1', 'BB1', 'C', 1, CURRENT_DATE)
INTO BOARD3(NO, TITLE, CONTENT, WRITER, HIT, REGDATE)
VALUES(FUNC_BOARD_SEQ, 'AA2', 'BB2', 'B', 1, CURRENT_DATE)
SELECT * FROM DUAL;
COMMIT;
SELECT * FROM BOARD3;
SELECT * FROM MEMBER3;함수 실습
- MEMBER3 에서 가장 나이가 많은 사람의 아이디를 구하는 함수 (FUNC_MEMBER3_AGE)
- MEMBER3 에서 나이가 10~20인 사람의 수(FUNC_MEMBER3_CNT)
- MEMBER3 에서 아이디가 일치하는 사람의 성별을 남, 여로 반환하는 함수 (FUNC_MEMBER3_GENDER)
- BOARD3 에서 조회수가 10이상인 개수를 구하는 함수(FUNC_BOARD3_HIT)
- 글번호가 전송되면 게시판의 자리수를 10자로 변환 (FUNC_BAORD3_HIT10)
- 1이면 0000000001, 1235면 0000001235
- 참고 : SELECT B.*, LPAD(HIT,10, 0) FROM BOARD3 B;
- 전체순위
- 성별별순위
1. MEMBER3 에서 가장 나이가 많은 사람의 아이디를 구하는 함수
- FUNC_MEMBER3_AGE
- 한명일때는 됨, 두명이상일때 안됨
1. 내꺼
SELECT * FROM MEMBER3;
CREATE OR REPLACE FUNCTION FUNC_MEMBER3_AGE RETURN VARCHAR2
IS
V_ID VARCHAR2(10);
V_AGE NUMBER := 0;
CURSOR CUR IS (SELECT AGE FROM MEMBER3);
BEGIN
FOR TMP IN CUR() LOOP
IF(V_AGE < TMP.AGE) THEN
V_AGE := TMP.AGE;
END IF;
END LOOP;
-- SELECT ID INTO V_AGE FROM MEMBER3 WHERE AGE=(SELECT MAX(AGE) FROM MEMBER3);
SELECT ID INTO V_ID FROM MEMBER3 WHERE AGE=V_AGE;
RETURN V_ID;
END;
/
SELECT FUNC_MEMBER3_AGE FROM DUAL;2. 쌤꺼
SELECT M.*, ROW_NUMBER() OVER(ORDER BY M.AGE DESC) ROWN FROM MEMBER3 M
: 나이순 내림차순 해서 순서대로 번호 붙인 뒤 1번 아이디 찾음- 나이많은 사람이 두명 이상일때도 작동은 하지만 한명의 아이디만 리턴함
SELECT ID FROM (
SELECT M.*, ROW_NUMBER() OVER(ORDER BY M.AGE DESC) ROWN FROM MEMBER3 M
)
WHERE ROWN = 1;
-------------------------------------------
CREATE OR REPLACE FUNCTION FUNC_MEMBER3_AGE RETURN MEMBER3.ID%TYPE
IS
V_TMP MEMBER3.ID%TYPE;
BEGIN
SELECT ID INTO V_TMP FROM (
SELECT M.*, ROW_NUMBER() OVER(ORDER BY M.AGE DESC) ROWN FROM MEMBER3 M
)
WHERE ROWN = 1;
RETURN V_TMP;
END;
/
SELECT FUNC_MEMBER3_AGE FROM DUAL;2. MEMBER3 에서 나이가 10~20인 사람의 수
- FUNC_MEMBER3_CNT
SELECT * FROM MEMBER3;
CREATE OR REPLACE FUNCTION FUNC_MEMBER3_CNT RETURN NUMBER
IS
V_CNT NUMBER := 0;
BEGIN
SELECT COUNT(*) INTO V_CNT FROM MEMBER3 WHERE (AGE >=10 AND AGE <= 20);
RETURN V_CNT;
END;
/
SELECT FUNC_MEMBER3_CNT FROM DUAL;3. MEMBER3 에서 아이디가 일치하는 사람의 성별을 남, 여로 반환하는 함수
- FUNC_MEMBER3_GENDER
SELECT M.*, FUNC_MEMBER3_GENDER(ID) FROM MEMBER3 M;
: 기존 테이블 마지막열에 남/여 로 반환된 값을 붙인 테이블을 조회
SELECT * FROM MEMBER3;
CREATE OR REPLACE FUNCTION FUNC_MEMBER3_GENDER(TID VARCHAR2) RETURN VARCHAR2
IS
V_GENDER VARCHAR2(10) := '남';
BEGIN
SELECT GENDER INTO V_GENDER FROM MEMBER3 WHERE ID = TID;
IF (V_GENDER = 'M') THEN
RETURN '남';
ELSIF (V_GENDER = 'M') THEN
RETURN '여';
END IF;
END;
/
SELECT FUNC_MEMBER3_GENDER('AR') FROM DUAL;
-- 추가
SELECT M.*, FUNC_MEMBER3_GENDER(ID) FROM MEMBER3 M;4. BOARD3 에서 조회수가 10이상인 개수를 구하는 함수
- FUNC_BOARD3_HIT
SELECT * FROM BOARD3;
CREATE OR REPLACE FUNCTION FUNC_BOARD3_HIT RETURN NUMBER
IS
V_HIT NUMBER := 0;
BEGIN
SELECT COUNT(*) INTO V_HIT FROM BOARD3 WHERE HIT > 10;
RETURN V_HIT;
END;
/
SELECT FUNC_BOARD3_HIT FROM DUAL;5. 글번호가 전송되면 게시판의 자리수를 10자로 변환
- FUNC_BAORD3_HIT10
- 1이면 0000000001, 1235면 0000001235
- 참고 : SELECT B.*, LPAD(HIT,10, 0) FROM BOARD3 B;
SELECT B.*, FUNC_BAORD3_HIT10(B.NO) FROM BOARD3 B;
: B 테이블 마지막열에 FUNC_BAORD3_HIT10의 결과값을 붙여서 조회한다
1. 내꺼
SELECT B.*, LPAD(HIT,10, 0) FROM BOARD3 B;
CREATE OR REPLACE FUNCTION FUNC_BAORD3_HIT10(BNO NUMBER) RETURN VARCHAR2
IS
V_CHAR VARCHAR(10);
BEGIN
SELECT LPAD(HIT,10, 0) INTO V_CHAR FROM BOARD3 WHERE NO = BNO;
RETURN V_CHAR;
END;
/
SELECT B.*, FUNC_BAORD3_HIT10(B.NO) FROM BOARD3 B;2. 쌤꺼
SELECT B.*, LPAD(HIT,10, 0) FROM BOARD3 B;
CREATE OR REPLACE FUNCTION FUNC_BAORD3_HIT10(tmp NUMBER) RETURN VARCHAR2
IS
V_TMP VARCHAR2(10);
BEGIN
SELECT LPAD(HIT,10, 0) INTO V_TMP FROM BOARD3 WHERE NO=tmp;
RETURN V_TMP;
END;
/
SELECT B.*, FUNC_BAORD3_HIT10(B.NO) FROM BOARD3 B;6. 순위 (참고용)
1. 전체순위
- FUNC_MEMBER3_RANK
- 전체순위를 이용하여 가장 나이 많은사람 구하기
SELECT M.*, RANK() OVER (ORDER BY AGE DESC ) RK FROM MEMBER3 M;
SELECT * FROM MEMBER3;
-- 전체순위를 이용하여 가장 나이 많은사람 구하기
CREATE OR REPLACE FUNCTION FUNC_MEMBER3_RANK RETURN VARCHAR2
IS
V_ID VARCHAR(10);
BEGIN
SELECT ID INTO V_ID FROM
(SELECT M.*, RANK() OVER (ORDER BY AGE DESC ) RK FROM MEMBER3 M)
WHERE RK = 1;
RETURN V_ID;
END;
/
SELECT FUNC_MEMBER3_RANK FROM DUAL;2. 지역별 순위
- FUNC_MEMBER3_RANK2
- 입력값 M/F로 성별별 나이 제일 많은 아이디 구하기
SELECT M.*, RANK() OVER (PARTITION BY GENDER ORDER BY AGE DESC ) RK FROM MEMBER3 M;
SELECT * FROM MEMBER3;
CREATE OR REPLACE FUNCTION FUNC_MEMBER3_RANK2(TGEN VARCHAR2) RETURN VARCHAR2
IS
V_ID VARCHAR2(10);
BEGIN
SELECT ID INTO V_ID FROM
(SELECT M.*, RANK() OVER (PARTITION BY GENDER ORDER BY AGE DESC ) RK FROM MEMBER3 M)
WHERE GENDER = TGEN AND RK = 1;
RETURN V_ID;
END;
/
SELECT FUNC_MEMBER3_RANK2('F') FROM DUAL;
The best