oracle 查询未返回基于参数化WHERE的预期结果

iyzzxitl 于 2023-02-18 发布在 Oracle

关注(0)|答案(2)|浏览(121)

我的程序没有给我想要的结果，是因为比较是在2个varchar变量之间进行的吗？
以员工人数和物业租金价格为参数
这是我写的代码
表格：

create or replace procedure proprety_info ( p_sno staff.sno%type, p_rent property_for_rent.rent%type)
as
cursor executive is
select  s.sno, fname, pnum, street, rent 
from staff s, property_for_rent p
where s.sno = p.sno
and  p_rent < rent;

begin

for v_loop in executive loop

DBMS_OUTPUT.PUT_LINE ('staff number:  ' || v_loop.sno);
dbms_output.put_line ('staff name:  ' || v_loop.fname);
dbms_output.put_line ('property number:  ' || v_loop.pnum);
dbms_output.put_line ('street:  ' || v_loop.street);
dbms_output.put_line ('rent price:  ' || v_loop.rent);

    end loop;
END proprety_info;
/
set serveroutput on;
exec proprety_info('sg14',400);

我得到结果是：

staff number:  sa9
staff name:  mary
property number:  pa14
street:  16 high st
rent price:  650

staff number:  sg14
staff name:  david
property number:  pg16
street:  5 novar rd
rent price:  450

staff number:  sg37
staff name:  ann
property number:  pg21
street:  18 dale rd
rent price:  600

我所期待的

staff number:  sg14
staff name:  david
property number:  pg16
street:  5 novar rd
rent price:  450

oracle

来源：https://stackoverflow.com/questions/75400030/query-does-not-return-expected-result-based-on-parameterized-where

2条答案

按热度按时间

w8biq8rn1#

我想你应该：

cursor executive is
  select  s.sno, fname, pnum, street, rent
  from staff s, property_for_rent p
  where s.sno=p_sno and s.sno = p.sno
    and  p_rent < rent;

赞(0）回复(0）举报 2023-02-18

rdlzhqv92#

WITH 
    staff AS
        (
            Select 'sa9'    "SNO", 'Mary'   "FNAME" From Dual Union All
            Select 'sg14'   "SNO", 'David'  "FNAME" From Dual Union All
            Select 'sg37'   "SNO", 'Ann'    "FNAME" From Dual 
        ),
    property_for_rent AS
        (
            Select 'pa14' "PNUM", '16, High St.' "STREET", 650 "RENT", 'sa9'  "SNO" From Dual Union All
            Select 'pg16' "PNUM", '5, Novar Rd.' "STREET", 450 "RENT", 'sg14' "SNO"From Dual Union All
            Select 'pg21' "PNUM", '18, Dale Rd.' "STREET", 600 "RENT", 'sg37' "SNO" From Dual 
        )
select  s.sno, fname, pnum, street, rent 
from staff s, property_for_rent p
where s.sno = p.sno
and  100 < rent           -- p_rent = 100 

SNO  FNAME PNUM STREET             RENT
---- ----- ---- ------------ ----------
sa9  Mary  pa14 16, High St.        650 
sg14 David pg16 5, Novar Rd.        450 
sg37 Ann   pg21 18, Dale Rd.        600 

select  s.sno, fname, pnum, street, rent 
from staff s, property_for_rent p
where s.sno = p.sno
and  100 < rent       -- p_rent = 100
and 'sg14' = s.sno    -- p_sno = 'sg14'

SNO  FNAME PNUM STREET             RENT
---- ----- ---- ------------ ----------
sg14 David pg16 5, Novar Rd.        450

您的游标（第一个SQL）没有通过函数参数（p_sno）过滤SNO列，因此您得到了3行。
如果你也使用那个过滤器（第二个SQL）-你应该得到你需要的...
您的光标定义应如下所示...

Select  s.SNO, s.FNAME, p.PNUM, p.STREET, p.RENT 
From    STAFF s  
Inner Join  PROPERTY_FOR_RENT p ON(s.SNO = p.SNO)  -- use this sintax to join tables - it is a standard & good practice since '92. (or near)
Where   p_rent < p.RENT And p_sno = s.SNO

赞(0）回复(0）举报 2023-02-18

我来回答

oracle 查询未返回基于参数化WHERE的预期结果

2条答案

相关问题

热门标签

最新问答