informix存储过程迁移oracle

y331044508

需求：需要将informix数据库中的存储过程通过shell脚本自动转化为oracle中的存储过程

informix存储样例：

1. drop procedure circuit_tmp_update();
   
2. create procedure circuit_tmp_update();
   
3.     define objectid_v varchar(128);
   
4.     define circuitnumber_v varchar(128);
   
5.     define aendzhandianobjectid_v varchar(128);
   
6.     define zendzhandianobjectid_v varchar(128);
   
7.     define aendswitchbuildingobjectid_v varchar(128);
   
8.     define zendswitchbuildingobjectid_v varchar(128);
   
9.     define rel_zhandian_id_v varchar(128);
   
10.     define zd varchar(128);
    
11.     define id varchar(128);
    
12. set debug file to '/home/informix/yuy/circuit_tmp_update.log';
    
13. --开启跟踪所有执行的语句
    
14. trace on;
    
15. trace '开始执行存储过程';
    
16. begin
    
17. --异常捕捉
    
18. on exception
    
19. rollback work;
    
20. return;
    
21. end exception
    
22. begin work;
    
23. --1、创建临时表
    
24. trace '开始创建tmp_table_circuit_a表';
    
25. create table tmp_table_circuit_a(
    
26. objectid varchar(128),
    
27. circuitnumber varchar(128),
    
28. rel_zhandian_id varchar(128),
    
29. aendswitchbuildingobjectid varchar(128));
    
30. trace 'tmp_table_circuit_a表创建完成';
    
31. trace '开始创建tmp_table_circuit_z表';
    
32. create table tmp_table_circuit_z(
    
33. objectid varchar(128),
    
34. circuitnumber varchar(128),
    
35. rel_zhandian_id varchar(128),
    
36. zendswitchbuildingobjectid varchar(128));
    
37. trace 'tmp_table_circuit_z表创建完成';
    
38. 
    
39. --查询circuit表中zhandianid为空的数据
    
40. foreach c0
    
41.         for select objectid,circuitnumber,aendzhandianobjectid,zendzhandianobjectid,aendswitchbuildingobjectid,zendswitchbuildingobjectid
    
42.         into objectid_v,circuitnumber_v,aendzhandianobjectid_v,zendzhandianobjectid_v,aendswitchbuildingobjectid_v,zendswitchbuildingobjectid_v from circuit
    
43.         --如果circuit表中aswitchbuildingobjectid不为空，则根据aswitchbuildingobjectid找到jifang表中的rel_zhandian_id
    
44. trace '开始查询符合条件的A端数据';
    
45.         if aendswitchbuildingobjectid_V != '' and aendswitchbuildingobjectid_V != 'null' then
    
46.                 select rel_zhandian_id into rel_zhandian_id_v from jifang where objectid=aendswitchbuildingobjectid_v;
    
47.                 insert into tmp_table_circuit_a values (objectid_v,circuitnumber_v,rel_zhandian_id_v,aendswitchbuildingobjectid_v);
    
48.         end if;
    
49. trace 'A端数据更新完成';
    
50.         --如果circuit表中zswitchbuildingobjectid不为空，则根据zswitchbuildingobjectid找到jifang表中的rel_zhandian_id
    
51. trace '开始查询符合条件的Z端数据';
    
52.         if zendswitchbuildingobjectid_V != '' and zendswitchbuildingobjectid_V != 'null' then
    
53.                 select rel_zhandian_id into rel_zhandian_id_v from jifang where objectid=zendswitchbuildingobjectid_v;
    
54.                 insert into tmp_table_circuit_z values (objectid_v,circuitnumber_v,rel_zhandian_id_v,zendswitchbuildingobjectid_v);
    
55.         end if;
    
56. trace 'Z端数据更新完成';
    
57. end foreach;
    
58. --更新A端数据
    
59. foreach c0
    
60.         for select objectid,rel_zhandian_id into id,zd from tmp_table_circuit_a
    
61.         update circuit set aendzhandianobjectid=zd where objectid=id;
    
62. end foreach;
    
63. --更新Z端数据
    
64. foreach c0
    
65.         for select objectid,rel_zhandian_id into id,zd from tmp_table_circuit_z
    
66.         update circuit set zendzhandianobjectid=zd where objectid=id;
    
67. end foreach;
    
68. --5、提交事务
    
69. commit work;
    
70. end;
    
71. trace '存储过程执行完毕';
    
72. --关闭跟踪
    
73. trace off;
    
74. end procedure;
    
75. execute procedure circuit_tmp_update();

复制代码

informix存储过程迁移oracle的异同，希望能够在这里与大家讨论下这个是否可以直接写个shell脚本自动转换
http://woying.me/blog/rewrite.php/read-265.html

XQKA

这个不可能实现吧。
数据库迁移是庞大的工程，想用shell脚本实现不切实际呀。

rdcwayx

例子里已经包括全部命令了吗？还有，有没有一一对应的Oracle 命令，还是只要是符合sqlplus 标准的命令就行？

y331044508

回复 3# rdcwayx


    实在是抱歉，，，前几天忙着做个迁移工作，没有及时回复大家，命令已经全部在这个例子里面了。。。


   这个估计实现起来有点麻烦，，，最后我们都是通过手工一个一个修改好的，我贴出一个短点的例子，大家看看是否可以做成shell。

informix存储

1. create procedure "informix".pc_report_reportcitynum()
   
2. define cityname varchar(32);
   
3. DEFINE  I INT ;
   
4. let I = 0;
   
5. foreach t for
   
6.         select city into cityname
   
7.         from district where  (country = '' or country is null) and city!=''
   
8.         order by city asc
   
9.         let I=I+1;
   
10.         insert into reportcitynum(cityname,num) values(cityname,I);
    
11. end foreach;
    
12. end procedure;

复制代码

oracle存储

1. CREATE OR REPLACE PROCEDURE PC_REPORT_REPORTCITYNUM
   
2. is
   
3. cityname varchar2(32);
   
4. c_temp varchar2(32);
   
5. c_count integer :=0;
   
6. cursor c_flag is select city into cityname
   
7.   from district where  (country = '' or country is null) and city is not null
   
8.   order by city asc;
   
9. begin
   
10.   for c_temp in c_flag loop
    
11.   c_count := c_count +1;
    
12.   insert into reportcitynum(cityname,num) values(cityname,c_count);
    
13.   end loop;
    
14. end;

复制代码