请问如何取得dbms_output.put_line的结果?举个存储过程的例子?

unm 2010-06-28 02:30:03
其实我是传入一个拼了的sql,然后想返回一个字符串,老是不通过啊

如果能一个sql执行完也可以,我查了一下好像不行,得用存储过程,是这样吗?
目的就是想把刚插入的id用一个字串取回,或者还有更好的方法吗?

declare rtndata nvarchar2(2000);mKeyID NUMBER;
begin
insert into t_vehicle_kind (id,code,name,org_id) values (S_vehicle_kind.NEXTVAL,'dd@g.com','singletab','0');
select S_vehicle_kind.currval into mKeyID from dual;
rtndata:=rtndata||',{comp_119:'||mKeyID||'}';
insert into t_vehicle_kind (id,code,name,org_id) values (S_vehicle_kind.NEXTVAL,'dd@g.com','singletab','0');
select S_vehicle_kind.currval into mKeyID from dual;
rtndata:=rtndata||',{comp_121:'||mKeyID||'}';
insert into t_vehicle_kind (id,code,name,org_id) values (S_vehicle_kind.NEXTVAL,'dd@g.com','singletab','0');
select S_vehicle_kind.currval into mKeyID from dual;
rtndata:=rtndata||',{comp_122:'||mKeyID||'}';
dbms_output.put_line(rtndata);
end;
...全文
1201 点赞 收藏 27
写回复
27 条回复
切换为时间正序
请发表友善的回复…
发表回复
shakusi 2011-09-14
能不能不用存储过程呢?
像SQL SERVER 那样直接
select rtndata;
回复
悠忧虫 2010-10-13
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:45027262935845

测试用到的存储过程

create or replace procedure emp_report
as
begin
dbms_output.put_line
( rpad( 'Empno', ) ||
rpad('Ename',) ||
rpad('Job',) );

dbms_output.put_line
( rpad( '-',, '-' ) ||
rpad(' -',,'-') ||
rpad(' -',,'-') );

for x in ( select * from emp )
loop
dbms_output.put_line
( to_char( x.empno, '' ) || ' ' ||
rpad( x.ename, ) ||
rpad( x.job, ) );
end loop;
end;





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();
}
}




测试类:



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();
}

}





回复
qldsrx 2010-07-04
显然你这个proc_getdata存储过程只能传递一个最简单的sql查询,也就是select语句,无法传递一个过程执行,因为过程是不能用into来输出的。
回复
forgetsam 2010-07-01
EXECUTE IMMEDIATE exesql into r_data 是把这个语句执行的结果存入r_data ,这个语句只能返回一行一列,你语句里写的什么?

另外你到底是想干啥啊?

如果你的表固定了,
insert into 表 values(....) returing 主键 into 你的输出参数 就完事了
回复
unm 2010-07-01
[Quote=引用 1 楼 xiaohu8855 的回复:]

你这个没问题啊,预期结果是不是这样的?
,{comp_119:1},{comp_121:2},{comp_122:3}
[/Quote]

是啊,预期的结果是这样的,说明这个sql是没有问题的
------------------------------------------------------------------------------
declare rtndata nvarchar2(2000);mKeyID NUMBER;
begin
insert into t_vehicle_kind (id,code,name,org_id) values (S_vehicle_kind.NEXTVAL,'dd@g.com','singletab','0');
select S_vehicle_kind.currval into mKeyID from dual;
rtndata:=rtndata||',{comp_119:'||mKeyID||'}';
insert into t_vehicle_kind (id,code,name,org_id) values (S_vehicle_kind.NEXTVAL,'dd@g.com','singletab','0');
select S_vehicle_kind.currval into mKeyID from dual;
rtndata:=rtndata||',{comp_121:'||mKeyID||'}';
insert into t_vehicle_kind (id,code,name,org_id) values (S_vehicle_kind.NEXTVAL,'dd@g.com','singletab','0');
select S_vehicle_kind.currval into mKeyID from dual;
rtndata:=rtndata||',{comp_122:'||mKeyID||'}';
dbms_output.put_line(rtndata);
select rtndata into rtndata from dual;
end;
------------------------------------------------------------------------------
当我把这个sql代入存储过程的时候
create or replace procedure proc_getdata(exesql in VARCHAR2,r_data out varchar2) AS
BEGIN
EXECUTE IMMEDIATE exesql into r_data;
END;
想返回一个值,可是java代码就是提示
java.sql.SQLException: ORA-01007: 选择列表中没有变量
ORA-06512: 在"RECODE_INIT.PROC_GETDATA", line 4
ORA-06512: 在line 1

难道是哪里还是oracle有地雷?不容许这样的sql?plsql只执行了啊,没有报错
java执行就有问题了,怎么测试存储过程呢?
用cs.execute()也是这个错误提示的。非常非常想解决掉。。。
回复
xiaohu8855 2010-06-30
那就是过程是错误的,你可以先在plsql下测试下,如果正常的情况下并且参数无误的话是不会有问题的,这是一个很简单的问题,楼主仔细检查检查就可以解决了
回复
unm 2010-06-29
哦,那是我写错的,就算正确的,也还是有问题的
在plsql中传入的sql都执行的正常,但是在java运行时就会错误

java里拼了sql,传入存储过程的sql是这样的
------------------------------------------------------------------------------
declare rtndata nvarchar2(2000);mKeyID NUMBER;
begin
insert into t_vehicle_kind (id,code,name,org_id) values (S_vehicle_kind.NEXTVAL,'dd@g.com','singletab','0');
select S_vehicle_kind.currval into mKeyID from dual;
rtndata:=rtndata||',{comp_119:'||mKeyID||'}';
insert into t_vehicle_kind (id,code,name,org_id) values (S_vehicle_kind.NEXTVAL,'dd@g.com','singletab','0');
select S_vehicle_kind.currval into mKeyID from dual;
rtndata:=rtndata||',{comp_121:'||mKeyID||'}';
insert into t_vehicle_kind (id,code,name,org_id) values (S_vehicle_kind.NEXTVAL,'dd@g.com','singletab','0');
select S_vehicle_kind.currval into mKeyID from dual;
rtndata:=rtndata||',{comp_122:'||mKeyID||'}';
dbms_output.put_line(rtndata);
select rtndata into rtndata from dual; -------不知这样写对不对?
end;
------------------------------------------------------------------------------
存储过程是这样的(参数一个in,一个out),不知这样返回是否正确?
create or replace procedure proc_getdata(exesql in VARCHAR2,r_data out varchar2) AS
BEGIN
EXECUTE IMMEDIATE exesql into r_data;
END;
------------------------------------------------------------------------------
java代码是这样的
cs = conne.prepareCall("{call proc_getdata(?,?)}");---此处已经改为正确的存储过程名
cs.setString(1, sql);
cs.registerOutParameter(2, oracle.jdbc.OracleTypes.VARCHAR);
cs.executeQuery();
String return_data = cs.getString(1);
return return_data;
------------------------------------------------------------------------------
java提示错误是这样的
java.sql.SQLException: ORA-01007: 选择列表中没有变量
ORA-06512: 在"RECODE_INIT.PROC_GETDATA", line 4
ORA-06512: 在line 1


我查的有些回复是说列数不一致?什么意思?
回复
qinfei008 2010-06-29
存储过程的调用是cs.execute()而不是cs.executeQuery()
回复
inthirties 2010-06-29
用out 参数
回复
xiaohu8855 2010-06-29
楼主,你这个肯定是错的嘛。
java提示错误是这样的
java.sql.SQLException: ORA-06550: 第 1 行, 第 7 列:
PLS-00905: 对象 RECODE_INIT.RETURN_SQL 无效
ORA-06550: 第 1 行, 第 7 列:
PL/SQL: Statement ignored
从PLS-00905: 对象 RECODE_INIT.RETURN_SQL 无效可以看出过程名不存在或失效。
在看下java代码
cs = conne.prepareCall("{call return_sql(?,?)}");
cs.setString(1, sql);
cs.registerOutParameter(2, oracle.jdbc.OracleTypes.VARCHAR);
cs.executeQuery();
String return_data = cs.getString(1);
return return_data;
而call 后面是过程名,而你的过程名是proc_getdata,所以错误了
回复
xiaohu8855 2010-06-29
楼主,你这个肯定是错的嘛。
java提示错误是这样的
java.sql.SQLException: ORA-06550: 第 1 行, 第 7 列:
PLS-00905: 对象 RECODE_INIT.RETURN_SQL 无效
ORA-06550: 第 1 行, 第 7 列:
PL/SQL: Statement ignored
从PLS-00905: 对象 RECODE_INIT.RETURN_SQL 无效可以看出过程名不存在或失效。
在看下java代码
cs = conne.prepareCall("{call return_sql(?,?)}");
cs.setString(1, sql);
cs.registerOutParameter(2, oracle.jdbc.OracleTypes.VARCHAR);
cs.executeQuery();
String return_data = cs.getString(1);
return return_data;
而call 后面是过程名,而你的过程名是proc_getdata,所以错误了
回复
unm 2010-06-29
[Quote=引用 19 楼 xiaohu8855 的回复:]

java代码是这样的
cs = conne.prepareCall("{call proc_getdata(?,?)}");---此处已经改为正确的存储过程名
cs.setString(1, sql);
cs.registerOutParameter(2, oracle.jdbc.OracleTypes.VARCHAR);
cs.executeQuery();
String return_data = cs.getString(1);
return return_data;

这句话不对
String return_data = cs.getString(1);
应该是
String return_data = cs.getString(2);
[/Quote]

现在是执行到 cs.executeQuery(); 就出现这个错误了。。。。。
这个问题这么郁闷。。。。。
回复
xiaohu8855 2010-06-29
java代码是这样的
cs = conne.prepareCall("{call proc_getdata(?,?)}");---此处已经改为正确的存储过程名
cs.setString(1, sql);
cs.registerOutParameter(2, oracle.jdbc.OracleTypes.VARCHAR);
cs.executeQuery();
String return_data = cs.getString(1);
return return_data;

这句话不对
String return_data = cs.getString(1);
应该是
String return_data = cs.getString(2);





回复
unm 2010-06-28
传入sql是这样的
------------------------------------------------------------------------------
declare rtndata nvarchar2(2000);mKeyID NUMBER;
begin
insert into t_vehicle_kind (id,code,name,org_id) values (S_vehicle_kind.NEXTVAL,'dd@g.com','singletab','0');
select S_vehicle_kind.currval into mKeyID from dual;
rtndata:=rtndata||',{comp_119:'||mKeyID||'}';
insert into t_vehicle_kind (id,code,name,org_id) values (S_vehicle_kind.NEXTVAL,'dd@g.com','singletab','0');
select S_vehicle_kind.currval into mKeyID from dual;
rtndata:=rtndata||',{comp_121:'||mKeyID||'}';
insert into t_vehicle_kind (id,code,name,org_id) values (S_vehicle_kind.NEXTVAL,'dd@g.com','singletab','0');
select S_vehicle_kind.currval into mKeyID from dual;
rtndata:=rtndata||',{comp_122:'||mKeyID||'}';
dbms_output.put_line(rtndata);
select rtndata into rtndata from dual;
end;
------------------------------------------------------------------------------
存储过程是这样的(参数一个in,一个out)
create or replace procedure proc_getdata(exesql in VARCHAR2,r_data out varchar2) AS
BEGIN
EXECUTE IMMEDIATE exesql into r_data;
END;
------------------------------------------------------------------------------
java代码是这样的
cs = conne.prepareCall("{call return_sql(?,?)}");
cs.setString(1, sql);
cs.registerOutParameter(2, oracle.jdbc.OracleTypes.VARCHAR);
cs.executeQuery();
String return_data = cs.getString(1);
return return_data;
------------------------------------------------------------------------------
java提示错误是这样的
java.sql.SQLException: ORA-06550: 第 1 行, 第 7 列:
PLS-00905: 对象 RECODE_INIT.RETURN_SQL 无效
ORA-06550: 第 1 行, 第 7 列:
PL/SQL: Statement ignored

at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289)
at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:573)
at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1891)
at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteFetch(TTC7Protocol.java:1093)
at oracle.jdbc.driver.OracleStatement.executeNonQuery(OracleStatement.java:2047)
at oracle.jdbc.driver.OracleStatement.doExecuteOther(OracleStatement.java:1940)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2709)
at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:589)
at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:527)


依然是这样的
回复
Derek-Chen 2010-06-28
补充一点: 在存储过程中,把你需要的数据赋给v_out
回复
unm 2010-06-28
哦,上面发的就是代码

一个传入sql语句
一个传回数据

两个参数啊
回复
Derek-Chen 2010-06-28
create or replace procedure test(v_sql in varchar2,v_out out 结构类型) is
begin
end;

declare
begin
//调用上面的存储过程
test( vsql,vout);
//这个vout里的值就是你需要的返回值了
end;
回复
xiaohu8855 2010-06-28
我发给你的帖子是经过测试的
回复
xiaohu8855 2010-06-28
你怎么会有两个?号呢 ,把你的代码贴出来,给你看看 包括你的java代码? 还有你的数据库连接打开了吗
回复
unm 2010-06-28
传入sql是这样的
------------------------------------------------------------------------------
declare rtndata nvarchar2(2000);mKeyID NUMBER;
begin
insert into t_vehicle_kind (id,code,name,org_id) values (S_vehicle_kind.NEXTVAL,'dd@g.com','singletab','0');
select S_vehicle_kind.currval into mKeyID from dual;
rtndata:=rtndata||',{comp_119:'||mKeyID||'}';
insert into t_vehicle_kind (id,code,name,org_id) values (S_vehicle_kind.NEXTVAL,'dd@g.com','singletab','0');
select S_vehicle_kind.currval into mKeyID from dual;
rtndata:=rtndata||',{comp_121:'||mKeyID||'}';
insert into t_vehicle_kind (id,code,name,org_id) values (S_vehicle_kind.NEXTVAL,'dd@g.com','singletab','0');
select S_vehicle_kind.currval into mKeyID from dual;
rtndata:=rtndata||',{comp_122:'||mKeyID||'}';
dbms_output.put_line(rtndata);
select rtndata into rtndata from dual;
end;
------------------------------------------------------------------------------
存储过程是这样的
create or replace procedure proc_getdata(exesql VARCHAR2,r_data out varchar2) AS
BEGIN
EXECUTE IMMEDIATE exesql into r_data;
END;
------------------------------------------------------------------------------
java代码是这样的
cs = conne.prepareCall("{call return_sql(?,?)}");
cs.setString(1, sql);
cs.registerOutParameter(2, oracle.jdbc.OracleTypes.VARCHAR);
cs.executeQuery();
String return_data = cs.getString(1);
return return_data;
------------------------------------------------------------------------------
java提示错误是这样的
java.sql.SQLException: ORA-06550: 第 1 行, 第 7 列:
PLS-00905: 对象 RECODE_INIT.RETURN_SQL 无效
ORA-06550: 第 1 行, 第 7 列:
PL/SQL: Statement ignored

at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289)
at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:573)
at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1891)
at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteFetch(TTC7Protocol.java:1093)
at oracle.jdbc.driver.OracleStatement.executeNonQuery(OracleStatement.java:2047)
at oracle.jdbc.driver.OracleStatement.doExecuteOther(OracleStatement.java:1940)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2709)
at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:589)
at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:527)

------------------------------------------------------------------------------
然后郁闷是我这样子的

:(
回复
发动态
发帖子
基础和管理
创建于2007-09-28

1.7w+

社区成员

Oracle 基础和管理
申请成为版主
社区公告
暂无公告