英文:
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;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论