-
[PLSQL] functionDataBase/PLSQL 2022. 9. 9. 17:02
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;
'DataBase > PLSQL' 카테고리의 다른 글
[PLSQL] cursor (0) 2022.09.09 [PLSQL] procedure (1) 2022.09.09 [PLSQL] if, case, loop (0) 2022.09.09 [PLSQL] block, 출력, 변수, 자료형, SQL (0) 2022.09.09 [PLSQL] Procedural Language for SQL 설치 (0) 2022.09.09