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) issqlstr varchar2(4000);beginsqlstr := '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 isbegin 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调用存储过程