oracle 未找到要求的From关键字

[ruhemei]

create or replace procedure ProMax_UserInfo
(
proID in varchar2,
proTableName in varchar2,
proCompanyID in varchar2,
proRemark in varchar2,
probackID in varchar2
)
AS
MaxID number;
BEGIN

select MaxID:=is null(max(MAXID),0) from Max_UserInfo where TableName=proTableName and
CompanyID=proCompanyID;


if MaxID<9;
begin
MaxID:=(MaxID||1);
insert into Max_UserInfo(id,CompanyID,TableName,MAXID,Remark,AddTime) values
(proID,proCompanyID,proTableName,MaxID,proRemark,todate());
probackID:=proCompanyID||'0'||MaxID;
end;
else if MaxID<99;
begin
MaxID:=(MaxID||1);
insert into Max_UserInfo(id,CompanyID,TableName,MAXID,Remark,AddTime) values
(proID,proCompanyID,proTableName,MaxID,proRemark,todate());
probackID:=proCompanyID||MaxID;
end;
if MaxID:>=99;
backID:=-1;
select probackID as backID;
END;


为什么说未找到要求的From关键字？？？

[Phoenix_99]

select MaxID:=is null(max(MAXID),0) from Max_UserInfo where TableName=proTableName and
CompanyID=proCompanyID;
oracle有在select中 :=的语法？

[真的不知道取什么名]

--select MaxID:=is null(max(MAXID),0) 不知道你这个是什么语法
--你应该是这个意思：如果max(MAXID)为null的话 让它为0吧，在oracle里用nvl
select nvl(max(MAXID),0) from Max_UserInfo where TableName=proTableName and  
CompanyID=proCompanyID;

[gelyon]

select nvl(max(MAXID),0) INTO MaxID from Max_UserInfo where TableName=proTableName and
CompanyID=proCompanyID;

还有最后一句：select probackID as backID; 你这句的from呢？？？？

[真的不知道取什么名]

--完整的应该是这样的
select nvl(max(MAXID),0)  into v_maxid from Max_UserInfo where TableName=proTableName and  
CompanyID=proCompanyID;
--建议你不要取和字段一样的变量名

[gelyon]

还有Oracle判断格式
if ...then ...elsif...then...else...end if;

if MaxID<9 then
....
....
end if;

[真的不知道取什么名]

create or replace procedure ProMax_UserInfo
(
proID in varchar2,
proTableName in varchar2,
proCompanyID in varchar2,
proRemark in varchar2,
probackID in varchar2  
)
AS
v_maxid number;
BEGIN

select nvl(max(MAXID),0) into v_maxid 
from Max_UserInfo 
where TableName=proTableName 
    and  CompanyID=proCompanyID;

if MaxID<9 then
  MaxID:=(MaxID||1);
  insert into Max_UserInfo(id,CompanyID,TableName,MAXID,Remark,AddTime) 
  values
  (proID,proCompanyID,proTableName,MaxID,proRemark,todate());
 
  probackID:=proCompanyID||'0'||MaxID;

  elsif MaxID<99 then
    MaxID:=(MaxID||1);
    insert into Max_UserInfo(id,CompanyID,TableName,MAXID,Remark,AddTime) 
    values
    (proID,proCompanyID,proTableName,MaxID,proRemark,todate());
    probackID:=proCompanyID||MaxID;

    else
    backID:=-1;

end if;
dbms_output.put_line(backID);

END;

[真的不知道取什么名]

[Quote=引用 6 楼 zhuomingwang 的回复:]
SQL code

create or replace procedure ProMax_UserInfo
(
proID in varchar2,
proTableName in varchar2,
proCompanyID in varchar2,
proRemark in varchar2,
probackID in varchar2
)
AS
v_maxid nu……
[/Quote]
上面的忘了修改变量名了

create or replace procedure ProMax_UserInfo
(
proID in varchar2,
proTableName in varchar2,
proCompanyID in varchar2,
proRemark in varchar2,
probackID in varchar2  
)
AS
v_maxid number;
BEGIN

select nvl(max(MAXID),0) into v_maxid 
from Max_UserInfo 
where TableName=proTableName 
    and  CompanyID=proCompanyID;

if v_maxid<9 then
  v_maxid:=(v_maxid||1);
  insert into Max_UserInfo(id,CompanyID,TableName,MAXID,Remark,AddTime) 
  values
  (proID,proCompanyID,proTableName,v_maxid,proRemark,todate());
 
  probackID:=proCompanyID||'0'||v_maxid;

  elsif v_maxid<99 then
    v_maxid:=(v_maxid||1);
    insert into Max_UserInfo(id,CompanyID,TableName,MAXID,Remark,AddTime) 
    values
    (proID,proCompanyID,proTableName,v_maxid,proRemark,todate());
    probackID:=proCompanyID||v_maxid;

    else
    backID:=-1;

end if;
dbms_output.put_line(backID);

END;

[gelyon]

我始终没有看出楼主过程中哪里用到了backID，哪里定义了这个变量？？？干嘛啊要？？？

[真的不知道取什么名]

[Quote=引用 8 楼 gelyon 的回复:]
我始终没有看出楼主过程中哪里用到了backID，哪里定义了这个变量？？？干嘛啊要？？？
[/Quote]呵呵，是哦 我也没仔细看，意思到位就行了
其他的楼主自己完成

[心中的彩虹]

[Quote=引用楼主 ruhemei 的回复:]
create or replace procedure ProMax_UserInfo
(
proID in varchar2,
proTableName in varchar2,
proCompanyID in varchar2,
proRemark in varchar2,
probackID in varchar2
)
AS
MaxID number;
BEGIN

……
[/Quote]

--改了多处 可能还有问题 
create or replace procedure ProMax_UserInfo
(
proID in varchar2,
proTableName in varchar2,
proCompanyID in varchar2,
proRemark in varchar2,
probackID in out varchar2  
)
AS
MaxID number;
BEGIN

select nvl(max(MAXID),0) into MaxID from Max_UserInfo where TableName=proTableName and  CompanyID=proCompanyID;

if MaxID<9;
MaxID:=MaxID+1;
insert into Max_UserInfo(id,CompanyID,TableName,MAXID,Remark,AddTime) values
(proID,proCompanyID,proTableName,MaxID,proRemark,todate());
probackID:=proCompanyID||'0'||MaxID;

elsif MaxID<99;

MaxID:=MaxID+1;
insert into Max_UserInfo(id,CompanyID,TableName,MAXID,Remark,AddTime) values
(proID,proCompanyID,proTableName,MaxID,proRemark,todate());
probackID:=proCompanyID||MaxID;
elsif MaxID:>=99;
backID:=-1;
probackID:=backID;
END if;
end;

[ruhemei]

我是要把SQL server 的存储过程改成oracle的存储过程
今天刚开始弄
还不太明白
多多指教
O(∩_∩)O谢谢

[ruhemei]

弄出来了
O(∩_∩)O谢谢各位的帮忙

[SSPLSQL]

[Quote=引用 2 楼 zhuomingwang 的回复:]

SQL code

--select MaxID:=is null(max(MAXID),0) 不知道你这个是什么语法
--你应该是这个意思：如果max(MAXID)为null的话 让它为0吧，在oracle里用nvl
select nvl(max(MAXID),0) from Max_UserInfo where TableName=proTableName and
CompanyID=……
[/Quote]


2楼正解