巧用SQL语言批量建立金蝶K3账套

庋藏天下 2013-07-09

展开全文

巧用SQL语言批量建立金蝶K3账套

2012-02-28 19:22:38 来源:中国管理信息化作者: 点击数:242次评论:0条

关键词:金蝶 ERP SQL语言实验室

实验教学是学校本科教学培养方案和教学计划的重要环节,随着各高校对应用型和创新型人才培养的重视,目前绝大部分的经管类院校都开设了ERP实验课程以加强应用型人才的培养,ERP实验室成为经管类学院的各个专业开展专业综合性实验和校内模拟实习的基地?因为金蝶K3单机版对计算机的配置要求较高,并且实验计算机安装有硬盘还原卡,学生每次实验时需进行恢复?拷贝和备份冗长的过程,所以我院实验室在实验教学中采用K3网络版,在K3服务器上为每位实验学生建立一个账套,由于ERP课程面向各个专业的学生,需建的账套的类型和数量有很多,如我院实验室一学期需建400多个各种类型(如标准财务?标准供应链?人力资源等)的实验账套?建立账套是一个重复和非常耗费时间的过程,随着账套数的不断增多,建账的速度也越来越慢,建立一个账套需近40多分钟的时间,建上百个账套需要专人几周的时间?为解决建立K3实验账套效率非常低的问题,经过摸索本文通过SQL语言来快速?简便地完成金蝶K3账套的批量建立?

1基于SQL语言的建库方式

金蝶K3是基于三层客户/服务器(C/S模式),用户通过客户端和中间层连接K3后台数据库的服务器来实现业务操作?当用户使用K3的账套管理工具建立账套时,实际上是程序使用SQL数据库管理语言对其后台数据库进行数据操作,因此可在K3建立账套时打开SQL数据库的SQL Server Profiler来跟踪和分析SQL建库的命令?通过对SQL Profiler记录的SQL语句的分析可得出K3账套管理建库的步骤:

1.1建立数据库

通过对SQL语句的分析可发现,K3建账时实际是调用了SQL语言的RESTORE DATABASE数据库恢复命令,该命令以恢复K3预置的数据库备份文件的方式新建一个账套的数据库,其SQL命令为:

RESTORE DATABASE AIS201102081015 from disk=’D:\Program Files\Kingdee\K3ERP\DBFILE\SCM.bak’ with recovery,move ’SCM102SP2_Data’ to ’D:\K3-DATA\ AIS201102081015_Data.mdf’,move ’SCM102SP2_log’ to ’D:\K3-DATA\AIS201102081015_log.ldf’,replace

RESTORE DATABASE命令把SCM.bak备份文件恢复成新的数据库AIS 201102081015,其中D:\K3-DATA是新建账套的数据库存放路径? SCM.bak是标准供应链解决方案类型的账套备份文件,SCM102SP2_Data和SCM102SP2_Log为它的数据库文件和数据库日志文件?K3提供了标准供应链?标准财务?人力资源?集团财务等8种账套类型,这些账套类型以数据库备份压缩文件的形式存放在D:\Program Files\Kingdee\K3ERP\DBFILE(假定金蝶的安装目录在D盘)中,其文件名为:ADCA?SCM?GFI?HR等9个压缩文件?当用户建立某个类型账套如选择集团财务解决方案时,程序会先调用SQL命令解压GFI.RAR文件,然后再以RESTORE DATABASE命令把GFI.bak数据库备份文件中的GFI102SP2_Date数据库文件和GFI102SP2_Log数据库日志文件恢复到新的数据库文件?在建立不同账套类型时只需选用对应的备份文件和修改参数Move后接的文件名,如建立战略人力资源类型账套则把数据库备份文件改为HR.bak,参数move后两个文件分别改为HR102SP2_data和HR102SP2_log?

为了更快捷和统一地建立学生实验账套,可采用K3账套管理中恢复账套的方式来新建账套?其方法是先建立一个账套类型的账套作为模板,完成账套属性及账套的用户?数据权限等方面的设置后备份该账套?建立相同类型的账套时采用恢复账套命令,选取账套备份文件后输入新账套号和账套名来完成账套的新建?这种恢复建账方法比新建账套的方法速度快很多,假定E:\K3-backup\Ftc.bak为建账模板的备份文件,恢复账套的SQL语句为:

RESTORE DATABASE AIS20110420070623 from disk=’E:\K3-backup\Ftc.bak’ with recovery, move ’SCM102SP2_Data’ to ’E:\K3-DATA\AIS20110420070623_Data.mdf’,move ’SCM102SP2_log’ to ’E:\K3-DATA\AIS20110420070623_log.ldf’, replace

可在SQL Server Management studio的数据库查询窗口中执行上述SQL语句,以下是数据库查询窗口完成数据库新建后的返还信息:

已为数据库’AIS20110420070623’,文件’SCM102SP2_Data’(位于文件1上)处理了28 496 页?已为数据库 ’AIS20110420070623’,文件 ’SCM102SP2_log’ (位于文件1上)处理了7页?RESTORE DATABASE 成功处理了 28 503 页,花费 18.867 秒(12.375 MB/秒)?

从返还信息中可看出,采用恢复账套的方式建立数据库的速度要快得多?

1.2在Master数据库中注册账套

账套的后台数据库建立后,还必须在SQL数据库的Master数据库的t_kdaccount_gl表中建立新账套的记录?Master数据库是SQL Server系统最重要的系统库,它记录了SQL Server系统的所有系统信息,包括所有的登录信息?系统设置信息?SQL Server的初始化信息和其他系统数据库及用户数据库的相关信息?使用以下SQL语句完成t_kdaccount_gl表的新账套记录:

insert into t_KDAccount_gl(CAcc_Name,CDbName,DBuildDate,cdbversion) values(’st2’,’ AIS201102081015’,’2011-02-08’,’3.0’)

该SQL语句在表t_KDAccount_gl中按顺序插入账套名?数据库实体名?建立账套日期和版本号的字段记录?

1.3在K3系统中注册账套

账套数据库的建立和在Master数据库中注册后,仍需在账套管理中重新注册账套才能正常使用?注册方法:打开K3账套管理工具,选择菜单数据库/注册账套,在注册窗口中先选择数据库实体,在弹出的账套列表中选择已建立的账套名称和数据库实体后在返回的注册窗口中输入账套号即可完成账套的注册,注册后就可正常使用新建账套了?

2批量建立账套

掌握了K3的SQL建账方法后,可采用Transact-SQL语言快速?简便地大批量地建立学生实验账套?假定需为实验准备200个账套,首先在K3账套管理中建立账套并备份该账套,然后使用SQL的条件循环流程控制语句,在begin end之间循环重复执行RESTORE DATABASE新建账套和在t_KDAccount_gl表插入新账套记录,当新建账套数满足200个条件时退出循环?假定作为模板的账套类型为标准财务解决方案,其备份文件存放在E:\k3_backup目录下,备份文件名为Ftc.bak,K3新建账套时其数据库实体名是建库时系统自动生成,如AIS20110208112545为前缀AIS,后面加上建库时系统的日期和时间,具有唯一性?批量建库采用手工指定方法,前缀AIS后加上数字顺序如AIS1?AIS2,账套名为前缀st后加后数字顺序即st1?st2?循环批量建立实验账套的SQL语句为:

declare @ztvarchar(255) /*设置账套的账套号*/

declare @i int

declare @nvarchar(255) /*设置账套数据库的实体名称*/

declare @p1varchar(255) /*设置账套的数据库文件的存放路径*/

declare @p2varchar(255) /*设置账套数据库的日志文件存放路径*/

set @i=1

while @i≤200 /*需建1到200个账套*/

begin

set @n=’AIS’+cast(@i as varchar(255)) /*账套数据库名为AIS加1-200后缀*/

set @p1=’e:\K3-DATA\’+’AIS’+cast(@i as varchar(255))+’_Data.mdf’

/*数据库文件存放在E:\K3_data目录下,文件名为AIS后缀数字如AIS1_data.mdf*/

set @p2=’e:\K3-DATA\’+’AIS’+cast(@i as varchar(255))+’_log.ldf’

/*数据库的日志文件存放在E:\K3_data目录下, 文件名为AIS后缀数字如AIS1_log.ldf*/

set @zt=’ST’+cast(@i as varchar(255))

RESTORE DATABASE @nfrom disk=’E:\K3-backup\Ftc.bak’ with recovery,

move ’STDFI102SP2_Data’ to @p1, move ’STDFI102SP2_LOG’ to @p2, replace

/*以预先设置好的Ftc.bak账套备份文件为母版新建账套*/

insert into t_KDAccount_gl(CAcc_Name,CDbName,DBuildDate,cdbversion) values(@zt,@n,getdate(),’3.0’)

/*在t_kdaccount_gl表中插入新账套记录,getdate()为SQL日期函数,取当前系统时期*/

set @i=@i+1

end

用户可以在服务器空闲的时间如晚上计划执行上述批量建库的SQL语句,让服务器在空闲时间里自动完成200个账套的数据库建库工作,然后再使用K3的账套管理工具中的注册账套完成新账套的注册?

3结语

建立上百个金蝶实验账套是一个烦琐?费时的过程,尤其是当账套数达到一定数量后,专人建立实验账套一天才完成十几个账套,效率相当低?采用SQL语言自动批量完成账套后台数据库的建立,而手工注册账套只需几分钟的时间,使建账变得快捷?轻松?SQL作为强大的关系数据库管理语言,在用友U8?金蝶K3等财务软件的实验数据管理和维护中,运用SQL语言可达到事半功倍效果,使我们的工作更快捷?简便和富有效率?

附录：RESTORE 语法：

--To Restore an Entire Database from a Full database backup (a Complete Restore):
RESTORE DATABASE { database_name | @database_name_var }
[ FROM <backup_device> [ ,...n ] ]
[ WITH
   {
    [ RECOVERY | NORECOVERY | STANDBY =
        {standby_file_name | @standby_file_name_var }
       ]
   | , <general_WITH_options> [ ,...n ]
   | , <replication_WITH_option>
   | , <change_data_capture_WITH_option>
   | , <FILESTREAM_WITH_option>
   | , <service_broker_WITH options>
   | , <point_in_time_WITH_options—RESTORE_DATABASE>
   } [ ,...n ]
]
[;]

--To perform the first step of the initial restore sequence
-- of a piecemeal restore:
RESTORE DATABASE { database_name | @database_name_var }
   <files_or_filegroups> [ ,...n ]
[ FROM <backup_device> [ ,...n ] ]
   WITH
      PARTIAL, NORECOVERY
      [  , <general_WITH_options> [ ,...n ]
       | , <point_in_time_WITH_options—RESTORE_DATABASE>
      ] [ ,...n ]
[;]

--To Restore Specific Files or Filegroups:
RESTORE DATABASE { database_name | @database_name_var }
   <file_or_filegroup> [ ,...n ]
[ FROM <backup_device> [ ,...n ] ]
   WITH
   {
      [ RECOVERY | NORECOVERY ]
      [ , <general_WITH_options> [ ,...n ] ]
   } [ ,...n ]
[;]

--To Restore Specific Pages:
RESTORE DATABASE { database_name | @database_name_var }
   PAGE = 'file:page [ ,...n ]'
[ , <file_or_filegroups> ] [ ,...n ]
[ FROM <backup_device> [ ,...n ] ]
   WITH
       NORECOVERY
      [ , <general_WITH_options> [ ,...n ] ]
[;]

--To Restore a Transaction Log:
RESTORE LOG { database_name | @database_name_var }
[ <file_or_filegroup_or_pages> [ ,...n ] ]
[ FROM <backup_device> [ ,...n ] ]
[ WITH
   {
     [ RECOVERY | NORECOVERY | STANDBY =
        {standby_file_name | @standby_file_name_var }
       ]
  | , <general_WITH_options> [ ,...n ]
   | , <replication_WITH_option>
    | , <point_in_time_WITH_options—RESTORE_LOG>
   } [ ,...n ]
]
[;]

--To Revert a Database to a Database Snapshot:
RESTORE DATABASE { database_name | @database_name_var }
FROM DATABASE_SNAPSHOT = database_snapshot_name

<backup_device>::=
{
   { logical_backup_device_name |
      @logical_backup_device_name_var }
| { DISK | TAPE } = { 'physical_backup_device_name' |
      @physical_backup_device_name_var }
}

<files_or_filegroups>::=
{
   FILE = { logical_file_name_in_backup | @logical_file_name_in_backup_var }
| FILEGROUP = { logical_filegroup_name | @logical_filegroup_name_var }
| READ_WRITE_FILEGROUPS
}

<general_WITH_options> [ ,...n ]::=
--Restore Operation Options
   MOVE 'logical_file_name_in_backup' TO 'operating_system_file_name'
          [ ,...n ]
| REPLACE
| RESTART
| RESTRICTED_USER

--Backup Set Options
| FILE = { backup_set_file_number | @backup_set_file_number }
| PASSWORD = { password | @password_variable }

--Media Set Options
| MEDIANAME = { media_name | @media_name_variable }
| MEDIAPASSWORD = { mediapassword | @mediapassword_variable }
| BLOCKSIZE = { blocksize | @blocksize_variable }

--Data Transfer Options
| BUFFERCOUNT = { buffercount | @buffercount_variable }
| MAXTRANSFERSIZE = { maxtransfersize | @maxtransfersize_variable }

--Error Management Options
| { CHECKSUM | NO_CHECKSUM }
| { STOP_ON_ERROR | CONTINUE_AFTER_ERROR }

--Monitoring Options
| STATS [ = percentage ]

--Tape Options
| { REWIND | NOREWIND }
| { UNLOAD | NOUNLOAD }

<replication_WITH_option>::=
| KEEP_REPLICATION

<change_data_capture_WITH_option>::=
| KEEP_CDC

<FILESTREAM_WITH_option>::=
| FILESTREAM ( DIRECTORY_NAME = directory_name )

<service_broker_WITH_options>::=
| ENABLE_BROKER
| ERROR_BROKER_CONVERSATIONS
| NEW_BROKER

<point_in_time_WITH_options—RESTORE_DATABASE>::=
| {
   STOPAT = { 'datetime'| @datetime_var }
| STOPATMARK = 'lsn:lsn_number'
                 [ AFTER 'datetime']
| STOPBEFOREMARK = 'lsn:lsn_number'
                 [ AFTER 'datetime']
   }

<point_in_time_WITH_options—RESTORE_LOG>::=
| {
   STOPAT = { 'datetime'| @datetime_var }
| STOPATMARK = { 'mark_name' | 'lsn:lsn_number' }
                 [ AFTER 'datetime']
| STOPBEFOREMARK = { 'mark_name' | 'lsn:lsn_number' }
                 [ AFTER 'datetime']
   }