I had an interesting case recently where
I had to modify a report to accept a parameter that could be a comma separated
list of values and I needed to filter on the value(s). Someone on the system
before me created a function called stringsplit that helps this occur. Her is
the form of stringsplit:
CREATE OR REPLACE
FUNCTION STRINGSPLIT (expression varchar2, delimiter varchar2 := ',')
return
SPLIT_TABLE pipelined
IS
idx pls_integer;
listTmp varchar2(32767) := expression;
BEGIN
loop
idx := instr(listTmp,delimiter);
if idx > 0 then
pipe row(substr(listTmp, 1, idx-1));
listTmp := substr(listTmp,
idx+length(delimiter));
else
pipe row(listTmp);
exit;
end if;
end loop;
return;
END
STRINGSPLIT;
As
you can see, this function returns a SPLIT_TABLE type. The form of the
Split_Table:
CREATE
OR REPLACE TYPE SPLIT_TABLE AS TABLE OF VARCHAR2(32767);
So,
our type is a table of large varchar2 values and our function takes a comma
separated list and returns a table of those values. Now, we need to reference
and conditionally filter on this in our report query. The condition is that if
it is null, do not filter, otherwise filter. It would look something like:
CREATE
OR REPLACE PROCEDURE myRecipeReport (
recipeTypeList IN VARCHAR2 DEFAULT NULL,
result_cursor OUT SYS_REFCURSOR)
IS
BEGIN
OPEN result_cursor FOR
SELECT r.name
FROM recipes r
WHERE ( recipeTypeList IS NULL
OR (EXISTS
(SELECT
TO_NUMBER (COLUMN_VALUE)
FROM TABLE (STRINGSPLIT (recipeTypeList, ','))
WHERE TO_NUMBER (COLUMN_VALUE) = r.type)))
ORDER BY 1;
END
myRecipeReport;
This
allows us to run the report for everything (list parameter is null) or filter
on a subset of available types.
We
are getting into several new things here: Types, Casting, etc. Google anything
that you are interested in that I do not cover explicitly.
Types:
A
Type is a user defined method to model the structure and behavior of data in an
application. You can create object types and static array types and as we've
seen, table types. These are usually collections of data that are used
somewhere in your application. You will need the Create Type privilege to
create a type in your schema, or Create Any Type to create a type in a
different schema.
Casting:
As
you may be aware from other coding ventures, Casting converts one datatype to
another.
Pipelined:
You
specify Pipelined on a function when you want the function to be the source of
data instead of a table. In our purpose here, we are calling the stringsplit
function on a single variable and creating a table out of that.
Pipe
row:
Pipe
row is an Oracle function that is used in the pipelined function to return the
results in a table format.
Select
from Table:
This
is how you treat the results of the pipelined function.
Pls_integer:
This
is a pl/sql datatype in the NUMBER family that ranges from -2,147,483,647
to 2,147,483,647. It uses machine arithmetic and is usually faster in
operations than the sql datatype NUMBER.
Column_value:
This
is how you refer to the column returned from the pipelined function in the
table format.
Exists:
This
is a native oracle function that executes a subquery and returns true or false
depending on whether or not rows were found. If the subquery refers to a column
in the parent query it is referred to as a correlated subquery.
I
feel I must apologize for the dearth of fucks and shits and goddamnits in this
post. I must be off my game today. I am still rather disgruntled and somewhat
riled up at the absurdities and idiocy that constantly surround me. I think I
must have just focused on getting the information out this time. I will
redouble my efforts to include references to coat-hanger abortions and liken my
fucktard predecessors to Corky from Life Goes On, Client #9 and/or Justin
Beiber's presumably tiny hairless nutsack. Until next time, keep fighting the
good fight, stockpiling weaponry, practicing your tomahawk moves, preparing for
the zombie apocalypse or civilization collapse and just whatever general
badassery you are into. Thanks Obama! ;)
No comments:
Post a Comment