【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