急——用存储过程解决自增字段的问题

Question

下面是我自己写的：table_maxid表结构：----名称-----数据类型---大小---小数位---是否为空？----默认值
tablename----varchar2----30---------------------------------
maxid----------number----10--------0------------------------
建表后，先插入一条记录：
insert into table_maxid (tablename,maxid) values ('table1',0);存储过程：testproc
 (table_name in varchar2, newmaxid out number)
as
begin
  update Table_MaxID set MaxID = MaxID + 1 where TableName=table_name;
  select maxid into newmaxid from Table_MaxID where TableName=table_name;
end;调用：
execute testproc('table1');出错， 我就不会写了，呵呵，晕菜！！感谢楼上，一定结贴！！！

Answer 1

CREATE  PROCEDURE PROC_AUTOID @IDName char(10),@IDString char(20)  out  as  
begin
   Declare @length char(20),@tempid char(20),@tempkind Char(2),@templength int
               Declare @tempint int
               Declare @yy char(2),@mm char(2),@dd char(2),@tempdate char(6)
   Declare ID_cursor Cursor For Select lastid,idname,idkind,idlength From autoid Where idname=@IDName
               Open ID_cursor
               Fetch Next From ID_cursor into @length,@tempid,@tempkind,@templength
               set @length=rtrim(@length)
               If  @@FETCH_STATUS = 0
                    begin                         if @tempkind='1'
                         begin
                               set @yy=convert(char(2),right(year(getdate()),2))
                               set @mm=convert(char(2),month(getdate()))
                               if (len(@mm)=1)
                                      set @mm='0'+@mm
                               set @dd=convert(char(2),day(getdate()))
                               if (len(@dd)=1)
                                      set @dd='0'+@mm
                               set @tempdate=@yy+@mm+@dd
                               
                               if  @tempdate=left(@length,6)
                                    begin
                                           set @tempint=convert(int, right(rtrim(@length),4))
                                           set @tempint= @tempint+1                               
                                           set @IDString=@tempdate+left('0000',4-len(@tempint))+convert(char,@tempint)
                                    end                             
                              else
                                   begin
                                         set @IDString=@tempdate+'0001'
                                   end
           end                   if @tempkind='2'
                         begin
                               if  @length=null
                                    begin
                                         set @IDString='0001'
                                    end                             
                              else
                                   begin
                                        set @tempint=convert(int,@length)
                                        set @tempint= @tempint+1
                                        set @IDString=left('0000',4-len(@tempint))+convert(char,@tempint)
                                   end
           end
         
                          If  @tempkind='3'
                         begin      
                                  If  (@length=null) or  (isnumeric(@length)=0)
                                       begin
                                                set @IDString='000001'
                                       end
                                  else
                                       begin
                                               set @tempint=convert(int,@length)
                                               set @tempint= @tempint+1
                                               set @IDString=left('000000',6-len(@tempint))+convert(char,@tempint)
                                       end
                         end           end
   close ID_cursor          
   deallocate ID_cursor   
   
   update autoid set lastid=@IDString where idname= @IDName        
    
    select lastid from autoid where idname= @IDName     --客户编号加前缀‘N’
   if @IDName='khbh'
     set  @IDString='N'+@IDStringend
GO
麻烦吧
还得建表autoid 
调用
public String executeProc(String IDName)
{
String returnstr,sqlstr,rsstr;
rs=null;
sqlstr = "{call PROC_AUTOID(?,?)}"; 
try
{
if (conn!=null)
       {
    conn.close();
       }
           conn = DriverManager.getConnection(sConnStr,UseName,PassWord);
   CallableStatement stmt = conn.prepareCall(sqlstr);
   stmt.setString(1,IDName); 
   stmt.registerOutParameter(2,Types.CHAR); 
   rs=stmt.executeQuery();
   rs.next();
   rsstr = rs.getString(1); 
   returnstr = stmt.getString(2);
   rs.close();
   stmt.close();
   conn.close();
}
catch (Exception erproc)
{
  return null;
}
return returnstr;
}

Answer 2

楼上，第一行返回下述错误：行号= 1 列号= 29 错误文本= PLS-00103: 出现符号 "CREATE"在需要下列之一时：  ( ; is with authid as    cluster compress order using compiled wrapped external    deterministic parallel_enable pipelined 这是什么意思啊？

Answer 3

楼上的写法是sql server的t-sql语法，不适用于oracle。
不明白楼主为什么不用sequence，那简单很多啊

Answer 4

先执行＋1操作锁住记录：select col1 from tablename for update;

Answer 5

如果我把存储过程写成这样：存储过程名：testproc内容：
 (table_name in varchar2, newmaxid out number)
as
begin
  update Table_MaxID set MaxID = MaxID + 1 where TableName=table_name;
  select maxid into newmaxid from Table_MaxID where TableName=table_name;
end;比如里面有一条记录，
insert into table_maxid (tablename,maxid) values ('table1',0);
应怎么调用呢？
execute testproc('table1'...);
上面这行，有三个点的地方应怎么写呢？请教！！