PL/SQL:从表中选择一个assoc数组 [英] PL/SQL: Selecting from a table into an assoc array

问题描述

我正在尝试在一个查询中将数据选择到pl/sql关联数组中.我知道我可以使用硬编码的键来做到这一点，但是我想看看是否有某种方式可以引用另一列(键列).

I am trying to select data into a pl/sql associative array in one query. I know I can do this with a hardcoded key, but I wanted to see if there was some way I could reference another column (the key column) instead.

DECLARE
TYPE VarAssoc IS TABLE OF varchar2(2) INDEX BY varchar2(3);
vars VarAssoc;
BEGIN
SELECT foo, bar INTO vars(foo) FROM schema.table;
END;

我收到一条错误消息，说在执行此操作时必须声明foo.有什么方法可以在单个查询中创建我的关联数组，还是需要回退到FOR循环上?

I get an error saying foo must be declared when I do this. Is there some way to create my associate array in a single query or do I need to fall back on a FOR loop?

推荐答案

只需阅读您对APC答案的评论，听起来您是自己想出来的.但是我想我还是会为将来的搜索者提供答案.

Just read your comment on APC's answer, it sounds like you figured this out on your own. But I figured I'd put the answer in anyway for future searchers.

这是更简单的代码，但是没有使用BULK COLLECT的速度优势.只需遍历查询返回的行并分别设置关联数组中的元素即可.

This is simpler code, but does not have the speed advantage of using BULK COLLECT. Just loop through the rows returned by the query and set the elements in the associative array individually.

DECLARE
  TYPE VarAssoc IS TABLE OF varchar2(200) INDEX BY varchar2(30);
  vars VarAssoc;
BEGIN
  FOR r IN (SELECT table_name,tablespace_name FROM user_tables) LOOP
    vars(r.table_name) := r.tablespace_name;
  END LOOP;

  dbms_output.put_line( vars('JAVA$OPTIONS') );
END;

这篇关于PL/SQL:从表中选择一个assoc数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！