快捷導(dǎo)航

Oracle存儲(chǔ)過(guò)程創(chuàng)建方式

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

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

前言

在做的一個(gè)功能，業(yè)務(wù)要求數(shù)據(jù)處理的一部分邏輯要通過(guò)Oracle的存儲(chǔ)過(guò)程實(shí)現(xiàn)，因?yàn)楹苌偈褂么鎯?chǔ)過(guò)程進(jìn)行數(shù)據(jù)邏輯的處理，故進(jìn)行一個(gè)記錄。

一、Oracle 存儲(chǔ)過(guò)程

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

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

2.1 package 的創(chuàng)建

2.1.1 package create

定義一個(gè) package 中的方法，類(lèi)似于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 對(duì)應(yīng)包名；
PROCEDURE find ：find 為testA 包名下對(duì)應(yīng)的存儲(chǔ)過(guò)程名稱(chēng)；
pi$param1 IN NUMBER, --參數(shù)1 數(shù)字： IN 代表是輸入的參數(shù)，即要傳值的參數(shù)；
po$count OUT NUMBER,： out 代表返回的數(shù)據(jù)

2.1.2 存儲(chǔ)過(guò)程常用的輸入/輸出參數(shù)類(lèi)型

數(shù)字類(lèi)型（Number）

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

字符類(lèi)型（VARCHAR2、CHAR）

定義：

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

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

日期時(shí)間類(lèi)型（DATE、TIMESTAMP）

定義：

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

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

2.2 package body的創(chuàng)建

package body 是對(duì)package 中存儲(chǔ)過(guò)程的具體實(shí)現(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--游標(biāo)結(jié)果集
  )
  is
 -- 此處可以聲明變量：在 Oracle 存儲(chǔ)過(guò)程中，使用DECLARE關(guān)鍵字來(lái)聲明變量。
 -- 變量聲明通常放在存儲(chǔ)過(guò)程的IS或AS關(guān)鍵字之后，BEGIN關(guān)鍵字之前。聲明變量的基本格式為變量名 數(shù)據(jù)類(lèi)型 [ := 初始值];
 DECLARE
    v_name VARCHAR2(50);
    v_count NUMBER := 0;
    v_1 VARCHAR2(50) :='';
 begin 
 	-- 此處處理業(yè)務(wù)邏輯
  -- 最后返回結(jié)果：
  -- 直接賦值
  po$count := 數(shù)量
  -- 通過(guò)select  into 賦值
   SELECT COUNT(1) INTO po$count FROM xxx;
  -- 通過(guò)游標(biāo)返回?cái)?shù)據(jù)集合
   OPEN po$result FOR sql 語(yǔ)句;
  end;
  
  
end testA ;

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

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)鍵字來(lái)調(diào)用存儲(chǔ)過(guò)程：

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;