解决方案 »
- 如何在Oracle中调用MSSQL的存储过程?
- 这样的PL/SQL怎么写?
- 网络通信的储存过程
- put_line()和putf()的区别,大家看着俩句话
- 关于oracle的系统表问题?
- 对clob字段的查询可用dbms_lob.instr(clob_field,'some text',1,1) > 0作为查询条件,但好像直接用instr(clob_field,'some text',1,1) >
- Oracle 新手请教,如何查询时间字段?
- 求大神帮忙看看
- Oracle如何在更改一个字段值后,不影响查询该字段值的历史数据?
- PLsql导入表的时候 user sql plus选项是空的
- VS2005下用occi访问Oracle11g
- Pro *c 删除表的问题
execute immediate v_sql_str into v_aae017;
--改成:
begin
execute immediate v_sql_str into v_aae017;
exception
when others then
v_aa3017 := '';
end;
=========================================================================
create or replace procedure check_person(
in_aae017 in varchar2, --查询被合并人员操作人所在机构代码
in_aac001 in varchar2, --将被合并人员内码
msg out varchar2) is
v_aae017 varchar2(20);
v_aac002 varchar2(18);
v_aac003 varchar2(18);
v_aab300 varchar2(60);
v_aab003 varchar2(18);
v_bunes varchar2(2000);
v_tname varchar2(2000);
v_sql_str varchar2(2000);
cursor t_name is
select table_name
from user_tab_cols
where column_name = 'AAC001'
and (length(table_name) = 4 or length(table_name) = 5)
and table_name not like 'S%'
and table_name not like '%AC01%';
--AC01表是人员基础信息表,AAC001是该表的主键也是各业务表需要引用的外键字段
--AAC002和AAC003分别表示人员的身份证号码和姓名
begin
msg := null;
v_aae017 := null;
open t_name;
loop
fetch t_name
into v_tname;
exit when t_name%notfound;
v_sql_str := 'select aae017 from ' || v_tname || ' where aac001 = ''' ||
in_aac001 || '''';
begin
execute immediate v_sql_str
into v_aae017;
exception
when others then
v_aae017 := null;
end;
if v_aae017 is not null and v_aae017 <> in_aae017 then
select comments
into v_bunes
from user_tab_comments
where table_name = v_tname;
select aab003 into v_aab003 from sc01 where bsc001 = v_aae017;
select aab300
into v_aab300
from sc01
where aab003 = (case when length(v_aab003) > 6 then
substr(v_aab003, 0, 4) || '01' else v_aab003 end);
if v_bunes is not null then
if v_bunes like '%意愿%' then
v_bunes := '就业培训报名';
end if;
msg := msg || ' ' || v_aab300 || ' ' || (case when v_bunes like '%$%' then substr(v_bunes, 0, length(v_bunes) - 4) else v_bunes end);
end if;
end if;
end loop;
if msg is not null then
begin
select aac002, aac003
into v_aac002, v_aac003
from ac01
where aac001 = in_aac001;
exception
when others then
v_aac002 := null;
v_aac003 := null;
end;
msg := (case when v_aac002 is not null and v_aac003 is not null then v_aac002 || ' ' || v_aac003 || ' 在其他地区存在以下业务数据,请与当地业务科室联系确认之后再做人员合并! ' || msg else '系统中不存在该人员的基础信息,请核实!' end);
end if;
return;
EXCEPTION
WHEN OTHERS THEN
begin
msg := sqlerrm;
return;
end;
end check_person;