Oracle and SQL Server use functions to generate date plus serial number

Recently in Oracle-related projects, just contact with Oracle, and SQL Server syntax is still different

Sql server:

Example: fx+ Current Month day +00001

As shown in the strength of the serial number

Principle:

First ' FX ' is fixed, getting the current month and date method in SQL Server is:

1. Years: Year (GETDATE ())

2. Monthly: Month (GETDATE ())

3. Days: Day (GETDATE ())

The second most important is the serial number automatically add 1, the main idea is to get to the current table in the largest number of Fxnum (the last 6 bits of the largest number), and use the right function to obtain the last 6 bits and add 1.

The SQL function code is as follows:

Create function [dbo].[F_getfxnum]()returns varchar( the) asbeginDeclare @FxNum varchar( the)Declare @time varchar(8)Set @time=CONVERT(varchar, Year(GETDATE()))+right('xx'+CONVERT(varchar,Month(getdate())),2)+CONVERT(varchar, Day(GETDATE()))--take the current month daySelect @FxNum='FX'+@time+right(100000+ISNULL( Right(MAX(Fxnum),5),0)+1,5)--Gets the current table maximum value and adds 1 fromPx_fxrecordreturn @FxNumEndALTER TABLEPx_fxrecordADD DEFAULT([dbo].[F_getfxnum]()) forFxnum--bind the function to Fxnum as the default value for the field

Parameter description:

Right (Str,len) -The field returns the rightmost Len character string str
Max () -the largest value in the field
IsNull () -Determines whether the content is empty

convert () -format conversion

Oracle:

Oracle is simpler than the SQL syntax to refine a little, in fact, the same way, the encoding mode with ' FX ' after the beginning of the 8-month date plus 8-digit serial number, in the Table Query day maximum plus 1, if not, then from the system time the smallest start (fx+ current Month day +000001), has been added 1.

The right function in SQL is replaced with SUBSTR in Oracle

The Oracle function code is as follows:

Create or Replace functionF_getfxnumreturnnvarchar2 asfxnum nvarchar2 ( -);beginSELECT 'FX'||(NVL (MAX(SUBSTR (Fxnum,3, -), To_char (Sysdate,'YYYYMMDD')||'000000')+1) intoFxnum fromPx_fxrecordWHERESUBSTR (Fxnum,3,8)=To_char (Sysdate,'YYYYMMDD') ;returnFxnum;End;

Parameter description:

1.substr (str, intercept start position, len) //Returns the intercepted Word, right (Str,len) returns the Len string starting from the rightmost

2.to_char (sysdate, ' YYYYMMDD ') //Return to current month day

3.NVL () // NVL (STR1,STR2) If the first parameter of Oracle is empty then the value of the second parameter is displayed, and if the value of the first parameter is not NULL, the first parameter is displayed.

Summarize

1.oracle and SQL Server are similar in principle, based on SQL development, except that the syntax is implemented differently.

2. Of course, the implementation of the serial number is more than the function of this way, you can also use stored procedures to achieve, interested friends can study.

Oracle and SQL Server use functions to generate date plus serial number