在Oracle中按记录字段的名称访问记录字段

Question

我想知道PL/SQL中是否存在类似Reflection API的东西。

我有一张类似的桌子

create table my_table (
id number,
value1 number,
value2 number, 
value3 number,
value4 number,
value5 number);

我有一个变量

rec as my_table%rowtype
... fill rec
insert into my_table values rec;

有没有任何方法可以通过名称动态填充CCD_ 1字段。

我的意思是，我知道索引(在这种情况下是1到5之间的值(，所以我想将'value'||index设置为某个值。

在我的实际情况中，最后一个索引远大于5，使用一组if/elsif是不合适的。顺便说一下，从长远来看，字段的数量会增加(例如，value6、value7可能会在明年增加，等等，所以我想写一些代码，不要在每一列上都更改(。

Answer 1

只有在全局可用的情况下，才能使用动态SQL访问程序中的变量。如果在规范中声明记录，则可以构建实用程序函数，该函数将使用EXECUTE IMMEDIATE来构建一个小的PL/SQL块来设置值。这里有一个简单的例子，说明你在寻找什么。请注意，您可以重载set过程以保持数据类型不变。

CREATE TABLE my_table (
value1 NUMBER,
value2 VARCHAR2(100), 
value3 DATE);
CREATE OR REPLACE PACKAGE pkg_my_table IS
my_table_rec my_table%ROWTYPE;
FUNCTION build_statement(i_record IN VARCHAR2,
i_field  IN VARCHAR2) RETURN VARCHAR2;
PROCEDURE set_value(i_record IN VARCHAR2,
i_field  IN VARCHAR2,
i_value  IN VARCHAR2);
PROCEDURE set_value(i_record IN VARCHAR2,
i_field  IN VARCHAR2,
i_value  IN NUMBER);
PROCEDURE set_value(i_record IN VARCHAR2,
i_field  IN VARCHAR2,
i_value  IN DATE);
PROCEDURE insert_a_row;
END pkg_my_table;
/
CREATE OR REPLACE PACKAGE BODY pkg_my_table IS
FUNCTION build_statement(i_record IN VARCHAR2,
i_field  IN VARCHAR2) RETURN VARCHAR2 IS
BEGIN
RETURN 'BEGIN ' || lower($$PLSQL_UNIT) || '.' || i_record || '.' || i_field || ' := :x; END;';
END build_statement;
PROCEDURE set_value(i_record IN VARCHAR2,
i_field  IN VARCHAR2,
i_value  IN VARCHAR2) IS
BEGIN
EXECUTE IMMEDIATE build_statement(i_record => i_record,
i_field  => i_field)
USING i_value;
END set_value;
PROCEDURE set_value(i_record IN VARCHAR2,
i_field  IN VARCHAR2,
i_value  IN NUMBER) IS
BEGIN
EXECUTE IMMEDIATE build_statement(i_record => i_record,
i_field  => i_field)
USING i_value;
END set_value;
PROCEDURE set_value(i_record IN VARCHAR2,
i_field  IN VARCHAR2,
i_value  IN DATE) IS
BEGIN
EXECUTE IMMEDIATE build_statement(i_record => i_record,
i_field  => i_field)
USING i_value;
END set_value;
PROCEDURE insert_a_row IS
BEGIN
my_table_rec := NULL;
set_value(i_record => 'my_table_rec',
i_field  => 'value1',
i_value  => 42);
set_value(i_record => 'my_table_rec',
i_field  => 'value2',
i_value  => 'forty-two');
set_value(i_record => 'my_table_rec',
i_field  => 'value3',
i_value  => to_date('1/1/1942',
'mm/dd/yyyy'));
INSERT INTO my_table
VALUES my_table_rec;
END insert_a_row;
END pkg_my_table;
/
BEGIN
pkg_my_table.insert_a_row;
END;
/
SELECT *
FROM my_table;
-- 42   forty-two   1/1/1942

小心全局变量：在再次使用它们之前，您需要正确地重置它们，否则您可能会从以前的调用中获得数据。将整个记录类型变量设置为NULL将重置所有基础字段(方便(。

您还将容易出现ORA-04068：如果您使用全局变量编译PL/SQL，其中存在引用代码的活动会话，则会出现现有包状态已被丢弃的错误。一般来说，这不是问题，但这是一种行为差异。