SQL활용 2022/03/23(SQL 작성하기) 함수

무간·2022년 3월 23일

함수 반환값 존재

일부만 쓰기위함

SELECT CURRENT_DATE FROM DUAL;
-- 반올림
SELECT ROUND(1.2345, 2) FROM DUAL;
-- 대문자로 변경
SELECT UPPER('asdfjiqjwri') FROM DUAL;
SELECT M.*, UPPER(M.USERID) FROM MEMBER1 M;

-- 일부만 가져오기(나이, 시작위치, 개수)
SELECT M.*, SUBSTR(M.USERAGE, 0, 1) FROM MEMBER1 M;

/

함수 : 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:MM:SS')
    INTO V_DATE FROM DUAL;
    RETURN V_DATE; -- 프로시저와 다른점!!
END;
/
-- 함수확인
SELECT FUNC_TODAY FROM DUAL;
/

함수 : FUNC_SUM(10)

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;
/
-- 실행 결과 55
SELECT FUNC_SUM(10) FROM DUAL;
SELECT M.*, FUNC_SUM(USERAGE) FROM MEMBER1 M;
/

일괄추가

제약조건 작성자는 MEMBER3에 있는 아이디만 가능

-- 시퀀스 사용 무결성 오류 발생

INSERT ALL 
    INTO BOARD3(NO, TITLE, CONTENT, WRITER, HIT, REGDATE )
        VALUES(SEQ_BOARD3_NO.NEXTVAL, 'A1', 'A', 'B', 1, CURRENT_DATE)
    INTO BOARD3(NO, TITLE, CONTENT, WRITER, HIT, REGDATE )
        VALUES(SEQ_BOARD3_NO.NEXTVAL, 'A1', 'A', 'B', 1, CURRENT_DATE)
SELECT * FROM DUAL;

COMMIT;
/

함수 : FUNC_BOARD3_SEQ

현재의 시퀀스 값을 가져오는 함수

CREATE OR REPLACE FUNCTION FUNC_BOARD3_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_BOARD3_SEQ FROM DUAL;
/

시퀀스 대신 함수 사용

INSERT ALL 
    INTO BOARD3(NO, TITLE, CONTENT, WRITER, HIT, REGDATE )
        VALUES(FUNC_BOARD3_SEQ, 'A1', 'A', 'B', 1, CURRENT_DATE)
    INTO BOARD3(NO, TITLE, CONTENT, WRITER, HIT, REGDATE )
        VALUES(FUNC_BOARD3_SEQ, 'A1', 'A', 'B', 1, CURRENT_DATE)
SELECT * FROM DUAL;

COMMIT;

SELECT * FROM BOARD3;
/

함수 : FUNC_MEMBER3_AGE

MEMBER3 에서 가장 나이가 많은 사람의 아이디를 구하는 함수

선생님 풀이

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;
/

직접풀이

CREATE OR REPLACE FUNCTION FUNC_MEMBER3_AGE RETURN VARCHAR2
IS
    V_AGE VARCHAR2(10);
BEGIN
    SELECT ID INTO V_AGE FROM MEMBER3 WHERE AGE=(SELECT MAX(AGE) FROM MEMBER3);
    RETURN V_AGE;
END;
/
SELECT FUNC_MEMBER3_AGE FROM DUAL;
/

순위

-- 전체순위

SELECT M.*, RANK() OVER (ORDER BY AGE DESC ) RK FROM MEMBER3 M;

-- 지역별 순위
SELECT M.*, RANK() OVER (PARTITION BY GENDER ORDER BY AGE DESC ) RK FROM MEMBER3 M;

함수 : FUNC_MEMBER3_CNT

MEMBER3 에서 나이가 10~20인 사람의 수

CREATE OR REPLACE FUNCTION FUNC_MEMBER3_CNT RETURN NUMBER
IS
    V_AGE NUMBER ;
BEGIN
    SELECT COUNT(*) INTO V_AGE FROM MEMBER3 WHERE AGE >=10 AND AGE <=20; 
    --SELECT COUNT(*) INTO V_AGE FROM MEMBER3 WHERE AGE BETWEEN 10 AND 20;
    RETURN V_AGE;
END;
/
SELECT FUNC_MEMBER3_CNT FROM DUAL;
/

함수 : FUNC_MEMBER3_GENDER

MEMBER3 아이디가 일치하는 사람의 성별을 남,여로 반환하는

CREATE OR REPLACE FUNCTION FUNC_MEMBER3_GENDER(uid VARCHAR2) RETURN VARCHAR2
IS
    V_GEN VARCHAR2(2);
BEGIN
    SELECT GENDER INTO V_GEN FROM MEMBER3 WHERE ID = uid;
    IF V_GEN = 'M' THEN
        RETURN '남';
    ELSE
        RETURN '여';
    END IF;
END;
/
SELECT FUNC_MEMBER3_GENDER('A') FROM DUAL;
SELECT M.*, FUNC_MEMBER3_GENDER(ID) FROM MEMBER3 M;
/

함수 : FUNC_BOARD3_HIT

BOARD3 에서 조회수가 10이상인 개수를 구하는 함수()

CREATE OR REPLACE FUNCTION FUNC_BOARD3_HIT RETURN NUMBER
IS
    V_HIT NUMBER;
BEGIN
    SELECT COUNT(*) INTO V_HIT FROM BOARD3 WHERE HIT >=10;
    RETURN V_HIT;
END;
/
SELECT FUNC_BOARD3_HIT FROM DUAL;
/

함수 : FUNC_BAORD3_HIT10

글번호가 전송되면 게시판의 자리수를 10자로 변환 ()
1이면 0000000001, 1235면 0000001235

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;

SELECT * FROM BOARD3;
SELECT * FROM MEMBER3;