Re: Calling all Store Procedure Gurus

[Sven Boden]

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;