JAVA获取DBMS_SQL输出语句（详解）

╰半夏微凉° 2022-05-10 14:03 184阅读 0赞

## JAVA获取DBMS\_SQL输出语句（详解） ##

最近项目中的一个功能模块是通过DBMS_SQL对SQL语句进行解析，然后输出语句对应的字段和字段类型，但需要在JAVA中获取到DBMS_SQL输出的语句。

# **DBMS_SQL的语句如下：**
    
    DECLare
      li_curid number;
      lvs_str  varchar(2000);
      ln_cnt  NUMBER;
      ltb_desctab dbms_sql.desc_tab;
    begin
      --select to_char (max(tstp_dm)+1) as tstp_dm from wxqyh_xtsz_tstp
      --lvs_str  := 'select * from wxqyh_fxss';
      lvs_str  := 'select tstp_mc,tstplj from wxqyh_xtsz_tstp  where tstp_dm in (select max(tstp_dm) from wxqyh_xtsz_tstp)';
      li_curid := dbms_sql.open_cursor();
      dbms_sql.parse(li_curid, lvs_str, dbms_sql.native);
      dbms_sql.describe_columns(li_curid, ln_cnt, ltb_desctab);
      FOR i IN 1 ..ltb_desctab.count LOOP
        dbms_output.put_line(rpad(ltb_desctab(i).col_name, 30)||rpad(ltb_desctab(i).col_type, 3));
      end LOOP;
      dbms_sql.close_cursor(li_curid);
    end;

输出如下  
![在这里插入图片描述][70]

如果我们想要在JAVA代码中获取到DBMS\_SQ的输出语句的话，需要用到CallableStatement接口。  
先封装一个DbmsOutput类，不用管里面的代码，我也是在网上找的资料就按照以下进行复制粘贴使用即可

package com.difu.framework.util;
    
    import java.sql.Array;
    import java.sql.CallableStatement;
    import java.sql.Connection;
    import java.sql.SQLException;
    import java.util.ArrayList;
    import java.util.Arrays;
    import java.util.HashMap;
    import java.util.List;
    import java.util.Map;
    /**
     * 获取DBMS_SQL输出的内容
     * @author 24576
     *
     */
    public class DbmsOutput {
    
    	/*
    	 * our instance variables. It is always best to 
    	 * use callable or prepared statements and prepare (parse)
    	 * them once per program execution, rather then one per 
    	 * execution in the program.  The cost of reparsing is 
    	 * very high.  Also -- make sure to use BIND VARIABLES!
    	 *
    	 * we use three statments in this class. One to enable 
    	 * dbms_output - equivalent to SET SERVEROUTPUT on in SQL*PLUS.
    	 * another to disable it -- like SET SERVEROUTPUT OFF.
    	 * the last is to "dump" or display the results from dbms_output
    	 * using system.out
    	 *
    	 */
    	private CallableStatement enable_stmt;
    	private CallableStatement disable_stmt;
    	private CallableStatement show_stmt;
    
    
    	/* 
    	 * our constructor simply prepares the three
    	 * statements we plan on executing. 
    	 *
    	 * the statement we prepare for SHOW is a block of 
    	 * code to return a String of dbms_output output.  Normally, 
    	 * you might bind to a PLSQL table type but the jdbc drivers
    	 * don't support PLSQL table types -- hence we get the output
    	 * and concatenate it into a string.  We will retrieve at least
    	 * one line of output -- so we may exceed your MAXBYTES parameter
    	 * below. If you set MAXBYTES to 10 and the first line is 100 
    	 * bytes long, you will get the 100 bytes.  MAXBYTES will stop us
    	 * from getting yet another line but it will not chunk up a line.
    	 *
    	 */
    	public DbmsOutput( Connection conn ) throws SQLException
    	{
    	    enable_stmt  = conn.prepareCall( "begin dbms_output.enable(:1); end;" );
    	    disable_stmt = conn.prepareCall( "begin dbms_output.disable; end;" );
    
    	    show_stmt = conn.prepareCall( 
    	          "declare " +
    	          "    l_line varchar2(255); " +
    	          "    l_done number; " +
    	          "    l_buffer long; " +
    	          "begin " +
    	          "  loop " +
    	          "    exit when length(l_buffer)+255 > :maxbytes OR l_done = 1; " +
    	          "    dbms_output.get_line( l_line, l_done ); " +
    	          "    l_buffer := l_buffer || l_line || chr(10); " +
    	          "  end loop; " +
    	          " :done := l_done; " +
    	          " :buffer := l_buffer; " +
    	          "end;" );
    	}
    
    	/*
    	 * enable simply sets your size and executes
    	 * the dbms_output.enable call
    	 *
    	 */
    	public void enable( int size ) throws SQLException
    	{
    	    enable_stmt.setInt( 1, size );
    	    enable_stmt.executeUpdate();
    	}
    
    	/*
    	 * disable only has to execute the dbms_output.disable call
    	 */
    	public void disable() throws SQLException
    	{
    	    disable_stmt.executeUpdate();
    	}
    
    	/*
    	 * show does most of the work.  It loops over
    	 * all of the dbms_output data, fetching it in this
    	 * case 32,000 bytes at a time (give or take 255 bytes).
    	 * It will print this output on stdout by default (just
    	 * reset what System.out is to change or redirect this 
    	 * output).
    	 */
    
    	public String[] show() throws SQLException
    	{
    	int               done = 0;
    
    	    show_stmt.registerOutParameter( 2, java.sql.Types.INTEGER );
    	    show_stmt.registerOutParameter( 3, java.sql.Types.VARCHAR );
    	    List list=new ArrayList();
    	    Map<String, String> map=new HashMap<String, String>();
    	    String[] split;
    	    for(;;)
    	    {    
    	        show_stmt.setInt( 1, 32000 );
    	        show_stmt.executeUpdate();
    	   
    	        String string = show_stmt.getString(3);
    	        split = string.split("\n");
    	        System.out.println("split"+Arrays.toString(split));
    	        if ( (done = show_stmt.getInt(2)) == 1 ) break;
    	       
    	    }
    	    return split;
    	}
    
    	/* 
    	 * close closes the callable statements associated with
    	 * the DbmsOutput class. Call this if you allocate a DbmsOutput
    	 * statement on the stack and it is going to go out of scope -- 
    	 * just as you would with any callable statement, result set
    	 * and so on.
    	 */
    	public void close() throws SQLException
    	{
    	    enable_stmt.close();
    	    disable_stmt.close();
    	    show_stmt.close();
    	}
    }

编写测试类代码如下：

import java.sql.*;  
       
    class test {  
    public static void main (String args [])   
       throws SQLException   
    {  
        DriverManager.registerDriver   
          (new oracle.jdbc.driver.OracleDriver());  
       
        Connection conn = DriverManager.getConnection  
             ("jdbc:oracle:thin:@dbserver:1521:ora8i",   
              "scott", "tiger");  //这里输入自己的URL，username,pwd
        conn.setAutoCommit (false);   
        Statement stmt = conn.createStatement();  
        DbmsOutput dbmsOutput = new DbmsOutput( conn );  
        dbmsOutput.enable( 1000000 );  
        stmt.execute  
        ( "begin emp_report; end;" );  
        stmt.close();  
       String[] strings = dbmsOutput.show();
    	System.out.println("stringspit" + Arrays.toString(strings));
        dbmsOutput.close();  
        conn.close();  
    }  
       
    }

[70]: /images/20220505/e85bda0f1cfc4b27a63feb9fd1e361a9.png