SQL SELECT INTO变量，两条语句，添加变量
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/681559/
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
SELECT INTO variable, two statements, add variables
提问by jlrolin
I have a variable called c_kilometers. I have a cursor that grabs a bunch of records that have those kilometers. I need to run two separate SELECT statements in the cursor that simply grab a kilometer from one table based on values in the cursor, and run another SELECT doing the same thing on another table.
我有一个名为 c_kilometers 的变量。我有一个光标，可以抓取一堆具有这些公里的记录。我需要在游标中运行两个单独的 SELECT 语句，根据游标中的值简单地从一个表中抓取一公里，然后在另一个表上运行另一个 SELECT 做同样的事情。
SELECT t.kilometers INTO c_kilometers
FROM table_name WHERE WHERE l.code = cursor_t.code_att
SELECT g.kilometers INTO c_kilometers
FROM table_name WHERE l.code = cursor_t.code_aff
My question is can I add the c_kilometers together without creating a temporary variable to hold on of the values? I haven't used PL/SQL in awhile, and I don't remember having to do this ever, so this is more of a learning question than anything.
我的问题是我可以在不创建临时变量的情况下将 c_kilometers 添加在一起吗？我有一段时间没有使用 PL/SQL，而且我不记得必须这样做过，所以这更像是一个学习问题。
回答by Quassnoi
Provided that both your queries always return exactly one row, you can do either of the following:
如果您的两个查询始终只返回一行，您可以执行以下任一操作：
/* Variant 1 */
SELECT t.kilometers + g.kilometers
INTO c_kilometers
FROM table_name t, table_name2 g
WHERE etc1
AND etc2
/* Variant 2 */
SELECT t.kilometers
INTO c_kilometers
FROM table_name
WHERE etc;
SELECT c_kilometers + g.kilometers
INTO c_kilometers
FROM table_name2
WHERE etc;
If they're in the same table as you posted, you can use:
如果它们与您发布的表在同一张表中，您可以使用：
SELECT COALESCE(SUM(kilometers), 0)
INTO c_kilometers
FROM table_name
WHERE l.code IN (cursor_t.code_aff, cursor_t.code_att)
It seems that it will be more efficient to put table_nameinto your SELECTquery that produces the cursor.
似乎将生成游标的查询table_name放入SELECT查询中会更有效。
If you post this query, I'll probably can help to do this.
如果您发布此查询，我可能会帮助您做到这一点。
回答by John Flack
Join the SELECTs like this:
SELECT a.kilometers + b.kilometers
FROM (SELECT code, kilometers FROM table_name WHERE ...) a JOIN
(SELECT code, kilometers FROM table_name WHERE ...) b ON a.code = b.code
像这样加入 SELECT：
SELECT a.kilometers + b.kilometers
FROM (SELECT code, kilometers FROM table_name WHERE ...) a JOIN
(SELECT code, kilometers FROM table_name WHERE ...) b ON a.code = b.code
