Oracle存儲過程和自定義函數

新博客文章鏈接,歡迎大家評論探討

概述

　　存儲過程和存儲函數是指存儲在資料庫中供所有用戶程式調用的子程式叫存儲過程、存儲函數。

異同點：　

• 存儲過程和存儲函數的相同點：完成特定功能的程式。
• 存儲過程和存儲函數的區別：是否用return語句返回值。

存儲過程的創建和調用

第一個存儲過程：列印 hello world

create or replace procedure sayhelloword
as
  -- 說明部分,as一定要寫
begin
    dbms_output.put_line('Hello World');
end;
/

調用存儲過程

1. exec sayhelloworld()

2. -- 調用兩次
   begin
      sayhelloworld();
      sayhelloworld();
   end;
   /

oracle 帶參數的存儲過程

-- 創建一個帶參數的存儲過程
-- 給指定的員工漲100塊錢的工資，並且列印漲前後漲後的薪水
create or replace procedure raisesalary(eno in number) -- in 這是一個輸入參數
as
  -- 定義一個變數保存漲前的薪水
    psal emp.sal%type;
begin
   -- 得到員工漲前的薪水
    select sal into psal from emp where empno=eno;
   -- 給該員工漲100塊錢
    update emp set sal=sal+100 where empno=eno;
   -- 一般，這裡不需要 commit ！ 也不需要 rollback
   -- 註意：一般不在存儲過程或者存儲函數中，commit 和 rollback 
   -- 列印
    dbms_output.put_line('漲前：'||psal||'，漲後：'||(psal+100));
end;
/
-- 調用：
begin
   raisesalary(7839);
   raisesalary(7566); 
end;
/

如何調試存儲過程

1. 調試存儲過程最好放到Oracle資料庫所在的系統或虛擬機上，解壓SQL developer ，雙擊運行。
2. 為了確保存儲過程或函數是可調試的，右鍵“以編譯併進行調試”，點擊紅色按鈕“調試”
3. 利用已寫好的調用函數進行調試。

4. 給調試賬戶授權

   grant DEBUG CONNECT SESSION ,DEBUG ANY PROCEDURE to scott;

存儲函數

函數的定義

　　是一個命名的存儲程式，可帶參數，並返回一個計算值。必須有return 子句，用於返回函數值。

創建存儲函數語法

  create or replace function 函數名(參數列表)
  return 函數值類型
  as
  begin
    PLSQL子程式體;
  end;

註意 表達式中某個欄位為空時，表達式返回值為空。為防止含有表達式的返回值錯誤，在可能為空的欄位上加上NVL(欄位名，0)。

--查詢某個員工的年收入
create or replace function queryemp_income(eno in number)
return number
as 
    --定義變數接收薪水和獎金
    p_sal emp.sal%type;
    p_comm emp.comm%type;
begin
  select sal,comm into p_sal,p_comm from emp where empno=eno;
  --nvl為遇空函數，如果p_comm為空則返回0
  return nvl(p_comm,0)+p_sal*12;
end;
/

out 參數

　　存儲過程和存儲函數都可以有多個輸入（in）和輸出參數（out），都可以通過out參數實現返回多個值。

-- out參數：查詢某個員工姓名、月薪和職位
-- 原則： 如果只有一個返回值，用存儲函數；否則，就用存儲過程。
create or replace procedure queryempinfor(eno in number,pename out varchar2,
  psal out number,pjob out varchar2)
as 
begin
   -- 得到該員工的姓名、月薪和職位
    select ename,sal,empjob into pename,psal,pjob from emp where empno=eno;
end;
/

在 out 參數中訪問游標

申明包結構

• 包頭(申明)
• 包體(實現)

案例

-- 查詢某個部門中所有員工的所有信息  //ref(reference引用) cursor(游標)
#包頭
create or replace package mypackage as
type empcursor is ref cursor;
procedure queryEmpList(dno in number,empList out empcursor);
end mypackage;

#包體
create or replace package body mypackage as
procedure queryEmpList(dno in number,empList out empcursor) as
 begin
   open empList for select * from emp where deptno=dno;
 end queryEmpList;
end mypackage;
***********包體需要實現包頭中聲明的所有方法*********************

在應用程式中訪問

　　在java應用程式中訪問存儲過程和存儲函數以及訪問包下的存儲過程，可以查看java API文檔。

Reference:

• 慕課網－oracle 存儲過程和自定義函數