ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Brandon Goodin <brandon.goo...@gmail.com>
Subject Re: Calling all Store Procedure Gurus
Date Mon, 02 May 2005 18:09:07 GMT
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 <list123@pandora.be> 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;
> 
>

Mime
View raw message