A stored procedure search allows the server to locate entities using a stored procedure in a database.
The stored procedure must provide the server with a list of the identifiers that it should use to update the users selection.
...
Stored procedure that returns result directly
Assume a database that supports returning a selection (in this case SQL Server)
| Code Block |
|---|
|
CREATE PROCEDURE RoadByNameDirect @roadname varchar(20) AS
SELECT DISTINCT id FROM roads WHERE name=@roadname
|
Stored procedure that returns result as a parameter
In this case Oracle requires the REF CURSOR type to be defined in a separate package (rather than being referenced directly in the parameters). Well this is what I have found, YMMV, either way the parameter must be a reference to a cursor.
| Code Block |
|---|
|
CREATE PACKAGE cursor_types AS
TYPE cursorType IS REF CURSOR;
END;
CREATE PROCEDURE RoadByNameParameter(roadname IN roads.name%TYPE DEFAULT NULL, results OUT cursor_types.cursorType) IS
BEGIN
OPEN results FOR
SELECT DISTINCT id
FROM roads
WHERE name = roadname;
END;
|
Stored procedure that returns results in a table
| Code Block |
|---|
|
CREATE PROCEDURE RoadByNameTable(user IN road_table.userid%TYPE, roadname IN roads.name%TYPE DEFAULT NULL) IS
BEGIN
DELETE FROM road_table
WHERE road_table.userid = user;
INSERT INTO road_table(userid, id)
SELECT userid, id
FROM roads
WHERE name = roadname;
COMMIT;
END;
|
| Code Block |
|---|
|
<search:procedure id="road_direct">
<entity>road</entity>
<displayName>by Stored Procedure (Direct)</displayName>
<description>Stored procedure returns result directly</description>
<dataSource>ds.main</dataSource>
<procedure>RoadByNameDirect</procedure>
<parameter id="roadname">
<parameter:label>Road Name</parameter:label>
</parameter>
</search:procedure>
<search:procedure id="road_parameter">
<entity>road</entity>
<displayName>by Stored Procedure (Parameter)</displayName>
<description>Stored procedure returns result as output parameter</description>
<dataSource>ds.main</dataSource>
<procedure>RoadByNameParameter</procedure>
<parameter id="roadname">
<label>Road Name</label>
</parameter>
</search:procedure>
<search:procedure id="road_table">
<entity>road</entity>
<displayName>by Stored Procedure (Table)</displayName>
<description>Stored procedure populates a table</description>
<dataSource>ds.main</dataSource>
<procedure>RoadByNameTable</procedure>
<table>road_table</table>
<key>id</key>
<parameter id="roadname">
<label>Road Name</label>
</parameter>
</search:procedure>
|