SQL过程返回太多行 [英] SQL procedure returning too many rows

问题描述

我创建了一个以pid为参数并返回该pid的每月销售统计信息的过程，它使用两个表，产品和购买，其中pid在购买中是外键引用产品中的pid。
该过程没有错误地生成，只对单行返回执行得很好，否则给予too_many_rows异常
我的过程如下：

I've created a procedure that takes pid as parameter and returns monthly sale stats of that pid , it uses two tables, products and purchases where pid in purchases is foreign key referencing pid in products. The procedure builds without errors and executes well for only single row returns and otherwise give too_many_rows exception My procedure is as follows:

set serveroutput on
create or replace procedure try(
p_pid in purchases.pid%type) is
p_pname products.pname%type;
p_date varchar2(10);
p_qty_monthly number(5);
p_amnt_monthly number(7,2);
p_avg_price number(7,2);


begin
select pname, p_time, qty_month, amount_month,(amount_month/qty_month) as avg_sale into p_pname,p_date,p_qty_monthly, p_amnt_monthly, p_avg_price
FROM 
    (select p.pname, to_char(q.ptime,'MON yyyy') p_time, 
    sum(qty) as qty_month, sum(total_price) as amount_month 
    from products p, purchases q
    where p.pid=q.pid
and q.pid= p_pid
group by pname, to_char(q.ptime,'MON yyyy')); 
dbms_output.put_line ('Product name is:'|| p_pname || 'Purchase date is:' || p_date || 'Units sold per month are/is: ' || p_qty_monthly || 'Monthly sale amount: ' || p_amnt_monthly || 'Average sale price is: ' || p_avg_price);
end;
/
show errors

它为一些输入返回多行，如何使它打印所有的行，而不是给too_many_rows异常？

It's returning more than one rows for some inputs, how can I make it print all the rows rather than give too_many_rows exception?

推荐答案

在我看来，你应该使用Ref游标来避免任何一种担心处理没有数据找到或太多行异常。

In my opinion you should use Ref cursor to avoid any kind of worry for handling No Data Found Or Too many Rows Exception. Below snippet will helo you.

CREATE OR REPLACE PROCEDURE m_pro(
    p_pid IN products.pid%type,
    p_lst OUT sys_refcursor) -- Use ref cursor to avoid no data found or too many rows exception
IS
--  p_pname products.pname%type;
--  p_date purchases.ptime%type;
--  p_qty_monthly purchases.qty%type;
--  p_amnt_monthly purchases.total_price%type;
--  p_avg_price purchases.total_price%type;
BEGIN
  OPEN p_lst FOR 
  SELECT pname, 
  p_time, 
  qty_month, 
  amount_month, 
  (amount_month/qty_month)
AS
  avg_sale FROM
  (SELECT p.pname,
    TO_CHAR(q.ptime,'MON yyyy') p_time,
    SUM(qty)         AS qty_month,
    SUM(total_price) AS amount_month
  FROM products p,
    purchases q
  WHERE p.pid=q.pid
  GROUP BY pname,
    TO_CHAR(q.ptime,'MON yyyy')
  ) WHERE P.pid=p_pid;
END; 

这篇关于SQL过程返回太多行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！