如何使用Oracle PL/SQL声明、填充和使用变量

Question

我发誓以前已经问过这么多次了，但是我似乎不能将其他的例子应用到我的用例中：

首先，此查询将作为Informatica限定符的一部分执行，在某些情况下，将从Server语句传递，因此请注意这一点，并且不会使用SQL；Oracle仅用于代码开发。

我的历史记录主要是Server & Teradata，但正如标题所示，我现在需要在Oracle中声明、填充和使用变量，所有这些都在相同的过程中。不是SP，所以没有入/出声明

在Server中，此代码将按预期工作(为清晰起见添加的行号)：

1.  Declare @MaxDate Int
2.
3.  With f_data (cal_period) As (Select 201904 As cal_period)
4.
5.  Select @MaxDate = Max(cal_period) From f_data
6.
7.  Select
8.  Case
9.      When (@MaxDate%100) < 12 Then @MaxDate+1
10.     Else (@MaxDate+100) - ((@MaxDate%100)-1)
11. End As dt

• 第1行:这些是定义为int
• 第3行的YYYYMM日期周期:我在这里使用内联视图(CTE)进行说明，并使您更容易复制和粘贴，但实际上，这实际上是一个物理控制表，因此通常不会在脚本中显示。
• 第5行:用单值结果集
• 第7-11行填充参数( Server前缀为参数)：这仅仅是一个推进周期的逻辑，Server中的百分比标记是Modulus函数，甲骨文被写成Mod(@MaxDate,100)

对于那些不熟悉Server的人，它不需要一个引用表(如Dual ("Sys.Dual") )来执行查询，因此对于Oracle来说，在缺少的第12行上需要一个"From Dual“语句

我的要求本质上是上述result的副本，所以我需要声明一个一次性use变量，用SQL查询的结果填充该变量，然后在转换中使用这个变量--转换的结果被捕获到Informatica和SSIS变量，供以后使用。

到目前为止，我已经尝试声明一个变量，这似乎是可行的(我的意思是它没有返回一个错误)：

Declare MaxPeriod Int;
Begin
    Select 201904 Into MaxPeriod From Dual;
End;

从SQL语句中填充也显示为成功完成：

Declare MaxPeriod Int;
Begin
    Select Max(MaxPeriodVal) Into MaxPeriod From CtrlTable;
End;

尽管我似乎无法超越这一点，在put.line语句失败时对变量进行实际测试，但简单的案例检查也是如此：

Declare MaxPeriod Int;
Begin
    Select 201904 Into MaxPeriod From Dual;
End;


Select
Case
    When 201904 = MaxPeriod Then 'Match'
    Else 'No Match'
End As dteChk
From Dual;

我尝试在检查中用冒号作为MaxPeriod的前缀，并且前缀、后缀/两者都带有符号，例如:MaxPeriod；&MaxPeriod；MaxPeriod& &MaxPeriod& & in；&MaxPeriod&所有这些都失败了。

Answer 1

基本问题是可变范围问题。您是在PL/SQL匿名块的上下文中声明MaxPeriod的，因此当该块在第4行结束时，它将消失(超出范围)。

您可以将整个查询放在PL/SQL块中，但是从PL/SQL块返回整个结果集并不容易，所以我认为您不想这样做。

我不知道Oracle驱动程序是如何处理本机查询的，但这可能会起作用：

var MaxPeriod number; -- bind variable declared as global scope for this script

Begin -- one of several ways to assign values to bind variables
    :MaxPeriod := 201904;
End;
/

Select
Case
    When 201904 = :MaxPeriod Then 'Match'
    Else 'No Match'
End As dteChk
From Dual;

如果var语法不适用于声明string变量，那么您可能需要研究为查询字符串传递绑定变量的其他方法。您可能会传递一个null值(无论如何，对于一个数字数据类型)，然后在SQL脚本中覆盖它。

或者，在您最初的示例代码中，我认为我应该使用CTE或内联视图，而不是变量。

With f_data As (Select 201904 As cal_period from dual)
Select
Case
      When Mod(MaxDate,100) < 12 Then MaxDate+1
     Else (MaxDate+100) - (Mod(MaxDate,100)-1)
End As dt
from (Select Max(cal_period) as MaxDate From f_data) mp

Answer 2

您可以在sql*plus中使用substitution变量define，如下所示。

Define MaxPeriod := 201904
Select
Case
    When &MaxPeriod = MaxPeriod Then 'Match'
    Else 'No Match'
End As dteChk
From Dual;

干杯！！