使用 count(*) 在 SQL 过程中获取多行

Question

我正在尝试使用函数和过程返回每个invoice_id的行数。有些invoice_id有多个行，我不确定在执行程序时如何获取计数。例如invoice_id(7) 只有一行，但 invoice_id(100) 有四行信息。

Create or replace function return_num_rows_function(invoice_id_text in varchar2)
Return varchar2
Is inv_id varchar2(20);
Begin
Select count(*)invoice_id into inv_id from invoice_line_items where invoice_id=invoice_id_text;
Return inv_id;
End;

Create or replace procedure return_num_rows (invoice_id_text in varchar2)
Is inv_id varchar(20);
line_item_desc invoice_line_items.line_item_description%type;
Begin
inv_id := return_num_rows_function(invoice_id_text);
If inv_id is not null then
Select count(*)invoice_id, line_item_description into inv_id,line_item_desc
From invoice_line_items where invoice_id = inv_id;
dbms_output.put_line('The number of rows returned:'|| inv_id);
dbms_output.put_line('Item description(s):'|| line_item_desc);
End if;
End;
set serveroutput on;
execute return_num_rows(7);

Answer 1

首先不要

• 将字符串类型变量用于数字变量 (invoice_id_text)。

• 对于您的情况，最好使用过程而不是调用 函数(return_num_rows_function)，因为你需要两个出 返回的参数。

• 如果不将聚合列和非聚合列放在一起Group By，就不能使用 SQL Select 语句(即不要使用这个：

  Select count(*) invoice_id, line_item_description 
  into inv_id,line_item_desc
  From invoice_line_items 
  Where invoice_id = inv_id;
  

  )

因此，尝试创建以下过程：

SQL> CREATE OR REPLACE Procedure 
return_num_rows_proc(
i_invoice_id        invoice_line_items.invoice_id%type,
inv_id          out pls_integer,
line_item_desc  out invoice_line_items.line_item_description%type
) Is
Begin
for c in
(
Select line_item_description
into line_item_desc
From invoice_line_items
Where invoice_id = i_invoice_id
)
loop
line_item_desc := line_item_desc||'  '||c.line_item_description;
inv_id := nvl(inv_id,0) + 1;
end loop;
End;
/
SQL> CREATE OR REPLACE Procedure 
return_num_rows(
i_invoice_id pls_integer
) Is
inv_id         pls_integer;
line_item_desc invoice_line_items.line_item_description%type;
Begin
return_num_rows_proc(i_invoice_id,inv_id,line_item_desc);
If inv_id is not null then
dbms_output.put_line('The number of rows returned:' || inv_id);
dbms_output.put_line('Item description(s):' || line_item_desc);
End if;
End;
/

并像您一样打电话：

SQL> set serveroutput on;
SQL> execute return_num_rows(7);

Answer 2

将 varchar2(20) inv_id替换为inv_id数字; 此外，如果您想从过程中获取两个输出，最好使用 refcursor。