在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中计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！