在 Oracle SQL Developer 代码窗口中运行带有变量的 sql 代码

我正在使用 Oracle SQL Developer 编写代码。我有一个有效的简单选择语句：

    SELECT
      CFS.CAE_SEC_ID,
      CFS.FM_SEC_CODE,
      CFS.LAST_USER_ID,
      case 
          when 1 = 1 then
            sl.usbank_to_edit
          else
            case 
              when 'ENT\CB174' = CFS.last_user_id then
                sl.owner_to_edit
              else
                sl.to_edit
            end
        end canEdit
    FROM
    CAEDBO.CAE_FOF_SECURITY CFS
    INNER JOIN caedbo.CAE_DATA_SET_ELEMENT CDSE_STAT 
        ON (CDSE_STAT.DATA_SET_ELEMENT_ID = CFS.APPR_STATUS)
    INNER JOIN caedbo.CAE_STATE_LOOKUP sl 
        ON (sl.object_state =  CDSE_STAT.data_set_element_id)
   where 
      cfs.CAE_SEC_ID in (3741, 3744, 3748, 3752);

不过，我想向其中添加一些变量并引用语句中的变量，类似于下面的内容，并在代码窗口中运行它。我该如何正确地做到这一点？

DECLARE 
  p_USBank_n NUMBER;
  p_user_id_c VARCHAR2(20);
BEGIN  
  p_USBank_n := 1; 
  p_user_id_c := 'ENT\CB174';

    SELECT
      CFS.CAE_SEC_ID,
      CFS.FM_SEC_CODE,
      CFS.LAST_USER_ID,
      case 
          when p_USBank_n = 1 then
            sl.usbank_to_edit
          else
            case 
              when p_user_id_c = CFS.last_user_id then
                sl.owner_to_edit
              else
                sl.to_edit
            end
        end canEdit
    FROM
    CAEDBO.CAE_FOF_SECURITY CFS
    INNER JOIN caedbo.CAE_DATA_SET_ELEMENT CDSE_STAT 
        ON (CDSE_STAT.DATA_SET_ELEMENT_ID = CFS.APPR_STATUS)
    INNER JOIN caedbo.CAE_STATE_LOOKUP sl 
        ON (sl.object_state =  CDSE_STAT.data_set_element_id)
   where 
      cfs.CAE_SEC_ID in (3741, 3744, 3748, 3752);
END;

当我在 sql 窗口中运行此命令时，我收到以下消息：

Error report:
ORA-06550: line 8, column 5:
PLS-00428: an INTO clause is expected in this SELECT statement
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

如果我没读错的话，我想你正在寻找Oracle 替换变量。 http://download.oracle.com/docs/cd/B19306_01/server.102/b14357/ch5.htm#i1211130

这将提示您每次输入值，通过使用 &val 它将提示您@运行时

  SELECT
      CFS.CAE_SEC_ID,
      CFS.FM_SEC_CODE,
      CFS.LAST_USER_ID,
      CASE 
          when &p_USBank_n = 1 then
            sl.usbank_to_edit
          else
            CASE 
              when '&p_user_id_c' = CFS.last_user_id then
                sl.owner_to_edit
              else
                sl.to_edit
            end
        end canEdit
    FROM
    CAEDBO.CAE_FOF_SECURITY CFS
    INNER JOIN caedbo.CAE_DATA_SET_ELEMENT CDSE_STAT 
        ON (CDSE_STAT.DATA_SET_ELEMENT_ID = CFS.APPR_STATUS)
    INNER JOIN caedbo.CAE_STATE_LOOKUP sl 
        ON (sl.object_state =  CDSE_STAT.data_set_element_id)
   where 
      CFS.CAE_SEC_ID IN (3741, 3744, 3748, 3752);

将其更改为 &&var 以使其保留值，然后使用

UNDEFINE var 

清除它

现在您可以通过使用 DEFINE 在页面顶部设置这些（从而避免提示）

DEFINE XYZ = 5
DEFINE AAA = to_date('10/10/2010','mm/dd/rrrr')
DEFINE textString = AaBbCc

SELECT &&XYZ b, &&AAA a, '&&textString' textString
  from dual ;

  B                      A                         TEXTSTRING 
  ---------------------- ------------------------- ---------- 
  5                      10.OCT.2010 00:00         AaBbCc     
  --typing define will show you all the "defined" values 
define

  DEFINE XYZ = "5"
  DEFINE TEXTSTRING = "AaBbCc"
  DEFINE AAA = "to_date('10/10/2010','mm/dd/rrrr')"

双＆符号将“保留”该值，直到您取消定义它（见上文）或重新定义它。