[ Master documentation index ]
Introduction
Many databases offers the capability to store programs in the
database. These programs may be of two types: functions and procedures.
The difference is that a function returns a value while a procedure
does not.
In addition, some databases offer the package concept, which means
that a collection of functions and/or procedures are grouped together
in
one unit. A package is the interface describing the functions and
procedures, while the package body contains the implementation.
You can use DbVisualizer actions to create and drop procedural
object of these types, and use the
procedure editor to browse, edit and compile these object types.
The examples throughout this document refer to the procedure
object type, but all described features can also be applied to
function, package and package body objects.
Create Procedure
To create a new procedure, simply select the Procedures node in the objects tree and choose Create Procedure from its action menu.
Figure: The actions menu for the Procedures node
Next,
a dialog is displayed in which you enter the procedure name and the
parameters for the new procedure. This data forms the interface for the
procedure.
Figure: The create procedure dialog
Use the buttons to the right of the parameter list to insert, remove
and move the parameters. For every parameter, you must supply its Name. Leaving out the Data Type results in the VARCHAR data type. The Direction is by default set to IN.
You can also enter the implementation source code in the Source area,
but be aware that it is saved as-is for some databases; if there are
any errors, you may not be notified when you save the procedure. You
need to verify the status and fix errors, if any, as described in the
next section. As an alternative, you may therefore like to leave just
the default sample implementation in the Create Procedure dialog and
enter the real the implementation later.
Click Execute in the dialog to create the new procedure.
Figure: The newly created procedure
Selecting the newly created procedure in the tree will show the source for it in the procedure editor.
Edit and Compile
Note: This editor is currently supported only for Oracle
databases! For other databases, you need to use the SQL Commander and
commands like ALTER PROCEDURE to edit and compile the procedure. See
your database documentation for details.
The editor has a toolbar with various actions to compile the procedure,
save and load the source to/from file, show parameter information and
common editing operations. The Status indicator shows whether the procedure is valid or invalid based on last compilation.
Edit the source code and compile the procedure when you are happy with the code, using the Compile toolbar button.
Figure: Compiling procedure with errors
If error(s) occur during compilation, the error list appears
below the editor. It show the row number for the error in the source
editor and an error message. When you click the error in the list, the
corresponding row is highlighted in the editor.
In addition to the Status indicator in the editor, the object
icon in
the tree shows a little red cross for invalid procedures. The
figure below shows the result of correcting the errors and recompiling
the procedure:
Figure: Compiling procedure with successful result
The status indicator now shows that the procedure is VALID.
Running in SQL Commander
You can now test the procedure in the SQL Commander as shown in the next screenshot.
Figure: Running the procedure in SQL Commander
The figure shows the invocation of the update_status
procedure
with parameter values meaning that all IDs in the ORDERS table between
1200 and 2000 should be set to "OPEN". The second statement selects
from the
updated table.
Copyright © 2007 Onseven Software AB. All rights reserved.
