当前位置:首 页>> 技术文档 [阅读]
存储过程在紫金桥软件的应用

我们都知道,在紫金桥软件中可以通过脚本或报表来访问关系库系统。我们通常的访问方式大概如下(以报表访问关系库为例):

  • 建立报表关系数据源点,配置关系数据源点连接的数据库用户名和密码等属性。
  • 绘制报表,在报表的关系数据库连接中指明第一步建立的报表关系数据源点。
  • 在报表上写对关系库操作的命令,比如SELECT命令。
  • 在报表中对关系库返回的结果进行处理,最简单的是报表自动显示结果。

这里第3步的SELECT命令中可以访问SQL中的表或视图。另外也可以使用存储过程,比如:

#R.SqlExeCmdNoRet("EXEC DOTRANCDATA");

这里的EXEC DOTRANCDATA表示执行DOTRANCDATA这个存储过程。

下面我们通过一个简单的例子来说明一下存储过程的用法。

比如我们需要做一个产品出入库的项目,产品在某一个地方通过驱动或条码设备自动进行入库操作,当数据进入关系库之后,可以通过关系库的各种统计分析查询功能来对产品进行统计和检索,由于需要在多个地方进行检索,所以SQL数据库放在远端网络的一个服务器上。

但是这里存在这么一个问题,由于网络有可能会偶尔出现故障,虽然在故障情况下暂时不能查询是可以理解的,但是我们不能允许在网络出现故障的情况下,产品不能入库。

这种问题可以这么解决,在本地关系库中建立一个缓冲表,数据先插入本地的缓冲表中,然后通过存储过程,把本地的缓冲表中的数据移动到远端的产品库中。在本地的任何检索和查询都是针对的远端的产品库来进行。这样当网络中断的时候,数据就可以先缓冲到本地,此时产品的入库工作仍然可以顺利的进行,只不过本地的数据无法自动的移动到远端,此时在远端的数据库中是无法检索到这些入库的产品的。当网络恢复之后,由存储过程自动的把数据移动到远端数据库中,此时在远端数据库中就可以检索到这些产品了。

下面我们举一个简单的例子,为了简化说明我们的两个表都在本地数据库中,首先数据插入其中的一个表中,然后在使用存储过程移动到另外一个表。对于跨数据库的表,处理方式一样,只需要稍微做些调整就行了。

首先我们在SQL中建立两个表,名为“测试数据源”和“测试目标”,如下图所示:

在测试数据源和测试目标中建立结构相同的两个数据表,如下图所示:

这里是一个简单的人员入库表,表明为User,有三个字段,第一个是自动增长的ID,第二个是人名,第三个年龄。

我们在紫金桥中创建一个关系数据源点,让该点连接“测试数据源”数据库,如下图所示:

在紫金桥中创建一个窗口,并且创建一个报表,给报表关联刚刚建立的报表关系数据源点。

在报表上允许用户输入姓名和年龄,如下图所示:

给姓名和年龄的输入位置设置相应的输入方式,给提交按钮关联如下的脚本:

SqlExeCmdNoRet("INSERT INTO [User](Name, [Year]) VALUES ('"+Txt(1,1)+"', "+Txt(2,1)+")");

即可把人员姓名和年龄插入数据库中。

下面我们通过存储过程来把数据从“测试数据源”库移动到“测试目标”库中。

在“测试数据源”库中创建一个存储过程,如下图所示:

点击确定即可创建存储过程。

打开SQL查询分析器,选中相应的存储过程,右键菜单选择编辑功能,如下图所示:

在此处输入如下的代码:

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_NULLS OFF

GO

ALTERPROCEDURE MoveDataAS

 

DECLARE @strVARCHAR(1000)

DECLARE @SqlstrVARCHAR(1000)

SET @str = ''

SELECT @str=@str+','+syscolumns.[name] FROM syscolumns WHERE syscolumns.id=object_id('User') and syscolumns.[name]<>'ID'

SET @str = stuff(@str,1,1,'')

SET @str = 'INSERT INTO 测试目标.dbo.[User](' + @str + ') SELECT ' + @str + ' FROM 测试数据源.dbo.[User]'

 

DECLARE Ptr CURSOR

FOR

SELECT ID FROM 测试数据源.dbo.[User]

OPEN Ptr

DECLARE @ID INT

 

FETCH NEXT FROM Ptr INTO @ID

WHILE (@@FETCH_STATUS <> -1)

BEGIN

IF (@@FETCH_STATUS <> -2)

BEGIN

SET @Sqlstr = @str + ' WHERE ID=' + CONVERT(varchar, @ID)

EXEC(@Sqlstr)

DELETE FROM 测试数据源.dbo.[User] WHERE ID = @ID

END

FETCH NEXT FROM Ptr INTO @ID

END

CLOSE Ptr

DEALLOCATE Ptr

 

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

GO

其中前面和后面5行代码,是固定的,功能主要是更改存储过程的内容。

中间的内容是移动数据,这里我们不能简单的这样写:

INSERT INTO 测试目标.dbo.[User] SELECT * FROM 测试数据源.dbo.[User]

因为,两个表中都有自动增长的字段ID,如果复制所有的内容,也会导致复制ID字段的内容,而这会打乱系统自动增长的规律,可能会导致执行失败。

 

DECLARE @strVARCHAR(1000)

DECLARE @SqlstrVARCHAR(1000)

SET @str = ''

SELECT @str=@str+','+syscolumns.[name] FROM syscolumns WHERE syscolumns.id=object_id('User') and syscolumns.[name]<>'ID'

SET @str = stuff(@str,1,1,'')

这一段代码,查询User表中的所有名称不为ID的字段的名称,并用逗号分隔。

SET @str = stuff(@str,1,1,'')

这一句代码的功能是去除开始的逗号。

SET @str = 'INSERT INTO 测试目标.dbo.[User](' + @str + ') SELECT ' + @str + ' FROM 测试数据源.dbo.[User]'

这一句代码生成复制数据的命令。

 

DECLARE Ptr CURSOR

FOR

SELECT ID FROM 测试数据源.dbo.[User]

OPEN Ptr

DECLARE @ID INT

 

FETCH NEXT FROM Ptr INTO @ID

WHILE (@@FETCH_STATUS <> -1)

BEGIN

IF (@@FETCH_STATUS <> -2)

BEGIN

SET @Sqlstr = @str + ' WHERE ID=' + CONVERT(varchar, @ID)

EXEC(@Sqlstr)

DELETE FROM 测试数据源.dbo.[User] WHERE ID = @ID

END

FETCH NEXT FROM Ptr INTO @ID

END

CLOSE Ptr

DEALLOCATE Ptr

这一段代码,使用了多个游标,逐行的复制数据和删除数据,以实现移动数据的目的。

这里之所以采取一行一行的移动数据,主要是为了防止,在移动数据的过程中,又有了新的人员入库,插入了新的记录。一行一行的移动可以使得复制数据和删除数据可以一一对应。

 

最后可以把此存储过程放到作业中,使得它可以被周期运行,就可以实现自动的数据移动了。