在 Oracle SQL Developer 代码窗口中运行带有变量的 sql 代码
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4672019/
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
Run sql code with variables in Oracle SQL Developer code window
提问by Ciaran Bruen
I'm writing code using Oracle SQL Developer. I have a simple select statement that works:
我正在使用 Oracle SQL Developer 编写代码。我有一个简单的 select 语句可以工作：
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;
When I run this in a sql window I get the message below:
当我在 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:
回答by Harrison
if I am reading this correctly, I think you are looking for Oracle Substitution variables.
如果我正确阅读本文，我认为您正在寻找Oracle 替换变量。
this will prompt you each time to input the values, by using &val it will prompt you @ runtime
这将提示您每次输入值，通过使用 &val 它将提示您 @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);
change it to &&var to have it retain the value, then use
将其更改为 &&var 以使其保留该值，然后使用
UNDEFINE var
to clear it
清除它
Now you can set these at the top of the page (thus avoiding the prompt) by utilizing DEFINE as such
现在您可以通过使用 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')"
the double ampersand will 'retain' the value until you UNDEFINE it (see above) or redefine it.
双与号将“保留”该值，直到您取消定义它（见上文）或重新定义它。
回答by Alex Poole
The error message explains the problem; inside a PL/SQL block you have to select INTOsomething, and you can't just dump the results of a query to screen as you can with plain SQL. (There are ways to do it but probably overly complicated for what it looks like you're trying to achieve here).
错误信息说明了问题；在 PL/SQL 块中，您必须选择INTO某些内容，并且不能像使用普通 SQL 那样将查询结果转储到屏幕上。（有很多方法可以做到，但对于您在这里尝试实现的目标来说可能过于复杂）。
If you don't want to use substitution variables as @Harrison suggested, you can use bind variables which you define at the start in a separate anonymous block. You can then refer to the bind variable in the plain SQL:
如果您不想像@Harrison 建议的那样使用替换变量，则可以使用在开始时在单独的匿名块中定义的绑定变量。然后，您可以在纯 SQL 中引用绑定变量：
var p_usbank_n number;
var p_user_id_c varchar2(20);
exec :p_usbank_n := 1;
exec :p_user_id_c := 'ENT\CB174';
select
cfs.cae_sec_id,
cfs.fm_sec_code,
cfs.last_user_id,
case
when 1 = :p_usbank_n then
sl.usbank_to_edit
when cfs.last_user_id = :p_user_id_c then
sl.owner_to_edit
else
sl.to_edit
end as 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);
