1、新建SQL窗口;
2、输入以下SQL:
不带参数:
1
2
3
4
5
6
7
8
9
10
11
12
|
create
or
replace
procedure
过程名称后不要加小括号
create
or
replace
procedure
p_syn_equipment_20161205
is
sqlstr varchar2(4000);
begin
--清空表
sqlstr :=
'truncate table staff_20161205'
;
execute
immediate sqlstr;
--插入数据
sqlstr :=
'insert into staff_20161205 select * from tb_base_staff s where s.staff_name like '
'王%'
' '
;
execute
immediate sqlstr;
commit
;
end
;
|
带参数:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
create
or
replace
procedure
p_syncossequipment(jndi
in
varchar2,res_spec_id
in
varchar2)
is
sqlstr varchar2(4000);
begin
sqlstr :=
'truncate table tml_2_area'
;
execute
immediate sqlstr;
sqlstr :=
'insert into tml_2_area
select t3.tml_id,t3.zone,t3.area_id from ... where hx<2'
;
execute
immediate sqlstr;
commit
;
sqlstr :=
'truncate table ossequipment'
;
execute
immediate sqlstr;
sqlstr :=
'insert into ossequipment
select * from phy_equipment@'
||jndi||'
on
st.tml_id=pe.tml_id;
execute
immediate sqlstr;
commit
;
end
;
|
或者
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
create
or
replace
procedure
p_sync_dynamic_ossequipment(jndi
in
varchar2)
is
sqlstr varchar2(4000);
begin
sqlstr :=
'truncate table tb_dynamic_ossequipment'
;
execute
immediate sqlstr;
sqlstr :=
'insert into tb_dynamic_ossequipment
select * from phy_equipment@'
||jndi||
' pe
left join spc_tml@'
||jndi||
' st on pe.tml_id=st.tml_id
left join phy_eqp_unit@'
||jndi||
' peu on peu.unit_id = pe.install_unit_id
left join bse_room@'
||jndi||
' br on pe.bse_eqp_id = br.room_id
where pe.res_spec_id in (703, 704, 411, 2530, 414)'
;
execute
immediate sqlstr;
commit
;
end
;
|
带异常处理:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
|
create
or
replace
procedure
proc_cablecheck_equipment_sync
is
begin
begin
execute
immediate
'truncate table tb_cablecheck_equipment_demo'
;
--将设备进行备份
insert
into
tb_cablecheck_equipment_demo
select
*
from
tb_cablecheck_equipment;
commit
;
--然后对设备表进行清空处理
execute
immediate
'truncate table tb_cablecheck_equipment'
;
--对tb_cablecheck_equipment_test进行错误处理
delete
tb_cablecheck_equipment_test t
where
t.equipment_id
in
(
select
t.equipment_id
from
tb_cablecheck_equipment_test t
group
by
t.equipment_id
having
count
(1) > 1);
commit
;
insert
into
tb_cablecheck_equipment
(equipment_id,
equipment_code,
equipment_name,
area_id,
address,
res_type_id,
res_type,
manage_area_id,
manage_area,
management_mode,
isrelated,
staff_id,
create_date,
ischecked,
check_date,
operate_staff,
parent_area_id
)
select
tcet.equipment_id,
tcet.equipment_code,
tcet.equipment_name,
tcet.area_id,
tcet.address,
tcet.res_type_id,
tcet.res_type,
tcet.manage_area_id,
tcet.manage_area,
tced.management_mode,
tced.isrelated,
tced.staff_id,
tcet.create_date,
tced.ischecked,
tced.check_date,
tced.operate_staff,
(
select
a.parent_area_id
from
area a
where
a.area_id = tcet.area_id) parent_area_id
from
tb_cablecheck_equipment_test tcet,
tb_cablecheck_equipment_demo tced
where
tcet.equipment_id = tced.equipment_id(+);
commit
;
proc_cablecheck_log(
'proc_cablecheck_equipment_sync:同步全省设备成功'
);
--异常处理
exception
when
others
then
proc_cablecheck_log(
'proc_cablecheck_equipment_sync:同步全省设备失败'
);
null
;
end
;
end
;
|
3、执行(F8):会看到Procedures目录下多了一个p_syn_equipment_20161205的文件
4、右键选中,点击测试,弹出新窗口,执行(F8)即可。
5、Mybatis调用存储过程