oracle：动态sql

现在的项目中的用户表是分表的，其实就是一个省一个用户表TF_F_USER_PXX ，其中XX为省份代码。现在引起的问题是给出一个号码，根本不知道在哪个表里，只能知道号码所在的省份后才能查到用户信息。

现在的问题是需要导出某个wvpn成员关系表tf_f_grp_mem中的用户数据，全部导入到tf_f_user表的insert语句 ，其实是为了我移植数据方便。以方便所有数据在同一表中操作。另外由于tf_f_user_pxx表中用户数据的可能存在重复。

根据需求中的描述，我想到的办法是 动态sql查询tf_f_user_pxx中的数据，然后在拼出insert语句。脚本如下：

重点:  open c1 for sqlstr 

 

declare
sqlstr varchar2(2000);
sqlstr1 varchar2(2000);
rec sid.tf_f_user_p00%rowtype;
type c_type is ref cursor;
c1 c_type;

begin

sqlstr :='';
sqlstr1 :='';

for recuser in (
select mem_user_id, mem_nbr ,province_code from sid.tf_f_grp_mem where group_id = 10000036 and sysdate < exp_date ) loop

sqlstr := 'select PARTITION_ID, USER_NAME, USER_ID, CUST_ID, USECUST_ID, SERVICE_ID, BRAND_CODE, PRODUCT_ID, PROVINCE_CODE, EPARCHY_CODE, CITY_CODE, USER_PASSWD, USER_TYPE_CODE, SERIAL_NUMBER, SERVICE_TYPE_CODE, PREPAY_TAG, IN_DATE, OPEN_DATE, OPEN_MODE, OPEN_DEPART_ID, USER_STATE, OPEN_STAFF_ID, IN_DEPART_ID, IN_STAFF_ID, REMOVE_TAG, DESTROY_TIME, REMOVE_EPARCHY_CODE, REMOVE_CITY_CODE, REMOVE_DEPART_ID, REMOVE_REASON_CODE, REMARK, IF_ONE_PRO, SID_TRANS_ID, CUST_PROJECT, LICENCE_NO, START_DATE, END_DATE, STOP_FLAG, PROVINCE_USER_ID, FEE_CYCLE, IN_NET_TAG, REV_TAG, USER_GRP_TAG, SML_ENTER_TAG '
||' from sid.tf_f_user_p'
||recuser.province_code
||' where user_id ='
||recuser.mem_user_id
||' and serial_number ='''
||recuser.mem_nbr
||''' ';

dbms_output.put_line(sqlstr);

open c1 for sqlstr;
loop
fetch c1 into rec;
exit when c1%notfound;

sqlstr1 := 'insert into sid.tf_f_user (PARTITION_ID, USER_NAME, USER_ID, CUST_ID, USECUST_ID, SERVICE_ID, BRAND_CODE, PRODUCT_ID, PROVINCE_CODE, EPARCHY_CODE, CITY_CODE, USER_PASSWD, USER_TYPE_CODE, SERIAL_NUMBER, SERVICE_TYPE_CODE, PREPAY_TAG, IN_DATE, OPEN_DATE, OPEN_MODE, OPEN_DEPART_ID, USER_STATE, OPEN_STAFF_ID, IN_DEPART_ID, IN_STAFF_ID, REMOVE_TAG, DESTROY_TIME, REMOVE_EPARCHY_CODE, REMOVE_CITY_CODE, REMOVE_DEPART_ID, REMOVE_REASON_CODE, REMARK, IF_ONE_PRO, SID_TRANS_ID, CUST_PROJECT, LICENCE_NO, START_DATE, END_DATE, STOP_FLAG, PROVINCE_USER_ID, FEE_CYCLE, IN_NET_TAG, REV_TAG, USER_GRP_TAG, SML_ENTER_TAG) '
||'values('''
||rec.PARTITION_ID
||''','''
||rec.USER_NAME
||''','''
||rec.USER_ID
||''','''
||rec.CUST_ID
||''','''
||rec.USECUST_ID
||''','''
||rec.SERVICE_ID
||''','''
||rec.BRAND_CODE
||''','''
||rec.PRODUCT_ID
||''','''
||rec.PROVINCE_CODE
||''','''
||rec.EPARCHY_CODE
||''','''
||rec.CITY_CODE
||''','''
||rec.USER_PASSWD
||''','''
||rec.USER_TYPE_CODE
||''','''
||rec.SERIAL_NUMBER
||''','''
||rec.SERVICE_TYPE_CODE
||''','''
||rec.PREPAY_TAG
||''','
||'to_date('''||to_char(rec.IN_DATE,'YYYYMMDDHH24MISS') ||''',''YYYYMMDDHH24MISS'')'
||','
-- ||rec.OPEN_DATE
||'to_date('''||to_char( rec.OPEN_DATE ,'YYYYMMDDHH24MISS') ||''',''YYYYMMDDHH24MISS'')'
||','''
||rec.OPEN_MODE
||''','''
||rec.OPEN_DEPART_ID
||''','''
||rec.USER_STATE
||''','''
||rec.OPEN_STAFF_ID
||''','''
||rec.IN_DEPART_ID
||''','''
||rec.IN_STAFF_ID
||''','''
||rec.REMOVE_TAG
||''','
--||rec.DESTROY_TIME
||'to_date('''||to_char( rec.DESTROY_TIME ,'YYYYMMDDHH24MISS') ||''',''YYYYMMDDHH24MISS'')'
||','''
||rec.REMOVE_EPARCHY_CODE
||''','''
||rec.REMOVE_CITY_CODE
||''','''
||rec.REMOVE_DEPART_ID
||''','''
||rec.REMOVE_REASON_CODE
||''','''
||rec.REMARK
||''','''
||rec.IF_ONE_PRO
||''','''
||rec.SID_TRANS_ID
||''','''
||rec.CUST_PROJECT
||''','''
||rec.LICENCE_NO
||''','
--||rec.START_DATE
||'to_date('''||to_char( rec.START_DATE ,'YYYYMMDDHH24MISS') ||''',''YYYYMMDDHH24MISS'')'
||','
--||rec.END_DATE
||'to_date('''||to_char( rec.END_DATE ,'YYYYMMDDHH24MISS') ||''',''YYYYMMDDHH24MISS'')'
||','''
||rec.STOP_FLAG
||''','''
||rec.PROVINCE_USER_ID
||''','''
||rec.FEE_CYCLE
||''','''
||rec.IN_NET_TAG
||''','''
||rec.REV_TAG
||''','''
||rec.USER_GRP_TAG
||''','''
||rec.SML_ENTER_TAG
||''');';


dbms_output.put_line(sqlstr1);


end loop;
close c1;

end loop;

end;

转载于:https://www.cnblogs.com/Eastsong/archive/2013/03/13/2956874.html