天天看點
傳回

PL/SQL存儲函數，存儲過程

一.存儲過程和存儲函數

存儲過程和存儲函數跟我們知道的表、視圖、索引、序列、同義詞等一樣是我們資料中的對象。

筆記教程見：https://github.com/caojx-git/learn/blob/master/notes/oracle/PLSQL.sql

1.1什麼是存儲過程和存儲函數：

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

存儲過程和存儲函數的相同點：完成特定功能的程式

存儲過程和存儲函數的差別：是否用return 語句傳回值，存儲過程不能使用return 傳回一個函數的值，存儲函數可以，對于其他的

可以認為他們是相同的。

二、建立和使用存儲過程

使用create procedure指令建立存儲過程和存儲函數

文法：

     
  1.  --建立或替換一個存儲過程              參數清單需要指明輸入或者輸出參數       
    2.       
  2.  create [          or           replace]           procedure 過程名（          Name           in |           out           type,           Name           in |           out           type, ...）       
    3.       
  3.  as |           is           --相當于PL/SQL塊的declare，這裡不可省略       
    4.       
  4.  PLSQL子程式體；       
    5.

1.2.1不帶參數的存儲過程

--第一個存儲過程列印helloworld --注意不帶參數的存儲過程過程名不能有()

     
  1.  create [          or           replace]           procedure sayhelloWorld       
    2.       
  2.  as       
    3.       
  3.  --相當于PL/SQL中declare說明部分，不過這裡即使沒有說明部分也需要寫       
    4.       
  4.  begin       
    5.       
  5.  dbms_output.put_line(          'helloWorld');       
    6.       
  6.  end sayhelloWorld;       
    7.

如果使用PL/SQL Developer 工具，如果執行成功可以在左邊的procedure區域看到我們執行後編譯後的存儲過程（成功一般顯示為綠色，如果不對會有一個小紅x）

PL/SQL存儲函數，存儲過程

1.2.2調用存儲過程

方式一：在 Command Windows（指令視窗中執行） exec sayhelloWorld();

PL/SQL存儲函數，存儲過程

方式二：可以在SQL Windows或 Command Windowsz中執行PL/SQL調用

PL/SQL存儲函數，存儲過程
PL/SQL存儲函數，存儲過程

1.2.3帶參數的存儲過程

--建立一個帶參數的存儲過程，給指定的員工漲100員工資，并列印漲前和漲後的薪水

--in表示是一個輸入參數，如果帶參數，需要指明是輸入參數還是輸出參數

     
  1.  create           or repalce           procedure raisesalary(eno           in           number)       
    2.       
  2.  as       
    3.       
  3.  --定義一個變量儲存漲前的薪水       
    4.       
  4.  psal emp.sal%          type;       
    5.       
  5.  begin       
    6.       
  6.  --得到員工的漲前的薪水       
    7.       
  7.  select sal           into psal emp           where empno=eno;       
    8.       
  8.  --給員工漲100       
    9.       
  9.  update emp           set sal = sal+          where empno = eno;       
    10.       
  10.  --這裡進行了update,不過我們一般不在存儲過程和存儲函數中進行送出事務，一般由調用者進行送出       
    11.           
  11.  --列印漲前和漲後的薪水       
    12.       
  12.  dbms_output.put_line('漲前:'||psal||'漲後'||(psal+100));       
    13.       
  13.  end;       
    14.

1.2.4 帶參數的存儲過程的調用

     
  1.  begin       
    2.       
  2.  raisesalary(          );           --給員工号為7839漲工資       
    3.       
  3.  raisesalary(7566);           --給員工号為7566漲工資       
    4.       
  4.  end;       
    5.

1.3存儲過程的debug調試

有時候有一些存儲過程比較大，需要進行debug調試，看是否符合我們的邏輯需求，使用PL/SQL Developer可以對存儲過程進行調試

1.3.1選中需要調試的存儲過程--》test進入debug調試模式

PL/SQL存儲函數，存儲過程

1.3.2設定斷點，可以單步運作

PL/SQL存儲函數，存儲過程

三、存儲函數

函數（Function） 為一命名的存儲程式，可帶參數，并傳回一計算值

函數和過程結構類似，但必需要有一個return子句，用于傳回函數數值。

3.1建立存儲函數的文法

--帶參數的存儲函數必需指明參數清單是輸入參數還是輸出參數

--假如不帶參數，不能帶（）

     
  1.  create [          or           replace]           function 函數名（          Name           in |           out           type,           Name           in |           out           type, ...）       
    2.       
  2.  return 函數值類型       
    3.       
  3.  as |           is       
    4.       
  4.  PL/          SQL子程式體；       
    5.

案例：

--存儲函數：查詢某個員工的年收入

     
  1.  create           or           replace           function queryempincomme(eno           in           number)       
    2.       
  2.  return           number       
    3.       
  3.  as       
    4.       
  4.  --定義變量儲存員工的薪水和獎金       
    5.       
  5.  psal emp.sal%          type;       
    6.       
  6.  pcomm emp.comm%type;       
    7.       
  7.  begin       
    8.       
  8.  --得到員工的月薪和獎金       
    9.       
  9.  select sal,comm           into psal,pcomm           from emp           where empno=eno;       
    10.       
  10.  --直接傳回年收入       
    11.       
  11.  return psal*12+nvl(pcomm,0);       
    12.       
  12.  end;       
    13.

3.1.1存儲函數的調用

     
  1.  declare       
    2.       
  2.  ypsal           number;       
    3.       
  3.  begin       
    4.       
  4.  --得到員工7891的年收入       
    5.       
  5.  ypsal:=queryempincomme(          );       
    6.       
  6.  dbms_output.put_line(ypsal);       
    7.       
  7.  end;       
    8.

四、in和out 參數

前邊我們介紹了存儲過程和存儲函數

a.一般來講，存儲過程和存儲函數的差別在于存儲函數可以有一個傳回值；而存儲過程沒有傳回值

b.如果存儲過程或存儲函數帶參數的話我們需要指明是輸入參數還是輸出參數

c.存儲過程和存儲函數都可以通過out參數指定一個或多個輸出參數，我們可以利用out參數，在過程或函數中實作傳回一個或多個值

 （即存儲過程本來不能有傳回值，但利用out參數，我們就可以實作存儲過程傳回值）

d.一般如果需要傳回多個值，我們優先使用存儲過程，如果隻要傳回一個值我們優先使用存儲函數

案例：

--利用out參數查詢員工的姓名，月薪和職位

     
  1.  create           or           replace           procedure queryempinfo(eno           in           number,pename           out           varchar2,psal           out nubmer,pjob           out           varchar2)       
    2.       
  2.  as       
    3.       
  3.  begin       
    4.       
  4.  --得到員工的姓名，月薪，職位       
    5.       
  5.  select ename,sal,empjob           into pename,psal,pjob           from emp           where empno=eno;       
    6.       
  6.  end;       
    7.

調用：

     
  1.  declare       
    2.       
  2.  eno           number;       
    3.       
  3.  pename varchar2(30);       
    4.       
  4.  psal number;       
    5.       
  5.  pjob varchar2(200);       
    6.       
  6.  begin       
    7.       
  7.  eno :=           ;       
    8.       
  8.  --調用存儲過程，我們可以得到out參數的傳回值       
    9.       
  9.  queryempinfo(eno,pename,psal,pjob);       
    10.       
  10.  dbms_output.put_line(pename);       
    11.       
  11.  dbms_output.put_line(psal);       
    12.       
  12.  dbms_output.put_line(pjob);       
    13.       
  13.  end;       
    14.

問題：上邊的案例隻利用out參數傳回了員工的部分資訊

1.假如需要查詢員工的所有資訊，out參數有很多，難道要寫很多個out參數？

2.查詢某個部門中所有員工的所有資訊---》out中傳回一個集合

五、程式包

程式包在一般情況下使用的還是比較少的，這裡粗略的說一下，如果需要使用，請見其他資料如《精通Oracle10g SQL和PL/SQL》

oracle中的程式包分為標頭和包體，標頭負責聲明，包體負責實作（者很像java中的接口與實作類的關系）

標頭文法：

     
  1.  create [          or           replace]           package package_name       
    2.       
  2.  is |           as       
    3.       
  3.  --定義公用常量、變量、遊标、類型       
    4.       
  4.  --定義公用的過程和函數       
    5.       
  5.  end package_name;       
    6.

包體文法

     
  1.  create [          or           replace]           package           body package_name       
    2.       
  2.  is |           as       
    3.       
  3.  --定義私有常量、變量、類型、遊标、過程和函數       
    4.       
  4.  --實作公用的過程和函數       
    5.       
  5.  end package_name       
    6.

案例：查詢某個部門中所有員工的所有資訊,這裡使用如下方案，實作第4條留下的幾個問題out參數很多顯然不可取，我們是使用cursor光标實作

建立標頭

     
  1.  create           or           replace           package mypackage           as       
    2.       
  2.  --定義公用的類型 自定義類型empcursor 為 cursor類型       
    3.       
  3.  type empcursor           is           ref           cursor;       
    4.       
  4.  --定義公用的過程和函數 --之後需要在包體中實作       
    5.       
  5.  procedure queryEmpList(dno in number,empList out empcursor);       
    6.       
  6.  end mypackage;       
    7.

建立包體

     
  1.  create           or           replace           package           body mypackage       
    2.       
  2.  as       
    3.       
  3.  procedure queryEmpList(dno           in           number,empList           out empcursor)       
    4.       
  4.  as       
    5.       
  5.  begin       
    6.       
  6.  --打開光标       
    7.       
  7.  open empcursor           from           select *           from emp           where deptno=dno;       
    8.       
  8.  end queryEmpList;       
    9.       
  9.  end mypackage;       
    10.

調用程式包：

調用公用變量

exec 程式包名.公用變量名 := 指派；

調用公共過程

exec 程式包名.公用過程名(參數)；

下邊兩個圖是我截取《精通Oracle10g SQL和PL/SQL》的片段

PL/SQL存儲函數，存儲過程
PL/SQL存儲函數，存儲過程

一.存儲過程和存儲函數

sql 存儲過程 存儲函數 plsql
上一篇: clickhouse 重命名表_基于 Flink+ClickHouse 打造輕量級點選流實時數倉
下一篇: 目前最完美的Windows Server 2008企業版激活方法

繼續閱讀