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

版权声明:本文为博主原创文章,遵循 CC 4.0 by-sa 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/u012881904/article/details/68948767

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;
  }
展开阅读全文

动态SQL列名使用变量?谢谢

11-14

CREATE procedure t_update -- 通用更新存储过程rn(rn@TableName [varchar](50), -- 表名rn@strSet [varchar](200) , -- 更新语句,不要加setrn@strWhere [varchar](200) = '' -- 更新条件 (注意: 不要加 where)rn)rnasrndeclare @tiaojian [varchar](300)rndeclare @sql [varchar](300)rnrnif @strWhere='' --构造条件语句rn beginrn set @tiaojian=''rn end rnelsern beginrn set @tiaojian=' where '+@strWherern endrnrnset @sql='update ['+@TableName+'] set '+@strSet+@tiaojianrnexec(@sql)rnrn在一个投票应用中用到该过程,如果列名用变量的话,必定报错“某某列不存在”。rn如果列名不用变量就不会报错,但是又增加很多繁琐的代码。rnC#代码:rn//写一个投票方法,用来处理投票rnprotected void toupiao(string column, int jifen) //前参数为投票项对应的列,后参数为对应的积分rnrn SqlConnection conn = new SqlConnection(connStr);rn SqlCommand cmd = new SqlCommand();rn cmd.Connection = conn;rn cmd.CommandType = CommandType.StoredProcedure;rn cmd.CommandText = "t_update";rn cmd.Parameters.Add(new SqlParameter("@TableName", MyTable));rn cmd.Parameters.Add(new SqlParameter("@strSet", "['"+column+"']=['"+column+"']+'"+jifen+"'"));rn cmd.Parameters.Add(new SqlParameter("@strWhere", "[id]='"+id+"'"));rn conn.Open();rn int count = cmd.ExecuteNonQuery();rn conn.Close();rn conn.Dispose();rn if(count>0)rn rn //输出投票成功提示rn rnrnrnrn请大侠们帮我改一下这个存储过程,让列名能够使用变量,谢谢!rn 论坛

oracle存储过程动态SQL Insert Into 的问题

03-23

对于varchar类型数据的插入,一般的Insert Into只需要:rnINSERT INTO TBNAME (VARC1,VARC2) VALUES ('s1', 's2')rn就可以了,但是动态SQL中应该怎么操作,我的存储过程是这样的:rnrnrnCREATE or replace procedure proInsertCustInfo(rn v_Names in VARCHAR2,rn v_Values in VARCHAR2) ISrn v_Cursor NUMBER;rn v_CreateString VARCHAR2(500);rn vID number;rnbeginrn v_Cursor := DBMS_SQL.OPEN_CURSOR;rn v_CreateString := 'INSERT INTO CSCUSTOM ( CUSTID, CUSTSTATDATE, ' || v_Names || ')' || ' VALUES (custom_id_seq.nextval, sysdate, ' || v_Values || ')';rn DBMS_SQL.PARSE(v_Cursor, v_CreateString, DBMS_SQL.NATIVE);rn DBMS_SQL.CLOSE_CURSOR(v_Cursor);rnrn commit work;rnend proInsertCustInfo;rnrn我现在需要在java中调用,rnrn附java代码:rnrnClass.forName("oracle.jdbc.driver.OracleDriver"); rn conn = DriverManager.getConnection ("jdbc:oracle:thin:@172.18.8.4:1521:ORCL", "scott","tiger"); rn CallableStatement procnone = conn.prepareCall ("begin proInsertCustInfo(?,?,?); end;"); rn procnone.setString(1, m_Names);rn procnone.setString(2, m_Values);rn rn procnone.execute();rnrnrn上面的存储过程是正确的,我将我的Java代码中的m_Names和m_Values直接copy到sql*plus work sheet中执行insert into操作完全正确,在sql*plus中执行存储过程也没有问题,但是无法插入数据,现在的问题据我看是在v_Values上面。rn假设我要传 3个参数(全是varchar2型):值分别是:hello, you, arern正常的sql是insert into CSCUSTOM (a,b,c) VALUES ('HELLO' ,'you', 'are')rn在上面的代码中应该如何做呢,就是v_Values在java中应该如何赋值呢。rn 论坛

没有更多推荐了,返回首页