Consultor Eletrônico

Status: Verified

GOAL:

Sample Stored Procedure for SQL-92 Engine

GOAL:

How do I test stored procedure against SQL-92 engine in V9?

FACT(s) (Environment):

Progress 9.x

FIX:

This solution provides a sample Java stored procedure to show the syntax and sample constructions using the Progress SQL92 Engine in V9. This stored procedure creates a result set with the total number of orders per customer. A similar result can be obtained using the following select statement: SELECT "order".custnum, customer.name, count("order".custnum) FROM pub."order", pub.customer WHERE "order".custnum >= and customer.custnum = "order".custnum GROUP BY"order".custnum, customer.name;
DROP PROCEDURE view_orders;
CREATE PROCEDURE view_orders ( IN from_cust INTEGER)
RESULT (
custnum NUMERIC,
name CHARACTER(30),
totalorders NUMERIC
)
BEGIN
Integer icustnum = new Integer(0);
Integer itotorders = new Integer(0);
String sname = "";

SQLCursor ordercursor = new SQLCursor (
"select custnum, count(1) from pub.\"order\" where custnum >=? group by custnum"
);
SQLCursor custcursor = new SQLCursor (
"select name from pub.customer where custnum = ?"
);

ordercursor.setParam (1, from_cust);
ordercursor.open ();
ordercursor.fetch ();
while (ordercursor.found())
{
icustnum = (Integer) ordercursor.getValue(1, INTEGER);
itotorders = (Integer) ordercursor.getValue(2, INTEGER);

custcursor.setParam (1, icustnum);
custcursor.open ();
custcursor.fetch ();
if (custcursor.found()) sname = (String) custcursor.getValue(1, CHARACTER);
custcursor.close ();

SQLResultSet.set (1, icustnum);
SQLResultSet.set (2, sname);
SQLResultSet.set (3, itotorders);
SQLResultSet.insert ();

ordercursor.fetch();
}
ordercursor.close ();
END
COMMIT;
Example syntax to run this stored procedure: call view_orders(myint);