编译这个存储过程,pl/sql马上就无响应了解决方案
编译这个存储过程,pl/sql马上就无响应了
这个过程一开始放在包里面编译就无响应了,拿出来单独运行也是这样.在会话中查看已经被锁死了,不知道是什么原因,
各位帮帮忙
create procedure findGuestById(
hid in pss_guest_data.id%type,
hguestid out pss_guest_data.id%type,
hguestname out pss_guest_data.name%type,
hguesttypeid out pss_guest_data.guesttypeid%type,
hguestTypename out pss_guest_type_data.name%type,
hcontact out pss_guest_data.contact%type,
hpersonalphone out pss_guest_data.personalphone%type,
hmobilephone out pss_guest_data.mobilephone%type,
hemail out pss_guest_data.email%type
)
is
begin
select a.id as guestid,a.name as guestname,a.guesttypeid as guesttypeid,b.name as guesttypename,
a.contact,nvl(a.personalphone,'无联系电话'),nvl(a.mobilephone,'无联系手机'),nvl(a.email,'无邮箱地址')
into hguestid,hguestname,hguesttypeid,hguestTypename,hcontact,hpersonalphone,hmobilephone,hemail
from pss_guest_data a
inner JOIN pss_guest_type_data b on(a.guesttypeid=b.id)
where a.id=hid;
end findGuestById;
------解决方案--------------------
这个过程一开始放在包里面编译就无响应了,拿出来单独运行也是这样.在会话中查看已经被锁死了,不知道是什么原因,
各位帮帮忙
create procedure findGuestById(
hid in pss_guest_data.id%type,
hguestid out pss_guest_data.id%type,
hguestname out pss_guest_data.name%type,
hguesttypeid out pss_guest_data.guesttypeid%type,
hguestTypename out pss_guest_type_data.name%type,
hcontact out pss_guest_data.contact%type,
hpersonalphone out pss_guest_data.personalphone%type,
hmobilephone out pss_guest_data.mobilephone%type,
hemail out pss_guest_data.email%type
)
is
begin
select a.id as guestid,a.name as guestname,a.guesttypeid as guesttypeid,b.name as guesttypename,
a.contact,nvl(a.personalphone,'无联系电话'),nvl(a.mobilephone,'无联系手机'),nvl(a.email,'无邮箱地址')
into hguestid,hguestname,hguesttypeid,hguestTypename,hcontact,hpersonalphone,hmobilephone,hemail
from pss_guest_data a
inner JOIN pss_guest_type_data b on(a.guesttypeid=b.id)
where a.id=hid;
end findGuestById;
------解决方案--------------------
- SQL code
create procedure findGuestById( hid in pss_guest_data.id%type, hguestid out pss_guest_data.id%type, hguestname out pss_guest_data.name%type, hguesttypeid out pss_guest_data.guesttypeid%type, hguestTypename out pss_guest_type_data.name%type, hcontact out pss_guest_data.contact%type, hpersonalphone out pss_guest_data.personalphone%type, hmobilephone out pss_guest_data.mobilephone%type, hemail out pss_guest_data.email%type ) is begin select a.id as guestid , a.name as guestname , a.guesttypeid as guesttypeid , b.name as guesttypename , a.contact , nvl(a.personalphone,'phone inval') , nvl(a.mobilephone,'cell inval') , nvl(a.email,'email inval') into hguestid , hguestname , hguesttypeid , hguestTypename , hcontact , hpersonalphone , hmobilephone , hemail from pss_guest_data a , pss_guest_type_data b where a.guesttypeid = b.id and a.id = hid; end findGuestById;
------解决方案--------------------
支持楼上的
------解决方案--------------------
------解决方案--------------------
注意 select into 在存储过程中如果没有数据或者数据大于一条的情况下会抛异常(no_data_found/to_many_data)
你最好加个异常处理。
------解决方案--------------------
应该是这两个表有被锁的情况吧
另外,如果select结果有两条会出错