Question

Oracle 存储过程insert into select 日期条件问题

遇到个不解问题：
先来三段存储过程，奇怪问题在其中（注意日期条件）：
--(1) T_Name有数据
create or replace procedure P_Name(
sStartDate in varchar2,
sEndDate in varchar2
) as
begin
Execute Immediate 'insert into T_Name '||
'select b.CWStyleID,b.MaterialID,b.OutNum Amount '||
'from T_Table1 a,T_Table2 b,T_Table3 c '||
'where to_char(a.CWDate,''yyyy-MM-dd'') between '''||sStartDate||''' and '''||sEndDate||''' '||
'and a.ID=b.ID '||
'and a.ID1=c.ID and b.ID1=344312 ';
commit;
exception
when others then
rollback;
end P_Name;

--(2) T_Name没有数据
create or replace procedure P_Name(
sStartDate in varchar2,
sEndDate in varchar2
) as
begin
insert into T_Name
select b.CWStyleID,b.MaterialID,b.OutNum Amount
from T_Table1 a,T_Table2 b,T_Table3 c
where to_char(a.CWDate,'yyyy-MM-dd') between sStartDate and sEndDate
and a.ID=b.ID
and a.ID1=c.ID and b.ID1=344312;
commit;
exception
when others then
rollback;
end P_Name;

--(3) T_Name有数据
create or replace procedure P_Name(
sStartDate in varchar2,
sEndDate in varchar2
) as
begin
insert into T_Name
select b.CWStyleID,b.MaterialID,b.OutNum Amount
from T_Table1 a,T_Table2 b,T_Table3 c
where to_char(a.CWDate,'yyyy-MM-dd') between '2014-06-30' and '2014-07-11'
and a.ID=b.ID
and a.ID1=c.ID and b.ID1=344312;
commit;
exception
when others then
rollback;
end P_Name;
从三段过程总结来看，在存储过程中，insert into select 嵌套语句无法识别变量，直接select没问题(字数原因没罗列)。
无法理解，那位大大帮忙解释下,，谢谢。
问题的关键是过程（2）这种方式为什么没有数据？
其实大家可以把焦点放在日期变量上，因为过程（1）通过Execute Immediate执行是成功的（有数据），日期采用变量方式（与过程2一样）；过程（3）也是成功的，相比较过程（2），只是日期采用具体方式。

---很抱歉，因为财富值问题，无法跟进大家的回答。谢谢各位了，让我知道问题的根源出在传入参数上，尽管暂时不知道原因。

Answer 1

首先你测试看看，第二段的日期是不是没有传递进去，在ORACLE里面设置输出跟踪到底问题出在哪儿，insert into select 嵌套语句是可以识别变量的，你的语句没什么问题，看看是不是你传递参数没到位

追问

传递进去了
我去掉insert into ,直接
select count(*) into nCount .......
然后将nCount插入到一个临时表，是大于0的；同时也试过将变量sStartDate和sEndDate插入临时表，再取出来，是有日期的

追答

行了，我大概知道怎么回事了，between and语法问题，你试试测试一下，between 。。and 。。可以比较字符串吗？我测试了一下，是有问题的，这能比较数字和时间类型。速回

追问

我一直采用这种方式，而且我试过，直接select，也用between 。。and没有问题

追答

你试过时间类型的吗？

追问

那我试试，不过从过程（1）与（2）比较来看，（1）是成功的

采用日期方式试过了，不行

追答

我试过了，在里面是可以用变量的，而且我插入成功了

Answer 2

1. oracle的在日期转换的时候，月份用mm

   to_char(a.CWDate,'yyyy-mm-dd')
   
   

追问

改后测试了，还是不行

追答

你需要确认一下sStartDate 和 sEndDate 传入的值内容。

看是不是 'yyyy-mm-dd' 格式的。

追问

是的，插入临时表看过，而且过程1也成功的，不过问题就出现在参数上，在过程中重新设置日期变量就OK,可能转换有问题,单不知为什么。

追答

语句看着是没有问题的，

你可以在执行前，将 sStartDate   和  sEndDate  插入 一个测试的表中，并提交，然后在  执行后看看结果。

这样就不要猜来猜去的了。

另外还有一个方法，用日期做比较：

insert into T_Name
     select b.CWStyleID,b.MaterialID,b.OutNum Amount
        from T_Table1 a,T_Table2 b,T_Table3 c
        where a.CWDate  between to_date(sStartDate,'yyyy-mm-dd') and to_date(sEndDate,'yyyy-mm-dd')          and a.ID=b.ID
          and a.ID1=c.ID and b.ID1=344312;
   commit;

Answer 3

大小写是没关系的，insert into select要带上字段，你把字段给自己省略了，好像这样是不行的，T_NAME表的字段要写上

追问

试过了，还是不行

追答

从你的存储过程来看没什么问题，条件都很对，应该不会出现没插入数据

追问

我也纳闷，过程（1）采用动态Execute Immediate执行是OK的

追答

从你的存储过程来看没什么问题，条件都很对，应该不会出现没插入数据，你可以这样创建个存储过程不带参数的，然后在里面开头的时候手动设置变量
create or replace procedure P_Name as
sStartDate varchar2(10);
sEndDate varchar2(10);
begin
sStartDate := '2014-06-30';
sEndDate := '2014-07-11';
insert into T_Name
select b.CWStyleID,b.MaterialID,b.OutNum Amount
from T_Table1 a,T_Table2 b,T_Table3 c
where to_char(a.CWDate,'yyyy-MM-dd') between sStartDate and sEndDate
and a.ID=b.ID
and a.ID1=c.ID and b.ID1=344312;
commit;
exception
when others then
rollback;
end P_Name;
这样试试

追问

试了，这种方式OK，但需要重新获取日期值设置日期变量。
测试时发现传入的参数在转换上出现了问题（不知为什么），如你所说，在过程中重新设置没有问题。

追答

你调用的时候是怎么调用的，是PLSQL客户端还是exec调用的？仔细看看你调用的时候是不是写的有问题

追问

能确定的是，写法没有问题，因为直接复制出来，去掉insert into，直接select count(*) into ncount ,将ncount插入临时表查看，显示是有数据的。
谢谢你了，因为财富值问题，可能暂时无法跟进你的回答。

Answer 4

我也碰到这个问题了啊 怎么解决啊 无法识别 定义的常量日期区间