在PL/SQL过程中创建视图

Question

我在论坛上看了一下，但目前还没有这个问题的答案。基本上，我试图在我的过程中创建一个视图，因为它比复杂的select语句更容易，因为它返回的'ORA-01422EXACTE fetch返回的行数超过了请求的行数'，所以它似乎不起作用。我把该语句放在底部以供参考

procedure proRekt (par_id number) is 
  vardec farm.parcel.description%type;
  vland farm.landuse.landuse_id%type;
  v_luse farm.landuse.landuse%type;
  varpl farm.parcel.parcel_id%type;

begin  
 create or replace view Door as
select a.parcel_id,a.description,b.landuse_id,c.landuse
from 
     farm.parcel a,
     farm.parcel_landuse b,
     farm.landuse c
where a.parcel_id = b.parcel_id
  and b.landuse_id = c.landuse_id;

 select parcel_id,description,landuse
    into varpl,
         vardec,
         vland
  from   door
  where  parcel_id = parID;

select a.parcel_id,c.description,a.landuse_id,b.landuse
into   varpl,
       vardec,
       vland,
       v_luse
from   farm.parcel_landuse a,
       farm.landuse b,
       farm.parcel c
where  c.parcel_id = parID
  and  a.landuse_id = b.landuse_id
  and  a.parcel_id = c.parcel_id;

Answer 1

在SQL范围内创建视图：

create or replace view Door as
select a.parcel_id,
       a.description,
       b.landuse_id,
       c.landuse
from   tithedb.parcel a
       INNER JOIN tithedb.parcel_landuse b
       ON ( a.parcel_id = b.parcel_id )
       INNER JOIN tithedb.landuse c
       ON ( b.landuse_id = c.landuse_id );

在过程的PL/SQL范围内使用它：

procedure proexempt (parID number)
is 
  vardescription tithedb.parcel.description%type;
  varland tithedb.landuse.landuse_id%type;
  varlanduse tithedb.landuse.landuse%type;
  varparcel tithedb.parcel.parcel_id%type;
begin  
  select parcel_id,
         description,
         landuse
  into   varparcel,
         vardescription,
         varlanduse
  from   door
  where  parcel_id = parID;

  -- ... more stuff
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    NULL; -- Handle error here.
  WHEN TOO_MANY_ROWS THEN
    NULL; -- Handle error here (or use "AND ROWNUM = 1" in your query).
END proexempt;
/