带有变量帮助的 PLSQL select 语句

发布于 2024-10-01 05:25:50 字数 502 浏览 4 评论 0原文

您好，我正在尝试执行这个简单的语句，但我想添加一个来自选择的变量。这是我所拥有的。

 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

我需要使用类似于“声明”和“开始”的东西吗？我对 sql 很陌生，很难找到答案。

原文

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.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

梦亿 2024-10-08 05:25:50

您需要：

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;

在注释后编辑：

如果 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;

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 into clause to store each retrieved value into a variable. Something like:

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;

回复收藏 0 原文

晨曦慕雪 2024-10-08 05:25:50

如果你想在 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)

，也许更有效的是这样的东西，让我们只访问 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

If you want to do this in SQL, you'd want something like

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.

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

回复收藏 0 原文

美羊羊 2024-10-08 05:25:50

我不确定您要做什么，但我认为您可能需要 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

I'm not sure what you're trying to do, but I think you might need 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

回复收藏 0 原文

~没有更多了~