Oracle中如何寫存儲過程

Oracle中如何寫存儲過程

存儲過程是什麼?

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

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

存儲過程被儲存在資料庫中，但是不能被SQL直接執行或調用，隻能通過EXECUT指令執行或在PL/SQL程式塊内部被調用。

建立存儲過程的文法格式:

1

2

3

4

5

6

CREATE [OR REPLACE] procedure pro_name[;number] [(parameter1[,parameter2]...] is|as

begin

plsql_sentences;           

[exception]

[dowith_sentences;]           

end [pro_name];

-->pro_name:存儲過程的名稱,如果資料庫中已經存在了此名稱,則可以指定"OR PLACE"關鍵字來覆寫原來的存儲過程;如果不想覆寫,可在名稱後面添加"；number"，number為随機數，作為區分存儲過程的标簽；例如：proc_tent;1 proctent;2 這樣就可以區分開了

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

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

-->plsql_sentences:PL/SQL語句,存儲過程功能實作的主體

-->dowith_sentences:異常處理語句,是可選的plsql語句

建立和運作簡單的存儲過程

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

create or replace procedure pro_insertDept is

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

insert into dept

values(id,dname,deptno);

end insert_dept;

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

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

傳參數的方式有三種：

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

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

還是用栗子比較容易懂,不太明白的,請看這裡:(因為标注字段名稱,是以,指派時位置可以颠倒)

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

　　　

2)按位置傳遞(這種方式不用寫字段名稱,是以指派順序必須與字段标準順序一緻)

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

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

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

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

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

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

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

7

8

9

10

11

12

13

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           

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;                --表示計算平方           

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           

insert into dept2 values(num_deptno,var_dname,var_loc); --插入一條記錄

end insert_dept2_default;

　執行執行過程：

邊學習邊總結了存儲過程的基本寫法規則也就這些,希望能給大家一些啟發~~~~

以上代碼都是個人親自敲的,執行過後都是沒有問題的。如果哪位大牛覺得代碼有不妥的地方,希望不吝賜教，萬分感謝！

原文位址

https://www.cnblogs.com/SpaceKiller/p/10615599.html