oracle 預存程序(1)

標籤：

說明

建立一個預存程序與編寫一個普通的PL/SQL程式快有很多相似地方,比如:包括生命部分,執行部分和異常部分.但是兩者之間實現細節還是有很多差別的,比如:建立預存程序需要使用procedure關鍵字,在關鍵字後就是過程名稱和參數列表;建立預存程序不需要使用declare關鍵字,而是使用create或replace.

要建立一個過程對象（procedural object），必須有 CREATE PROCEDURE 系統許可權。如果這個過程對象需要被其他的使用者schema 使用，那麼你必須有 CREATE ANY PROCEDURE 許可權。執行 procedure 的時候，可能需要excute許可權。或者EXCUTE ANY PROCEDURE 許可權。如果單獨賦予許可權，如下例所示：  

grant  execute on MY_PROCEDURE  to Jelly

基本文法如下:

    CREATE [OR REPLACE] PROCEDURE 預存程序名[(參數[IN|OUT|IN OUT] 資料類型...)]
　　{AS|IS}
　　[說明部分]
　　BEGIN 　　
　　可執行部分
　　[EXCEPTION
　　錯誤處理部分]
　　END [過程名];

其中： 可選關鍵字OR REPLACE 表示如果預存程序已經存在，則用新的預存程序覆蓋，通常用於預存程序的重建。 參數部分用於定義多個參數(如果沒有參數，就可以省略)。參數有三種形式：IN、OUT和IN OUT。如果沒有指明參數的形式，則預設為IN。 關鍵字AS也可以寫成IS，後跟過程的說明部分，可以在此定義過程的局部變數。 編寫預存程序可以使用任何文字編輯器或直接在SQL*Plus環境下進行，編寫好的預存程序必須要在SQL*Plus環境下進行編譯，產生編譯代碼，原代碼和編譯代碼在編譯過程中都會被存入資料庫。編譯成功的預存程序就可以在Oracle環境下進行調用了。 一個預存程序在不需要時可以刪除。刪除預存程序的人是過程的建立者或者擁有DROP ANY PROCEDURE系統許可權的人。刪除預存程序的文法如下：

DROP PROCEDURE 預存程序名；

預存程序的優點

• 執行效率很高，因為預存程序是先行編譯的，即建立時編譯，而SQL語句是執行一次，編譯一次。調用預存程序可以大大減少同資料庫的互動次數。
• 降低網路通訊量，因為預存程序執行的時候，只需要call預存程序名，不需要傳遞大量的SQL語句。 
• 有利於複用。

預存程序的缺點

• 移植性非常差，如果在oracle上寫的預存程序，移植到mysql需要修改。
• 代碼可讀性差，實現一個簡單的邏輯，代碼會非常長。

預存程序的用途

• 造測試資料：可以使用預存程序，往表裡造幾百萬條資料。
• 資料同步：兩個表之間按照一定的商務邏輯進行資料同步。
• 資料採礦。

預存程序注意事項

• 資料量大的時候（10萬+），一定要做壓力測試，有些預存程序在大資料量的情況下才會出現問題。
• 如果插入或者更新的次數比較多，為了提高效率，可以執行一萬次，再commit一次。
• 如果先插入記錄，沒有commit，再對這條記錄進行更新，會引起死結。如果先後對同一筆記錄進行更新，又沒有commit，也會引起死結。因為後一條語句會等待前一條語句提交。如果出現這種情況，則需要一條條commit。
• 不要忘記在預存程序裡寫commit。

效能測試

•  用預存程序插入40萬資料用了10秒。
•  遍曆並判斷40萬條資料用了25秒。
•  80萬次SQL判斷+40萬次SQL插入=25秒。

擴充

•  預存程序執行非常慢，有可能是更新語句引起了死結，也有可能是語句執行慢（需要建索引）。
•  預存程序編譯非常慢，有可能是當前預存程序正在執行，被鎖住了。（使用DBA帳號解鎖）。

執行個體:

(1)  建立表

 create table dept(id int,name varchar(30),address varchar(20));

(2) 建立預存程序

    create or replace procedure pro_insertDept is
 begin
 insert into dept values(100,‘shimang‘,‘beijing‘);
 commit;
 dbms_output.put_line(‘insert sucess!‘);
 end pro_insertDept;

(3) 執行預存程序

SQL> execute pro_insertDept;

PL/SQL 過程已成功完成。

SQL> select * from dept;

        ID NAME ---------- ------------------------------------------------------------ ADDRESS ----------------------------------------      

      100 shimang beijing

 

oracle 預存程序(1)