Database Management SQL in 10 Minutes Book Ch.20 - 22

Understanding Cursors

A cursor is a database query stored on the DBMS server—not a SELECT statement, but the result set retrieved by that statement. Once the cursor is stored, applications can scroll or browse up and down through the data as needed.

Understanding Constraints - Unique Constraints

An example of the use of constraints is an employees table. Every employee has a unique Social Security number, but you would not want to use it for the primary key because it is too long (in addition to the fact that you might not want that information easily available). Therefore, every employee also has a unique employee ID (a primary key) in addition to his Social Security number. Because the employee ID is a primary key, you can be sure that it is unique. You also might want the DBMS to ensure that each Social Security number is unique, too (to make sure that a typo does not result in the use of someone else's number). You can do this by defining a UNIQUE constraint on the Social Security number column. The syntax for unique constraints is similar to that for other constraints. Either the UNIQUE keyword is defined in the table definition or a separate CONSTRAINT is used.

Close Cursor

As already mentioned and seen in the previous examples, cursors need to be closed after they have been used. In addition, some DBMSs (such as SQL Server) require that the resources used by the cursor be explicitly deallocated. Here's the DB2, Oracle, and PostgreSQL syntax: CLOSE CustCursor The CLOSE statement is used to close cursors; once a cursor is closed, it cannot be reused without being opened again. However, a cursor does not need to be declared again to be used; an OPEN is sufficient.

Ex. of full transaction

BEGIN TRANSACTION INSERT INTO Customers(cust_id, cust_name) VALUES('1000000010', 'Toys Emporium'); SAVE TRANSACTION StartOrder; INSERT INTO Orders(order_num, order_date, cust_id) VALUES(20100,'2001/12/1','1000000010'); IF @@ERROR <> 0 ROLLBACK TRANSACTION StartOrder; INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price) VALUES(20100, 1, 'BR01', 100, 5.49); IF @@ERROR <> 0 ROLLBACK TRANSACTION StartOrder; INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price) VALUES(20100, 2, 'BR03', 100, 10.99); IF @@ERROR <> 0 ROLLBACK TRANSACTION StartOrder; COMMIT TRANSACTION Here are a set of four INSERT statements enclosed within a transaction block. A savepoint is defined after the first INSERT so that, if any of the subsequent INSERT operations fail, the transaction is only rolled back that far. In SQL Server, a variable named @@ERROR can be inspected to see if an operation succeeded. (Other DBMSs use different functions or variables to return this information.) If @@ERROR returns a value other than 0, an error occurred, and the transaction rolls back to the savepoint. If the entire transaction is processed, a COMMIT is issued to save the data.

Check Constraints

Check constraints are used to ensure that data in a column (or set of columns) meets a set of criteria that you specify. Common uses of this are • Checking minimum or maximum values—For example, preventing an order of 0 (zero) items (even though 0 is a valid number) • Specifying ranges—For example, making sure that a ship date is greater than or equal to today's date and not greater than a year from now • Allowing only specific values—For example, allowing only M or F in a gender field. The following example applies a check constraint to the OrderItems table to ensure that all items have a quantity greater than 0: CREATE TABLE OrderItems ( order_num INTEGER NOT NULL, order_item INTEGER NOT NULL, prod_id CHAR(10) NOT NULL, quantity INTEGER NOT NULL CHECK (quantity > 0), item_price MONEY NOT NULL );

Creating cursors

Cursors are created using the DECLARE statement, which differs from one DBMS to the next. DECLARE names the cursor and takes a SELECT statement, complete with WHERE and other clauses if needed. To demonstrate this, we'll create a cursor that retrieves all customers without e-mail addresses, as part of an application enabling an operator to provide missing e-mail addresses. DECLARE CustCursor CURSOR FOR SELECT * FROM Customers WHERE cust_email IS NULL the DECLARE statement is used to define and name the cursor—in this case CustCursor. The SELECT statement defines a cursor containing all customers with no e-mail address (a NULL value).

Opening the cursor

Cursors are opened using the OPEN CURSOR statement, which is so simple a statement that most DBMSs support exactly the same syntax: OPEN CURSOR CustCursor When the OPEN CURSOR statement is processed, the query is executed, and the retrieved data is stored for subsequent browsing and scrolling.

Cursor option & features

Different DBMSs support different cursor options and features. Some of the more common ones are • The capability to flag a cursor as read-only so that data can be read but not updated or deleted • The capability to control the directional operations that can be performed (forward, backward, first, last, absolute position, relative position, and so on) • The capability to flag some columns as editable and others as not editable • Scope specification so as to be able to make the cursor accessible to a specific request that created it (a stored procedure, for example) or to all requests • Instructing the DBMS to make a copy of the retrieved data (as opposed to pointing to the live data in the table) so that data does not change between the time the cursor is opened and the time it is accessed Cursors are used primarily by interactive applications in which users need to scroll up and down through screens of data, browsing or making changes.

Fetch cursor

Now the cursor data can be accessed using the FETCH statement. FETCH specifies the rows to be retrieved, where they are to be retrieved from, and where they are to be stored (variable names, for example). The first example uses Oracle syntax to retrieve a single row from the cursor (the first row): DECLARE TYPE CustCursor IS REF CURSOR RETURN Customers%ROWTYPE; DECLARE CustRecord Customers%ROWTYPE BEGIN OPEN CustCursor; FETCH CustCursor INTO CustRecord; CLOSE CustCursor; END;

Using Savepoints

Simple ROLLBACK and COMMIT statements enable you to write or undo an entire transaction. Although this works for simple transactions, more complex transactions might require partial commits or rollbacks. For example, the process of adding an order described previously is a single transaction. If an error occurs, you only want to roll back to the point before the Orders row was added. You do not want to roll back the addition to the Customers table (if there was one). To support the rollback of partial transactions, you must be able to put placeholders at strategic locations in the transaction block. Then, if a rollback is required, you can roll back to one of the placeholders. In SQL, these placeholders are called savepoints. To create one in MariaDB, MySQL, and Oracle, the SAVEPOINT statement is used, as follows: SAVEPOINT delete1; Each savepoint takes a unique name that identifies it so that, when you roll back, the DBMS knows where you are rolling back to. To roll back to this savepoint, do the following in SQL Server: ROLLBACK TO delete1;

Transaction Processing using orders ex.

So, looking at the same example, this is how the process would work: 1. Check if the customer is already in the database; if not add him or her. 2. Commit the customer information. 3. Retrieve the customer's ID. 4. Add a row to the Orders table. 5. If a failure occurs while adding the row to Orders, roll back. 6. Retrieve the new order ID assigned in the Orders table. 7. Add one row to the OrderItems table for each item ordered. 8. If a failure occurs while adding rows to OrderItems, roll back all the OrderItems rows added and the Orders row. When working with transactions and transaction processing, there are a few keywords that'll keep reappearing. *Here are the terms you need to know:* • Transaction - A block of SQL statements • Rollback - The process of undoing specified SQL statements • Commit - Writing unsaved SQL statements to the database tables • Savepoint - A temporary placeholder in a transaction set to which you can issue a rollback (as opposed to rolling back an entire transaction)

Using Rollback

The SQL ROLLBACK command is used to roll back (undo) SQL statements, as seen in this next statement: DELETE FROM Orders; ROLLBACK; In this example, a DELETE operation is performed and then undone using a ROLLBACK statement. Although not the most useful example, it does demonstrate that, within a transaction block, DELETE operations (like INSERT and UPDATE operations) are never final.

Triggers Ex.

The following example creates a trigger that converts the cust_state column in the Customers table to uppercase on all INSERT and UPDATE operations. This is the SQL Server version: CREATE TRIGGER customer_state ON Customers FOR INSERT, UPDATE AS UPDATE Customers SET cust_state = Upper(cust_state) WHERE Customers.cust_id = inserted.cust_id;

How to begin a transaction

The key to managing transactions involves breaking your SQL statements into logical chunks and explicitly stating when data should be rolled back and when it should not. Some DBMSs require that you explicitly mark the start and end of transaction blocks. In SQL Server, for example, you can do the following: BEGIN TRANSACTION ... COMMIT TRANSACTION other dbms like mysql would use: START TRANSACTION ...

Transaction Processing

Transaction processing is used to maintain database integrity by ensuring that batches of SQL operations execute completely or not at all. As mechanism used to manage sets of SQL operations that must be executed in batches so as to ensure that databases never contain the results of partial operations.

Understanding Triggers

Triggers are special stored procedures that are executed automatically when specific database activity occurs. Triggers might be associated with INSERT, UPDATE, and DELETE operations (or any combination thereof) on specific tables. A trigger associated with INSERT operations on the Orders table will be executed only when a row is inserted into the Orders table. Common Triggers include: • Ensuring data consistency—For example, converting all state names to uppercase during an INSERT or UPDATE operation • Performing actions on other tables based on changes to a table—For example, writing an audit trail record to a log table each time a row is updated or deleted • Performing additional validation and rolling back data if needed—For example, making sure a customer's available credit has not been exceeded and blocking the insertion if it has • Calculating computed column values or updating timestamps

working with cursors

Using cursors involves several distinct steps: • Before a cursor can be used it must be declared (defined). This process does not actually retrieve any data, it merely defines the SELECT statement to be used and any cursor options. • Once it is declared, the cursor must be opened for use. This process actually retrieves the data using the previously defined SELECT statement. • With the cursor populated with data, individual rows can be fetched (retrieved) as needed. • When it is done, the cursor must be closed and possibly deallocated (depending on the DBMS). Once a cursor is declared, it may be opened and closed as often as needed. Once it is open, fetch operations can be performed as often as needed.

Using Commit

Usually SQL statements are executed and written directly to the database tables. This is known as an implicit commit—the commit (write or save) operation happens automatically. Within a transaction block, however, commits might not occur implicitly. This, too, is DBMS specific. Some DBMSs treat a transaction end as an implicit commit; others do not. To force an explicit commit, the COMMIT statement is used. The following is a SQL Server example: BEGIN TRANSACTION DELETE OrderItems WHERE order_num = 12345 DELETE Orders WHERE order_num = 12345 COMMIT TRANSACTION In this SQL Server example, order number 12345 is deleted entirely from the system. Because this involves updating two database tables, Orders and OrderItems, a transaction block is used to ensure that the order is not partially deleted. The final COMMIT statement writes the change only if no error occurred. If the first DELETE worked, but the second failed, the DELETE would not be committed.

Understanding Indexes

You may define an index on one or more columns so that the DBMS keeps a sorted list of the contents for its own use. After an index is defined, the DBMS uses it in much the same way as you would use a book index. It searches the sorted index to find the location of any matches and then retrieves those specific rows. Indexes are created with the CREATE INDEX statement (which varies dramatically from one DBMS to another). The following statement creates a simple index on the Products table's product name column: CREATE INDEX prod_name_ind ON Products (prod_name);