动态列名称以记录类型变量 [英] Dynamic column name to record type variable

问题描述

 DECLARE
 TYPE t IS RECORD (
 col_name   VARCHAR2 (100)
 );

  t_row   t;
  cname   VARCHAR (100) := 'col_name';
BEGIN
  t_row.col_name             := 'col';
 DBMS_OUTPUT.put_line ('out');

  IF t_row.cname IS NULL THEN
    DBMS_OUTPUT.put_line ('in');
  END IF;
 END;

第1行出现错误
ORA-06550:第12行，第12列:
PLS-00302:必须声明组件"CNAME"
ORA-06550:第12行，第3列:
PL/SQL:语句被忽略

Error at line 1
ORA-06550: line 12, column 12:
PLS-00302: component 'CNAME' must be declared
ORA-06550: line 12, column 3:
PL/SQL: Statement ignored

如何为记录类型变量分配动态列名?

How can I assign dynamic column name to type variable of record?

推荐答案

您可以使用动态sql来做到这一点:
为了使示例更简单，我将为您的类型t提供一个架构对象(但基本上不必这样做-您也可以将其放在动态部分中)

You can do that with dynamic sql:
To make the example simpler I'll make your type t a schema object (but basically you don't have to - you can put it in the dynamic part as well)

create or replace type t is object(col_name varchar2(100));
/

然后您可以看一下此脚本:

Then you can look at this script:

declare
  t_row t;
  cname varchar2(100) := 'col_name';
begin

  t_row := new t('col');

  execute immediate 'declare t_in t := :0; begin if t_in.' || cname ||
                    ' is null then dbms_output.put_line(''in''); end if; end;'
    using t_row;
end;

尽管，我必须说，这是一个奇怪的要求...

Though, I must say, that this is a strange requirement ...

这篇关于动态列名称以记录类型变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！