Function (함수)
- 함수는 앞 절에 살펴본 스토어드 프로시저와 조금 비슷해 보이지만, 일반적인 프로그래밍 언어에서 사용되는 함수와 같이 복잡한 프로그래밍이 가능하도록 지원함
1. 함수의 생성
1. 함수의 형식
CREATE [ OR REPLACE ] FUNCTION [ schema. ] function_name
[ ( parameter_declaration [, parameter_declaration] )
]
RETURN datatype
[ { invoker_rights_clause
| DETERMINISTIC
| parallel_enable_clause
| result_cahe_clause
}...
]
{ { AGGREGATE | PIPELINED }
USING [ schema. ] implementation_type
| [ PIPELINED ] { IS | AS } { ]declare_section ] body | call_spec }
};
CREATE OR REPLACE FUNCTION 함수_이름 (파라미터)
RETURN 데이터 형식
AS
변수 선언 부분
BEFIN
이 부분에 PL/SQL 프로그래밍 코딩...
RETURN 변수;
END [함수_이름];2. 함수와 스토어드 프로시저의 차이
- 스토어드 프로시저의 파라미터와 달리 IN, OUT, IN OUT 등을 사용할 수 없음, 함수는 모두 입력파라미터로 사용됨
- 함수는 RETURN문으로 반환할 값의 데이터 형식을 지정하고, 본문 안에서는 RETURN 문으로 하나의 값을 반환해야 함, 스토어드 프로시저는 별도의 반환하는 구문이 없으며, 꼭 필요하다면 여러 개의 OUT 파라미터를 사용해서 값을 반활할 수 있었음
- 스토어드 프로시저는 EXECUTE로 호출하지만, 함수는 EXECUTE뿐 아니라, SELECT 문장 안에서도 호출됨
2. 함수 실습
1. BASIC 예제
CREATE OR REPLACE FUNCTION userFunc(value1 INT, value2 INT)
RETURN NUMBER
AS
BEGIN
RETURN value1 + value2;
END;
-- SELECT 문으로 호출
SELECT userFunc(100,200) from DUAL;
-- EXECUTE 문으로 호출
VAR retValue NUMBER;
EXECUTE :retValue := userFunc(100,200);
PRINT retValue;2. sqlDB를 초기화
-- 유저 삭제
DROP USER sqlDB CASCADE;
-- 유저 생성 및 공간 부여
CREATE USER sqlDB IDENTIFIED BY 1234
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP;
-- 권한 부여
GRANT CONNECT, DBA, RESOURCE TO sqlDB;
-- 회원 및 구매 테이블 생성
CREATE TABLE userTBL -- 회원테이블
( userID CHAR(8) NOT NULL PRIMARY KEY, --사용자 아이디(PK)
userName nvarchar2(10) not null, --사용자 이름
birtYear 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, --사용자아이디
prodName nchar(6) not null, --물품 명
groupName nchar(4), --분류
price number(8) not null, -- 단가
amount number(3) not null, -- 수량
FOREIGN KEY (userID) REFERENCES userTBL(userID)
-- 외래 키 설정하여 buyTBL의 userID를 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('JLW', '조관우', 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', '메모리', '전자', 80, 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);3. 출생년도를 입력하면 나이가 출력되는 함수 생성
-- 함수를 생성
CREATE OR REPLACE FUNCTION getAgeFunc(bYear NUMBER)
RETURN NUMBER
AS
v_age NUMBER;
BEGIN
-- 현재의 연도 EXTRACT(YEAR FROM SYSDATE) - 출생 연도 = 만나이를 돌려주는 함수
-- EXTRACT : 추출
v_age := EXTRACT(YEAR FROM SYSDATE) - bYear;
RETURN v_age;
END getAgeFunc;
-- 함수 호출(SELECT구문)
SELECT getAgeFunc(1997) FROM DUAL;
-- 함수 호출(EXECUTE 구문)
VAR retValue NUMBER;
EXECUTE :retValue := getAgeFunc(1979);
PRINT retValue;
-- 함수는 주로 테이블을 조회할 때 활용할 수 있다
SELECT userID, userName, getAgeFunc(birtYear) AS "만 나이" FROM userTBL;4. 개인 정보 보호를 위해서 이벤트가 당첨된 사용자 이름과 전화번호를 일부 가려서 출력하는 함수
-- 함수 작성
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;
-- 함수 삭제
DROP FUNCTION blindFunc;3. 테이블 반환 함수
- 함수는 일반적으로 하나의 값을 반환하지만, 반환하는 값(=RETURN 값)이 하나의 값이 아닌 '테이블' 형태인 함수도 필요한 경우가 있음. Oracle은 테이블 형태로 반환하는 함수를 'PIPELINED TABLE FUNCTION' 이라고 부름
1. 테이블 함수 형식
-- 1. TYPE 정의 : 테이블에 사용될 열의 형태를 정의
CREATE OR REPLACE TYPE Object_타입_이름 AS OBJECT
(
열 이름1 데이터 형식1,
열 이름2 데이터 형식2,
...
);
-- 2. TYPE 정의 : 테이블 형태의 컬렉셔을 정의
CREATE OR REPLACE TYPE 테이블_타입_이름 AS TABLE OF Object_타입_이름;
-- 3. 함수 정의
CREATE OR REPLACE FUNCTION 함수이름(파라미터)
RETURN 테이블_타입_이름
PIPELINED
AS
BEGIN
주로 반복문 안에서 PIPE ROW() 함수 사용
RETURN;
END;
-- 4. 실행
SELECT * FROM TABLE(함수 이름(파라미터));2. 테이블 함수 실습( 이름과 출생년도를 반환하는 형태 )
-- 함수 형태의 열을 정의하는 타입을 생성, 이름과 출생년도를 반환
CREATE OR REPLACE TYPE tableRowType AS OBJECT
(uName NCHAR(5), bYear NUMBER);
-- 테이블 타입을 정의, 위에 tableRowType을 사용
CREATE OR REPLACE TYPE tableType IS TABLE OF tableRowType;
-- 함수 생성
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;
-- 함수 실행
SELECT *FROM TABLE(tableReturnFunc('토마스, 제임스, 고든, 에밀리', '1990, 1995, 1993, 1999'));4. 함수 활용
-- 1. 지역 이름을 딱 한번만 출력하라
select distinct region from natios;
-- 2. 인구가 2억명 이싱인 국가의 이름을 검색하라
CREATE OR REPLACE FUNCTION returnPopulationFunc(nationName VARCHAR2,population NUMBER)
RETURN VARCHAR2
AS
v_nationName VARCHAR2(30) := '';
BEGIN
IF population >= 200000000 THEN
v_nationName := nationName;
ELSE
v_nationName := '';
END IF;
RETURN v_nationName;
END returnPopulationFunc;
SELECT returnPopulationFunc(nationname, population) FROM natios WHERE returnPopulationFunc(nationname, population) IS NOT NULL;
-- 인구가 2억 이상인 국가의 이름과 1인당 GDP를 검색하라
CREATE OR REPLACE FUNCTION returnPopulationFunc(population NUMBER, gdp NUMBER)
RETURN NUMBER
AS
v_gdp NUMBER;
BEGIN
IF population >= 200000000 THEN
v_gdp := gdp/population;
ELSE
v_gdp := 0;
END IF;
RETURN v_gdp;
END returnPopulationFunc;
SELECT nationname, returnPopulationFunc(population, gdp) FROM natios WHERE returnPopulationFunc(population, gdp) != 0;
아직까지는 코린이!