oracle 带变量帮助的 PLSQL 选择语句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4136667/
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
PLSQL select statement with variable help
提问by Matt
Hello i am trying to do this simple statement but i want to add a variable that comes from a select. Here is what i have.
您好，我正在尝试执行此简单语句，但我想添加一个来自选择的变量。这是我所拥有的。
userEmail varChar(50) := SELECT user_id FROM users WHERE email = '[email protected]';
SELECT *
FROM iphone_alerts
WHERE user_id = userEmail
AND date_added = (SELECT MAX(date_added) FROM iphone_alerts WHERE user_id = userEmail
Do i need to use something along the lines of Declare and Begins? I am new to the sql stuff and am having trouble finding answers.
我是否需要使用类似 Declare 和 Begins 的东西？我是 sql 的新手，无法找到答案。
回答by Pablo Santa Cruz
You need:
你需要：
declare
userEmail varchar2(200);
begin
select user_id into userEmail
from users
where email = '[email protected]';
-- you will need a cursor to itare these results
select *
from iphone_alerts
where user_id = userEmail
and date_added = (select max(date_added) from iphone_alerts WHERE user_id);
end;
Edit after comment:
评论后编辑：
If select should return only ONE row, you don't need a cursor, but you need an intoclause to store each retrieved value into a variable. Something like:
如果 select 应该只返回一行，则不需要游标，但需要一个into子句将每个检索到的值存储到变量中。就像是：
declare
userEmail varchar2(200);
v_field1 number;
v_field2 date;
v_field3 varchar2(200);
begin
select user_id into userEmail
from users
where email = '[email protected]';
-- you will need a cursor to itare these results
select field1, field2, field3
into v_field1, v_field2, v_field3
from iphone_alerts
where user_id = userEmail
and date_added = (select max(date_added) from iphone_alerts WHERE user_id);
end;
回答by Justin Cave
If you want to do this in SQL, you'd want something like
如果你想在 SQL 中做到这一点，你会想要像
SELECT alerts.*
FROM iphone_alerts alerts,
users
WHERE alerts.user_id = users.user_id
AND users.email = '[email protected]'
AND alerts.date_added = (SELECT MAX(date_added)
FROM iphone_alerts alerts2
WHERE alerts2.user_id = user.user_id)
Probably more efficient would be something like this that lets us hit the IPHONE_ALERTS table just once.
可能更有效的是这样的事情，让我们只点击一次 IPHONE_ALERTS 表。
SELECT <<list of columns in IPHONE_ALERTS>>
FROM (
SELECT alerts.*,
RANK() OVER (PARTITION BY alerts.user_id ORDER BY date_added DESC) rnk
FROM iphone_alerts alerts,
users
WHERE alerts.user_id = users.user_id
AND users.email = '[email protected]'
)
WHERE rnk = 1
回答by FrustratedWithFormsDesigner
I'm not sure what you're trying to do, but I think you might need select into
我不确定你想做什么，但我认为你可能需要 select into
declare
V_userID varChar(50) ;
begin
/*This will store the value of the user_id field into the v_userID variable.*/
SELECT user_id into v_userID FROM users WHERE email = '[email protected]';
end;
http://download.oracle.com/docs/cd/B13789_01/appdev.101/b10807/13_elems045.htm
http://download.oracle.com/docs/cd/B13789_01/appdev.101/b10807/13_elems045.htm
