SQL问题

feipuppy

请问一下，以下的PL/SQL script， 哪里出了问题？

CREATE OR REPLACE procedure getPostcode (v_postcode IN OUT NUMBER,
v_suburb IN VARCHAR2, v_category IN     VARCHAR2)                                               
                          
IS
BEGIN

   SELECT dbp_postcodes.postcode
   INTO v_postcode
   FROM dbp_postcodes INNER JOIN Helens_data
   ON dbp_postcodes.Locality = Helens_data.Suburb
   WHERE dbp_postcodes.State = 'NSW'
   AND suburb = v_suburb;


END getpostcode;


错误讯息如下：

ERROR at line 1:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "KKKONG.GETPOSTCODE", line 8

[ Last edited by feipuppy on 3-5-2005 at 06:09 PM ]

max^^

是不是你的select statement return超过一个result了？？

feipuppy

max^^ 于 4-5-2005 05:24 PM  说 :
是不是你的select statement return超过一个result了？？

对，我只是要一行而已，要怎么写呢？

dino5100

feipuppy 于 4-5-2005 05:58 PM  说 :


对，我只是要一行而已，要怎么写呢？

试一试这个或用group function.
   SELECT dbp_postcodes.postcode
   INTO v_postcode
   FROM dbp_postcodes INNER JOIN Helens_data
   ON dbp_postcodes.Locality = Helens_data.Suburb
   WHERE dbp_postcodes.State = 'NSW'
   AND suburb = v_suburb
   AND rownum=1;