Managing Database Procedures

12345678910
CREATE PROCEDURE <procedure_name>
[(<parameter_clause>)}
[LANGUAGE <lang>]
[SQL SECURITY <mode>]
[DEFAULT SCHEMA <default_schema_name>]
[READS SQL DATA]
AS
BEGIN [SEQUENTIAL EXECUTION]
<procedure body>
END;

1234567891011121314151617181920212223
-- create a read-only procedure that passes in a unit of time and converts
-- the overtime hours of officials into the target unit; use the 
-- previously-created Convert_Hours function to provide the time conversion

CREATE PROCEDURE Convert_OfficialsHours
(IN im_to VARCHAR(1),
 OUT ex_official TABLE 
     (PNr NVARCHAR(3),
      Name NVARCHAR(20), 
      Overtime DEC(5,2)
      )
)
LANGUAGE SQLSCRIPT
SQL SECURITY INVOKER
READS SQL DATA 
AS 

BEGIN

 ex_official = SELECT PNr, Name, Convert_Hours(Overtime, :im_to) AS Overtime
                   FROM :lt_official;

END;

Procedures can also have parameters which are both input and output. This is why the keywords IN, OUT, and INOUT are necessary in the definition of the signature to indicate the kind of parameter. IN is the default.

The following procedure uses declarative logic only:

123456789101112131415161718192021222324252627282930313233343536373839
-- create a read-only procedure that provides separate counts of privately-owned and
-- company-owned cars; exclude cars that are reported as stolen

CREATE PROCEDURE CarOwner_Count 
(OUT ex_company_cars INTEGER, 
 OUT ex_private_cars INTEGER )
LANGUAGE SQLSCRIPT
SQL SECURITY INVOKER
READS SQL DATA
AS 
BEGIN

-- identify the owner IDs of company cars, which have a NULL value of owner birthday
lt_companies = SELECT OwnerID 
                   FROM Owner 
               WHERE Birthday IS NULL;

-- identify the owner IDs of personal cars, which have a non-NULL value owner birthday
lt_persons = SELECT OwnerID 
                 FROM Owner
             WHERE Birthday IS NOT NULL;
-- identify the plate numbers of cars that are not reported as stolen
lt_cars = SELECT * 
              FROM Car c 
          WHERE PlateNumber NOT IN (SELECT PlateNumber 
                                    FROM Stolen);

-- populate the output parameter values
SELECT COUNT(*) INTO ex_company_cars
    FROM :lt_cars JOIN :lt_companies ON Owner = OwnerID;

SELECT COUNT(*) INTO ex_private_cars
    FROM :lt_cars JOIN :lt_persons ON Owner = OwnerID;

END;

-- after the procedure is created, execute it via a CALL statement:

CALL CarOwner_Count(?,?);

The procedure CarOwner_Count uses declarative logic only. This allows the execution of the first three queries and assignments in parallel because their results are mutually independent.

If they have been created directly using SQL, procedures can be deleted using the DROP PROCEDURE command:

1
DROP PROCEDURE <procedure name>;

To call a procedure, use the CALL statement. There are different options depending on where you CALL the procedure and for passing parameters. Arguments for tabular IN parameters can either be physical tables, views, or table variables. The actual value passed for tabular OUT parameters must be ‘?’ when calling a procedure in the SQL Console.

Passing parameters by position

Code Snippet

Copy codeSwitch to dark mode

1
CALL Convert_OfficialsHours(NULL,'d',?)

Passing parameters by name – allows ignoring their order

Code Snippet

Copy codeSwitch to dark mode

12
  CALL Convert_OfficialsHours(im_filter=>NULL, im_to=>'d',
                              ex_official=>?)

In another procedure:

Code Snippet

Copy codeSwitch to dark mode

12
  CALL Convert_OfficialsHours(im_filter=>NULL, im_to=>'d',
                              ex_official=>lt_official)

WITH OVERVIEW defines that the result of the procedure call is stored in one or more physical tables. These tables are dropped automatically when the database session is closed.

Calling a procedure WITH OVERVIEW returns one result set that details which physical table contains the result of each table output variable. Scalar outputs are represented as temporary tables with only one cell. When you pass existing tables to the output parameters, WITH OVERVIEW inserts the result set tuples of the procedure into the provided tables. When you pass '?' to the output parameters, temporary tables holding the result sets are generated.

A procedure should always be created as a source file in the Development view of SAP Web IDE. Click a project folder that belongs to a HDB module and use the menu option New → Procedure. You don’t need to specify the extension .hdbprocedure as this is automatically appended to the name you provide for the file.

As this is the recommended approach to define a procedure, let’s take a look at it now.

The file opens with basic shell code for you to complete. When you have finished writing the code, you simply save, then build the object. Assuming you have no errors, a runtime procedure is created in the container that belongs to the project. You can then open the SQL Console against the container and write some SQL to test your procedure.

As with a function built using a source file, you do not drop the procedure directly using a Drop statement in SQL. If you wish to remove the procedure you simply delete the source file. Then, in the next build of the folder in which it was stored, the run-time object is then deleted.