[大数据]Oracle系列之 -------- 存储过程

目录

-- 创建一个最简单的oracle存储过程

-- 创建一个带输入参数的oracle存储过程

-- 创建一个带异常处理的oracle存储过程

-- 创建一个带输入参数和输出参数的oracle存储过程

-- 创建带while loop循环的oracle存储过程

-- 创建一个带for循环的oracle存储过程

-- 创建一段可以执行的程序

--游标测试（显示游标）

-- 测试游标的使用

--记录测试

-- 测试记录型变量的使用

-- 触发器测试（自动补齐表格AAA中的反范式字段）

-- 测试PLSQL中变量的声明和赋值

-- 测试引用型变量（通过%type来定义变量类型）

-- 测试记录型变量

-- 测试loop循环（通过if加exit退出循环）

-- 测试loop循环（通过exit when退出循环）

-- 测试while loop循环

-- 测试for loop循环

-- 测试函数（该函数是阶乘函数n! = n*(n-1)*...*1）

-- 测试sql的内置函数

oracle 存储过程

-- 创建一个最简单的oracle存储过程

create or replace procedure myDemo01
as
BEGIN
?? ?-- 打印语句
?? ?dbms_output.put_line('hello word, my name is stored procedure');
END;
-- 调用存储过程
CALL myDemo01();

-- 创建一个带输入参数的oracle存储过程

CREATE OR REPLACE PROCEDURE myDemo02(name IN varchar, age IN int)
AS
BEGIN
?? ?-- 打印输入的参数
?? ?dbms_output.put_line('姓名：'||name||',年龄：'||age);
END;
-- 调用存储过程
CALL myDemo02('张三'，21);

-- 创建一个带异常处理的oracle存储过程

CREATE OR REPLACE PROCEDURE myDemo03 AS
?? ?-- 定义变量
?? ?age int;
begin?
?? ?-- 在存储过程的程序中给变量赋值
?? ?age:=1/0;
?? ?-- 打印变量
?? ?dbms_output.put_line(age);
?? ?-- 处理oracle存储过程中的异常
?? ?EXCEPTION WHEN OTHERS THEN
?? ??? ?dbms_output.put_line('error');
END;
--调用存储过程
CALL myDemo03();

-- 创建一个带输入参数和输出参数的oracle存储过程

CREATE OR REPLACE PROCEDURE myDemo04(name OUT PGFSBWL_MATERIAL_PLAN.ITEM_NAME%TYPE, age IN PGFSBWL_MATERIAL_PLAN.MR_ID%TYPE)
AS
?? ?-- 定义变量
?? ?itemName PGFSBWL_MATERIAL_PLAN.ITEM_NAME%TYPE;
BEGIN ?
?? ?-- 查询指定数据
?? ?SELECT ITEM_NAME INTO itemName FROM PGFSBWL_MATERIAL_PLAN WHERE MR_ID = age;
?? ?-- 将指定数据写入输出参数name中
?? ?SELECT sum(APPLY_QTY) INTO name FROM PGFSBWL_MATERIAL_PLAN WHERE ITEM_NAME=itemName ;
END;
-- 调用存储过程
DECLARE
?? ?name varchar(10);
begin?
?? ?myDemo04(name,'141GF22060545');
?? ?dbms_output.put_line('姓名：'||name);
END;

-- 创建带while loop循环的oracle存储过程

CREATE OR REPLACE PROCEDURE myDemo05
AS?
?? ?-- 定义变量并直接赋值
?? ?n_count NUMBER := 0;
begin?
?? ?-- 定义循环，并指定执行循环的条件（当n_count小于5时执行循环）
?? ?WHILE n_count < 5 LOOP
?? ??? ?dbms_output.put_line(n_count);
?? ??? ?n_count := ?n_count + 1;
?? ?END LOOP;
END;
-- 调用存储过程
CALL myDemo05();

-- 创建一个带for循环的oracle存储过程

CREATE OR REPLACE PROCEDURE myDemo06
AS?
BEGIN?
?? ?-- 定义循环（循环查询语句返回的所有数据）
?? ?FOR v_ele IN (SELECT * FROM PGFSBWL_MATERIAL_PLAN) LOOP
?? ??? ?dbms_output.put_line(v_ele.ID||' - '||v_ele.MR_ID||' - '||v_ele.item_id||' - '||v_ele.item_name);
?? ?END LOOP;
END;
-- 调用存储过程
CALL myDemo06();

-- 创建一段可以执行的程序

DECLARE
?? ?-- 声明变量message，类型为varchar2，长度为20，并赋值为：Hello World !
?? ?message varchar2(20):='Hello World!';
begin?
?? ?-- 输出message的内容
?? ?dbms_output.put_line(message);
END;

--游标测试（显示游标）

DECLARE
?? ?-- 声明变量，并指定变量类型为引用类型
?? ?a PGFSBWL_MATERIAL_PLAN.id%TYPE;
?? ?b PGFSBWL_MATERIAL_PLAN.mr_id%TYPE;
?? ?/**
?? ? * 使用游标执行查询语句的时候，必须指定查询字段，不能用*代替
?? ? *?
?? ? */
?? ?CURSOR my_cursor IS?
?? ?SELECT id,MR_ID ?FROM PGFSBWL_MATERIAL_PLAN;
BEGIN?
?? ?-- 打开游标
?? ?OPEN my_cursor ;
?? ?LOOP
?? ??? ?-- 从游标中取数据时，必须按照查询的顺序和数量
?? ??? ?FETCH my_cursor INTO a, b;
?? ??? ?-- 如果游标中没有了数据就退出循环
?? ??? ?EXIT WHEN my_cursor%notfound;
?? ??? ?dbms_output.put_line(a||' ? ?'||b);
?? ?END LOOP;
?? ?-- 关闭游标
?? ?CLOSE my_cursor ;
END;

-- 测试游标的使用

DECLARE
????-- 声明变量
????id PGFSBWL_MATERIAL_PLAN.id%type;
????mrId PGFSBWL_MATERIAL_PLAN.MR_ID%type;
????itemName PGFSBWL_MATERIAL_PLAN.ITEM_NAME%type;
????-- 创建游标（指定游标接收PGFSBWL_MATERIAL_PLAN表的查询结果）
????CURSOR plan_cursor IS SELECT ID,MR_ID ,ITEM_NAME ?FROM PGFSBWL_MATERIAL_PLAN;
BEGIN
????-- 打开游标
????OPEN plan_cursor;
????-- 循环获取游标中的数据（遍历游标中的数据）
????LOOP
????????-- 获取游标中的数据，并存储到声明的变量中
????????FETCH plan_cursor INTO id,mrId,itemName;
????????-- 指定循环的退出条件（当游标中没有数据时）
????????EXIT WHEN plan_cursor%notfound;
????????dbms_output.put_line(id||' ??'||mrId||' ??'||itemName);
????END LOOP;
????-- 关闭游标
????CLOSE plan_cursor;
END;

--记录测试

DECLARE
?? ?--记录只能接收一行数据（这是基于表创建的记录）（声明记录型变量）
?? ?custom_record PGFSBWL_MATERIAL_PLAN%rowtype;
BEGIN?
?? ?SELECT * INTO custom_record?
?? ?FROM PGFSBWL_MATERIAL_PLAN
?? ?WHERE id = '8a85949681675fb701816b352b81000c';
?? ?dbms_output.put_line(custom_record.mr_id);
END;

-- 测试记录型变量的使用

DECLARE
?? ?--创建游标（游标就是临时存放查询返回的数据集）
?? ?CURSOR my_cursor IS SELECT id,mr_id ,item_id FROM PGFSBWL_MATERIAL_PLAN;
?? ?--（基于游标创建记录）
?? ?custom_record my_cursor%rowtype;
BEGIN?
?? ?/**
?? ? * 基于游标的记录，可以通过和游标配合使用来遍历数据集
?? ? */
?? ?OPEN my_cursor;
?? ?LOOP
?? ??? ?-- 获取游标中的数据到记录型变量中
?? ??? ?FETCH my_cursor INTO custom_record;
?? ??? ?-- 指定循环退出的条件
?? ??? ?EXIT WHEN my_cursor%notfound;
?? ??? ?dbms_output.put_line(custom_record.id||' ? '||custom_record.mr_id||' ? '||custom_record.item_id);
?? ?END LOOP;
-- 异常处理
EXCEPTION
?? ?WHEN OTHERS THEN
?? ??? ?dbms_output.put_line('error!');
END;

-- 触发器测试（自动补齐表格AAA中的反范式字段）

CREATE OR REPLACE TRIGGER ITEM_NAME_TRIGGER
BEFORE INSERT OR UPDATE ON AAA
FOR EACH ROW
DECLARE?
?? ?-- 声明属性
?? ?itemName varchar(100);
BEGIN?
?? ?-- 通过itemID查询物料表中对应的itemName
?? ?SELECT ITEM_NAME INTO itemName?
?? ?FROM PGFSBWL_MATERIAL_CODE?
?? ?WHERE ITEM_ID = :NEW.item_id ;
?? ?-- 设置新增数据的itemName
?? ?:NEW.item_name := itemName;
END;

-- 测试PLSQL中变量的声明和赋值

DECLARE
??? ?-- 声明时直接赋值
?? ?v_name varchar2(10) := '张三';
?? ?v_sal NUMBER;
?? ?v_addr varchar2(100);
BEGIN
?? ?-- 在程序中直接赋值
?? ?v_sal := 15000;
?? ?-- 语句赋值
?? ?SELECT '四川省成都市' INTO v_addr FROM dual;
?? ?-- 打印数据
?? ?dbms_output.put_line('姓名：'||v_name||'，工资：'||v_sal||'，地址：'||v_addr);
END;

-- 测试引用型变量（通过%type来定义变量类型）

DECLARE
?? ?-- 引用型变量是让变量直接引用数据库表的指定字段的类型。
?? ?v_itemId PGFSBWL_MATERIAL_CODE.item_id%TYPE;
?? ?v_itemName PGFSBWL_MATERIAL_CODE.item_name%TYPE;
BEGIN?
?? ?SELECT item_id,item_name INTO v_itemId,v_itemName FROM PGFSBWL_MATERIAL_CODE WHERE item_id = 'C0203914';
?? ?dbms_output.put_line(v_itemId||'?? ?'||v_itemName);
END;

-- 测试记录型变量

DECLARE
?? ?v_data PGFSBWL_MATERIAL_CODE%rowtype;
BEGIN
?? ?SELECT * INTO v_data FROM PGFSBWL_MATERIAL_CODE WHERE item_id = 'C0203914';
?? ?dbms_output.put_line(v_data.item_id||'?? ?'||v_data.item_name||'?? ?'||v_data.item_desc);
END;

-- 测试loop循环（通过if加exit退出循环）

DECLARE
?? ?-- 声明一个直接赋值变量
?? ?x NUMBER :=10;
BEGIN
?? ?LOOP
?? ??? ?dbms_output.put_line(x);
?? ??? ?x := x + 10;
?? ??? ?-- 指定循环退出条件，使用EXIT退出循环
?? ??? ?IF x > 50 THEN?
?? ??? ??? ?EXIT;
?? ??? ?END IF;
?? ?END LOOP;
END;

-- 测试loop循环（通过exit when退出循环）

DECLARE
?? ?x NUMBER :=10;
BEGIN
?? ?LOOP
?? ??? ?dbms_output.put_line(x);
?? ??? ?x:=x+1;
?? ??? ?-- 指定退出条件：当x>50的时候
?? ??? ?EXIT WHEN x>50;
?? ?END LOOP;
END;

-- 测试while loop循环

DECLARE
?? ?x NUMBER:=10;
BEGIN
?? ?-- 指定循环进行的条件
?? ?WHILE x<50 LOOP
?? ??? ?dbms_output.put_line(x);
?? ??? ?x:=x+5;
?? ?END LOOP;
END;

-- 测试for loop循环

DECLARE
?? ?x NUMBER;
BEGIN
?? ?-- 指定for循环，通过in指定for循环中变量的范围在10~20之间
?? ?FOR x IN 10 .. 20 LOOP
?? ??? ?dbms_output.put_line(x);
?? ?END LOOP;
END;

-- 测试函数（该函数是阶乘函数n! = n*(n-1)*...*1）

DECLARE
?? ?-- 声明变量
?? ?num NUMBER;
?? ?factorial NUMBER;
-- 创建函数fact(x number)
CREATE OR REPLACE FUNCTION fact(x number)
-- 指定返回数据的类型number
RETURN NUMBER
IS
?? ?-- 声明变量
?? ?f NUMBER;
BEGIN
?? ?IF x=0 THEN
?? ??? ?f:=1;
?? ?ELSE?
?? ??? ?f:=x * fact(x-1);
?? ?END IF;
RETURN f;
END;
-- 调用函数fact
DECLARE
?? ?-- 声明变量
?? ?num NUMBER;
?? ?factorial NUMBER;
BEGIN
?? ?num:=0;
?? ?factorial:=fact(num);
?? ?dbms_output.put_line(factorial);
END;

-- 测试sql的内置函数

DECLARE
?? ?-- 声明变量
?? ?total_row NUMBER;
BEGIN
?? ?-- 修改数据
?? ?UPDATE PGFSBWL_MATERIAL_PLAN?
?? ?SET APPLY_QTY = APPLY_QTY + 10
?? ?WHERE MR_ID ='141YF22080002';
?? ?-- 判断sql是否有修改数据
?? ?IF SQL%notfound THEN
?? ??? ?dbms_output.put_line('没有更新到数据');
?? ?ELSIF SQL%FOUND THEN
?? ??? ?total_row := SQL%rowcount;
?? ??? ?dbms_output.put_line('有'||total_row||'行数据被修改');
?? ?END IF;
END;

Spring-data-jpa调用oracle存储过程

/**
 * 在JPA Entity的实体类上添加注解，用以声明对应的存储过程名称和存储过程参数
 * name：声明的存储过程名称
 * procedureName：指定数据库中存储过程的名称
 * parameters：指定存储过程的参数类型（in/out）、名称和数据类型
 */
@NamedStoredProcedureQueries({
        @NamedStoredProcedureQuery(
                name = "myDemo04",
                procedureName = "myDemo04",
                parameters = {
                        @StoredProcedureParameter(type = String.class, mode = ParameterMode.IN, name = "age"),
                        @StoredProcedureParameter(type = String.class, mode = ParameterMode.OUT, name = "name")
                }
        ),
        @NamedStoredProcedureQuery(
                name = "myDemo03",
                procedureName = "myDemo03",
                parameters = {
                        @StoredProcedureParameter(type = String.class, mode = ParameterMode.IN, name = "age")
                }
        )
})




/**
* 在对应的Repository中创建方法
* 参数@Param中的名称必修和声明的入参名称一致，顺序页必须保持一致
* @Procedure中指定的名称就是申请中的name
* @param age
* @return
*/
@Procedure(name = "myDemo04")
String testInAndOut(@Param("age") String age);