主页 / dba / 问题 / 68518
Accepted
Dzyann
Asked: 2014-06-19 10:47:36 +0800 CST

有没有办法传递一个变量立即执行一次但多次使用它？

  • 772

我有一个 Cursor，它沿着这些行从脚本中获取数据：

OPEN myCursor FOR
SELECT value1 AS Value1,
  myPackage.function1 (value1, :myId) AS Result1,
  myPackage.function2 (value1, :myId) AS Result2,
  myPackage.function3 (value1, :myId) AS Result3                       
FROM mySchema.myTable
WHERE tpk = :myPk
USING myId, myId, myID

如您所见，我使用了myId变量 3 次，并且我必须在 using 语句中将其传递 3 次才能使其正常工作。我在任何地方都找不到它，但是有没有办法只传递一次myId变量？

oracle plsql

1 个回答

  1. Best Answer

    我不知道有什么内置方法可以实现这一点，但如果您的目标是仅在的USING：

    -- derived table
OPEN myCursor FOR
    SELECT value1 AS Value1,
      myPackage.function1 (value1, my.id) AS Result1,
      myPackage.function2 (value1, my.id) AS Result2,
      myPackage.function3 (value1, my.id) AS Result3                       
    FROM (SELECT :myId AS id, :myPk AS pk FROM dual) my 
      CROSS JOIN 
        mySchema.myTable
    WHERE tpk = my.pk
USING myPk, myId

-- CTE
OPEN myCursor FOR
    WITH my AS
        (SELECT :myId AS id, :myPk AS pk FROM dual)
    SELECT value1 AS Value1,
      myPackage.function1 (value1, my.id) AS Result1,
      myPackage.function2 (value1, my.id) AS Result2,
      myPackage.function3 (value1, my.id) AS Result3                       
    FROM my 
      CROSS JOIN
        mySchema.myTable
    WHERE tpk = my.pk
USING myPk, myId
    • 6

相关问题