Oracle存储过程实现执行动态SQL

news/2024/7/5 1:47:57

Oracle存储过程中执行动态SQL

最近在工作中使用到了存储过程，要用存储过程执行动态的SQL，于是便有了这篇文章。

思考

既然是动态的SQL，最好有个table用来存储这些动态SQL，Oracle中这么多数据类型该选择哪种呢？首先想到的肯定是 varchar2 ，但其实这个数据类型有4000字节的最大长度限制，当动态SQL超过这个字节时是没法存储的，这里推荐使用 clob ，这个数据类型可以存储最大4G的数据，另外其实还有一个原因，这里留个悬念。OK，动手。

建表

动态SQL的数据类型选择好了，字段也很简单：表名、动态SQL、是否使用标记，SQL如下：

create table t_dim_dynamic_sql_st(
    table_name     varchar2(100),
    dynamic_sql    clob,
    use_flag       integer
);
comment on table t_dim_dynamic_sql_st is '维表_动态SQL存储_静态表';
comment on column t_dim_dynamic_sql_st.table_name is '表名';
comment on column t_dim_dynamic_sql_st.dynamic_sql is '动态SQL';
comment on column t_dim_dynamic_sql_st.use_flag is '是否使用标记';

然后建个测试表，并在动态SQL存储维表中写入一条数据。

create table t_test(
    test_column   varchar2(50)
);
comment on table t_test is '表_测试表';
comment on column t_test.test_column is '测试字段';

--动态SQL维表写入数据
insert into t_dim_dynamic_sql_st
select 
't_test' as table_name,
'insert into t_test select ''test_data'' from dual' as dynamic_sql,
1 from dual;

动态SQL执行存储过程

准备工作结束，开始写存储过程。这边已经构建了动态SQL存储维表，那么存储过程最好是可以输入表名，所以得有表名参数。然后这里说明一下，之前将动态SQL的数据类型选择为 clob 是因为存储过程中执行动态SQL得用到关键字 execute ，这个对数据类型是有限制的，比如同样可以存储4G的 long 就无法被执行。存储过程如下：

create or replace procedure p_exec_dynamic_sql(in_table_name varchar2,
                                              in_data varchar2)
is
v_sql clob;
begin

--查询动态SQL
select dynamic_sql into v_sql
from t_dim_dynamic_sql_st
where upper(table_name)=upper(in_table_name) and use_flag=1;

--替换数据
v_sql:=replace(v_sql,'test_data',in_data);

--执行，这里使用exception做了异常捕获，避免SQL执行报错，在维表中添加SQL，最好先自测下动态SQL是否可以正常运行。
begin
    execute immediate v_sql;
    commit;
exception
    when others then null;
end;

end;