Entering content frame

This graphic is explained in the accompanying text DBPROCPARAMS Locate the document in the library structure

Prerequisites

You have generated the Structure linkdemo 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, Structure linkDatabase Procedures and Structure linkDatabase 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'

Note

Database procedures: see DBPROCEDURES

Database functions: see FUNCTIONS

 

Leaving content frame