这是我的PL/SQL:create or replace procedure indexRecord(v_title varchar2, v_pub_Name varchar2,
v_author varchar2, v_Smalltype_name varchar2 ,v_isbn varchar2, v_introduction varchar2,
myRecord out sp_package1.index_cursor) is
v_sql1 varchar2(2000);
v_sql2 varchar2(2000);
begin
v_sql1 := 'select title, books.isbn, author, pub_Name, introduction,
smallType_Name, stock_Qty, price, discount
from books,booktype2,publish,stock
where books.isbn = stock.isbn
and books.type = booktype2.smalltype_id
and books.pub_id = publish.pub_id';
v_sql2 := ' and books.title like ''%'''||v_title||'''%''
and publish.pub_name like ''%'''||v_pub_Name||'''%''
and books.author like ''%'''||v_author||'''%''
and booktype2.smallType_Name like ''%'''||v_Smalltype_name||'''%''
and books.isbn like ''%'''||v_isbn||'''%''
and books.introduction like ''%'''||v_introduction||'''%''';
if v_title = '' and v_pub_Name = '' and v_author = '' and v_Smalltype_name = ''
and v_isbn = '' and v_introduction = ''
then v_sql1 := v_sql1;
else
v_sql1 := v_sql1 || v_sql2;
end if;
dbms_output.put_line(v_sql1);
open myRecord for v_sql1;
end;
我的java调用代码:(其中一个)
list = dao.getIndexPageDate(sortValue, "", "", "", "", "");我的问题是:
1.按我给的存储过程传进去的参数有空字符串 "", 在pl/sql就是 ''吗?
2.这个语句有错,弄了一天了,不知哪错了。可能是 v_sql2 没写对 刚学PL/SQL,望各位大侠说具体点!
v_author varchar2, v_Smalltype_name varchar2 ,v_isbn varchar2, v_introduction varchar2,
myRecord out sp_package1.index_cursor) is
v_sql1 varchar2(2000);
v_sql2 varchar2(2000);
begin
v_sql1 := 'select title, books.isbn, author, pub_Name, introduction,
smallType_Name, stock_Qty, price, discount
from books,booktype2,publish,stock
where books.isbn = stock.isbn
and books.type = booktype2.smalltype_id
and books.pub_id = publish.pub_id';
v_sql2 := ' and books.title like ''%'''||v_title||'''%''
and publish.pub_name like ''%'''||v_pub_Name||'''%''
and books.author like ''%'''||v_author||'''%''
and booktype2.smallType_Name like ''%'''||v_Smalltype_name||'''%''
and books.isbn like ''%'''||v_isbn||'''%''
and books.introduction like ''%'''||v_introduction||'''%''';
if v_title = '' and v_pub_Name = '' and v_author = '' and v_Smalltype_name = ''
and v_isbn = '' and v_introduction = ''
then v_sql1 := v_sql1;
else
v_sql1 := v_sql1 || v_sql2;
end if;
dbms_output.put_line(v_sql1);
open myRecord for v_sql1;
end;
我的java调用代码:(其中一个)
list = dao.getIndexPageDate(sortValue, "", "", "", "", "");我的问题是:
1.按我给的存储过程传进去的参数有空字符串 "", 在pl/sql就是 ''吗?
2.这个语句有错,弄了一天了,不知哪错了。可能是 v_sql2 没写对 刚学PL/SQL,望各位大侠说具体点!
解决方案 »
- Oracle客户端 - Oracle SQL Handler(超方便实用且免费,能运行于 Windows 7、Linux、Mac)
- 求助~oracle 海量数据 查询语句的问题
- 求相邻的相同的数据的个数(在线等)
- 这个sql语句 怎么写?
- pl/sql 中调用ora存储过程,若传入/传出参数是自定义包中的数据类型时,编译不通过,报错ORA-00922:缺少或无效选项。何解? 3Q :)
- Oracle无驱动连接工具,不需安装Oracle客户端,连接oracle,仅500K
- 两台机器上装有不同ORACLE版本,相互间无法连接
- Oracle字符集相关问题,请各位仁兄帮忙!
- 晕,为什么我不能装ORACLE 8I??
- oracle8i如何获得错误码的详细信息?
- oracle左连接问题
- sqlldr语法问题
v_title varchar2,
v_pub_Name varchar2,
v_author varchar2,
v_Smalltype_name varchar2 ,
v_isbn varchar2,
v_introduction varchar2,
myRecord out sp_package1.index_cursor--总共七个参数,你只穿入6个,怎么能对了。
list = dao.getIndexPageDate(sortValue, "", "", "", "", "");
Connection conn = ....
String cmdString = "{call indexRecord(?,?,?,?,?,?,?)}";
CallableStatement call = conn.prepareCall(cmdString);
call.setString(1, sortValue);
call.setString(2,"");
...
call.registerOutParameter(7, OracleTypes.CURSOR);
嗯,我是同楼上的一样调用的存储过程(因为全部封装到DAO类了,这里只是调用),但是我的问题好没解决