Oracle 常用小知識(shí)點(diǎn)匯集

發(fā)布時(shí)間: 2012/9/5 17:44:37

使用sqlldr導(dǎo)入數(shù)據(jù)：

1.control控制文件的寫(xiě)法：
load data
infile 'E:\netbu.csv'
insert
into table temp_zhuren_need
FIELDS TERMINATED BY ','
TRAILING NULLCOLS --表的字段沒(méi)有對(duì)應(yīng)的值時(shí)允許為空(空格，空白，null都為空)-

(BELONG_CODE,RUN_NAME,PHONE_NO)

2.命令行語(yǔ)句：
sqlldr name/pass@tnsname control='E:\ctl.txt' log='E:\log.txt'
注釋?zhuān)喝粢型緮嗔�，可以查看日志，入�?kù)了多少行，然后使用skip=入庫(kù)了的行數(shù)，就可以繼續(xù)追加了。但是注意把insert改為append。

Oracle CASE的兩種用法：

例子1：
select product_id,product_type_id,
case product_type_id
when 1 then 'Book'
when 2 then 'Video'
else 'Magazine'
end ----注意end不能少
from products
例子2：
select product_id,product_type_id,
case
when product_type_id=1 then 'Book'
when product_type_id=2 then 'Video'
else 'Magazine'
end ----注意end不能少
from products
注意：when這里的條件可以是多個(gè)，用and，or連接

在看一個(gè)復(fù)合的例子：

select t.phone_no1204,
nvl(t.phone_no1201,t.phone_no1204) as "phone_no1201",
nvl(t.phone_no1110,t.phone_no1204) as "phone_no1110",
nvl(t.phone_no1107,t.phone_no1204) as "phone_no1107",
case
when t.phone_no1204=nvl(t.phone_no1201,t.phone_no1204)
and t.phone_no1204=nvl(t.phone_no1110,t.phone_no1204)
and t.phone_no1204=nvl(t.phone_no1107,t.phone_no1204)
then '0'
else '1'
end as phone_no1101
from mark_518_t9 t

instr()函數(shù)，特殊用法 --instr()函數(shù)代替like，IN

instr（）返回值是目標(biāo)字符（串）在母字符里第一次出現(xiàn)的位置，故而是整數(shù)。

例如：

instr("efabcdefg","e")的結(jié)果當(dāng)然是1
instr(2，"efabcdefg","e")的結(jié)果就是7
instr(5，"efabcdefg","a")的結(jié)果就是0
instr("efabcdefg","k")的結(jié)果也是0

instr是內(nèi)置函數(shù)，他是會(huì)走索引的，性能好。

like不一定。like '%iii%’是不走索引的，like ‘ik%’走索引。

例如：
SELECT code, name, dept, occupation FROM staff WHERE instr(code, '001') > 0;
等同于===>
SELECT code, name, dept, occupation FROM staff WHERE code LIKE '%001%' ;

代替in：
SELECT code , name , dept, occupation FROM staff WHERE code IN ('A10001','A10002');
等同于===>
SELECT code , name , dept, occupation FROM staff WHERE instr('A10001,A10002',code)>0

組合索引

在Oracle 中可以創(chuàng)建組合索引，使用表中多個(gè)字段的組合作為索引的鍵值。當(dāng)我們進(jìn)行查詢(xún)時(shí)可以使用”where col1 = ?”，也可以使用”where col1 = ? and col2 = ?”，這樣的限制條件都會(huì)使用索引，但是”where col2 = ?”查詢(xún)就不會(huì)使用該索引。所以限制條件中包含先導(dǎo)列時(shí)，該限制條件才會(huì)使用該組合索引

創(chuàng)建組合索引的方法：

create index index_name on table_name (column_name1，column_name2)

minus與intersect的使用
minus是A中存在，B中不存在的部分
intersect是AB的交際
eg：
select job from accounts
intersect/minus
select job from sales;

Oracle set命令
SQL>set colsep' '; //-域輸出分隔符
SQL>set echo off; //顯示start啟動(dòng)的腳本中的每個(gè)sql命令，缺省為on
SQL> set echo on //設(shè)置運(yùn)行命令是是否顯示語(yǔ)句
SQL> set feedback on; //設(shè)置顯示“已選擇XX行”
SQL>set feedback off; //回顯本次sql命令處理的記錄條數(shù)，缺省為on
SQL>set heading off; //輸出域標(biāo)題，缺省為on
SQL>set pagesize 0; //輸出每頁(yè)行數(shù)，缺省為24,為了避免分頁(yè)，可設(shè)定為0。
SQL>set linesize 80; //輸出一行字符個(gè)數(shù)，缺省為80
SQL>set numwidth 12; //輸出number類(lèi)型域長(zhǎng)度，缺省為10
SQL>set termout off; //顯示腳本中的命令的執(zhí)行結(jié)果，缺省為on
SQL>set trimout on; //去除標(biāo)準(zhǔn)輸出每行的拖尾空格，缺省為off
SQL>set trimspool on; //去除重定向（spool）輸出每行的拖尾空格，缺省為off
SQL>set serveroutput on; //設(shè)置允許顯示輸出類(lèi)似dbms_output
SQL> set timing on; //設(shè)置顯示“已用時(shí)間：XXXX”
SQL> set autotrace on-; //設(shè)置允許對(duì)執(zhí)行的sql進(jìn)行分析
set verify off //可以關(guān)閉和打開(kāi)提示確認(rèn)信息old 1和new 1的顯示.

查看/恢復(fù)回收站的表

select * from recyclebin t where t.original_name like 'MB_G%';
或者 show recyclebin
flashback table MB_G_2009 to before drop;

篩選oralce表中某列是漢字還是字符的方法：
SQL> select * from aa;

A B
---------- ----------
1 aaa
1 11111
1 書(shū)

方法1：
原理：asciistr()函數(shù)對(duì)非ASCII代碼，會(huì)轉(zhuǎn)換成二進(jìn)制，且前面加\ ;所以asciistr(acolumn) 如果是漢字;轉(zhuǎn)換出來(lái)的會(huì)有 \
SQL> select aa.* from aa where asciistr(b) like '\%' ;

A B
---------- ----------
1 書(shū)
方法2：
原理：通過(guò)截取第一個(gè)字符的長(zhǎng)度來(lái)判斷。
SQL> select aa.* from aa where lengthb(substr(b,1,1))=2 ;

A B
---------- ----------
1 書(shū)

擴(kuò)展的GROUP BY
所謂的擴(kuò)展的GROUP BY就是使用了分析函數(shù)rollup() 和 cube()。

rollup()：使分組結(jié)果中包含小計(jì)及總計(jì)信息，并可以傳入多列字段
cube()：對(duì)做為參數(shù)傳入的每一列都進(jìn)行小計(jì)，多列的時(shí)候能顯示優(yōu)勢(shì)。
例：
select id,sum(mount) from tt group by rollup(id);
ID SUM(MOUNT)
---------- ----------
1 60
2 150
3 240
4 210
5 440
1100
select id,sum(mount) from tt group by cube(id);
ID SUM(MOUNT)
---------- ----------
1100
1 60
2 150
3 240
4 210
5 440