导数据问题

txg303

有两个表 vac_user_test 和 cbd.temp_user_test，两个表表结构不同，cbd.temp_user_test 中有 10W 条数据，vac_user_test 无数据
现在想把 cbd.temp_user_test 中的数据按照对应规则导入到 vac_user_test 中
写了个存储过程，执行时间是 50s
使用语句执行时间是 9s
想知道如何优化存储过程
如果cbd.temp_user_test中的数据扩展到 2000W，那个执行效率高？

表结构如下：
SQL> desc vac_user_test
Name                                                                                Null?    Type
----------------------------------------------------------------------------------- -------- --------------------------------------------------------
MDN                                                                                 NOT NULL VARCHAR2(30)
IMSI                                                                                         VARCHAR2(50)
USERTYPE                                                                            NOT NULL VARCHAR2(2)
USERSTAT                                                                            NOT NULL VARCHAR2(2)
USERBRAND                                                                                    VARCHAR2(5)
USERSCPTYPE                                                                                  VARCHAR2(1)
USERPREPAIDID                                                                                VARCHAR2(20)
UPDATEDATE                                                                          NOT NULL VARCHAR2(14)
CREATEBY                                                                                     NUMBER(1)
CREATETIME                                                                                   DATE
UPDATETIME                                                                                   DATE
LASTUPDATETYPE                                                                               NUMBER(1)
USERSERVICETYPE                                                                              NUMBER(1)
IFSENT                                                                                       NUMBER(1)




SQL> desc cbd.temp_user_test
Name                                                                                Null?    Type
----------------------------------------------------------------------------------- -------- --------------------------------------------------------
USER_NUMBER                                                                         NOT NULL VARCHAR2(30)
NETWORK_TYPE                                                                        NOT NULL NUMBER(2)
REGISTER_DATE                                                                                DATE
PAYMENT_TYPE                                                                        NOT NULL NUMBER(2)
STATUS                                                                              NOT NULL NUMBER(2)
IMSI                                                                                         VARCHAR2(30)
BRAND                                                                                        VARCHAR2(50)
UPDATE_DATE                                                                              DATE
SCP_TYPE                                                                                     NUMBER(2)
PREPAID_ID                                                                                   VARCHAR2(30)

存储过程如下：

1. CREATE OR REPLACE PROCEDURE INSERTUSER(resultCode out number,message out varchar2)
   
2. IS
   
3. exec_time varchar2(14);
   
4. sqlstr varchar2(2000);
   
5. 
   
6. TYPE CURSOR_TYPE IS REF CURSOR;
   
7. cur_user CURSOR_TYPE;
   
8. 
   
9. TYPE U_TYPE IS RECORD(
   
10. v_mdn cbd.temp_user_test.user_number%TYPE,
    
11. v_imsi cbd.temp_user_test.imsi%TYPE,
    
12. v_payment_type cbd.temp_user_test.payment_type%TYPE,
    
13. v_status cbd.temp_user_test.status%TYPE,
    
14. v_brand cbd.temp_user_test.brand%TYPE,
    
15. v_scp_type cbd.temp_user_test.scp_type%TYPE,
    
16. v_prepaid_id cbd.temp_user_test.prepaid_id%TYPE,
    
17. v_update_date cbd.temp_user_test.update_date%TYPE,
    
18. v_register_date cbd.temp_user_test.register_date%TYPE,
    
19. v_network_type cbd.temp_user_test.network_type%TYPE);
    
20. 
    
21. user_row U_TYPE;
    
22. 
    
23. 
    
24. BEGIN
    
25. --- programe exec starttime
    
26. SELECT to_char(sysdate,'MM-DD HH24:MI:SS') INTO exec_time FROM dual;
    
27. DBMS_OUTPUT.put_line('start : '||exec_time);
    
28. resultCode := 0;
    
29. message := 'success';
    
30. 
    
31. BEGIN
    
32. sqlstr := 'SELECT u.user_number,u.imsi,u.payment_type,';
    
33. sqlstr := sqlstr || 'decode(u.status,0,10,1,11,2,14,3,30),u.brand,u.scp_type,u.prepaid_id,';
    
34. sqlstr := sqlstr || 'u.update_date,u.register_date,decode(u.network_type,0,0,8,1,2,2,3,3,4,4,1,5,7,6,6,7,5,8,9) ';
    
35. sqlstr := sqlstr || ' FROM cbd.temp_user_test u';
    
36. sqlstr := sqlstr || '';
    
37. 
    
38. OPEN cur_user FOR sqlstr;
    
39. 
    
40. LOOP
    
41. FETCH cur_user INTO user_row;
    
42. EXIT WHEN cur_user%NOTFOUND;
    
43. 
    
44. sqlstr := 'insert into vac_user_test(MDN,IMSI,USERTYPE,USERSTAT,USERBRAND,USERSCPTYPE,USERPREPAIDID,';
    
45. sqlstr := sqlstr || 'UPDATEDATE,CREATEBY,CREATETIME,UPDATETIME,LASTUPDATETYPE,USERSERVICETYPE,IFSENT)';
    
46. sqlstr := sqlstr || 'values (:MDN,:IMSI,:USERTYPE,:USERSTAT,:USERBRAND,:USERSCPTYPE,:USERPREPAIDID,';
    
47. sqlstr := sqlstr || ':UPDATEDATE,3,:CREATETIME,:UPDATETIME,1,:USERSERVICETYPE,1)';
    
48. 
    
49. execute immediate sqlstr using user_row.v_mdn,user_row.v_imsi,user_row.v_payment_type,
    
50. user_row.v_status,user_row.v_brand,user_row.v_scp_type,user_row.v_prepaid_id,
    
51. nvl(to_char(user_row.v_update_date,'YYYYMMDDHH24MISS'),to_char(user_row.v_register_date,'YYYYMMDDHH24MISS')),
    
52. user_row.v_register_date,user_row.v_update_date,user_row.v_network_type;
    
53. 
    
54. END LOOP;
    
55. 
    
56. COMMIT;
    
57. CLOSE cur_user;
    
58. END;
    
59. 
    
60. --- programe exec endtime
    
61. SELECT to_char(sysdate,'MM-DD HH24:MI:SS') INTO exec_time FROM dual;
    
62. DBMS_OUTPUT.put_line('end : '||exec_time);
    
63. 
    
64. END INSERTUSER;
    
65. /
    

复制代码

执行的 sql 语句如下：

1. insert into vac_user_test(MDN,IMSI,USERTYPE,USERSTAT,USERBRAND,USERSCPTYPE,USERPREPAIDID,
   
2. UPDATEDATE,CREATEBY,CREATETIME,UPDATETIME,
   
3. LASTUPDATETYPE,USERSERVICETYPE,IFSENT)
   
4. select u.user_number,u.imsi,u.payment_type,decode(u.status,0,10,1,11,2,14,3,30),u.brand,u.scp_type,u.prepaid_id,
   
5. nvl(to_char(u.update_date,'YYYYMMDDHH24MISS'),to_char(u.register_date,'YYYYMMDDHH24MISS')),3,u.register_date,u.update_date,
   
6. 1,decode(u.network_type,0,0,8,1,2,2,3,3,4,4,1,5,7,6,6,7,5,8,9),1
   
7. from cbd.temp_user_test u;

复制代码

请各位老师和同学帮我看看是哪的问题？

LuiseDalian

建议用SQL来完成。PL/SQL最应该避免的就是你这种操作，循环倒数据。

txg303

OK，谢谢老师