oracle 将 varray 变量传递给存储过程 - 基本
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4892480/
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
Pass varray variables into stored procedure - basic
提问by macha
Hello I am a php developer, trying to get going with Oracle. So I need to pass a collection of variables into an Oracle stored procedure. So as a basic try, I am trying to access a procedure which would accept three parameters, out of which two would be varrays, but when I pass the declared varrays, I am getting an error. I am pretty sure, it is something to do with a little syntax, but i am not able to figure out that thing.
您好，我是一名 php 开发人员，正在尝试使用 Oracle。所以我需要将一组变量传递给 Oracle 存储过程。所以作为一个基本的尝试，我试图访问一个接受三个参数的过程，其中两个是 varrays，但是当我传递声明的 varrays 时，我收到一个错误。我很确定，这与一些语法有关，但我无法弄清楚那件事。
Below is my table schema and stored procedure:
下面是我的表架构和存储过程：
create table emails (
user_id varchar2(10),
friend_name varchar2(20),
email_address varchar2(20));
create or replace type email_array as varray(100) of varchar2(20);
/
show errors
create or replace type friend_array as varray(100) of varchar2(20);
/
show errors
create or replace procedure update_address_book(
p_user_id in varchar2,
p_friend_name friend_array,
p_email_addresses email_array)
is
begin
delete from emails where user_id = p_user_id;
forall i in indices of p_email_addresses
insert into emails (user_id, friend_name, email_address)
values (p_user_id, p_friend_name(i), p_email_addresses(i));
end update_address_book;
Now, below pasted is my the way I am trying to access this procedure from an anonymous block.
现在，下面粘贴的是我试图从匿名块访问此过程的方式。
declare
type email_list is varray(100) of varchar2(20);
type friend_list is varray(100) of varchar2(20);
emails email_list;
friends friend_list;
begin
emails :=email_list('[email protected]','[email protected]','[email protected]');
friends := friend_list('kwaja','sayya','mayya');
execute update_address_book('1',emails,friends);
end;
The error I am getting is near the execute, I think I am not supposed to execute a procedure inside a declare block, but I am unable to understand how would I work around.
我得到的错误接近执行，我认为我不应该在声明块内执行过程，但我无法理解我将如何解决。
回答by Alex Poole
You don't need the keyword EXECUTEto call the procedure from within a PL/SQL block. Just remove that word.
您不需要关键字EXECUTE从 PL/SQL 块中调用过程。去掉那个词就行了。
But it would be helpful if you showed the actual error(s) you're getting as there could be something else wrong. For example, you also have the parameters to the update_address_book()call in the wrong order, and you're recreating new types inside your block instead of using the ones declared earlier.
但是，如果您显示了您遇到的实际错误，那将会很有帮助，因为可能还有其他错误。例如，您还具有以update_address_book()错误顺序调用的参数，并且您正在块中重新创建新类型，而不是使用之前声明的类型。
This will run:
这将运行：
declare
emails email_array;
friends friend_array;
begin
emails := email_array('[email protected]','[email protected]','[email protected]');
friends := friend_array('kwaja','sayya','mayya');
update_address_book('1',friends,emails);
end;
/
