数据库语言

在帆软报表中的函数写法:

多个语句查询:

CREATE OR REPLACE FUNCTION "public"."mrp_routing_workcenter_price1"(flagcompany_id int4, flagprice int4)
  RETURNS SETOF "pg_catalog"."record" AS $BODY$
    declare str varchar;    
            declare
                            rec record;
begin
  --首先执行此语句,用于查询是否有新的报工工序产生
  if exists(select lprocedure from mrp_production_report_line a where lstate=2 and not exists(select id from routing_workcenter_price b where a.lprocedure=b.workcenter_proid and a.company_id=b.company_id and a.lemployee=b.user_id)) then
    begin
      insert into routing_workcenter_price(workcenter_proid,company_id,user_id,cost,price)
            select DISTINCT lprocedure,company_id,lemployee,0.0,0.0 from mrp_production_report_line a
                where lstate=2 and not exists(select id from routing_workcenter_price b where a.lprocedure=b.workcenter_proid and a.company_id=b.company_id and a.lemployee=b.user_id);
  end;
  end if;
 
  if flagprice=1 then
 
   for rec in EXECUTE '
 
    select t0.id ,t1.id as cpdm,t2.name  as pm,t4.material,t4.cust_spec,t1.sequence,t1.name as gxm,t1.produce_description,t0.price,t0.cost,t5.login,t6.name,t0.remark from routing_workcenter_price t0  
                    left join mrp_routing_workcenter t1 on t0.workcenter_proid=t1.id
                    left join mrp_routing t2 on t1.routing_id=t2.id
                    left join product_product t3 on t3.name_template=t2.name
                    left join product_template t4 on t4.id=t3.product_tmpl_id
                    left join res_users t5 on t5.id=t0.user_id
                    left join res_partner t6 on t5.partner_id=t6.id
        where t0.company_id='||flagcompany_id||' and t4.active=''t'' and t3.active=''t'' and t0.price>0.0
         order by t2.name,t1.sequence;'loop
RETURN next rec;
  end loop;
return;
  ELSEIF( flagprice=2) then
     for rec in EXECUTE '
            select t0.id ,t1.id as cpdm,t2.name  as pm,t4.material,t4.cust_spec,t1.sequence,t1.name as gxm,t1.produce_description,t0.price,t0.cost,t5.login,t6.name,t0.remark from routing_workcenter_price t0  
                        left join mrp_routing_workcenter t1 on t0.workcenter_proid=t1.id
                        left join mrp_routing t2 on t1.routing_id=t2.id
                        left join product_product t3 on t3.name_template=t2.name
                        left join product_template t4 on t4.id=t3.product_tmpl_id
                        left join res_users t5 on t5.id=t0.user_id
                      left join res_partner t6 on t5.partner_id=t6.id
            where t0.company_id='||flagcompany_id||' and t4.active=''t'' and t3.active=''t''  and t0.price<=0.0  
             order by t2.name,t1.sequence;'loop
RETURN next rec;
  end loop;
return;
  ELSE
     for rec in EXECUTE '
        select t0.id ,t1.id as cpdm,t2.name  as pm,t4.material,t4.cust_spec,t1.sequence,t1.name as gxm,t1.produce_description,t0.price,t0.cost,t5.login,t6.name,t0.remark from routing_workcenter_price t0  
                        left join mrp_routing_workcenter t1 on t0.workcenter_proid=t1.id
                        left join mrp_routing t2 on t1.routing_id=t2.id
                        left join product_product t3 on t3.name_template=t2.name
                        left join product_template t4 on t4.id=t3.product_tmpl_id
                        left join res_users t5 on t5.id=t0.user_id
                      left join res_partner t6 on t5.partner_id=t6.id
            where t0.company_id='||flagcompany_id||' and t4.active=''t'' and t3.active=''t''  
             order by t2.name,t1.sequence;'loop
 RETURN next rec;
   end loop;
   return;
end if;
END
$BODY$
  LANGUAGE 'plpgsql' VOLATILE COST 100
 ROWS 1000
;