PLSQL ORACLE 中的计数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7764773/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
COUNT in PLSQL ORACLE
提问by shakur007
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
有没有其他方法可以做到这一点，以便我可以检索该 SUBURB 中的实际总人数
Some datas from PERSON TABLE
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 Hyman Hilfgott
17 Flood St. Bondi 2026 9387-0573
SOME DATA from PROPERTY TABLE
来自属性表的一些数据
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>
The solution suppose to be this
解决方案假设是这样的
SQL> exec p1('randwick');
Number of People: 7
Number of Property: 2
回答by GolezTrol
You named the variable the same as the field. In the query, suburbis first sought in the scope of the query, and it matches the field suburbeven though it doesn't use the pptable alias.
您将变量命名为与字段相同的名称。在查询中，suburb首先在查询范围内查找，suburb即使它不使用pp表别名，它也会匹配该字段。
So you're actually comparing the field with itself, therefore getting all records (where suburbis NOT NULL, that is). The procedure parameter isn't used in the query at all.
因此，您实际上是在将字段与其自身进行比较，从而获取所有记录（即，其中suburb不是 NULL）。查询中根本不使用过程参数。
The solution: change the name of the procedure parameter.
解决方法：更改过程参数的名称。
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.
为了防止这样的错误，我总是将P_其用作过程/函数参数V_的前缀和局部变量的前缀。这样，它们就永远不会与字段名称混在一起。
回答by David Aldridge
Although I agree that the cause of the problem is a namespace issue between SQL and PL/SQL, in that the SQL engine has "captured" the name of the PL/SQL variable, I don't believe that changing the name of the parameter is the best approach. If you do this then you doom every developer to start prefixing every parameter name with "p_" or some other useless appendage, and to make sure that they never create a column with a P_ prefix.
虽然我同意问题的原因是 SQL 和 PL/SQL 之间的命名空间问题，因为 SQL 引擎已经“捕获”了 PL/SQL 变量的名称，但我不相信更改参数名称是最好的方法。如果您这样做，那么您注定每个开发人员都开始为每个参数名称添加“p_”或其他一些无用的附加物的前缀，并确保他们永远不会创建带有 P_ 前缀的列。
If you look through the PL/SQL Supplied Packages documentation you see very few, if any, cases where Oracle themselves do this, although they have in the past done irritatingly inconsistent things like refer to table_name as "tabname".
如果您查看 PL/SQL Supplied Packages 文档，您会看到 Oracle 自己这样做的情况很少（如果有的话），尽管他们过去做过令人恼火的不一致的事情，例如将 table_name 称为“tabname”。
A more robust approach is to prefix the variable name with the pl/sql procedure name when referencing it in SQL statements:
更可靠的方法是在 SQL 语句中引用变量名时，在变量名前加上 pl/sql 过程名：
SELECT count(*)
INTO person_count
FROM person p WHERE p.suburb = p1.suburb ;
In your case you clearly wouldn't name your procedure "P1" so in fact you'd have something like:
在您的情况下，您显然不会将您的程序命名为“P1”，因此实际上您将拥有以下内容：
SELECT count(*)
INTO person_count
FROM person p WHERE p.suburb = count_suburb_objects.suburb ;
Your code is now immune to variable name capture -- as a bonus your text editor might highlight all the instances where you've used a variable name in a SQL statement when you double-click on the procedure name.
您的代码现在不受变量名称捕获的影响——作为奖励，当您双击过程名称时，您的文本编辑器可能会突出显示您在 SQL 语句中使用变量名称的所有实例。
回答by vemcaster
First, create indices for case-insensitive search:
首先，为不区分大小写的搜索创建索引：
CREATE INDEX idx_person_suburb_u ON person(upper(suburb))
/
CREATE INDEX idx_property_suburb_u ON property(upper(suburb))
/
Second, use prefixes for procedure parameters and local variables:
其次，对过程参数和局部变量使用前缀：
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;
/
