This might give you an idea:
create table Customer (
c_firstname varchar2(50),
c_lastname varchar2(50),
c_userid varchar2(50)
);
insert into Customer values ('Micky' , 'Mouse', 'mm');
insert into Customer values ('Donald', 'Duck' , 'dd');
insert into Customer values ('Peter' , 'Pan' , 'pp');
create or replace function GetCustomer(
FirstN varchar2 := null,
LastN varchar2 := null,
CID varchar2 := null
) return sys_refcursor
as
stmt varchar2(4000);
ret sys_refcursor;
begin
stmt := 'select * from Customer where 1=1';
if FirstN is not null then
stmt := stmt || ' and c_firstname like ''%' || FirstN || '%''';
end if;
if LastN is not null then
stmt := stmt || ' and c_lastname like ''%' || LastN || '%''';
end if;
if CID is not null then
stmt := stmt || ' and c_userid like ''%' || CID || '%''';
end if;
dbms_output.put_line(stmt);
open ret for stmt;
return ret;
end;
/
Later, in SQL*Plus:
set serveroutput on size 100000 format wrapped
declare
c sys_refcursor;
fn Customer.c_firstname%type;
ln Customer.c_lastname %type;
id Customer.c_userid %type;
begin
c := GetCustomer(LastN => 'u');
fetch c into fn, ln, id;
while c%found loop
dbms_output.put_line('First Name: ' || fn);
dbms_output.put_line('Last Name: ' || ln);
dbms_output.put_line('user id: ' || id);
fetch c into fn, ln, id;
end loop;
close c;
end;
/
Edit: The comment is right, and the procedure is subject to SQL injection. So, in order to prevent that, you could go with bind variables such as in this modified procedure:
create or replace function GetCustomer(
FirstN varchar2 := null,
LastN varchar2 := null,
CID varchar2 := null
) return sys_refcursor
as
stmt varchar2(4000);
ret sys_refcursor;
type parameter_t is table of varchar2(50);
parameters parameter_t := parameter_t();
begin
stmt := 'select * from Customer where 1=1';
if FirstN is not null then
parameters.extend;
parameters(parameters.count) := '%' || FirstN || '%';
stmt := stmt || ' and c_firstname like :' || parameters.count;
end if;
if LastN is not null then
parameters.extend;
parameters(parameters.count) := '%' || LastN || '%';
stmt := stmt || ' and c_lastname like :' || parameters.count;
end if;
if CID is not null then
parameters.extend;
parameters(parameters.count) := '%' || CID || '%';
stmt := stmt || ' and c_userid like :' || parameters.count;
end if;
if parameters.count = 0 then
open ret for stmt;
elsif parameters.count = 1 then
open ret for stmt using parameters(1);
elsif parameters.count = 2 then
open ret for stmt using parameters(1), parameters(2);
elsif parameters.count = 3 then
open ret for stmt using parameters(1), parameters(2), parameters(3);
else raise_application_error(-20800, 'Too many parameters');
end if;
return ret;
end;
/
Note, that now, whatever the input, the select statement becomes something like select ... from ... where 1=1 and col1 like :1 and col2 :2 ...
which is obviously much safer.
You're correct: FULLTEXT
search didn't hit InnoDB until MySQL 5.6. This leaves you with a few options:
- Update to MySQL 5.6 and use a
FULLTEXT
index
- Change the contract of your function to only allow prefix searches; that is, 'term%'. It will fulfill many use cases while saving your DB.
- Convert to a MyISAM table, or create a spare MyISAM table that you can join on specifically for searches.
Since you're building the query in Java, I'd also suggest removing the CONCAT('%'
stuff in favor of just creating a second variable that has the %
s already applied. That saves the DB from the extra work of parsing it and lets it utilize indexes better. Speaking of indexes, be sure you have indexes that span the columns where possible.
Regarding the Duplicate removal, you may be better off performing a GROUP BY p.idNumber
to save the extra effort and data transfer in Java.
Best Answer
It's possible for a SELECT statement to modify the database, for example:
select mysequence.nextval
increments a sequenceselect myfunction
executes a PL/SQL function which could have any sort of side effectsThe last point is a bit of a corner case since it only works with autonomous transactions, otherwise errors such as
are returned: