Oracle 引导到其他表

发布于 2024-08-30 14:23:40 字数 1467 浏览 1 评论 0原文

目前我正在编写一个小型转换程序，它将把主键策略转换为使用 GUID 而不是整数。这是一个简单的客户引发的要求，我无法改变它。

我已将 RAW(16) 的替代 pk 候选添加到数据库中的每个表中，并使用 SYS_GUID() 填充每个记录。我对 FK 做了同样的事情，我为每个 FK 添加了一个替代列。现在我正在将 FK 链接到它们的 PK 的过程中，通过查询父表，我获得了特定行的 guid/新键，之后我想插入到子表中的替代候选 FK 中。

有点像这样：

sqlString = "SELECT PK FROM " + t+ " WHERE " + fkcol+ " = " + childValue;
OracleDataReader guidReader = GetDataReader(sqlString);

while (guidReader.Read())
{
    sqlString = "UPDATE T SET FK = " + guidReader["PK"];
}

调试这个 sqlString 得到以下值：

更新 SIS_T_USER 设置 FK_C_EMPLOYEE_ID = System.Byte[]

现在，我该如何继续并将其作为一个好的 GUID 保存在我的 Oracle 数据库中？

编辑方式：

OracleCommand command = new OracleCommand(sqlString, this.oracleConnection);
                                command.CommandType = CommandType.Text;
                                OracleParameter op1 = new OracleParameter("guid", OracleDbType.Raw);
                                op1.Value = guidReader["PK"];
                                command.Parameters.Add(op1);
                                try
                                {
                                    command.ExecuteNonQuery();
                                }
                                catch (OracleException oex)
                                {
                                    Console.WriteLine("Unable to update: {0}", oex.Message);
                                }

原文

At the moment I'm writing a small conversion program, it will convert the primary key strategy to the using of GUIDs in stead of integers. This is a simple client induced requirement and I can't change that.

I've added a substitute pk candidate of the RAW(16) to every table in the database and filled each record with a SYS_GUID().
I did the same for the FKs, I added a substitute column for each FK.
Now I'm in the process of linking the FKs to their PKs, by querying the parent table I get the guid/new key for the specific row, after that I want to insert into the substitute candidate FK in the child table.

Somewhat like this:

sqlString = "SELECT PK FROM " + t+ " WHERE " + fkcol+ " = " + childValue;
OracleDataReader guidReader = GetDataReader(sqlString);

while (guidReader.Read())
{
    sqlString = "UPDATE T SET FK = " + guidReader["PK"];
}

Debugging this sqlString gets me the following value:

UPDATE SIS_T_USER SET FK_C_EMPLOYEE_ID
= System.Byte[]

Now, how do I go forth and save this as a nice guid in my oracle database?

EDit how:

OracleCommand command = new OracleCommand(sqlString, this.oracleConnection);
                                command.CommandType = CommandType.Text;
                                OracleParameter op1 = new OracleParameter("guid", OracleDbType.Raw);
                                op1.Value = guidReader["PK"];
                                command.Parameters.Add(op1);
                                try
                                {
                                    command.ExecuteNonQuery();
                                }
                                catch (OracleException oex)
                                {
                                    Console.WriteLine("Unable to update: {0}", oex.Message);
                                }

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

还给你自由 2024-09-06 14:23:40

为什么不在 Oracle 端完成这一切呢？

MERGE
INTO    sis_t_user s
USING   employee e
ON      (s.integer_fk = e.integer_pk)
WHEN MATCHED THEN
UPDATE
SET     s.guid_fk = e.guid_pk

Why don't you just do this all on Oracle side?

MERGE
INTO    sis_t_user s
USING   employee e
ON      (s.integer_fk = e.integer_pk)
WHEN MATCHED THEN
UPDATE
SET     s.guid_fk = e.guid_pk

回复收藏 0 原文

笔芯 2024-09-06 14:23:40

试试这个代码：

sqlString = "UPDATE T SET FK = '" + (new Guid((byte[])guidReader["PK"])).ToString() + "'";

基本上，您只需要从字节创建 guid，然后将其转换为字符串。
有允许它的 Guid 构造函数： http:// /msdn.microsoft.com/en-us/library/90ck37x3(v=VS.100).aspx。

Try this code:

sqlString = "UPDATE T SET FK = '" + (new Guid((byte[])guidReader["PK"])).ToString() + "'";

Basically, you just need to create guid from bytes and then convert it to string.
There is Guid constructor that allows it: http://msdn.microsoft.com/en-us/library/90ck37x3(v=VS.100).aspx.

回复收藏 0 原文

~没有更多了~