PgSql函数——赋值问题

-- Function: sp_feesauditrecord_insert(character varying, character varying, integer, character varying, numeric, numeric, character varying, integer, character varying)


-- DROP FUNCTION sp_feesauditrecord_insert(character varying, character varying, integer, character varying, numeric, numeric, character varying, integer, character varying);


CREATE OR REPLACE FUNCTION sp_feesauditrecord_insert(p_registrationnumber character varying, p_detailcode character varying, p_violatetype integer, p_audittypecode character varying, p_deductionsamount numeric, p_deductionsnumber numeric, p_deductionswhy character varying, p_auditstate integer, p_auditagent character varying)
  RETURNS integer AS
$BODY$ 
declare
 d_deductionsamount numeric ; 
BEGIN
    if EXISTS(select * from feesauditrecord where detailcode=p_detailcode and registrationnumber=p_registrationnumber) and p_detailcode<>'' then
       d_deductionsamount:=(select sum(deductionsamount)-p_deductionsamount from feesauditrecord where detailcode=p_detailcode and registrationnumber=p_registrationnumber 
     group by deductionsamount);
    end if ; 
   
   if d_deductionsamount<0 then
       p_deductionsamount:=0;
   end if;
  
INSERT INTO feesauditrecord(
            registrationnumber, detailcode, violatetype, audittypecode, deductionsamount, 
            deductionsnumber, deductionswhy ,auditstate,auditdate,auditagent )
    VALUES (p_registrationnumber, p_detailcode, p_violatetype, p_audittypecode, p_deductionsamount, 
            0,p_deductionswhy, p_auditstate , now() ,p_auditagent );
return 1; 
end;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION sp_feesauditrecord_insert(character varying, character varying, integer, character varying, numeric, numeric, character varying, integer, character varying)
  OWNER TO hfzc;