在PLSQL ORACLE中计数 [英] COUNT in PLSQL ORACLE
问题描述
我之前曾问过这个问题，但没有得到任何帮助. 我想获得给定属性的两个不同表中的行数. 这是我的代码. 我没有获取条件所在的总计数，而是获得了表的全部计数
I have asked this question before but I did not get any help. I want to get the count of rows in two different table given an attribute. This is my code . Instead of fetching the total count where the condition holds, I am getting the whole count of the table
create or replace PROCEDURE p1( suburb IN varchar2 )
as
person_count NUMBER;
property_count NUMBER;
BEGIN
SELECT count(*) INTO person_count
FROM person p WHERE p.suburb = suburb ;
SELECT count(*) INTO property_count
FROM property pp WHERE pp.suburb = suburb ;
dbms_output.put_line('Number of People :'|| person_count);
dbms_output.put_line('Number of property :'|| property_count);
END;
/
还有其他方法可以做到这一点，以便我可以检索该郊区的实际总人数
Is there any other way to do this so that i can retrieve the real total count of people in that SUBURB
PERSON TABLE中的一些数据
Some datas from PERSON TABLE
PEID FIRSTNAME LASTNAME
---------- -------------------- --------------------
STREET SUBURB POST TELEPHONE
---------------------------------------- -------------------- ---- ------------
30 Robert Williams
1/326 Coogee Bay Rd. Coogee 2034 9665-0211
32 Lily Roy
66 Alison Rd. Randwick 2031 9398-0605
34 Jack Hilfgott
17 Flood St. Bondi 2026 9387-0573
属性表中的某些数据
PNO STREET SUBURB POST
---------- ---------------------------------------- -------------------- ----
FIRST_LIS TYPE PEID
--------- -------------------- ----------
48 66 Alison Rd. Randwick 2031
12-MAR-11 Commercial 8
49 1420 Arden St. Clovelly 2031
27-JUN-10 Commercial 82
50 340 Beach St. Clovelly 2031
05-MAY-11 Commercial 38
很抱歉表格的显示方式.
Sorry for the way the table is looking .
这是我运行上述脚本时获得的值.
This is the value I get when I run the above script.
SQL> exec p1('Randwick')
Number of People :50
Number of property :33
我更改了过程，这就是我得到的.
I changed the PROCEDURE ,this is what I get .
SQL> create or replace PROCEDURE p1( location varchar2 )
IS
person_count NUMBER;
property_count NUMBER;
BEGIN
SELECT count(p.peid) INTO person_count
FROM person p WHERE p.suburb = location ;
SELECT count(pp.pno) INTO property_count
FROM property pp WHERE pp.suburb = location ;
dbms_output.put_line('Number of People :'|| person_count);
dbms_output.put_line('Number of property :'|| property_count);
END;
/
2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17
Procedure created.
SQL> exec p1('KINGSFORD')
Number of People :0
Number of property :0
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> exec p1('Randwick')
Number of People :0
Number of property :0
PL/SQL procedure successfully completed.
SQL>
解决方案应该是这个
SQL> exec p1('randwick');
Number of People: 7
Number of Property: 2
推荐答案
您将变量命名为与字段相同的名称.在查询中，首先在查询范围内搜索suburb，即使它不使用pp表别名，它也会与字段suburb匹配.
You named the variable the same as the field. In the query, suburb is first sought in the scope of the query, and it matches the field suburb even though it doesn't use the pp table alias.
因此，您实际上是在将字段与其自身进行比较，因此将获得所有记录(即suburb不是NOT NULL的地方).该过程参数根本不在查询中使用.
So you're actually comparing the field with itself, therefore getting all records (where suburb is NOT NULL, that is). The procedure parameter isn't used in the query at all.
解决方法:更改过程参数的名称.
The solution: change the name of the procedure parameter.
为防止此类错误，我始终将P_用作过程/函数参数的前缀，并将V_用作局部变量的前缀.这样，他们就不会与字段名称混在一起.
To prevent errors like this, I always use P_ as a prefix for procedure/function parameters and V_ as a prefix for local variables. This way, they never mingle with field names.
这篇关于在PLSQL ORACLE中计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
