TIPTOP GP5之使用ORACLE端FUNCTION

来源：互联网发布：国内租车公司排名知乎编辑：程序博客网时间：2024/06/06 01:23

测试FUNCTION如下(function返回一个值)：

CREATE OR REPLACE FUNCTION get_no(v_mmb01 IN mmb_file.mmb01%type,v_mmb02 IN mmb_file.mmb02%type) RETURN varchar2 ISv_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_3END FUNCTION