ibatis/mybatis的xml中写类似函数的sql

有些时候，我们需要在ibatis或者mybatis(本质区别不大)执行一些类似函数的代码串，因为我现在所在的公司是sass模式，种种原因，添加函数不方便，所以想到了下面的这种方式，记录一下（支持传参数）。

do language plpgsql <![CDATA[$$$$]]>
  	DECLARE
     c_record record;
     c_temp record;
     exesql varchar;
     c_code varchar;
     BEGIN
      update bas_cm_district set id_full_path='',code_full_path='',name_full_path='' WHERE id = #id:BIGINT#;
      update bas_cm_district set id_full_path=id,code_full_path=code,name_full_path=name where id='1';
      for c_record in
       select id,pid,n from bas_cm_district_r where n>=0 and id = #id:BIGINT# order by n desc
      loop
       select id::varchar,code,name INTO c_temp from bas_cm_district where id= c_record.pid;
       if c_temp.code = '' or c_temp.code is null THEN
          update bas_cm_district set id_full_path=(CASE id_full_path WHEN '' then c_temp.id else concat(id_full_path,',',c_temp.id) END),name_full_path=concat(name_full_path,'/',c_temp.name) where id = c_record.id;
       else
          update bas_cm_district set id_full_path=(CASE id_full_path WHEN '' then c_temp.id else concat(id_full_path,',',c_temp.id) END),code_full_path=concat(code_full_path,'/',c_temp.code),name_full_path=concat(name_full_path,'/',c_temp.name) where id = c_record.id;
       end if;
      end loop;
      END;
    <![CDATA[$$$$]]>

 如果一个企业就是一个库，那么加函数比较好，然后在xml写法改为

<update id="refreshAppointNodeFullPath" parameterClass="java.lang.Long">
  	select p_update_dist_full_path()
  </update>

附上函数写法如下：

CREATE OR REPLACE FUNCTION "public"."p_update_dist_full_path"()
  RETURNS "pg_catalog"."void" AS $BODY$ 
DECLARE
     c_record record;
     c_temp record;
     exesql varchar;
     c_code varchar;
     BEGIN
      update bas_cm_district set id_full_path='',code_full_path='',name_full_path='';
      update bas_cm_district set id_full_path=id,code_full_path=code,name_full_path=name where id='1';
      for c_record in
       select id,pid,n from bas_cm_district_r where n>=0 and id !=1 order by n desc 
      loop
       select id::varchar,code,name INTO c_temp from bas_cm_district where id= c_record.pid;
       if c_temp.code = '' or c_temp.code is null THEN
          update bas_cm_district set id_full_path=(CASE id_full_path WHEN '' then c_temp.id else concat(id_full_path,',',c_temp.id) END),name_full_path=concat(name_full_path,'/',c_temp.name) where id = c_record.id;
       else
          update bas_cm_district set id_full_path=(CASE id_full_path WHEN '' then c_temp.id else concat(id_full_path,',',c_temp.id) END),code_full_path=concat(code_full_path,'/',c_temp.code),name_full_path=concat(name_full_path,'/',c_temp.name) where id = c_record.id;
        end if;
      end loop;
      END; 
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100