create or replace procedure ecs_customdetail_0929 --创建存储过程
is
longerstation VARCHAR2(50); --定义变量
type v_cursor is ref cursor; --变义游标
detailid v_cursor;
in_id v_cursor;
v_str varchar2(200);
in_str varchar2(200);
tab_detailid VARCHAR2(32);
in_stationname VARCHAR2(50);
begin
v_str := 'select id from customdetail_0927';
OPEN detailid FOR v_str ; --打开游标
LOOP FETCH detailid INTO tab_detailid; --循环游标
EXIT WHEN detailid%NOTFOUND; --当找不到数据的时候退出循环
in_str :='select stationname from bak0902_sell_customerdetail where id = '''||tab_detailid||''''; --变量的连接方法
OPEN in_id FOR in_str ;
longerstation :='a'; --给变量赋值
loop fetch in_id into in_stationname;
exit when in_id%notfound;
if length(in_stationname)>=length(longerstation) then longerstation :=in_stationname;
end if;
end loop;
DBMS_OUTPUT.PUT_LINE(longerstation);
insert into B_BUS_SELCUSTOMERDETAILDEL1010 select * from bak0902_sell_customerdetail where id=tab_detailid and stationname=longerstation; --把查出来的数据插入另一数据表里
delete from bak0902_sell_customerdetail where id=tab_detailid and stationname=longerstation;--删除相应的数据
commit;
CLOSE in_id;
end loop;
CLOSE detailid;
END ecs_customdetail_0929;