oracle 游标使用的一个例子-CSDN博客

本文链接：https://blog.csdn.net/YJHAndYW/article/details/52053994

这里用的市plsql 处理oracle 数据库的大量数据，因为之前也没怎么用过，也就自己瞎摸索这弄弄看，业务需求，如果有什么不妥的地方还请指正

这里有个需求是这样的一张类型表 T_DEMO_TYPE

一张信息表 demo_handel_1

表结构如下：

create table T_DEMO_TYPE
(
id NUMBER,
parentid NUMBER,
name VARCHAR2(100),
leaf NUMBER
)

create table DEMO_HANDEL_1
(
id NUMBER,
companyname VARCHAR2(100),
province VARCHAR2(50),
city VARCHAR2(50),
createdate VARCHAR2(50),
registerfund VARCHAR2(50),
areaname VARCHAR2(200),
busi_name VARCHAR2(4000),
busi_typename VARCHAR2(560),
busi_id VARCHAR2(500),
busitype_id VARCHAR2(500),
citycode VARCHAR2(10)
)

说白了就是需要将 busi_name 这个字段的值像 “aa,bb,cc,dd” 这样的字符串转换成与他们对应的 id 的 “1,3,2,6”

create or replace procedure pro_demo_test
as
cursor c_project_cursor is select t.id,t.busi_name from demo_handel_1 t;
cursor c_busi_type_cursor is select t.id,t.name from T_DEMO_TYPE t;
v_busi_id T_DEMO_TYPE.id%type;
v_busi_type T_DEMO_TYPE.name%type;
v_busi_name demo_handel_1.busi_name%type;
v_id demo_handel_1.id%type;
v_temp varchar2(2000);
v_count number default 0;
v_temp_ids varchar(2000);
begin
open c_project_cursor;
fetch c_project_cursor into v_id,v_busi_name;
while c_project_cursor%found loop
-- dbms_output.put_line('v_busi_name:'||v_busi_name);
v_temp_ids:=null;
---字符串处理包含逗号个数--
v_count:= nvl(LENGTH(REGEXP_REPLACE(REPLACE(v_busi_name,',','@'),'[^@]+','')),0)+1;
-------包含逗号-------
while v_count>0 loop
if v_count>1 then
---当前项
v_temp:=substr(v_busi_name,0,instr(v_busi_name,',')-1);
else
v_temp:=v_busi_name;
end if;
----截取当前项后剩余
v_busi_name:=substr(v_busi_name,instr(v_busi_name,',')+1);

----读取类型表匹配当前项
open c_busi_type_cursor;
fetch c_busi_type_cursor into v_busi_id,v_busi_type;
while c_busi_type_cursor%found loop
if v_temp=v_busi_type then
--dbms_output.put_line('v_busi_id:'||v_busi_id);
--拼接id
v_temp_ids:=concat(v_temp_ids,v_busi_id||',');
end if;
fetch c_busi_type_cursor into v_busi_id,v_busi_type;
end loop;
close c_busi_type_cursor;
-- dbms_output.put_line('v_temp:'||v_temp);
-- dbms_output.put_line('v_count:'||v_count);

v_count:=v_count-1;
end loop;
update demo_handel_1 t set t.busi_id=substr(v_temp_ids,0,length(v_temp_ids)-1) where t.id=v_id;
--dbms_output.put_line('v_temp_ids:'||v_temp_ids);
fetch c_project_cursor into v_id,v_busi_name;
end loop;
close c_project_cursor;
end;

call pro_demo_test();

后面又碰到一个更麻烦的但是也类似这种的要从一串地址中抽取省市的信息.

但是问题是这些地址中很多信息不全就很麻烦因为确定一个市有时候至少需要明确省市县中两个.有的甚至只是一个街道名而已，索性这样的数据是少数大概占五分之一多点先处理能处理的吧那就

/**********根据地址拆分出省市***************/
create or replace procedure pro_demo_province_city
as
--cursor c_project_cursor is select t.id,t.areaname from temp_city t where rownum<50 ;where t.id=1142 where t.id=584
cursor c_project_cursor is select t.id,t.areaname,t.province from temp_city t ;
cursor c_city_cursor is select t.id,t.cityname,t.citycode,t.parentcode,t.leaf from T_CITY_MODEL t;
cursor c_city_byname(v_name varchar) is select cityname,citycode,parentcode,leaf from T_CITY_MODEL where cityname=v_name;
cursor c_county_byname(v_name varchar) is select cityname,citycode,parentcode,leaf from T_CITY_MODEL where cityname=v_name and leaf=2;

v_city_id T_CITY_MODEL.id%type;
v_city_name T_CITY_MODEL.cityname%type;
v_city_code T_CITY_MODEL.citycode%type;
v_city_parent_code T_CITY_MODEL.parentcode%type;


v_county_name T_CITY_MODEL.cityname%type;
v_county_code T_CITY_MODEL.citycode%type;
v_county_parent_code T_CITY_MODEL.parentcode%type;

v_leaf T_CITY_MODEL.leaf%type;
v_areaname temp_city.areaname%type;
v_provinceName temp_city.province%type;
v_id temp_city.id%type;
v_temp varchar2(2000);
v_count number default 0;
v_temp_ids varchar(2000);
v_province varchar(100);
v_city varchar(100);
v_county varchar(100);
v_province_index number;
v_city_index number;
v_county_index number;
v_temp_index number;
v_flag number;

some_kinds_of_err EXCEPTION; -- Exception to indicate an error condition
v_ErrorCode NUMBER; -- Variable to hold the error message code
v_ErrorText VARCHAR2(200); -- Variable to hold the error message text


begin
open c_project_cursor;
fetch c_project_cursor into v_id,v_areaname,v_provinceName;

while c_project_cursor%found loop
v_city:='';
v_county:='';
v_province_index:=instr(v_areaname,'省');
v_city_index:=instr(v_areaname,'市');
v_county_index:=instr(v_areaname,'县');
v_flag:=v_county_index-v_city_index;

if(v_city_index>0 and v_province_index>0 and v_county_index>0) then ---省市县存在
v_province:=substr(v_areaname,0,v_province_index-1);
v_areaname:=substr(v_areaname,v_province_index+1);
v_temp_index:=instr(v_areaname,'市');
v_city:=substr(v_areaname,0,v_temp_index-1);
v_areaname:=substr(v_areaname,v_temp_index+1);
v_temp_index:=instr(v_areaname,'县');
v_county:=substr(v_areaname,0,v_temp_index-1);
if(length(v_county)=1)then
v_county:=v_county||'县';
end if;
-- dbms_output.put_line('省:>>>'||v_province||'---市:>>>>'||v_temp||'---县:>>>>'||v_county);
end if;


if(v_city_index>0 and v_county_index>0 and v_province_index =0 and v_flag>0) then ---市县存在长治市平顺县青羊镇大渠村

v_city:=substr(v_areaname,0,v_city_index-1);
v_temp_index:=instr(v_areaname,'县');
v_county:=substr(v_areaname,v_city_index+1,v_temp_index-v_city_index);
if(length(v_county)=1)then
v_county:=v_county||'县';
end if;
-- dbms_output.put_line('---市>>>>'||v_city||'---县>>>>'||v_county);
end if;


if((v_city_index=0 and v_province_index=0 and v_county_index>0 )or (v_city_index>0 and v_county_index>0 and v_province_index =0 and v_flag<0)) then ---只有县存在

v_temp_index:=instr(v_areaname,'县');
v_county:=substr(v_areaname,0,v_temp_index-1);
if(length(v_county)=1)then
v_county:=v_county||'县';
end if;
-- dbms_output.put_line('--只有县----------'||v_county);
--dbms_output.put_line(v_provinceName);
begin

select citycode into v_city_parent_code from T_CITY_MODEL t where t.cityname like '%'||v_provinceName||'%' and leaf =0;--省id
-- dbms_output.put_line(v_city_parent_code);
select parentcode into v_city_code from T_CITY_MODEL t where t.cityname =v_county and leaf=2 and t.citycode like '%'||substr(v_city_parent_code,0,2)||'%';---市id
-- dbms_output.put_line(v_provinceName||'----'||v_county||'----'||v_city_code||'----------'||v_city_parent_code);
select citycode,cityname into v_city_code,v_city_name from T_CITY_MODEL where citycode=v_city_code and parentcode = v_city_parent_code;
exception
when no_data_found then
v_city_code :='null';
v_city_name :='null';
v_city_parent_code :='null';
end;

update temp_city t set t.city=v_city_name,t.citycode=v_city_code where t.id=v_id;

commit;
-- dbms_output.put_line('省>>>'||v_province||'---市>>>>'||v_temp||'---县>>>>'||v_county);

end if;


if(v_province_index>0 and v_city_index>0 and v_county_index=0 ) then ---省市都存在 (同省市县都存在的情况)
v_province:=substr(v_areaname,0,v_province_index-1);
v_areaname:=substr(v_areaname,v_province_index+1);
v_temp_index:=instr(v_areaname,'市');
v_city:=substr(v_areaname,0,v_temp_index-1);
--v_temp:=v_city;
-- dbms_output.put_line('省>>>'||v_province||'---市：>>>>'||v_city);
end if;



if(v_city_index>0 and v_province_index=0 and v_county_index=0) then ---市存在
v_city:=substr(v_areaname,0,v_city_index-1);
-- dbms_output.put_line('---市>>>>'||v_city||'-----记录id:'||v_id);
end if;


if(v_city_index=0 and v_province_index>0 and v_county_index>0) then ---省县存在
v_province:=substr(v_areaname,0,v_province_index-1);
v_areaname:=substr(v_areaname,v_province_index+1);
v_temp_index:=instr(v_areaname,'县');
v_county:=substr(v_areaname,0,v_temp_index-1);
if(length(v_county)=1)then
v_county:=v_county||'县';
end if;

begin

select citycode into v_city_parent_code from T_CITY_MODEL t where t.cityname like '%'||v_provinceName||'%' and leaf =0;--省id
-- dbms_output.put_line(v_city_parent_code);
select parentcode into v_city_code from T_CITY_MODEL t where t.cityname =v_county and leaf=2 and t.citycode like '%'||substr(v_city_parent_code,0,2)||'%';---市id
-- dbms_output.put_line(v_provinceName||'----'||v_county||'----'||v_city_code||'----------'||v_city_parent_code);
select citycode,cityname into v_city_code,v_city_name from T_CITY_MODEL where citycode=v_city_code and parentcode = v_city_parent_code;
exception
when no_data_found then
v_city_code :='null';
v_city_name :='null';
v_city_parent_code :='null';
end;

update temp_city t set t.city=v_city_name,t.citycode=v_city_code where t.id=v_id;

commit;
--- dbms_output.put_line('省>>>'||v_province||'---县：>>>>'||v_county);
end if;

if(v_province_index=0 and v_city_index=0 and v_county_index=0)then
-- dbms_output.put_line('省市县都不存在'||'市:'||v_temp||'县'||v_county);
v_temp:='';
v_county:='';

end if;

-------------------
-- dbms_output.put_line('省：'||v_province||'>>>市:'||v_temp||'>>>县:'||v_county);
if(v_city_index>0) then

open c_city_byname(v_city);

fetch c_city_byname into v_city_name,v_city_code,v_city_parent_code,v_leaf;
while c_city_byname%found loop

begin
--- dbms_output.put_line('----------：'||v_city_name);
if(v_leaf=2) then ----是县级市（存在很多名字一样的县级单位）
---根据城市名称找到省code
----select parentcode into v_city_parent_code from T_CITY_MODEL where cityname = v_city and leaf = 2;
open c_county_byname(v_city);

fetch c_county_byname into v_county_name,v_county_code,v_county_parent_code,v_leaf;
while c_county_byname%found loop
select cityname into v_city_name from T_CITY_MODEL where citycode = v_city_parent_code;
--dbms_output.put_line('-------v_city_code---：'||v_city_code||'----v_city_parent_code---:'||v_city_parent_code);
update temp_city t set t.city=v_city_name,t.citycode=v_city_parent_code where t.id=v_id;
commit;
/* begin
if(v_county_parent_code=v_city_code)then
select cityname into v_city_name from T_CITY_MODEL where citycode = v_city_parent_code;
dbms_output.put_line('----------：'||v_city_name);
update temp_city t set t.city=v_city_name,t.citycode=v_city_parent_code where t.id=v_id;
commit;
end if;
\* exception
when no_data_found then
v_city_code :='null';
v_city_name :='null';*\

end; */
fetch c_county_byname into v_city_name,v_city_code,v_city_parent_code,v_leaf;
end loop;
close c_county_byname;

elsif(v_leaf=1) then
select citycode into v_city_code from T_CITY_MODEL where cityname = v_city and leaf = 1;
-- dbms_output.put_line('----------：'||v_id);
update temp_city t set t.city=v_city,t.citycode=v_city_code where t.id=v_id;
commit;
end if;
/* exception
when no_data_found then
v_city_code :='null';
v_city_name :='null';*/

end;

fetch c_city_byname into v_city_name,v_city_code,v_city_parent_code,v_leaf;
end loop;
close c_city_byname;
end if;

fetch c_project_cursor into v_id,v_areaname,v_provinceName;
end loop;
close c_project_cursor;

EXCEPTION
WHEN OTHERS THEN
/* v_city_code :='null';
v_city_name :='null';
v_city_parent_code :='null';*/

v_ErrorCode := SQLCODE;
v_ErrorText := SUBSTR(SQLERRM, 1, 200);
--dbms_output.put_line(v_ErrorCode || '::'||v_ErrorText);

end;