在 Oracle 脚本中使用变量

Question

有一个生成报告的复杂查询。该查询有几个子查询，为不同的产品生成 3 列的表。每个子查询返回一行。然后需要合并所有返回的行。 但有一个要求。如果子查询没有结果行，我们无论如何都需要将相应的产品包含到最终报告中，但指定 Trades_Count 等于 0。

我可以使用一组变量来实现这一点。以下代码将在 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)
)

我认为我没有使用任何特定于 T-SQL 的东西，而是纯粹的 ANSI-SQL（尽管我不是 100% 确定）。

所以这在 Oracle 中不起作用。

首先，它要求只有一个 DECLARE 关键字。然后它迫使我使用 Begin … End 执行范围。然后它不允许我像我一样分配变量（参见上面的示例） - 我需要使用“Select INTO”语句来代替。完成所有计算后，它不允许我从局部变量中选择值。哎呀。

有谁知道如何让它在Oracle中运行？

谢谢！

Answer 1

PL/SQL 与 t-sql 不同，我做了一些更改，并为您提供了一些注释，但一定要看看 Andy 的链接。这是在 oracle 的免费 SQL Developer 中运行的（它也有一个可能有用的“Translation Scratch Handler（工具>迁移>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 概念：
双表 , NVL，变量，pl/sql 异常

并查看此 http://www.dba-oracle.com/t_convent_sql_server_tsql_oracle_plsql.htm

Answer 2

PL/SQL 格式化过程块的方式与 T-SQL 不同。

您将需要使用以下结构：

DECLARE
    astring varchar2(1000);
    anumber number;

BEGIN
   my SQL code here...
END;

在 PL/SQL 中也不使用 @。直接使用变量名即可。