sqlplus在调用存储过程后停止响应

Question

create or replace procedure pizza_qty(order_num IN order_list.order_no%type)  is
cursor c1 is 
select order_no,qty from order_list; 
temp1 order_list.order_no%type;
pizzacount order_list.qty%type;
temp2 order_list.qty%type;
begin
open c1;
loop
fetch c1 into temp1,temp2;
if order_num=temp1 then
pizzacount := pizzacount+1;
--exit;
elsif c1%notfound and pizzacount=0 then
dbms_output.put_line('the order '||order_num||' is invalid..');
exit;
end if;
end loop;
dbms_output.put_line('the order '||order_num||' has '||pizzacount||' pizza(s) to be delivered');
close c1;
end;
/

Answer 1

您编写的代码是。。。嗯，很奇怪。它假设那张桌子上的所有订单都应该只包含披萨。不过，这不是你问题的原因。

声明变量pizzacount会将其设置为null。任何+ null仍然是null，所以它从未设置为其他任何内容，所以循环永远不会退出。

此外，您不需要光标；一个简单的CCD_ 5就足够了。如果它返回该订单号的东西，那么这些一定是披萨。否则，它根本不会返回任何内容，因此订单号无效。

类似于这样：测试用例优先：

SQL> create table order_list (order_no number, qty number);
Table created.
SQL> insert into order_list (order_no, qty) values (1, 5);
1 row created.
SQL>

程序：

SQL> create or replace procedure pizza_qty (par_order_num in order_list.order_no%type)
2  is
3    l_qty order_list.qty%type;
4  begin
5    select qty
6      into l_qty
7      from order_list
8      where order_no = par_order_num;
9
10    dbms_output.put_line('Order ' || par_order_num || ' has ' || l_qty ||
11                         ' pizza(s) to be delivered');
12  exception
13    when no_data_found then
14      dbms_output.put_line('Order ' || par_order_num || ' is invalid');
15  end;
16  /
Procedure created.
SQL>

测试：

SQL> set serveroutput on;
SQL> exec pizza_qty(1);
Order 1 has 5 pizza(s) to be delivered
PL/SQL procedure successfully completed.
SQL> exec pizza_qty(2);
Order 2 is invalid
PL/SQL procedure successfully completed.
SQL>