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

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

下面是内存溢出 jb51.cc 通过网络收集整理的代码片段。

内存溢出小编现在分享给大家，也给大家做个参考。

select rowID from books b;select sec8 from upload_sec;----------------------------------------------------------数据插入create or replace procedure proc_insert(  head in varchar2,num in varchar2)isbegininsert into SEC_DETAIL values('陕西','西安','029',head||num,sysdate);commit;dbms_output.put_line('存储成功');end;-------------------------------------------------------------------------------------------------------------------------------------select * from SEC_DETAIL;select sysdate from dual;----------------------------数据插入验证declarebeginproc_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 beginend;--------------------------------------------------------------------------------------------------------主 *** 作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;-----------------------------------------------循环插入验证declarebeginproc_for('130','037','042'); end;select instr('029','0') from dual;---------------------------------------------------------select *from sec_detail;select '0'||'0' from dual;------------------------------------------------------每单元格分拆测试declareel varchar2(100);beginel:='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;casewhen 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;

以上是内存溢出(jb51.cc)为你收集整理的全部代码内容，希望文章能够帮你解决所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。

总结

以上是内存溢出为你收集整理的Oracle存储过程分离表中的数据全部内容，希望文章能够帮你解决Oracle存储过程分离表中的数据所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。