Блог

Я задавал этот вопрос раньше, но я не получил никакой помощи. Я хочу получить количество строк в двух разных таблицах с заданным атрибутом. Это мой код . Вместо того, чтобы извлекать общее количество там, где выполняется условие, я получаю весь подсчет таблицы

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

Есть ли какой-либо другой способ сделать это, чтобы я мог получить реальное общее количество людей в этом ПРИГОРОДЕ

Некоторые данные из ТАБЛИЦЫ PERSON

        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
  

Вы назвали переменную так же, как поле. В запросе suburb сначала ищется в области запроса и соответствует полю suburb , даже если оно не использует pp псевдоним таблицы.

Таким образом, вы фактически сравниваете поле с самим собой, поэтому получаете все записи (то есть, где suburb НЕ NULL). Параметр процедуры вообще не используется в запросе.

Решение: измените имя параметра процедуры.

Чтобы предотвратить подобные ошибки, я всегда использую P_ в качестве префикса для параметров процедуры / функции и V_ в качестве префикса для локальных переменных. Таким образом, они никогда не смешиваются с именами полей.

Хотя я согласен, что причиной проблемы является проблема с пространством имен между SQL и PL / SQL, поскольку механизм SQL «захватил» имя переменной PL / SQL, я не считаю, что изменение имени параметра является наилучшим подходом. Если вы сделаете это, то вы обрекаете каждого разработчика на то, чтобы начать добавлять к каждому имени параметра префикс «p_» или какой-либо другой бесполезный довесок и убедиться, что они никогда не создают столбец с префиксом P_.

Если вы просмотрите документацию по поставляемым пакетам PL / SQL, вы увидите очень мало, если вообще есть, случаев, когда Oracle сами делают это, хотя в прошлом они делали раздражающе непоследовательные вещи, такие как ссылка на имя_таблицы как «tabname».

Более надежный подход заключается в том, чтобы префиксить имя переменной именем процедуры pl / sql при ссылке на нее в операторах SQL:

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

В вашем случае вы явно не назвали бы свою процедуру «P1», так что на самом деле у вас было бы что-то вроде:

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

Теперь ваш код защищен от захвата имени переменной — в качестве бонуса ваш текстовый редактор может выделить все случаи, когда вы использовали имя переменной в инструкции SQL при двойном щелчке по имени процедуры.

Сначала создайте индексы для поиска без учета регистра:

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