文章內容

Oracle 常用小知識點匯集

發布時間: 2012/9/5 17:44:37

使用sqlldr導入數據：

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

(BELONG_CODE,RUN_NAME,PHONE_NO)

2.命令行語句：
sqlldr name/pass@tnsname control='E:\ctl.txt' log='E:\log.txt'
注釋：若要中途斷了，可以查看日志，入庫了多少行，然后使用skip=入庫了的行數，就可以繼續追加了。但是注意把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這里的條件可以是多個，用and，or連接

在看一個復合的例子：

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()函數，特殊用法 --instr()函數代替like，IN

instr（）返回值是目標字符（串）在母字符里第一次出現的位置，故而是整數。

例如：

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

instr是內置函數，他是會走索引的，性能好。

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 中可以創建組合索引，使用表中多個字段的組合作為索引的鍵值。當我們進行查詢時可以使用”where col1 = ?”，也可以使用”where col1 = ? and col2 = ?”，這樣的限制條件都會使用索引，但是”where col2 = ?”查詢就不會使用該索引。所以限制條件中包含先導列時，該限制條件才會使用該組合索引

創建組合索引的方法：

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啟動的腳本中的每個sql命令，缺省為on
SQL> set echo on //設置運行命令是是否顯示語句
SQL> set feedback on; //設置顯示“已選擇XX行”
SQL>set feedback off; //回顯本次sql命令處理的記錄條數，缺省為on
SQL>set heading off; //輸出域標題，缺省為on
SQL>set pagesize 0; //輸出每頁行數，缺省為24,為了避免分頁，可設定為0。
SQL>set linesize 80; //輸出一行字符個數，缺省為80
SQL>set numwidth 12; //輸出number類型域長度，缺省為10
SQL>set termout off; //顯示腳本中的命令的執行結果，缺省為on
SQL>set trimout on; //去除標準輸出每行的拖尾空格，缺省為off
SQL>set trimspool on; //去除重定向（spool）輸出每行的拖尾空格，缺省為off
SQL>set serveroutput on; //設置允許顯示輸出類似dbms_output
SQL> set timing on; //設置顯示“已用時間：XXXX”
SQL> set autotrace on-; //設置允許對執行的sql進行分析
set verify off //可以關閉和打開提示確認信息old 1和new 1的顯示.

查看/恢復回收站的表

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 書

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

A B
---------- ----------
1 書
方法2：
原理：通過截取第一個字符的長度來判斷。
SQL> select aa.* from aa where lengthb(substr(b,1,1))=2 ;

A B
---------- ----------
1 書

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

rollup()：使分組結果中包含小計及總計信息，并可以傳入多列字段
cube()：對做為參數傳入的每一列都進行小計，多列的時候能顯示優勢。
例：
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