Oracle存储过程分离表中的数据

以下代码由PHP站长网 52php.cn收集自互联网

现在PHP站长网小编把它分享给大家，仅供参考

select rowid from books b;
select sec8 from upload_sec;



----------------------------------------------------------数据插入
create or replace procedure proc_insert
( 
 head in varchar2,num in varchar2
)
is
begin
insert into SEC_DETAIL values('陕西','西安','029',head||num,sysdate);
commit;
dbms_output.put_line('存储成功');
end;



-------------------------------------------------------------------------------------------------------------------------------------

select * from SEC_DETAIL;
select sysdate from dual;
----------------------------数据插入验证
declare
begin
proc_insert('132','310');
end;

dbms_output.put_line('ooo');
dbms_output.put_line('产品名称:');
select * from sec_detail;
delete from sec_detail;

-------------------------提交数据
Insert into UPLOAD_SEC
   (PROVINCE,CITY,AREA_CODE,SEC0,SEC1,SEC4,SEC5,SEC6,SEC8,SEC9,HEAD)
 Values
   ('陕西','29','950-959','040-049、910-919','918、920-929','200-219、240-249','571-575、578-579','600-619','330-339','131');
Insert into UPLOAD_SEC
   (PROVINCE,SEC2,SEC7,'140-189','700-709、770-789、800-809','410','940-949、970-999','920-949','920-939、980-989','900-913、916-917、919','132');
Insert into UPLOAD_SEC
   (PROVINCE,'290-299、918','900-909、920-969','180-189、410-419、460-469、480-499、616-618、640-644、660-669、680-689','155');
Insert into UPLOAD_SEC
   (PROVINCE,SEC3,'670-679、687-689','680-684、690-695','460-464、478','478-499、581-599','145');
Insert into UPLOAD_SEC
   (PROVINCE,'290-299、928','900-909、914-915、919、924-926、928-969','180-189、194、290-299','145、148-149、152、155-157、163、180-189、195','186');
Insert into UPLOAD_SEC
   (PROVINCE,'290-299、920-929','140-149、175-179、184-189、191-228、250-299','185');
Insert into UPLOAD_SEC
   (PROVINCE,'920-929','900-909、920-949','460-499、700-709','600-609、620-629、640-649','140-149、170-199','156');
COMMIT;


select * from upload_sec;
--------------------------------------
select sec0 from upload_sec;
----------------------------
create or replace procedure proc_find

(

)
is 
begin
end;
--------------------------------------------------------------------------------------------------------主操作
create or replace procedure proc_ok
(
row in upload_sec%rowtype
)
  is
 begin

 
     if row.sec0 is null or row.sec0='' then
       dbms_output.put_line('0是空的');
      else
      proc_el(row.sec0,row.head);
        dbms_output.put_line('----------------------------------------0号'||row.sec0);
     end if;
     
       if row.sec1 is null or row.sec1='' then
       dbms_output.put_line('1是空的');
      else
      proc_el(row.sec1,row.head);
        dbms_output.put_line('----------------------------------------1号'||row.sec1);
     end if;
      
       if row.sec2 is null or row.sec2='' then
       dbms_output.put_line('2是空的');
      else
      proc_el(row.sec2,row.head);
        dbms_output.put_line('----------------------------------------2号'||row.sec2);
     end if;
     
       if row.sec3 is null or row.sec3='' then
       dbms_output.put_line('3是空的');
      else
      proc_el(row.sec3,row.head);
        dbms_output.put_line('----------------------------------------3号'||row.sec3);
     end if;
     
       if row.sec4 is null or row.sec4='' then
       dbms_output.put_line('4是空的');
      else
      proc_el(row.sec4,row.head);
        dbms_output.put_line('----------------------------------------4号'||row.sec4);
     end if;
     
       if row.sec5 is null or row.sec5='' then
       dbms_output.put_line('5是空的');
      else
      proc_el(row.sec5,row.head);
        dbms_output.put_line('----------------------------------------5号'||row.sec5);
     end if;
     
       if row.sec6 is null or row.sec6='' then
       dbms_output.put_line('6是空的');
      else
      proc_el(row.sec6,row.head);
        dbms_output.put_line('----------------------------------------6号'||row.sec6);
     end if;
     
       if row.sec7 is null or row.sec7='' then
       dbms_output.put_line('7是空的');
      else
      proc_el(row.sec7,row.head);
        dbms_output.put_line('----------------------------------------7号'||row.sec7);
     end if;
     
       if row.sec8 is null or row.sec8='' then
       dbms_output.put_line('8是空的');
      else
      proc_el(row.sec8,row.head);
        dbms_output.put_line('----------------------------------------8号'||row.sec8);
     end if;
    
        if row.sec9 is null or row.sec9='' then
       dbms_output.put_line('9是空的');
      else
      proc_el(row.sec9,row.head);
        dbms_output.put_line('----------------------------------------9号'||row.sec9);
     end if;
         
     
       end;
  ------------------------------------------------------------------
  select * from sec_detail order by sec;

delete from sec_detail;
  select * from upload_sec;
  ----------------------------------------------------------------------------------------游标取表中数据调proc_ok;
  declare
  cursor  num is select * from upload_sec;
  begin
  
 
  for row in num loop
  proc_ok(row);
  
  end loop;
  end;
  ----------------------------------------------输出设置
  
  set serveroutput on size 1000000;
  
  
  -------------------------------------- 循环插入
  create or replace procedure proc_for 
  (
    head in varchar2,sta in  varchar2,en in varchar2  
  )
  is
  a int(10);
  b int(10);
    nu varchar2(50);
  begin
  
  if instr(sta,'0')=1 then

   a:=to_number(sta);
  dbms_output.put_line(a);
  b:=to_number(en);
  dbms_output.put_line(b);
   for i in a..b loop
   select  to_char(i) into nu from dual; 
   proc_insert(head,'0'||nu);
   dbms_output.put_line('0'||nu);
  end loop;
  
else
  a:=to_number(sta);
  dbms_output.put_line(a);
  b:=to_number(en);
  dbms_output.put_line(b);
   for i in a..b loop
   select  to_char(i) into nu from dual; 
   dbms_output.put_line(nu);
   proc_insert(head,nu);
  end loop;
end if;
  
end;
-----------------------------------------------循环插入验证
declare

begin
proc_for('130','037','042');
 
end;

select instr('029','0') from dual;

--------------------------------------
-------------------
select *from sec_detail;
select '0'||'0' from dual;

------------------------------------------------------每单元格分拆测试
declare
el varchar2(100);

begin
el:='22-33-44-55-66';
loop
  dbms_output.put_line(substr(el,1,instr(el,'-')-1));
  
  
 el:=substr(el,'-')+1);
 exit when length(el)=2; 
 end loop;
  dbms_output.put_line(el);
  end;
  
  
  -----------------------------------------------------------------分拆单元格过程并插入
  create or replace procedure  proc_el 
  (
  see in varchar2,head in varchar2
  )
  is
  se varchar2(500);
  begin
  se:=see;
case
when instr(se,'、')=0 then 
      if instr(se,'-')=0 then
       proc_insert(head,se);
     
     else   
      proc_for(head,substr(se,3),5,3));
     end if;
when instr(se,'、')!=0 then
     loop
  dbms_output.put_line(substr(se,instr(se,'、')-1));
  if instr(substr(se,'、')-1),'、')-1));
  else
   proc_for(head,substr(substr(se,3));
  end if;
   se:=substr(se,'、')+1);
   if instr(se,'、')=0 then 
         exit when length(se)<=7;
       
   else
    exit when length(se)<=3;
   end if; 
  
   end loop;
   
      dbms_output.put_line(se);  
   if instr(se,'-')=0 then
   proc_insert(head,se);
   else
   proc_for(head,3));
   end if;
   
   
  
    end  case;

 end;
  -------------------------------------------------------------------------测试单元格插入
  declare
  see varchar2(100);
  head varchar2(10);
  
   begin
   see:='190-193、750-759、891、895、220-229';
   proc_el(see,head);
    
   end;
  
 select instr(substr('037-042',(instr('037-042','、')-1)),'-')=0 from dual;
  
  ----------------------------------------------------------------------------------------
     

  
------------------------------------------------创建抽象数据类型
 create type ty_sec as object(
 t1 varchar2(15),t2 varchar2(15),t3 varchar2(15),t4 varchar2(15),t5 varchar2(15),t6 varchar2(15),t7 varchar2(15),t8 varchar2(15),t9 varchar2(15),t10 varchar2(15),t11 varchar2(15),t12 varchar2(15),t13 varchar2(15) 
 )
 -----------------
 select substr('abc',0) from dual;

以上内容由PHP站长网【52php.cn】收集整理供大家参考研究

如果以上内容对您有帮助，欢迎收藏、点赞、推荐、分享。

（编辑：常州站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!