高手进来看看,存储过程的一些问题。
declare
type type_array is varray(10) of varchar2(20);
var_array type_array := type_array('jayppt','gzyppt','jdzyppt','jjyppt','pxyppt','sryppt','xyyppt','ycyppt','ytyppt','fzyppt');
begin
for i in 1..var_array.count loop
insert into axx
(bh)
select t.case_code
from TRANSACT_CASE_REGISTER@var_array(i) T
left join (SELECT T.DEPT_ID DEPT_ID,T.DEPT_CODE CODE,T.DEPT_NAME NAME,T.UPPER_DEPT_ID FROM RS_DEPT@var_array(i) T) j on to_char(t.handle_organ) =
to_char(j.dept_id)
where t.case_code is not null
and to_char(t.report_case_date, 'yyyymmddhh24miss') > '20121101000000' and t.report_case_date<=sysdate
and not exists (select * from axx b where t.case_code = b.ajbh);
commit;
end loop;
END;
TRANSACT_CASE_REGISTER@var_array(i) 这后面这样用数组因该不可以吧?那要怎么改?
TRANSACT_CASE_REGISTER是个视图
------最佳解决方案--------------------
declare
type type_array is varray(10) of varchar2(20);
var_array type_array := type_array('jayppt','gzyppt','jdzyppt','jjyppt','pxyppt','sryppt','xyyppt','ycyppt','ytyppt','fzyppt');
begin
for i in 1..var_array.count loop
execute immediate
'insert into axx (bh)
select t.case_code
from TRANSACT_CASE_REGISTER@var_array(i) T
left join (SELECT T.DEPT_ID DEPT_ID,T.DEPT_CODE CODE,T.DEPT_NAME NAME,T.UPPER_DEPT_ID FROM RS_DEPT@'
------其他解决方案--------------------
var_array(i)
------其他解决方案--------------------
' T) j on to_char(t.handle_organ) =
to_char(j.dept_id)
where t.case_code is not null
and to_char(t.report_case_date, ''yyyymmddhh24miss'') > ''20121101000000'' and t.report_case_date<=sysdate
and not exists (select * from axx b&n