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;
输出如下
如果我们想要在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();
}
}