CREATE or replace
PROCEDURE TraceBatch
(
StartTime nvarchar2,
EndTIme nvarchar2,
Line nvarchar2,
PartNo nvarchar2
)
Is
Strsql nvarchar2(300);
Num number;BEGIN
Strsql:='select count(panel_sn) into Num from N_SMT_SNTRACE_T A join n_smt_panelflash_t b on a.sn_operate_code=b.sn_operate_code where
b.create_time between to_date('||StartTime||',''yyyy-mm-dd hh24:mi:ss'') and to_date('||EndTime||',''yyyy-mm-dd hh24:mi:ss'')';
execute immediate Strsql;
dbms_output.put_line(Num);
END TraceBatch;以上这个存储过程在编译的时候报表达式类型错误,请问是怎么回事?我初学Oracle,对他的很多用法都不太熟悉,以前一直是用SqlServer做开发的,请大侠路过指导一下,谢谢!
PROCEDURE TraceBatch
(
StartTime nvarchar2,
EndTIme nvarchar2,
Line nvarchar2,
PartNo nvarchar2
)
Is
Strsql nvarchar2(300);
Num number;BEGIN
Strsql:='select count(panel_sn) into Num from N_SMT_SNTRACE_T A join n_smt_panelflash_t b on a.sn_operate_code=b.sn_operate_code where
b.create_time between to_date('||StartTime||',''yyyy-mm-dd hh24:mi:ss'') and to_date('||EndTime||',''yyyy-mm-dd hh24:mi:ss'')';
execute immediate Strsql;
dbms_output.put_line(Num);
END TraceBatch;以上这个存储过程在编译的时候报表达式类型错误,请问是怎么回事?我初学Oracle,对他的很多用法都不太熟悉,以前一直是用SqlServer做开发的,请大侠路过指导一下,谢谢!
解决方案 »
- 触发器相关 紧急求救~~~~大侠们帮帮忙~~
- to_number(to_char(p_stat_time,'w'))得到什么值
- oracle 视图怎么建主键或者增加个序列都行!!!
- C# 调用Oracle数据库,怎样执行CALL
- shell脚本求解
- 急!过程动态sql的问题。。。
- 怎么导出oracle中某个用户下的所有对象(包括表、存储过程、视图、触发器、序列、包等)
- 求救,用oracle sqlloade导数据| 1,000.00|这样一个字段,其中,号怎么处理
- 关于用delphi 调用oracle 的存贮过程问题(很简单)
- 今天下午去面试,问道Oracle与SQL server的主要区别是什么,你认为因该怎么回答?
- 写个SQL语句纠结了很久
- 语句的优化
b.create_time between to_date('||StartTime||N',''yyyy-mm-dd hh24:mi:ss'') and to_date('||EndTime||N',''yyyy-mm-dd hh24:mi:ss'')';
execute immediate Strsql;数据类型不对吧,试下看看.
(
StartTime nvarchar2,
EndTIme nvarchar2,
Line nvarchar2,
PartNo nvarchar2
)
Is
strsql nvarchar2(300);
Num number;BEGIN
strsql:='select count(panel_sn) from N_SMT_SNTRACE_T A join n_smt_panelflash_t b on a.sn_operate_code=b.sn_operate_code';
execute immediate strsql ;
END TraceBatch;我把问题再简化一点吧,我按以上这个写法去执行都报错:表达式类型错误,但是如果我以下这样写
CREATE or replace PROCEDURE sfcrfmaster.TraceBatch
(
StartTime nvarchar2,
EndTIme nvarchar2,
Line nvarchar2,
PartNo nvarchar2
)
Is
strsql nvarchar2(300);
Num number;BEGIN
execute immediate 'select count(panel_sn) from N_SMT_SNTRACE_T A join n_smt_panelflash_t b on a.sn_operate_code=b.sn_operate_code' ;
END TraceBatch;却可以,我怀疑是不是execute immediate 的问题?
应该是 dbms_output.put_line(to_char(Num));
CREATE OR REPLACE PROCEDURE TraceBatch
( StartTime nvarchar2,
EndTime nvarchar2,
Line nvarchar2,
PartNo nvarchar2
)
IS
strsql nvarchar2(1000);
num number(18,0);
BEGIN
strsql := ' select count(panel_sn) into Num from N_SMT_SNTRACE_T A join n_smt_panelflash_t b on a.sn_operate_code=b.sn_operate_code ';
strsql := strsql||' where b.create_time between to_date(:starttime),''yyyy-mm-dd hh24:mi:ss'' and to_date(:endtime),''yyyy-mm-dd hh24:mi:ss'')';
execute immediate strsql using StartTime, EndTime;
dbms_output.put_line(to_char(num));
END TraceBatch;
/
-- *(1) 日期区间尽量少用between ... and ... ,具体原因请参考:http://topic.csdn.net/u/20110324/09/12bf9066-57c8-4cdf-8481-d9efad646c5c.html
-- *(2) 尽量使用绑定变量,如上所示: to_date(:starttime)...to_date(:endtime)... using StartTime, EndTime;
EndTIme nvarchar2,
Line nvarchar2,
PartNo nvarchar2) Is
Strsql varchar2(300);
Num number;BEGIN
Strsql := 'select count(panel_sn) from N_SMT_SNTRACE_T A join n_smt_panelflash_t b on a.sn_operate_code=b.sn_operate_code and
b.create_time between to_date('' StartTime
'',''yyyy-mm-dd hh24:mi:ss'')
and to_date(''EndTime '',''yyyy-mm-dd hh24:mi:ss'')';
execute immediate Strsql
into num;
dbms_output.put_line(Num);
END TraceBatch;
--试试看
当你把SQL用execute immediate 'sql语句'的时候编译就没有问题了。
但是为什么CREATE or replace PROCEDURE TraceBatch(StartTime nvarchar2,
EndTIme nvarchar2,
Line nvarchar2,
PartNo nvarchar2) Is
Strsql nvarchar2(300);
Num number;BEGIN
execute immediate 'select count(panel_sn) into Num from N_SMT_SNTRACE_T A join n_smt_panelflash_t b on a.sn_operate_code=b.sn_operate_code where
b.create_time between to_date(' || StartTime ||
',''yyyy-mm-dd hh24:mi:ss'') and to_date(' || EndTime ||
',''yyyy-mm-dd hh24:mi:ss'')';
dbms_output.put_line(Num);
END TraceBatch;
这样就行,没有找到原因