Oracle的預存程序介紹

優點：

1。先行編譯，已最佳化，效率較高。避免了SQL語句在網路中傳輸然後再解釋的低效率。
2。如果公司有專門的DBA，寫預存程序可以他來做，程式員只要按他提供的介面調用就好了。這樣分開來做，比較清楚。
3。修改方便。嵌入在程式中的SQL語句修改比較麻煩，而且經常不能肯定該改的是不是都改了。SQLSERVER上的預存程序修改就比較方便，直接改掉該預存程序，調用它的程式基本不用動，除非改動比較大（如改了傳入的參數，返回的資料等）。
4。會安全一點。不會有SQL語句注入問題。

當然，也有缺點。特別是 商務邏輯 比較複雜時，全用預存程序來寫，估計也累的夠嗆。

■SQL預存程序執行起來比SQL命令文本快得多。當一個SQL語句包含在預存程序中時，伺服器不必每次執行它時都要分析和編譯它。

■調用預存程序，可以認為是一個三層結構。這使你的程式易於維護。如果程式需要做某些改動，你只要改動預存程序即可

■你可以在預存程序中利用Transact-SQL的強大功能。一個SQL預存程序可以包含多個SQL語句。你可以使用變數和條件。這意味著你可以用預存程序建立非常複雜的查詢，以非常複雜的方式更新資料庫。

預存程序的能力大大增強了SQL語言的功能和靈活性。預存程序可以用流量控制語句編寫，有很強的靈活性，可以完成複雜的判斷和較複雜的 運算。
　　* 可保證資料的安全性和完整性。
　　# 通過預存程序可以使沒有許可權的使用者在控制之下間接地存取資料庫，從而保證資料的安全。
　　# 通過預存程序可以使相關的動作在一起發生，從而可以維護資料庫的完整性。
　　* 在運行預存程序前，資料庫已對其進行了文法和句法分析，並給出了最佳化執行方案。這種已經編譯好的過程可極大地改善SQL語句的效能。由於執行SQL語句的大部分工作已經完成，所以預存程序能以極快的速度執行。
　　* 可以降低網路的通訊量。
　　* 使體現企業規則的運算程式放入資料庫伺服器中，以便：
　　# 集中控制。
　　# 當企業規則發生變化時在伺服器中改變預存程序即可，無須修改任何應用程式。企業規則的特點是要經常變化，如果把體現企業規則的運算程式放入應用程式中，則當企業規則發生變化時，就需要修改應用程式工作量非常之大（修改、發行和安裝應用程式）。如果把體現企業規則的運算放入預存程序中，則當企業規則發生變化時，只要修改預存程序就可以了，應用程式無須任何變化。

預存程序的寫法：

---建立表
create table TESTTABLE
(
  id1  VARCHAR2(12),
  name VARCHAR2(32)
)
select t.id1,t.name from TESTTABLE t
insert into TESTTABLE (ID1, NAME)
values ('1', 'zhangsan');


insert into TESTTABLE (ID1, NAME)
values ('2', 'lisi');


insert into TESTTABLE (ID1, NAME)
values ('3', 'wangwu');


insert into TESTTABLE (ID1, NAME)
values ('4', 'xiaoliu');


insert into TESTTABLE (ID1, NAME)
values ('5', 'laowu');
---建立預存程序
create or replace procedure test_count
as
v_total number(1);
begin
  select count(*) into v_total from TESTTABLE;
  DBMS_OUTPUT.put_line('總人數：'||v_total);
end;
--準備
--線對scott解鎖：alter user scott account unlock; 
--應為預存程序是在scott使用者下。還要給scott賦予密碼
---alter user scott identified by tiger;
---去命令下執行
EXECUTE test_count;
----在ql/spl中的sql中執行
begin
  -- Call the procedure
  test_count;
end;



create or replace procedure TEST_LIST
      AS 
      ---是用遊標
        CURSOR test_cursor IS select t.id1,t.name from TESTTABLE t;
      begin
        for Test_record IN test_cursor loop---遍曆遊標，在列印出來
           DBMS_OUTPUT.put_line(Test_record.id1||Test_record.name);
           END LOOP;
            test_count;--同時執行另外一個預存程序（TEST_LIST中包含預存程序test_count）
            end;
      -----執行預存程序TEST_LIST
      begin 
         TEST_LIST;
         END;
       ---預存程序的參數
        ---IN  定義一個輸入參數變數，用於傳遞參數給預存程序   
        --OUT 定義一個輸出參數變數，用於從預存程序擷取資料   
        ---IN OUT  定義一個輸入、輸出參數變數，兼有以上兩者的功能  
        --這三種參數只能說明類型，不需要說明具體長度 比如 varchar2(12)，defaul 可以不寫，但是作為一個程式員最好還是寫上。
        
      ---建立有參數的預存程序
      create or replace procedure test_param(p_id1 in VARCHAR2 default '0')
        as v_name varchar2(32);
        begin
          select t.name into v_name from TESTTABLE t where t.id1=p_id1;
          DBMS_OUTPUT.put_line('name：'||v_name);
         end;
      ----執行預存程序
      begin
         test_param('1');
       end;
       
       default '0'




      ---建立有參數的預存程序
      create or replace procedure test_paramout(v_name OUT VARCHAR2 )
        as  
        begin
          select name into v_name from TESTTABLE where id1='1';
          DBMS_OUTPUT.put_line('name：'||v_name);
         end;
      ----執行預存程序
    DECLARE  
        v_name VARCHAR2(32);   
        BEGIN  
        test_paramout(v_name);
        DBMS_OUTPUT.PUT_LINE('name：'||v_name);   
        END;  
    -------IN OUT
    ---建立預存程序
    create or replace procedure test_paramINOUT(p_phonenumber in out varchar2)
    as  
    begin 
      p_phonenumber:='0571-'||p_phonenumber;
    end;
    
    ----
    DECLARE  
    p_phonenumber VARCHAR2(32);  
    BEGIN  
    p_phonenumber:='26731092';  
    test_paramINOUT(p_phonenumber);  
    DBMS_OUTPUT.PUT_LINE('新的電話號碼：'||p_phonenumber);  
    END;  
    -----sql命令下，查詢目前使用者的預存程序或函數的原始碼，
    -----可以通過對USER_SOURCE資料字典視圖的查詢得到。USER_SOURCE的結構如下：
    
    SQL> DESCRIBE USER_SOURCE ;
    Name Type           Nullable Default Comments                                                                                                      
    ---- -------------- -------- ------- ------------------------------------------------------------------------------------------------------------- 
    NAME VARCHAR2(30)   Y                Name of the object                                                                                            
    TYPE VARCHAR2(12)   Y                Type of the object: "TYPE", "TYPE BODY", "PROCEDURE", "FUNCTION",
    "PACKAGE", "PACKAGE BODY" or "Java SOURCE" 
    LINE NUMBER         Y                Line number of this line of source                                                                            
    TEXT VARCHAR2(4000) Y                Source text                                                                                                   
    SQL>