Oracle - Function 예제1

[ 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