Oracle存储过程分离表中的数据代码,PLSQL代码库,德仔网

代码语言
代码分类
【PLSQL】 Oracle存储过程分离表中的数据
作者:癫狂俗人 / 发布于2014/10/14/ 1334

	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;
试试其它关键字
　存储过程　　分离表　
同语言下
可能有用的
癫狂俗人贡献的其它代码(7)