Question

oracle中如何用存储过程实现数字的模糊查询

oracle中如何用存储过程实现数字的模糊查询
create table student(sno number(10) primary key,sname varchar2(20) not null,sex varchar2(20) not null,age number(3) not null );
create or replace procedure get_data_by_sno(no in varchar2----》这里该怎么写,out_cursor out sys_refcursor) as
begin open out_cursor for select sno,sname,sex,age from studentwhere cast(student.sno as varchar2) like no ;
end;
/

该如何更改
要求：可以实现用java代码调用存储过程实现查询
String sql = "{call get_data_by_sno(?,?)}";
CallableStatement state = conn.prepareCall(sql);
state.set (1, "%039"); ------》 这里该怎么做
state.registerOutParameter(2, OracleTypes.CURSOR);

Answer 1

create or replace procedure get_data_by_sno(no in varchar2,out_cursor out sys_refcursor) as
begin open out_cursor for select student.sno,sname,thname,cname,teacher,grade from student,theme,grade where to_char(student.sno) like no and student.sno=grade.sno and theme.thno=grade.thno;
end
state.setString(1, "%039"老枣陵);
state.registerOutParameter(2, OracleTypes.CURSOR);
改侍戚成岩高这样就行了。

Answer 2

什么叫做数亮派字的模糊查询？
select * from student where to_char(sno) like '%10%'; //表示只要你慧禅的sno里面前键尘含有10.
select * from student where to_char(sno) like '10%';//表示只要你的sno里面10开头
select * from student where to_char(sno) like '%10';//表示你的sno里面10结尾

create or replace procedure get_data_by_sno(
no in varchar2,
out_cursor out sys_refcursor) as
begin
open out_cursor for
select sno,sname,sex,age
from student
where to_char(sno) like no;
end;
/

然后，你在调用的时候，在no前后加上%%。

Answer 3

数字的模糊查询?
是粗搏不是你那岁亏个 sno number(10) 字段？

直接岩雀祥
WHERE TO_CHAR(sno) LIKE '%123%'
就可以了