2021.02.22 Oracle SQL 함수

CREATE USER "SQLDB" IDENTIFIED BY "1234"  
DEFAULT TABLESPACE "USERS"
TEMPORARY TABLESPACE "TEMP";

GRANT connect, resource, dba TO SQLDB;   

--테이블 생성
   CREATE TABLE userTBL -- 회원테이블
( 
    userID CHAR(8) NOT NULL PRIMARY KEY, -- 사용자 아이디(PK)
    userName NVARCHAR2(10) NOT NULL, -- 이름
    birthYear Number(4) NOT NULL, -- 출생년도
    addr NCHAR(2) NOT NULL, -- 지역(경기, 서울, 경남 식으로 2글자만 입력)
    mobile1 CHAR(3), -- 휴대폰의 국번(010, 011, 016, 017, 018, 019 등)
    mobile2 CHAR(8), -- 휴대폰의 나머지 전화번호(하이픈 제외)
    height NUMBER(3), -- 키
    mDate  DATE -- 회원 카입일
);

CREATE TABLE buyTBL -- 회원 구매 테이블
(
    idNum NUMBER(8) NOT NULL PRIMARY KEY, -- 순번(PK)
    userID CHAR(8) NOT NULL, -- 아이디(FK)
    prodName NCHAR(6) NOT NULL, -- 물품명
    groupName NCHAR(4), -- 분류
    price NUMBER(8) NOT NULL, -- 단가
    amount NUMBER(3) NOT NULL, -- 수량
    FOREIGN KEY (userID) REFERENCES userTBL(userID)
);


--데이터 입력
INSERT INTO userTBL VALUES('LSG', '이승기', 1987, '서울', '011', '11111111', 182, '2008-8-8');
INSERT INTO userTBL VALUES('KBS', '김범수', 1979, '경남', '011', '22222222', 173, '2012-4-4');
INSERT INTO userTBL VALUES('KKH', '김경호', 1971, '전남', '019', '33333333', 177, '2007-7-7');
INSERT INTO userTBL VALUES('JYP', '조용필', 1950, '경기', '011', '44444444', 166, '2009-4-4');
INSERT INTO userTBL VALUES('SSK', '성시경', 1979, '서울', NULL , NULL , 186, '2013-12-12');
INSERT INTO userTBL VALUES('LJB', '임재범', 1963, '서울', '016', '66666666', 182, '2009-9-9');
INSERT INTO userTBL VALUES('YJS', '윤종신', 1969, '경남', NULL , NULL , 170, '2005-5-5');
INSERT INTO userTBL VALUES('EJW', '은지원', 1972, '경북', '011', '88888888', 174, '2014-3-3');
INSERT INTO userTBL VALUES('JKW', '조관우', 1965, '경기', '018', '99999999', 172, '2010-10-10');
INSERT INTO userTBL VALUES('BBK', '바비킴', 1973, '서울', '010', '00000000', 176, '2013-5-5');

CREATE SEQUENCE idSEQ; -- 순차번호 입력을 위해서 시퀀스 생성
INSERT INTO buyTBL VALUES(idSEQ.NEXTVAL, 'KBS', '운동화', NULL , 30, 2);
INSERT INTO buyTBL VALUES(idSEQ.NEXTVAL, 'KBS', '노트북', '전자', 1000, 1);
INSERT INTO buyTBL VALUES(idSEQ.NEXTVAL, 'JYP', '모니터', '전자', 200, 1);
INSERT INTO buyTBL VALUES(idSEQ.NEXTVAL, 'BBK', '모니터', '전자', 200, 5);
INSERT INTO buyTBL VALUES(idSEQ.NEXTVAL, 'KBS', '청바지', '의류', 50, 3);
INSERT INTO buyTBL VALUES(idSEQ.NEXTVAL, 'BBK', '메모리', '전자', 50, 10);
INSERT INTO buyTBL VALUES(idSEQ.NEXTVAL, 'SSK', '책' , '서적', 15, 5);
INSERT INTO buyTBL VALUES(idSEQ.NEXTVAL, 'EJW', '책' , '서적', 15, 2);
INSERT INTO buyTBL VALUES(idSEQ.NEXTVAL, 'EJW', '청바지', '의류', 50, 1);
INSERT INTO buyTBL VALUES(idSEQ.NEXTVAL, 'BBK', '운동화', NULL, 30, 2);
INSERT INTO buyTBL VALUES(idSEQ.NEXTVAL, 'EJW', '책' , '서적', 15 , 1);
INSERT INTO buyTBL VALUES(idSEQ.NEXTVAL, 'BBK', '운동화', NULL , 30, 2);

CREATE OR REPLACE FUNCTION userFunc(value1 INT, value2 INT)
RETURN NUMBER
AS
BEGIN
    RETURN value1 + value2;
END;

SELECT userFunc(100, 200) FROM DUAL;

VAR retValue NUMBER;
EXECUTE :retValue := userFunc(100,200);
PRINT retValue;

--실습2 출생년도를 입력하면 나이가 출력되는 함수를 생성해 보자
CREATE OR REPLACE FUNCTION getAgeFunc(bYear NUMBER)
    RETURN NUMBER
AS
    v_age NUMBER;
BEGIN
    v_age := EXTRACT(YEAR FROM SYSDATE) - bYear;
    RETURN v_age;
END getAgeFunc;

SELECT getAgeFunc(1979) FROM DUAL;
SELECT userID, userName, getAgeFunc(birthYear) AS "만 나이" FROM userTbl;

--개인 정보 보호를 위해서, 사용자 이름과 전화번호를 일부 가려서 출력하는 경우의 함수 작성
CREATE OR REPLACE FUNCTION blindFunc(uString NCHAR)
    RETURN NCHAR
AS
    v_string NCHAR(20) := '';
BEGIN
    IF uString = '-' THEN -- '-'는 전화번호가 없는 사용자.
        RETURN v_string;
    END IF;
    
    IF SUBSTR(uString, 1, 1) = '0' THEN -- 제일 앞이 0이면 전화번호
        v_string := CONCAT(SUBSTR(uString, 1, 8), '-xxxx');
    ELSE
        v_string := CONCAT(SUBSTR(uString, 1, 1), '00');
    END IF;
    RETURN v_string;
END blindFunc;

--SELECT에 함수를 사용해 보자
SELECT blindFunc(userName) AS "회원",
    blindFunc(mobile1 || '-' || mobile2) AS "연락처" FROM userTBL;






--실습3테이블 형태를 반환하는 함수를 생성하고 사용해 보자
--(1)테이블 형태의 열을 정의하는 타입을 생성한다. 이번 예제에서는 이름과 출생년도를 반환하는 형태를 만들어 보겠다.
CREATE OR REPLACE TYPE tableRowType AS OBJECT
( uName NCHAR(5), bYear NUMBER);

--(2)테이블 타입을 정의한다. (1)에서 만든 tableRowType을 사용한다.
CREATE OR REPLACE TYPE tableType AS TABLE OF tableRowType;

--(3)함수를 작성한다. 함수는 2개의 문자열을 입력받아서 테이블 형태로 반환하는 기능을 한다.
--예로 '토마스, 제임스, 고든, 에밀리'와 '1990, 1995, 1993, 1999' 두 문자열을 다음의 테이블 형태로 반환한다.
CREATE OR REPLACE FUNCTION tableReturnFunc(nameString NVARCHAR2, birthString NVARCHAR2)
    RETURN tableType
    PIPELINED
AS
    v_nameString NVARCHAR2(500) := nameString;
    v_birthString NVARCHAR2(500) := birthString;
    v_rowType tableRowType; -- 1개 행(이름, 생년)
    v_nameIdx NUMBER; -- 이름 문자열에서 추출할 콤마(,)의 현재 위치
    v_birthIndex NUMBER; -- 생년 문자열에서 추출할 콤마(,)의 현재 위치
    v_name NCHAR(5); -- 추출할 1개의 이름 문자열
    v_birth NUMBER; -- 추출한 1개의 생년 숫자
BEGIN
    LOOP
        v_nameIdx := INSTR(v_nameString, ',');
        v_birthIndex := INSTR(v_birthString, ',');
        IF v_nameIdx > 0 AND v_birthIndex > 0 THEN
        
            v_name := SUBSTR(v_nameString, 1, v_nameIdx-1);
            v_birth := TO_NUMBER(SUBSTR(v_birthString, 1, v_birthIndex-1));
            v_rowType := tableRowType(v_name, v_birth);
            PIPE ROW(v_rowType);
            v_nameString := SUBSTR(v_nameString, v_nameIdx+1);
            v_birthString := SUBSTR(v_birthString, v_birthIndex+1);
        ELSE
            v_rowType := tableRowType(v_nameString, v_birthString); -- 나머지 값
            --대입
            PIPE ROW(v_rowType);
            EXIT;
        END IF;
    END LOOP;
    RETURN;
END tableReturnFunc;
    
--Function TABLERETURNFUNC 컴파일 출력
SELECT * FROM TABLE(tableReturnFunc('토마스,제임스,고든,에밀리' , '1990, 1995, 1993, 1999'));