Stored Procedure

A stored procedure is a precompiled set of one or more SQL statements that are stored on the database server. It is a subroutine available to applications that access a relational database management system (RDBMS). Stored procedures can perform a variety of tasks such as data validation, data manipulation, and control logic.

Procedure Name

The procedure should be installed into the database before using it.

Input Parameters

All input parameters are optional. Each input parameter has three attributes:

• Name - mandatory string with a length of less than 255 characters

• Type - mandatory. Possible types are:

  • pdk.core.String and pdk.core.Array<String>

  • pdk.core.Integer and pdk.core.Array<Integer>

  • pdk.core.Boolean and pdk.core.Array<Boolean>

  • pdk.core.Float and pdk.core.Array<Float>

  • pdk.core.Array<Byte> - for BLOB data type

• Cast - optional. Sometimes it needs a type to be explicitly defined to be cast. For example:

  • string with date to be casted to datetime (::datetime operator for PostgreSQL)

  • string to VARCHAR2(100) for Oracle

Example

Procedure

CREATE PROCEDURE GetUserName (user_id IN NUMBER, user_name OUT VARCHAR2(200))
IS
BEGIN
  SELECT u_name
  INTO user_name
  FROM users
  WHERE id = user_id;
END;

Configuration:

Procedure configuration