在Oracle SQL Developer代码窗口中使用变量运行sql代码 [英] Run sql code with variables in Oracle SQL Developer code window

问题描述

我正在使用Oracle SQL Developer编写代码.我有一个简单的选择语句，可以工作:

I'm writing code using Oracle SQL Developer. I have a simple select statement that works:

    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);

但是，我想向其中添加一些变量，并在语句中引用变量，如下所示，并在代码窗口中运行它.如何正确执行此操作?

However I want to add some variables to it and reference the variables in the statement, similar to below, and also run it in the code window. How do I do this correctly?

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窗口中运行此命令时，我收到以下消息:

When I run this in a sql window I get the message below:

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替代变量.

if I am reading this correctly, I think you are looking for Oracle Substitution variables.

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

this will prompt you each time to input the values, by using &val it will prompt you @ runtime

  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以保留其值，然后使用

change it to &&var to have it retain the value, then use

UNDEFINE var 

清除

现在，您可以使用DEFINE这样在页面顶部设置这些设置(从而避免出现提示)

Now you can set these at the top of the page (thus avoiding the prompt) by utilizing DEFINE as such

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')"

双&"号将保留"该值，直到您取消定义(见上文)或重新定义它为止.

the double ampersand will 'retain' the value until you UNDEFINE it (see above) or redefine it.

这篇关于在Oracle SQL Developer代码窗口中使用变量运行sql代码的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！