SQL 在 Oracle 脚本中使用变量
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5041653/
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
Using variables in Oracle script
提问by Bashir Magomedov
There is a complex query which generates a report. The query has several sub queries that generate 3-columns table for different products. Each sub query returns one row. All returned rows then need to be united. But there is one requirement. If there are no result rows for a sub query we need to include the corresponding product to the final report anyway, but specify that Trades_Count is equal to zero.
有一个生成报告的复杂查询。该查询有几个子查询，为不同的产品生成 3 列表。每个子查询返回一行。所有返回的行都需要合并。但是有一个要求。如果子查询没有结果行，我们无论如何都需要将相应的产品包括到最终报告中，但指定 Trades_Count 为零。
I can achieve this using set of variables. The following code will work perfectly in MS SQL Server:
我可以使用一组变量来实现这一点。以下代码将在 MS SQL Server 中完美运行：
DECLARE @PRODUCT_NAME_1 nvarchar(100);
DECLARE @OFFER_VALID_DATE_1 datetime;
DECLARE @TRADES_COUNT_1 int;
DECLARE @PRODUCT_NAME_2 nvarchar(100);
DECLARE @OFFER_VALID_DATE_2 datetime;
DECLARE @TRADES_COUNT_2 int;
--Product 1
select @PRODUCT_NAME_1 = PRODUCT_NAME, @OFFER_VALID_DATE_1 = MAX(EXPIRY_DATE), @TRADES_COUNT_1 = COUNT(DEAL_NUMBER)
from (
--Data extractions with several joins goes here....
) as TempTable1
GROUP BY PRODUCT_NAME
--Product 2
select @PRODUCT_NAME_2 = PRODUCT_NAME, @OFFER_VALID_DATE_2 = MAX(EXPIRY_DATE), @TRADES_COUNT_2 = COUNT(DEAL_NUMBER)
from (
--Data extractions with several joins goes here....
) as TempTable2
GROUP BY PRODUCT_NAME
SELECT ISNULL(@PRODUCT_NAME_1,'Product 1') AS PRODUCT_NAME, @OFFER_VALID_DATE_1 AS MAX_MATURITY, ISNULL(@TRADES_COUNT_1,0)
UNION
(
SELECT ISNULL(@PRODUCT_NAME_2,'Product 2') AS PRODUCT_NAME, @OFFER_VALID_DATE_2 AS MAX_MATURITY, ISNULL(@TRADES_COUNT_2,0)
)
I think that I haven't used anything T-SQL specific, but pure ANSI-SQL (I'm not 100% sure though).
我认为我没有使用任何特定于 T-SQL 的东西，而是使用纯 ANSI-SQL（虽然我不是 100% 确定）。
So this is not workingin Oracle.
所以这在 Oracle 中不起作用。
First of all it requires having only one DECLARE keyword. Then it forces me using Begin … End execution scope. Then it doesn't allow me to assign variables like I do (see example above) – I need to use “Select INTO” statement instead. After all calculations are done it doesn't allow me selecting values from local variables. Heck.
首先，它只需要一个 DECLARE 关键字。然后它迫使我使用 Begin ... End 执行范围。然后它不允许我像我一样分配变量（见上面的例子）——我需要使用“Select INTO”语句来代替。完成所有计算后，它不允许我从局部变量中选择值。哎呀。
Does anyone know how to make it work in Oracle?
有谁知道如何使它在 Oracle 中工作？
Thanks!
谢谢！
回答by Harrison
PL/SQL is different than t-sql, I did a change with some comments for you, but definitely look at the links from Andy. This was ran in oracle's free SQL Developer (which also has a "Translation Scratch Handler (tools>Migration>Translation Scratch Handler) that may be of use.
PL/SQL 与 t-sql 不同，我做了一些更改，并为您提供了一些评论，但请务必查看 Andy 的链接。这是在 oracle 的免费 SQL Developer 中运行的（它还有一个可能有用的“Translation Scratch Handler (tools>Migration>Translation Scratch Handler)”。
--this creates a refcursor to allow us to simply print the results
var refc refcursor
/
declare --here we declare our variables
product_name_1 varchar2(15) ;
offer_valid_date_1 date ;
trade_count_1 number ;
product_name_2 varchar2(15) ;
offer_valid_date_2 date ;
trade_count_2 number ;
begin
begin --this creates a block so we may handle any exceptions just to this
select PRODUCT_NAME, MAX(EXPIRY_DATE), COUNT(DEAL_NUMBER)
into product_name_1 , offer_valid_date_1 , trade_count_1
--in oracle you select INTO, not var=COL
from (
--Data extractions with several joins goes here....
select
123 PRODUCT_NAME,
sysdate EXPIRY_DATE,
5 DEAL_NUMBER
from dual --this is a 'fake' table to generate some data for testing
) TempTable1 --drop the "as"
GROUP BY PRODUCT_NAME ;
exception --if not data is found, then this error is thrown
--if multiple values are thrown an error will also be thrown (not caught here)
when no_data_found then
product_name_1 := null ; --note, to do a var = , we use "var := value;"
offer_valid_date_1 := null;
trade_count_1 := null;
end ;
begin
select PRODUCT_NAME, MAX(EXPIRY_DATE), COUNT(DEAL_NUMBER)
into product_name_2 , offer_valid_date_2 , trade_count_2
--in oracle you select INTO, not var=COL
from (
--Data extractions with several joins goes here....
select 555 PRODUCT_NAME, sysdate EXPIRY_DATE, 6 DEAL_NUMBER
from dual
) TempTable2 -- drop the "as"
GROUP BY PRODUCT_NAME ;
exception --if not data is found, then this error is thrown
--if multiple values are thrown an error will also be thrown (not caught here)
when no_data_found then
product_name_2 := null ;
offer_valid_date_2 := null;
trade_count_2 := null;
end ;
open :refc for --you cannot just have a select statement, you must "open" a cursor for it
--oracle IsNull is NVL (or NVL2 or you can do a case or decode...)
SELECT nvl(PRODUCT_NAME_1,'Product 1') AS PRODUCT_NAME
, OFFER_VALID_DATE_1 AS MAX_MATURITY
, nvl(TRADE_COUNT_1,0)
FROM DUAL --you also must have a table, DUAL is an oracle table for this tasks
UNION
SELECT nvl(PRODUCT_NAME_2,'Product 2') AS PRODUCT_NAME
, OFFER_VALID_DATE_2 AS MAX_MATURITY
, nvl(TRADE_COUNT_2,0)
FROM DUAL;
end ;
/
--now print the results, if you did this in a proc you would simple have this as an output
print refc;
-------------
PRODUCT_NAME MAX_MATURITY NVL(:B1,0)
-------------------------------------- ----------------------
123 18.FEB.2011 08:43 1
555 18.FEB.2011 08:43 1
Oracle concepts used here: Dual Table, NVL, Variables, pl/sql Exception
此处使用的 Oracle 概念： Dual Table、NVL、Variables、pl/sql Exception
and look at this http://www.dba-oracle.com/t_convent_sql_server_tsql_oracle_plsql.htm
看看这个 http://www.dba-oracle.com/t_convent_sql_server_tsql_oracle_plsql.htm
回答by Jordan Parmer
PL/SQL formats procedural blocks differently than T-SQL.
PL/SQL 格式化程序块的方式与 T-SQL 不同。
You'll want to use the following structure:
您将需要使用以下结构：
DECLARE
astring varchar2(1000);
anumber number;
BEGIN
my SQL code here...
END;
You don't use the @ either in PL/SQL. Just use variables names directly.
在 PL/SQL 中也不使用 @。直接使用变量名即可。
