오라클 FUNCTION() 생성 예제

Procedure] UDF oracle function 프로시져 create 예제

POSTED AT 2010/03/08 12:52 | POSTED IN DATABASE/ORACLE SQL

 프로젝트 참여 과정중 예제로 오라클 프로시져를 만들 일이 생겨서 간단히 summery  해 두겠습니다 ^^

/* 예제 테이블 생성*/
create table exam1 (
yoil varchar2(1),
gyosi varchar2(2),
cnt varchar2(2),
gyo_name varchar2(50),
gang_dongsil varchar2(12),
judamdang varchar2(20),
gyo_code varchar2(10),
gangjwa_code varchar2(20))

/* 예제 데이터 집어 넣기 */
/*insert data*/
insert into exam1 values ('1', '13', '6' , '유통기관경영론' , '(059-610)', '' ,'251.563', '');
insert into exam1 values ('1', '14', '6' , '유통기관경영론' , '(059-610)', '' ,'251.563', '');
insert into exam1 values ('1', '15', '6' , '유통기관경영론' , '(059-610)', '' ,'251.563', '');
insert into exam1 values ('1', '16', '6' , '유통기관경영론' , '(059-610)', '' ,'251.563', '');
insert into exam1 values ('1', '17', '6' , '유통기관경영론' , '(059-610)', '' ,'251.563', '');
insert into exam1 values ('1', '18', '6' , '유통기관경영론' , '(059-610)', '' ,'251.563', '');

insert into exam1 values ('1', '23', '6' , '행정법일반이론' , '(017-105)', '' ,'270.721', '');
insert into exam1 values ('1', '24', '6' , '행정법일반이론' , '(017-105)', '' ,'270.721', '');
insert into exam1 values ('1', '25', '6' , '행정법일반이론' , '(017-105)', '' ,'270.721', '');
insert into exam1 values ('1', '26', '6' , '행정법일반이론' , '(017-105)', '' ,'270.721', '');

insert into exam1 values ('2', '16', '6' , '러시아예술과 문화' , '(003-204)', '' ,'100.124', '');
insert into exam1 values ('2', '17', '6' , '러시아예술과 문화' , '(003-204)', '' ,'100.124', '');
insert into exam1 values ('2', '18', '6' , '러시아예술과 문화' , '(003-204)', '' ,'100.124', '');
insert into exam1 values ('2', '19', '6' , '러시아예술과 문화' , '(003-204)', '' ,'100.124', '');
insert into exam1 values ('2', '20', '6' , '러시아예술과 문화' , '(003-204)', '' ,'100.124', '');
insert into exam1 values ('2', '21', '6' , '러시아예술과 문화' , '(003-204)', '' ,'100.124', '');

다음과 같이 예제 테이블과 예제 데이터를 만들었으면, select 를 해보겠습니다.

/* 일반적인 SELECT 문 */
select
    yoil, gyo_name, gang_dongsil, judamdang, gyo_code, gangjwa_code
from
    exam1

다음과 같은 결과 값이 나왔습니다.

그런데 문제는 요일에 따른 교시를 나타내고 싶습니다.

1 | 13, 14, 15, 16, 17 | 유통기관경영론 | (059-610) | .....

이렇게 데이터를 만들고 싶을때!

---

UDF : 사용자 FUNCTION 을 만듭니다. Procedure 라고도 부르는것 같군요

/*user function */
CREATE OR REPLACE FUNCTION GET_ALL_PROJECT( p_gyo_code IN varchar) RETURN VARCHAR2
IS
RESULT_STR VARCHAR2 (200);
BEGIN
  SELECT SUBSTR(MAX( SYS_CONNECT_BY_PATH (gyosi,'|')),2) INTO RESULT_STR
  FROM (
    SELECT gyosi, ROWNUM rnum
    FROM EXAM1
    WHERE exam1.gyo_code = p_gyo_code
  )
  START WITH rnum = 1
  CONNECT BY PRIOR rnum = rnum -1; 
  RETURN RESULT_STR;
END GET_ALL_PROJECT;
/

GET_ALL_PROJECT : 함수 이름
p_gyo_code  :  p_gyo_code : 프로시져 안에서 쓰일 변수 
IN varchar : 파라미터  타입
RESULT_STR : 리턴값
UDF 생성후 쿼리를 다시 한번 날리겠습니다.

select
    yoil, gyo_name, gang_dongsil, judamdang, gyo_code, gangjwa_code, count(gyosi) as gyosi_cnt , GET_ALL_PROJECT(gyo_code)
from
    exam1
group by
    yoil, gyo_name, gang_dongsil, judamdang, gyo_code, gangjwa_code ;

다음과 같은 결과 화면을 얻을 수 있습니다 ^^

어려우면서도 쉬운것 같은 UDF 네요 - - 히힛-