CREATE PROCEDURE getOutput( IN cnt INTEGER, IN currency VARCHAR(3),
OUT output_pubs tt_publishers,
OUT output_year tt_year)
LANGUAGE SQLSCRIPT READS SQL DATA AS
BEGIN
big_pub_ids = SELECT publisher AS pid FROM books -- Query Q1
GROUP BY publisher HAVING COUNT(isbn) > :cnt;
big_pub_books = SELECT title, name, publisher, -- Query Q2
year, price
FROM :big_pub_ids, publishers, books
WHERE pub_id = pid AND pub_id = publisher
AND crcy = :currency;
output_pubs = SELECT publisher, name, -- Query Q3
SUM(price) AS price, COUNT(title) AS cnt
FROM :big_pub_books GROUP BY publisher, name;
output_year = SELECT year, SUM(price) AS price, -- Query Q4
COUNT(title) AS cnt
FROM :big_pub_books GROUP BY year;
END;
标准存储过程创建语句:
CREATE PROCEDURE <proc_name> [(<parameter_clause>)] [LANGUAGE <lang>] [SQL SECURITY
<mode>]
[READS SQL DATA [WITH RESULT VIEW <view_name>]] AS
<local_scalar_variables>
BEGIN
<procedure_code>
EN