create or replace procedure pro_sbgl_jsda_gjjh
(
in_gjid in varchar2, --工具ID
in_sl in varchar2, --数量
in_jg in varchar2, --价格
in_zje in varchar2, --总金额
in_sqr in varchar2 --申请人
in_zzc in varchar2, --制造厂
in_bz in varchar2, --备注
in_fjnc in varchar2 --附件名称
)
is
p_gjid type_sbgl_varchar;
p_id type_sbgl_varchar;
p_sql varchar2(2000);
P_currentDate varchar2(20);
begin
--获取当前时间
p_currentDate:=to_cahr(sysdate,'nnnn-yy-rr ss:ff:mm');
--查询工具ID是否存在的sql
p_sql :='select gjid,id from sbgl_jsda_gjjh where id = '''||in_gjid||'''';
--执行sql查询，把工具id，id存入 p_gjid，p_id中
execute immediate p_sql bulk collect into p_gjid,p_id;
--判断结果是否为空
if p_gjid.count = null then
--将工具信息,插入工具计划表中
insert into sbgl_jsda_gjjh (id,gjid,sl,jg,zje,sqr,zzc,bz,fjnc,gxsj,cjsj)
values (sys_guid(),in_gjid,in_sl,in_jg,in_zje,in_sqr,in_zzc,in_bz,in_fjnc,p_currentDate,p_currentDate)
commit;
else
update sbgl_jsda_gjjh set sl = sl + in_sl where id= p_id(1);
commit;
end if;
end pro_sbgl_jsda_gjjh