Oracle存储过程汇总

最新推荐文章于 2025-06-17 14:30:00 发布

原创

最新推荐文章于 2025-06-17 14:30:00 发布 · 2.5k 阅读

31 ·

CC 4.0 BY-SA版权

文章标签：

#oracle #package #trigger #cursor

本文详细介绍了Oracle存储过程的使用，包括创建包、动态SQL语句和触发器的实践。首先，通过一个实际需求展示了如何利用包来组织多个存储过程，以解决复杂问题。接着，解释了动态SQL在创建临时表和处理DDL语句时的作用，以及为何在存储过程中使用动态SQL。最后，讨论了触发器的基本结构和用法，以及如何在触发器中调用存储过程。文章还提到了游标、变量类型定义以及事务级临时表的概念。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

开场来一发本次举例的脚本：

--创建mq表
create table t_commodity_frozen_test(id int primary key,commodity_id int,province varchar2(20),create_time date,status integer default 0);
---------------------------------------------------------------------------------------
/*--测试数据
select * from t_commodity_frozen_test;
delete from t_commodity_frozen_test where id =2;
insert into t_commodity_frozen_test values(1,123211,'anhui',sysdate,0);
drop table t_temp_frozen
*/
---------------------------------------------------------------------------------------
--创建包头
create or replace package commodity_frozen_test is
procedure tempfrozen;
procedure commodityfrozen
end commodity_frozen_test;

-----------------------------------------------------------------------------------------
--procedure1 创建临时表

create or replace package body commodity_frozen_test is
procedure tempfrozen is
begin
  execute immediate
  'create global temporary
  table t_temp_frozen(ext1 number(10),ext2 varchar2(100))
on commit preserve rows';
end tempfrozen;

------------------------------------------------------------------------------------------
--procedure2 执行体
procedure commodityfrozen is
V_commoditid t_commodity_info.commodity_id%type;
V_provinceid t_config.configvalue%type;
V_exchangecodeid t_commodity_info.exchangescopeid%type;
V_merchantid t_merchant_expressscope.merchantid%type;
V_provincename t_config.configname%type;

--定义游标范围
cursor  C_frozen is
select t.commodity_id,t.province from t_commodity_frozen_test t
where t.status=0
and t.create_time > trunc(sysdate-1);

-- 定义商品范围
  begin
  open C_frozen;
  loop fetch  C_frozen into V_commoditid,V_provincename;
    exit when  C_frozen%notfound;
-- 获取要冻结省份value
    select g.configvalue
      into V_provinceid
      from t_config g
     where g.configname = V_provincename and g.configtype=4;

-- 判断商品还是商户
select fo.exchangescopeid
  into V_exchangecodeid
  from t_commodity_info fo
 where fo.commodity_id = V_commoditid;

if V_exchangecodeid is null
  then

    select me.merchantid
      into V_merchantid
      from t_merchant_expressscope me
      join t_commodity_info fo
        on me.merchantid = fo.business_id
     where fo.commodity_id = V_commoditid;
-- 判断商户是单省商品还是多省商品（单省直接删掉商户区域配置）
if V_merchantid is null then
     delete from (select me.provincecode
                    from t_merchant_expressscope me
                    join t_merchant_info mi
                      on me.merchantname = mi.merchant_loginname
                    join t_commodity_info fo
                      on mi.id = fo.