DBPROCPARAMS 

Prerequisites

You have generated the demo data for the SQL Tutorial.

Log on to the demo database instance DEMODB as user MONA.

Create database procedures and database functions and use them. Proceed as described in SQL Tutorial, Database Procedures and Database Functions.

Examples

You can use the system table DBPROCPARAMS to determine the following database information, among other things:

●     All input parameters of the database function AVGPRICE in the HOTEL schema and the following parameter information: data type, length and the number of the affected parameter in the range of all parameters within that database function.

SELECT parametername, datatype, len, dec, pos
  FROM DOMAIN.DBPROCPARAMS
    WHERE schemaname = 'HOTEL'
      AND dbprocname = 'AVGPRICE'
      AND "IN/OUT-TYPE" = 'IN'

●     All output parameters of the database function AVG_PRICE in the HOTEL schema and the following parameter information: data type, length and ordinal number of the parameter within the database procedure.

SELECT parametername, datatype, len, dec, pos
  FROM DOMAIN.DBPROCPARAMS
    WHERE schemaname = 'HOTEL'
      AND dbprocname = 'AVG_PRICE'
      AND "IN/OUT-TYPE" = 'OUT'

●     Total number of parameters for the database procedure AVG_PRICE in the HOTEL schema

SELECT COUNT(*)
  FROM DOMAIN.DBPROCPARAMS
    WHERE schemaname = 'HOTEL'
      AND dbprocname = 'AVG_PRICE'

Database procedures: see DBPROCEDURES

Database functions: see FUNCTIONS