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

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

• 绑定值,将查询到的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;  }