时间戳

首页 > 解决方案 > 在 Oracle 中按名称访问记录字段

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

问题描述

我想知道 PL/SQL 中是否存在诸如反射 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;

有什么方法可以rec通过名称动态填充字段。

我的意思是我知道索引（在这种情况下，介于 1 和 5 之间），所以我想设置'value'||index一些东西。

在我的真实情况下，最后一个索引远大于 5，使用一组if/elsif是不合适的。顺便说一句，长期增加字段的数量（例如value6，value7明年可能会添加等等，所以我想写一些代码不会在每个新列上更改）。

标签： oracleplsqlreflection

解决方案

只有在全局可用的情况下，才能使用动态 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 将重置所有基础字段（方便）。

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

推荐阅读