create or replace function "FN_COMPANY_CHANGE_TO_GL"(iFiscal IN INTEGER)
RETURN VARCHAR2
is
Result varchar2(15);
SQLS varchar2(300);
stbl varchar2(60);
coln varchar2(60);
YSDW NUMBER;
GNFL NUMBER;
ZJLY NUMBER;
ZBLY NUMBER;
ZFFS NUMBER;
CURSOR C1 IS
select table_name, column_name from TEMP_COCODE;BEGIN
select count(*) into YSDW
from GL_COMPANY_CHANGE WHERE BEGIN_CHANGE='更新';
select count(*) into GNFL
from GL_GNFL_CHANGE WHERE BEGIN_CHANGE='更新';
select count(*) into ZJLY
from GL_ZJLY_CHANGE WHERE BEGIN_CHANGE='更新';
select count(*) into ZBLY
from GL_ZBLY_CHANGE WHERE BEGIN_CHANGE='更新';
select count(*) into ZFFS
from GL_ZFFS_CHANGE WHERE BEGIN_CHANGE='更新';
IF YSDW > 0
THEN
update gl_vou_head t
set t.co_code =(SELECT CO_CODE FROM gl_company_change)
where t.I_co_code =(SELECT I_CO_CODE FROM gl_company_change)
and t.fiscal =iFiscal
AND (select BEGIN_CHANGE from gl_company_change)='更新';
update gl_vou_detail p
set p.co_code = (SELECT CO_CODE FROM gl_company_change)
where p.I_co_code = (SELECT I_CO_CODE FROM gl_company_change)
and p.fiscal = iFiscal
AND (select BEGIN_CHANGE from gl_company_change)='更新';
update gl_vou_detail_ass q
set q.co_code = (SELECT CO_CODE FROM gl_company_change)
where q.I_co_code = (SELECT I_CO_CODE FROM gl_company_change)
and q.fiscal =iFiscal
AND (select BEGIN_CHANGE from gl_company_change)='更新';
update GL_COMPANY_CHANGE set BEGIN_CHANGE='已更新' where BEGIN_CHANGE='更新';
OPEN C1;
loop
FETCH C1
INTO stbl, coln;
exit when c1%notfound;
SQLS := 'update ' || stbl || ' set ' || coln || ' =(SELECT CO_CODE FROM gl_company_change)
where ' || coln || ' = (SELECT C_CO_CODE FROM gl_company_change)';
execute immediate sqls;
end loop;
close c1;
elsif GNFL >0
THEN
update gl_vou_detail_ass q
set q.b_acc_code = (SELECT B_ACC_CODE FROM gl_gnfl_change)
where q.SY_XTM_GNFL = (SELECT SY_XTM_GNFL FROM gl_gnfl_change)
and q.fiscal =iFiscal
AND (select BEGIN_CHANGE from gl_gnfl_change)='更新';
elsif ZJLY >0
THEN
update gl_vou_detail_ass q
set q.ACC_ITEM3 = (SELECT B_ACC_ITEM3 FROM gl_ZJLY_change)
where q.SY_XTM_ZJLY = (SELECT SY_XTM_ZJLY FROM gl_ZJLY_change)
and q.fiscal =iFiscal
AND (select BEGIN_CHANGE from gl_ZJLY_change)='更新';
elsif ZBLY >0
THEN
update gl_vou_detail_ass q
set q.ACC_ITEM8 = (SELECT B_ACC_ITEM8 FROM gl_ZBLY_change)
where q.SY_XTM_ZBLY = (SELECT SY_XTM_ZBLY FROM gl_ZBLY_change)
and q.fiscal =iFiscal
AND (select BEGIN_CHANGE from gl_ZBLY_change)='更新';
elsif ZFFS >0
THEN
update gl_vou_detail_ass q
set q.ACC_ITEM6 = (SELECT B_ACC_ITEM6 FROM gl_ZFFS_change)
where q.SY_XTM_ZFFS = (SELECT SY_XTM_ZFFS FROM gl_ZFFS_change)
and q.fiscal =iFiscal
AND (select BEGIN_CHANGE from gl_ZFFS_change)='更新';
END IF;
dbms_output.put_line(Result);
return(Result);
END FN_COMPANY_CHANGE_TO_GL;
在执行SELECT FN_COMPANY_CHANGE_TO_GL FROM DUAL时报错ORA-06553: PLS-306: wrong number or types of arguments in call to "FN_COMPANY_CHANGE_TO_GL",到底哪出了问题呢。。
RETURN VARCHAR2
is
Result varchar2(15);
SQLS varchar2(300);
stbl varchar2(60);
coln varchar2(60);
YSDW NUMBER;
GNFL NUMBER;
ZJLY NUMBER;
ZBLY NUMBER;
ZFFS NUMBER;
CURSOR C1 IS
select table_name, column_name from TEMP_COCODE;BEGIN
select count(*) into YSDW
from GL_COMPANY_CHANGE WHERE BEGIN_CHANGE='更新';
select count(*) into GNFL
from GL_GNFL_CHANGE WHERE BEGIN_CHANGE='更新';
select count(*) into ZJLY
from GL_ZJLY_CHANGE WHERE BEGIN_CHANGE='更新';
select count(*) into ZBLY
from GL_ZBLY_CHANGE WHERE BEGIN_CHANGE='更新';
select count(*) into ZFFS
from GL_ZFFS_CHANGE WHERE BEGIN_CHANGE='更新';
IF YSDW > 0
THEN
update gl_vou_head t
set t.co_code =(SELECT CO_CODE FROM gl_company_change)
where t.I_co_code =(SELECT I_CO_CODE FROM gl_company_change)
and t.fiscal =iFiscal
AND (select BEGIN_CHANGE from gl_company_change)='更新';
update gl_vou_detail p
set p.co_code = (SELECT CO_CODE FROM gl_company_change)
where p.I_co_code = (SELECT I_CO_CODE FROM gl_company_change)
and p.fiscal = iFiscal
AND (select BEGIN_CHANGE from gl_company_change)='更新';
update gl_vou_detail_ass q
set q.co_code = (SELECT CO_CODE FROM gl_company_change)
where q.I_co_code = (SELECT I_CO_CODE FROM gl_company_change)
and q.fiscal =iFiscal
AND (select BEGIN_CHANGE from gl_company_change)='更新';
update GL_COMPANY_CHANGE set BEGIN_CHANGE='已更新' where BEGIN_CHANGE='更新';
OPEN C1;
loop
FETCH C1
INTO stbl, coln;
exit when c1%notfound;
SQLS := 'update ' || stbl || ' set ' || coln || ' =(SELECT CO_CODE FROM gl_company_change)
where ' || coln || ' = (SELECT C_CO_CODE FROM gl_company_change)';
execute immediate sqls;
end loop;
close c1;
elsif GNFL >0
THEN
update gl_vou_detail_ass q
set q.b_acc_code = (SELECT B_ACC_CODE FROM gl_gnfl_change)
where q.SY_XTM_GNFL = (SELECT SY_XTM_GNFL FROM gl_gnfl_change)
and q.fiscal =iFiscal
AND (select BEGIN_CHANGE from gl_gnfl_change)='更新';
elsif ZJLY >0
THEN
update gl_vou_detail_ass q
set q.ACC_ITEM3 = (SELECT B_ACC_ITEM3 FROM gl_ZJLY_change)
where q.SY_XTM_ZJLY = (SELECT SY_XTM_ZJLY FROM gl_ZJLY_change)
and q.fiscal =iFiscal
AND (select BEGIN_CHANGE from gl_ZJLY_change)='更新';
elsif ZBLY >0
THEN
update gl_vou_detail_ass q
set q.ACC_ITEM8 = (SELECT B_ACC_ITEM8 FROM gl_ZBLY_change)
where q.SY_XTM_ZBLY = (SELECT SY_XTM_ZBLY FROM gl_ZBLY_change)
and q.fiscal =iFiscal
AND (select BEGIN_CHANGE from gl_ZBLY_change)='更新';
elsif ZFFS >0
THEN
update gl_vou_detail_ass q
set q.ACC_ITEM6 = (SELECT B_ACC_ITEM6 FROM gl_ZFFS_change)
where q.SY_XTM_ZFFS = (SELECT SY_XTM_ZFFS FROM gl_ZFFS_change)
and q.fiscal =iFiscal
AND (select BEGIN_CHANGE from gl_ZFFS_change)='更新';
END IF;
dbms_output.put_line(Result);
return(Result);
END FN_COMPANY_CHANGE_TO_GL;
在执行SELECT FN_COMPANY_CHANGE_TO_GL FROM DUAL时报错ORA-06553: PLS-306: wrong number or types of arguments in call to "FN_COMPANY_CHANGE_TO_GL",到底哪出了问题呢。。
这样把代码贴出来,可读性很低的