将值分配给行类型的字段，其中“字段名称"是字符串 [英] Assign value to a field of rowtype where `field name` is a string

问题描述

我想为行类型的字段分配一个值，但是我不知道该怎么做.

I want to assign a value to a rowtype's field but I don't know how to do it.

假设我的数据库中有一个表 X .

Suppose that I have a table X inside my database.

还假设我具有以下变量

• a(X%ROWTYPE)，代表表X的一行
• b(VARCHAR2)，其中包含表X的列名
• c(VARCHAR2)，其中包含我要存储在a.b中的内容

• a ( X%ROWTYPE ), representing a row of the table X
• b ( VARCHAR2 ), containing a column name of the table X
• c ( VARCHAR2 ), containing what I want to store inside a.b

我想做什么:类似于a.b := c.

我想出了类似这样的东西:

I've come up with something like this :

EXECUTE IMMEDIATE 'SELECT '|| c || ' INTO a.' || b || ' FROM DUAL';

显然，这不是正确的方法.我收到 ORA-0095:缺少关键字错误.

Apparently, this isn't the right way to go. I get a ORA-0095: missing keyword error.

有人可以帮我吗?

这是完整的代码:

DECLARE
    tRow            MyTable%ROWTYPE;
    col_name        VARCHAR(10) := 'Length';
    nValue          NUMBER(12,4) := 0.001;
    dynamic_request VARCHAR(300);
BEGIN 
    dynamic_request := 'SELECT '|| nValue || ' INTO tRow.' || col_name || ' FROM DUAL';
    EXECUTE IMMEDIATE dynamic_request;
END;

推荐答案

好，我解决了！

简短答案:使用全局变量可以解决问题

Short answer : Using a global variable does the trick

答案开发

让我们考虑一下有关动态PL/SQL块的两个事实(即，通过EXECUTE IMMEDIATE语句执行的以字符串形式编写的PL/SQL块)

Let us consider two facts about dynamic PL/SQL blocks (i.e., PL/SQL blocks written as strings, to be executed trough an EXECUTE IMMEDIATE statement)

[1] 创建动态PLSQL块时，没有变量作用域之类的东西.我的意思是，如果您这样做:

[1] There is no such thing as variable scope when you create a dynamic PLSQL block. What I mean by that is, if you do something like this :

CREATE OR REPLACE PROCEDURE DynamicVariableAssignment(
   theString IN VARCHAR2
 ) 
IS
BEGIN 
   EXECUTE IMMEDIATE 'BEGIN theString := ''test''; END; ';
END;

它根本不起作用，因为theString的范围未转移到动态PL/SQL块中.换句话说，动态PL/SQL块无论在哪里执行，都不会继承"任何变量.

it will simply not work because the scope of theString is not transfered to the dynamic PL/SQL block. In other words, the dynamic PL/SQL block doesn't "inherit" of any variable, wherever it is executed.

[2] 您可能会说:好吧，不用担心，我可以为动态PL/SQL块提供输入/输出参数，对吗?".当然可以，但是请猜测:您只能将SQL类型指定为in/out！另一方面，不接受真正的PL/SQL类型(例如myTable%rowtype)作为动态PL/SQL块的输入.所以 hmmftg 的答案也不会起作用:

[2] You might say "OK, no panic, I can give input/output arguments to my dynamic PL/SQL block, right ?". Sure you can, but guess what : you can only give SQL types as in/out ! True PL/SQL types on the other hand, such as a myTable%rowtype, are not accepted as an input for a dynamic PL/SQL block. So the answer of hmmftg won't work either :

-- I've reduced the code to the interesting part
dynamic_request := 'BEGIN :t_row.' || col_name || ':= 0.001; END;';
EXECUTE IMMEDIATE dynamic_request USING IN OUT tRow;
-- (where tRow is of type myTable%ROWTYPE)

由于tRow属于MyTable％ROWTYPE，因此它不是有效的SQL类型，因此作为动态PL/SQL块的输入无效.

since tRow is of MyTable%ROWTYPE, it is not a valid SQL type and is therefore not valid as an input to the dynamic PL/SQL block.

解决方案?谁会想到全局变量会来拯救这一天呢?正如我们在 [1] 中所说的那样，我们没有引用动态PL/SQL块之外的任何变量.但是我们仍然可以访问包头中定义的全局变量！

The Solution Who would have thought that global variables would come and save the day ? As we said in [1], we have no reference to any variable outside the dynamic PL/SQL block. BUT we can still access global variables defined in package headers !

让我们假设我有一个软件包kingPackage，其中定义了以下内容:

Let us assume that I have a package kingPackage in which I define the following :

tempVariable  myTable%ROWTYPE;

然后我可以这样做:

-- Copy tRow into temp variable
kingPackage.tempVariable := tRow;

-- We modify the column of the temp variable
vString := 'BEGIN kingPackage.tempVariable.' || col_val || ' := ' || TO_CHAR(vNumber) ||'; END;'; 
EXECUTE IMMEDIATE vString;    

-- The column value has been updated \o/ 
tRow := kingPackage.tempVariable;

你去了，伙计们！ 祝你有美好的一天

There you go, fellas ! Have a nice day

这篇关于将值分配给行类型的字段，其中“字段名称"是字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！