如何选择到一个由VARCHAR2索引的NUMBER表格中。

huangapple go评论63阅读模式
英文:

How to Select Into a TABLE OF NUMBER INDEX BY VARCHAR2

问题

我有一个存储过程。与其将数据批量收集到 v_value_list 中,我想要将列名作为索引,如下所示:

v_values['DIST_TABLE.APPRVD'] = val1,
v_values['DIST_TABLE.TOT_CAP'] = val2,
v_values['DIST_TABLE.NEW_CAP'] = val3,

等等。我们可以使用 values_table_type(请参见下文)进行批量收集以实现这一目标吗?

我的存储过程:

PROCEDURE pr_capacities_update(id_in IN NUMBER) 
AS
v_value_list SYS.ODCINUMBERLIST;
v_values values_table_type;
TYPE values_table_type IS TABLE OF NUMBER INDEX BY VARCHAR2(100);
v_values values_table_type;
BEGIN
    SELECT val
    BULK COLLECT INTO v_value_list
    FROM (
        SELECT val
        FROM DIST_TABLE
        UNPIVOT (val FOR col IN (APPRVD, TOT_CAP, NEW_CAP, EXIST_CAP))
        WHERE DIST_ID = id_in
    );
END pr_capacities_update;

如果还有其他问题,请随时提出。

英文:

I have a stored procedure. Instead of bulk collect into v_value_list, I want it to have a column name as index, like:

v_values['DIST_TABLE.APPRVD'] = val1,
v_values['DIST_TABLE.TOT_CAP'] = val2,
v_values['DIST_TABLE.NEW_CAP'] = val3,

etc. Can we use the Bulk Collect Into the values_table_type (see below) to achieve this?

My procedure:

PROCEDURE pr_capacities_update(id_in IN NUMBER) 
AS
v_value_list SYS.ODCINUMBERLIST;
v_values values_table_type;
TYPE values_table_type IS TABLE OF NUMBER INDEX BY VARCHAR2(100);
v_values values_table_type;
BEGIN
    SELECT val
    BULK COLLECT INTO v_value_list
    FROM (
        SELECT val
        FROM DIST_TABLE
        UNPIVOT (val FOR col IN (APPRVD, TOT_CAP, NEW_CAP, EXIST_CAP))
        WHERE DIST_ID = id_in
    );
END pr_capacities_update;

答案1

得分: 1

无法,但一个简单的FOR循环将处理它并提供相同的数组提取优势。

PROCEDURE pr_capacities_update(id_in IN NUMBER) 
AS
v_value_list SYS.ODCINUMBERLIST;
v_values values_table_type;
TYPE values_table_type IS TABLE OF NUMBER INDEX BY VARCHAR2(100);
v_values values_table_type;
BEGIN
  for i in (
    SELECT key, value
    FROM ... ) 
  loop
      v_values(i.key) := i.value;
  end loop;
END pr_capacities_update;
英文:

You can't, but a simple FOR-LOOP will take care of it and give the same array fetching benefits

PROCEDURE pr_capacities_update(id_in IN NUMBER) 
AS
v_value_list SYS.ODCINUMBERLIST;
v_values values_table_type;
TYPE values_table_type IS TABLE OF NUMBER INDEX BY VARCHAR2(100);
v_values values_table_type;
BEGIN
  for i in (
    SELECT key, value
    FROM ... ) 
  loop
      v_values(i.key) := i.value;
  end loop;
END pr_capacities_update;

huangapple
  • 本文由 发表于 2023年7月17日 09:48:14
  • 转载请务必保留本文链接:https://go.coder-hub.com/76701080.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定