It is a set of SQL extensions for SAP HANA database which allow developers to push data intensive logic into the database.
2. When should be SQLScript used?
It should be used in cases where other modeling constructs of HANA such as Attribute views or Analytic views are not sufficient.
3. Explain how SQLScript procedure is processed in HANA database?
When a user creates a new procedure, the HANA database query compiler first:
Parse the statements
Check the statement semantic correctness
Optimize the code for Declarative and Imperative logic
Code generation creates Calculation models for Declarative
logic and L nodes for Imperative logic
Compiled procedure creates Content in the database Catalog
and in Repository.
4. What happens when you compile the procedure?
During compilation, the call to the procedure is rewritten for processing by the calculation engine.
5. What happens when you execute the procedure?
It binds the actual parameters to the calculation models generated in the definition phase.
6.What are the data types supported in SQLScript?
SQLScript allows the definitions of table types and also supports primitive data types such as Integer, Decimal(p,q), Varchar, Blob, Timestamp, etc.
7. What is a table type?
Table types are used to define parameters for a procedure that represent tabular results.
Example:
CREATE TYPE tt_publishers AS TABLE ( publisher INTEGER,
name VARCHAR(50), price DECIMAL, cnt INTEGER);
CREATE TYPE tt_years AS TABLE ( year VARCHAR(4), price
DECIMAL, cnt INTEGER);
8. What is the general syntax used in creating procedure?
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> END
9. How to recompile a procedure?
ALTER PROCEDURE proc_name RECOMPILE [WITH PLAN]
10. Can you specify a default value while creating procedure? Is there any change while calling the procedure?
CREATE PROCEDURE my_proc (IN P1 INT,
IN P2 INT DEFAULT 1,
OUT out1 DUMMY) AS
BEGIN
out1 = SELECT :P1 + :P2 AS DUMMY FROM DUMMY;
END;
While calling the procedure, we need to pass 'Named Parameters'
with token (=>).
CALL my_proc (P1 => 3, out1 => ?)
If you want to specify all the input fields then call
statement would be:
CALL my_proc (3, 4, ?)
11. What is the purpose of procedure 'Call... With Overview' option?
To write the results of a procedure call directly into a physical table.
CALL my_proc (3, 4, NULL) WITH OVERVIEW;
12. What is the purpose of procedure 'Call.....In Debug Mode' option?
It creates the additional debug information during the execution of the procedure. This type of call has additional runtime overhead hence should be used for debugging purposes only.
CALL my_proc (3, 4, NULL) IN DEBUG MODE;
13.What is Table variable?
These are variables with table type which are bound to the value of a physical table, SQL query or a calculation engine plan operator. Table variables are bound using equality operator.
14. What is a scalar variable?
We define scalar variable as IN, OUT, or INOUT and can
be referenced anywhere in a procedure.
lt_expensive_books = SELECT title, price,
crcy FROM :it_books
WHERE price > :minPrice AND crcy =
:currency;
In above assignment, the variable lt_expensive_books is bound. Variable :it_books refers to an IN parameter of a Table type. :minPrice and :currency refer to IN parameter of a scalar type.
15. What is CE plan operator? What are the different categories?
CE plan operator does the data transformation functionality internally and used in definition of functions. It is an alternate to using SQL statements.
Data Source Access operators that bind a column table or a column view to a table variable. (CE_COLUMN_TABLE, CE_JOIN_VIEW, CE_OLAP_VIEW, CE_CALC_VIEW)
Relational operators that allow a user to bypass the SQL
processor during evaluation and directly executed in the calculation engine.
(CE_JOIN,
CE_LEFT_OUTER_JOIN, CE_RIGHT_OUTER_JOIN, CE_PROJECTION,CE_CALC, CE_AGGREGATION,
CE_UNION_ALL)
Special extensions implement, e.g., crucial business functions
inside the database kernel.
(CE_VERTICAL_UNION,
CE_CONVERSION)
16. What is CE_COLUMN_TABLE operator?
It provides access to an existing column table. It takes the name of the table and returns its content bound to a variable.
Example:
ot_books1 = CE_COLUMN_TABLE (“BOOKS”);
ot_books2 = CE_COLUMN_TABLE (“BOOKS”, [“TITLE”, “PRICE”,
“CRCY”]);
This example only works on a column table and does
not invoke the SQL processor. It is semantically equivalent to the following:
ot_books3 = SELECT * FROM books;
ot_books4 = SELECT title, price, crcy FROM books;
The function that provide data source access does not allow renaming of attributes.
