|
SUID procedures - Select, Update, Insert and Delete procedures - can be very easily created by using the context menu from the Database Navigator and selecting Create SUID Procedures.
Many people use these procedures in order to avoid direct access to the database tables.
The Table context menu
A screen with the information about the procedures will be shown.
The Create SUID Procedures screen
As you can see in the above screen shot, you can select what columns to include.
The procedure names are created from the Name Templates and can be editted in the Name Template Editor.
You have to use the Create Procedures button to actually create the procedures - they will be automatically added to the cached Stored Procedure list in the Database Navigator.
UPDATE and DELETE Procedure
The UPDATE and DELETE procedures will always accept the primary key columns of the table as input parameters - of course, when doing the update, other columns need to be included as well.
INSERT Procedure
If you have a Primary Key on the table that consist of a numerical datatype, Database Workbench will enable the selection of a sequence to generate a new ID number in the INSERT procedure. This number will be returned through an output parameter. The sequence will only be used if the input parameter is NULL or zero.
INSERT/UPDATE Procedure
With the combined INSERT/UPDATE procedure, you can generate a single procedure that allows you to either insert a new row, or update an existing row if a primary key violation is generated. Note that this INSERT procedure will not let you automatically generate a new ID number from a sequence and requires valid values for the primary key columns.