Oracle存儲過程創(chuàng)建方式

更新時間：2025年02月19日 11:15:09 作者：拽著尾巴的魚兒

本文介紹了Oracle存儲過程的創(chuàng)建,包括package和package body的定義,以及輸入/輸出參數的類型,通過示例代碼,詳細說明了如何創(chuàng)建和使用存儲過程,并總結了個人經驗

前言

在做的一個功能，業(yè)務要求數據處理的一部分邏輯要通過Oracle的存儲過程實現，因為很少使用存儲過程進行數據邏輯的處理，故進行一個記錄。

一、Oracle 存儲過程

Oracle 存儲過程是一種存儲在數據庫中的可重復使用的代碼塊，用于執(zhí)行特定的任務或一系列 SQL 操作。

二、Oracle 存儲的創(chuàng)建

2.1 package 的創(chuàng)建

2.1.1 package create

定義一個 package 中的方法，類似于java 中的借口。

（示例）：

create or replace package testA is
PROCEDURE find(
    pi$param1   IN NUMBER,  --參數1 數字
    pi$param2   IN VARCHAR2,--參數2 字符串
    pi$param3   IN DATE,    --參數3 日期
    --------------------輸出參數-----------------------
    po$count           OUT NUMBER, --總條數
    po$result          OUT  row_types.RESULTSET--結果集
  );
end testA ;

create or replace package testA is ： testA 對應包名；
PROCEDURE find ：find 為testA 包名下對應的存儲過程名稱；
pi$param1 IN NUMBER, --參數1 數字： IN 代表是輸入的參數，即要傳值的參數；
po$count OUT NUMBER,： out 代表返回的數據

2.1.2 存儲過程常用的輸入/輸出參數類型

數字類型（Number）

定義：用于存儲數值數據，可以表示整數、小數等。例如，NUMBER(5,2)表示一個總共 5 位，其中小數部分占 2 位的數字。
用途：在輸入參數中，可以用于接收如商品價格、員工工資、數量等數值。在輸出參數中，可返回計算后的數值結果，如計算后的總價、平均值等。

字符類型（VARCHAR2、CHAR）

定義：

VARCHAR2是可變長度的字符類型，存儲長度根據實際存儲的字符數確定。例如，VARCHAR2(100)可以存儲最多 100 個字符的字符串。
CHAR是固定長度的字符類型，存儲時如果實際字符數小于定義長度，會用空格填充。如CHAR(10)存儲長度總是 10 個字符。

用途：作為輸入參數，常用于接收名稱（如員工姓名、產品名稱）、描述信息等字符串。作為輸出參數，可以返回如格式化后的名稱、錯誤消息等字符串。

日期時間類型（DATE、TIMESTAMP）

定義：

DATE類型用于存儲日期和時間信息，精確到秒。它存儲世紀、年、月、日、時、分、秒等信息。
TIMESTAMP類型比DATE更精確，它可以存儲小數秒，并且在處理跨時區(qū)的日期時間等場景更有用。

用途：在輸入參數方面，可接收如訂單日期、員工入職日期等日期時間值。在輸出參數中，能返回如計算后的到期日期、操作完成時間等。

2.2 package body的創(chuàng)建

package body 是對package 中存儲過程的具體實現（示例）：

（1） package body 示例：

create or replace package body testA is
PROCEDURE find(
    pi$param1   IN NUMBER,  --參數1 數字
    pi$param2   IN VARCHAR2,--參數2 字符串
    pi$param3   IN DATE,    --參數3 日期
    --------------------輸出參數-----------------------
    po$count           OUT NUMBER, --總條數
    po$result          OUT row_types.RESULTSET--游標結果集
  )
  is
 -- 此處可以聲明變量：在 Oracle 存儲過程中，使用DECLARE關鍵字來聲明變量。
 -- 變量聲明通常放在存儲過程的IS或AS關鍵字之后，BEGIN關鍵字之前。聲明變量的基本格式為變量名 數據類型 [ := 初始值];
 DECLARE
    v_name VARCHAR2(50);
    v_count NUMBER := 0;
    v_1 VARCHAR2(50) :='';
 begin 
 	-- 此處處理業(yè)務邏輯
  -- 最后返回結果：
  -- 直接賦值
  po$count := 數量
  -- 通過select  into 賦值
   SELECT COUNT(1) INTO po$count FROM xxx;
  -- 通過游標返回數據集合
   OPEN po$result FOR sql 語句;
  end;
  
  
end testA ;

（2）結果集定義游標返回：

CREATE OR REPLACE PACKAGE row_types
AS
  TYPE  resultSet  IS REF CURSOR;
  TYPE  strSplit IS TABLE OF VARCHAR2 (40);
  Type  uuidArray is Table OF number(20);
END;

（3）使用 CALL 或 EXECUTE 關鍵字來調用存儲過程：

DECLARE
  count_1 NUMBER;
  result_1 row_types.RESULTSET;
  TYPE result_record_type IS RECORD (
        str1   VARCHAR2(500),
        str2   VARCHAR2(4000),
        str3   VARCHAR2(500),
        str4   VARCHAR2(500),
        str5   VARCHAR2(500),
        str6   VARCHAR2(500),
        str7   VARCHAR2(500),
        str8   VARCHAR2(500),
        str9   VARCHAR2(500),
        str10 VARCHAR2(500),
        str11 VARCHAR2(500),
        str12 VARCHAR2(500),
        str13 VARCHAR2(500),
        str14 VARCHAR2(500),
        str15 VARCHAR2(500),
        str16 VARCHAR2(500),
        str17 VARCHAR2(500),
        str18 VARCHAR2(500),
        str19 VARCHAR2(500),
        str20 VARCHAR2(500),
        str21 VARCHAR2(500),
        str22 VARCHAR2(500),
        str23 VARCHAR2(500),
        num1 NUMBER,
        num2 NUMBER,
        num3 NUMBER,
        num4 NUMBER,
        num5 NUMBER,
        num6 NUMBER,
        num7 NUMBER,
        num8 NUMBER,
        num9 NUMBER,
        DT   DATE,
        DT1  DATE,
        DT2  DATE,
        DT3  DATE,
        DT4  DATE
    );
    result_row result_record_type;
begin
	testA.find(1,'test',count_1,result_1);
  DBMS_OUTPUT.put_line('DHIDHDIDIH:'||count_1);
  loop
      fetch result_1 into result_row.str1,result_row.DT;
      EXIT WHEN result_1%NOTFOUND;
      dbms_output.put_line('結果內容:'||result_row.str1 ||'=='|| 
      result_row.DT  
      );
  end loop;
  close result_1;
end;