show_stmt = conn.prepareCall("declare " + " l_line varchar2(255); "
+ " l_done number; " + " l_buffer long; " + "begin "
+ " loop "
+ " exit when length(l_buffer)+255 > :1 OR l_done = 1; "
+ " dbms_output.get_line( l_line, l_done ); "
+ " l_buffer := l_buffer || l_line || chr(10); " //循环加上 相当于+=
+ " end loop; " + " :2 := l_done; " + " :buffer := l_buffer; "
+ "end;");上面是java 调用过程代码我提取出的过程是:DECLARE
l_line VARCHAR2(255);
l_done number;
l_buffer long;
begin
LOOP
exit when length(l_buffer)+255 >:1 OR l_done = 1;
dbms_output.get_line( l_line, l_done );
l_buffer := l_buffer || l_line || chr(10);
end loop;
:2 := l_done;
:buffer := l_buffer;
end;我有几个问题:
1: l_buffer 是long 类型 length(l_buffer )是什么意思2 :l_buffer 是 long 类型 怎么可以把字符串赋值给它 l_buffer := l_buffer || l_line || chr(10);3: :buffer := l_buffer;是什么意思,它为什么会是第三个输出参数:buffer := 这种语法是什么
3,4。:buffer表示一个输入参数,因为前面加了:,这里的buffer可以随便写成什么,只要是个字符串即可。
:buffer := 'aa'表示将字符串'aa'赋值给变量:buffer,:=是oracle的PL/SQL的赋值。
答:length(l_buffer)是求变量l_buffer的长度。2 :l_buffer 是 long 类型 怎么可以把字符串赋值给它 l_buffer := l_buffer || l_line || chr(10);
答:此处的long非java中的long,此处的long是oracle中的long是“LONG 数据类型中存储的是可变长字符串,最大长度限制是2GB“3: :buffer := l_buffer;是什么意思,它为什么会是第三个输出参数 答:把l_buffer的值赋给绑定变量buffer.
:buffer := 这种语法是什么
答:‘:buffer’是绑定变量。‘:=’是oracle的pl/sql的赋值运算符,把该符号左变的值赋给右边的变量。
??????
我说错了,其实应该像2楼说的那样,带:表示一个绑定变量。我把你这个当存储过程来解释了,sorry
:buffer := 这种语法是什么
答:‘:buffer’是绑定变量。‘:=’是oracle的pl/sql的赋值运算符,把该符号右边的值赋给左边的变量。
这个明白了!但:buffer是输出变量还是输入变量呢
这个匿名过程,怎么区分输入还是输出变量
show_stmt.registerOutParameter(3, java.sql.Types.VARCHAR);后面的java 代码里都没有赋值给绑定变量:1
那么它:1会取什么值
import java.sql.CallableStatement;
import java.sql.SQLException;
import java.sql.Connection;
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 > :1 OR l_done = 1; " +
" dbms_output.get_line( l_line, l_done ); " +
" l_buffer := l_buffer || l_line || chr(10); " +
" end loop; " +
" :2 := 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();
}
}网上一段代码 ,具体出处,已不表楚!
for(;;)
{
show_stmt.setInt( 1, 32000 );
show_stmt.executeUpdate();
System.out.print( show_stmt.getString(3) );
if ( (done = show_stmt.getInt(2)) == 1 ) break;
}
}
:buffer和:1,:2应该是接受从java程序中参入的对应的参数值,然后修改完对应参数的值后,java程序可以得到修改后的值(至于怎么得到,本人没有做过这方面的实验),这些只是我的猜测。
E:\oracle\ora92\jdbc\demo\demo.zip
里面很全的,N多例子,哈哈。
另外,你还可以了解下
oracle 官方文档 jdbc
http://www.oracle.com/technology/docs/tech/java/sqlj_jdbc/doc_library/javadoc/tree.html
现在还有最后一个不清楚
show_stmt.registerOutParameter( 3, java.sql.Types.VARCHAR );
这个三输出参数是哪来
难道:buffer := l_buffer;这样就设置了??