|
|
In this section, you'll look at some of the most common operations that insert, update, or delete catalog records. We won't attempt to list all queries that would be required in a complete solution because many of them are similar.
In this case study, you're more interested in the statements themselves, rather than the way they're packaged: In most cases, where possible, the statements should be saved as stored procedures into the database. This is a good practice that keeps the code clean and also brings some other benefits, as you learned in Chapter 9, "Using Stored Procedures." We'll first present the statements and then show how to package them as SQL Server, Oracle, and DB2 stored procedures.
Almost any command that updates information has input parameters, which define how to update, insert, or delete catalog information. When presenting the statements without including them in stored procedures, we'll replace the input parameters with general identifiers, such as <<ProductID>> or <<DepartmentName>>.
These should be replaced by stored procedure parameters if you're using stored procedures or by values if you're executing the commands directly on the database.
These will be most useful for MySQL users because MySQL doesn't support stored procedures. With Access queries, the stored procedure parameters aren't separately declared; instead they're used directly in the statements, written between square brackets. For more information, see Chapter 9, "Using Stored Procedures."
Let's start with something simple....
When you want to modify or delete a single record, you always identify it by its primary key. This ensures that you only affect a single record or no records at all. For example, the following is a typical command that updates the name of the department with an ID of 1:
UPDATE Department SET Name = 'Weird Stuff' WHERE DepartmentID = 1;
As you can see, you don't attempt to change the department's ID because you have no reason to do that. Moreover, changing the value of an ID column might break the referential integrity, established through foreign keys, with existing categories.
A command that updates a department's information looks like this if you're executing the command directly in the database:
UPDATE Department
SET Name = <<DepartmentName>>,
Description = <<DepartmentDescription>>
WHERE DepartmentID = <<DepartmentID>>;
Packaged as a SQL Server stored procedure, the query looks like this:
CREATE PROCEDURE UpdateDepartment
(@DepartmentID int,
@DepartmentName varchar(50),
@DepartmentDescription varchar(1000))
AS
UPDATE Department
SET Name = @DepartmentName,
Description = @DepartmentDescription
WHERE DepartmentID = @DepartmentID;
The same procedure looks like this for Oracle:
CREATE PROCEDURE UpdateDepartment
(DeptID IN integer,
DepartmentName IN varchar2,
DepartmentDescription IN varchar2)
AS
BEGIN
UPDATE Department
SET Name = DepartmentName,
Description = DepartmentDescription
WHERE DepartmentID = DeptID;
END;
/
| Tip |
Remember to use the SHOW ERRORS command, which comes in handy when writing stored procedures in SQL*Plus. |
And the DB2 equivalent is as follows:
CREATE PROCEDURE DB2ADMIN.UpdateDepartment
(i_DepartmentID INT,
i_DepartmentName VARCHAR(50),
i_DepartmentDescription VARCHAR(1000))
P1: BEGIN
UPDATE Department
SET Name = i_DepartmentName,
Description = i_DepartmentDescription
WHERE DepartmentID = i_DepartmentID;
END P1
Once you have stored a stored procedure, you can execute it using the EXECUTE command. Let's look at how you can call UpdateDepartment inside a transaction and then roll back the transaction: This allows you to test that the stored procedure works okay, without damaging the existing department data. (Chapter 10, "Transactions," covered transactions.)
Here's the SQL Server version of the code:
BEGIN TRANSACTION
EXECUTE UpdateDepartment 1, 'Strange new name',
'Strange new description'
SELECT * FROM Department
ROLLBACK TRANSACTION;
The same script looks a bit different with Oracle:
EXECUTE UpdateDepartment (1, 'Strange new name',
'Strange new description');
SELECT * FROM Department;
ROLLBACK WORK;
The DB2 version is similar to the Oracle syntax, except that you use the CALL keyword instead of EXECUTE:
CALL UpdateDepartment (1, 'Strange new name',
'Strange new description');
SELECT * FROM Department;
ROLLBACK WORK;
In all cases, you rolled back the changes, so none of your amendments were actually applied. However, you've verified that the procedure works as expected.
The SQL query that adds a new category is simple, and the way you package it as a stored procedure is just as in the previous example. The query looks like this:
INSERT INTO Category (DepartmentID, Name, Description)
VALUES (<<DepartmentID>>, <<CategoryName>>,
<<CategoryDescription>>);
Because CategoryID is an autonumbered field, you don't specify values for it when inserting new rows into Category.
When deleting departments, you're faced a new issue: The department you want to delete might have categories associated with it, so trying to delete it can break the referential integrity rules.
So how do you deal with this problem? How do you make sure you don't generate any database exceptions? Well, the answer relates to how the application is architected.
For example, you might simply want not to avoid database exceptions and handle the errors within the application's code. So, you might simply remove the department, without caring if you break any existing integrity constraints:
DELETE FROM Department WHERE DepartmentID = <<DepartmentID>>;
This would be a perfectly fine stored procedure if the application were meant to handle the database errors itself. In this scenario, you rely on the database to throw an error if you attempt to delete a department that has any related categories and on the software application to catch these errors and respond accordingly.
If you want to avoid any errors, you need to test if there are any categories that belong to the department that you're trying to delete. So, you first issue a SQL command that tests if any related categories exist, like this:
SELECT Name FROM Category WHERE DepartmentID = <<DepartmentID>>;
If no rows are returned, you can delete the department:
DELETE FROM Department WHERE DepartmentID = <<DepartmentID>>;
You can package these two commands as a SQL Server stored procedure; here we've used NOT EXISTS, but you could very well use other techniques, such as using COUNT to see how many categories belong to that department:
CREATE PROCEDURE DeleteDepartment
(@DepartmentID int)
AS
IF NOT EXISTS
(SELECT Name FROM Category WHERE DepartmentID = @DepartmentID)
DELETE FROM Department
WHERE DepartmentID = @DepartmentID;
Let's now see the Oracle version of the DeleteDepartment stored procedure. Notice that NOT EXISTS is used in a slightly different manner here:
CREATE PROCEDURE DeleteDepartment
(DeptID IN integer)
AS
BEGIN
DELETE FROM Department
WHERE DepartmentID = DeptID
AND NOT EXISTS (SELECT Name FROM Category WHERE DepartmentID = DeptID);
END;
/
Finally, you have the following for DB2:
To assign a product to a category, you need to execute a simple INSERT command that inserts a pair of rows into the ProductCategory table:
INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (<<ProductID>>, <<CategoryID>>);
Let's see the SQL Server procedure that does the same thing:
CREATE PROCEDURE AssignProductToCategory (@ProductID int, @CategoryID int) AS INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (@ProductID, @CategoryID) RETURN;
Its Oracle version looks like this:
CREATE PROCEDURE AssignProductToCategory
(ProdID IN integer,
CategID IN integer)
AS
BEGIN
INSERT INTO ProductCategory (ProductID, CategoryID)
VALUES (ProdID, CategID);
END;
/
And the DB2 version looks like this:
CREATE PROCEDURE DB2ADMIN.AssignProductToCategory (i_ProductID INT, i_CategoryID INT) P1: BEGIN INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (i_ProductID, i_CategoryID); END P1
These stored procedures assume that the supplied CategoryID and ProductID values are valid. However, you can use the techniques you saw in the previous example to verify this.
This isn't a difficult task, but it's got a catch. There are basically two operations that you need to perform: adding a new product to the Product table and then associating this product with the appropriate category by adding a record into ProductCategory.
The catch is that product IDs are automatically generated by the database (because you designed them this way), so you need a way to find these IDs.
The first of the two operations is a simple INSERT into the Product table:
INSERT INTO Product (... fields ...) VALUES (... values ...)
The second operation that you need to perform is an INSERT into ProductCategory:
INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (<<ProductID>>, <<CategoryID>>);
Let's see these two operations as a SQL Server stored procedure:
CREATE PROCEDURE CreateProductToCategory (@CategoryID int, @ProductName varchar(50), @ProductDescription varchar(1000), @ProductPrice money, @ProductImage varchar(50), @OnDepartmentPromotion bit, @OnCatalogPromotion bit) AS DECLARE @ProductID int INSERT INTO Product (Name, Description, Price, ImagePath, OnDepartmentPromotion, OnCatalogPromotion) VALUES (@ProductName, @ProductDescription, CONVERT(money,@ProductPrice), @ProductImage, @OnDepartmentPromotion, @OnCatalogPromotion) SELECT @ProductID = @@Identity INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (@ProductID, @CategoryID) RETURN;
With SQL Server, you use the @@Identity system variable, which returns the last-generated ID. You need to save its value to a local variable (@ProductID in this case) immediately after you issue the INSERT command because SQL Server resets its value automatically.
With Oracle, you use sequences to automatically generate ID values—the sequence that generates the value is the one that can tell you what the current value is. With the DepartmentIDSeq sequence, you call DepartmentIDSeq.NextVal to get the next value of the sequence. To get the current value, you call DepartmentID.CurrVal:
CREATE PROCEDURE CreateProductToCategory (CategoryID integer, ProductName IN varchar2, ProductDescription IN varchar2) AS BEGIN INSERT INTO Product (Name, Description) VALUES (ProductName, ProductDescription); INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (ProductID.CurrVal, CategoryID); END; /
The DB2 version of this procedure looks as follows:
CREATE PROCEDURE DB2ADMIN.CreateProductToCategory (i_CategoryID INT, i_ProductName VARCHAR(50), i_ProductDescription VARCHAR(1000), i_ProductPrice DECIMAL(7,2), i_ProductImage VARCHAR(50), i_OnDepartmentPromotion SMALLINT, i_OnCatalogPromotion SMALLINT) P1: BEGIN DECLARE pid INT; INSERT INTO Product (Name, Description, Price, ImagePath, OnDepartmentPromotion, OnCatalogPromotion) VALUES (i_ProductName, i_ProductDescription, i_ProductPrice, i_ProductImage, i_OnDepartmentPromotion, i_OnCatalogPromotion); SET pid = IDENTITY_VAL_LOCAL(); INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (pid, i_CategoryID); END P1
Because DB2 supports autonumbered fields, you can use the IDENTITY_VAL_LOCAL() function to retrieve the next available ID and assign it to your new product.
We can't provide a similar example with MySQL because it doesn't support stored procedures, but it does have something similar to SQL Server's @@Identity—it's the LAST_INSERT_ID() function, which also returns the last automatically generated value. You can display its value like this:
SELECT LAST_INSERT_ID();
After inserting a product into the Product table in MySQL, you can populate ProductCategory like this:
INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (LAST_INSERT_ID(), <<CategoryID>>);
Imagine that you want to remove a product from the database. The problem you have is that the product can belong to a number of categories, and you need to remove the ProductCategory entries before removing the product.
You want to create a stored procedure or script with the following functionality: You provide as parameters a product ID and a category ID. The script will first remove the product from the category by deleting a ProductCategory record. Then, if no more categories are left for this product, the product is removed from the Product table as well. In other words, you make sure there are no products that don't have an associated category: As soon as you detach the product from its last category, the product is removed altogether.
This functionality is simple to implement as a number of separated SQL statements. First, you delete the product from the mentioned category:
DELETE FROM ProductCategory WHERE CategoryID=<<CategoryID>> AND ProductID=<<ProductID>>;
Second, you count how many categories are associated with the product:
SELECT COUNT(*) FROM ProductCategory WHERE ProductID=<<ProductID>>;
If you get a value higher than zero, you leave the product alone. Otherwise, you remove the product from the Product table:
DELETE FROM Product where ProductID=<<ProductID>>;
Okay, the theory is simple. Let's see how to put it in practice in a SQL Server procedure:
CREATE PROCEDURE RemoveProduct
(@ProductID int, @CategoryID int)
AS
DELETE FROM ProductCategory
WHERE CategoryID=@CategoryID AND ProductID=@ProductID
IF (SELECT COUNT(*) FROM ProductCategory
WHERE ProductID=@ProductID) = 0
DELETE FROM Product WHERE ProductID=@ProductID;
Here's one implementation of the same procedure in Oracle:
CREATE OR REPLACE PROCEDURE RemoveProduct (ProdID int, CategID int) AS BEGIN DECLARE CategoriesCount integer; BEGIN DELETE FROM ProductCategory WHERE CategoryID = CategID AND ProductID = ProdID; SELECT COUNT(*) INTO CategoriesCount FROM ProductCategory WHERE ProductID = ProdID; IF CategoriesCount = 0 THEN DELETE FROM Product WHERE ProductID = ProdID; END IF; END; END; /
Finally, let's take a quick look at the DB2 equivalent:
CREATE PROCEDURE DB2ADMIN.RemoveProduct
(i_ProductID INT,
i_CategoryID INT)
P1: BEGIN
DELETE FROM ProductCategory
WHERE CategoryID = i_CategoryID AND
ProductID = i_ProductID;
IF (SELECT COUNT(*) FROM ProductCategory
WHERE ProductID = i_ProductID) = 0
THEN
DELETE FROM Product WHERE ProductID = i_ProductID;
END IF;
END P1
|
|