Java Reference
In-Depth Information
sqltext varchar(4000);
first char(1) default 'Y';
begin
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;
Starts building
the SQL
Adds category
IDs to the SQL
Adds names to the SQL
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;
Executes the SQL
open return_cursor for sqltext;
Returns results
return return_cursor;
end get_category;
end;
/
Admittedly the example in listing 8.16 breaks a valuable rule of stored proce-
dures: we did not use parameter binding, which would prevent SQL injection and
increase performance. Given that, we would not have reduced our complexity but
would have increased it. So, is avoiding complexity always the rule by which we
live? Of course not! But in the case of Dynamic SQL we would be hard pressed to
arrive at a reason for performing this in a stored procedure.
Two of the major reasons why we use stored procedures are security and perfor-
mance, neither of which is applicable when it comes to Dynamic SQL . Using param-
eterized SQL on either the Java side or the stored procedure side will provide an
equivalent level of performance and security. When we shift our attention to legi-
bility and maintainability, we may become a bit discouraged. This procedure is
Search WWH ::




Custom Search