Oracle中如何寫存儲過程_ZenDei技術網路在線

>存儲過程是什麼? 存儲過程是一種命名的PL/SQL程式塊,輸入、輸出參數數量【0個或多個】，通常沒有返回值【也有特殊的情況】。運行SQL語句時，Oracle是要先編譯再執行的；而存儲過程是已經編譯好的代碼，所以被調用或引用時，執行效率非常高。存儲過程被保存在資料庫中，但是不能被SQL直接執行或 ...

>存儲過程是什麼?

存儲過程是一種命名的PL/SQL程式塊,輸入、輸出參數數量【0個或多個】，通常沒有返回值【也有特殊的情況】。

運行SQL語句時，Oracle是要先編譯再執行的；而存儲過程是已經編譯好的代碼，所以被調用或引用時，執行效率非常高。

存儲過程被保存在資料庫中，但是不能被SQL直接執行或調用，可以通過EXECUTE命令執行或在PL/SQL程式塊內部被調用。

>創建存儲過程的語法格式:

CREATE [OR REPLACE] procedure pro_name
    [(parameter1[,parameter2]...] is|as
begin
    plsql_sentences;
[exception]
    [dowith_sentences;]
end [pro_name];

-->pro_name:存儲過程的名稱,如果資料庫中已經存在了此名稱,則可以指定"OR PLACE"關鍵字來覆蓋原來的存儲過程;如果不想覆蓋,可修改名稱,這樣就可以區分開了

-->parameter1: 存儲過程被調用或執行時用到的參數。【註意：這個值不是存儲過程內定義的內部變數，內部變數應該在is｜as之後定義】

parameter1如果是輸入參數,則需要在後面指定IN關鍵字;如果是輸出參數,則需要在後面指定OUT關鍵字,在IN和OUT後面加的是參數的數據類型,不需要指定長度;【不明白不要緊,後面會舉例說明;】

-->plsql_sentences:PL/SQL語句,存儲過程中功能實現的主要代碼

-->dowith_sentences:異常處理語句,是可選的PL/SQL語句(不需要可以不寫)

>創建和運行簡單的存儲過程

>舉個慄子----【創建一個存儲過程,該存儲過程實現向dept表中插入一條記錄】（首先記得自己建一張表，這裡不再演示了）

create [or replace] procedure pro_insertDept is
begin
insert into dept values(77,'市場拓展部','8');
commit;
dbms_output.put_line('插入新紀錄成功!');
end pro_insertDept;

執行Execute之後,

從上面的結果看出,我們已經順利的創建出了存儲過程pro_insertDept。【如果沒有創建成功的話,可以使用show error命令查看錯誤信息。】

》》如果在我們創建之前,就已經有這個同名的存儲過程了，那麼應該怎麼處理呢？【兩種方法】

　　①修改現有的存儲過程名稱，重新創建

　　②使用欄位中“OR Replcae"關鍵字創建一個同名的存儲過程覆蓋已經存在的存儲過程。實際使用不加中括弧,這裡只是表示可有可無;

　　　　表示已經存在的話覆蓋後,創建存儲過程；沒有的話，直接創建存儲過程。

-->如果現在使用SQL*PLUS去查詢插入的記錄，一定是不存在的

原因是,我們只是創建了存儲過程而並沒有執行,若想要執行的話,使用Execute關鍵字來執行存儲過程；也可以簡寫”EXEC“

-->接下里我們嘗試在PL/SQL塊中調用此存儲過程，之前先將上一條記錄刪除，因為欄位ID為主鍵；

然後按照下麵的方式調用即可；

註:set serverout on 意思是在視窗里顯示伺服器輸出信息。

>帶參數的存儲過程

①IN模式參數-------->“輸入類型參數,參數由調用者傳入,只能被存儲過程讀取,是預設的參數模式,也是最常用的”

>舉個慄子----【創建一個存儲過程,並定義3個IN模式的變數,然後將這3個變數的值插入到dept表中,代碼及運行結果如下。】

create or replace procedure insert_dept(

　　id in number,

　　dname in varchar2,

　　deptno in number) is
begin
insert into dept
values(id,dname,deptno);
commit;
end insert_dept;

創建存儲過程成功！ 【註意點：參數類型不能指定長度。】

在調用或執行IN模式的存儲過程時，用戶需要自己傳參數，你傳什麼，它就使用什麼去執行。

>傳參數的方式有三種：

1)指定名稱傳遞-->參數名稱在左,參數值在右,中間使用賦值符號"=>"連接:

  pro_name(parameter1=>value1[,parameter2=>value2]...)

還是用慄子比較容易懂,不太明白的,請看這裡:(因為標註欄位名稱,所以,賦值時位置可以顛倒)

【在PL/SQL塊中調用存儲過程insert_dept,然後使用'指定名稱'的方式向其傳入參數數值,最後執行當前的PL/SQL塊】　　

2)按位置傳遞(這種方式不用寫欄位名稱,所以賦值順序必須與欄位標準順序一致)

##因為有的時候參數過多,或者參數名稱太長不好記,這種方法只要記住順序就可以了,實在記不住,使用DESC查看;

3)混合方式傳遞(顧名思義:這是將前兩者結合使用的)

執行方法,還可以使用Execute關鍵字執行,和上面使用的方法結果是一樣的;

特別註意:因為在中間使用了"指名方式"傳值,所以後面的參數都要使用指名方式;因為指名方式可能已經破壞了參數原始的定義順序了.

②OUT模式參數----->"輸出類型參數,表示這個參數在存儲過程中已經被賦值,並且參數值可以傳遞到當前存儲過程以外的環境中"

老規矩--看慄子(慄子說:"為什麼老是看我 ~_~!!")

【創建一個存儲過程，要求定義兩個OUT模式的字元類型的參數，然後在dept表中檢索到的一行部門信息存儲到這兩個參數中】

create or replace procedure select_dept_out(
    m_id in number,                            --定義IN模式變數,要求輸入人員編號
    m_dname out  dept.dname%type,    --定義OUT模式變數,可以存儲部門名稱並輸出
    m_deptno out dept.deptno%type) is
begin
    select dname,deptno
    into m_dname,m_deptno
    from dept
    where id=m_id;                           --檢索某個員工號的員工信息
exception 
  when no_data_found then                   --如果select語句沒有結果返回
      dbms_output.put_line('該員工編號不存在');   --輸出信息
end select_dept_out;

　　當調用或者執行以上存儲過程時,都需要定義變數來保存這兩個out參數，下麵對OUT模式如何調用或執行分別舉例子說明：

一》在PL/SQL塊中調用OUT模式的存儲過程:在PL/SQL塊的DECLARE部分定義與存儲過程中out參數相容的若幹變數

【首先在PL/SQL塊中聲明若幹變數，然後調用select_dept_out存儲過程,並將定義的變數傳入該存儲過程,以便接收out參數的返回值】

具體過程：執行上述代碼時，聲明的兩個變數會被傳入到存儲過程中，但存儲過程執行時，其中的out參數會被賦值，存儲過程執行完畢後，OUT參數的值會在調用處（begin）返回，之後定義的兩個變數(declare)就能得到傳回來的值，就可以在存儲過程之外任意使用了。

二》使用Exec執行OUT模式的存儲過程:使用Exec命令需要在SQL*Plus環境中使用variable關鍵字聲明兩個變數,用來存儲out參數的返回值

【使用variable關鍵字聲明兩個變數,分別用來存儲部門名稱和部門編號,然後使用exec命令執行存儲過程,並傳入聲明的兩個變數來接收out參數的返回值】

Exec執行的方式是無法直接看到結果的,可以通過兩種方式輸出變數的值: ①print命令 ②select語句

#>print命令

#>select語句

③IN OUT模式參數

開始之前咱們現總結一下IN和OUT的特性：

在執行存儲過程時,

IN參數只能根據調用者傳入的值去執行存儲過程,不能被修改;

OUT參數只能等待存儲過程執行完畢為其賦值再供外界使用,不能像IN一樣為存儲過程提供數據;

到這里,大家想一想:如果我要是想【計算一個數的平方或者平方根】,這種存儲過程怎麼寫呢？

豈不是要是用IN傳入一個數,再用OUT定義一個變數來接收了？不過大家仔細想一下，我們想要計算的值傳進去後，就沒用了，如果再原路將計算結果返還回來，那該多好，就不用單獨定義OUT參數了，結果就有了IN OUT模式參數

IN OUT就是解決這個問題的;兼顧了IN和OUT的參數特性調用存儲過程時，上面的分析如果看懂了，這裡就不詳細解釋定義了。就是給定一個參數，在存儲過程執行過程中，發生了改變，之後再將該參數原路返還給調用者；

創建存儲過程，計算一個數的平方或平方根：

create or replace procedure pro_square(
    num in out number,
    flag in boolean) is
    i int:=2;                --表示計算平方
begin
    if flag then             --if語句,如果為true
      num:=power(num,i);     --計算平方
    else                     --否則
      num:=sqrt(num);        --計算平方根
      end if;
end pro_square;

執行上述存儲過程：

--->IN參數的預設值(IN類型是可以設定預設值的,上面忘了說了,放在最後吧,重新建了表dept2)

IN模式的參數是可以設定預設值的,當執行時,調用者沒有傳值的話,可以使用預設值賦值:看慄子

【創建一個存儲過程，定義3個IN參數，並將其中的兩個參數設置為初始預設值；然後將這三個IN參數的值插入到dept中】

create or replace procedure insert_dept2_default(
      num_deptno in number,
      var_dname in varchar2 default '綜合部',        --定義部門名稱並設定預設值
      var_loc in varchar2 default '北京') is
begin 
  insert into dept2 values(num_deptno,var_dname,var_loc);  --插入一條記錄
end insert_dept2_default;

　執行執行過程：