• <ins id="pjuwb"></ins>
    <blockquote id="pjuwb"><pre id="pjuwb"></pre></blockquote>
    <noscript id="pjuwb"></noscript>
          <sup id="pjuwb"><pre id="pjuwb"></pre></sup>
            <dd id="pjuwb"></dd>
            <abbr id="pjuwb"></abbr>
            posts - 62,  comments - 19,  trackbacks - 0
            -- 建表語句
            CREATE ? TABLE ? [ MyTable ] ?(
            ????
            [ MyId ] ? [ varchar ] ?( 50 )?COLLATE?Chinese_PRC_CI_AS? NULL ?,
            ????
            [ MyName ] ? [ varchar ] ?( 50 )?COLLATE?Chinese_PRC_CI_AS? NULL ?
            )?
            ON ? [ PRIMARY ]
            GO

            -- 得到新的編號(不考慮補號)
            create ? function ?f_NextID( @myDate ? datetime )
            Returns ? char ( 12 )
            as
            begin ?
            ????
            Declare ? @temp ? char ( 6 )
            ?????
            Declare ? @ret ? char ( 12 )
            ????
            set ? @temp = Substring ( Convert ( char , @myDate ), 9 , 2 ) + Substring ( Convert ( char , @myDate ), 1 , 2 ) + Substring ( Convert ( char , @myDate ), 4 , 2 )
            ????
            if ( @temp ? in ?( SELECT ? distinct ?myid = left (myid, 6 )? FROM ?mytable? WITH (XLOCK,PAGLOCK)))
            ????????????
            Select ? @ret = @temp + right ( 1000001 + Isnull ( Max ( Right (MyID, 6 )), 0 ), 6 )? from ?Mytable? with (xlock,paglock)? where ? @temp = left (MyID, 6 )
            ????
            else
            ????????
            Select ? @ret = @temp + ' 000001 ' ? from ?Mytable? with (xlock,paglock)
            ????
            return ? @ret
            end ?
            GO


            drop ? table ?MyTable
            -- 建表語句
            CREATE ? TABLE ? [ MyTable ] ?(
            ????
            [ MyId ] ? [ varchar ] ?( 50 )?COLLATE?Chinese_PRC_CI_AS? NULL ?,
            ????
            [ MyName ] ? [ varchar ] ?( 50 )?COLLATE?Chinese_PRC_CI_AS? NULL ?
            )?
            ON ? [ PRIMARY ]
            GO

            -- 得到新的編號(融合了補號處理)
            create ? FUNCTION ?f_NextNewID( @myDate ? datetime )
            RETURNS ? char ( 12 )
            AS
            BEGIN
            ????
            Declare ? @temp ? char ( 6 )
            ?????
            Declare ? @ret ? char ( 12 )
            ????
            set ? @temp = Substring ( Convert ( char , @myDate ), 9 , 2 ) + Substring ( Convert ( char , @myDate ), 1 , 2 ) + Substring ( Convert ( char , @myDate ), 4 , 2 )
            ????
            DECLARE ? @r ? char ( 12 )
            ????
            ????
            SELECT ? @r = @temp + RIGHT ( 1000001 + MIN (MyId), 6 )
            ????
            FROM (
            ????????
            SELECT ?MyId = RIGHT (MyId, 6 )? FROM ?MyTable? WITH (XLOCK,PAGLOCK)
            ????????
            UNION ? ALL ? SELECT ? 0
            ????)a?
            ??
            WHERE ? NOT ? EXISTS (
            ????????
            SELECT ? * ? FROM ?MyTable?? WITH (XLOCK,PAGLOCK)
            ????????
            WHERE ?MyId = @temp + RIGHT ( 1000001 + a.myID, 6 ))
            ????
            RETURN ( @r )
            END
            GO


            -- 測試語句(可改變時間測試一下)
            insert ?MyTable? values ?(dbo.f_NextNewID( GetDate ()), ' one ' )
            insert ?MyTable? values ?(dbo.f_NextNewID( GetDate ()), ' two ' )
            insert ?MyTable? values ?(dbo.f_NextNewID( GetDate ()), ' three ' )
            insert ?MyTable? values ?(dbo.f_NextNewID( GetDate ()), ' four ' )
            insert ?MyTable? values ?(dbo.f_NextNewID( GetDate ()), ' five ' )
            insert ?MyTable? values ?(dbo.f_NextNewID( GetDate ()), ' six ' )
            Select ? * ? from ?MyTable? order ? by ?MyId
            GO
            delete ?MyTable? where ?myId = ' 060629000002 '
            delete ?MyTable? where ?myId = ' 060629000005 '
            Select ? * ? from ?MyTable? order ? by ?MyId
            Go
            insert ?MyTable? values ?(dbo.f_NextNewID( GetDate ()), ' nexttwo ' )
            insert ?MyTable? values ?(dbo.f_NextNewID( GetDate ()), ' nextfive ' )
            Select ? * ? from ?MyTable? order ? by ?MyId
            GO



            §§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§

            SQL下測試成功







            posted on 2007-03-06 13:38 喬棟 閱讀(442) 評論(0)  編輯 收藏 引用 所屬分類: 數據庫
            <2007年3月>
            25262728123
            45678910
            11121314151617
            18192021222324
            25262728293031
            1234567

            常用鏈接

            留言簿(6)

            隨筆分類

            隨筆檔案

            文章分類

            文章檔案

            搜索

            •  

            最新評論

            閱讀排行榜

            評論排行榜


            My blog is worth $0.00.
            How much is your blog worth?

            国产精品久久久天天影视香蕉| 成人午夜精品久久久久久久小说| 亚洲AV伊人久久青青草原| 久久婷婷色综合一区二区| 日韩一区二区久久久久久| 成人午夜精品久久久久久久小说| 亚洲国产成人久久笫一页| 亚洲精品无码久久久久去q| 88久久精品无码一区二区毛片| 久久无码国产| 久久777国产线看观看精品| 伊人久久成人成综合网222| 久久久久无码精品国产不卡| 日本久久中文字幕| 国产成人综合久久综合 | 亚洲AV无码久久| 99久久久久| 久久天天躁狠狠躁夜夜avapp| 国产福利电影一区二区三区久久老子无码午夜伦不 | 久久精品国产国产精品四凭| 伊人久久大香线蕉亚洲五月天| 2022年国产精品久久久久| 久久精品国产精品亚洲精品| 久久成人国产精品一区二区| 国产精品久久久久久福利漫画| 亚洲中文字幕无码久久综合网| 久久男人中文字幕资源站| 国产欧美久久一区二区| 欧美大香线蕉线伊人久久| A级毛片无码久久精品免费| 激情五月综合综合久久69| 久久久久四虎国产精品| 久久综合亚洲欧美成人| 无码人妻久久一区二区三区免费 | 久久精品综合一区二区三区| 久久99精品国产一区二区三区| 久久香蕉超碰97国产精品| 久久久女人与动物群交毛片 | 久久久久一区二区三区| 久久夜色精品国产亚洲| 秋霞久久国产精品电影院|