游标循环 - 来自两个表的 COUNT - PLSQL Oracle [英] Cursor loop - COUNT from two tables - PLSQL Oracle

问题描述

在这里需要一些建议.我有两张表:1)车主；2)汽车.使用游标循环，我需要计算每个人拥有多少辆车.不明白涉及到两个表的select代码是怎么写的.

Need some advice here. I have two tables: 1) Car owners; 2) Cars. With cursor loop I need to count how many cars owns each person. I don't understand how do you write the select code when two tables are involved.

CREATE TABLE car_owner(
pnr VARCHAR2(13) PRIMARY KEY,
fname VARCHAR2(20);

CREATE TABLE car(
regnr VARCHAR2(6) PRIMARY KEY,
pnr REFERENCES car_owner(pnr);

INSERT INTO car_owner VALUES('19490321','anna');
INSERT INTO car_owner VALUES('19540201','tomas');
INSERT INTO car_owner VALUES('19650823','roger');

INSERT INTO car VALUES('ase456','19490321');
INSERT INTO car VALUES('ptg889','19490321');
INSERT INTO car VALUES('bon666','19650823');

declare 
cursor c_cars is select pnr, fnamn, count(amount_of_cars(pnr))
                from car_owner, car;
v_pnr car_owner.pnr%type;
v_fnamn car_owner.fnamn%type;
begin
if not c_cars %isopen then
open c_cars;
end if;
loop
fetch c_cars
into v_pnr, v_fnamn;
exit when c_cars %notfound;
dbms_output.put_line(v_pnr || ', ' || v_fnamn || ', ' || 
                    ', owns: ' || amount_of_cars || ' cars');
end loop;
close c_cars;
end; 

所以答案应该是这样的:

So the answer should look like this:

• 19490321，安娜，拥有:2 辆汽车
• 19540201，托马斯，拥有:1 辆车
• 19650823，罗杰，拥有:0 辆汽车

推荐答案

加入这些表.当您聚合东西时，所有非聚合列都应该是 group by 子句的一部分.

Join those tables. As you're aggregating something, all non-aggregated columns should be part of the group by clause.

另外，考虑使用游标 FOR 循环，因为它更简单(Oracle 为您完成所有脏工作 - 您不必声明变量，打开游标，担心关于退出循环，关闭光标)

Also, consider using a cursor FOR loop as it is way simpler (Oracle does all the dirty job for you - you don't have to declare variable(s), open the cursor, worry about exiting the loop, close the cursor).

SQL> DECLARE
  2     CURSOR c_cars IS
  3          SELECT o.pnr, o.fname, COUNT (*) cnt
  4            FROM car_owner o JOIN car c ON c.pnr = o.pnr
  5        GROUP BY o.pnr, o.fname;
  6  BEGIN
  7     FOR cur_r IN (  SELECT o.pnr, o.fname, COUNT (*) cnt
  8                       FROM car_owner o JOIN car c ON c.pnr = o.pnr
  9                   GROUP BY o.pnr, o.fname)
 10     LOOP
 11        DBMS_OUTPUT.put_line (
 12              cur_r.pnr
 13           || ', '
 14           || cur_r.fname
 15           || ', '
 16           || ', owns: '
 17           || cur_r.cnt
 18           || ' car(s)');
 19     END LOOP;
 20  END;
 21  /
19650823, roger, , owns: 1 car(s)
19490321, anna, , owns: 2 car(s)

PL/SQL procedure successfully completed.

SQL>

这篇关于游标循环 - 来自两个表的 COUNT - PLSQL Oracle的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！