测试FUNCTION如下(function返回一个值):
CREATE OR REPLACE FUNCTION get_no(v_mmb01 IN mmb_file.mmb01%type,v_mmb02 IN mmb_file.mmb02%type) RETURN varchar2
IS
v_mmb02_prior mmb_file.mmb02%type;
v_ta_mmb01 mmb_file.ta_mmb01%type;
v_mmb141 mmb_file.mmb141%type;
v_ta_mma01 mma_file.ta_mma01%type;
BEGIN
SELECT ta_mma01 INTO v_ta_mma01 FROM mma_file
WHERE mma01=v_mmb01;
SELECT ta_mmb01 INTO v_ta_mmb01 FROM mmb_file
WHERE mmb01=v_mmb01 AND mmb02=v_mmb02;
SELECT max(nvl(ecm03,0)) INTO v_mmb02_prior FROM ecm_file
WHERE ecm01=v_ta_mma01 AND ta_ecm01=v_ta_mmb01 AND ecm08<v_mmb02;
SELECT mmb141 INTO v_mmb141 FROM mmb_file
WHERE mmb01=v_mmb01 AND mmb02=v_mmb02_prior;
RETURN v_mmb141;
EXCEPTION
WHEN NO_DATA_FOUND THEN RETURN '';
END;
Genero中使用此函数有两种方法:
FUNCTION abcd()
define l_mmb141_1 varchar(16)
define l_mmb141_2 varchar(16)
define l_mmb141_3 varchar(16)
define l_sql string
##1.普通字段
select mmb141 into l_mmb141_1 from acm111208.mmb_file,acm111208.mma_file
where mmb01=mma01 and ta_mma01='M11M-11110014' and mmb02=60
display 'l_mmb141_1=',l_mmb141_1
##2.静态sql
select get_no(mma01,mmb02) into l_mmb141_2 from mmb_file,mma_file
where mmb01=mma01 and ta_mma01='M11M-11110014' and mmb02=60
display 'l_mmb141_2=',l_mmb141_2
##3.动态sql
let l_sql = "select get_no(mma01,mmb02) from",
" mmb_file,mma_file",
" where mmb01=mma01 and ta_mma01='M11M-11110014' and mmb02=60"
PREPARE s1 FROM l_sql
EXECUTE s1 INTO l_mmb141_3
display 'l_mmb141_3=',l_mmb141_3
END FUNCTION