CREATE PROCEDURE statement
458
would have the following signature:
’(ZILjava/math/BigDecimal;[[B[Ljava/sql/ResultSet;)D’
$ For more information, see "Returning result sets from Java methods"
on page 577 of the book ASA User’s Guide.
♦
SQL/92 Persistent Stored Module feature.
♦
Sybase The Transact-SQL CREATE PROCEDURE statement is
different.
♦
SQLJ The syntax extensions for Java result sets are as specified in the
proposed SQLJ1 standard.
♦ The following procedure uses a case statement to classify the results of a
query.
CREATE PROCEDURE ProductType (IN product_id INT, OUT
type CHAR(10))
BEGIN
DECLARE prod_name CHAR(20);
SELECT name INTO prod_name FROM "DBA"."product"
WHERE id = product_id;
CASE prod_name
WHEN ’Tee Shirt’ THEN
SET type = ’Shirt’
WHEN ’Sweatshirt’ THEN
SET type = ’Shirt’
WHEN ’Baseball Cap’ THEN
SET type = ’Hat’
WHEN ’Visor’ THEN
SET type = ’Hat’
WHEN ’Shorts’ THEN
SET type = ’Shorts’
ELSE
SET type = ’UNKNOWN’
END CASE;
END
♦ The following procedure uses a cursor and loops over the rows of the
cursor to return a single value.
CREATE PROCEDURE TopCustomer (OUT TopCompany
CHAR(35), OUT TopValue INT)
BEGIN
DECLARE err_notfound EXCEPTION
FOR SQLSTATE ’02000’;
DECLARE curThisCust CURSOR FOR
SELECT company_name, CAST(
sum(sales_order_items.quantity *
product.unit_price) AS INTEGER) VALUE
FROM customer
LEFT OUTER JOIN sales_order
Standards and
compatibility
Example
To Next Page
Loading...