如何在SELECT INTO语句中使用数字表 [英] How to use table of number in SELECT INTO Statment
问题描述
我想将表OS_CW.CW_FELDDATEN属性的PRIMITIVUMNUMMER(NOT NULL NUMBER(38))与本地数组v_list_pNummber
中的值进行比较.这是我到目前为止的内容:
I'd like to compare the PRIMITIVUMNUMMER (NOT NULL NUMBER(38)) of table OS_CW.CW_FELDDATEN attribute with the values in a local array, v_list_pNummber
. This is what I have so far:
DECLARE
TYPE array_of_numbers IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
v_list_parentID array_of_numbers;
v_list_pNummer array_of_numbers;
BEGIN
SELECT DBUID BULK COLLECT INTO v_list_parentID
FROM OS_SYS.V_CATALOG
WHERE PARENTID = 1;
SELECT PRIMITIVUMNUMMER BULK COLLECT INTO v_list_pNummer
FROM OS_CW.CW_FELDDATEN
WHERE KATALOG IN (v_list_parentID);
END;
运行上面的代码时,出现此错误:
When running the code above I get this error:
错误报告-
ORA-06550:第28行,第104列:
PLS-00382:表达式的类型错误
06550.00000-%s行,%s列:\ n%s"
*原因:通常是PL/SQL编译错误.
*操作:
Error report -
ORA-06550: line 28, column 104:
PLS-00382: expression is of wrong type
06550. 00000 - "line %s, column %s:\n%s"
*Cause: Usually a PL/SQL compilation error.
*Action:
如何比较列(数字)和表NUMBER INDEX BY BINARY_INTEGER?
How can I compare a column (number) with a table NUMBER INDEX BY BINARY_INTEGER?
谢谢!
推荐答案
SQL无法使用在本地PL/SQL范围内声明的类型.您需要在SQL (*)中定义它:
SQL cannot use types declared in local PL/SQL scope. You need to define it in SQL (*):
SQL> create TYPE array_of_numbers IS TABLE OF NUMBER ;
2 /
Type created.
SQL>
然后使用TABLE()运算符将第一个集合转换为可以使用IN运算符引用的子查询:
Then use the TABLE() operator to convert the first collection into a sub-query you can reference with the IN operator:
SQL> set serveroutput on
SQL> declare
2 v_list_parentID array_of_numbers;
3 v_list_pNummer array_of_numbers;
4 begin
5 select dbuid bulk collect into v_list_parentID
6 from v_catalog
7 where parentid = 1;
8 dbms_output.put_line('v_list_parentID count = ' || v_list_parentID.count());
9
10 select primitivumnummer bulk collect into v_list_pNummer
11 from cw_felddaten
12 where katalog in (select * from table( v_list_parentID));
13
14 dbms_output.put_line('v_list_pNummer count = ' || v_list_pNummer.count());
15 end;
16 /
v_list_parentID count = 4
v_list_pNummer count = 24
PL/SQL procedure successfully completed.
SQL>
MEMBER OF语法也适用.如果CW_FELDDATEN的行很多,它的键入较少,但性能可能不如TABLE()运算符.
The MEMBER OF syntax works too. It's less typing but may not perform as well as the TABLE() operator if CW_FELDDATEN has a lot of rows.
SQL> declare
2 v_list_parentID array_of_numbers;
3 v_list_pNummer array_of_numbers;
4 begin
5 select dbuid bulk collect into v_list_parentID
6 from v_catalog
7 where parent_id = 1;
8 dbms_output.put_line('v_list_parentID count = ' || v_list_parentID.count());
9
10 select primitivumnummer bulk collect into v_list_pnummer
11 from cw_felddaten
12 where katalog member of v_list_parentID;
13
14 dbms_output.put_line('v_list_pNummer count = ' || v_list_pNummer.count());
15 end;
16 /
v_list_parentID count = 4
v_list_pNummer count = 24
PL/SQL procedure successfully completed.
SQL>
(*)在12c中,我们可以使用在SQL的包规范中声明的类型.
(*) In 12c we can use types declared in a package spec in SQL.
这篇关于如何在SELECT INTO语句中使用数字表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!