Return to Answer

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'  , 'dd');

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;
/

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;
/

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.

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'  , 'dd');

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;
end;
/

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'  , 'dd');

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;
/

This might be a starting point:

create or replace function GetCustomer(
  FirstN    varchar2 := null
  -- Further parameters...
) return sys_refcursor
as
  stmt varchar2(4000);
  ret sys_refcursor;
begin
  stmt := 'select xyz from customer where 1=1 ';

  if FirstN is not null then
     stmt := stmt || ' and c_firstname like ' || FirstN;
  end if;
  // ... Further ifs for parameters...

  open ret for stmt;
  return ret;
end;

Later:

declare
  c sys_refcursor;
  xyz varchar2(50);
begin
  c := GetCustomer;

  fetch c into xyz;
  while  c%found loop
      dbms_output.put_line(xyz);
   fetch c into xyz;
  end loop;
end;
/

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'  , 'dd');

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;
end;
/