select to_char(sysdate,'yyyymmdd') from dual ;要的效果,是不是这个样子的?
select to_char(sysdate,'yyyymmdd') from dual ;结果: 20080417是不是这个样子?
select to_char(sysdate,'yyyymm') from dual
我是按着这么写的,可是怎么都不对啊?麻烦高手帮我看一下吧:--3个参数:站号、列名、日期 create or replace function fun_T_mete_realtime_ave(sta in t_Mete_Realtime.V12004%TYPE, col_name in varchar2,date_now in date) return number is Result_ave number; temp_var varchar2 (200); begin
temp_var:='select ave('||col_name||') as Result_ave FROM T_mete_realtime where IIIII='|| sta ||' and VYEAR=to_number(to_char('||date_now||',''YYYYMM'')) and VDAY=to_number(to_char('||date_now||',''DD''))'; EXECUTE IMMEDIATE temp_var into Result_ave; return(Result_ave); end fun_T_mete_realtime_ave;
写的大概是对的,把最后的sql,print出来 注意 ' 也是转义的意思
temp_var:='select ave(' ¦ ¦col_name ¦ ¦') into Result_ave FROM T_mete_realtime where IIIII=' ¦ ¦ sta ¦ ¦' and VYEAR=to_number(to_char(' ¦ ¦date_now ¦ ¦',''YYYYMM'')) and VDAY=to_number(to_char(' ¦ ¦date_now ¦ ¦',''DD''))'; dbms_output.put_line(temp_var); --看下你的sql EXECUTE IMMEDIATE temp_var ;
传人的日期变量date_now应该是什么形式的?我用vb.net调用的这个函数,传入的是一个vb.net中定义的日期型的变量,这样对吗?这个不知道可不可以你把这个sql,return回来就好了create or replace function fun_T_mete_realtime_ave(sta in t_Mete_Realtime.V12004%TYPE, col_name in varchar2,date_now in date) return varchar2 is Result_ave number; temp_var varchar2 (200); begin
temp_var:='select ave(' ¦ ¦col_name ¦ ¦') into Result_ave FROM T_mete_realtime where IIIII=' ¦ ¦ sta ¦ ¦' and VYEAR=to_number(to_char(' ¦ ¦date_now ¦ ¦',''YYYYMM'')) and VDAY=to_number(to_char(' ¦ ¦date_now ¦ ¦',''DD''))'; return temp_var; end fun_T_mete_realtime_ave; select fun_T_mete_realtime_ave(参数1,参数2,sysdate) from dual;
调试的时候说这句有问题?不明白问题在哪? EXECUTE IMMEDIATE temp_var into Result_ave;
select xx into Result_ave; EXECUTE IMMEDIATE temp_var ;这样写试下
谢谢,但是好像还不是主要错误原因 我在sqlplus里单独运行了这句,出错了!能帮我看看语法错在哪里了吗?select avg(V11012) as Result_ave FROM T_mete_realtime where IIIII=50136 and VYEAR=to_number(to_char('04-12月-07','YYYYMM')) and VDAY=to_number(to_char('04-12月-07','DD'));
请大家帮忙呀 select avg(V11012) as Result_ave FROM T_mete_realtime where IIIII=50136 and VYEAR=to_number(to_char('04-12月-07','YYYYMM')) and VDAY=to_number(to_char('04-12月-07','DD')); 错在哪里了? 或这句: begin dbms_output.put_line(to_date('12/04/07','YYYYMM')); end; /也是一样的错误?不明白哪错了啊!真着急!
20080417是不是这个样子?
create or replace function fun_T_mete_realtime_ave(sta in t_Mete_Realtime.V12004%TYPE,
col_name in varchar2,date_now in date) return number is
Result_ave number;
temp_var varchar2 (200);
begin
temp_var:='select ave('||col_name||') as Result_ave
FROM T_mete_realtime
where IIIII='|| sta ||' and VYEAR=to_number(to_char('||date_now||',''YYYYMM'')) and
VDAY=to_number(to_char('||date_now||',''DD''))';
EXECUTE IMMEDIATE temp_var into Result_ave;
return(Result_ave);
end fun_T_mete_realtime_ave;
注意 ' 也是转义的意思
FROM T_mete_realtime
where IIIII=' ¦ ¦ sta ¦ ¦' and VYEAR=to_number(to_char(' ¦ ¦date_now ¦ ¦',''YYYYMM'')) and
VDAY=to_number(to_char(' ¦ ¦date_now ¦ ¦',''DD''))';
dbms_output.put_line(temp_var); --看下你的sql
EXECUTE IMMEDIATE temp_var ;
col_name in varchar2,date_now in date) return varchar2 is
Result_ave number;
temp_var varchar2 (200);
begin
temp_var:='select ave(' ¦ ¦col_name ¦ ¦') into Result_ave
FROM T_mete_realtime
where IIIII=' ¦ ¦ sta ¦ ¦' and VYEAR=to_number(to_char(' ¦ ¦date_now ¦ ¦',''YYYYMM'')) and
VDAY=to_number(to_char(' ¦ ¦date_now ¦ ¦',''DD''))';
return temp_var;
end fun_T_mete_realtime_ave;
select fun_T_mete_realtime_ave(参数1,参数2,sysdate) from dual;
我想应该是转义字符没用好的问题吧?程序编译是没有问题的。
但是执行结果都是零
传字符串就去,在function里面转型
EXECUTE IMMEDIATE temp_var into Result_ave;
EXECUTE IMMEDIATE temp_var ;这样写试下
我在sqlplus里单独运行了这句,出错了!能帮我看看语法错在哪里了吗?select avg(V11012) as Result_ave FROM T_mete_realtime
where IIIII=50136 and VYEAR=to_number(to_char('04-12月-07','YYYYMM')) and
VDAY=to_number(to_char('04-12月-07','DD'));
select avg(V11012) as Result_ave FROM T_mete_realtime
where IIIII=50136 and VYEAR=to_number(to_char('04-12月-07','YYYYMM')) and
VDAY=to_number(to_char('04-12月-07','DD'));
错在哪里了?
或这句:
begin
dbms_output.put_line(to_date('12/04/07','YYYYMM'));
end;
/也是一样的错误?不明白哪错了啊!真着急!