Primary key Generator

These days busy doing graduation design, busy knocking code, early found as the original "boring" of those technical words to knock again is not much significance, because it would be better to write down the time it takes to read it carefully or use the code to do a few practical examples, but it's a good idea. Mainly because too want to be a teacher! Now, do not do so do not write because of the temporary give up the plan to be a teacher, the two days of free time to review the database, turned to a previous written stored procedure "PRIMARY key Generator", SQL Server version of the previous project used, Oracle version of the later learning Oracle , no matter what, have looked at again, knocked again, in the future when the face of the time may be necessary.

SQL Server Edition

Use TEST

If EXISTS (SELECT name from sysobjects-check the system for objects with the same name as the custom stored procedure

WHERE name = ' Getno ' and type = ' P '

DROP PROCEDURE Getno

Go

CREATE PROCEDURE Getno

@precRecord varchar (10),--3 parameters. The 1th is the input parameter (table name), and the 3rd is input

--parameter (used to determine the output format); 2nd is an output parameter (the last generated primary key number)

@primarykey varchar OUTPUT, if the 3rd parameter equals 0 o'clock, primary key = track + period + number

--If the 3rd parameter equals 1 o'clock, the primary key = the word track + number

@sign smallint--If the 3rd parameter equals other times wrong

As

DECLARE @prctmpdate datetime, a temporary variable, to record intermediate variables in stored procedures

@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 frecord= @precRecord)-If the table does not have the same name as parameter 1, insert a new one

Begin

Insert into TBRECNO (Frecord,predate,prenum,word) VALUES (@precRecord, @today, 1,left (@precRecord, 2))

End

Else

Begin

Select @prctmpdate =predate from Tbrecno where frecord= @precRecord

if (DateDiff (day, @today, @prctmpdate) < 0)--Compare if current date < Last date, make

--Last date = Last date and last number is zero

Begin

Update tbrecno Set prenum = ' 1 ', predate = @today where frecord= @precRecord

End

else if (DateDiff (day, @today, @prctmpdate) = 0)--if the current date = Last date, make the last number plus a

Begin

Update Tbrecno Set prenum = Prenum + 1 where frecord= @precRecord

End

else if (DateDiff (day, @today, @prctmpdate) > 0)--if the current date is the last date, the error

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 frecord= @precRecord

Select @tmpprenum =--The last number is not enough four-bit fill four-bit

Case Len (LTrim (RTrim (@tmpprenum))

When 1 Then ' +rtrim (LTrim (@tmpprenum))

When 2 Then ' + RTrim (LTrim (@tmpprenum))

When 3 Then ' 0 ' + rtrim (LTrim (@tmpprenum))

When 4 then RTrim (LTrim (@tmpprenum))

End

--------------------------------------------------------------------------------------------------------------- --

Set @year1 =right (RTrim (str (@prctmpdate))) (2)--Check out year

Select @month1 =

Case Len (LTrim (Month (@prctmpdate)))--Take out the month, if not enough two-bit to fill two-digit

When 1 Then ' 0 ' +ltrim (str (month (@prctmpdate))

When 2 then LTrim (STR (month (@prctmpdate))

End

Select @day1 =--Take out the number of days, if not enough two-bit to fill two-bit

Case Len (LTrim (str (@prctmpdate)))

When 1 Then ' 0 ' +ltrim (str (@prctmpdate))

When 2 then LTrim (str (@prctmpdate))

End

--------------------------------------------------------------------------------------------------------------- -

If @sign =1

Begin--Judging the output type

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

-------------------------------------------------------------------

--Test

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 (' the first two letters of the table name conflict with the existing ones, modify the table name ', 16, 1)

--Delete Tbrecno

--
--select * from Tbrecno
Oracle version

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 the 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 (lastdate) and Mymonth>=extract (month from lastdate)) then

if (myday=extract 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 (' Server time changes, please check the system! ');
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