Oracle Notes

最近工作中用到了Oracle，筆記記錄一下：

遊标cursor

create or replace procedure sp_test(seq_id number) as

        cursor curData is select t.propertyname, t.propertyvalue from kinfoc3.new_prop_39460 t where t.id = prop_id;

begin

        for rowData in curData loop

                  -- do something with rowData

        end loop;

end;

使用遊标查詢到資料庫集後再周遊裡面的子集，感覺速度非常快。

還有一種遊标是SYS_REFCURSOR型遊标，經過測試，發現SYS_REFCURSOR型遊标在效率上比前面的那種遊标差了好多！好處是可以作出參數進行傳遞。

create or replace procedure test(rsCursor out SYS_REFCURSOR) is

cursor SYS_REFCURSOR; name varhcar(20);

OPEN cursor FOR select name from student where ... --SYS_REFCURSOR隻能通過OPEN方法來打開和指派

LOOP

fetch cursor into name   --SYS_REFCURSOR隻能通過fetch into來打開和周遊 exit when cursor%NOTFOUND;              --SYS_REFCURSOR中可使用三個狀态屬性：                                         ---%NOTFOUND(未找到記錄資訊) %FOUND(找到記錄資訊)                                         ---%ROWCOUNT(然後目前遊标所指向的行位置)

dbms_output.putline(name);

end LOOP;

rsCursor := cursor;

end test;

建索引加快查詢速度。

假如某個表經常根據列ID和列Name來查詢，則可以建立ID+Name的是以加快查詢速度。

synonyms同義詞

從字面上了解就是别名的意思，和試圖的功能類似。就是一種映射關系。

同義詞擁有如下好處：節省大量的資料庫空間，對不同使用者的操作同一張表沒有多少差别;擴充的資料庫的使用範圍，能夠在不同的資料庫使用者之間實作無縫互動;同義詞可以建立在不同一個資料庫伺服器上，通過網絡實作連接配接。

SQL*Loader

SQL*Loader，是Oracle資料庫系統提供的一個資料移植工具，它提供了一個指令行的方式，可以讓使用者成批的向Oracle資料庫中裝入大量資料。雖然Oracle資料庫與SQL Server資料庫都提供了圖形界面的導入工具，但是，圖形界面有一個很大的不足，就是不能夠直接給前台程式引用。而指令行的導入子產品，則可以直接被前台的應用程式所調用，這也是SQL*Loader之是以成為Oracle資料庫系統最通用的工具之一的原因。

SQL*Loader其具有如下的優勢：

1. 接被前台應用程式調用。

2. 可以從既定檔案中大量導入資料。

3. 可以實作把多個資料檔案合并成一個檔案。

4. 修複、分離壞的記錄。

一般SQL*Loader子產品至少需要兩個檔案，才可以使用。

一是資料檔案。

        資料檔案，顧名思義，就是我們需要導入的資料集合。對于Oracle系統來說，其可以支援多個格式的資料檔案，如逗号分隔符或者 TAB鍵分隔符或者分号分隔符等文本檔案，也支援固定寬度的文本檔案等等。不過在實際應用中，用的最多的還是逗号分隔的文本檔案。

二是控制檔案。

        控制檔案其起的作用就是建立資料檔案與Oracle資料表字段之間的一一對應關系。簡單的說，把資料檔案中的某個内容放在Oracle資料表中的那個字段上，這就是控制檔案所起的主要作用。

TRUNC()函數

截斷函數文法：

TRUNC（date[,fmt]）

TRUNC（number[,decimals]）

舉例：假如現在是2008-9-11 9:30(sysdate)，則：

trunc(sysdate, 'yy') = 2008-1-1         -- 意思是将該日期截斷到“年(yy)”

trunc(sysdate, 'mm') = 2008-9-1       -- 将日期截斷到“月(mm)”

不傳第二個參數時，預設截斷到“天(dd)”，

trunc(sysdate, 'dd') = 2008-9-11

當參數是數字時，如：

trunc(1234.5678, 1) = 1234.5          -- 小數點後一位開始截斷

trunc(1234.5678, -1) = 1230            -- 小數點前一位開始截斷

索引

Cost　該操作的成本

Card　該操作通路的行

Bytes 該操作通路的byte 數

1.用IN來替換OR

    這是一條簡單易記的規則，但是實際的執行效果還須檢驗，在ORACLE8i下，兩者的執行路徑似乎是相同的．

    低效: 　　SELECT…. FROM LOCATION WHERE LOC_ID = 10 OR LOC_ID = 20 OR LOC_ID = 30 　　

    高效 　　SELECT… FROM LOCATION WHERE LOC_IN IN (10,20,30);

2.對索引列使用OR将造成全表掃描，用UNION替換OR (适用于索引列，where 子句為等号的情況)

    高效: 　　SELECT LOC_ID , LOC_DESC , REGION 　　FROM LOCATION 　　WHERE LOC_ID = 10 　　UNION 　　SELECT LOC_ID , LOC_DESC , REGION 　　FROM LOCATION 　　WHERE REGION = “MELBOURNE” 　　

    低效: 　　SELECT LOC_ID , LOC_DESC , REGION 　　FROM LOCATION 　　WHERE LOC_ID = 10 OR REGION = “MELBOURNE”

OVER (PARTITION BY ..)

測試了下面兩種寫法的效率，發現效率幾乎沒有什麼差别：

寫法一：

select t.id id, max(t.time) time

        from table t

       where t.id is not null

         and trunc(t.time) < trunc(sysdate)

       group by t.id

寫法二：

select b.id id, b.time time from (select t.id id, t.time time,

       row_number() over(partition by t.id order by t.time desc) rn

         and trunc(t.time) < trunc(sysdate))b where b.rn = 1

觸發器

是特定事件出現的時候，自動執行的代碼塊。類似于存儲過程，但是使用者不能直接調用他們。

　　功能：

　　1、 允許/限制對表的修改

　　2、 自動生成派生列，比如自增字段

　　3、 強制資料一緻性

　　4、 提供審計和日志記錄

　　5、 防止無效的事務處理

　　6、 啟用複雜的業務邏輯 

例子：

create trigger biufer_employees_department_id

        before insert or update

               of department_id

               on employees

        referencing old as old_value

                        new as new_value

        for each row

        when (new_value.department_id<>80 )

　　begin

        :new_value.commission_pct :=0;

　　end;

instr函數

INSTR方法的格式為

INSTR(源字元串, 目标字元串, 起始位置, 比對序号)

例如：INSTR('CORPORATE FLOOR','OR', 3, 2)中，源字元串為'CORPORATE FLOOR', 目标字元串為'OR'，起始位置為3，取第2個比對項的位置。

預設查找順序為從左到右。當起始位置為負數的時候，從右邊開始查找。

是以SELECT INSTR('CORPORATE FLOOR', 'OR', -1, 1) "Instring" FROM DUAL的顯示結果是

Instring

——————

14

Oracle左連接配接,右連接配接

資料表的連接配接有: 

1、内連接配接(自然連接配接): 隻有兩個表相比對的行才能在結果集中出現 

2、外連接配接: 包括 

（1）左外連接配接(左邊的表不加限制) 

（2）右外連接配接(右邊的表不加限制) 

（3）全外連接配接(左右兩表都不加限制) 

3、自連接配接(連接配接發生在一張基表内)

select a.studentno, a.studentname, b.classname

      from students a, classes b

      where a.classid(+) = b.classid;

STUDENTNO STUDENTNAM CLASSNAME

---------- ---------- ------------------------------

            1 周虎          一年級一班

            2 周林          一年級二班

                             一年級三班

以上語句是右連接配接：

即"(+)"所在位置的另一側為連接配接的方向，右連接配接說明等号右側的所有

記錄均會被顯示，無論其在左側是否得到比對。也就是說上例中，無

論會不會出現某個班級沒有一個學生的情況，這個班級的名字都會在

查詢結構中出現。

反之： 

       from students a, classes b

      where a.classid = b.classid(+);

            3 鐘林達

則是左連接配接，無論這個學生有沒有一個能在一個班級中得到比對的部門号，

這個學生的記錄都會被顯示。

      where a.classid = b.classid;

這個則是通常用到的内連接配接,顯示兩表都符合條件的記錄

總之,

左連接配接顯示左邊全部的和右邊與左邊相同的 

右連接配接顯示右邊全部的和左邊與右邊相同的 

内連接配接是隻顯示滿足條件的!

（待補充……）

本文轉自CoderZh部落格園部落格，原文連結：http://www.cnblogs.com/coderzh/archive/2008/11/23/1288850.html，如需轉載請自行聯系原作者