SAP HANA存储过程样例(Table Type输出)

image

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

展开阅读全文

没有更多推荐了,返回首页