我的实验代码如下:
declare sdl_cursor cursor for select * from isdev_sdl1;
open sdl_cursor;
在pl/sql developer里一执行,就提示“ORA-06550: line 1, column 27: PLS-00103: Encountered the symbol "FOR" when expecting one of the following: := . ( @ % ; not null range default character
请与系统管理员联系。”
可是在PB里执行没语法错误,困扰之至,请各位大虾帮忙啊!
declare sdl_cursor cursor for select * from isdev_sdl1;
open sdl_cursor;
在pl/sql developer里一执行,就提示“ORA-06550: line 1, column 27: PLS-00103: Encountered the symbol "FOR" when expecting one of the following: := . ( @ % ; not null range default character
请与系统管理员联系。”
可是在PB里执行没语法错误,困扰之至,请各位大虾帮忙啊!
cursor sdl_cursor is select * from isdev_sdl1;
begin
open sdl_cursor;
...这些最基础的语法看看书即可,自己也要动动手。
希望大家帮帮我的忙,谢谢了!
set serveroutput on;declare
cursor cur_test is select * from dual;
cur_row dual%rowtype;
begin
open cur_test;
fetch cur_test into cur_row;
while(cur_test%found)
loop
dbms_output.put_line(cur_row.dummy);
fetch cur_test into cur_row;
end loop;
close cur_test;
end;
/SQL>
X
PL/SQL procedure successfully completed
SQL> set serveroutput off;
你典型的语法错误。
open sdl_cursor;
这是我的代码declare cursor cur_test is select * from dual;
还没看出来?
但是我还有个疑问。我现在是在java中运行上面的代码,我大概这样做的,以您的代码为例:
m_query="declare"+
" cursor cur_test is select * from dual;"+
"cur_row dual%rowtype;"+
"begin"+
"open cur_test;"+
"fetch cur_test into cur_row;"+
executeQuery(m_query);
while(cur_test%found)
loop
dbms_output.put_line(cur_row.dummy);
//这里是java语句
m_query="fetch cur_test into cur_row;"+
"end loop;"+
"close cur_test;"+
"end;"
executeQuery(m_query);
这样的话,cursor的begin和end部分就被java语句分开了,那语法就有问题了。
您能明白我的意思吗?
这样的话,你需要将匿名块写成一个存储过程
create or replace procedure p_test
(
ocursor out sys_refcursor;
)
as
begin
open ocursor for select * from isdev_sdl1;
end p_test;
然后 Java 代码调用存储过程即可,将一个 OracleTypes.CURSOR 类型传入
m_query="declare cursor sdl_cursor is"+
" select"+
" id,"+
"PARTNUMBER,"+
"LEVELBOM"
" from isdept.isdev_bomcom_lrj;"+
" begin"+
" open sdl_cursor;"+
" fetch sdl_cursor into "+id+"," +
""+PARTNUMBER+"," +
""+LEVELBOM+""
这里面的几个变量id,PARTNUMBER,LEVELBOM的值在后面的java语句中也会用到,这也可以写到存储过程里吗?
哦,这样的话,我还是先琢磨一下存储过程。
非常感谢您的耐心指导!
实际上,像你这么简单的一个 Java 查询数据库表的应用,根本不需要写存储过程这么复杂。
Connection conect; //打开一个数据库连接
Statement stmt = conect.createStatement();
String sql = "select id, partnumber, levelbom from isdev_sdl1";
ResultSet rs = stmt.executeQuery(Sql);
String id;
String partnumber;
String levelbom;
while(rs.next())
{
id = rs.getString("id");
partnumber = rs.getString("partnumber");
levelbom = rs.getString("levelbom");
//然后对这几个变量进行应用即可
}
declare
cursor cur_test is select * from dual;
begin
open cur_test;
fetch cur_test into cur_row;
while(cur_test%found)
loop
//java代码放在这里
fetch cur_test into cur_row;
end loop;
close cur_test;
end;我是觉得java代码放在cursor的操作中间,执行的时候语法会有问题。
declare
cursor 游标名称 is
select 语句;