농부지기

제목 : Oracle-Function. Table function. Table return

--1. sql문장에서 테이블 return function 사용하기 SELECT * FROM TABLE(FN_RETIRE_EMP('20190617', '20190623')); --2. 테이블 객체 생성 CREATE OR REPLACE TYPE "TABLE_RETIRE_EMP" AS TABLE OF ROW_RETIRE_EMP --3. 테이블 레코드 객체 생성 CREATE OR REPLACE TYPE "ROW_RETIRE_EMP" AS OBJECT ( EMP_NO VARCHAR2(8) ,EMP_NAME VARCHAR2(10) ,RETIRE_DATE VARCHAR2(20) ) --4. 테이블 객체를 return하는 function 생성 CREATE OR REPLACE FUNCTION FNC.FN_RETIRE_EMP ( P_STRT_DT VARCHAR2 ,P_END_DT VARCHAR2 ) RETURN TABLE_RETIRE_EMP IS -- *------------------------------------------------------------------------------------------------ -- * 퇴사 직원 조회 -- *------------------------------------------------------------------------------------------------ -- * OBJECT NAME : TABLE_RETIRE_EMP -- * OBJECT TYPE : FUNCTION -- * CREATE DATE : 2019-06-04 -- * CREATE NAME : farmerkyh -- * COMMENT : 퇴사 직원 조회 -- *------------------------------------------------------------------------------------------------ -- 시스템 변수 V_TABLE TABLE_RETIRE_EMP; V_ROW ROW_RETIRE_EMP; LI_SEQ NUMBER(7); -- 사용자 변수 V_RET_VALUE VARCHAR2(20); -- 함수리턴 변수 ------------------------------------------------------------------------------------------------- -- CURSOR 영역 ------------------------------------------------------------------------------------------------- CURSOR CUR_EMP IS SELECT A.EMP_NO , A.EMP_NAME , A.RETIRE_DATE FROM T_EMP A WHERE A.RETIRE_DATE BETWEEN P_STRT_DT AND P_END_DT; ------------------------------------------------------------------------------------------------- -- MAIN 영역 시작 ------------------------------------------------------------------------------------------------- BEGIN ------------------------------------------------------------------------------------------------- -- 변수 설정 (단가적용일/검색조건) ------------------------------------------------------------------------------------------------- V_EMP_NAME := P_EMP_NAME; V_RETIRE_DATE := P_RETIRE_DATE; ------------------------------------------------------------------------------------------------- -- 커서처리 ------------------------------------------------------------------------------------------------- -- ROW_RETIRE_EMP 초기화 V_ROW := ROW_RETIRE_EMP('','',''); LI_SEQ := 0; FOR C1 IN CUR_EMP LOOP V_ROW.EMP_NO := C1.EMP_NO; --1 V_ROW.EMP_NAME := C1.EMP_NAME; --2 V_ROW.RETIRE_DATE := C1.RETIRE_DATE; --3 LI_SEQ := LI_SEQ + 1; IF LI_SEQ = 1 THEN V_TABLE := TABLE_RETIRE_EMP( V_ROW ); ELSE V_TABLE.EXTEND; END IF; V_TABLE(LI_SEQ):= V_ROW; V_ROW := ROW_RETIRE_EMP('','',''); END LOOP; RETURN V_TABLE; END;

[ Oracle - Function 공통.두 날자의 년수를 구함 ]

CREATE OR REPLACE FUNCTION OF_COMM_YEAR_CALU ( aSta_ymd VARCHAR2, --시작일자 aEnd_ymd VARCHAR2, --종료일자 aDay_Add_yn VARCHAR2, --처음일자 포함여부 aRound_gubn VARCHAR2 --반올림 여부 (1:올림, 2:반올림, 3:버림) ) RETURN VARCHAR2 IS /**************************************************************************************** PROCEDURE NAME : OF_COMM_YEAR_CALU DESCRIPTION : 두 날자의 년수를 구함 AUTHOR : FARMER-KYH CREATE DATE : 2008.01.17 HISTORY : *****************************************************************************************/ nMonth NUMBER(10,5) := ''; --개월수 BEGIN dbms_output.enable; --처음일자 or 종료일자 포함여부 IF aDay_Add_yn = 'Y' THEN nMonth := months_between(to_char(to_date(aEnd_ymd) + 1, 'yyyymmdd'), aSta_ymd) / 12; ELSE nMonth := months_between(to_char(to_date(aEnd_ymd), 'yyyymmdd'), aSta_ymd) / 12; END IF; --반올림여부 --올림 IF aRound_gubn = '1' THEN nMonth := ROUND(nMonth + 0.4, 0) ; --반올림 ELSIF aRound_gubn = '2' THEN nMonth := ROUND(nMonth, 0) ; --버림 ELSE nMonth := TRUNC(nMonth, 0) ; END IF; RETURN nMonth; EXCEPTION WHEN OTHERS THEN RETURN 0; END;

[ Oracle - Function 공통.두 날자의 개월수를 구함 ]

[ Oracle - Function 공통.당월 말 구하기 ]

[ Oracle - Function 공통.두 날자 차이 구하기 ]

[ Oracle - Function 월 더하기.빼기 ]

[ Oracle - Function 예제2 ]

[ Oracle - Function 예제1 ]

Stored Function Sample 2 /* SAL601T에서 거래처별 금월판매액 */ CREATE OR REPLACE FUNCTION DF_SAL601T_AMT_G (df_vndno in sal601t.vndno%type, df_yy in sal601t.yy%type, df_mm in sal109t.mm%type ) RETURN number is out_num number(12); BEGIN IF df_mm = '01' THEN SELECT m01_gum + m01_vat INTO out_num FROM sal601t WHERE vndno = df_vndno AND yy = df_yy ; ELSIF df_mm = '02' THEN SELECT m02_gum + m02_vat INTO out_num FROM sal601t WHERE vndno = df_vndno AND yy = df_yy ; ELSIF df_mm = '03' THEN SELECT m03_gum + m03_vat INTO out_num FROM sal601t WHERE vndno = df_vndno AND yy = df_yy ; ELSIF df_mm = '04' THEN SELECTm04_gum + m04_vat INTO out_num FROM sal601t WHERE vndno = df_vndno AND yy = df_yy ; ELSIF df_mm = '05' THEN SELECT m05_gum + m05_vat INTO out_num FROM sal601t WHERE vndno = df_vndno AND yy = df_yy ; ELSIF df_mm = '06' THEN SELECT m06_gum + m06_vat INTO out_num FROM sal601t WHERE vndno = df_vndno AND yy = df_yy ; ELSIF df_mm = '07' THEN SELECT m07_gum + m07_vat INTO out_num FROM sal601t WHERE vndno = df_vndno AND yy = df_yy ; ELSIF df_mm = '08' THEN SELECT m08_gum + m08_vat INTO out_num FROM sal601t WHERE vndno = df_vndno AND yy = df_yy ; ELSIF df_mm = '09' THEN SELECT m09_gum + m09_vat INTO out_num FROM sal601t WHERE vndno = df_vndno AND yy = df_yy ; ELSIF df_mm = '10' THEN SELECT m10_gum + m10_vat INTO out_num FROM sal601t WHERE vndno = df_vndno AND yy = df_yy ; ELSIF df_mm = '11' THEN SELECT m11_gum + m11_vat INTO out_num FROM sal601t WHERE vndno = df_vndno AND yy = df_yy ; ELSIF df_mm = '12' THEN SELECT m12_gum + m12_vat INTO out_num FROM sal601t WHERE vndno = df_vndno AND yy = df_yy ; END IF ; RETURN(out_num); EXCEPTION WHEN NO_DATA_FOUND THEN RETURN (' '); END; / 발주처 : http://myhome.netsgo.com/mino70/main_03_oracle03.htm