PLSQL ORACLE 中的计数

Question

我以前问过这个问题，但没有得到任何帮助。 我想获取给定属性的两个不同表中的行数。 这是我的代码。 我不是获取条件成立的总计数，而是获取表的全部计数

    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;
    /

是否有其他方法可以执行此操作，以便我可以检索该郊区的实际总人数来自人员表的

一些数据来自人员表的

       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

抱歉该表的外观。

这是我运行上述脚本时得到的值。

     SQL> exec p1('Randwick')
     Number of People :50
     Number of property :33

我改变了程序，这就是我得到的。

    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

Answer 1

您将该变量命名为与字段相同的名称。在查询中，首先在查询范围内查找 suburb，并且它与字段 suburb 匹配，即使它不使用 pp > 表别名。

因此，您实际上是将字段与其自身进行比较，从而获取所有记录（即，其中 suburb 不为 NULL）。查询中根本不使用过程参数。

解决办法：更改过程参数名称。

为了防止这样的错误，我总是使用 P_ 作为过程/函数参数的前缀，使用 V_ 作为局部变量的前缀。这样，它们就不会与字段名称混合。

Answer 2

虽然我同意问题的原因是 SQL 和 PL/SQL 之间的命名空间问题，因为 SQL 引擎已经“捕获”了 PL/SQL 变量的名称，但我不认为更改参数的名称是最好的方法。如果这样做，那么每个开发人员都将开始在每个参数名称前添加“p_”或其他无用的附加符前缀，并确保他们永远不会创建带有 P_ 前缀的列。

如果您浏览 PL/SQL 提供的软件包文档，您会发现 Oracle 自己这样做的情况很少（如果有的话），尽管他们过去曾做过一些令人恼火的不一致的事情，例如将 table_name 引用为“tabname”。

更可靠的方法是在 SQL 语句中引用变量名时在变量名前加上 pl/sql 过程名称作为前缀：

SELECT count(*)
INTO   person_count
FROM   person p WHERE p.suburb = p1.suburb ;

内容：

SELECT count(*)
INTO   person_count
FROM   person p WHERE p.suburb = count_suburb_objects.suburb ;

在您的情况下，您显然不会将过程命名为“P1”，因此实际上您会得到类似以下内容的 现在不受变量名称捕获的影响——作为奖励，当您双击过程名称时，您的文本编辑器可能会突出显示在 SQL 语句中使用变量名称的所有实例。

Answer 3

首先，为不区分大小写的搜索创建索引：

CREATE INDEX idx_person_suburb_u ON person(upper(suburb))
/
CREATE INDEX idx_property_suburb_u ON property(upper(suburb))
/

其次，对过程参数和局部变量使用前缀：

CREATE OR REPLACE PROCEDURE p1(p_location VARCHAR2) 
IS
    v_person_count    NUMBER;
    v_property_count  NUMBER; 
    v_location        VARCHAR2(32767);
BEGIN
    IF p_location IS NOT NULL THEN
      v_location := upper(p_location);
      SELECT count(*) INTO v_person_count
      FROM person WHERE upper(suburb) = v_location ;

      SELECT count(*) INTO v_property_count
      FROM property WHERE upper(suburb) = v_location ;
    ELSE
      SELECT count(*) INTO v_person_count
      FROM person WHERE upper(suburb) IS NULL;

      SELECT count(*) INTO v_property_count
      FROM property WHERE upper(suburb) IS NULL;        
    END IF;
    dbms_output.put_line('Number of People :' || v_person_count);
    dbms_output.put_line('Number of Property :' || v_property_count);
END;
/