这样就可以实现,怎么这儿人气这么不旺了?
这样的问题都没人回答:)
v_cursorid integer;
v_cursorret integer;
v_statement varchar2(2000);
BEGIN
select to_char(last_day(to_date(arg_date8,'YYYYMMDD')),'YYYYMMDD') into arg_date from sys.dual;
str_snks := to_char(to_number(substr(arg_date,1,4)) - 1) || '0101';
str_snjz := to_char(to_number(substr(arg_date,1,4)) - 1) || '1231';
str_sntqjz := to_char(add_months(to_date(arg_date,'YYYYMMDD'),-12),'YYYYMMDD');
str_snsyjz := to_char(add_months(to_date(arg_date,'YYYYMMDD'),-13),'YYYYMMDD');
str_sytq := to_char(add_months(to_date(arg_date,'YYYYMMDD'),-1),'YYYYMMDD');
str_bnks := substr(arg_date,1,4) || '0101';
str_byks := substr(arg_date,1,6) || '01';
delete from TGYBSJ_TBL
where bbdm = '0017' and bbnd = substr(arg_date,1,4) and bbyf = substr(arg_date,5,2);
commit;
delete from JSPMB_TEMP_TBL where jspmb_temp_tbl.tbl_name = 'zqhgtjb';
commit;
v_statement := 'SELECT JSCY_TBL.CYSX,sum(ZQJGL) ';
v_statement := v_statement || ' FROM JSCY_TBL,JSHT_TBL ';
v_statement := v_statement || ' WHERE ( JSHT_TBL.FQFTGZH = JSCY_TBL.ZZJGDM ) and ( JSHT_TBL.YWLX = ';
v_statement := v_statement || '''0002''' || ' ) and ( JSHT_TBL.SCJGR >= to_date(:str_var1,';
v_statement := v_statement || '''YYYYMMDD''' || ') and JSHT_TBL.SCJGR <= to_date(:str_var2,';
v_statement := v_statement || '''YYYYMMDD''' || ') )';
v_statement := v_statement || ' and ((substr(jsht_tbl.fqftgzh,1,1) = ' || '''Z''' || 'or substr(jsht_tbl.sqftgzh,1,1) = ' || '''Z''';
v_statement := v_statement || ') or ((substr(jsht_tbl.fqftgzh,1,1) = ' || '''A''' || 'and substr(jsht_tbl.sqftgzh,6,6) <= ' || '''000010''';
v_statement := v_statement || ')and (substr(jsht_tbl.sqftgzh,1,1) = ' || '''A''' || 'and substr(jsht_tbl.sqftgzh,6,6) <= ' || '''000010''' || ')))' ;
v_statement := v_statement || ' group by JSCY_TBL.CYSX ' || ' order by JSCY_TBL.CYSX ';
v_cursorid := dbms_sql.open_cursor;
dbms_sql.parse(v_cursorid,v_statement,dbms_sql.native);
dbms_sql.bind_variable(v_cursorid,':str_var1',str_byks);
dbms_sql.bind_variable(v_cursorid,':str_var2',arg_date);
dbms_sql.define_column(v_cursorid,1,str_cylb,4);
dbms_sql.define_column(v_cursorid,2,num_sl);
v_cursorret := dbms_sql.execute(v_cursorid);
LOOP
if dbms_sql.fetch_rows(v_cursorid) = 0 then
exit;
end if;
dbms_sql.column_value(v_cursorid,1,str_cylb);
dbms_sql.column_value(v_cursorid,2,num_sl);
insert into jspmb_temp_tbl(tbl_name,cyzh,lx,num_1)
values('zqhgtjb',str_cylb,'0001',num_sl );
end LOOP;
dbms_sql.close_cursor(v_cursorid);
commit;