These days are busy doing graduation design, busy tapping code, early shot, "bored", "bored", is not much more meaningful, because the time they use is carefully watching or re-use code Do a few practical examples should be better, in fact, this is early, then I have known that it is still so dry, mainly because I want to be a teacher! Now, not so don't write because I have given up the teacher's plan. When these two days are free, review the database, turn it to the previous stored procedure "primary key generator", the SQLServer version is before The project is used in the project, and the Oracle version is made up of the Oracle, no matter what, there is a look, knock it once, and maybe it will be used in the future. SQLServer version USE TEST
If exists (Select Name from sysobjects - Check if there is object in the system with custom stored procedures
Where name = 'getno' and type = 'p')
Drop Procedure GetNo
Go
Create Procedure GetNo
@PRecrecRecord varchar (10), --3 parameters. The first is the input parameter (table name); the third is the input-parameter (to determine the output format) ;; the second is the output parameter (final generated primary key number)
@PrimaryKey varchar (12) Output, - When the third parameter is equal to 0, the primary key = track period number - If the third parameter is equal to 1, the primary key = track number
@Sign Smallint - If the third parameter is equal to other time
AS
Declare @prctmpdate datetime, - Temporary variable to record intermediate variables during 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 whereford = @ precRecord) - If there is no name in the table, it is newly inserted.
Begin
INSERT INTO TBRECNO (FRECORD, PREDATE, PRENUM, WORD) VALUES (@ PRecrecord, @ Today, 1, Left (@ PRecrecord, 2))
end
Else
Begin
SELECT @ prctMpdate = predate from tbrecno whereford = @ precRecord
IF (Datediff (Day, @today, @PrctMpdate) <0) - Compare if the current date Update TBRECNO SET PRENUM = '1', PREDATE = @today where frecord = @ precrecord end Else if (Datediff (DAY, @Today, @PrctMpdate) = 0) - If the current date = last date, let the last number plus one Begin Update TBRECNO SET PRENUM = PRENUM 1 WHERE FRECORD = @ precRecord end Else if (Datediff (Day, @today, @PrctMpdate)> 0) - If the current date Begin Raiserror ('The DB Server Date Erreor Check System Date Please!', 16, 1) end end -------------------------------------------------- -------------------------------------------------- ----------- SELECT @ prctMpdate = predate, @ TmpWord = word, @Tmpprenum = prenum from tbrecno whereford = @ precrecord SELECT @TMPPRENUM = - The last number is not enough four points Case Len (Ltrim (@TMPPRENUM)))) When 1 Then '000' RTRIM (Ltrim (@TMPPRenum)) When 2 Then '00' RTRIM (Ltrim (@TMPPRenum)) When 3 Ten '0' RTRIM (Ltrim (@TMPPRenum)) When 4 Then Rtrim (Ltrim (@TMPPRENUM)) end -------------------------------------------------- -------------------------------------------------- ------------- Set @ Year1 = Right (LTRIM (RTRIM ((strick))))))))))))))))))))))). SELECT @ MONTH1 = Case Len (Ltrim (Str (Month (@PrctMpdate))))) - Take out the month, if you are not enough to make up two When 1 TEN '0' LTRIM (Str (Month (@PrctMpdate))) When 2 Then Ltrim (Str (Month (@PrctMpdate))) end SELECT @ day1 = - Take out the number of days, if it is not enough to make two Case Len (LTRIM (Str (DAY (@PRCTMPDATE)))) When 1 TEN '0' LTRIM (Str (DAY (@PrctMpdate)))))) When 2 Then Ltrim (Str (DAY (@prctmpdate)))) end -------------------------------------------------- -------------------------------------------------- ---------------- IF @ Sign = 1 Begin - judgment 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 (r @PRecRecRecord), 2)) - raiserror ('The first two letters of the table name and the existing conflicts, please modify the table name ", 16, 1) - Delete TBRECNO - --Select * from TBRECNO ORACLE Edition 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; beginmyyear: = 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)) THENIF (myday = extract (day from lastdate)) Thenupdate getmyKey set my_lastno = my_la stno 1 where my_tablename = tablename; select my_lastno into tmpint from getmykey where my_tablename = tablename; else if (myday> extract (day from lastdate)) thenupdate 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 change, 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 ), 2, '0') || tmpstr;
