如何将oracle VARRAY转换为SQL SERVER [英] How to convert oracle VARRAY to SQL SERVER

问题描述

我正在使用oracle进行sql server迁移任务，我被困在一个需要在oracle中转换类型的地方看起来像：



创建或替换类型Q_ARRAY

是VARCHAR2（11）的varray（2000）;



在甲骨文。



任何对此的帮助都表示赞赏..

谢谢，

Dave



我的尝试：



我没有获得varray的精确转换。我是sql的新手。抱歉

Hi,

I am working in an oracle to sql server migration task, and I am stuck at a place where i need to convert a type in oracle which looks like:

create or replace type Q_ARRAY
is varray(2000) of VARCHAR2(11);

in Oracle.

Any help on this is appreciated..
thanks,
Dave

What I have tried:

I didnt get an exact conversion of varray. I am new to sql. sorry

推荐答案

据我所知，SQL Server中不存在VARRAY的完全匹配，至少不包含所有集合函数。



因此决定使用什么在很大程度上取决于你要对阵列进行什么样的操作。在大多数情况下，我认为使用表变量就足够了。看看下面的例子

As far as I know an exact match for VARRAY does not exist in SQL Server, at least not with all the collection functions.

So the decision what to use depends highly on what kind of operations you're going to make with the array. In most scenarios I believe that using a table variable would be sufficient. Have a look at the following example

-- Define the type, valid only inside the batch
DECLARE @SomeList TABLE ( col1 int );

-- Add some data
INSERT INTO @SomeList (col1) values
(1), (2), (3), (4)

--Set based operation
BEGIN
   SELECT * FROM @SomeList
END;

-- The result is 
--   col1
--   1
--   2
--   3
--   4

--Cursor based operation to handle individual rows
BEGIN
   DECLARE @col1value int;
   DECLARE ListCursor CURSOR FOR SELECT col1 FROM @SomeList;
   OPEN ListCursor;
   FETCH NEXT FROM ListCursor INTO @col1value;
   WHILE @@FETCH_STATUS = 0  BEGIN 
      PRINT 'Col1 value is '+ CAST(@col1value AS varchar(100))
      FETCH NEXT FROM ListCursor INTO @col1value;
   END;
END ;
-- the result is 
--    Col1 value is 1
--    Col1 value is 2
--    Col1 value is 3
--    Col1 value is 4
GO

类型也可以像Oracle一样持久化。在这种情况下，您需要单独创建类型。请查看如何传递多条记录的示例到存储过程 [ ^ ]



还记得SQL Server没有包的概念，所以你不能定义公共类型和代码在同一模块中。

The type can also be persistent just like in Oracle. In such case you need create the type separately. Have a look for an example at How to pass multiple records to a Stored Procedure[^]

Also remember that SQL Server does not have the concept of packages so you cannot define public types and the code in the same module.

我尝试了很多东西并最终得到了解决方案..



而不是VARRAY在Oracle中，我在SQL服务器中创建了TABLE TYPE，如下所示：

I tried a lot of things and got a solution at last..

Instead of VARRAY in Oracle a I created TABLE TYPE in SQL server like this:

CREATE TYPE dbo.Q_ARRAY
AS TABLE
(
  Q_ID VARCHAR(11)
);
GO

这在存储过程中用作参数，我需要将字符串数组传递给此存储过程来自java。

我能用下面的java代码做到这一点：

This is used in a stored proc as an argument and I need to pass the string array to this stored proc from java.
I was able to do this with the below java code:

SQLServerDataTable qDT = new SQLServerDataTable(); 
				vehDT.addColumnMetadata("Q_ID", java.sql.Types.VARCHAR);
				vehDT.addRow("11122369");
				vehDT.addRow("45523264");
				
				
				SQLServerPreparedStatement spStat = (SQLServerPreparedStatement)con.prepareStatement("{call [dbo].[process_qRequest](?)}");
				spStat.setStructured(1, "dbo.Q_ARRAY", qDT);

这就像冠军一样！

这里我使用的是sqljdbc41 jar。

请注意，旧的sql jdbc jars不提供许多这些功能。



谢谢，

戴夫

This is working like a champ!!
Here I am using sqljdbc41 jar.
Please note that the old sql jdbc jars doesnt provide many of these functionalities.

thanks,
Dave

可以为sqljdbc4.jar的下载链接提供帮助吗？

can any one help with the download link for sqljdbc4.jar?

这篇关于如何将oracle VARRAY转换为SQL SERVER的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！