| Download Docs | Site Map | Glossary | |
|
|
|||
| bea.com | products | dev2dev | support | askBEA |
|
|
|
|||||||
 |
| e-docs > Liquid Data for WebLogic > Building Queries and Data Views > Sample Stored Procedure Description Files |
|
Building Queries and Data Views
|
Sample Stored Procedure Description Files
This section shows several sample Stored Procedure Description files. For simplicity and readability, each of the examples shown defines a single stored procedure and its supporting type; your Stored Procedure Description files can define multiple stored procedures and multiple types. This section includes the following examples:
DB2 Simple input_only, output_only, and input_output Example
The following Stored Procedure Description file describes a DB2 stored procedure that returns simple data types with input_only, output_only, and input_output parameters.
<?xml version="1.0" encoding="UTF-8"?>
<definitions>
<types>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="EmptyOutput">
<xs:complexType>
<xs:sequence>
</xs:sequence>
</xs:complexType>
</xs:schema>
</types>
<functions>
<!-- given a customer id if valid, returns as output
parameters all the customer details -->
<function name="CALLINCALLOUT" return_type="EmptyOutput">
<argument label="custid" mode="input_only" type="xs:string"/>
<argument label="fname" mode="output_only" type="xs:string"/>
<argument label="lname" mode="output_only" type="xs:string"/>
<argument label="telephoneNumber" mode="output_only"
type="xs:long"/>
<argument label="customerSinceAsData" mode="output_only"
type="xs:date"/>
<argument label="customerSinceAsTimeStamp"
mode="output_only" type="xs:dateTime"/>
<presentation group="DB2 stored procedures"/>
</function>
</functions>
</definitions>
The following is the stored procedure signature for this Stored Procedure Description file. This signature creates a procedure that has one simple input and returns five simple outputs.
CREATE PROCEDURE DB2ADMIN.CALLINCALLOUT (
IN CUSTID varchar(64), OUT FNAME varchar(4000),
OUT LNAME varchar(4000), OUT TELEPHONENUMBER bigint,
OUT CUSTOMERSINCE date, OUT CUSTOMERSINCE1 timestamp )
EXTERNAL NAME
'"DB2ADMIN".SQL30205005750980:db2test.CallInCallOut.callInCallOut'
SPECIFIC DB2ADMIN.CALLINCALLOUT
RESULT SETS 0
LANGUAGE JAVA
PARAMETER STYLE JAVA
NOT DETERMINISTIC
FENCED NO
DBINFO NULL
CALL MODIFIES SQL DATA
Oracle Cursor Output Parameter Example
The following Stored Procedure Description file describes an Oracle stored procedure that returns an output parameter as a cursor.
<?xml version="1.0" encoding="UTF-8"?>
<definitions>
<types>
<xs:element name="OutCursor">
<xs:complexType>
<xs:sequence>
<xs:element name="CUSTOMER" minOccurs="0"
maxOccurs="unbounded">
<xs:complexType>
<xs:sequence>
<xs:element name="C_CUSTKEY" type="xs:integer"/>
<xs:element name="C_FNAME" type="xs:string"/>
<xs:element name="C_LNAME" type="xs:string"/>
<xs:element name="C_STATE" type="xs:string"/>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="Output">
<xs:complexType>
<xs:sequence/>
</xs:complexType>
</xs:element>
</xs:schema>
</types>
<functions>
<function name="TEST_PACKAGE.GETCUSTOMER" return_type="Output">
<argument label="CUSTID" mode="input_only" type="xs:string"/>
<argument label="customer_OUT" mode="output_only"
type="OutCursor"/>
<presentation group="OR-TEST stored procedures"/>
</function>
</functions>
</definitions>
The following is the stored procedure signature for this Stored Procedure Description file. This signature creates a procedure that returns a cursor as an output parameter.
create or replace package test_package as
-- Stored procedure that returns a cursor as an output parameter
procedure getCustomer
( CUSTOMERID IN VARCHAR, cust_cursor1 OUT ref_cursor );
end test_package ;
DB2 Multiple Result Set Example
The following Stored Procedure Description file describes a DB2 stored procedure that returns multiple result sets.
<?xml version="1.0" encoding="UTF-8"?>
<definitions>
<types>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="CustomerAndOrders">
<xs:complexType>
<xs:sequence>
<xs:element ref="resultSetCustomer"/>
<xs:element ref="resultSetCustomerOrders"/>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="resultSetCustomer">
<xs:complexType>
<xs:sequence>
<xs:element name="customerRow" minOccurs="1"
maxOccurs="unbounded">
<xs:complexType>
<xs:sequence>
<xs:element name="C_CUSTKEY" type="xs:string"/>
<xs:element name="C_FNAME" type="xs:string"/>
<xs:element name="C_LNAME" type="xs:string"/>
<xs:element name="C_STATE" type="xs:string"/>
<xs:element name="C_SINCE" type="xs:date"/>
<xs:element name="C_TELEPHONENO" type="xs:long"/>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="resultSetCustomerOrders">
<xs:complexType>
<xs:sequence>
<xs:element name="orderRow" minOccurs="1"
maxOccurs="unbounded">
<xs:complexType>
<xs:sequence>
<xs:element name="C_CUSTKEY" type="xs:string"/>
<xs:element name="CO_ORDERKEY" type="xs:string"/>
<xs:element name="CO_ORDERDATE" type="xs:date"/>
<xs:element name="CO_SHIPMETHOD" type="xs:date"/>
<xs:element name="CO_TOTALORDERAMT" type="xs:decimal"/>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>
</types>
<functions>
<!-- result one returns a customer, result 2 has the
orders for that customer -->
<function name="CALLMULTIPLERESULTSET"
return_type="CustomerAndOrders">
<argument label="custid" mode="input_only" type="xs:string"/>
<presentation group="DB2 stored procedures"/>
</function>
</functions>
</definitions>
The following is the stored procedure signature for this Stored Procedure Description file. This signature creates a procedure that returns multiple result sets.
CREATE PROCEDURE DB2ADMIN.CALLMULTIPLERESULTSET (
IN CUSTID varchar(64) )
EXTERNAL NAME '"DB2ADMIN".SQL30206110348560:db2test.CallMultipleResultSet.callMultipleResultSet'
SPECIFIC DB2ADMIN.CALLMULTIPLERS
RESULT SETS 2
LANGUAGE JAVA
PARAMETER STYLE JAVA
NOT DETERMINISTIC
FENCED NO
DBINFO NULL
CALL MODIFIES SQL DATA
The following Stored Procedure Description file describes an Oracle stored procedure that returns a cursor as a return_value.
<?xml version="1.0" encoding="UTF-8"?>
<definitions>
<types>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="Output_TEST_PACKAGE.GETCUSTOMERBYID">
<xs:complexType>
<xs:sequence>
<xs:element name="return_value">
<xs:complexType>
<xs:sequence>
<xs:element maxOccurs="unbounded" minOccurs="0"
name="customer">
<xs:complexType>
<xs:sequence>
<xs:element name="FIRST_NAME" type="xs:string"/>
<xs:element name="LAST_NAME" type="xs:string"/>
<xs:element name="CUSTOMER_ID" type="xs:string"/>
<xs:element name="STATE" type="xs:string"/>
<xs:element name="ZIPCODE" type="xs:string"/>
<xs:element name="CITY" type="xs:string"/>
<xs:element name="STREET_ADDR2"
type="xs:string"/>
<xs:element name="STREET_ADDR1"
type="xs:string"/>
<xs:element name="CUSTOMER_SINCE"
type="xs:dateTime"/>
<xs:element name="EMAIL_ADDRESS"
type="xs:string"/>
<xs:element name="TELEPHONE_NUMBER"
type="xs:string"/>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>
</types>
<functions>
<function name="TEST_PACKAGE.GETCUSTOMERBYID"
return_type="Output_TEST_PACKAGE.GETCUSTOMERBYID">
<argument label="CUSTID" mode="input_only" type="xs:string"/>
</function>
</functions>
</definitions>
The following is the stored procedure signature for this Stored Procedure Description file. This signature creates a procedure that returns a cursor.
create or replace package body test_package as
-- SP that returns a cursor
FUNCTION getCustomerByID (custID varchar)
RETURN CUST_CURSOR IS cur CUST_CURSOR;
BEGIN
open cur for
select first_name, last_name, customer_id, state,
zipCode,city, street_address2, street_address1,
customer_since, email_address, telephone_number
from wireless.customer
where customer_id = custID;
return cur;
END;
end test_package ;
|
|
|
|
||
 |
|
|
 |
 |
 |
|
||
