Michael Rechberger wrote:
Hey
I am studying DB2 right know and trying to make some things to work. I
noticed that I might understood some things wrong. Could you help me
clarify a bit?
That's what I wanted to do: A function/procedure I can call in a SQL.
This function queries the database and returns a value. I thought I can
write a stored proc like...
CREATE PROCEDURE DB2ADMIN.getLoadId ( IN p_table_name CHAR(30)
, IN p_load_grp_id INTEGER
, OUT p_load_id INTEGER)
LANGUAGE SQL
SPECIFIC getLoadId
------------------------------------------------------------------------
-- SQL Stored Procedure
------------------------------------------------------------------------
getId: BEGIN
SELECT load_info.load_id INTO p_load_id
FROM
load_info LEFT JOIN loads ON
load_info.load_id = loads.load_id
WHERE
load_grp_id = p_load_grp_id AND
load_table = p_table_name;
END getId
...and use this in a sql query like...
******
SELECT * FROM employee WHERE employee.load_info =
getLoadId('employee',1,?)
******
But obviously you cannot call a stored procedure in a SQL query?
Then I wanted to do the same stuff with a UDF but I read somewhere that
an external scalar function cannot query the database??
You can query the database. You can use a SQL-bodied function, i.e. one
declared with LANGUAGE SQL, or you use an external routine written in C/C++
or Java. Either way, queries can be used w/o any problem. Brian already
gave you a solution for a LANGUAGE SQL UDF.
If possible, I'd recommend to stick with SQL-bodied functions. They are
compiled into the SQL statement that invokes the function and, thus, gives
the optimizer a much better chance to optimize the overall query. This is
not possible with external code that can influence which (sub-)statements
are executed.
--
Knut Stolze
DB2 Information Integration Development
IBM Germany