主键生成器_sqlserver

这些天忙着做毕业设计，忙着敲代码，早发现像原来那样“无聊”的把那些技术文字再敲一遍是没有多大意义的，因为写出它们所用的时间要是仔细在看或者再用代码做几个实际的例子应该更好吧，其实这是早知道的，那以前知道还那么干，主要是因为太想做老师了吧！现在呢，不那么干不写了是因为暂时放弃做老师的打算吧，这两天空闲的时候复习数据库，翻到了以前的所写的一个存储过程“主键生成器”，sqlserver版本的是在以前项目中用过的，oracle版本的是后面学习Oracle时补上的，不管怎么样，有看了一遍，敲了一遍，将来面视的时候也许用得着吧。

SQLServer版

USE TEST

http://www.gaodaima.com/34906.html主键生成器_sqlserver

IF EXISTS (SELECT name FROM sysobjects    –检查系统中是否 有与自定义存储过程同名的对象

         WHERE name = ‘getNo’ AND type = ‘P’)

   DROP PROCEDURE getNo

GO

  CREATE PROCEDURE getNo 

                  @precRecord varchar(10),    –3个参数。第1个 为输入参数（表名）；第3个 为输入

          –参数（用以判断输出格式）；； 第2个 为输出参数 （最后生成的主键编号）

                  @primarykey varchar(12) output ,           –若第3个参数等于0时，主键= 字轨+时期+编号 

                          — 若第3个参数等于1时，主键=字轨+编号

                  @sign  smallint                                  —         若第3个参数等于其他时 报错

AS

    declare @prctmpdate datetime,                        –临时变量，用以记录存储过程中的中间变量

        @today datetime,

        @tmpprenum   varchar(4),

        @tmpword   varchar(2),

        @year1  varchar(2),

        @day1   varchar(2),

        @month1 varchar(2) ————————————————————————————————————

set @today = getdate()

if not exists(select fRecord from tbrecno where [email protected])   –若表中没有与参数1同名的则新插入一条

   begin

  insert into tbrecno  (fRecord,Predate,Prenum,Word) values(@precRecord,@today,1,left(@precRecord,2))

   end

else

   begin

       select @prctmpdate=Predate from tbrecno where [email protected]

       if ( datediff (day, @today , @prctmpdate ) <0 )   –比较    若当前日期<上次日期  ，令  

                   –上次日期=上次日期 且 上次编号置为零

           begin

             update tbrecno  set Prenum = ‘1’ ,predate = @today where [email protected]

           end 

       else if (datediff(day,  @today , @prctmpdate ) = 0 )  –若当前日期=上次日期  ，令上次编号加一

           begin

             update tbrecno set Prenum = Prenum + 1 where [email protected]     

           end

       else if ( datediff (day, @today , @prctmpdate ) > 0 )               –若当前日期〈上次日期  ，报错

           begin

             raiserror (‘the db server date erreor  check system date please!’, 16, 1)

           end

  end

—————————————————————————————————————

  select  @prctmpdate=Predate,@tmpword=Word, @tmpprenum=prenum from tbrecno where [email protected]

  select @tmpprenum=                                                                –上次编号不够四位的补够四位

              case len(ltrim(rtrim(@tmpprenum)))

               when 1 then ‘000’+rtrim(ltrim(@tmpprenum))

                when 2 then ’00’+ rtrim(ltrim(@tmpprenum))

                when 3 then ‘0’+  rtrim(ltrim(@tmpprenum))

                when 4 then       rtrim(ltrim(@tmpprenum))

              end

—————————————————————————————————————–

set @year1 =right(ltrim(rtrim((str(year(@prctmpdate ))))),2)                      –取出年份

select @month1=

               case len (ltrim(str(month(@prctmpdate))))                   –取出月份，若不够两位的补够两位

                 when 1 then ‘0’+ltrim(str(month(@prctmpdate )))

                 when 2 then     ltrim(str(month(@prctmpdate )))

               end

select @day1=                                                                        –取出天数，若不够两位的补够两位

               case len (ltrim(str(day(@prctmpdate))))

                 when 1 then ‘0’+ltrim(str(day(@prctmpdate )))

                 when 2 then  ltrim(str(day(@prctmpdate )))

               end

—————————————————————————————————————-

 if @sign=1

    begin                                                                             –判断输出类型

       set  @primarykey =rtrim(ltrim(@tmpword))+ @tmpprenum

    end

 else if   @sign=0

    begin

        set @primarykey = @tmpword+ @year1 + @month1 + @day1 + @tmpprenum

    end

 else

    begin

      raiserror (‘parameter error’, 16, 1)  

    end

————————————————————————–

GO

——————————————————————-

—                                    测试

declare @mybillno varchar(12) 

 exec getNo ‘pG’, @mybillno output,0

  select @mybillno

—  IF EXISTS (select * from Tbrecno where word = left(ltrim(rtrim( @precRecord )),2))

      —    raiserror (‘表名的前两个字母与已有的发生冲突 请修改表名’, 16, 1)

— delete tbrecno

—
–select * from tbrecno
Oracle版

create or replace procedure getmykeyno(
sign varchar2,
tablename varchar2,
outkey out varchar2
)
is

— sign getmykey.my_ziguei %type; 
— tablename getmykey.my_tablename %type; 
–outkey varchar(20);

lastdate getmykey.my_lastdate%type;
tmpint getmykey.my_lastno %type;
nowdate date;
tmpStr varchar(4);
tmpsign varchar(2);

myyear int;
mymonth int;
myday int;

tmpcount int;

begin
myyear := extract(year from sysdate);
mymonth := extract(month from sysdate);
myday := extract(day from sysdate);

nowdate:=sysdate;
–tablename:=’aaaa’;
–sign:=’ad’;

select count(*) into tmpcount from getmykey where my_tablename = tablename;
if tmpcount=0 then

   begin
      insert into getmyke(my_tablename,my_ziguei,my_lastno,my_lastdate) values          (tablename,sign,1,nowdate) ;tmpInt := 1;
end;
else 
select my_lastdate into lastdate from getmykey where my_tablename=tablename ;

if (myyear>=extract(year from lastdate) and mymonth>=extract(month from lastdate)) then

if(myday=extract(day from lastdate)) then

update getmykey set my_lastno = my_lastno + 1 where my_tablename=tablename; 
select my_lastno into tmpint from getmykey where my_tablename=tablename;

else 
if (myday > extract(day from lastdate)) then

update getmykey set my_lastno = 1 where my_tablename=tablename ;
update getmykey set my_lastdate = nowdate where my_tablename=tablename;
tmpInt := 1 ;
else 
dbms_output.put_line(‘服务器的时间改变，请检查系统!’);
end if; 

end if; 
end if; 

end if;
tmpStr:=lpad(to_char(tmpint),4,’0′);

— dbms_output.put_line(tmpstr);

outkey := substr(to_char(myyear),3,2) || lpad(to_char(mymonth),2,’0′) || lpad(to_char(myday),2,’0′) || tmpStr;
select my_ziguei into tmpsign from getmykey where my_tablename=tablename;

outkey:=tmpsign ||outkey;
–dbms_output.put_line(outkey);

end;

欢迎大家阅读《主键生成器_sqlserver》,跪求各位点评,若觉得好的话请收藏本文，by