投稿
  1. 簡單一點首頁
  2. 投稿

oracle存儲過程語法「oracle存儲過程入門詳解」

投稿專員 • • 投稿

教學大綱：

  1. PLSQL編程：Hello World、程序結構、變數、流程式控制制、游標.
  2. 存儲過程：概念、無參存儲、有參存儲（輸入、輸出）.
  3. JAVA調用存儲存儲過程.

1. PLSQL編程

1.1. 概念和目的

什麼是PL/SQL?

  1. PL/SQL（Procedure Language/SQL）
  2. PLSQL是Oracle對sql語言的過程化擴展 (類似於Basic)
  3. 指在SQL命令語言中增加了過程處理語句（如分支、循環等），使SQL語言具有過程處理能力。

1.2. 程序結構

通過Plsql Developer工具的Test Window 創建 程序模版或者通過語句在SQL Window編寫

提示：PLSQL語言的大小寫是不區分的

PL/SQL可以分為三個部分：聲明部分、可執行部分、異常處理部分。

-- Created on 2018/3/21 by ADMINISTRATOR 
DECLARE
  -- 聲明變數、游標。
  I INTEGER;
BEGIN
  -- 執行語句

  --[異常處理]
​
END;

其中 DECLARE部分用來聲明變數或游標（結果集類型變數），如果程序中無變數聲明可以省略掉

1.3. Hello World

BEGIN
​
  --列印hello world
​
  DBMS_OUTPUT.PUT_LINE('hello world');
​
END;

其中DBMS_OUTPUT 為oracle內置程序包，相當於Java中的System.out,而PUT_LINE（）是調用的方法,相當於println（）方法

在sqlplus中也可以編寫運行PLSQL程序：

SQL> BEGIN
  2
  3    --列印hello world
  4
  5    DBMS_OUTPUT.PUT_LINE('hello world');
  6
  7  END;
  8  /

PL/SQL 過程已成功完成。

執行結束後並未顯示輸出的結果，默認情況下，輸出選項是關閉狀態的 我們需要開啟一下 set serveroutput on

基於oracle資料庫存儲過程的創建及調用

SQLPLUS中執行PLSQL程序 需要在程序最後添加一個 / 標識程序的結束

1.4. 變數

PLSQL編程中常見的變數分兩大類：

  1. 普通數據類型（char,varchar2, date, number, boolean, long）
  2. 特殊變數類型（引用型變數、記錄型變數）

聲明變數的方式為

變數名  變數類型（變數長度）  例如： v_name  varchar2(20);

1.4.1. 普通變數

變數賦值的方式有兩種：

  1. 直接賦值語句 := 比如: v_name := ‘zhangsan’
  2. 語句賦值，使用select …into … 賦值：（語法 select 值 into 變數）

【示例】列印人員個人信息，包括： 姓名、薪水、地址

-- 列印人員個人信息，包括： 姓名、薪水、地址
DECLARE
  -- 姓名
  V_NAME VARCHAR2(20) := '張三'; -- 聲明變數直接賦值
  --薪水
  V_SAL NUMBER;
  --地址
  V_ADDR VARCHAR2(200);
​
BEGIN
​
  --在程序中直接賦值
  V_SAL := 1580;
​
  --語句賦值
  SELECT '上海市傳智播客' INTO V_ADDR FROM DUAL;
​
  --列印變數
  DBMS_OUTPUT.PUT_LINE('姓名：' || V_NAME || ',薪水：' || V_SAL || ',地址：' ||V_ADDR);
​
END;

1.4.2. 引用型變數

變數的類型和長度取決於表中欄位的類型和長度

通過表名.列名%TYPE指定變數的類型和長度，例如： v_name emp.ename%TYPE;

【示例】查詢emp表中7839號員工的個人信息，列印姓名和薪水

-- 查詢emp表中7839號員工的個人信息，列印姓名和薪水
DECLARE
  -- 姓名
  V_NAME EMP.ENAME%TYPE; -- 聲明變數直接賦值
  --薪水
  V_SAL EMP.SAL%TYPE;
​
BEGIN
  --查詢表中的姓名和薪水並賦值給變數
  --注意查詢的欄位和賦值的變數的順序、個數、類型要一致
  SELECT ENAME, SAL INTO V_NAME, V_SAL FROM EMP WHERE EMPNO = 7839;
​
  --列印變數
  DBMS_OUTPUT.PUT_LINE('姓名：' || V_NAME || ',薪水：' || V_SAL);
​
END;

引用型變數的好處：

使用普通變數定義方式，需要知道表中列的類型，而使用引用類型，不需要考慮列的類型，使用%TYPE是非常好的編程風格，因為它使得PL/SQL更加靈活，更加適應於對資料庫定義的更新。

1.4.3. 記錄型變數

接受表中的一整行記錄，相當於Java中的一個對象

語法： 變數名稱 表名%ROWTYPE， 例如： v_emp emp%rowtype;

【示例】

查詢並列印7839號員工的姓名和薪水

-- 查詢emp表中7839號員工的個人信息，列印姓名和薪水
DECLARE
  -- 記錄型變數接受一行
  V_EMP EMP%ROWTYPE;
​
BEGIN
  --記錄型變數默認接受表中的一行數據，不能指定欄位。
  SELECT * INTO V_EMP FROM EMP WHERE EMPNO = 7839;
​
  --列印變數，通過變數名.屬性的方式獲取變數中的值
  DBMS_OUTPUT.PUT_LINE('姓名：' || V_EMP.ENAME || ',薪水：' || V_EMP.SAL);
​
END;

如果有一個表，有100個欄位，那麼你程序如果要使用這100欄位話，如果你使用引用型變數一個個聲明，會特別麻煩，記錄型變數可以方便的解決這個問題

錯誤的使用：

1． 記錄型變數只能存儲一個完整的行數據

基於oracle資料庫存儲過程的創建及調用

2．返回的行太多了，記錄型變數也接收不了

基於oracle資料庫存儲過程的創建及調用

1.5. 流程式控制制

1.5.1. 條件分支

語法：

BEGIN
​
  IF 條件1 THEN 執行1
    
   ELSIF 條件2 THEN 執行 2
  
   ELSE 執行3
    
  END IF; 
  
END;

注意關鍵字：ELSIF

【示例】判斷emp表中記錄是否超過20條，10-20之間，或者10條以下

DECLARE
  --聲明變數接受emp表中的記錄數
  V_COUNT NUMBER;
​
BEGIN
​
  --查詢emp表中的記錄數賦值給變數
​
  SELECT COUNT(1) INTO V_COUNT FROM EMP;
​
  --判斷列印
​
  IF V_COUNT > 20 THEN
    DBMS_OUTPUT.PUT_LINE('EMP表中的記錄數超過了20條為：' || V_COUNT || '條。');
​
  ELSIF V_COUNT >= 10 THEN
    DBMS_OUTPUT.PUT_LINE('EMP表中的記錄數在10~20條之間為：' || V_COUNT || '條。');
​
  ELSE
    DBMS_OUTPUT.PUT_LINE('EMP表中的記錄數在10條以下為：' || V_COUNT || '條。');
​
  END IF;
​
END;

1.5.2. 循環

在ORACLE中有三種循環方式，這裡我們不展開,只介紹其中一種：loop循環

語法：

BEGIN
  LOOP
    EXIT WHEN 退出循環條件  
  END LOOP;
END;

【示例】列印數字1-10

DECLARE
  --聲明循環變數並賦初值
  V_NUM NUMBER := 1;
​
BEGIN
​
  LOOP
  
    EXIT WHEN V_NUM > 10;
    
    DBMS_OUTPUT.PUT_LINE(V_NUM);
  
    --循環變數自增
    V_NUM := V_NUM + 1;
  
  END LOOP;
​
END;

2. 游標

2.1. 什麼是游標

用於臨時存儲一個查詢返回的多行數據（結果集,類似於Java的Jdbc連接返回的ResultSet集合），通過遍歷游標，可以逐行訪問處理該結果集的數據。

游標的使用方式：聲明—>打開—>讀取—>關閉

2.2. 語法

游標聲明：

CURSOR 游標名[(參數列表)] IS 查詢語句;

游標的打開：

OPEN 游標名;

游標的取值：

FETCH 游標名 INTO 變數列表;

游標的關閉：

CLOSE 游標名;

2.3. 游標的屬性

游標的屬性返回值類型說明

其中 %NOTFOUND是在游標中找不到元素的時候返回TRUE,通常用來判斷退出循環

2.4. 創建和使用

【示例】使用游標查詢emp表中所有員工的姓名和工資，並將其依次列印出來。

--使用游標查詢emp表中所有員工的姓名和工資，並將其依次列印出來。
DECLARE
  --聲明游標
  CURSOR C_EMP IS
    SELECT ENAME, SAL FROM EMP;
​
  --聲明變數用來接受游標中的元素
  V_ENAME EMP.ENAME%TYPE;
​
  V_SAL EMP.SAL%TYPE;
​
BEGIN
​
  --打開游標
  OPEN C_EMP;
​
  --遍歷游標中的值
  LOOP
  
    --通過FETCH語句獲取游標中的值並賦值給變數
    FETCH C_EMP
      INTO V_ENAME, V_SAL;
  
    --通過%NOTFOUND判斷是否有值,有值列印,沒有則退出循環
    EXIT WHEN C_EMP%NOTFOUND;
  
    DBMS_OUTPUT.PUT_LINE('姓名:' || V_ENAME || ',薪水:' || V_SAL);
  
  END LOOP;
​
  --關閉游標
  CLOSE C_EMP;
​
END;

執行結果:

基於oracle資料庫存儲過程的創建及調用

2.5. 帶參數的游標

【示例】使用游標查詢並列印某部門的員工的姓名和薪資，部門編號為運行時手動輸入。

 --使用游標查詢並列印某部門的員工的姓名和薪資，部門編號為運行時手動輸入。
DECLARE
  --聲明游標傳遞參數
  CURSOR C_EMP(V_EMPNO EMP.EMPNO%TYPE) IS
    SELECT ENAME, SAL FROM EMP WHERE EMPNO = V_EMPNO;
​
  --聲明變數用來接受游標中的元素
  V_ENAME EMP.ENAME%TYPE;
​
  V_SAL EMP.SAL%TYPE;
​
BEGIN
​
  --打開游標並傳遞參數
  OPEN C_EMP(10);
​
  --遍歷游標中的值
  LOOP
​
 
​
​
       --通過%NOTFOUND判斷是否有值,有值列印,沒有則退出循環
       EXIT WHEN C_EMP%NOTFOUND;
       
       --通過FETCH語句獲取游標中的值並賦值給變數
    FETCH C_EMP
      INTO V_ENAME, V_SAL;
​
    DBMS_OUTPUT.PUT_LINE('姓名:' || V_ENAME || ',薪水:' || V_SAL);
​
​
  END LOOP;
​
  --關閉游標
  CLOSE C_EMP;
​
END;

注意:%NOTFOUND屬性默認值為FLASE,所以在循環中要注意判斷條件的位置.如果先判斷在FETCH會導致最後一條記錄的值被列印兩次(多循環一次默認);

3. 存儲過程

3.1. 概念作用

之前我們編寫的PLSQL程序可以進行表的操作,判斷,循環邏輯處理的工作,但無法重複調用.

可以理解之前的代碼全都編寫在了main方法中,是匿名程序. JAVA可以通過封裝對象和方法來解決復用問題

PLSQL是將一個個PLSQL的業務處理過程存儲起來進行復用,這些被存儲起來的PLSQL程序稱之為存儲過程

存儲過程作用：

1， 在開發程序中，為了一個特定的業務功能，會向資料庫進行多次連接關閉(連接和關閉是很耗費資源), 需要對資料庫進行多次I/O讀寫，性能比較低。如果把這些業務放到PLSQL中，在應用程序中只需要調用PLSQL就可以做到連接關閉一次資料庫就可以實現我們的業務,可以大大提高效率.

2， ORACLE官方給的建議：能夠讓資料庫操作的不要放在程序中。在資料庫中實現基本上不會出現錯誤，在程序中操作可能會存在錯誤.(如果在資料庫中操作數據,可以有一定的日誌恢復等功能.)

3.2. 語法

CREATE OR REPLACE PROCEDURE 過程名稱[(參數列表)] IS
BEGIN
​
END [過程名稱];

根據參數的類型，我們將其分為3類講解：

l 不帶參數的

l 帶輸入參數的

l 帶輸入輸出參數(返回值)的。

3.3. 無參存儲

3.3.1. 創建存儲

通過Plsql Developer或者語句創建存儲過程:

基於oracle資料庫存儲過程的創建及調用

【示例】 通過調用存儲過程列印hello world

創建存儲過程:

--通過調用存儲過程列印hello world
CREATE OR REPLACE PROCEDURE P_HELLO IS
BEGIN
​
  DBMS_OUTPUT.PUT_LINE('hello world');
​
END P_HELLO;

通過工具查看創建好的存儲過程:

基於oracle資料庫存儲過程的創建及調用

3.3.2. 調用存儲過程

1.通過PLSQL程序調用：

BEGIN
 --直接輸入調用存儲過程的名稱
  P_HELLO;
​
END P_HELLO;

2.在SQLPLUS中通過EXEC命令調用:

基於oracle資料庫存儲過程的創建及調用

提示:SQLPLUS中顯示結果的前提是需要 set serveroutput on

注意：

第一個問題：is和as是可以互用的，用哪個都沒關係的 第二個問題：過程中沒有declare關鍵字，declare用在語句塊中

3.4. 帶輸入參數的存儲過程

【示例】查詢並列印某個員工（如7839號員工）的姓名和薪水–存儲過程：要求，調用的時候傳入員工編號，自動控制台列印。

--查詢並列印某個員工（如7839號員工）的姓名和薪水
--要求，調用的時候傳入員工編號，自動控制台列印。
CREATE OR REPLACE PROCEDURE P_QUERYNAMEANDSAL(I_EMPNO IN EMP.EMPNO%TYPE) IS
  --聲明變數接受查詢結果
  V_ENAME EMP.ENAME%TYPE;
  V_SAL   EMP.SAL%TYPE;
​
BEGIN
​
  --根據用戶傳遞的員工號查詢姓名和薪水
  SELECT ENAME, SAL INTO V_ENAME, V_SAL FROM EMP WHERE EMPNO = I_EMPNO;
​
  --列印結果
  DBMS_OUTPUT.PUT_LINE('姓名:' || V_ENAME || ',薪水:' || V_SAL);
​
END P_QUERYNAMEANDSAL;

命令調用：

SQL> exec p_querynameandsal(7839);
姓名:KING,薪水:5000

PL/SQL 過程已成功完成。

PLSQL程序調用：

BEGIN
​
  P_QUERYNAMEANDSAL(7839);
​
END;

執行結果:

基於oracle資料庫存儲過程的創建及調用

3.5. 帶輸出參數的存儲過程

【示例】輸入員工號查詢某個員工（7839號員工）信息，要求，將薪水作為返回值輸出，給調用的程序使用。

 --輸入員工號查詢某個員工（7839號員工）信息，要求，將薪水作為返回值輸出，給調用的程序使用。
CREATE OR REPLACE PROCEDURE P_QUERYSAL_OUT(I_EMPNO IN EMP.EMPNO%TYPE,O_SAL OUT EMP.SAL%TYPE) IS
​
BEGIN
​
  SELECT SAL INTO O_SAL FROM EMP WHERE EMPNO = I_EMPNO;
​
END P_QUERYSAL_OUT;

PLSQL程序調用：

DECLARE
  --聲明一個變數接受存儲過程的輸出參數
  V_SAL EMP.SAL%TYPE;
​
BEGIN
​
  P_QUERYSAL_OUT(7839, V_SAL); --注意參數的順序
​
  DBMS_OUTPUT.PUT_LINE(V_SAL);
​
END;

注意:調用的時候,參數要與定義的參數的順序和類型一致.

3.7. JAVA程序調用存儲過程

需求:如果一條語句無法實現結果集, 比如需要多表查詢,或者需要複雜邏輯查詢,我們可以選擇調用存儲查詢出你的結果.

3.7.1. 分析jdk API

基於oracle資料庫存儲過程的創建及調用

通過Connection對象的prepareCall方法可以調用存儲過程

基於oracle資料庫存儲過程的創建及調用

得出結論: 通過Connection對象調用prepareCall方法傳遞一個轉義sql語句調用存儲過程, 輸入參數直接調用set方法傳遞.輸出參數需要註冊後,執行存儲過程,通過get方法獲取.參數列表的下標是從1開始

3.7.2. 實現代碼

準備環境：

l  導入Oracle的jar包
【示例】通過員工號查詢員工的姓名和薪資
package cn.itcast.oracle.jdbc;
​
import oracle.jdbc.OracleTypes;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
​
public class ProcedureTest {
    public static void main(String[] args) throws Exception {
        //1.載入驅動
        Class.forName("oracle.jdbc.driver.OracleDriver");
​
        //2.獲得連接對象
​
        //2.1 設置連接字元串
        String url ="jdbc:oracle:thin:@localhost:1521:xe" ;
​
        String name = "scott";
​
        String password = "tiger";
​
        Connection conn = DriverManager.getConnection(url, name, password);
​
​
        //3.獲取語句對象
        String sql = "{call p_querysal_out(?,?)}";//轉義語法,{call  存儲過程(參數列表)}
        CallableStatement call = conn.prepareCall(sql);
​
        //4.設置輸入參數
        call.setInt(1,7839);
​
        //5.註冊輸出參數
        call.registerOutParameter(2, OracleTypes.DOUBLE);
​
        //6.執行存儲過程
        call.execute();
​
        //7.獲取輸出參數
        double sal = call.getDouble(2);
        System.out.println("薪水:"+sal);
​
        //8.釋放資源
        call.close();
        conn.close();
​
    }
}

原創文章，作者：投稿專員，如若轉載，請註明出處：https://www.506064.com/zh-tw/n/255390.html

贊 (0)
打賞 微信掃一掃 微信掃一掃 支付寶掃一掃 支付寶掃一掃
投稿專員的頭像投稿專員
0 0
上一篇 2024-12-15 12:26
下一篇 2024-12-15 12:26

相關推薦

發表回復

登錄後才能評論