【Oracle】存储过程表名称列名称做参数

转自：https://blog.csdn.net/u012881904/article/details/68948767?locationNum=12&fps=1

背景

1. 当前的系统中有几种日志信息做了分表处理，每个月插入到一个表中，一个表的分表有12个。这样的这种表有四个左右。
2. 有个定时器，每天晚上的时候去执行满足条件的表去删除日志的信息，可以配置保存日志的时间的。
3. 所以只想要通过表名称，删除时间信息，列名称。

实践

• 看上去挺简单的，调用储存过程就好了，没有想到遇到了很多的坑。挺深刻的哈哈，折腾了一天去搞定这个东西。
• 第一：表名称作为传递参数，直接在储存存储过程中进行好像不行。
• 第二：列名称也作为参数，使用动态SQL的时候怎么得到返回的结果。
• 第二：使用动态SQL的时候，传递值不能直接放在StringInfo中直接使用EXECUTE IMMEDIATE StringInfo。这样会报错的，非常坑。需要使用<=:1 这样的参数替换机制，然后使用 using xxxvalue1,xxxvalue2;
• 第四：批量删除。

代码

CREATE OR REPLACE 
PROCEDURE ALARM_LOG_CLEAR(
  table_name IN VARCHAR,//表名称
  rowname IN VARCHAR,//列名称
   actiontime IN VARCHAR//产生时间
) AS 
 MAX_ROWS NUMBER (10) ; 
 DELETE_COUNT NUMBER ; 
 selectCountStr VARCHAR2 (500) := '' ;//作为动态SQL的选择数量
 deleteStr VARCHAR2(500):='';
 startTime DATE;
BEGIN
   MAX_ROWS := 1000 ; 
   DELETE_COUNT := 0 ;
   startTime :=TO_DATE(actiontime, 'yyyy-mm-dd');

   selectCountStr := 'select count(*) from ' ||table_name || ' where '|| rowname ||' <=:1 ';  
   //使用动态SQL语法将count的值放置到DELETE_COUNT中去
   //必须using startTime，直接拼接会错误的 ||这种错误的！
   EXECUTE IMMEDIATE selectCountStr  into  DELETE_COUNT using startTime;
   COMMIT;
   --DBMS_OUTPUT.PUT_LINE(selectCountStr||'   '||DELETE_COUNT);
   deleteStr :='delete from ' || table_name || ' WHERE ' || rowname || ' <=:1    AND  ROWNUM <=:2 ';
   if DELETE_COUNT = 0 THEN return;
   end if;
   //这里就是一个for巡回的删除信息，动态SQL和这个一样的
   FOR i IN 1..TRUNC (DELETE_COUNT / MAX_ROWS) + 1 LOOP     
     EXECUTE IMMEDIATE deleteStr using startTime, MAX_ROWS; 
     COMMIT; 
    END LOOP ;
END;
 
 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

语法

EXECUTE IMMEDIATE v_sql [BULK COLLECT INTO 或INTO 返回值变量 ][INTO 入参 1,.., out 出参1,..]

 
 
1
2

说明：
1. v_sql为varchar2类型或clob（11g才支持），可以为DDL、DML等动态拼接的sql字符串。用在pl/sql代码中时，如果是varchar2类型，则长度不能大于32767(32K)。

1. v_sql为DML动态语句时，执行后不会提交，需要使用commit显式提交。如果为DDL命令,执行后则会提交所有之前改变的。

2. 如果需要从动态sql返回值，则可以定义返回值变量，BULK COLLECT INTO返回多行值，此时定义的变量需是组变量的列数表或记录表类型；INTO返回单行，此时定义的变量可以使多个pl/sql变量的列表或记录类型。

3. 如果动态sql中需要绑定变量，则使用USING，通常绑定的变量为输入入参，此时变量的in可以省略；如果需要绑定输出变量（如调用过程时可能需要输出），则在变量前用out显示指明。

4. 参考动态SQL博客

oracle 存储过程的基本语法

参考博客，orcle基本的语法
* 基本的语法

CREATE OR REPLACE PROCEDURE 存储过程名字
(
    参数1 IN NUMBER,
    参数2 IN NUMBER
) IS/AS 
变量1 INTEGER :=0;
变量2 DATE;
BEGIN
END 
 
 
1
2
3
4
5
6
7
8
9

• 绑定值,将查询到的col1和col2的值放到这里面去

 SELECT col1,col2 into 变量1,变量2 FROM typestruct where xxx;
 
 
1

• 判断 这里的不是赋值哦!!!

 IF V_TEST=1 THEN
    BEGIN 
       do something
    END;
  END IF;
 
 
1
2
3
4
5

• 赋值

V_TEST := 123;
 
 
1

Java调用储存过程

public int executeUpdate(Connection conn, String tableName, String columeName, String actionTime){
    CallableStatement cs = null;
    try {
      cs = conn.prepareCall("{call ALARM_LOG_CLEAR(?,?,?)}");
      cs.setString(1, tableName);
      cs.setString(2, columeName);
      cs.setString(3, actionTime);
      cs.execute();
      return 1;
    } catch (SQLException e) {
      return 0;    
    } finally {
     //关闭资源
    }
    return 0;
  }
 
 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

        <link href="https://csdnimg.cn/release/phoenix/template/css/markdown_views-ea0013b516.css" rel="stylesheet">
            </div>