Create Database Objects with SAP HANA Database Explorer

1. In the SAP HANA database explorer, select the previously created SAP HANA Cockpit or SAP HANA database and open a SQL console. Do not use the HDI container.

   

2. Create two users.

   SQL

   Copy

   CREATE USER USER1 PASSWORD Password1 no force_first_password_change;
   CREATE USER USER2 PASSWORD Password2 no force_first_password_change;
   

   With SAP HANA Cloud, HANA database, if the DBAdmin user has privileges on more than one USERGROUP, then it must be specified which USERGROUP USER1 and USER2 will be added to as shown below.

   SQL

   Copy

   CREATE USER USER1 PASSWORD Password1 no force_first_password_change SET USERGROUP DEFAULT;
   CREATE USER USER2 PASSWORD Password2 no force_first_password_change SET USERGROUP DEFAULT;
   

   ---

   The following shows an example of creating a USERGROUP, assigning USER1 to the group, and querying the password policy of the user.

   SQL

   Copy

   CREATE USERGROUP HOTEL_USER_GROUP SET PARAMETER 'minimal_password_length' = '8', 'force_first_password_change' = 'FALSE';
   ALTER USER USER1 SET USERGROUP HOTEL_USER_GROUP;
   SELECT * from "PUBLIC"."M_EFFECTIVE_PASSWORD_POLICY" where USER_NAME = 'USER1';
   

   ---

   It is recommended to not use the DBADMIN user for day to day operations in production environments. Having specific users for specific tasks also will aid in auditing. For additional details see Deactivate the DBADMIN User.

   For additional detail on creating users see CREATE USER Statement (Access Control) and CREATE USERGROUP Statement. Note that the user USER1 will be used in tutorial 5 and tutorial 7 of this tutorial group.

3. The list of users can be seen by executing the following statement:

   SQL

   Copy

   SELECT USER_NAME FROM USERS;
   

4. Create a schema named HOTEL. A schema provides a way to group database objects together. Privileges can be assigned to users directly (commented line below) or a better practice is to assign users to a role that has a set of privileges which is shown in the next step.

   SQL

   Copy

   CREATE SCHEMA HOTEL;
   --GRANT ALL PRIVILEGES ON SCHEMA HOTEL TO USER1;
   

   The following statement deletes the schema and objects it contains if you wish to remove the schema after completing the tutorials.

   SQL

   Copy

   DROP SCHEMA HOTEL CASCADE;
   

   For additional details see CREATE SCHEMA Statement.

5. Create two roles, assign privileges to the roles, and assign users to the roles.

   SQL

   Copy

   CREATE ROLE HOTEL_ADMIN;
   CREATE ROLE HOTEL_READER;
   
   GRANT ALL PRIVILEGES ON SCHEMA HOTEL TO HOTEL_ADMIN;
   GRANT SELECT ON SCHEMA HOTEL TO HOTEL_READER;
   
   GRANT HOTEL_ADMIN TO USER1;
   GRANT HOTEL_READER TO USER2;
   

   An example follows showing the privileges.

   SQL

   Copy

   CREATE TABLE HOTEL.TEST(
     myValue VARCHAR(50)
   );
   
   --USER1 has all privileges on the HOTEL schema
   CONNECT USER1 PASSWORD Password1;
   INSERT INTO HOTEL.TEST VALUES('Value1'); --succeeds
   SELECT * FROM HOTEL.TEST; --succeeds
   
   --USER2 can only select
   CONNECT USER2 PASSWORD Password2;
   SELECT * FROM HOTEL.TEST; --succeeds
   INSERT INTO HOTEL.TEST VALUES('Value2'); --fails
   CONNECT DBADMIN PASSWORD myPassword;
   
   --Remove the unused table, role and user
   DROP TABLE HOTEL.TEST;
   DROP ROLE HOTEL_READER;
   DROP USER USER2;
   

   For additional details see CREATE Role Statement and Managing SAP HANA Users. Users and roles can also be managed in the SAP HANA Cloud Cockpit.

   

1. Create tables that represent a basic hotel administration system by running the SQL statements below:

   SQL

   Copy

   CREATE COLUMN TABLE HOTEL.HOTEL(
     hno INTEGER PRIMARY KEY,
     name VARCHAR(50) NOT NULL,
     address VARCHAR(40) NOT NULL,
     city VARCHAR(30) NOT NULL,
     state VARCHAR(2) NOT NULL,
     zip VARCHAR(6),
     location ST_Point(4326)
   );
   CREATE COLUMN TABLE HOTEL.ROOM(
     hno INTEGER,
     type VARCHAR(6),
     free NUMERIC(3),
     price NUMERIC(6, 2),
     PRIMARY KEY (hno, type),
     FOREIGN KEY (hno) REFERENCES HOTEL.HOTEL
   );
   CREATE COLUMN TABLE HOTEL.CUSTOMER(
     cno INTEGER PRIMARY KEY,
     title VARCHAR(7),
     firstname VARCHAR(20),
     name VARCHAR(40) NOT NULL,
     address VARCHAR(40) NOT NULL,
     zip VARCHAR(6)
   );
   CREATE COLUMN TABLE HOTEL.RESERVATION(
     resno INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY,
     rno INTEGER NOT NULL,
     cno INTEGER,
     hno INTEGER,
     type VARCHAR(6),
     arrival DATE NOT NULL,
     departure DATE NOT NULL,
     PRIMARY KEY (
       "RESNO", "ARRIVAL"
     ),
     FOREIGN KEY(cno) REFERENCES HOTEL.CUSTOMER,
     FOREIGN KEY(hno) REFERENCES HOTEL.HOTEL
   );
   CREATE COLUMN TABLE HOTEL.MAINTENANCE(
     mno INTEGER PRIMARY KEY,
     hno INTEGER,
     description VARCHAR(100),
     date_performed DATE,
     performed_by VARCHAR(40)
   );
   

   To find the newly created tables in the database browser, select the Tables folder and set the schema to HOTEL. If needed, the contents of the database browser can be updated by pressing the refresh button.

   

   ---

   Should you wish to examine the SQL of a table, it can be viewed after selecting a table’s context menu Generate CREATE Statement.

   

2. Execute the following SQL statements to add data into the tables in the HOTEL schema.

   SQL

   Copy

   INSERT INTO HOTEL.HOTEL VALUES(10, 'Congress', '155 Beechwood St.', 'Seattle', 'WA', '98121', NEW ST_POINT('POINT(-122.347340 47.610546)', 4326));
   INSERT INTO HOTEL.HOTEL VALUES(11, 'Regency', '477 17th Avenue', 'Seattle', 'WA', '98177', NEW ST_POINT('POINT(-122.371104 47.715210)', 4326));
   INSERT INTO HOTEL.HOTEL VALUES(12, 'Long Island', '1499 Grove Street', 'Long Island', 'NY', '11716', NEW ST_POINT('POINT(-73.133741 40.783602)', 4326));
   INSERT INTO HOTEL.HOTEL VALUES(13, 'Empire State', '65 Yellowstone Dr.', 'Albany', 'NY', '12203', NEW ST_POINT('POINT(-73.816182 42.670334)', 4326));
   INSERT INTO HOTEL.HOTEL VALUES(14, 'Midtown', '12 Barnard St.', 'New York', 'NY', '10019', NEW ST_POINT('POINT(-73.987388 40.766153)', 4326));
   INSERT INTO HOTEL.HOTEL VALUES(15, 'Eighth Avenue', '112 8th Avenue', 'New York', 'NY', '10019', NEW ST_POINT('POINT(-73.982495 40.767161)', 4326));
   INSERT INTO HOTEL.HOTEL VALUES(16, 'Lake Michigan', '354 OAK Terrace', 'Chicago', 'IL', '60601', NEW ST_POINT('POINT(-87.623608 41.886403)', 4326));
   INSERT INTO HOTEL.HOTEL VALUES(17, 'Airport', '650 C Parkway', 'Rosemont', 'IL', '60018', NEW ST_POINT('POINT(-87.872209 41.989378)', 4326));
   INSERT INTO HOTEL.HOTEL VALUES(18, 'Sunshine', '200 Yellowstone Dr.', 'Clearwater', 'FL', '33755', NEW ST_POINT('POINT(-82.791689 27.971218)', 4326));
   INSERT INTO HOTEL.HOTEL VALUES(19, 'Beach', '1980 34th St.', 'Daytona Beach', 'FL', '32018', NEW ST_POINT('POINT(-81.043091 29.215968)', 4326));
   INSERT INTO HOTEL.HOTEL VALUES(20, 'Atlantic', '111 78th St.', 'Deerfield Beach', 'FL', '33441', NEW ST_POINT('POINT(-80.106612 26.312141)', 4326));
   INSERT INTO HOTEL.HOTEL VALUES(21, 'Long Beach', '35 Broadway', 'Long Beach', 'CA', '90804', NEW ST_POINT('POINT(-118.158403 33.786721)', 4326));
   INSERT INTO HOTEL.HOTEL VALUES(22, 'Indian Horse', '16 MAIN STREET', 'Palm Springs', 'CA', '92262', NEW ST_POINT('POINT(-116.543342 33.877537)', 4326));
   INSERT INTO HOTEL.HOTEL VALUES(23, 'Star', '13 Beechwood Place', 'Hollywood', 'CA', '90029', NEW ST_POINT('POINT(-118.295017 34.086975)', 4326));
   INSERT INTO HOTEL.HOTEL VALUES(24, 'River Boat', '788 MAIN STREET', 'New Orleans', 'LA', '70112', NEW ST_POINT('POINT(-90.076919 29.957531)', 4326));
   INSERT INTO HOTEL.HOTEL VALUES(25, 'Ocean Star', '45 Pacific Avenue', 'Atlantic City', 'NJ', '08401', NEW ST_POINT('POINT(-74.416135 39.361078)', 4326));
   INSERT INTO HOTEL.HOTEL VALUES(26, 'Bella Ciente', '1407 Marshall Ave', 'Longview', 'TX', '75601', NEW ST_POINT('POINT(-94.724051 32.514183)', 4326));
   
   INSERT INTO HOTEL.ROOM VALUES(10, 'single', 20, 135.00);
   INSERT INTO HOTEL.ROOM VALUES(10, 'double', 45, 200.00);
   INSERT INTO HOTEL.ROOM VALUES(12, 'single', 10, 70.00);
   INSERT INTO HOTEL.ROOM VALUES(12, 'double', 13, 100.00);
   INSERT INTO HOTEL.ROOM VALUES(13, 'single', 12, 45.00);
   INSERT INTO HOTEL.ROOM VALUES(13, 'double', 15, 80.00);
   INSERT INTO HOTEL.ROOM VALUES(14, 'single', 20, 85.00);
   INSERT INTO HOTEL.ROOM VALUES(14, 'double', 35, 140.00);
   INSERT INTO HOTEL.ROOM VALUES(15, 'single', 50, 105.00);
   INSERT INTO HOTEL.ROOM VALUES(15, 'double', 230, 180.00);
   INSERT INTO HOTEL.ROOM VALUES(15, 'suite', 12, 500.00);
   INSERT INTO HOTEL.ROOM VALUES(16, 'single', 10, 120.00);
   INSERT INTO HOTEL.ROOM VALUES(16, 'double', 39, 200.00);
   INSERT INTO HOTEL.ROOM VALUES(16, 'suite', 20, 500.00);
   INSERT INTO HOTEL.ROOM VALUES(17, 'single', 4, 115.00);
   INSERT INTO HOTEL.ROOM VALUES(17, 'double', 11, 180.00);
   INSERT INTO HOTEL.ROOM VALUES(18, 'single', 15, 90.00);
   INSERT INTO HOTEL.ROOM VALUES(18, 'double', 19, 150.00);
   INSERT INTO HOTEL.ROOM VALUES(18, 'suite', 5, 400.00);
   INSERT INTO HOTEL.ROOM VALUES(19, 'single', 45, 90.00);
   INSERT INTO HOTEL.ROOM VALUES(19, 'double', 145, 150.00);
   INSERT INTO HOTEL.ROOM VALUES(19, 'suite', 60, 300.00);
   INSERT INTO HOTEL.ROOM VALUES(20, 'single', 11, 60.00);
   INSERT INTO HOTEL.ROOM VALUES(20, 'double', 24, 100.00);
   INSERT INTO HOTEL.ROOM VALUES(21, 'single', 2, 70.00);
   INSERT INTO HOTEL.ROOM VALUES(21, 'double', 10, 130.00);
   INSERT INTO HOTEL.ROOM VALUES(22, 'single', 34, 80.00);
   INSERT INTO HOTEL.ROOM VALUES(22, 'double', 78, 140.00);
   INSERT INTO HOTEL.ROOM VALUES(22, 'suite', 55, 350.00);
   INSERT INTO HOTEL.ROOM VALUES(23, 'single', 89, 160.00);
   INSERT INTO HOTEL.ROOM VALUES(23, 'double', 300, 270.00);
   INSERT INTO HOTEL.ROOM VALUES(23, 'suite', 100, 700.00);
   INSERT INTO HOTEL.ROOM VALUES(24, 'single', 10, 125.00);
   INSERT INTO HOTEL.ROOM VALUES(24, 'double', 9, 200.00);
   INSERT INTO HOTEL.ROOM VALUES(24, 'suite', 78, 600.00);
   INSERT INTO HOTEL.ROOM VALUES(25, 'single', 44, 100.00);
   INSERT INTO HOTEL.ROOM VALUES(25, 'double', 115, 190.00);
   INSERT INTO HOTEL.ROOM VALUES(25, 'suite', 6, 450.00);
   
   INSERT INTO HOTEL.CUSTOMER VALUES(1000, 'Mrs', 'Jenny', 'Porter', '1340 N. Ash Street, #3', '10580');
   INSERT INTO HOTEL.CUSTOMER VALUES(1001, 'Mr', 'Peter', 'Brown', '1001 34th St., APT.3', '48226');
   INSERT INTO HOTEL.CUSTOMER VALUES(1002, 'Company', NULL, 'Datasoft', '486 Maple St.', '90018');
   INSERT INTO HOTEL.CUSTOMER VALUES(1003, 'Mrs', 'Rose', 'Brian', '500 Yellowstone Drive, #2', '75243');
   INSERT INTO HOTEL.CUSTOMER VALUES(1004, 'Mrs', 'Mary', 'Griffith', '3401 Elder Lane', '20005');
   INSERT INTO HOTEL.CUSTOMER VALUES(1005, 'Mr', 'Martin', 'Randolph', '340 MAIN STREET, #7', '60615');
   INSERT INTO HOTEL.CUSTOMER VALUES(1006, 'Mrs', 'Sally', 'Smith', '250 Curtis Street', '75243');
   INSERT INTO HOTEL.CUSTOMER VALUES(1007, 'Mr', 'Mike', 'Jackson', '133 BROADWAY APT. 1', '45211');
   INSERT INTO HOTEL.CUSTOMER VALUES(1008, 'Mrs', 'Rita', 'Doe', '2000 Humboldt St., #6', '97213');
   INSERT INTO HOTEL.CUSTOMER VALUES(1009, 'Mr', 'George', 'Howe', '111 B Parkway, #23', '75243');
   INSERT INTO HOTEL.CUSTOMER VALUES(1010, 'Mr', 'Frank', 'Miller', '27 5th St., 76', '95054');
   INSERT INTO HOTEL.CUSTOMER VALUES(1011, 'Mrs', 'Susan', 'Baker', '200 MAIN STREET, #94', '90018');
   INSERT INTO HOTEL.CUSTOMER VALUES(1012, 'Mr', 'Joseph', 'Peters', '700 S. Ash St., APT.12', '92714');
   INSERT INTO HOTEL.CUSTOMER VALUES(1013, 'Company', NULL, 'TOOLware', '410 Mariposa St., #10', '20019');
   INSERT INTO HOTEL.CUSTOMER VALUES(1014, 'Mr', 'Antony', 'Jenkins', '55 A Parkway, #15', '20903');
   
   INSERT INTO HOTEL.RESERVATION VALUES(1, 100, 1000, 11, 'single', '2020-12-24', '2020-12-27');
   INSERT INTO HOTEL.RESERVATION VALUES(2, 110, 1001, 11, 'double', '2020-12-24', '2021-01-03');
   INSERT INTO HOTEL.RESERVATION VALUES(3, 120, 1002, 15, 'suite', '2020-11-14', '2020-11-18');
   INSERT INTO HOTEL.RESERVATION VALUES(4, 130, 1009, 21, 'single', '2019-02-01', '2019-02-03');
   INSERT INTO HOTEL.RESERVATION VALUES(5, 150, 1006, 17, 'double', '2019-03-14', '2019-03-24');
   INSERT INTO HOTEL.RESERVATION VALUES(6, 140, 1013, 20, 'double', '2020-04-12', '2020-04-30');
   INSERT INTO HOTEL.RESERVATION VALUES(7, 160, 1011, 17, 'single', '2020-04-12', '2020-04-15');
   INSERT INTO HOTEL.RESERVATION VALUES(8, 170, 1014, 25, 'suite', '2020-09-01', '2020-09-03');
   INSERT INTO HOTEL.RESERVATION VALUES(9, 180, 1001, 22, 'double', '2020-12-23', '2021-01-08');
   INSERT INTO HOTEL.RESERVATION VALUES(10, 190, 1013, 24, 'double', '2020-11-14', '2020-11-17');
   
   INSERT INTO HOTEL.MAINTENANCE VALUES(10, 24, 'Replace pool liner and pump', '2019-03-21', 'Discount Pool Supplies');
   INSERT INTO HOTEL.MAINTENANCE VALUES(11, 25, 'Renovate the bar area.  Replace TV and speakers', '2020-11-29', 'TV and Audio Superstore');
   INSERT INTO HOTEL.MAINTENANCE VALUES(12, 26, 'Roof repair due to storm', null, null);
   

   For additional details see CREATE Table statement and Insert Statement.

   Note that the identifiers such as table names are automatically upper cased unless they are within "".

   SQL

   Copy

   SELECT * FROM HoTeL.RoOm;  --succeeds
   SELECT * FROM "HoTeL"."RoOm"; --fails
   SELECT * FROM "HOTEL"."ROOM"; --succeeds
   

   For further details, consult Identifiers and case sensitivity.

Auto-commit is a setting that when enabled, causes each SQL statement to be immediately committed to the database. When auto-commit is turned off, multiple statements can be executed and then they can all be committed together or they can all be rolled back. There are two auto-commit settings in an SAP HANA database. The first setting which can be set in the SQL Console, applies to SQL statements that manipulate data such as insert, update, or delete statements. These types of statements are known as Data Manipulation Language (DML). The second setting can be set via SQL applies to SQL statements that modify database schema such create table statements or alter table statements. These types of statements are known as Data Definition Language (DDL).

The following steps will demonstrate these settings.

1. Execute the following SQL statements. By default, auto-commit is on.

   SQL

   Copy

   CREATE COLUMN TABLE HOTEL.TEST1(
     ID INTEGER PRIMARY KEY,
     FIRSTNAME VARCHAR(50) NOT NULL
   );
   INSERT INTO HOTEL.TEST1 VALUES (1, 'Bob');
   INSERT INTO HOTEL.TEST1 VALUES (2, 'Sue');
   INSERT INTO HOTEL.TEST1 VALUES (2, 'John');
   

   An error will occur and the table TEST1 will exist with two rows. To undo the operation, the table can be deleted. Notice that the table TEST1 appears in the catalog browser.

   

2. In the SQL Console, set auto-commit off.

   

3. Execute the following SQL statements.

   SQL

   Copy

   CREATE COLUMN TABLE HOTEL.TEST2(
   ID INTEGER PRIMARY KEY,
       FIRSTNAME VARCHAR(50) NOT NULL
   );
   INSERT INTO HOTEL.TEST2 VALUES (1, 'Bob');
   INSERT INTO HOTEL.TEST2 VALUES (2, 'Sue');
   INSERT INTO HOTEL.TEST2 VALUES (2, 'John');
   

   An error will occur and you can then decide to undo all the inserted rows from the table TEST1 by executing a ROLLBACK; or you have the option to keep the successfully inserted rows by executing a COMMIT.

   

   Note that until a COMMIT is executed, the table will appear to have no rows inserted when viewed from another SQL Console or database connection.

   Additional details can be found at ROLLBACK Statement and COMMIT Statement.

4. Execute the following SQL statements.

   SQL

   Copy

   SET TRANSACTION AUTOCOMMIT DDL OFF;
   CREATE COLUMN TABLE HOTEL.TEST3(
     ID INTEGER PRIMARY KEY,
     FIRSTNAME VARCHAR(50) NOT NULL
   );
   INSERT INTO HOTEL.TEST3 VALUES (1, 'Bob');
   INSERT INTO HOTEL.TEST3 VALUES (2, 'Sue');
   SELECT * FROM HOTEL.TEST3;
   INSERT INTO HOTEL.TEST3 VALUES (2, 'John');
   

   An error will occur and you can then decide to undo the inserted rows and the table creation by executing a ROLLBACK; or you have the option to keep the successfully created table and rows by executing a COMMIT.

   Note that until a COMMIT is executed, the table will not appear from another SQL Console or in the catalog browser.

   The following query can be used to determine if AUTOCOMMIT DDL is enabled.

   SQL

   Copy

   SELECT 	key, value
   FROM m_session_context
   WHERE connection_id = current_connection
   	AND key = 'DDL_AUTO_COMMIT';
   

   Additional details can be found at SET TRANSACTION AUTOCOMMIT DDL Statement.

5. Ensure both settings are back to their default values before continuing.

   
   SQL

   Copy

   SET TRANSACTION AUTOCOMMIT DDL ON;
   

Partitions can be created to divide the data in a large table into smaller parts.

1. Execute the following SQL statement to create one partition that contains older reservations and one that contains reservations made in 2019 or later.

   SQL

   Copy

   ALTER TABLE HOTEL.RESERVATION PARTITION BY RANGE(ARRIVAL)
   ((
       PARTITION '2000-01-01' <= VALUES < '2020-01-01',
       PARTITION OTHERS
   ));
   

   In the example in this section, older reservation data will be stored on disk rather than in memory. Other reasons for partitioning include load balancing across multiple hosts and the 2 billion row size limit on a non-partitioned table. For more details see Table Partitioning and SAP Note: 2044468 - FAQ: SAP HANA Partitioning.

2. Execute the following SQL to make the partition containing older reservations loadable from disk using Native Storage Extensions (NSE).

   SQL

   Copy

   ALTER TABLE HOTEL.RESERVATION ALTER PARTITION 1 PAGE LOADABLE;
   

   The partition information can be seen in the Runtime Information tab of the reservation table, which can be shown by right-clicking on the reservation table and choosing Open.

   

   The following select statement shows the load unit type of columns in the schema HOTEL.

   SQL

   Copy

   SELECT TABLE_NAME, PART_ID, COLUMN_NAME, LOAD_UNIT FROM M_CS_COLUMNS where schema_Name = 'HOTEL';
   

   

   Notice above that the partition 1 (containing older reservations) is page loadable and partition 2 (containing recent reservations) is column loadable.

For further information see Reduce the Memory Footprint Using Page-Loadable Columns and SAP Note: 2799997 - FAQ: SAP HANA Native Storage Extension (NSE).

Another option for data that is accessed less frequently is the SAP HANA Data Lake. Additional information on when to use Native Store Extensions and Data Lake can be found at Introduction to SAP HANA Cloud - Storage Options.

1. Views can be created to combine columns from multiple tables into one view or to provide access to certain columns of a table. Executing the following SQL statements creates a view that displays all information from the reservation table. The joins allow for more information about the customer and hotel to be displayed.

   SQL

   Copy

   CREATE OR REPLACE VIEW HOTEL.RESERVATION_VIEW AS
   	SELECT
   		R.RESNO,
   		H.NAME AS HOTEL_NAME,
       R.ARRIVAL,
       R.DEPARTURE,
   		CUS.TITLE,
   		CUS.FIRSTNAME,
   		CUS.NAME AS CUSTOMER_NAME,
   		CUS.ADDRESS AS CUSTOMER_ADDRESS
   	FROM HOTEL.RESERVATION R
   		LEFT JOIN HOTEL.HOTEL H ON H.HNO = R.HNO
   		LEFT JOIN HOTEL.CUSTOMER CUS ON CUS.CNO = R.CNO
     ORDER BY H.NAME, R.ARRIVAL DESC;
   

2. The result of querying RESERVATION_VIEW can be seen below.

   SQL

   Copy

   SELECT * FROM HOTEL.RESERVATION_VIEW;
   

   

3. The following view displays information about hotel rooms.

   SQL

   Copy

   CREATE OR REPLACE VIEW HOTEL.HOTEL_ROOMS_VIEW AS
   	SELECT
   		H.NAME AS HOTEL_NAME,
   		R.TYPE,
   		R.FREE,
   		R.PRICE
   	FROM HOTEL.ROOM R
   		LEFT JOIN HOTEL.HOTEL H ON R.HNO = H.HNO
           ORDER BY H.NAME;
   

   To find the newly created views in the database browser, select the Views folder.

   

4. The result of calling HOTEL_ROOMS_VIEW can be seen below.

   SQL

   Copy

   SELECT * FROM HOTEL.HOTEL_ROOMS_VIEW;
   

   

   For additional details see CREATE VIEW Statement (Data Definition).

1. User-defined functions and procedures can be used to save a set of SQL statements. Functions are considered read-only in that they cannot make modifications to the data. Stored procedures can modify the data through the use of DDL or DML statements.

   Execute the following SQL to create a function that calculates the average price of a specific room type.

   SQL

   Copy

   CREATE OR REPLACE FUNCTION HOTEL.AVERAGE_PRICE(room_type CHAR(6))
   RETURNS avg_price NUMERIC(6, 2)
   AS
   BEGIN
       DECLARE EXIT HANDLER FOR SQLEXCEPTION avg_price := '-1';
       SELECT TO_DECIMAL(ROUND(sum(PRICE)/COUNT(*), 2, ROUND_HALF_UP)) INTO avg_price FROM HOTEL.ROOM WHERE TYPE = :room_type GROUP BY TYPE;
   END;
   

   To find the newly created function in the database browser, select the Functions folder.

   

   ---

   Should you wish to examine the SQL of a function, it can be viewed after selecting a functions’s context menu Open.

   

2. An example of how to use the Average_Price function is shown below:

   SQL

   Copy

   SELECT HOTEL.AVERAGE_PRICE('single') from dummy;
   

   

   For additional details see User-Defined Functions.

3. Functions such as the one above that return single defined values are know as scalar user defined functions. Table user defined functions can return a tabular result set. The following is an example of user defined function that returns results in a table format.

   SQL

   Copy

   CREATE FUNCTION HOTEL.PAST_VISITS (CNO INT)
   RETURNS TABLE (FIRSTNAME VARCHAR(20), NAME VARCHAR(40), HOTEL_NAME VARCHAR(50), ARRIVAL DATE, STAY_DURATION INT) LANGUAGE SQLSCRIPT AS
   BEGIN
       RETURN SELECT C.FIRSTNAME, C.NAME, H.NAME AS HOTEL_NAME, R.ARRIVAL, DAYS_BETWEEN(R.ARRIVAL, R.DEPARTURE) as STAY_DURATION
           FROM HOTEL.HOTEL H, HOTEL.RESERVATION R, HOTEL.CUSTOMER C
           WHERE C.CNO = R.CNO AND R.ARRIVAL <= CURRENT_DATE AND R.CNO = :CNO AND R.HNO= H.HNO
           ORDER BY R.ARRIVAL DESC;
   END;
   
   SELECT * FROM "HOTEL"."PAST_VISITS"(1001);
   

4. Stored procedures can be used to save SQL statements. They can contain control statements and can have parameters. The following stored procedure generates and inserts reservations into the HOTEL.RESERVATION table. In order to run, a parameter must be entered that identifies the number of reservations to insert.

   SQL

   Copy

   CREATE OR REPLACE PROCEDURE HOTEL.RESERVATION_GENERATOR(
       IN numToGenerate INTEGER
   )
   	LANGUAGE SQLSCRIPT AS
   BEGIN
   	USING SQLSCRIPT_PRINT AS PRTLIB;
   	DECLARE val INT :=0;
   	DECLARE stmt VARCHAR(256) := '';
   	DECLARE rno INT :=0;
   	DECLARE cno INT :=0;
   	DECLARE hno INT :=0;
   	DECLARE roomType STRING := '';
   	DECLARE arriveDate DATE := null;
   	DECLARE arriveDateString STRING := '';
   	DECLARE departDate DATE := null;
   	DECLARE departDateString STRING := '';
   	DECLARE randomDaysFromCurrent INT :=0;
   	DECLARE randomLengthOfStay INT :=0;
   	DECLARE rType INT :=0;
   	DECLARE EXIT HANDLER FOR SQL_ERROR_CODE 301
   	SELECT ::SQL_ERROR_CODE, ::SQL_ERROR_MESSAGE FROM DUMMY;
   	WHILE (val < numToGenerate) DO
   		-- generate random room number from 100-300
   		rno := FLOOR(RAND_SECURE() * 201) + 100;
   		-- generate random customer number from 1000-1014
   		cno := FLOOR(RAND_SECURE() * 15) + 1000;
   		-- generate random hotel number from 10-26
   		hno := FLOOR(RAND_SECURE() * 17) + 10;
   		-- generate random number from 1-3 to determine room type
   		rType := FLOOR(RAND_SECURE() * 3) + 1;
   		IF (rType = 1) THEN
   			roomType := '''single''';
   		ELSEIF (rType = 2) THEN
   			roomType := '''double''';
   		ELSEIF (rType = 3) THEN
   			roomType := '''suite''';
   		END IF;
   
           -- generate random number of days to be used for arrival date.  
   		-- date range is one year in the past to one year in the future
   		randomDaysFromCurrent := FLOOR(RAND_SECURE() * 730) + 1 - 365;
   		arriveDate := ADD_DAYS( TO_DATE( CURRENT_DATE, 'YYYY-MM-DD' ), randomDaysFromCurrent );
   		arriveDateString := '''' || TO_VARCHAR( arriveDate, 'YYYY-MM-DD' ) || '''';
   		-- generate a random number of days to stay
   		randomLengthOfStay := FLOOR(RAND_SECURE() * 7) + 1;
   		departDate := ADD_DAYS( arriveDate, randomLengthOfStay );
   		departDateString := '''' || TO_VARCHAR( departDate, 'YYYY-MM-DD' ) || '''';
   
           -- Reservations Columns: RNO, CNO, HNO, Type, Arrival, Departure
   		stmt := 'INSERT INTO HOTEL.RESERVATION (RNO, CNO, HNO, TYPE, ARRIVAL, DEPARTURE) VALUES(' || rno || ',' || cno || ',' || hno || ',' || roomType || ',' || arriveDateString || ',' || departDateString || ');';
   		PRTLIB:PRINT_LINE(stmt);
   		EXEC(stmt);
   		val := val + 1;
   	END WHILE;
     PRTLIB:PRINT_LINE('Rows inserted: ' || val);
   END;
   

5. To run this procedure, execute the SQL statement below.

   SQL

   Copy

   CALL HOTEL.RESERVATION_GENERATOR(NUMTOGENERATE => 100);
   

   

   Once the procedure has been successfully executed, notice that dates both in the past and future are generated.

   Another way to run this procedure is to generate a CALL statement using the UI. Right-click the procedure name and select Generate CALL Statement With UI.

   

   The parameters are listed in the parameters tab and once they are manually filled in or loaded from a file, the procedure can be called.

   For additional details see Procedures.

   Procedures can also be scheduled in SAP HANA Cloud. An example follows. For additional details see Scheduling Administrative Tasks and CREATE SCHEDULER JOB Statement.

   SQL

   Copy

      SELECT current_date, current_time FROM dummy;  --be sure to schedule an event in the future
      CREATE SCHEDULER JOB GEN_RESERVATIONS_JOB CRON '2021 12 23 * 14 25 0' ENABLE PROCEDURE "HOTEL"."RESERVATION_GENERATOR" PARAMETERS numtogenerate=10;
      SELECT * FROM SCHEDULER_JOBS WHERE SCHEDULER_JOB_NAME = 'GEN_RESERVATIONS_JOB';
   

Congratulations! You have now created tables and inserted data, as well as created partitions, views, functions, and stored procedures.