在 Oracle SQL developer 中定义变量
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/38721460/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Define variable in Oracle SQL developer
提问by Nickpick
I'm trying to define standard values in variables in ORACLE SQL Developer, but it keeps asking me to enter a value. How can I avoid that and put as default value for v_mode ='X1','X2' and set COB_DATE to 14 July so that there is NO popup?
我试图在 ORACLE SQL Developer 的变量中定义标准值，但它一直要求我输入一个值。如何避免这种情况并将 v_mode ='X1','X2' 设为默认值并将 COB_DATE 设置为 7 月 14 日，以便没有弹出窗口？
variable COB_DATE date
variable v_mode varchar(20);
exec :COB_DATE := '14-JUL-2016';
exec :v_mode := 'MAG';
select *
FROM DF_RISK_SIT2_OWNER.recon_ts_rs
WHERE SRC_HUB = 'DBRS'
AND TRD_SRC_SYS in :v_mode
AND DSET_COB_DT = :COB_DATE
but I get the error: Bind Variable "COB_DATE" is NOT DECLARED
但我收到错误消息：未声明绑定变量“COB_DATE”
回答by Tony Andrews
You have to use "Run Script (F5)" not "Run Statement (Control+Enter") - I have circled the toolbar icon in red:
您必须使用“运行脚本（F5）”而不是“运行语句（Control+Enter”）——我用红色圈出了工具栏图标：
回答by Marcin Wroblewski
SQL> help var
VARIABLE
--------
Declares a bind variable that can be referenced in PL/SQL, or
lists the current display characteristics for a single variable
or all variables.
VAR[IABLE] [variable [type]]
where type represents one of the following:
NUMBER CHAR CHAR (n [CHAR|BYTE])
NCHAR NCHAR (n) VARCHAR2 (n [CHAR|BYTE])
NVARCHAR2 (n) CLOB NCLOB
REFCURSOR BINARY_FLOAT BINARY_DOUBLE
As you can see there is no DATE type here. I guess the whole
如您所见，此处没有 DATE 类型。我猜整个
variable COB_DATE date
is ignored.
被忽略。
As a workaround you can define COB_DATE as varchar2 and convert it to DATE in the sql
作为一种解决方法，您可以将 COB_DATE 定义为 varchar2 并将其转换为 sql 中的 DATE
variable COB_DATE varchar2(30)
variable v_mode varchar2(20)
exec :COB_DATE := '14-JUL-2016';
exec :v_mode := 'MAG';
select *
FROM DF_RISK_SIT2_OWNER.recon_ts_rs
WHERE SRC_HUB = 'DBRS'
AND TRD_SRC_SYS in :v_mode
AND DSET_COB_DT = TO_DATE(:COB_DATE, 'DD-MON-YYYY')
or rely on implicit conversion using your original query
或依赖使用原始查询的隐式转换
