PgSql函数——赋值问题
来源:互联网 发布:游奇网络礼包 编辑:程序博客网 时间:2024/06/05 07:31
-- 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;
-- 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;
0 0
- PgSql函数——赋值问题
- [pgsql]pgsql数据库时间函数
- memset函数赋值问题
- PGSQL 重建索引 函数
- pgsql字符串函数
- pgsql聚集函数
- A——赋值问题‘
- PGSQL下的常用函数
- pgsql函数和操作符
- pgsql 数学函数及操作
- php pgsql的常用函数
- PL/Pgsql 常用字符串函数
- pgsql 时间函数的方法
- java String赋值问题,nul,new String()函数赋值问题
- pgsql
- JS——声明式函数与赋值式函数
- 函数参数赋值问题(arguments)
- 一个js函数中的变量赋值问题
- mx临时修改遥控器码值
- PHP中常用设置
- Jenkins邮件设置
- 苹果与Unicode协会合作推进emoji多样化
- Makefile 的函数-1
- PgSql函数——赋值问题
- secureCRT建了一个串口COM1后,连接上开发板后,可以正确接受和显示串口的输出,但是按键输入无效
- 使用CoreLocation来跟踪用户距离
- 服务器编程入门(10)TCP回射服务器实现 - 并发
- reportviewer 解决按钮tips英文问题
- 游戏公司日记(5)
- Makefile 的变量-2
- Python正则表达式的用法
- Leetcode_subsets