初试sql脚本

wxyfj

oracle 9i:

declare
  countNum      NUMBER(10);--原文数量
  recId         varchar2(32);--条目记录id
  usertableId varchar2(32);--档案库表id
  mSQL          varchar2(2000);--定义sql
  cursor MyCur is
    select count(c_recid), c_recid, c_usertable_id
      from s_link  
     group by c_recid, c_usertable_id  order by c_usertable_id;
begin
  open MyCur;
  loop
    fetch MyCur
      into countNum, recId, usertableId;
    exit when MyCur%notfound;
    mSQL := 'update  T_' || substr(usertableId, 1,13) ||
            ' set I_FILECOUNT=' || countNum || ' where id= ' || '''' ||
            recId || '''';
      
     execute immediate mSQL;
     commit;
  end loop;

  EXCEPTION
  WHEN OTHERS THEN
        NULL;--啥也不做
   close MyCur;  
end;


sql-server 2005:

declare @countNum      numeric(10, 0);--原文数量
declare @recId         varchar(32);--条目记录id
declare @usertableId   varchar(32);--档案库表id
declare @mSQL          varchar(2000);--定义sql
declare @err int;
declare MyCur cursor for

select count(c_recid), c_recid, c_usertable_id
      from s_link  
     group by c_recid, c_usertable_id  order by c_usertable_id;

open MyCur
fetch next from MyCur into @countNum,@recId , @usertableId
while @@FETCH_STATUS= 0

begin
select @err = @@error
  if @err = 0
  begin try
    set @mSQL = 'update  T_' + substring(@usertableId, 1,13) +
            ' set I_FILECOUNT=' + convert(varchar(10),@countNum) + ' where id= ' + '''' +
            @recId + ''''
    exec(@mSQL)
  end try
  begin catch
   --不处理
  end catch
  
fetch next from MyCur into @countNum,@recId , @usertableId
end
close MyCur
deallocate MyCur