create or replace procedure endsoftwareprocedure
(emac in out varchar2(64),
eip in out varchar2(64),
eprovince in out varchar2(64),
ecity in out varchar2(32),
eusername in out varchar2(64),
eversion in out varchar2(32))
consumid number(20); useid number(20);
select count(a.id) into consumid from bm_consumer a where a.mac=emac; if consumid >0
then
update bm_consumer a set a.ip =eip,a.province=eprovince,a.city = ecity,a.username=eusername;
else
select bm_consumer_sequences.nextval into consumid from dual; insert into bm_consumer (id,mac,ip,province,city,username)
values(consumid,emac,eip,eprovince,ecity,eusername);
insert into bm_userinfo(id,consumerid,version,starttime,totaltime,islogin)
values(bm_userinfo_sequences.nextval,consumid,eversion,TO_DATE(sysdate,'yyyy-mm-dd hh24:mi:ss'),0,1);
end if; update bm_userinfo set lasttime=TO_DATE(sysdate,'yyyy-mm-dd hh24:mi:ss'),
totaltime=round(to_number(TO_DATE(sysdate,'yyyy-mm-dd hh24:mi:ss')-bm_userinfo.starttime)*1440) ,
islogin=0;
commit; exceptionwhen others thenrollback;end endsoftwareprocedure;
is
begin
……
end;
consumid number(20);
useid number(20);
声明这两个变量的时候老是出问题
1.传递的参数的类型不需要带长度
2.缺少begin关键字
3.else中最后一条更新语句有问题。
create or replace procedure endsoftwareprocedure
(emac in varchar2,eip in varchar2, eprovince in varchar2,ecity in varchar2,eusername in varchar2,eversion in varchar2)
as
consumid out number;
useid out number;
begin
select count(a.id) into consumid from bm_consumer a where a.mac=emac;
if consumid >0
then
update bm_consumer a set a.ip =eip,a.province=eprovince,a.city = ecity,a.username=eusername;
else
select bm_consumer_sequences.nextval into consumid from dual;
insert into bm_consumer (id,mac,ip,province,city,username)
values(consumid,emac,eip,eprovince,ecity,eusername);
insert into bm_userinfo(id,consumerid,version,starttime,totaltime,islogin)
values(bm_userinfo_sequences.nextval,consumid,eversion,TO_DATE(sysdate,'yyyy-mm-dd hh24:mi:ss'),0,1);
end if;
update bm_userinfo set lasttime=TO_DATE(sysdate,'yyyy-mm-dd hh24:mi:ss'),
totaltime=round(to_number(TO_DATE(sysdate,'yyyy-mm-dd hh24:mi:ss')-bm_userinfo.starttime)*1440) ,
islogin=0;
commit;
exception
when others then
rollback;
end endsoftwareprocedure;当我把
consumid out number;
useid out number;
这两个参数放在和传入的参数一起,就能编译通过,这样写就不通过。怎么声明变量才对啊?
(emac in varchar2,eip in varchar2, eprovince in varchar2,ecity in varchar2,eusername in varchar2,eversion in varchar2)
as
consumid number;
useid number;
begin.....这样就是声明变量