oracle - 带有变量帮助的 PLSQL select 语句

Question

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

 userEmail varChar(50) := SELECT user_id FROM users WHERE email = '<a href="https://stackoverflow.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="e88d85898184a88d85898184c68b8785" rel="noreferrer noopener nofollow">[email protected]</a>';
        SELECT * 
        FROM iphone_alerts
        WHERE user_id =  userEmail
        AND date_added = (SELECT MAX(date_added) FROM iphone_alerts WHERE user_id = userEmail  

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

Answer 1

您需要:

declare
    userEmail varchar2(200);
begin
    select user_id into userEmail
      from users
     where email = '<a href="https://stackoverflow.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="096c64686065496c64686065276a6664" rel="noreferrer noopener nofollow">[email protected]</a>';

    -- 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 = '<a href="https://stackoverflow.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="2d48404c44416d48404c4441034e4240" rel="noreferrer noopener nofollow">[email protected]</a>';

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