Java执行SQL代码块，得到dbms_output.put_line的结果

在Java中执行一段SQL的代码段如下：

declarevar_user_no varchar2(50);begin select userName into var_user_no from user where user_id='1234'; dbms_output.put_line(var_user_no); end;

的时候因为不是存储过程所以没有什么入参和出参

 

我参考的是http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:45027262935845

帖子的内容，这里贴一下用作留存

 

<quote>Teaching other environments about DBMS_OUTPUTBy default, tools such as SQL*PLUS and SVRMGRL are DBMS_OUTPUT aware. Most other environments are not. For example, your Java/JDBC program is definitely not DBMS_OUTPUT aware. We'll see in this section how to make Java/JDBC DBMS_OUTPUT aware. The same principles used below apply equally to any programming environment. The methods I use with Java can be easily applied to Pro*C, OCI, VB or any number of programmatic environments.We'll start with a small PL/SQL routine that generates some output data:scott@TKYTE816> create or replace 2 procedure emp_report 3 as 4 begin 5 dbms_output.put_line 6 ( rpad( 'Empno', 7 ) || 7 rpad('Ename',12) || 8 rpad('Job',11) ); 9 10 dbms_output.put_line 11 ( rpad( '-', 5, '-' ) || 12 rpad(' -',12,'-') || 13 rpad(' -',11,'-') ); 14 15 for x in ( select * from emp ) 16 loop 17 dbms_output.put_line 18 ( to_char( x.empno, '9999' ) || ' ' || 19 rpad( x.ename, 12 ) || 20 rpad( x.job, 11 ) ); 21 end loop; 22 end; 23 /Procedure created.scott@TKYTE816> set serveroutput on format wrappedscott@TKYTE816> exec emp_reportEmpno Ename Job----- ---------- --------- 7369 SMITH CLERK 7499 ALLEN SALESMAN … 7934 MILLER CLERKPL/SQL procedure successfully completed.Now, we'll set up a class to allow Java/JDBC to easily perform DBMS_OUTPUT for us.import java.sql.*;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 void show() throws SQLException{int done = 0; show_stmt.registerOutParameter( 2, java.sql.Types.INTEGER ); show_stmt.registerOutParameter( 3, java.sql.Types.VARCHAR ); for(;;) { show_stmt.setInt( 1, 32000 ); show_stmt.executeUpdate(); System.out.print( show_stmt.getString(3) ); if ( (done = show_stmt.getInt(2)) == 1 ) break; }}/* * 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();}}In order to demonstrate its use, I've set up the following small Java/JDBC test program. Here dbserver is the name of the database server and ora8i is the service name of the instance: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"); conn.setAutoCommit (false); Statement stmt = conn.createStatement(); DbmsOutput dbmsOutput = new DbmsOutput( conn ); dbmsOutput.enable( 1000000 ); stmt.execute ( "begin emp_report; end;" ); stmt.close(); dbmsOutput.show(); dbmsOutput.close(); conn.close();} }Now we will test it, by first compiling it and then running it:$ javac test.java$ java testEmpno Ename Job ----- ---------- --------- 7369 SMITH CLERK 7499 ALLEN SALESMAN 7521 WARD SALESMAN……So, that shows how to teach Java to do DBMS_OUTPUT for us. Just as SQL*PLUS does, you'll have to call DbmsOutput.show() after executing any statement that might procedure some output to be displayed. After we execute an insert, update, delete or stored procedure call – SQL*PLUS is calling DBMS_OUTPUT.GET_LINES to get the output. Your Java (or C, or VB) application would call 'show' to display the results.

 

而实际时用的时候只需要将 main方法中的

 

stmt.execute ( "begin emp_report; end;" );

里面的SQL替换成你需要执行的SQL即可

 

 

而实际输出的时候是按照一句dbms_output.put_line一行输出来输出的。