将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('khwaja@gmail.com','sayya@gmail.com','mayya@gmail.com');
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('khwaja@gmail.com','sayya@gmail.com','mayya@gmail.com');
friends := friend_array('kwaja','sayya','mayya');
update_address_book('1',friends,emails);
end;
/
这篇关于将varray变量传递到存储过程 - 基本的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!