Oracle 存储过程创建

---

前言

在做的一个功能，业务要求数据处理的一部分逻辑要通过Oracle的存储过程实现，因为很少使用存储过程进行数据逻辑的处理，故进行一个记录。

---

一、Oracle 存储过程：

Oracle 存储过程是一种存储在数据库中的可重复使用的代码块，用于执行特定的任务或一系列 SQL 操作。

二、Oracle 存储的创建

2.1 package 的创建：

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更精确，它可以存储小数秒，并且在处理跨时区的日期时间等场景更有用。
用途：在输入参数方面，可接收如订单日期、员工入职日期等日期时间值。在输出参数中，能返回如计算后的到期日期、操作完成时间等。

2.2 package body的创建：

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 
 	-- 此处处理业务逻辑
  -- 最后返回结果：
  -- 直接赋值
  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;

---

总结

本文对 oracle 存储过程的创建进行记录。