时间:2021-07-01 10:21:17 帮助过:24人阅读
ORACLE数据库sql 无 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_DETA
ORACLE 数据库sql <无>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, CITY, AREA_CODE, SEC0, SEC2, SEC4, SEC5, SEC7, SEC8, SEC9, HEAD) Values ('陕西', '西安', '29', '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, CITY, AREA_CODE, SEC0, SEC2, SEC9, HEAD) Values ('陕西', '西安', '29', '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, CITY, AREA_CODE, SEC2, SEC3, SEC4, SEC6, HEAD) Values ('陕西', '西安', '29', '670-679、687-689', '680-684、690-695', '460-464、478', '478-499、581-599', '145'); Insert into UPLOAD_SEC (PROVINCE, CITY, AREA_CODE, SEC0, SEC2, SEC8, SEC9, HEAD) Values ('陕西', '西安', '29', '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, CITY, AREA_CODE, SEC0, SEC9, HEAD) Values ('陕西', '西安', '29', '290-299、920-929', '140-149、175-179、184-189、191-228、250-299', '185'); Insert into UPLOAD_SEC (PROVINCE, CITY, AREA_CODE, SEC0, SEC1, SEC6, SEC8, SEC9, HEAD) Values ('陕西', '西安', '29', '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,instr(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,1,3),substr(se,5,3)); end if; when instr(se,'、')!=0 then loop dbms_output.put_line(substr(se,1,instr(se,'、')-1)); if instr(substr(se,1,instr(se,'、')-1),'-')=0 then proc_insert(head,substr(se,1,instr(se,'、')-1)); else proc_for(head,substr(substr(se,1,instr(se,'、')-1),1,3),substr(substr(se,1,instr(se,'、')-1),5,3)); end if; se:=substr(se,instr(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,substr(se,1,3),substr(se,5,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',1 ,(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',1,0) from dual;