Table of Contents

Open all
Close all
Introduction
15
Structure of the Book
18
Acknowledgments
20
1 SAP HANA
21
1.1 What Is SAP HANA?
22
1.1.1 SAP HANA: A Fast SQL Database
22
1.1.2 SAP HANA: An Application Server
26
1.1.3 SAP HANA: A Collection of Tools
27
1.2 System Architecture
29
1.2.1 SAP HANA Server Components
29
1.2.2 Databases and Tenants
30
1.3 Organizing Database Objects
32
1.3.1 Database Schemas
32
1.3.2 Database Catalogs
34
1.3.3 Content and Repositories
35
1.4 Development Environments
36
1.4.1 SAP HANA Studio
37
1.4.2 SAP HANA Database Explorer
40
1.5 The SQL Console
44
1.6 Summary
47
2 Getting Started with SQLScript
49
2.1 SQL versus SQLScript
49
2.2 Basic Language Elements
53
2.2.1 Statements
53
2.2.2 Whitespace
54
2.2.3 Comments
54
2.2.4 Literals
56
2.2.5 Identifiers
58
2.2.6 Access to Local Variables and Parameters
59
2.2.7 System Variables
60
2.2.8 Reserved Words
61
2.2.9 Operators
61
2.2.10 Expressions
63
2.2.11 Predicates
65
2.2.12 Data Types
66
2.2.13 The NULL Value
67
2.2.14 The DUMMY Table
69
2.3 Modularization and Logical Containers
70
2.3.1 Blocks
72
2.3.2 Procedures
75
2.3.3 User-Defined Functions
83
2.3.4 User-Defined Libraries
87
2.4 Sample Program
89
2.4.1 Requirements
89
2.4.2 Requirements Analysis
90
2.4.3 Implementation
91
2.4.4 Testing the Implementation
97
2.5 Summary
100
3 Declarative Programming in SQLScript
101
3.1 Table Variables
102
3.1.1 Declaring Table Variables
102
3.1.2 Using Table Variables
103
3.2 SELECT Statements
104
3.2.1 SELECT Clauses
105
3.2.2 Field List of SELECT Clauses
105
3.2.3 FROM Clauses
119
3.2.4 Joins
122
3.2.5 WHERE Conditions
130
3.2.6 WITH Clauses
137
3.2.7 GROUP BY Clauses
139
3.2.8 HAVING Clauses
141
3.2.9 ORDER BY Clauses
142
3.2.10 Set Theory
143
3.2.11 Subqueries
145
3.2.12 Alias Names
146
3.3 Other Operators
148
3.3.1 Calculation Engine Plan Operators
148
3.3.2 MAP_MERGE Operator
149
3.3.3 MAP_REDUCE Operator
150
3.4 Summary
151
4 Data Types and Their Processing
153
4.1 Character Strings
153
4.1.1 Data Types for Character Strings
154
4.1.2 Conversions
157
4.1.3 Character String Functions
157
4.1.4 SQLSCRIPT_STRING Library
171
4.2 Date and Time
176
4.2.1 Date Information
176
4.2.2 Time Information
181
4.2.3 Combined Time and Date Information
182
4.2.4 Processing Time and Date Values
182
4.2.5 Examples of Processing Time Values
187
4.3 Numerical Data
189
4.3.1 Basic Arithmetic Operations
191
4.3.2 Square Roots and Exponents
191
4.3.3 Logarithms
192
4.3.4 Rounding or Trimming
192
4.3.5 Trigonometry
194
4.3.6 Random Numbers
194
4.3.7 Sign
194
4.3.8 Quantities and Amounts
195
4.4 Binary Data Types
200
4.4.1 Conversion between Binary Data, Hexadecimal Data, and Character Strings
201
4.4.2 Bits and Bytes
202
4.5 Conversions between Data Types
204
4.6 Summary
205
5 Write Access to the Database
207
5.1 INSERT
208
5.1.1 Individual Data Records
208
5.1.2 Inserting Multiple Records Simultaneously
209
5.2 UPDATE
211
5.2.1 Simple UPDATE Statement
211
5.2.2 UPDATE Statement with Reference to Other Tables
212
5.3 UPSERT or REPLACE
213
5.3.1 Inserting or Updating Individual Data Records
213
5.3.2 Inserting or Updating Multiple Data Records
214
5.4 MERGE INTO
214
5.5 DELETE
217
5.6 TRUNCATE TABLE
217
5.7 Summary
217
6 Imperative Programming
219
6.1 Variables
219
6.1.1 Local Scalar Variables
219
6.1.2 Local Table Variables
224
6.1.3 Session Variables
234
6.1.4 Temporary Tables
235
6.2 Flow Control Using IF and ELSE
236
6.3 Loops
239
6.3.1 FOR Loop
239
6.3.2 WHILE Loop
240
6.3.3 Controlling Loop Passes
241
6.3.4 Exercise: Greatest Common Divisor
242
6.4 Cursors
243
6.4.1 FOR Loop via a Cursor
243
6.4.2 Open, Read, and Close Explicitly
244
6.4.3 Updateable Cursors
246
6.5 Arrays
246
6.5.1 Generating an Array
247
6.5.2 Accessing the Array
247
6.5.3 Arrays as Local Variables
248
6.5.4 Splitting and Concatenating Arrays
249
6.5.5 Arrays and Table Columns
250
6.5.6 Bubble Sort Exercise
251
6.6 Transaction Control
253
6.6.1 Transactions
253
6.6.2 Autonomous Transactions
254
6.7 Executing Dynamic SQL
255
6.7.1 Parameters of Dynamic SQL
257
6.7.2 Input Parameters
258
6.8 Error Handling
260
6.8.1 What Are Exceptions?
261
6.8.2 Triggering Exceptions
262
6.8.3 Catching Exceptions
262
6.9 Summary
266
7 Creating, Deleting, and Editing Database Objects
267
7.1 Tables
268
7.1.1 Creating Database Tables
268
7.1.2 Changing Database Tables
272
7.1.3 Deleting Database Tables
273
7.2 Table Types
273
7.3 Views
274
7.4 Sequences
276
7.4.1 Increment
277
7.4.2 Limits
277
7.4.3 Behavior When Reaching the Limit
277
7.4.4 Resetting the Sequence
278
7.4.5 Changing and Deleting a Sequence
278
7.5 Triggers
278
7.5.1 Parameters
280
7.5.2 Per Row or Per Statement
281
7.6 Summary
281
8 SQLScript in ABAP Programs
283
8.1 AMDP Procedures
283
8.1.1 Introduction to AMDP
284
8.1.2 Creating AMDP Procedures
287
8.1.3 Generated Objects of an AMDP Method
290
8.1.4 Lifecycle of the Generated Objects
294
8.1.5 Two-Track Development
294
8.1.6 Using AMDP Procedures in Other AMDP Procedures
297
8.2 CDS Table Functions
300
8.2.1 Creating a CDS Table Function
300
8.2.2 Generated Objects of a CDS Table Function
305
8.2.3 Implicit Client Handling of CDS Table Functions
306
8.3 AMDP Functions for AMDP Methods
307
8.3.1 AMDP Table Functions
307
8.3.2 Scalar AMDP Functions
309
8.4 Alternatives to AMDP for Calling SQLScript Code from ABAP Programs
310
8.5 Recommendations
311
8.6 Summary
312
9 SQLScript in SAP Business Warehouse
313
9.1 Executing the Data Transfer Process in ABAP vs. SAP HANA
314
9.2 Transformation Routines as AMDP
318
9.2.1 Creating Transformation Routines in Eclipse
318
9.2.2 Creating Transformation Routines in SAP GUI
319
9.3 Successive Transformations and Mixed Execution
320
9.4 Generated AMDP Classes
321
9.4.1 Signature of AMDP Methods
323
9.4.2 Assigning the Output Tables
325
9.4.3 Access to Data from Other Data Models
325
9.5 Individual Routines
328
9.5.1 Start Routines
329
9.5.2 End Routines
329
9.5.3 Expert Routines
330
9.5.4 Field Routines
332
9.6 Error Processing and Error Stack
333
9.6.1 Processing Flow in the Data Transfer Process
335
9.6.2 Example: Recognizing Incorrect Data in Table OUTTAB
336
9.6.3 Example: Finding Invalid Field Contents with Regular Expressions
337
9.7 Summary
337
10 Clean SQLScript Code
339
10.1 Code Readability
339
10.1.1 Formatting the Code
340
10.1.2 Mnemonic Names
341
10.1.3 Granularity of Procedures and Functions
342
10.1.4 Comments
345
10.1.5 Decomposing Complex Queries
347
10.1.6 Readable SQLScript Statements
351
10.2 Performance Recommendations
352
10.2.1 Reducing Data Volumes
353
10.2.2 Avoid Switching between Row and Column Engines
353
10.2.3 Declarative Queries
353
10.2.4 Scalar Functions
353
10.3 Summary
354
11 Tests, Errors, and Performance Analysis
355
11.1 Testing SQLScript Code
356
11.1.1 SQL Console
356
11.1.2 Testing ABAP-Managed Database Procedure Methods
358
11.1.3 SQLSCRIPT_LOGGING Library
359
11.1.4 End-User Test Framework in SQLScript
361
11.2 Debugger for SQLScript
365
11.2.1 SQLScript Debugger in SAP HANA Studio
366
11.2.2 ABAP-Managed Database Procedure Debugger in the ABAP Development Tools
369
11.2.3 Debugging in the SAP HANA Database Explorer
372
11.3 Performance Analysis
374
11.3.1 Runtime Measurement
374
11.3.2 Execution Plan
375
11.3.3 Plan Visualizer
377
11.3.4 SQL Analyzer of the SAP HANA Database Explorer
384
11.3.5 SQLScript Code Analyzer
386
11.4 Summary
390
Appendices
391
A Data Model for Task Management
393
A.1 Overview of the Tables
393
A.2 Data
394
A.3 Installation
395
B List of Abbreviations
397
C The Author
399
Index
401