Exec sp_addtype rowid,'Varchar(6)','Not Null' --自定義數(shù)據(jù)類型rowid　　標(biāo)識(shí)代碼

Exec sp_addtype orgcode,'Varchar(20)','Not Null'  --自定義數(shù)據(jù)類型orgcode　　公司代碼
    

      --可根據(jù)實(shí)際情況設(shè)置默認(rèn)值

CREATE TABLE _sysparameters(

nodetype varchar(10) NOT NULL,	　　　　　　--標(biāo)識(shí)類型

nodeid rowid NOT NULL,			　　--標(biāo)識(shí)的最大值,之后新的ID在這個(gè)基礎(chǔ)上遞增

siteid varchar(10),				--默認(rèn)為NULL,自定義

version char(10)				--版本,自定義

--設(shè)置rowid為6位數(shù).創(chuàng)建規(guī)則

CREATE RULE rowid6

AS

STR(@rowid)=6

--綁定規(guī)則

SP_BINDRULE rowid6,'_sysparameters.nodeid'
    

      /*

	2015.4.26	HZF

*/

ALTER PROC [dbo].[p_sysxxxxrowid]

	@XXXX ROWID OUTPUT

AS

	DECLARE @nodeid rowid

	DECLARE @oldnodeid rowid

	

	BEGIN TRAN

		

		--  ASCII 40 - 96

		select @nodeid = nodeid from _sysparameters where nodetype='rowid'	--獲取歷史最大的id碼

		set @oldnodeid = @nodeid

		

		if RIGHT(@nodeid,1) = '9'

			set @nodeid = left(@nodeid,5) + 'A'

		else if RIGHT(@nodeid,2) = '9Z'

			set @nodeid = left(@nodeid,4) + 'A0'

		else if RIGHT(@nodeid,3) = '9ZZ'

			set @nodeid = left(@nodeid,3) + 'A00'

		else if RIGHT(@nodeid,4) = '9ZZZ'

			set @nodeid = left(@nodeid,2) + 'A000'

		else if RIGHT(@nodeid,5) = '9ZZZZ'

			set @nodeid = left(@nodeid,1) + 'A0000'

		else if @nodeid = '9ZZZZZ'

			set @nodeid = 'A00000'

		else if Right(@nodeid,1) <> 'Z'

			set @nodeid = left(@nodeid,5) + CHAR(ASCII(right(@nodeid,1))+1) --尾數(shù)不為Z的情況下，遞增1，使用CHAR是為了字母遞增

		else if @nodeid = 'ZZZZZZ'

			raiserror('請(qǐng)重建編號(hào)',16,1)

		

		--尾數(shù)為Z的時(shí)候,Z前一位+1,之后的0替換

		else if RIGHT(@nodeid,5) = 'ZZZZZ'

			set @nodeid = CHAR(ASCII(@nodeid)+1) + '00000'

		else if RIGHT(@nodeid,4) = 'ZZZZ'

			set @nodeid = left(@nodeid,1) + CHAR(ASCII(right(@nodeid,5))+1) + '0000'

		else if RIGHT(@nodeid,3) = 'ZZZ'

			set @nodeid = left(@nodeid,2) + CHAR(ASCII(right(@nodeid,4))+1) + '000'

		else if RIGHT(@nodeid,2) = 'ZZ'

			set @nodeid = left(@nodeid,3) + CHAR(ASCII(right(@nodeid,3))+1) + '00'

		else if RIGHT(@nodeid,1) = 'Z'

			set @nodeid = left(@nodeid,4) + CHAR(ASCII(right(@nodeid,2))+1) + '0'

		

		--更新_sysparameters

		update _sysparameters set nodeid = @nodeid where nodetype='rowid' and nodeid = @oldnodeid

		

		if @@rowcount <> 0

			begin

			select @xxxx = @nodeid

			commit

			end

		

		else begin

			  raiserror('rowid 重復(fù)！',16,1)

			  rollback

			end



GO


    

      --uom:計(jì)量單位，uomname:計(jì)量單位名稱

CREATE TABLE guom(UOM VARCHAR(50) NOT NULL PRIMARY KEY,uomname VARCHAR(40))

INSERT INTO guom values('%','百分比')

INSERT INTO guom values('UOM','通用單位,用于無(wú)法確定單位的虛擬物料')

--數(shù)據(jù)根據(jù)實(shí)際情況設(shè)置，通用單位必須設(shè)置
    

      --設(shè)置表架構(gòu)

CREATE TABLE oCompany(

	companyid orgcode NOT NULL primary key,

	companyname varchar(50),

	isproduct int, --是否在生產(chǎn)之中，綁定規(guī)則默認(rèn)1（生產(chǎn)），2（停產(chǎn)）

	coacode float, --公司標(biāo)識(shí)1 ,1.1, 1.2,便于生成父級(jí)標(biāo)識(shí)碼

	rowid varchar(6) not null, --標(biāo)識(shí)碼

	treecontrol varchar(60) not null --父級(jí)標(biāo)識(shí)碼

)

--rowid系統(tǒng)中使用存儲(chǔ)過(guò)程來(lái)執(zhí)行

insert into oCompany values('JX10','江西公司',1,1.0,'000001','000001')


    

      CREATE TABLE ms_hrcodebycompany 

(

	companyid varchar(20) not null primary key,	--公司ID

	hrbeginno int not null,		--最小工號(hào)

	hrendno int not null,		--工號(hào)上限

	cuhrcode int not null,		--當(dāng)前最大的工號(hào)

	memo varchar(50)	--說(shuō)明

)　


    

      CREATE     FUNCTION f_hrNextHrcode ( @companyid VARCHAR(10) )  

RETURNS @Table TABLE ( newhrid VARCHAR(10) )  

AS   

    BEGIN      

         

        INSERT  INTO @Table  

                SELECT  MAX(hrcode) + 1  

                FROM    hrgeneral a  

                        JOIN Ms_HrCodeByCompany b ON a.companyid = b.companyid  

                WHERE   a.hrcode BETWEEN b.hrbeginno AND b.hrendno  

                        AND a.companyid = @companyid   

            

        RETURN   

    END