• <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?

            色婷婷综合久久久久中文字幕| 99精品国产在热久久无毒不卡| 国产精品久久久天天影视| 国产成人精品久久亚洲| 久久久久久久久久久免费精品 | 青青久久精品国产免费看| 国内精品欧美久久精品| 久久久久亚洲AV无码专区体验| 久久香蕉国产线看观看精品yw| 国产日产久久高清欧美一区| 国产麻豆精品久久一二三| 亚洲国产精品一区二区久久| 精品久久久久成人码免费动漫 | 久久久精品2019免费观看| 久久国产精品77777| 亚洲国产婷婷香蕉久久久久久 | 亚洲狠狠综合久久| 久久综合日本熟妇| 天天久久狠狠色综合| 久久人人爽人人爽人人片AV高清| 青青青国产成人久久111网站| 国产视频久久| 亚洲国产精品嫩草影院久久| 国产一区二区精品久久| 国产毛片欧美毛片久久久| 久久久91精品国产一区二区三区| 日本久久久精品中文字幕| 999久久久免费精品国产| 亚洲人成无码www久久久| 国产99久久久国产精免费| 日本欧美久久久久免费播放网| 亚洲国产另类久久久精品黑人 | 天天爽天天爽天天片a久久网| 久久久久久久97| 久久99精品久久久久久不卡| 99久久婷婷国产综合亚洲| 99久久综合国产精品免费| AAA级久久久精品无码区| 91久久国产视频| 亚洲狠狠久久综合一区77777| 久久99国产亚洲高清观看首页 |