为什么我得到绑定变量“DeliveryDate_Variable”未声明（Oracle 完全新）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2708981/
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
Why do I get Bind Variable "DeliveryDate_Variable" is NOT DECLARED(Completely New TO Oracle)
提问by GigaPr
I have the following script in Oacle I do not understand why i get
我在 Oacle 中有以下脚本我不明白为什么我得到
Bind Variable "DeliveryDate_Variable" is NOT DECLARED
绑定变量“DeliveryDate_Variable”未声明
Everything looks ok to me
对我来说一切都很好
VARIABLE RollingStockTypeId_Variable NUMBER := 1;
VARIABLE DeliveryDate_Variable DATE := (to_date('2010/8/25:12:00:00AM', 'yyyy/mm/dd:hh:mi:ssam'));
SELECT DISTINCT
rs.Id,
rs.SerialNumber,
rsc.Name AS Category,
(SELECT COUNT(Id) from ROLLINGSTOCKS WHERE ROLLINGSTOCKCATEGORYID = rsc.id) as "Number Owened",
(SELECT COUNT(rs.Id)
FROM ROLLINGSTOCKS rs
WHERE rs.ID NOT IN( select RollingStockId
from ROLLINGSTOCK_ORDER
WHERE :DeliveryDate_Variable BETWEEN DEPARTUREDATE AND DELIVERYDATE)
AND rs.RollingStockCategoryId IN (Select Id
from RollingStockCategories
Where RollingStockTypeId = :RollingStockTypeId_Variable)
AND rs.RollingStockCategoryId = rsc.Id) AS "Number Available"
FROM ROLLINGSTOCKS rs
JOIN RollingStockCategories rsc ON rsc.Id = rs.RollingStockCategoryId
WHERE rs.ID NOT IN(
select RollingStockId
from ROLLINGSTOCK_ORDER
WHERE :DeliveryDate_Variable BETWEEN DEPARTUREDATE AND DELIVERYDATE
)
AND rs.RollingStockCategoryId IN
(
Select Id
from RollingStockCategories
Where RollingStockTypeId = :RollingStockTypeId_Variable
)
ORDER BY rsc.Name
回答by APC
It is a definitely odd quirk of SQL*plus that the list of allowable datatypes for variablesdoes not include DATE.
SQL*plus 的一个绝对奇怪的怪癖是变量的允许数据类型列表不包括 DATE。
The solution is to declare "date" variables as varchar2(9) or barchar2(18) (depending on whether we want include the time element) and then cast the variables TO_DATE() as necessary.
解决方案是将“日期”变量声明为 varchar2(9) 或 barchar2(18)（取决于我们是否想要包含时间元素），然后根据需要转换变量 TO_DATE()。
回答by GigaPr
I Managed to find the problem, for some reason Oracle didn't like the casting ov the sting to date (in line)
我设法找到了问题，出于某种原因，Oracle 不喜欢迄今为止的投射（在线）
This is how i changed it
这就是我改变它的方式
VARIABLE RollingStockTypeId_Variable NUMBER;
exec :RollingStockTypeId_Variable := 2;
VARIABLE DeliveryDate_Variable VARCHAR2(30);
exec :DeliveryDate_Variable := '2010/8/25:12:00:00AM';
SELECT DISTINCT
rs.Id,
rs.SerialNumber,
rsc.Name AS Category,
(SELECT COUNT(Id) from ROLLINGSTOCKS WHERE ROLLINGSTOCKCATEGORYID = rsc.id) as "Number Owened",
(SELECT COUNT(rs.Id)
FROM ROLLINGSTOCKS rs
WHERE rs.ID NOT IN( select RollingStockId
from ROLLINGSTOCK_ORDER
WHERE (to_date(:DeliveryDate_Variable, 'yyyy/mm/dd:hh:mi:ssam')) BETWEEN DEPARTUREDATE AND DELIVERYDATE)
AND rs.RollingStockCategoryId IN (Select Id
from RollingStockCategories
Where RollingStockTypeId = :RollingStockTypeId_Variable)
AND rs.RollingStockCategoryId = rsc.Id) AS "Number Available"
FROM ROLLINGSTOCKS rs
JOIN RollingStockCategories rsc ON rsc.Id = rs.RollingStockCategoryId
WHERE rs.ID NOT IN(
select RollingStockId
from ROLLINGSTOCK_ORDER
WHERE (to_date(:DeliveryDate_Variable, 'yyyy/mm/dd:hh:mi:ssam')) BETWEEN DEPARTUREDATE AND DELIVERYDATE
)
AND rs.RollingStockCategoryId IN
(
Select Id
from RollingStockCategories
Where RollingStockTypeId = :RollingStockTypeId_Variable
)
ORDER BY rsc.Name
回答by MJB
I could be wrong, but I thought that you had to initialize VARIABLES without functions -- that is, I thought that TO_DATE was not allowed.
我可能是错的，但我认为您必须在没有函数的情况下初始化 VARIABLES -- 也就是说，我认为不允许使用 TO_DATE。
I think it is because the VARIABLE declaration is not really part of SQL -- it is specific to SQLPlus, and you can't drop back and forth between the tw.
我认为这是因为 VARIABLE 声明实际上并不是 SQL 的一部分——它特定于 SQLPlus，并且您不能在 tw 之间来回切换。
回答by Doug Porter
The datedatatype is not allowed for variables declared in SQLPlus. You have to create the variable as varcharand then use to_date. SQLPlus variable syntax
dateSQLPlus 中声明的变量不允许使用该数据类型。您必须创建变量 asvarchar然后使用to_date. SQLPlus 变量语法
