我想比较表OS_CW.CW.CW.FELDDATEN属性的pripitivumnummer(不是null号(38((与本地数组中的值v_list_pNummber
中的值。这就是我到目前为止的:
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;
运行上面的代码时,我会收到此错误:
错误报告 -
ORA-06550:第28行,第104列:
PLS-00382:表达式是错误的类型
06550。00000-"线%s,列%s: n%s"
*原因:通常是PL/SQL汇编错误。
*动作:
如何将列(编号(与binary_integer的表号索引进行比较?
谢谢!
sql不能使用本地PL/SQL范围中声明的类型。您需要在SQL (*(中定义它:
SQL> create TYPE array_of_numbers IS TABLE OF NUMBER ;
2 /
Type created.
SQL>
然后使用table((运算符将第一个集合转换为一个子查询,您可以使用in oterator参考:
: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>
语法的成员也有效。它的打字率较小,但如果CW_FELDDATEN有很多行,则可能不如表((运算符。
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中的软件包规范中声明的类型。
您需要使用MEMBER OF
子句在SQL语句中构建inlist
。另请注意,不可能在匿名块中创建类型,并像您这样做的那样在子句中的SQL语句中使用它。您必须声明PLSQL块外部的类型,然后使用它:
CREATE OR REPLACE TYPE array_of_numbers IS TABLE OF NUMBER ;
/
DECLARE
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 MEMBER OF v_list_parentID;
END;
在此处查看更多:http://www.oracle-developer.net/display.php?id=301