【oracle 学习笔记 7】外部表， 对象表

Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> conn system as sysdba
Enter password: ***********
Connected.
SQL> create directory ext_data as 'c:/exterior';
Directory created.
SQL> grant read, write on directory ext_data to ymc;
Grant succeeded.
SQL> edit
Wrote file afiedt.buf
  1  create table testExt(
  2  no varchar(10),
  3  tip varchar(10))
  4  organization external (
  5  type oracle_loader
  6  default directory ext_data
  7  access parameters(
  8  records delimited by newline
  9  badfile 'bad_dev.txt'
 10  fields terminated by ',')
 11  location('f1.txt','f2.txt')
 12* )
SQL> /
Table created.
SQL> select * from testExt;
NO         TIP                                                                  
---------- ----------                                                           
11         OneLine                                                              
22         TwoLine                                                              
33         ThreeLine                                                            
44         FourLine                                                             
--在f1中添加一行后
SQL> /
NO         TIP                                                                  
---------- ----------                                                           
11         OneLine                                                              
22         TwoLine                                                              
66         sixLine                                                              
33         ThreeLine                                                            
44         FourLine                                                             
SQL> delete testExt;
delete testExt
       *
ERROR at line 1:
ORA-30657: operation not supported on external organized table 
SQL> edit
Wrote file afiedt.buf
  1  create table impExt
  2  as
  3  select * from testExt
  4* where 1=2
SQL> /
Table created.
SQL> insert into impExt
  2  select * from testExt;
5 rows created.
SQL> select * from impExt;
NO         TIP                                                                  
---------- ----------                                                           
11         OneLine                                                              
22         TwoLine                                                              
66         sixLine                                                              
33         ThreeLine                                                            
44         FourLine                                                             
SQL> delete impExt;
5 rows deleted.
SQL> select * from impExt;
no rows selected
SQL> spool off;

SQL> edit
Wrote file afiedt.buf
  1  create or replace type student as object (
  2  	sid varchar2(10),
  3  	sname varchar2(15),
  4  	sex     char(2),
  5  	birthday date,
  6  	nativePlace varchar2(100),
  7  	member procedure printInfo,
  8  	map member function compare return date
  9* );
SQL> /
Type created.
SQL> edit
Wrote file afiedt.buf
  1  create or replace type body student is
  2  member procedure printInfo is
  3  begin
  4  	dbms_output.put('sid=' || sid || '.sname=' || sname);
  5  	dbms_output.put(',sex' || sex || ', birthday=' || birthday);
  6  	dbms_output.put_line(',nativePlace' || nativePlace);
  7  end;
  8  Map member function compare return date is
  9  begin
 10  	return birthday;
 11  end;
 12*  end;
 13  /
Type body created.
SQL> edit
Wrote file afiedt.buf
  1* create table studentTable of student
SQL> /.
Table created.
SQL> edit
Wrote file afiedt.buf
  1  declare
  2  vstudent student;
  3  begin
  4  	insert into studentTable
  5  	values('0001','mwt','F',date'1991-05-15','Jinyun');
  6  	vstudent:=student('0002','ymc','M',date'1989-07-17','Shangyu');
  7  	insert into studentTable
  8  	values(vstudent);
  9* end;
SQL> /
PL/SQL procedure successfully completed.
SQL> set serveroutput on
SQL> edit
Wrote file afiedt.buf
  1  declare
  2  vstudent student;
  3  begin
  4  	select value(t) into vstudent from studentTable t where sid = '0001';
  5  	vstudent.printInfo;
  6* end;
SQL> /
sid=0001.sname=mwt,sexF , birthday=15-5月 -91,nativePlaceJinyun                 
PL/SQL procedure successfully completed.
SQL> edit
Wrote file afiedt.buf
  1  declare
  2  Ann student;
  3  Ben	student;
  4  begin
  5  	select value(t) into Ann from studentTable t where sid='0001';
  6  	select value(t) into Ben from studentTable t where sid='0002';
  7  	if Ann>Ben then
  8  		dbms_output.put_line(Ann.sname || ' Litter Ann');
  9  	elsif Ann=Ben then
 10  		dbms_output.put_line('Equal~');
 11  	else
 12  		dbms_output.put_line(Ben.sname || ' Litter Ben');
 13  	end if;
 14* end;
SQL> /
mwt Litter Ann                                                                  
PL/SQL procedure successfully completed.
SQL> spool off