How about the following for Oracle... I can also send it as an
attachment.
Regards,
Sven Boden
EDS
create table category
(
categoryid char(2),
name varchar(255),
title varchar(255),
description varchar(255),
sequence number
);
insert into category(categoryid, name, title, description, sequence)
values('AA', 'AA name', 'Title AA', 'Description AA', 1);
insert into category(categoryid, name, title, description, sequence)
values('AA', 'AB name', 'Title AB', 'Description AB', 1);
insert into category(categoryid, name, title, description, sequence)
values('BB', 'AA name', 'Title BB', 'Description BB', 1);
insert into category(categoryid, name, title, description, sequence)
values('CC', 'CC name', 'Title CC', 'Description CC', 1);
insert into category(categoryid, name, title, description, sequence)
values('AA', 'DD name', 'Title DD', 'Description DD', 1);
/
create or replace package category_pkg
as
type ref_cursor is ref cursor;
function get_category(categoryid varchar default null,
name category.name%type default null)
return ref_cursor;
end;
/
create or replace package body category_pkg
as
function get_category(categoryid varchar default null,
name category.name%TYPE default null)
return ref_cursor
is
return_cursor ref_cursor;
sqltext varchar(4000);
first char(1) default 'Y';
begin
--
-- Note that this is a very bad example of Oracle PL-SQL code
-- Any query should use parameter binding to be scalable and
-- to avoid 'SQL injection'.
--
sqltext := 'select c.categoryid, c.title, c.description,
c.sequence ' ||
' from category c ';
if ( categoryid is not null ) then
if ( first = 'Y' ) then
sqltext := sqltext || 'where c.categoryid in (' ||
categoryid || ') ';
first := 'N';
end if;
end if;
if ( name is not null ) then
if ( first = 'Y' ) then
sqltext := sqltext || 'where ';
else
sqltext := sqltext || 'and ';
end if;
sqltext := sqltext || 'c.name like ''' || name || '%''' ;
first := 'N';
end if;
open return_cursor for sqltext;
return return_cursor;
end get_category;
end;
/
-- Examples of execution via SQL-plus
set autoprint on
declare
c category_pkg.ref_cursor;
begin
-- :c := category_pkg.get_category();
-- :c := category_pkg.get_category(name => 'AB');
-- :c := category_pkg.get_category(name => 'AA', categoryid =>
'''AA'', ''BB''');
:c := category_pkg.get_category(categoryid => '''AA'', ''BB''');
end;
