SAP HANA存储过程样例（Table Type输出）

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