Excellent Sven! Thanks! I'll use your code and give you credit in the book.
I welcome any other examples from others. We can add them to the wiki
for some comparative observations.
Brandon
On 5/2/05, Sven Boden <[EMAIL PROTECTED]> wrote:
>
> 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;
>
>
