oracle 筆記10 雜

--%rowtype的使用...
--不用使用於采用對象方式創建的表，如下，stu表是采用對象方式創建的，就不能執行
declare
stu_record1 stu%rowtype;
begin
    --stu_record1.id:=14;
    --stu_record1.name:=''dklast'';
    select id,name into stu_record1.id,stu_record1.name from stu where id = 1;
    insert into stu (id,name) values(stu_record1.id+2,stu_record1.name);
end;

net start OracleServiceOracle9I
net start OracleOraHome92TNSListener 

--Oracle下面比較重要的文件
...\Oracle\ora92\network\admin\listener.ora   --保存監聽器信息  
...\Oracle\ora92\network\admin\tnsnames.ora   --保存數據庫信息，如IP 

--查出記錄數多於一條的記錄
select * from  test where name in
(select name from
(select name, count(name) count from test group by name)
 where count>1);
another one
select distinct t1.rowid, t1.* from test t1, test t2 where t1.name=t2.name and t1.rowid<>t2.rowid;

--alter table person2  add (gender char(1));
--alter table person2  modify (gender char(2));
--alter table person2  drop (gender,position1);
--ALTER TABLE person2  RENAME COLUMN name1 TO name2;