以下脚本显示了一组简单的表和存储过程(用 Microsoft SQL Server 2008 编写，但应该可以在其他版本中工作，包括 SQL Express)，它们可以处理基本会计系统的任务。请注意，这是一个非常基本的系统，几乎没有错误处理。但是，通过在 SQL 中创建系统并运行一些示例事务，您可以直观地看到日志条目的影响。
表设计可以在其他数据库产品中工作，但是需要调整脚本来处理差异，尤其是在身份密钥和日期处理方面。
会计科目表和日记账表如下所示。这两个表都使用标识键来唯一标识行:
CREATE TABLE Chart_of_Accounts
( ID INT IDENTITY(1,1),
AccountNum VARCHAR(12) UNIQUE NOT NULL,
Descrip VARCHAR(48),
AcctType CHAR(1) CHECK (AcctType in ('A','L','O','R','E')),
Balance MONEY,
CONSTRAINT PK_Chart_of_Accounts PRIMARY KEY (ID)
)
CREATE TABLE Journals
(
ID INT IDENTITY(1,1), -- Unique key per line item
AccountID INT,
JrnlType CHAR(2), -- GJ, AR, AP, SJ, PJ, etc.
TransNum INT, -- Key to group entries together.
DC CHAR(1) CHECK (DC in ('D','C')),
Posted CHAR(1) DEFAULT 'N',
TransDate DATETIME DEFAULT GetDate(),
PostDate DATETIME,
Amount MONEY NOT NULL,
CONSTRAINT PK_Journals PRIMARY KEY (ID),
CONSTRAINT FK_Chart FOREIGN KEY (AccountID) REFERENCES Chart_of_Accounts(ID)
)
表格设计简单，便于说明数据流；实际的会计系统会有额外的字段，如交易描述、支票参考号等。
如果您想跟随帐簿中的示例交易，可以运行以下脚本来加载示例会计科目表:
INSERT INTO [dbo].Chart_of_Accounts (AccountNum,Descrip,AcctType,Balance)
VALUES
('1000','Cash-Checking Account','A',0),
('1100','Software','A',0),
('1200','Subscriptions','A',0),
('1600','Computer System','A',0),
('2000','Loan For Computer','L',0),
('3000','Owner Equity','O',0),
('3100','Retained Earnings','O',0)
-- Add income statement accounts (Chapter 2)
INSERT INTO [dbo].Chart_of_Accounts (AccountNum,Descrip,AcctType,Balance)
VALUES
('4000','Sales Revenue','R',0),
('5000','Rent Expense','E',0),
('5100','Postage Expense','E',0),
('5200','Shipping Supplies Expense','E',0),
('5300','Office Supplies Expense','E',0)
有两个主要过程依赖于存储过程:
- 添加交易记录
- 过帐交易记录
添加事务的存储过程需要两个参数；第一个是用逗号分隔的帐户条目列表。每个条目的格式是:
Comma Separated: Format is AcctNum|D or C|Amount
第二个参数是日记帐类型，默认为普通日记帐(GJ)。
该代码依赖于一个用户定义的函数(UDF)来将帐户输入字符串拆分成组件并返回一个数据表。该功能的代码如下所示:
CREATE Function [dbo].TransToTable
(@AcctList VARCHAR(1000) )
RETURNS
@RowTable TABLE
( AcctNumber VARCHAR(12),
Jrnl_Account_ID INT,
DebitCredit CHAR(1),
Amt MONEY
)
AS
BEGIN
DECLARE @X INT
DECLARE @Y INT
DECLARE @OneLine VARCHAR(30)
DECLARE @acctNUM VARCHAR(12)
DECLARE @DebCred CHAR(1)
DECLARE @TransAmt MONEY
SET @AcctList=@AcctList+','
SET @x = CHARINDEX(',',@AcctList)
WHILE @x >0
BEGIN
SET @OneLine = LEFT(@AcctList,@x-1)
SET @AcctList = RTRIM(SUBSTRING(@AcctList,@x+1,9999))
if LEN(@OneLine) > 0
begin
SET @Y = CHARINDEX('|',@OneLine)
SET @AcctNum = LEFT(@OneLine,@y-1)
SET @DebCred = SUBSTRING(@OneLine,@y+1,1)
SET @OneLine = RTRIM(SUBSTRING(@OneLine,@y+3,9999))
SET @TransAmt = CAST(@OneLine AS MONEY)
INSERT INTO @RowTable VALUES (@AcctNum,-1,@DebCred,@TransAmt)
end
UPDATE @rowTable SET Jrnl_Account_ID = xx.id
FROM (select id,accountNum FROM [dbo].chart_of_accounts) xx
WHERE xx.accountNum=AcctNumber
SET @x = CHARINDEX(',',@AcctList)
END
RETURN
END
有了这个函数，添加事务函数使用这个函数，并将字符串转换成一个表。然后，它会检查:
- 所有账户都找到了。
- 借方和贷方相符。
如果是，该条目会添加到日记帐中(但不会过帐):
CREATE PROCEDURE [dbo].AddTransaction
(
@AcctList VARCHAR(1000), -- Comma Separated: Format is AcctNum|D or C|Amount,
@JrnlType CHAR(2) ='GJ'
)
AS
BEGIN
SET NOCOUNT ON
-- Split the parameter into a table
DECLARE @TransTable TABLE (AccoutNum VARCHAR(12),ID INT,DC CHAR(1),amt MONEY)
INSERT INTO @TransTable
SELECT * FROM [dbo].TransToTable(@AcctList)
-- Validate all accounts, return -1 if any invalid accounts
DECLARE @nCtr INT
SELECT @nCtr = COUNT(*) FROM @TransTable WHERE ID <0
IF (@nCtr >0 )
BEGIN
-- Optionally, could raise an error
PRINT 'Missing account numbers'
RETURN -1
END
-- Validate Debits = Credits, return -2 if not
DECLARE @DebitTot MONEY
DECLARE @CreditTot MONEY
SELECT @DebitTot = SUM(amt) FROM @TransTable WHERE DC='D'
SELECT @CreditTot = SUM(amt) FROM @TransTable WHERE DC='C'
IF (@DebitTot <> @CreditTot )
BEGIN
-- Optionally, could raise an error
PRINT 'Debits <> Credits'
RETURN -2
END
-- Post the transaction into journals
BEGIN TRANSACTION
DECLARE @nNext INT
SELECT @nNext = IsNull(max(transNum)+1,1)
FROM [dbo].Journals WHERE jrnlType=@JrnlType
INSERT INTO [dbo].Journals (AccountID,JrnlType,TransNum,DC,Amount)
SELECT ID,@JrnlType,@nNext,DC,amt
FROM @TransTable
COMMIT
RETURN 0
END
该过程返回一个代码，指示事务是否已添加到日记帐:
- 0 正常
- -1 找不到帐号
- -2 交易未平衡
以下是将根据第 1 章的交易记录的日记帐分录:
EXEC [dbo].AddTransaction '1000|D|10000,3000|C|10000','GJ'
EXEC [dbo].AddTransaction '1600|D|6000,1000|C|1000,2000|C|5000','GJ'
EXEC [dbo].AddTransaction '1100|D|794,1200|D|99,1000|C|893','GJ'
EXEC [dbo].AddTransaction '1000|C|1000,2000|D|1000','GJ'
日记帐分录被记录为未过帐；它们不适用于会计科目表。在某些时候，需要将条目应用到帐户中。你可以在条目写好后立即打电话给发帖人，或者定期发帖。过账交易代码采用一个参数——要么是要过账的交易编号，要么是表示过账所有交易的 0。代码如下所示:
CREATE PROCEDURE [dbo].PostTransaction( @TransNumb INT = 0 )
AS
BEGIN
SET NOCOUNT ON
UPDATE [dbo].Chart_of_Accounts SET Balance = Balance +xx.PostAmt
FROM
(
SELECT AccountID,
Sum(
CASE WHEN jl.dc='D' THEN amount ELSE -1*amount END
) as PostAmt
FROM [dbo].Journals jl
JOIN [dbo].Chart_of_Accounts ca on jl.AccountID=ca.id
WHERE jl.posted='N' AND ca.AcctType in ('A','E')
AND (Transnum = @TransNumb or @TransNumb=0)
GROUP BY AccountID
) xx
WHERE xx.accountID=ID
UPDATE [dbo].Chart_of_Accounts SET Balance = Balance +xx.PostAmt
FROM
(
SELECT AccountID,
Sum(
CASE WHEN jl.dc='C' THEN amount ELSE -1*amount END
) as PostAmt
FROM [dbo].Journals jl
JOIN [dbo].Chart_of_Accounts ca on jl.AccountID=ca.id
WHERE jl.posted='N' AND ca.AcctType in ('L','O','R')
AND (Transnum = @TransNumb or @TransNumb=0)
GROUP BY AccountID
) xx
WHERE xx.accountID=ID
UPDATE [dbo].Journals SET posted='Y',PostDate=getDate()
WHERE posted='N' AND (Transnum = @TransNumb or @TransNumb=0)
END
然后，应用的日记帐交易被标记为已过帐，并记录过帐日期。
以下两个视图创建了资产负债表和损益表。它们可以作为收集原始数据的起点，向用户显示这些报告。
CREATE VIEW [dbo].BalanceSheet
AS
SELECT AccountNum,Descrip,Balance FROM [dbo].Chart_of_Accounts WHERE AcctType='A'
UNION
SELECT '1900','TOTAL ASSETS',Sum(Balance) FROM [dbo].Chart_of_Accounts
WHERE AcctType='A'
UNION
SELECT AccountNum,Descrip,Balance FROM [dbo].Chart_of_Accounts
WHERE AcctType='L'
UNION
SELECT '2900','TOTAL LIABILITIES',Sum(Balance) FROM [dbo].Chart_of_Accounts
WHERE AcctType='L'
UNION
SELECT AccountNum,Descrip,Balance FROM [dbo].Chart_of_Accounts
WHERE AcctType='O'
UNION
SELECT '3900','TOTAL EQUITY',Sum(Balance) FROM [dbo].Chart_of_Accounts
WHERE AcctType='O'
UNION
SELECT '3999','TOTAL LIABILITIES/EQUITY',Sum(Balance) FROM [dbo].Chart_of_Accounts
WHERE AcctType IN ('L','O')
请注意，上面的代码被假定为在结账分录过账之后，但在下一期交易发生之前运行。要创建中期资产负债表，您需要从上面开始，然后根据日记账中收入和支出之间的差额添加“计算的”留存收益。如果你这样做，一定要注意资产负债表是中期的。
损益表视图是根据未过帐的日记帐交易记录准备的:
CREATE VIEW [dbo].IncomeStatement
AS
SELECT 4000 as Seq,'REVENUE' as 'Account Name',IsNull(Sum(jl.Amount),0) as Balance
FROM [dbo].Journals jl
JOIN [dbo].Chart_of_Accounts ca on ca.id=jl.AccountId
WHERE jl.posted='N' and ca.AcctType='R'
UNION
SELECT ca.AccountNum,descrip,IsNull(Sum(jl.Amount),0) as Balance
FROM [dbo].Journals jl
JOIN [dbo].Chart_of_Accounts ca on ca.id=jl.AccountId
WHERE jl.posted='N' and ca.AcctType='E'
GROUP BY ca.descrip,ca.AccountNum
UNION
SELECT '9999','NET INCOME(loss)',xx.Balance
FROM (
SELECT IsNull(
Sum(CASE when jl.dc='D' then -1*jl.amount
else jl.amount end),0 ) as Balance
FROM [dbo].Journals jl
JOIN [dbo].Chart_of_Accounts ca on ca.id=jl.AccountId
AND jl.posted='N' and (ca.AcctType IN ('R','E'))
) xx
您可以使用此视图中的数据生成该期间的损益表。它还可以用来生成期末分录，将收入转入留存收益账户，为下一期做准备。
这个简单系统的 SQL 代码可以在 Syncfusion 网站上找到。如果您想回顾书中的一些早期事务，可以运行以下脚本。
这将构建会计科目表和日记帐表。如果需要，您可以向这些表中添加额外的字段，但是，如果您更改了任何字段名称，请务必更新各种过程和视图。
这将生成向日记帐和会计科目表添加和过帐交易记录的过程。
这创建了为资产负债表和损益表提供数据的视图。
这将加载前两章中的帐户和交易示例图表。