快捷導(dǎo)航

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

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

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

前言

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

一、Oracle 存儲過程

Oracle 存儲過程是一種存儲在數(shù)據(jù)庫中的可重復(fù)使用的代碼塊，用于執(zhí)行特定的任務(wù)或一系列 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,  --參數(shù)1 數(shù)字
    pi$param2   IN VARCHAR2,--參數(shù)2 字符串
    pi$param3   IN DATE,    --參數(shù)3 日期
    --------------------輸出參數(shù)-----------------------
    po$count           OUT NUMBER, --總條數(shù)
    po$result          OUT  row_types.RESULTSET--結(jié)果集
  );
end testA ;

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

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

數(shù)字類型（Number）

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

字符類型（VARCHAR2、CHAR）

定義：

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

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

日期時間類型（DATE、TIMESTAMP）

定義：

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

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

2.2 package body的創(chuàng)建

package body 是對package 中存儲過程的具體實現(xiàn)（示例）：

（1） package body 示例：

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

（2）結(jié)果集定義游標返回：

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 關(guān)鍵字來調(diào)用存儲過程：

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('結(jié)果內(nèi)容:'||result_row.str1 ||'=='|| 
      result_row.DT  
      );
  end loop;
  close result_1;
end;