Oracle存储过程表名称列名称做参数，动态sql

背景

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;

语法

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

说明：
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

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

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

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

IF V_TEST=1 THEN
    BEGIN do something END;
  END IF;

• 赋值

V_TEST := 123;

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;
  }