- create or replace procedure test_edage_cell is
- --变量
- /*v_city_code varchar2(10);*/
- /*v_roam_cell_id varchar2(10);*/
- /*v_roam_city varchar2(10);*/
- v_date varchar2(20);
- v_create_sql varchar2(200);
- v_sql varchar2(200);
- /*v_area_id varchar2(10);*/
- --提取bss侧的数据,如果有*即为省际漫游,组合新的CITY_CODE和ROMA_CITY添加到新表
- cursor c_out_hebei is
- select trim(city_code) city_code,
- trim(roam_cell_id) roam_cell_id,
- trim(roam_city) roam_city
- from edge_roam_syn;
- --遍历的编码
- cursor c_hb_code is
- select to_char(area_id) area_id from area_hb where length(area_id) = 3;
- begin
- ---每天创建边界小区的表
- SELECT TO_CHAR(SYSDATE, 'yyyymmdd') INTO v_date FROM DUAL;
- v_create_sql := 'create table edge_roam_syn_' || v_date ||
- ' (city_code varchar2(10),roam_cell_id varchar2(10),roam_city varchar2(10))';
- EXECUTE IMMEDIATE v_create_sql;
- for v_out_hebei in c_out_hebei loop
- ----判断city_code是否为*,如果为*,遍历的city_code与roam_city进行组合,插入新表
- if v_out_hebei.city_code = '*' then
- for v_hb_code in c_hb_code loop
- v_sql := 'insert into edge_roam_syn_' || v_date || ' values (' ||
- v_hb_code.area_id || ',' || v_out_hebei.roam_cell_id || ',' ||
- v_out_hebei.roam_city || ');';
- EXECUTE IMMEDIATE v_sql;
- commit;
- end loop;
- else
- v_sql := 'insert into edge_roam_syn_' || v_date || ' values (' ||
- v_out_hebei.city_code || ',' || v_out_hebei.roam_cell_id || ',' ||
- v_out_hebei.roam_city || ')';
- dbms_output.put_line('v_sql=' || v_sql);
- EXECUTE IMMEDIATE v_sql;
- commit;
- end if;
- end loop;
- EXCEPTION
- WHEN OTHERS THEN
- DBMS_OUTPUT.PUT_LINE('SQLCODE=' || SQLCODE);
- DBMS_OUTPUT.PUT_LINE('SQLERRM=' || SQLERRM);
- end test_edage_cell;
- --该片段来自于http://www.codesnippet.cn/detail/121020136377.html
来源: http://www.codesnippet.cn/detail/121020136377.html