[PLSQL] function

PL/SQL 코드 작성시에는 지금까지 사용하던 익명 블럭은 잘 사용하지 않음. 일반적으로 이름이 있는 서브프로그램(함수) 또는 프로시져를 사용하는 것이 대부분임. 익명 블럭은 한번 사용하고 나면 없어져 버리는 휘발성 블럭이지만 함수 또는 프로시져는 컴파일을 거쳐 데이터베이스에 저장되어 재사용이 가능한 구조.

-- 함수의 형태
CREATE OR REPLACE RUNCTION 함수이름(매개변수1, 매개변수2, ...)
RETURN 리턴될데이터타입;
 IS[AS]
      변수,상수 선언
 BEGIN
      실행부
      RETURN 리턴값;
[EXCEPTION
      예외처리부]
END [함수이름];

-CREATE OR REPLASCE FUNCTION 라는 구문을 이용하여 함수를 생성함. 함수를 만들고 수정하더라도 이 구문을 계속 컴파일 할 수 있고, 마지막으로 컴파일 한 내용이 함수의 내용과 이름으로 사용됨.
-매개변수: 전달인수를 저장하는 변수로 "변수이름 변수자료형" 형태로 작성함
-첫번째 RETURN 구문 다음에는 리턴될 자료의 자료형을 쓰고 아래쪽 두번재 RETURN 구문 없애는 그 자료형으로 실제 리턴될 값 도는 변수명을 써줌.

-- 두개의 정수를 전달해서 
-- 첫번째 값을 두번째 값으로 나눈 나머지를 구해서 리턴해주는 함수
create or replace function myMod(num1 NUMBER, num2 NUMBER)
    return NUMBER
is
    v_remainder NUMBER :=0; --나눈 나머지를 저장할 변수
    v_mok NUMBER :=0; --나눈 몫을 저장할 변수
begin
    v_mok:=floor(num1/num2); --나눈 몫의 정수부분만 저장(소수점 절사)
    v_remainder:=num1-(num2*v_mok); -- 몫*젯수로 피젯수 빼면 나머지 계산
    return v_remainder;
end;

 

 

 

연습하기

-- 연습문제 1
-- booklist에 도서번호를 전달인수로 전달하여 
-- booklist에 해당 도서 제목을 리턴받는 함수를 제작
create or replace function subjectbynum(v_num NUMBER)
    return varchar2 
is
    v_subject booklist.subject%TYPE; 
begin
    select subject into v_subject from booklist where num=v_num;
    return v_subject;
end;

-- 함수 호출 명령
select subjectbynum(5), subjectbynum(8) from dual;

-- 연습문제 2
-- 위의 함수의 기능 중 전달된 도서번호로 검색된 도서가 없다면 
-- '해당 도서 없음'이라는 문구가 리턴되도록 수정
create or replace function subjectbynum(v_num NUMBER)
    return varchar2
is
    v_cnt NUMBER:=0;
    v_subject booklist.subject%TYPE; 
begin
    select count(*) into v_cnt from booklist where num=v_num; 
    --전달받은 도서번호의 해당하는 도서가 몇개인지 조회
    if v_cnt=0 then 
    --해당 도서의 갯수가 0이라면 '해당 도서 없음'을 출력
        v_subject:='해당 도서 없음';
    else --0이 아니라면 도서제목 조회
        select subject into v_subject from booklist where num=v_num;
    end if;
    return v_subject;
end;

-- select 구문 안에 count(*)를 이용해서 결과가 0이면 해당 도서 없음, 
-- 그렇지 않으면 해당 도서제목이 리턴되게 코딩함
select subjectbynum(5), subjectbynum(20) from dual;

 

매개변수가 없는 함수

CREATE OR REPLACE FUNCTION fn_get_user 
--매개변수가 없는 함수는 괄호 없이 정의하기도 함
    RETURN VARCHAR2
IS
    vs_user_name VARCHAR2(80);
BEGIN
    SELECT USER INTO vs_user_name FROM DUAL;
    RETURN vs_user_name; -- 사용자 이름 변환
END;

-- 매개변수가 없는 함수는 괄호 없이 호출하기도 함
SELECT fn_get_user(), fn_get_user FROM DUAL; 

 

-- 연습문제 3
-- emp 테이블에서 각 부서번호를 전달받아서 
-- 급여의 평균값을 계산하여 리턴하는 함수를 제작
-- 전달된 부서번호의 사원이 없으면 급여평균은 0으로 리턴
CREATE OR REPLACE FUNCTION salAvgDept(p_deptNo NUMBER)
    RETURN NUMBER
IS
    v_avg NUMBER;
    v_cnt NUMBER;
BEGIN
    SELECT count(*) INTO v_cnt FROM emp WHERE deptno=p_deptNo; --전달받은 도서번호의 해당하는 도서가 몇개인지 조회
    IF v_cnt=0 THEN
        v_avg:=0;
    ELSE 
        SELECT AVG(sal) INTO v_avg FROM emp WHERE deptno=p_deptNo;
    END IF;
    RETURN v_avg;
END;

-- 함수 호출
select salAvgDept(10), salAvgDept(20), salAvgDept(30), salAvgDept(40), salAvgDept(50) from dual;