A stored procedure is a named group of SQL statements that have been previously created and stored in the server database. Stored procedures accept input parameters so that a single procedure can be used over the network by several clients using different input data. Stored procedures reduce network traffic and improve performance. Additionally, stored procedures can be used to help ensure the integrity of the database.
DBPak can use stored procedures as an alternate means of inserting, deleting, or updating records.
In this tutorial, we'll create and use a stored procedure as part of a new part-entry application. In the OrderEntry database, adding a part means inserting a new record into the Inventory table.
Suppose the following restrictions have been placed on the database. In the Inventory table, we have imposed the rule that all part numbers must be entered in uppercase. In addition, all fields in each record must be specified--none can be left blank. These are restrictions that we would rather not have in our application. Therefore, the first step is to write a stored procedure to take input data from our application and make it conform to the specified rules.
This section describes the Sybase and Oracle versions of the sample stored procedure provided for use with this tutorial.
create procedure newPart @pno PartType, @desc varchar(40), @price money @quant int = 0, @reorder int = 10 asThe following code converts the part number to uppercase:
declare @newPno PartType select @newPno = upper(@pno)If the quantity and reorder quantity values are NULL, the default values are used:
if @quant is NULL select @quant = 0 if @reorder is NULL select @reorder = 10The following code inserts the record into the Inventory table:
insert into Inventory (Part_Number, Description, Price, Quantity_On_Hand, Reorder_Quantity) values (@newPno,@desc,@price,@quant,@reorder )We select the fields from the record that we just inserted and use them as the return value of the procedure. These values will be displayed in DBPak widgets.
select Part_Number, Description, Price, Quantity_On_Hand, Reorder_Quantity from Inventory where Part_Number = @newPnoThis is the complete stored procedure. The next step is to use it in Database Xcessory. We will create a data entry screen with the Schema Browser.
create or replace procedure newPart( pno IN OUT char, thePrice IN OUT NUMBER, part_description IN varchar, quantity_on_hand IN OUT integer, reorder IN OUT integer ) as newPno CHAR(8); quan integer := 0; BEGIN
The following code converts the part number to uppercase:
newPno := UPPER(pno);
If the quantity and reorder quantity values are NULL, the default values are used:
IF (quantity_on_hand is not NULL) then quan := quantity_on_hand; end if; IF (reorder is NULL) then reorder := 10; end if;
The following code inserts the record into the Inventory table:
INSERT INTO INVENTORY (PART_NUMBER, DESCRIPTION, PRICE, QUANTITY_ON_HAND, REORDER_QUANTITY) VALUES (newPno, part_description, thePrice, quan, reorder);
We select the fields from the record that we just inserted and store the values in the in/out parameters of the stored procedure. These values will be displayed in DBPak widgets.
SELECT PART_NUMBER, PRICE, QUANTITY_ON_HAND,REORDER_QUANTITY
INTO pno, thePrice, quantity_on_hand, reorder
FROM INVENTORY WHERE PART_NUMBER = newPno;
END
Interface Created by Dragging and Dropping All Inventory Fields onto Browser
The Quantity_On_Hand and the Reorder_Quantity fields are considered optional items.To tell DBPak that these fields are not required for an XiDB_INSERT operation, set the XmNrequiredForInsert resource to False for each of these fields:
Use the following steps to tell DBPak to use the stored procedure you have written in this tutorial when performing an insert.
inventory_Part_Number, inventory_Description,
inventory_Price, inventory_Quantity_On_Hand,
inventory_Reorder_Quantity <- newPart(
[inventory_Part_Number], [inventory_Description],
[inventory_Price], [inventory_Quantity_On_Hand] opt,
[inventory_Reorder_Quantity] opt)
This resource setting specifies the following:
Call the
newPart
stored procedure with the following arguments:
- The current contents of the inventory_Part_Number, inventory_Price, and inventory_Description widgets:
[inventory_Part_Number], [inventory_Description], [inventory_Price]
- If they are not empty, the current contents of the inventory_Quantity_On_Hand and inventory_Reorder_Quantity widgets:
[inventory_Quantity_On_Hand] opt, [inventory_Reorder_Quantity] opt
- Assign the results of stored procedure "newPart" to the following widgets:
inventory_Part_Number, inventory_Description, inventory_Price, inventory_Quantity_On_Hand, inventory_Reorder_Quantity <- newPartThis ensures that the user sees the values that have actually been entered into the database.
The following resource setting is included as the tutorial-6.text file in your example/dx-tutorials/oracle directory. You can cut and paste the following from the file into the insertProc input field in the Resource Editor:
newPart([inventory_Part_Number] in out, [inventory_Price] in out, [inventory_Description], [inventory_Quantity_On_Hand] in out opt, [inventory_Reorder_Quantity] in out opt )
This resource setting specifies the following:
Call the newPart stored procedure with the following arguments: The current contents of the inventory_Part_Number, inventory_Price, and inventory_Description widgets: [inventory_Part_Number] in out, [inventory_Price] in out, [inventory_Description], If they are not empty, the current contents of the inventory_Quantity_On_Hand and inventory_Reorder_Quantity widgets: [inventory_Quantity_On_Hand] in out opt, [inventory_Reorder_Quantity] in out opt
Note: The parameters [inventory_ Part_Number], [inventory_Price], [inventory_Quantity_On_Hand], and [inventory_ Reorder_Quantity] are used for both input and output ("in out" parameters). The current values of each widget are used as input, and the values that the stored procedure returns are displayed in each widget. This ensures that the user sees the values that have actually been entered into the database.
You can now enter Play mode and test the interface.