procedure intensity1(p_dtype number,p_type number,p_date varchar2,p_year varchar2,p_mon varchar2,p_district varchar2 ,p_rc out myrctype)
is
t_date time_thunder.thunder_date%type;
swhere varchar2(800);
sdate varchar2(500);
stype varchar2(500);
sdis varchar2(500);
str varchar2(8000);
begin
t_date:=to_date(p_date,'yyyy-mm-dd');
if p_type=1 then
sdate:='and thunder_date=t_date';
end if;
if p_type=2 then
sdate:=' and to_char(thunder_date,''yyyy'')=p_year and to_char(thunder_date,''mm'')=p_mon';
end if ;
if p_type=3 then
sdate:=' and to_char(thunder_date,''yyyy'')=p_year ';
end if ;
if p_type=1 then
stype:='and replay_number=1';
else
stype:='';
end if;if p_district is null then
sdis:='';
else
sdis:=' and fall_district=p_district';
end if ;
swhere:=sdate+stype+sdis;
这里该如何写?
----------------------------
str:='select count(id) a from history_thunder where cast(intensity as float)>=2 swhere '/?
---------------------
open p_rc for str;
end;
is
t_date time_thunder.thunder_date%type;
swhere varchar2(800);
sdate varchar2(500);
stype varchar2(500);
sdis varchar2(500);
str varchar2(8000);
begin
t_date:=to_date(p_date,'yyyy-mm-dd');
if p_type=1 then
sdate:='and thunder_date=t_date';
end if;
if p_type=2 then
sdate:=' and to_char(thunder_date,''yyyy'')=p_year and to_char(thunder_date,''mm'')=p_mon';
end if ;
if p_type=3 then
sdate:=' and to_char(thunder_date,''yyyy'')=p_year ';
end if ;
if p_type=1 then
stype:='and replay_number=1';
else
stype:='';
end if;if p_district is null then
sdis:='';
else
sdis:=' and fall_district=p_district';
end if ;
swhere:=sdate+stype+sdis;
这里该如何写?
----------------------------
str:='select count(id) a from history_thunder where cast(intensity as float)>=2 swhere '/?
---------------------
open p_rc for str;
end;
这样写提示报错,请问大侠该如何改写才正确。
create or replace function f_sczhdt_jc(as_sql in long,as_table in varchar2) return number is
v_sql varchar2(2000);
v_sum number(12,2);
BEGIN
v_sql := 'SELECT sum(nvl(a.ljjc,0)) from a ,'||as_table||' where a.wellno='||as_table||'.wellno ';
if as_sql is not null then
v_sql := v_sql||as_sql;
end if;
-- DBMS_OUTPUT.PUT_LINE(v_sql);
execute immediate v_sql into v_sum;
return v_sum;
end f_sczhdt_jc;
V_DY_SQL_CUR := ' SELECT sum(nvl(dchjsj,0)) + sum(nvl(yhnjsj,0))+ sum(nvl(tjqxz,0)) + sum(nvl(zlyssj,0)) as djw
FROM tt where ( to_char(rbsjsc,''yyyy-mm-dd'') = ''' ||
AS_DATE || ''' ) AND (a in (' || AS_WHERE || '))';
OPEN CUR_DY_FIND FOR V_DY_SQL_CUR;
FETCH CUR_DY_FIND
INTO RESULT;
CLOSE CUR_DY_FIND;
RETURN(RESULT);
如果在运行过程中要看那条动态的sql语句,该怎么写?