You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user-java@ibatis.apache.org by Brandon Goodin <br...@gmail.com> on 2005/05/02 11:55:50 UTC
Calling all Store Procedure Gurus
Hey all,
I am putting together a comparison of using iBatis dynamic SQL versus
dynamic SQL in a stored procedure. I want to solicit some assistance
on this. If you are willing i would like for you to write a stored
procedure that can accomplish the following requirements.
The stored procedure must use the following SQL statement:
SELECT
categoryId,
title,
description,
sequence
FROM Category
WHERE
categoryId IN (?,?,?,?,...) AND
name LIKE ('?%')
- The 'categoryId IN' should be completely omitted/ignored if no
values are passed in for it and the 'AND' should be removed.
- The 'categoryId IN' statement should be able to accommodate a
dynamic number of ids.
- The 'name LIKE' statement should be ignored if no LIKE value is
passed in and the 'AND' should be removed..
- The value of the 'name LIKE' should be an alpha character that has
the '%' wildcard appended to it inside the stored procedure.
Please provide straight JDBC code that calls the stored procedure.
I wanted to get a few examples from various databases if possible.
But, one will do as much as several. Whoever delivers the best usable
example will receive credit for it in the an upcoming iBatis book.
I hope a few of you are up for the challenge!
Brandon
P.S. I need this today :)
Re: Calling all Store Procedure Gurus
Posted by Brandon Goodin <br...@gmail.com>.
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 <li...@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;
>
>
Re: Calling all Store Procedure Gurus
Posted by Sven Boden <li...@pandora.be>.
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;