Procedure/Function

Select database in the drop-down list of repository objects. When database changes, the driver specified in the selected database settings is selected automatically.

To quickly select an object, enter object's name/identifier/key in the search box depending on display settings. Search is executed automatically while the searched text is entered into the search string. The list will display the objects, which names/identifiers/keys contain the entered text.

To set up displaying of repository objects, click the Show Object button and select display option in the drop-down menu:

• Name. Objects are displayed with their names. Default option.

• Identifier. Objects are displayed with their identifiers.

• Key. Objects are displayed with their keys.

Several options can be selected. Identifier and key will be specified in brackets.

To reset selection for selected objects, click the Clear button.

Specify the physical object name (procedure or function), which is created on the DBMS server and which corresponds to the one created in the repository. Name will match the identifier by default.

Select the type of created object in the drop-down list: procedure or function.

Select the driver type in the drop-down list. The default driver used by the selected database is shown in bold. If required, one can select another driver and determine the SQL query text for this driver. It is relevant if the repository will be used on servers with different DBMS and database settings in repository will be changed.

The ClickHouse driver is available only when the Function object type is selected.

To show or hide the driver list, use the Hide Driver List/Show Driver List button.

NOTE. Creating of procedures and functions is unavailable in SQLite DBMS because such object type is missing in this DBMS.

 

Enter the header part, containing input and output parameters if required and procedure (function) body. Features of working with the field:

• The script text should be written in the SQL language, which syntax depends on the selected DBMS.

• The header part containing initialization code and physical name is generated automatically. They should not be specified in the script.

• Create a parameter with the Function Result value for the function.

• The description of parameters of the same type (input and output) should be in the same order as they are created on the Object Parameters page.

NOTE. If the PostgreSQL driver is selected, the first string of the script should contain the list of parameters and it should be in one string.

The button is used to refresh procedure or function text from database. Clicking this button returns a request to confirm the operation. If the answer is Yes, procedure or function text will be refreshed from database.

The button can be used to apply the specified script text for all DBMS. Clicking this button returns a request to confirm the operation. Confirmation is required if at least one driver other than the current one, has non-empty query text.

1. In the object navigator create the Procedure object.

2. Enter name and identifier of the created object on the Basic Properties page of the procedure wizard.

3. Add three parameters on the Object Parameters page of the procedure wizard:

Name	Identifier	Data type	Select the checkbox
Input	INPARAM1	Integer	Input
Function result	REZULT	String	Function result
Input2	IINPARAM2	Integer	Input

4. Select a database, driver, object type, and enter physical name on the Procedure/Function page of the procedure wizard.

5. In the Script box enter the following code:

(p1 in int, p2 in int)

    return int

    as

        l_number int;

    begin

        l_number := p1+p2;

        return l_number;

    exception

        when others then

            return 0;

    end;

NOTE. On enumerating input/output parameters the function parameters are used in the order from the Object Parameters page. Therefore the input parameter with the INPARAM1 identifier is sent to the p1 variable of the function, and the parameter with the INPARAM2 identifier is sent to the p2 variable.

Click the Finish button. A physical object is created in the DBMS repository.

1. In the object navigator create the Procedure object.

2. Enter name and identifier of the created object on the Basic Properties page of the procedure wizard.

3. Add three parameters on the Object Parameters page of the procedure wizard:

Name	Identifier	Data type	Select the checkbox
Input	PIN	Integer	Input
Input2	PIN2	Integer	Input
Input3-Output	PINOUT	Integer	Input, Output

4. Select a database, driver, object type, and enter physical name on the Procedure/Function page of the procedure wizard.

5. In the Script box enter the following code:

(PIn in number, PIN2 in varchar2, PINOUT in out varchar2)

    is

    begin

        insert into EXECPR (field, field1, field2) values (PIN,PIN2, PINOUT);

    end;

where:

• PIN, PIN2, PINOUT. Procedure parameter identifiers.

• EXECPR. Identifier of the table, to which values are added.

• field, field1, field2. Table fields, to which values of the PIN, PIN2, PINOUT parameters are placed.

Click the Finish button. A physical object is created in the DBMS repository.

For details see the Example of Creating a Procedure subsection.