4.3Database Procedures

Now we’ll describe how to implement database procedures in SAP HANA Studio using SQLScript.

In Section 4.2, you learned which commands are available in the SAP HANA database for creating database procedures and how you can use SQLScript within procedures. Now we’ll discuss how to implement a database procedure in SAP HANA Studio.

For this purpose, we use the SAP HANA Development perspective. We won’t detail the alternative usage of the Modeler perspective or the SAP HANA Web Workbench because we assume that as an ABAP developer, you’ll create database procedures from ABAP in most cases (at least as of Release 7.4). Creating database procedures from ABAP is discussed in Chapter 6.

You particularly use the SAP HANA Development perspective if you build applications based on SAP HANA Extended Application Services (SAP HANA XS; see Chapter 1, Section 1.1.4). If you create a database procedure in the SAP HANA Development perspective, the system stores it in the SAP HANA repository—as in the Modeler perspective—and creates corresponding runtime objects for the procedure in the database catalog upon activation.

Now we’ll cover the individual steps for creating and subsequently testing the read-only procedure DETERMINE_CONNECTION_UTILIZATION. The database procedure determines the percentage utilization for each flight connection. It has the following input and output parameters:

The output parameter ET_UTILIZATION is a table parameter that comprises the following columns:

You need to create an SAP HANA XS Project and a Repository Workspace to create a database procedure:

  1. Open the SAP HANA Development perspective, and navigate to the Project Explorer view.

  2. Create an XS Project by choosing the menu path, File • New • Other.

  3. The system displays the New XS Project dialog window (see Figure 4.4). Enter a Project name, for example, “chapter04”. Then click Next.

    Creating an XS Project

    Figure 4.4Creating an XS Project

  4. In the next dialog step, choose the Add Workspace button.

  5. The Create New Repository Workspace dialog window appears (see Figure 4.5). Select an SAP HANA system, and select the Use Default Workspace checkbox. Then click Finish.

  6. In the New XS Project dialog window, enter a package (in this example, “test.a4h.book”), and select the Add Project Folder as Subpackage checkbox. Click Next.

  7. In the last dialog step, deselect the two checkboxes, XS Application Access (.xsaccess) and XS Application Descriptor (.xsapp), and then click Finish.

    Creating a Repository Workspace

    Figure 4.5Creating a Repository Workspace

Next, you create the database procedure by following these steps:

  1. Choose the menu path, File • New • Other. Then choose the Stored Procedure wizard for creating a procedure.

  2. Enter the name of the procedure in the File Name field and fill in the Target Schema field in the dialog window that opens (see Figure 4.6). In this example, the name is “DETERMINE_CONNECTION_UTILIZATION” and the target schema is “_SYS_BIC”.

  3. You must also specify the File Format. Text and XML are available as file formats. You should usually use the Text file format because XML is outdated and is no longer recommended for use. However, the Text file format isn’t supported if you use the database procedure proxies (see Chapter 5, Section 5.2) so that you don’t have any choice when you use database procedure proxies. Close the dialog by clicking on Finish.

  4. Now the editor for editing the database procedure opens. Enter the source code of the procedure (for our example, you can find the complete code in Listing 4.8). Change the default schema if required.

    Creating a Database Procedure

    Figure 4.6Creating a Database Procedure

  5. Copy the source code, and then activate the database procedure.

    PROCEDURE "_SYS_BIC"."test.a4h.book.chapter04:
    :DETERMINE_CONNECTION_UTILIZATION"
    ( IN IV_MANDT NVARCHAR(3), IN IV_CARRID NVARCHAR(3),
    OUT ET_UTILIZATION TABLE (
    CARRID NVARCHAR(3),
    CONNID NVARCHAR(4),
    UTILIZATION DECIMAL(5,2) )
    )
    LANGUAGE SQLSCRIPT
    SQL SECURITY INVOKER
    DEFAULT SCHEMA "SAPH74"
    READS SQL DATA AS
    BEGIN
    ET_UTILIZATION = SELECT CARRID, CONNID,
    AVG(TO_DECIMAL(SEATSOCC + SEATSOCC_B + SEATSOCC_F) /
    TO_DECIMAL(SEATSMAX + SEATSMAX_B + SEATSMAX_F) * 100)
    AS UTILIZATION FROM SFLIGHT
    WHERE MANDT = :IV_MANDT
    AND CARRID = :IV_CARRID
    GROUP BY CARRID, CONNID;
    END;

    Listing 4.15Source Code for Sample Procedure

The system stores the database procedure in the SAP HANA repository and as a runtime object in the database catalog (within the _SYS_BIC schema in this example). You can check the creation of the runtime object as described in Chapter 2, Section 2.4.3.

You can use the SQL console to test the database procedure (more information on the SQL console is also available in Chapter 2, Section 2.4.3):

  1. Open the SQL console (e.g., by using the context menu of the SAP HANA System Library node within the Project Explorer view).

  2. Use the CALL statement to call the database procedure. Figure 4.7 shows the result.

    Testing a Database Procedure

    Figure 4.7Testing a Database Procedure

You can find more information, for example, on debugging of database procedures, in the SAP HANA Developer Guide (for SAP HANA Studio) at http://help.sap.com/hana.