在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,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,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 Substitution variables.

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

SELECT
      CFS.CAE_SEC_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,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')"

双符号将“保留”该值,直到您启用它(见上文)或重新定义它.

在Oracle SQL Developer代码窗口中使用变量运行sql代码

猜你在找的Oracle相关文章