将varray变量传递到存储过程 - 基本 [英] Pass varray variables into stored procedure - basic

问题描述

您好，我是一个PHP开发人员，试图与Oracle一起去。所以我需要传递一个变量集合到一个Oracle存储过程。所以作为一个基本的尝试，我试图访问一个过程，将接受三个参数，其中两个将是varray，但是当我通过声明的varray，我得到一个错误。我很肯定，这是一个小的语法，但我不能弄清楚这一点。

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.

下面是我的表模式和存储过程：

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;

现在，下面粘贴的是我的方式，我试图从一个匿名块访问此过程。 / p>

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('[e-mail]','[e-mail]','[e-mail]');
 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.

推荐答案

您不需要关键字 EXECUTE 从PL / SQL中调用过程块。只要删除那个词。

You don't need the keyword EXECUTE to call the procedure from within a PL/SQL block. Just remove that word.

但是，如果你显示实际的错误，你会得到，因为可能有其他错误。例如，您还具有以 update_address_book（）调用的参数的顺序错误，并且您正在块中重新创建新类型，而不是使用先前声明的类型。

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.

这将运行：

declare 
    emails email_array;
    friends friend_array;
begin
    emails := email_array('[e-mail]','[e-mail]','[e-mail]');
    friends := friend_array('kwaja','sayya','mayya');
    update_address_book('1',friends,emails);
end;
/

这篇关于将varray变量传递到存储过程 - 基本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！