Supported Types of Stored Procedures
Different database types have different types of stored procedures. See which ones Database Tour can work with.
Note: The current database user must have permissions to see, create or edit stored procedures.
PostgreSQL
- Functions
- Procedures
- Aggregates
- Triggers
Oracle
- Functions
- Procedures
- Packages
- Triggers
- Java Sources
SQL Server
- Functions
- Procedures
MySQL, MariaDB
- Functions
- Procedures
Interbase
- Procedures
Firebird
- Procedures
Creating Stored Procedures
To create a stored procedure from scratch, switch to the needed type of procedures in the Object Browser and click New button 
. The new SQL editor will be opened with the skeleton of the procedure. Complete the code and click Execute button 
. If necessary, read the documentation for your database.
You can also create a stored procedure using an existing one as a base. Open the existing procedure in edit mode (see below) and modify the procedure name and code appropriately.
Note: After executing your SQL code for stored procedures, if your database is not Oracle and you work in the no auto-commit mode, finish the transaction by clicking Commit button.
Deleting Stored Procedures
To delete (drop) a stored procedure, switch to the needed type of procedures in the Object Browser, select the needed procedure in list, and click Delete button.
To delete several stored procedures, right-click the list of procedures and choose Multiple Selection. Then select needed procedures and click Delete button.
Hint: Stored procedures can also be deleted by the corresponding SQL command.
Opening Existing Procedures
Switch to the needed type of procedures in the Object Browser. Locate the needed procedure and select it. This will open a new sub-window displaying the procedure's properties. Switch to the Script tab to view the procedure code.
Hint: If you double-click the procedure name in the procedure list, the sub-window will be opened with the Script tab activated.
Editing Existing Procedures
Open the needed procedure (see above) and switch to the Script tab. Then click Copy to Editor button 
. The new SQL editor will be opened with the full procedure code. Modify it appropriately and click Execute button .
Hint: In Oracle databases, you can quickly recompile the procedure without opening it, which is especially convenient for invalid objects when after changing the related database objects. Locate the procedure in the list, right-click it and choose Recompile. If everything is OK, the icon of the procedure will immediately change to normal (if it was invalid), otherwise the error message will appear and the procedure icon will change to reflect the invalid status of the procedure.
Useful Hints
When editing the code of procedures, functions or packages, it is possible to see the hints for variables:
You can customize the usage of SQL token hints by the corresponding options of SQL Editor.
When editing the code of procedures, functions or packages, use clickable variables:
You can customize the usage of SQL hyperlinks by the corresponding options of SQL Editor.
See also