存储过程学习 第11章

1byte=8bit
a char(32767 byte)
varchar2 4000byte
number(5,2) 999.99
number(3,-2)99900;
round trunc ceil floor
pls_integer(性能更好) 2**31
binary_integer 
positive
natural




-----------------------字符串测试
DECLARE
  c_str CHAR(3);
  str   VARCHAR2(10) not null:=''; 限制为非空
BEGIN
  c_str := 'abc';
  dbms_output.put_line('c_str=========' || c_str || '==============');
  c_str := 'a';  --占用3个位置
  dbms_output.put_line('c_str=========' || c_str || '==============');

  str := 'abc';  --变长
  dbms_output.put_line('str=========' || str || '==============');
  str := rpad('a', 5); --右填充
  dbms_output.put_line('str=========' || str || '==============');
  c_str := 'abcd';
EXCEPTION
  WHEN value_error THEN
    dbms_output.put_line('value error!');
END;

--------------------------record 测试
SELECT * FROM emp;

DECLARE
  TYPE emp_record IS RECORD(  --记录
    ename emp.ename%TYPE,
    job   emp.ename%TYPE);
  employee emp_record;

	type default_type is record(
		birthday date:=trunc(sysdate),
		age number(3):=0);
	
BEGIN
  SELECT ename, job INTO employee FROM emp WHERE empno = 7369;
  dbms_output.put_line('employee name:' || employee.ename ||
                       ' ,employee job:' || employee.job);
END;


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

declare
	type my_emp_record is record( --嵌套
	id emp.empno%type,
	employee emp_pkg.emp_record);  
	mydata my_emp_record;
begin
	mydata.id:='7369';
	mydata.employee:=emp_pkg.empTool(mydata.id);
	dbms_output.put_line(mydata.employee.name);
end;

----------------------index by table (非约束索引 线性 稀疏表
记录和index-by表在包或过程中存储,不能作为对象存储
varray 可存储,索引是紧密 初始化 从1 开始 定长

declare
	type table_type is table of emp_pkg.emp_record index by binary_integer;
	tab table_type;
	emp_id emp.empno%type:=7369;
	location binary_integer;
begin
	location:=emp_id;
	tab(location):=emp_pkg.empTool(emp_id);
	dbms_output.put_line(tab(location).name);
	dbms_output.put_line(tab(location).job);
	dbms_output.put_line('d'||tab(location+1).name);

	dbms_output.put_line(tab(location).job);
	exception when others then
		dbms_output.put_line('at '||location||' cannot find data!');
end;

---------------------找到存在的值
declare
	type table_type is table of integer index by binary_integer;
	tab table_type;
begin
	tab(1):=1000;
	tab(4):=200;
	dbms_output.put_line(tab.count);
	for i in tab.first..tab.last loop  
		if(tab.exists(i))then
			dbms_output.put_line(tab(i));
		else
			dbms_output.put_line('no data in slot:'||i);
		end if ;
	end loop;
	
	dbms_output.put_line(tab.next(0));
	dbms_output.put_line(tab.prior(0));
	dbms_output.put_line(tab.next(1));
	dbms_output.put_line(tab.next(2));
	dbms_output.put_line(tab.next(4));
	dbms_output.put_line(tab.prior(5));
end;
---------------varray
declare
	type my_numbers_type is varray(4) of number;
	my_numbers my_numbers_type;
begin
	my_numbers:=my_numbers_type(3,4,5); --初始化
	for i in 1..my_numbers.count loop
		dbms_output.put_line(my_numbers(i));
	end loop;
end;
---------------创建对象
create or replace type point_type is object(
	x number,
	y number);
create or replace type points_varray_type is varray(10) of point_type;
create or replace type points_nested_table_type is table of point_type; --嵌套表

declare 
	type points_varray_type is varray(10) of point_type;
	points points_varray_type:=points_varray_type();
	a_point point_type;
	pt point_type;
begin
	a_point:=point_type(3,4); 
	points:=points_varray_type(point_type(1,2),point_type(2,3),															            
a_point,point_type(4,5));  --初始化
	for i in 1..points.count loop
		pt:=points(i);
		dbms_output.put_line('x='||pt.x||', y='||pt.y);
	end loop;
end;

---------嵌套表
create table environment_data(
	sample_id number(3),
	points_varray points_varray_type,
	points_nested_table points_nested_table_type)
	nested table
	points_nested_table store as points_nested_tab; --指定嵌套表变量;
-------------
declare
	a_points_varray points_varray_type:=points_varray_type();
	a_points_nested_table points_nested_table_type;
begin
	insert into environment_data
		(sample_id,points_varray,points_nested_table)
	values(1,points_varray_type(point_type(3,4),point_type(3,5)),					points_nested_table_type(point_type(1,2),point_type(5,9)));

	a_points_varray:=points_varray_type(point_type(1,2),point_type(2,3));
	a_points_nested_table:=points_nested_table_type(point_type(10,11));
	
	insert into environment_data(sample_id,points_varray,points_nested_table)
	values(2,a_points_varray,a_points_nested_table);
	select points_varray,points_nested_table into
		a_points_varray,a_points_nested_table from environment_data 
	where sample_id=1;
end;

---------------------------对象
create or replace type points_object_type as object(
	points points_nested_table_type,
	member function sample_size return number;
	member function point_text return varchar2;
	member function min_x return number;
	member function max_x return number,
	member function avg_x return number,
	member function best_point return point_type,
	member procedure add_to(v point_type));
	
create or replace type body points_object_type as object(
	member function sample_size return number is
	begin
		return points.count;	
	end;
	member function point_text return varchar2 is
		s varchar2(1000);
	begin
		for i in 1..points.count loop
			s:=s||' ('||point(i).x||','||point(i).y||')';
		end loop;
		return s;
	end;
	member function min_x return number is
		result number:=null;
	begin
		for i in 1..points.count loop
			result:=least(nvl(result,point(i).x),point(i).x);
		end loop;
	end;
	
	member function max_x return number is 
		result number:=null;
	begin
		for i in 1..points.count loop
			result:=greatest(nvl(result,point(i).x),point(i).x);
		end loop;
	end;
	
	member function avg_x return number is
		result number:=0;
	begin
		for i in 1..points.count loop
			result:=result+point(i).x;
		end loop;
		return result/points.count;
	end;
	
	member function best_point return point_type is
		pt point_type;
	begin
		pt:=point_type(point(1).x,points(points.count).y);
		return pt;
	end;
	
	member procedure add_to(v point_type) is
	begin
		points.extend; --表扩展
		points(points.count):=v;
		exception when others then
			points:=points_nested_table_type(v); --返回一个?
	end;
	
end;
	
------------------------- 使用对象
create table test_data(
	sample_id number(3),
	points_object points_object_type)
	nested table
	points_object.points store as points_object_tab;
	
declare 
	point_obj points_object_type:=points_obejct_type(
		points_nested_table_type());
	best_point point_type;
begin
	point_obj.add_to(point_type(2,3));
	point_obj.add_to(point_type(6,1));
	point_obj.add_to(point_type(7,3));
	point_obj.add_to(point_type(8,3));

	insert into test_data(sample_id,points_object)
	values(1,point_obj);
	select points_object into point_obj from 
		test_data where sample_id=1;
		
	dbms_output.put_line(point_obj.min_x);
	dbms_output.put_line(point_obj.max_x);
	dbms_output.put_line(point_obj.avg_x);
	best_point:=point_obj.best_point;
	dbms_output.put_line('x='||best_point.x||',y='||best_point.y);
end;
	
-------------------blob操作 大对象
blob 二进制大对象 图像
clob 字符对象
bfile 用于引用文件

create table doc(
	doc_id number(5) primary key,
	document clob);
insert into doc(doc_id,document) values(1,empty_clob()); --空文档

--创建逻辑连接 地位物理文件位置 create any directory权限
create or replace directory sample_docs as 'd:\docs'; 
----------------- 写文件
declare
	the_bfile bfile;
	the_clob clob;
	bfile_size pls_integer;
	clob_size pls_integer;
	v_directory varchar2(30):='SAMPLE_DOCS'; 
	v_filename varchar2(30):='users_manual.pdf';
begin
	the_bfile:=bfilename(v_directory,v_filename); --bfile 逻辑路径获取
	select document into the_clob from doc where doc_id=1 --clob
	for update of doc.document nowait;  --clob

	dbms_lob.open(the_clob,dbms_lob.lob_readwrite); --open clob
	dbms_lob.fileopen(the_bfile,dbms_lob.file_readonly);--open bfile
	bfile_size:=dbms_lob.getlength(the_bfile); 

	dbms_lob.loadfromfile(the_clob,the_bfile,bfile_size); --load to clob
	clob_size:=dbms_lob.getlength(the_clob);
	dbms_lob.fileclose(the_bfile);--close
	dbms_lob.close(the_clob);
	commit;
end;

---------------------读取clob
declare
	the_clob clob;
	clob_size pls_integer;
	max_size pls_integer:=50;
	amount_to_read pls_integer;
	offset pls_integer:=1;
	vbuf varchar2(100):=null;
begin
	select document into the_clob from doc where doc_id=1;  --clob
	--dbms_lob.close(the_clob);
	dbms_lob.open(the_clob,dbms_lob.lob_readonly);--open clob
	clob_size:=dbms_lob.getlength(the_clob);
	amount_to_read:=lease(clob_size,max_size);
dbms_lob.read(the_clob,amount_to_read,offset,vbuf); --read clob 缓冲区要>=长度2倍?
	dbms_lob.close(the_clob); --close
	dbms_output.put_line(vbuf);--replace(vbuf,chr(10),'-')); --替代换行,完全输出
end;	
--------------------case 语句
create table hello (a number(2));
insert into hello values(11);

declare 
	b integer;
begin
	select case
		when a<10 then 1
		when a>=10 and a<20 then 2
		when a>255 then 3 end
	into b from hello;
	dbms_output.put_line('b='||b);
end;

---------- decode
declare 
	b integer;
begin
	select decode(a,'A',0,'B',1,2)
	into b from hello;
	dbms_output.put_line('b='||b);
end;

declare
	name emp.ename%type;
	procedure p(s varchar2) is  --内部
	begin
		dbms_output.put_line(s);
	end;
begin
	name:='kevain';
	case name
		when 'hell' then p('a1');
		when 'john' then p('a2');
		when 'kevin' then p('a3');
		else p('nothing!');
	end case;
end;

-------------------loop
declare
	counter integer:=10;
begin
	loop 
		dbms_output.put_line(counter);
		counter:=counter-1;
		exit when counter=0; -- if counter=0 then exit; end if;
	end loop;
end;

declare 
	ascii_code integer:=97;
	alphabet varchar2(26):=null;
begin
	while(nvl(length(alphabet),0)<26) loop
		alphabet:=alphabet||chr(ascii_code);
		ascii_code:=ascii_code+1;
	end loop;
	dbms_output.put_line(alphabet);

end;

select instr('/aa/bb/cc/dd','/',-1,1) from dual; --最后一个斜杠位置

create or replace function pad_number(n number) return varchar2 is 
	the_pad varchar2(3);
	the_number varchar2(30):=to_char(n);
begin
	select decode(instr(the_number,'.',1),0,'.00',
		length(the_number)-1,'0') into the_pad
	from dual;
	return '$'||to_char(n)||the_pad;
end;

select next_day(trunc(sysdate),'星期六') from dual;

---------------------文件读取
create or replace function get_next_record
	(file in utl_file.file_type,text out varchar2) return boolean is
begin
	utl_file.get_line(file,text);  --read line
	return false;
	exception when no_data_found then
		return true;
end;

create or replace directory D_OUTPUT as 'D:\TEMP';
grant read,write on directory D_OUTPUT to testdb;
GRANT EXECUTE ON utl_file TO testdb;

declare 
	file utl_file.file_type;
	text varchar2(1000);
	end_of_file boolean:=false;
begin
	file:=utl_file.fopen('SAMPLE_DOCS','test.txt','r'); --open
	loop 
		end_of_file:=get_next_record(file,text);
		exit when end_of_file;
		dbms_output.put_line(text);
	end loop;
	utl_file.fclose(file); --close
	exception
		when utl_file.invalid_path then
			dbms_output.put_line('Invalid Path!');
		when utl_file.invalid_mode then
			dbms_output.put_line('Invalid Mode!');
		when utl_file.invalid_operation then
			dbms_output.put_line('Invalid Operation!');
end;