oracle PLS-00222：此范围内不存在名为“INFO_TYPE”的函数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/37569470/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
PLS-00222: no function with name 'INFO_TYPE' exists in this scope
提问by mikcutu
I am trying to return a table of records in 2 situations:
我试图在两种情况下返回一个记录表：
- using a function
- using a anonymous block
- 使用函数
- 使用匿名块
When I am using the function, everything is working just fine but when I am trying to transform it into anonymous block i receive the above error. Here are the codes:
当我使用该函数时，一切正常，但是当我尝试将其转换为匿名块时，我收到上述错误。以下是代码：
For the function:
对于函数：
create or replace
function get_info(p_city varchar2) return info_type_table
as
l_info info_type_table := info_type_table();
begin
for i in (select e.employeeid,
e.lastname,
c.customerid,
c.companyname,
o.orderid,
o.orderdate
from ntw_employees e
inner join
ntw_orders o
on e.employeeid = o.employeeid
inner join
ntw_customers c
on o.customerid = c.customerid
where e.city = p_city)
loop
l_info.extend;
l_info(l_info.count) := (info_type(i.employeeid, i.lastname, i.customerid, i.companyname, i.orderid, i.orderdate));
end loop;
return l_info;
end;
And here is for anonymous block:
这是匿名块：
declare
type info_type is record
(
emp_no number(3),
lastname varchar2(26),
cust_no varchar2(5),
CO_name varchar2(50),
orderid number(5),
orderdate date
);
type info_type_table is table of info_type;
l_info info_type_table := info_type_table();
begin
for i in (select e.employeeid,
e.lastname,
c.customerid,
c.companyname,
o.orderid,
o.orderdate
from ntw_employees e
inner join
ntw_orders o
on e.employeeid = o.employeeid
inner join
ntw_customers c
on o.customerid = c.customerid
where e.city = 'London')
loop
l_info.extend;
l_info(l_info.count) := (info_type(i.employeeid, i.lastname, i.customerid, i.companyname, i.orderid, i.orderdate));
dbms_output.put_line('angajat = ' || i.employeeid);
end loop;
end;
Can anyone explain me what is wrong in my anonymous block, please?
谁能解释一下我的匿名块出了什么问题？
Thank you.
谢谢你。
回答by Alex Poole
Your function is (presumably) referring to an object type called info_type. Your anonymous block is using a record type. Record types do not have constructors. You have to assign each column individually, and have a record-type variable:
您的函数（大概）是指称为info_type. 您的匿名块正在使用记录类型。记录类型没有构造函数。您必须单独分配每一列，并有一个记录类型变量：
...
l_info_rec info_type;
begin
...
loop
l_info.extend;
l_info_rec.emp_no := i.employeeid;
l_info_rec.lastname := i.lastname;
l_info_rec.cust_no := i.customerid;
l_info_rec.CO_name := i.companyname;
l_info_rec.orderid := i.orderid;
l_info_rec.orderdate := i.orderdate;
l_info(l_info.count) := l_info_rec;
dbms_output.put_line('angajat = ' || i.employeeid);
end loop;
end;
You could also have an explicit cursor and use the row that returns:
您还可以使用显式游标并使用返回的行：
declare
cursor c is select e.employeeid,
e.lastname,
c.customerid,
c.companyname,
o.orderid,
o.orderdate
from ntw_employees e
inner join
ntw_orders o
on e.employeeid = o.employeeid
inner join
ntw_customers c
on o.customerid = c.customerid
where e.city = 'London';
type info_type_table is table of c%rowtype;
l_info info_type_table := info_type_table();
begin
for r in c
loop
l_info.extend;
l_info(l_info.count) := r;
dbms_output.put_line('angajat = ' || r.employeeid);
end loop;
end;
/
Or even with bulk collectstraight into your table type:
或者甚至bulk collect直接进入您的表格类型：
declare
cursor c is select e.employeeid,
...
where e.city = 'London';
type info_type_table is table of c%rowtype;
l_info info_type_table;
begin
open c;
fetch c bulk collect into l_info;
close c;
for i in 1..l_info.count loop
dbms_output.put_line('angajat = ' || l_info(i).employeeid);
end loop;
end;
/
... with a loop just to display the values from that table.
... 用一个循环来显示该表中的值。
回答by Avrajit Roy
Basically row-by-row processing is similar to slow-by-slow processing. Always try to use BULK collect where ever possible to avoid system performance glitch. Hope below snippet helps.
基本上逐行处理类似于逐慢处理。始终尝试尽可能使用 BULK 收集以避免系统性能故障。希望下面的片段有帮助。
--Function with Bulk collect
CREATE OR REPLACE FUNCTION get_info(
p_city VARCHAR2)
RETURN info_type_table
AS
l_info info_type_table := info_type_table();
BEGIN
SELECT e.employeeid,
e.lastname,
c.customerid,
c.companyname,
o.orderid,
o.orderdate
BULK COLLECT -- Using bulk collect for the performance
INTO l_info
FROM ntw_employees e
INNER JOIN ntw_orders o
ON e.employeeid = o.employeeid
INNER JOIN ntw_customers c
ON o.customerid = c.customerid
WHERE e.city = p_city;
RETURN l_info;
END;
----Anonymous block with Bulk collect
DECLARE
type info_type
IS
record
(
emp_no NUMBER(3),
lastname VARCHAR2(26),
cust_no VARCHAR2(5),
CO_name VARCHAR2(50),
orderid NUMBER(5),
orderdate DATE );
type info_type_table
IS
TABLE OF info_type;
l_info info_type_table; -- default constructor not required in record type case
BEGIN
SELECT e.employeeid,
e.lastname,
c.customerid,
c.companyname,
o.orderid,
o.orderdate
BULK COLLECT
INTO
l_info
FROM ntw_employees e
INNER JOIN ntw_orders o
ON e.employeeid = o.employeeid
INNER JOIN ntw_customers c
ON o.customerid = c.customerid
WHERE e.city = 'London';
FOR I IN l_info.FIRST..l_info.LAST LOOP
dbms_output.put_line('angajat = ' || l_info(i).employeeid);
END LOOP;
END;
