TIPTOP GP5之使用ORACLE端FUNCTION

测试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