[ Master documentation index ]
Introduction
Many databases offers the capability to store custom code in the
database, primarily as functions and procedures, where a function
returns a value but 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.
Many databases also support triggers: code that is executed when
triggered by an event such as deleting a row in a table. 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 the
other types of custom code objects. The screenshots show the
interface for the Oracle profile, but it is very similar for other
profiles.
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,
the Data Type and the Direction (typically one of IN,
OUT or INOUT).
The action uses this information together with a simple sample body to
create a CREATE statement. You can not enter the real code in the
action dialog. The real code is often complex and large, so
DbVisualizer provides a more powerful editing environment than what
would fit in an action dialog via the Procedure Editor, described
below.
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
The editor has a toolbar with various actions to save/compile the
procedure,
save and load the source to/from file and
perform common editing operations. The Status indicator shows
whether the procedure is valid or invalid based on last compilation
(not available for all databases).
Edit the source code and save/compile the procedure when you are happy
with the code, using the Execute
toolbar button.
Figure: Compiling procedure with errors
If error occur during compilation, the error list appears
below the editor. It shows the row/column 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. Note, however, that
some databases do not provide row/column information, only an error
message. You then have to locate the incorrect statement yourself based
on the description of the error.
In addition to the Status indicator in the editor, the
object
icon in
the tree shows a little red cross for invalid procedures, for
databases that provide this information. You can see this for the
UPDATE_STATUSA procedure node in the figure in the previous section.
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 © 2008 Onseven Software AB. All rights reserved.
