Notes 0005

Selecting Records for UPDATE

Sometimes you'd like to select records to be updated, but you don't want them to be updated in the meantime while you're looking at what you've selected.

For example: You search for an airline flight, after finding it and noticing that there is an open seat, you decide to buy it. After entering your credit card info, etc., the system notifies you that there are no available seats: someone has taken your seat while you were in the process of buying it.

Oracle avoids that by allowing you to lock a record when you select it. This is called selecting FOR UPDATE.

An example of a command may look like this:

SELECT * FROM productorder WHERE order_id = 1 AND product_id = 3 FOR UPDATE OF QUANTITY;

Which produces:

  ORDER_ID PRODUCT_ID   QUANTITY
---------- ---------- ----------
         1          3          2

This selects an order, where we may update the quantity. Nobody else is allowed to update the quantity. For example, if we go into another window and run the same command, the window will just sit there, waiting for the original update to complete.

Now, we may or may not update it! But we do have to COMMIT to release the lock. So, after we do a commit, the 2nd window locked those records, and now the first one will be locked out if we do that query.

What we could do now is attempt to lock it, and bomb out with an error if we can't. To do that, we add on NOWAIT to the SQL, which just says that we're not willing to wait for someone to update the other table.

The error is: ORA-00054: resource busy and acquire with NOWAIT specified

Also note that once some select has selected something for update, nobody else can update that record until you do the commit.

Database Views

Database Views are logical database tables. They don't store any values and cannot be updated. They are defined by a select statement.

For example, lets say for some application, all you need to know is the product_id and price, but don't really care for a product description. You can create a view that only gives you these few fields.

CREATE VIEW product_price_view AS SELECT product_id, price FROM product;

We've created the a view, that's defined as a select statement. Now that we have the view, we can select from it just like from a table!

SELECT * FROM product_price_view;

This results in:

PRODUCT_ID      PRICE
---------- ----------
         3      26.99
         4      19.95

We can also do nifty table like things like:

DESCRIBE product_price_view;

Views are very useful when granting certain applications rights to some data. Views can build queries several tables, and provide only the needed information, in a relatively security limited way.

Views also tend to be optimized; which implies that your SQL statement inside a view will be optimized to execute very efficiently (as opposed to statements that you just type up interactively in SQL*Plus).

Databases also tend to cache view results; which could speed up retrieval significantly if cache wasn't invalidated by an update.

What Views Do You Have?

You can get a list of all the views that you have by doing a query (what else?).

SELECT view_name FROM ALL_VIEWS WHERE owner = 'PARTICLE';

Note that in this case, my username is 'PARTICLE' which you'd replace with whatever your username is ('SYSTEM' ?)

This correctly returns the one view I've just created:

VIEW_NAME
------------------------------
PRODUCT_PRICE_VIEW

Dropping Views

If you no longer have a need for some view, you can drop it just like a plain table. Simply type:

DROP VIEW tablename

In our case, this would be:

DROP VIEW PRODUCT_PRICE_VIEW;

A bit of security

You can grant users the right to get access to the view. This gives you the option of preventing users from accessing the tables (which may contain more information that your average users should not be allowed to access).

For example, a company may grant views that provide statistical data about their employees to other companies (or other departments within the company), but prevent access to the database that may have personally identifiable information.

© 2006, Particle