Download

MODULE 3

3.1 Transaction and system preliminaries.

The concept of transaction has been devised as a convenient and precise way of describing the various logical units that form a database system. We have transaction systems which are systems that operate on very large databases, on which several (sometimes running into hundreds) of users concurrently operate – i.e. they manipulate the database transaction. There are several such systems presently in operation in our country also – if you consider the railway reservation system, wherein thousands of stations – each with multiple number of computers operate on a huge database, the database containing the reservation details of all trains of our country for the next several days. There are many other such systems like the airlines reservation systems, distance banking systems, stock market systems etc. In all these cases apart from the accuracy and integrity of the data provided by the database (note that money is involved in almost all the cases – either directly or indirectly), the systems should provide instant availability and fast response to these hundreds of concurrent users. In this block, we discuss the concept of transaction, the problems involved in controlling concurrently operated systems and several other related concepts. We repeat – a transaction is a logical operation on a database and the users intend to operate with these logical units trying either to get information from the database and in some cases modify them. Before we look into the problem of concurrency, we view the concept of multiuser systems from another point of view – the view of the database designer.

3.1.1 A typical multiuser system

We remind ourselves that a multiuser computer system is a system that can be used by a number of persons simultaneously as against a single user system, which is used by one person at a time. (Note however, that the same system can be used by different persons at different periods of time). Now extending this concept to a database, a multiuser database is one which can be accessed and modified by a number of users simultaneously – whereas a single user database is one which can be used by only one person at a time. Note that multiuser databases essentially mean there is a concept of multiprogramming but the converse is not true. Several users may be operating simultaneously, but not all of them may be operating on the database simultaneously.

Now, before we see what problems can arise because of concurrency, we see what operations can be done on the database. Such operations can be single line commands or can be a set of commands meant to be operated sequentially. Those operations are invariably limited by the “begin transaction” and “end transaction” statements and the implication is that all operations in between them are to be done on a given transaction.

Another concept is the “granularity” of the transaction. Assume each field in a database is named. The smallest such named item of the database can be called a field of a record. The unit on which we operate can be one such “grain” or a number of such grains collectively defining some data unit. However, in this course, unless specified otherwise, we use of “single grain” operations, but without loss of generality. To facilitate discussions, we presume a database package in which the following operations are available.

i) Read_tr(X: The operation reads the item X and stores it into an assigned variable. The name of the variable into which it is read can be anything, but we would give it the same name X, so that confusions are avoided. I.e. whenever this command is executed the system reads the element required from the database and stores it into a program variable called X.

ii) Write – tr(X): This writes the value of the program variable currently stored in X into a database item called X.

Once the read –tr(X) is encountered, the system will have to perform the following operations.

1. Find the address of the block on the disk where X is stored.

2. Copy that block into a buffer in the memory.

3. Copy it into a variable (of the program) called X.

A write –tr (x) performs the converse sequence of operations.

1. Find the address of the diskblock where the database variable X is stored.

2. Copy the block into a buffer in the memory.

3. Copy the value of X from the program variable to this X.

4. Store this updated block back to the disk.

Normally however, the operation (4) is not performed every time a write –tr is executed. It would be a wasteful operation to keep writing back to the disk every time. So the system maintains one/more buffers in the memory which keep getting updated during the operations and this updated buffer is moved on to the disk at regular intervals. This would save a lot of computational time, but is at the heart of some of the problems of concurrency that we will have to encounter.

3.1.2 The need for concurrency control

Let us visualize a situation wherein a large number of users (probably spread over vast geographical areas) are operating on a concurrent system. Several problems can occur if they are allowed to execute their transactions operations in an uncontrolled manner.

Consider a simple example of a railway reservation system. Since a number of people are accessing the database simultaneously, it is obvious that multiple copies of the transactions are to be provided so that each user can go ahead with his operations. Let us make the concept a little more specific. Suppose we are considering the number of reservations in a particular train of a particular date. Two persons at two different places are trying to reserve for this train. By the very definition of concurrency, each of them should be able to perform the operations irrespective of the fact that the other person is also doing the same. In fact they will not even know that the other person is also booking for the same train. The only way of ensuring the same is to make available to each of these users their own copies to operate upon and finally update the master database at the end of their operation.

Now suppose there are 10 seats are available. Both the persons, say A and B want to get this information and book their seats. Since they are to be accommodated concurrently, the system provides them two copies of the data. The simple way is to perform a read –tr (X) so that the value of X is copied on to the variable X of person A (let us call it XA) and of the person B (XB). So each of them know that there are 10 seats available.

Suppose A wants to book 8 seats. Since the number of seats he wants is (say Y) less than the available seats, the program can allot him the seats, change the number of available seats (X) to X-Y and can even give him the seat numbers that have been booked for him.

The problem is that a similar operation can be performed by B also. Suppose he needs 7 seats. So, he gets his seven seats, replaces the value of X to 3 (10 – 7) and gets his reservation.

The problem is noticed only when these blocks are returned to main database (the disk in the above case).

Before we can analyse these problems, we look at the problem from a more technical view.

1 The lost update problem: This problem occurs when two transactions that access the same database items have their operations interleaved in such a way as to make the value of some database incorrect. Suppose the transactions T₁ and T₂ are submitted at the (approximately) same time. Because of the concept of interleaving, each operation is executed for some period of time and then the control is passed on to the other transaction and this sequence continues. Because of the delay in updatings, this creates a problem. This was what happened in the previous example. Let the transactions be called T_A and T_B.

T_A T_B

Read –tr(X)

Read –tr(X) Time

X = X – N_A

X = X - N_B

Write –tr(X)

write –tr(X)

fig1 fig2

Note that the problem occurred because the transaction T_B failed to record the transactions T_A. I.e. T_B lost on T_A. Similarly since T_A did the writing later on, T_A lost the updatings of T_B.

2 The temporary update (Dirty read) problem

This happens when a transaction T_A updates a data item, but later on (for some reason) the transaction fails. It could be due to a system failure or any other operational reason. Or the system may have later on noticed that the operation should not have been done and cancels it. To be fair, it also ensures that the original value is restored.

But in the meanwhile, another transaction T_B has accessed the data and since it has no indication as to what happened later on, it makes use of this data and goes ahead. Once the original value is restored by T_A, the values generated by T_B are obviously invalid.

T_A T_B

Read –tr(X) Time

X = X – N

Write –tr(X)

Read –tr(X)

X = X - N

write –tr(X)

Failure

X = X + N

Write –tr(X)

Fig3

The value generated by T_A out of a non-sustainable transaction is a “dirty data” which is read by T_B, produces an illegal value. Hence the problem is called a dirty read problem.

3 The Incorrect Summary Problem: Consider two concurrent operations, again called T_A and T_B. T_B is calculating a summary (average, standard deviation or some such operation) by accessing all elements of a database (Note that it is not updating any of them, only is reading them and is using the resultant data to calculate some values). In the meanwhile T_A is updating these values. In case, since the Operations are interleaved, T_A, for some of it’s operations will be using the not updated data, whereas for the other operations will be using the updated data. This is called the incorrect summary problem.

T_A T_B

Sum = 0

Read –tr(A)

Sum = Sum + A

Read –tr(X)

X = X – N

Write –tr(X)

Read tr(X)

Sum = Sum + X

Read –tr(Y)

Sum = Sum + Y

Read (Y)

Y = Y – N

Write –tr(Y)

Fig4

In the above example, both T_A will be updating both X and Y. But since it first updates X and then Y and the operations are so interleaved that the transaction T_B uses both of them in between the operations, it ends up using the old value of Y with the new value of X. In the process, the sum we got does not refer either to the old set of values or to the new set of values.

4 Unrepeatable read: This can happen when an item is read by a transaction twice, (in quick succession) but the item has been changed in the meanwhile, though the transaction has no reason to expect such a change. Consider the case of a reservation system, where a passenger gets a reservation detail and before he decides on the aspect of reservation the value is updated at the request of some other passenger at another place.

3.1.4 The concept of failures and recovery

Any database operation can not be immune to the system on which it operates (both the hardware and the software, including the operating systems). The system should ensure that any transaction submitted to it is terminated in one of the following ways.

a) All the operations listed in the transaction are completed, the changes are recorded permanently back to the database and the database is indicated that the operations are complete.

b) In case the transaction has failed to achieve it’s desired objective, the system should ensure that no change, whatsoever, is reflected onto the database. Any intermediate changes made to the database are restored to their original values, before calling off the transaction and intimating the same to the database.

In the second case, we say the system should be able to “Recover” from the failure. Failures can occur in a variety of ways.

i) A System Crash: A hardware, software or network error can make the completion of the transaction an impossibility.

ii) A transaction or system error: The transaction submitted may be faulty – like creating a situation of division by zero or creating a negative numbers which cannot be handled (For example, in a reservation system, negative number of seats convey no meaning). In such cases, the system simply discontinuous the transaction by reporting an error.

iii) Some programs provide for the user to interrupt during execution. If the user changes his mind during execution, (but before the transactions are complete) he may opt out of the operation.

iv) Local exceptions: Certain conditions during operation may force the system to raise what are known as “exceptions”. For example, a bank account holder may not have sufficient balance for some transaction to be done or special instructions might have been given in a bank transaction that prevents further continuation of the process. In all such cases, the transactions are terminated.

v) Concurrency control enforcement: In certain cases when concurrency constrains are violated, the enforcement regime simply aborts the process to restart later.

The other reasons can be physical problems like theft, fire etc or system problems like disk failure, viruses etc. In all such cases of failure, a recovery mechanism is to be in place.

3.2 Transaction States and additional operations

Though the read tr and write tr operations described above the most fundamental operations, they are seldom sufficient. Though most operations on databases comprise of only the read and write operations, the system needs several additional operations for it’s purposes. One simple example is the concept of recovery discussed in the previous section. If the system were to recover from a crash or any other catastrophe, it should first be able to keep track of the transactions – when they start, when they terminate or when they abort. Hence the following operations come into picture.

i) Begin Trans: This marks the beginning of an execution process.

ii) End trans: This marks the end of a execution process.

iii) Commit trans: This indicates that transaction is successful and the changes brought about by the transaction may be incorporated onto the database and will not be undone at a later date.

iv) Rollback: Indicates that the transaction is unsuccessful (for whatever reason) and the changes made to the database, if any, by the transaction need to be undone.

Most systems also keep track of the present status of all the transactions at the present instant of time (Note that in a real multiprogramming environment, more than one transaction may be in various stages of execution). The system should not only be able to keep a tag on the present status of the transactions, but also should know what are the nextpossibilities for the transaction to proceed and in case of a failure, how to roll it back. The whole concept takes the state transition diagram. A simple state transition diagram, in view of what we have seen so for can appear as follows:

Terminate

Abort Terminate

Begin End

Transaction Transaction Commit

Read/Write

Fig5

The arrow marks indicate how a state of a transaction can change to a next state. A transaction is in an active state immediately after the beginning of execution. Then it will be performing the read and write operations. At this state, the system protocols begin ensuring that a system failure at this juncture does not make erroneous recordings on to the database. Once this is done, the system “Commits” itself to the results and thus enters the “Committed state”. Once in the committed state, a transaction automatically proceeds to the terminated state.

The transaction may also fail due to a variety of reasons discussed in a previous section. Once it fails, the system may have to take up error control exercises like rolling back the effects of the previous write operations of the transaction. Once this is completed, the transaction enters the terminated state to pass out of the system.

A failed transaction may be restarted later – either by the intervention of the user or automatically.

The concept of system log:

To be able to recover from failures of the transaction operations the system needs to essentially maintain a track record of all transaction operations that are taking place and that are likely to affect the status of the database. This information is called a “System log” (Similar to the concept of log books) and may become useful when the system is trying to recover from failures. The log information is kept on the disk, such that it is not likely to be affected by the normal system crashes, power failures etc. (Otherwise, when the system crashes, if the disk also crashes, then the entire concept fails). The log is also periodically backed up into removable devices (like tape) and is kept in archives.

The question is, what type of data or information needs to be logged into the system log?

Let T refer to a unique transaction – id, generated automatically whenever a new transaction is encountered and this can be used to uniquely identify the transaction. Then the following entries are made with respect to the transaction T.

i) [Start-Trans, T] : Denotes that T has started execution.

ii) [Write-tr, T, X, old, new]: denotes that the transaction T has changed the old value of the data X to a new value.

iii) [read_tr, T, X] : denotes that the transaction T has read the value of the X from the database.

iv) [Commit, T] : denotes that T has been executed successfully and confirms that effects can be permanently committed to the database.

v) [abort, T] : denotes that T has been aborted.

These entries are not complete. In some cases certain modification to their purpose and format are made to suit special needs.

(Note that though we have been talking that the logs are primarily useful for recovery from errors, they are almost universally used for other purposes like reporting, auditing etc).

The two commonly used operations are “undo” and “redo” operations. In the undo, if the transaction fails before permanent data can be written back into the database, the log details can be used to sequentially trace back the updatings and return them to their old values. Similarly if the transaction fails just before the commit operation is complete, one need not report a transaction failure. One can use the old, new values of all write operation on the log and ensure that the same is entered onto the database.

Commit Point of a Transaction:

The next question to be tackled is when should one commit to the results of a transaction? Note that unless a transaction is committed, it’s operations do not get reflected in the database. We say a transaction reaches a “Commit point” when all operations that access the database have been successfully executed and the effects of all such transactions have been included in the log. Once a transaction T reaches a commit point, the transaction is said to be committed – i.e. the changes that the transaction had sought to make in the database are assumed to have been recorded into the database. The transaction indicates this state by writing a [commit, T] record into it’s log. At this point, the log contains a complete sequence of changes brought about by the transaction to the database and has the capacity to both undo it (in case of a crash) or redo it (if a doubt arises as to whether the modifications have actually been recorded onto the database).

Before we close this discussion on logs, one small clarification. The records of the log are on the disk (secondary memory). When a log record is to be written, a secondary device access is to be made, which slows down the system operations. So normally a copy of the most recent log records are kept in the memory and the updatings are made there. At regular intervals, these are copied back to the disk. In case of a system crash, only those records that have been written onto the disk will survive. Thus, when a transaction reaches commit stage, all records must be forcefully written back to the disk and then commit is to be executed. This concept is called ‘forceful writing’ of the log file.

3.3 Desirable Transaction properties. (ACID properties)

For the effective and smooth database operations, transactions should possess several properties. These properties are – Atomicity, consistency preservation, isolation and durability. Often by combining their first letters, they are called ACID properties.

i) Atomicity: A transaction is an atomic unit of processing i.e. it cannot be broken down further into a combination of transactions. Looking otherway, a given transaction will either get executed or is not performed at all. There cannot be a possibility of a transaction getting partially executed.

ii) Consistency preservation: A transaction is said to be consistency preserving if it’s complete execution takes the database from one consistent state to another.

We shall slightly elaborate on this. In steady state a database is expected to be consistent i.e. there are not anomalies in the values of the items. For example if a database stores N values and also their sum, the database is said to be consistent if the addition of these N values actually leads to the value of the sum. This will be the normal case.

Now consider the situation when a few of these N values are being changed. Immediately after one/more values are changed, the database becomes inconsistent. The sum value no more corresponds to the actual sum. Only after all the updatings are done and the new sum is calculated that the system becomes consistent.

A transaction should always ensure that once it starts operating on a database, it’s values are made consistent before the transaction ends.

iii) Isolation: Every transaction should appear as if it is being executed in isolation. Though, in a practical sense, a large number of such transactions keep executing concurrently no transaction should get affected by the operation of other transactions. Then only is it possible to operate on the transaction accurately.

iv) Durability; The changes effected to the database by the transaction should be permanent – should not vanish once the transaction is removed. These changes should also not be lost due to any other failures at later stages.

Now how does one enforce these desirable properties on the transactions? The atomicity concept is taken care of, while designing and implementing the transaction. If, however, a transaction fails even before it can complete it’s assigned task, the recovery software should be able to undo the partial effects inflicted by the transactions onto the database.

The preservation of consistency is normally considered as the duty of the database programmer. A “consistent state” of a database is that state which satisfies the constraints specified by the schema. Other external constraint may also be included to make the rules more effective. The database programmer writes his programs in such a way that a transaction enters a database only when it is in a consistent state and also leaves the state in the same or any other consistent state. This, of course implies that no other transaction “interferes” with the action of the transaction in question.

This leads us to the next concept of isolation i.e. every transaction goes about doing it’s job, without being bogged down by any other transaction, which may also be working on the same database. One simple mechanism to ensure this is to make sure that no transaction makes it’s partial updates available to the other transactions, until the commit state is reached. This also eliminates the temporary update problem. However, this has been found to be inadequate to take care of several other problems. Most database transaction today come with several levels of isolation. A transaction is said to have a level zero (0) isolation, if it does not overwrite the dirty reads of higher level transactions (level zero is the lowest level of isolation). A transaction is said to have a level 1 isolation, if it does not lose any updates. At level 3, the transaction neither loses updates nor has any dirty reads. At level 3, the highest level of isolation, a transaction does not have any lost updates, does not have any dirty reads, but has repeatable reads.

3.4 The Concept of Schedules

When transactions are executing concurrently in an interleaved fashion, not only does the action of each transaction becomes important, but also the order of execution of operations from each of these transactions. As an example, in some of the problems that we have discussed earlier in this section, the problem may get itself converted to some other form (or may even vanish) if the order of operations becomes different. Hence, for analyzing any problem, it is not just the history of previous transactions that one should be worrying about, but also the “schedule” of operations.

Schedule (History of transaction):

We formally define a schedule S of n transactions T₁, T2 …Tn as on ordering of operations of the transactions subject to the constraint that, for each transaction, T_i that participates in S, the operations of T_i must appear in the same order in which they appear in T_i. I.e. if two operations T_i1 and T_i2 are listed in T_isuch that T_i1is earlier to T_i2, then in the schedule also T_i1 should appear before T_i2. However, if T_i2 appears immediately after T_i1 in T_i, the same may not be true in S, because some other operations T_j1 (of a transaction T_j) may be interleaved between them. In short, a schedule lists the sequence of operations on the database in the same order in which it was effected in the first place.

For the recovery and concurrency control operations, we concentrate mainly on readtr and writetr operations, because these operations actually effect changes to the database. The other two (equally) important operations are commit and abort, since they decide when the changes effected have actually become active on the database.

Since listing each of these operations becomes a lengthy process, we make a notation for describing the schedule. The operations of readtr, writetr, commit and abort, we indicate by r, w, c and a and each of them come with a subscript to indicate the transaction number

For example SA : r1(x); y2(y); w2(y); r1(y), W1 (x); a1

Indicates the following operations in the same order:

Readtr(x) transaction 1

Read tr (y) transaction 2

Write tr (y) transaction 2

Read tr(y) transaction 1

Write tr(x) transaction 1

Abort transaction 1

Conflicting operations: Two operations in a schedule are said to be in conflict if they satisfy these conditions

i) The operations belong to different transactions

ii) They access the same item x

iii) Atleast one of the operations is a write operation.

For example : r1(x); w2 (x)

W1 (x); r2(x)

w1 (y); w2(y)

Conflict because both of them try to write on the same item.

But r1 (x); w2(y) and r1(x) and r2(x) do not conflict, because in the first case the read and write are on different data items, in the second case both are trying read the same data item, which they can do without any conflict.

A Complete Schedule: A schedule S of n transactions T₁, T₂…….. Tn is said to be a “Complete Schedule” if the following conditions are satisfied.

i) The operations listed in S are exactly the same operations as in T₁, T₂ ……Tn, including the commit or abort operations. Each transaction is terminated by either a commit or an abort operation.

ii) The operations in any transaction. Ti appear in the schedule in the same order in which they appear in the Transaction.

iii) Whenever there are conflicting operations, one of two will occur before the other in the schedule.

A “Partial order” of the schedule is said to occur, if the first two conditions of the complete schedule are satisfied, but whenever there are non conflicting operations in the schedule, they can occur without indicating which should appear first.

This can happen because non conflicting operations any way can be executed in any order without affecting the actual outcome.

However, in a practical situation, it is very difficult to come across complete schedules. This is because new transactions keep getting included into the schedule. Hence, often one works with a “committed projection” C(S) of a schedule S. This set includes only those operations in S that have committed transactions i.e. transaction Ti whose commit operation Ci is in S.

Put in simpler terms, since non committed operations do not get reflected in the actual outcome of the schedule, only those transactions, who have completed their commit operations contribute to the set and this schedule is good enough in most cases.

3.5 Schedules and Recoverability :

Recoverability is the ability to recover from transaction failures. The success or otherwise of recoverability depends on the schedule of transactions. If fairly straightforward operations without much interleaving of transactions are involved, error recovery is a straight forward process. On the other hand, if lot of interleaving of different transactions have taken place, then recovering from the failure of any one of these transactions could be an involved affair. In certain cases, it may not be possible to recover at all. Thus, it would be desirable to characterize the schedules based on their recovery capabilities.

To do this, we observe certain features of the recoverability and also of schedules. To begin with, we note that any recovery process, most often involves a “roll back” operation, wherein the operations of the failed transaction will have to be undone. However, we also note that the roll back need to go only as long as the transaction T has not committed. If the transaction T has committed once, it need not be rolled back. The schedules that satisfy this criterion are called “recoverable schedules” and those that do not, are called “non-recoverable schedules”. As a rule, such non-recoverable schedules should not be permitted.

Formally, a schedule S is recoverable if no transaction T which appears is S commits, until all transactions T₁ that have written an item which is read by T have committed.

The concept is a simple one. Suppose the transaction T reads an item X from the database, completes its operations (based on this and other values) and commits the values. I.e. the output values of T become permanent values of database.

But suppose, this value X is written by another transaction T’ (before it is read by T), but aborts after T has committed. What happens? The values committed by T are no more valid, because the basis of these values (namely X) itself has been changed. Obviously T also needs to be rolled back (if possible), leading to other rollbacks and so on.

The other aspect to note is that in a recoverable schedule, no committed transaction needs to be rolled back. But, it is possible that a cascading roll back scheme may have to be effected, in which an uncommitted transaction has to be rolled

back, because it read from a value contributed by a transaction which later aborted. But such cascading rollbacks can be very time consuming because at any instant of time, a large number of uncommitted transactions may be operating. Thus, it is desirable to have “cascadeless” schedules, which avoid cascading rollbacks.

This can be ensured by ensuring that transactions read only those values which are written by committed transactions i.e. there is no fear of any aborted or failed transactions later on. If the schedule has a sequence wherein a transaction T1 has to read a value X by an uncommitted transaction T2, then the sequence is altered, so that the reading is postponed, till T₂ either commits or aborts.

This delays T₁, but avoids any possibility of cascading rollbacks.

The third type of schedule is a “strict schedule”, which as the name suggests is highly restrictive in nature. Here, transactions are allowed neither to read or write a value X until the last transaction that wrote X has committed or aborted. Note that the strict schedules largely simplifies the recovery process, but the many cases, it may not be possible device strict schedules.

It may be noted that the recoverable schedule, cascadeless schedules and strict schedules each is more stringent than it’s predecessor. It facilitates the recovery process, but sometimes the process may get delayed or even may become impossible to schedule.

3.6 Serializability

Given two transaction T₁ and T₂ are to be scheduled, they can be scheduled in a number of ways. The simplest way is to schedule them without in that bothering about interleaving them. I.e. schedule all operation of the transaction T₁ followed by all operations of T₂ or alternatively schedule all operations of T₂ followed by all operations of T₁.

T₁ T₂

read_tr(X)

X=X+N

write_tr(X)

read_tr(Y)

Y=Y+N

Write_tr(Y)

Time read_tr(X)

X=X+P

Write_tr(X)

Fig 6 Non-interleaved (Serial Schedule) :A

T₁ T₂ T₂ T₂

read_tr(X) read_tr(X) read_tr(X)

X=X+N X=X+P X=X+P

write_tr(X) Write_tr(X) write_tr(X)

read_tr(Y) readtr(X)

Y=Y+N |

Write_tr(Y) |

Fig 7 Non-interleaved (Serial Schedule):B

These now can be termed as serial schedules, since the entire sequence of operation in one transaction is completed before the next sequence of transactions is started.

In the interleaved mode, the operations of T₁ are mixed with the operations of T₂. This can be done in a number of ways. Two such sequences are given below:

T₁ T₂

read_tr(X )

X=X+N

read_tr(X)

X=X+P

write_tr(X)

read_tr(Y)

Write_tr(X)

Y=Y+N

Write_tr(Y)

Fig 8 Interleaved (non-serial schedule):C

T₁ T₂

read_tr(X)

X=X+N

write_tr(X)

read_tr(X)

X=X+P

Write_tr(X)

read_tr(Y)

Y=Y+N

Write_tr(Y)

Fig 9 Interleaved (Nonserial) Schedule D.

Formally a schedule S is serial if, for every transaction, T in the schedule, all operations of T are executed consecutively, otherwise it is called non serial. In such a non-interleaved schedule, if the transactions are independent, one can also presume that the schedule will be correct, since each transaction commits or aborts before the next transaction begins. As long as the transactions individually are error free, such a sequence of events are guaranteed to give a correct results.

The problem with such a situation is the wastage of resources. If in a serial schedule, one of the transactions is waiting for an I/O, the other transactions also cannot use the system resources and hence the entire arrangement is wasteful of resources. If some transaction T is very long, the other transaction will have to keep waiting till it is completed. Moreover, wherein hundreds of machines operate concurrently becomes unthinkable. Hence, in general, the serial scheduling concept is unacceptable in practice.

However, once the operations are interleaved, so that the above cited problems are overcome, unless the interleaving sequence is well thought of, all the problems that we encountered in the beginning of this block become addressable. Hence, a methodology is to be adopted to find out which of the interleaved schedules give correct results and which do not.

A schedule S of N transactions is “serialisable” if it is equivalent to some serial schedule of the some N transactions. Note that there are n! different serial schedules possible to be made out of n transaction. If one goes about interleaving them, the number of possible combinations become unmanageably high. To ease our operations, we form two disjoint groups of non serial schedules- these non serial schedules that are equivalent to one or more serial schedules, which we call “serialisable schedules” and those that are not equivalent to any serial schedule and hence are not serialisable once a nonserial schedule is serialisable, it becomes equivalent to a serial schedule and by our previous definition of serial schedule will become a “correct” schedule. But now can one prove the equivalence of a nonserial schedule to a serial schedule?

The simplest and the most obvious method to conclude that two such schedules are equivalent is to find out their results. If they produce the same results, then they can be considered equivalent. i.e. it two schedules are “result equivalent”, then they can be considered equivalent. But such an oversimplification is full of problems. Two sequences may produce the same set of results of one or even a large number of initial values, but still may not be equivalent. Consider the following two sequences:

S₁ S₂

read_tr(X) read_tr(X)

X=X+X X=X*X

write_tr(X) Write_tr(X)

fig 10

For a value X=2, both produce the same result. Can be conclude that they are equivalent? Though this may look like a simplistic example, with some imagination, one can always come out with more sophisticated examples wherein the “bugs” of treating them as equivalent are less obvious. But the concept still holds -result equivalence cannot mean schedule equivalence. One more refined method of finding equivalence is available. It is called “ conflict equivalence”. Two schedules can be said to be conflict equivalent, if the order of any two conflicting operations in both the schedules is the same (Note that the conflicting operations essentially belong to two different transactions and if they access the same data item, and atleast one of them is a write_tr(x) operation). If two such conflicting operations appear in different orders in different schedules, then it is obvious that they produce two different databases in the end and hence they are not equivalent.

1 Testing for conflict serializability of a schedule:

We suggest an algorithm that tests a schedule for conflict serializability.

1. For each transaction Ti, participating in the schedule S, create a node labeled T₁ in the precedence graph.

2. For each case where Tj executes a readtr(x) after Ti executes write_tr(x), create an edge from Ti to Tj in the precedence graph.

3. For each case where Tj executes write_tr(x) after Ti executes a read_tr(x), create an edge from Ti to Tj in the graph.

4. For each case where Tj executes a write_tr(x) after Ti executes a write_tr(x), create an edge from Ti to Tj in the graph.

5. The schedule S is serialisable if and only if there are no cycles in the graph.

If we apply these methods to write the precedence graphs for the four cases of section 1.8, we get the following precedence graphs.

X

Schedule A Schedule B

Schedule C Schedule D

Fig 11

We may conclude that schedule D is equivalent to schedule A.

2.View equivalence and view serializability:

Apart from the conflict equivalence of schedules and conflict serializability, another restrictive equivalence definition has been used with reasonable success in the context of serializability. This is called view serializability.

Two schedules S and S¹ are said to be “view equivalent” if the following conditions are satisfied.

i) The same set of transactions participates in S and S¹ and S and S¹ include the same operations of those transactions.

ii) For any operation ri(X) of Tiin S, if the value of X read by the operation has been written by an operation wj(X) of Tj(or if it is the original value of X before the schedule started) the same condition must hold for the value of x read by operation ri(X) of Ti in S¹.

iii) If the operation W_k(Y) of T_k is the last operation to write, the item Y in S, then W_k(Y) of T_k must also be the last operation to write the item y in S¹.

The concept being view equivalent is that as long as each read operation of the transaction reads the result of the same write operation in both the schedules, the write operations of each transaction must produce the same results. Hence, the read operations are said to see the same view of both the schedules. It can easily be verified when S or S¹ operate independently on a database with the same initial state, they produce the same end states. A schedule S is said to be view serializable, if it is view equivalent to a serial schedule.

It can also be verified that the definitions of conflict serializability and view serializability are similar, if a condition of “ constrained write assumption” holds on all transactions of the schedules. This condition states that any write operation wi(X) in Ti is preceded by a ri(X) is Ti and that the value written by wi(X) in Ti depends only on the value of X read by ri(X). This assumes that computation of the new value of X is a function f(X) based on the old value of x read from the database. However, the definition of view serializability is less restrictive than that of conflict serializability under the “unconstrained write assumption” where the value written by the operation Wi(x) in Ti can be independent of it’s old value from the database. This is called a “blind write”.

But the main problem with view serializability is that it is extremely complex computationally and there is no efficient algorithm to do the same.

3.Uses of serializability:

If one were to prove the serializability of a schedule S, it is equivalent to saying that S is correct. Hence, it guarantees that the schedule provides correct results. But being serializable is not the same as being serial. A serial scheduling inefficient because of the reasons explained earlier, which leads to under utilization of the CPU, I/O devices and in some cases like mass reservation system, becomes untenable. On the other hand, a serializable schedule combines the benefits of concurrent execution( efficient system utilization, ability to cater to larger no of concurrent users) with the guarantee of correctness.

But all is not well yet. The scheduling process is done by the operating system routines after taking into account various factors like system load, time of transaction submission, priority of the process with reference to other process and a large number of other factors. Also since a very large number of possible interleaving combinations are possible, it is extremely difficult to determine before hand the manner in which the transactions are interleaved. In other words getting the various schedules itself is difficult, let alone testing them for serializability.

Hence, instead of generating the schedules, checking them for serializability and then using them, most DBMS protocols use a more practical method – impose restrictions on the transactions themselves. These restrictions, when followed by every participating transaction, automatically ensure serializability in all schedules that are created by these participating schedules.

Also, since transactions are being submitted at different times, it is difficult to determine when a schedule begins and when it ends. Hence serializability theory can be used to deal with the problem by considering only the committed projection C(CS) of the schedule. Hence, as an approximation, we can define a schedule S as serializable if it’s committed C(CS) is equivalent to some serial schedule.

3.7.Locking techniques for concurrency control

Many of the important techniques for concurrency control make use of the concept of the lock. A lock is a variable associated with a data item that describes the status of the item with respect to the possible operations that can be done on it. Normally every data item is associated with a unique lock. They are used as a method of synchronizing the access of database items by the transactions that are operating concurrently. Such controls, when implemented properly can overcome many of the problems of concurrent operations listed earlier. However, the locks themselves may create a few problems, which we shall be seeing in some detail in subsequent sections.

Types of locks and their uses:

Binary locks: A binary lock can have two states or values ( 1 or 0) one of them indicate that it is locked and the other says it is unlocked. For example if we presume 1 indicates that the lock is on and 0 indicates it is open, then if the lock of item(X) is 1 then the read_tr(x) cannot access the time as long as the lock’s value continues to be 1. We can refer to such a state as lock (x).

The concept works like this. The item x can be accessed only when it is free to be used by the transactions. If, say, it’s current value is being modified, then X cannot be (infact should not be) accessed, till the modification is complete. The simple mechanism is to lock access to X as long as the process of modification is on and unlock it for use by the other transactions only when the modifications are complete.

So we need two operations lockitem(X) which locks the item and unlockitem(X) which opens the lock. Any transaction that wants to makes use of the data item, first checks the lock status of X by the lockitem(X). If the item X is already locked, (lock status=1) the transaction will have to wait. Once the status becomes = 0, the transaction accesses the item, and locks it (makes it’s status=1). When the transaction has completed using the item, it issues an unlockitem (X) command, which again sets the status to 0, so that other transactions can access the item.

Notice that the binary lock essentially produces a “mutually exclusive” type of situation for the data item, so that only one transaction can access it. These operations can be easily written as an algorithm as follows:

The Locking algorithm

Lockitem(X):

Start: if Lock(X)=0, /* item is unlocked*/

Then Lock(X)=1 /*lock it*/

Else

{

wait(until Lock(X)=0) and

the lock manager wakes up the transaction)

go to start

}

The Unlocking algorithm:

Unlock item(X):

Lock(X)¬ 0; ( “unlock the item”)

{ If any transactions are waiting,

Wakeup one of the waiting transactions}

The only restrictions on the use of the binary locks are that they should be implemented as indivisible units (also called “critical sections” in operating systems terminology). That means no interleaving operations should be allowed, once a lock or unlock operation is started, until the operation is completed. Otherwise, if a transaction locks a unit and gets interleaved with many other transactions, the locked unit may become unavailable for long times to come with catastrophic results.

To make use of the binary lock schemes, every transaction should follow certain protocols:

1. A transaction T must issue the operation lockitem(X), before issuing a readtr(X) or writetr(X).

2. A transaction T must issue the operation unlockitem(X) after all readtr(X) and write_tr(X) operations are complete on X.

3. A transaction T will not issue a lockitem(X) operation if it already holds the lock on X (i.e. if it had issued the lockitem(X) in the immediate previous instance)

4. A transaction T will not issue an unlockitem(X) operation unless it holds the lock on X.

Between the lock(X) and unlock(X) operations, the value of X is held only by the transaction T and hence no other transaction can operate on X, thus many of the problems discussed earlier are prevented.

Shared/Exclusive locks

While the operation of the binary lock scheme appears satisfactory, it suffers from a serious drawback. Once a transaction holds a lock (has issued a lock operation), no other transaction can access the data item. But in large concurrent systems, this can become a disadvantage. It is obvious that more than one transaction should not go on writing into X or while one transaction is writing into it, no other transaction should be reading it, no harm is done if several transactions are allowed to simultaneously read the item. This would save the time of all these transactions, without in anyway affecting the performance.

This concept gave rise to the idea of shared/exclusive locks. When only read operations are being performed, the data item can be shared by several transaction, only when a transaction wants to write into it that the lock should be exclusive. Hence the shared/exclusive lock is also sometimes called multiple mode lock. A read lock is a shared lock (which can be used by several transactions), whereas a writelock is an exclusive lock. So, we need to think of three operations, a read lock, a writelock and unlock. The algorithms can be as follows:

Read Lock Operation:

Readlock(X):

Start: If Lock (X) = “unlocked”

Then {

Lock(X) “read locked”,

No of reads(X) 1

}

else if Lock(X) = “read locked”

then no. of reads(X) = no of reads(X)0+1;

else { wait until Lock(X) “unlocked” and the lock manager

wakes up the transaction) }

go to start

end.

The writelock operation:

Writelock(X)

Start: If lock(X) = “unlocked”

Then Lock(X) “write-locked”.

Else { wait until Lock(X) = “unlocked” and

The lock manager wakes up the transaction}

Go to start

End;

The Unlock Operation:

Unlock(X)

If lock(X) = “write locked”

Then { Lock(X) “unlocked”’

Wakeup one of the waiting transaction, if any

}

else if Lock(X) = “read locked”

then { no of reads(X) no of reads –1

if no of reads(X)=0

then { Lock(X) = “unlocked”

wakeup one of the waiting transactions, if any

}

The algorithms are fairly straight forward, except that during the unlocking operation, if a number of read locks are there, then all of them are to be unlocked before the unit itself becomes unlocked.

To ensure smooth operation of the shared / exclusive locking system, the system must enforce the following rules:

1. A transaction T must issue the operation readlock(X) or writelock(X) before any read or write operations are performed.

2. A transaction T must issue the operation writelock(X) before any writetr(X) operation is performed on it.

3. A transaction T must issue the operation unlock (X) after all readtr(X) are completed in T.

4. A transaction T will not issue a readlock(X) operation if it already holds a readlock or writelock on X.

5. A transaction T will not issue a writelock(X) operation if it already holds a readlock or writelock on X.

Conversion Locks

In some cases, it is desirable to allow lock conversion by relaxing the conditions (4) and (5) of the shared/ exclusive lock mechanism. I.e. if a transaction T already holds are type of lock on a item X, it may be allowed to convert it to other types. For example, it is holding a readlock on X, it may be allowed to upgrade it to a writelock. All that the transaction does is to issue a writelock(X) operation. If T is the only transaction holding the readlock, it may be immediately allowed to upgrade itself to a writelock, otherwise it has to wait till the other readlocks (of other transactions) are released. Similarly if it is holding a writelock, T may be allowed to downgrade it to readlock(X). The algorithms of the previous sections can be amended to accommodate the conversion locks and this has been left as on exercise to the students.

Before we close the section, it should be noted that use of binary locks does not by itself guarantee serializability. This is because of the fact that in certain combinations of situations, a key holding transaction may end up unlocking the unit too early. This can happen because of a variety of reasons, including a situation wherein a transaction feels it is no more needing a particular data unit and hence unlocks, it but may be indirectly writing into it at a later time (through some other unit). This would result in ineffective locking performance and the serializability is lost. To guarantee such serializability, the protocol of two phase locking is to be implemented, which we will see in the next section.

Two phase locking:

A transaction is said to be following a two phase locking if the operation of the transaction can be divided into two distinct phases. In the first phase, all items that are needed by the transaction are acquired by locking them. In this phase, no item is unlocked even if it’s operations are over. In the second phase, the items are unlocked one after the other. The first phase can be thought of as a growing phase, wherein the store of locks held by the transaction keeps growing. In the second phase, called the shrinking phase, the no. of locks held by the transaction keep shrinking.

readlock(Y)

readtr(Y) Phase I

writelock(X)

-----------------------------------

unlock(Y)

Readtr(X) Phase II

X=X+Y

writetr(X)

unlock(X)

fig12

3.8Query Optimization Techniques:

1. Heuristic-based query optimization – This is based on heuristic rules for ordering the operations in a query execution strategy. In general, many different relational algebra expressions-and hence many different query trees can be equivalent.i.e they can correspond to the same query. The query parser will typically generate a standard initial query tree to correspond to an SQL query without doing an optimization. The optimizer must include rules for equivalence among relational algebra expressions that can be applied to the query. The heuristic query optimization rules then utilize these equivalence expressions to transform the initial tree, into the final optimized query tree.

General transformation rules for relational algebra operations:

1. Cascade of σ : A conjunctive selection condition can be broken up into a cascade of individual σ operations.

2. Commutativity of σ : The σ operation is commutative.

3. Cascade of П : In a cascade of П operations, all but the last one can be ignored.

4. Commutating σ with П : If the selection condition c involves only those attributes A1, A2,…An in the projection list, the 2 operations can be commuted:

П _{A1, A2,..An} (σ_c ( R) ) = σ_c (П _{A1, A2,..An} ( R))

5. Commutativity of $\bowtie$ (and X ) : The $\bowtie$ operation is commutative as is the X operation. i.e. R $\bowtie$ S = S $\bowtie$ R

R X S = S X R

6. Commuting σ with $\bowtie$ (or X) : If all the attributes in the selection condition c involve only the attributes of one of the relations being joined, say R, the two operations can be commuted as follows:

σ_c (R $\bowtie$ S) = (σ_c(R) ) $\bowtie$ S

Alternatively, if the selection condition c can be written as c1 and c2, where condition c1 involves only the attributes of R and condition c2 involves only the attributes of S, the operations commute as follows:

σ_c (R $\bowtie$ S) = (σ_c1(R) ) $\bowtie$ (σ_c2(S) )

The same rule apply if the $\bowtie$ is replaced by a X operation.

7. Commuting П with $\bowtie$ (or X) : Suppose that the projection list is L = {A1, A2,….An, B1, B2,….Bm} where A1, A2, ……..An are attributes of R and B1, B2, ……Bm are attributes of S. If the join condition c involves only attributes in L, the two operations can be commuted as follows: П _L ( R $\bowtie$ c S) = (П _A1,
A2,..An (R) ) $\bowtie$ c (П _{B1, B2,..Bm} (S) )

If the join condition c contains additional attributes not in L, these must be added to the projection list, and a final П operation is needed. i.e. if attributes A_n+1,……,A_n+k of R and B_m+1,……,B_m+pof S are involved in the join condition c but are not in the projection list L, the operations commute as follows:

П _L ( R $\bowtie$ c S) = П _L ( (П _{A1, A2,..An,An+1,…..An+k} (R) ) $\bowtie$ c (П _{B1, B2,..Bm,Bm+1,….Bm+p}(S) )).

For X, there is no condition c, so the first transformation rule always by replacing $\bowtie$ c with X.

8. Commutativity of set operations: The set operations U and ∩ are commutative but – is not.

9. Associativity of $\bowtie$ , X , U and ∩ : These 4 operations are individually associative. i.e if Ө stands for any of these four operations then (R Ө S) Ө T = R Ө (S Ө T).

10. Commuting σ with set operations: The σ operation commutes with U, ∩ and - . If Ө stands for any of these three operations then σ_c(R Ө S) = (σ_c(R ) Ө (σ_c( S)).

11. The П operation commutes with U: П _L ( R U S) = ( П _L ( R)) U (П _L ( S)).

12. Converting a (σ , X) sequence into $\bowtie$ : If the condition c of a σ that follows a X corresponds to a join condition, convert the (σ, X) sequence into a $\bowtie$ as follows:

(σ_c (R X S) = (R $\bowtie$ c S)

Outline Of Heuristic Algebraic Optimization Algorithm

Based on the above mentioned rules we can now outline the steps of the algorithm as :

Using rule1, break up any SELECT operations with conjunctive conditions into a cascade of SELECT operations.
Using rules 2, 4, 6 and 10 concerning the commutativity of SELECT with other operations, move each SELECT operations as far down the query tree as is permitted by the attributes involved in the select condition.
Using rules 5 and 9 concerning commutativity and associativity of binary operations, rearrange the leaf nodes of the tree using the following criteria. First, position the leaf node relations with the most restrictive SELECT operations so they are executed first in the query tree representation. The definition of most restrictive SELECT can mean either the ones that produce a relation with the fewest tuples or with the smallest absolute size. Another possibility is to define the most restrictive SELECT as the one with the smallest selectivity. Second, make sure that the ordering of leaf nodes does not cause CARTESIAN PRODUCT operations. For e.g. if the two relations with the most restrictive SELECT do not have a direct join condition between them, it may be desirable to change the order of leaf nodes to avoid Cartesian products.
Using rule 12, combine a CARTESIAN PRODUCT operation with a subsequent SELECT operation in the tree into a JOIN operation, if the condition represents a join condition.
Using rules 3, 4, 7 and 11 concerning the cascading of PROJECT and the commuting of PROJECT with other operations, break down and move lists of projection attributes down the tree as far as possible by creating new PROJECT operations as needed. Only those attributes needed in the query result and in subsequent operations in the query tree should be kept after each PROJECT operation.
Identify subtrees that represent groups of operations that can be executed by a single algorithm.

2. Cost Based optimization – A query optimizer should not solely depend on heuristic rules; it should also estimate and compare the costs of executing a query using different execution strategies and should choose the strategy with the lowest cost estimate. This approach is more suitable for compiled queries where the optimization is done at compile time and the resulting execution strategy code is stored and executed directly at run-time.

Cost Components for Query Execution

The cost of executing a query includes the following components:

1. Access cost to secondary storage: This is the cost of searching for, reading and writing data blocks that reside on secondary storage, mainly on disk. The cost of searching for records in a file depends on the type of access structures on that file, such as ordering, hashing and primary or secondary indices. In addition, factors such as whether the file blocks are allocated contiguously on the same disk cylinder or scattered on the disk affect the access cost.

2. Storage cost: This is the cost of storing any intermediate files that are generated by an execution strategy for the query.

3. Computation cost: This is the cost of performing in memory operations on the data buffers during query execution. Such operations include searching for and sorting records, merging records for a join and performing computations on field values.

4. Memory usage cost: This is the cost pertaining to the number of memory buffers needed during query execution.

5. Communication cost: This is the cost of shipping the query and its result from the database site to the site or terminal where the query originated.

These components are used for cost function that is used to estimate query execution cost. To estimate the costs of various execution strategies, we must keep track of information that is needed for the cost functions. This information may be stored in the DBMS catalog, where it is accessed by the query optimizer. First, we must know the size of each file. For a file whose records are all of the same type, the number of records(tuples), the (average) record size and the number of blocks are needed. The blocking factor of the file may also be needed.

3.10 Assertions

An assertion is a predicate expressing a condition that we wish the database always to satisfy. Domain constraints and referential-integrity constraints are special forms of assertions. There are many constraints that we cannot express using only these special forms. Examples of such constraints include

1. The sum of all loan numbers for each branch must be less than the sum of all account balances at the branch.

2. Every loan has at least one customer who maintains an account with a minimum balance of $1000.00

An assertion in SQL-92 takes the form

Create assertion

check

The two constraints mentioned can be written as shown next. Since SQL does not provide a “for all X, P(X)” construct (where P is a predicate), we are forced to implement the construct using the equivalent “not exists X such that not P(X) ” construct , which can be written in SQL.

Create assertion sum-constraint check (not exists (select * from branch where (select sum(amount) from loan where loan.branch-name=branch.branch-name) >= (select sum(amount) from account where loan.branch-name=branch.branch-name)))
Create assertion balance-constraint check (not exists (select * from loan where not exists (select * from borrower, depositor, account where

loan.loan-number=borrower.loan-number and

borrower.customer-name=depositor.customer-name and

depositor.account-number=account.account.number and account.balance>=1000)))

When an assertion is created, the system tests it for validity. If the assertion is valid, then any future modification to the database is allowed only if it does not cause that assertion to be violated.

3.10 Triggers

A trigger is a statement that is executed automatically by the system as a side effect of a modification to the database. To design a trigger mechanism, we must meet two requirements:

1. Specify the conditions under which the trigger is to be executed.

2. Specify the actions to be taken when the trigger executes

3.11 The basic structure of the oracle system

An Oracle server consists of an Oracle database – the collection of stored data, including log and control files – and the Oracle instance – the processes, including Oracle (system) processes and user processes taken together, created for a specific instance of the database operation.

Oracle Database Structure

The Oracle database has two primary structures:

A physical structure – referring to the actual stored data.
A logical structure – corresponding to an abstract representation of stored data, which roughly corresponds to the conceptual schema of the databases.

The database contains the following types of files:

One or more data files; these contain the actual data.
Two or more log files called redo log files; these record all changes made to data and are used in the process of recovering, if certain changes do not get written to permanent storage.
One or more control files; these contain control information such as database name, file names and locations and a database creation timestamp.
Trace files and an alert log; background processes have a trace file associate with them and the alert log maintains major database events.

The structure of an Oracle database consists of the definition of database in terms of schema objects and one or more tablespaces. The schema objects contain definitions of tables, views, sequences, stored procedures, indexes, clusters and database links.

Oracle instance: The set of processes that constitute an instance of the server’s operation is called an Oracle instance, which consists of a System Global Area and a set of background processes.

· System Global Area (SGA) : This area of memory is used for database information shared by users. Oracle assigns an SGA area when an instance starts. The SGA in turn is divided into several types of memory structures:

Database buffer cache: This keeps the most recently accessed data blocks from the database. This helps in reducing the disk I/O activity.
Redo log buffer, which is the buffer for the redo log file and is used for recovery purposes.
Shared pool, which contains shared memory constructs.

· User processes : Each user process corresponds to the execution of some application or some tool.

· Program Global Area (PGA): This is a memory buffer that contains data and control information for a server process.

· Oracle processes: A process is a thread of control or a mechanism in an operating system that can execute a series of steps. A process has its own private memory area where it runs.

Oracle Processes: Oracle creates server processes to handle requests from connected user processes. The background processes are created for each instance of Oracle; they perform I/O asynchronously and provide parallelism for better performance.

Oracle Startup and Shutdown: An Oracle database is not available to users until the Oracle server has been started up and the database has been opened. Starting a database and making it available system wide requires the following steps:

1. Starting an instance of the database: The SGA is allocated and background processes are created in this step.

2. Mounting a database: This associates a previously started Oracle instance with a database. Until then it is available only to administrators. The database administrator chooses whether to run the database in exclusive or parallel mode. When an oracle instance mounts a database in an exclusive mode, only that instance can access the database. On the other hand, if the instance is started in a parallel or share mode, other instances that are started in parallel mode can also mount the database.

3. Opening a database: Opening a database makes it available for normal database operations by having oracle open the on-line data files and log files.

The reverse of the above operations will shut down an Oracle instance as follows:

Close the window.
Dismount the database.
Shut down the Oracle instance.

3.12 Database structure and its manipulation in oracle

Schema Objects: In Oracle schema refers to a collection of data definition objects. Schema objects are the individual objects that describe tables, views etc. Tables are the basic units of data. Synonyms are direct reference to objects. Program units include function, stored procedure or package.

Oracle Data Dictionary: This is a read-only set of tables that keeps the metadata – schema description – for a database. Oracle dictionary, has the following components:

Names of users
Security information
Schema objects information
Integrity constraints
Space allocation and utilization of database objects
Statistics on attributes, tables and predicates
Access audit trail information

3.13 Storage organization in oracle

A database is divided into logical storage units called tablespaces, with the following characteristics:

Each database is divided into one or more tablespaces.
There is a system tablespace and users tablespace.
One or more datafiles are created in each tablespace.
The combined storage capacity of a database’s tablespace is the total storage capacity of the database.

Data Blocks: Data Block represents the smallest unit of I/O. A data block has the following components:

Header: Contains general block information such as block address and type of segment.
Table directory: Contains information about tables that have data in the data block.
Row directory: Contains information about the actual rows.
Row data: Uses the bulk of space in the data block.
Free space: Space allocated for row updates and new rows.

Extents: When a table is created, Oracle allocates it an initial extent. Incremental extents are automatically allocated when the initial extent becomes full. All extents allocated in index segments remain allocated as long as the index exists. When an index associated with a table or cluster is dropped, Oracle reclaims the space.

Segments: A segment is made up of a number of extents and belongs to a tablespace. Oracle uses the following types of 4 segments:

Data segments: Each nonclustered table and each cluster has a single data segment to hold all its data, which is created when the application creates the table or cluster with the CREATE command.

Index segments: Each index in an Oracle database has a single index segment, which is created with the CREATE INDEX command.

Temporary segments: These are created by Oracle for use by SQL statements that need a temporary work area.

Rollback segments: Each database must contain one or more rollback segments, which are used for “undoing” transactions.

3.14 Programming in PL/SQL:

BLOCK PL/SQL STRUCTURE:

PL/SQL is a block-structured language. A PL/SQL block defines a unit of processing, which can include its own local variables, SQL statements, cursors, and exception handlers. The blocks can be nested. The simplest block structure is given below.

DECLARE

Variable declarations

BEGIN

Program statements

EXCEPTION

WHEN exception THEN

Program Statements

END;

In the above PL/SQL block, block parts are logical. Blocks starts with DECLARATION section in which memory variables and other oracle objects can be declared. The next section contains SQL executable statements for manipulating table data by using the variables and constants declared in the DECLARE section. EXCEPTIONS is the last sections of the PL/SQL block which contains SQL and/or PL/SQL code to handle errors that may crop up during the execution of the above code block. EXCEPTION section is optional.

Each block can contain other blocks, i.e. blocks can be nested. Blocks of the code cannot be nested in the DECLARATION section.

PL/SQL CHARACTER SET

PL/SQL uses the standard ASCII set. The basic character set includes the following.

§ Uppercase alphabets A to Z.

§ Lowercase alphabets a to z.

§ Numbers 0 to 9

§ Symbols ( ) + - * / < > = ! ; : , . @ ‘

% “ # $ ^ & _ \ { } ? [ ]

Words used in a PL/SQL blocks are called lexical units. We can freely insert blank spaces between lexical units in a PL/SQL blocks. The spaces have no effect on the PL/SQL block.

The ordinary symbols used in PL/SQL blocks are

( ) + - * / < > = ; % , “ [ ] :

Compound symbols used in PL/SQL block are

<> != -= ^= <= >= : = ** || << >>

VARIABLES

Variables may be used to store the result of a query or calculations. Variables must be declared before being used. Variables in PL/SQL block are named variables. A variable name must begin with a character and can be followed by a maximum of 29 other characters (variable length is 30 characters).

Reserved words cannot be used as variable names unless enclosed within the double quotes. Variables must be separated from each other by at least one space or by a punctuation mark.

The case (upper/lower) is insignificant when declaring variable names. Space cannot be used in a variable name.

LITERALS

A literal is a numeric value or a character string used to represent itself. So, literals can be classified into two types.

· Numeric literals

· Non- numeric literals (string literals)

Numeric literals:

These can be either integers or floating point numbers. If a float is being represented, then the integer part must be separated from the float part by a period ( . ).

Integers 25 43 437 -57 etc

Floats 6.34 25E-03 0.1 +17.1 etc

Non numeric literals:

These are represented by one or more legal characters and must be enclosed within single quotes.

Ex: ‘ Hello world ’

‘ EMPLOYEE NAME ’

‘ ******* ’

‘ A’

‘ * ’

We can represent single quote character itself in a non-numeric literal by writing it twice.

Ex: ‘Don’’t go without saving the program’

PL/SQL will also have literals, which are called as logical (boolean) literals. These are predetermined constants. The value it can take are TRUE, FALSE, and NULL.

COMMENTS

v A comment line begins with a double hyphen (--). In this case the entire

line will be treated as a comment.

Ex: -- This section performs salary updation.

v The comment line begins with a slash followed by an asterisk (/*) till the

occurrence of an asterisk followed by a slash (*/). In this case comment

lines can be extended to more than one lines.

Ex-1: /* this is only for user purpose

which calculates the total salary temporarily

and stores the value in temp_sal */

Ex-2: /* This takes rows from /* table EMPLOYEE */

and put on another table */

In the above comment, there is a comment within an another comment line,

this is not allowed in PL/SQL.

PL/SQL DATA TYPES AND DECLARATIONS:

PL/SQL supports the standard ORACLE SQL data types. The default data types that

can be declared in PL/SQL are

v NUMBER: For storing numeric data

Syntax: variable name NUMBER (precision, [scale])

precision determines the number of significant digits that NUMBER

can contain. Scale determines the number of digits to the right of the

decimal point.

Ex: NUMBER (4,2) stores 4234.60

NUMBER (10) stores 3289473348

v CHAR: This data type stores fixed length character data.

Syntax: Variable name CHAR (size)

where size specifies fixed length of the variable name.

Ex: CHAR (10) stores MASTERFILE

v VARCHAR2: It stores variable length character string data.

Syntax: Variable name VARCHAR2 (size)

Where size specifies the maximum length of the variable name.

Ex: VARCHAR2 (20) stores TRANSACTIONFILE

v DATE: The date data types store a date and time.

Syntax: variable name DATE

Ex: date_of_birth DATE

v BOOLEAN: This data type stores only TRUE, FALSE or NULL values.

Syntax: variable name BOOLEAN

Ex: flag BOOLEAN.

%TYPE declares a variable or constant to have the same data type as that of a previously defined variable or of a column in a table or in a view.

NOT NULL causes creation of a variable or a constant that cannot have a NULL value. If you attempt to assign the value NULL to a variable or a constant that has been assigned a NOT NULL constraint, causes an error.

NOTE: As soon as a variable or constant has been declared as NOT NULL, it must be assigned a value. Hence every NOT NULL declaration of a variable or constant needs to be followed by PL/SQL expression that loads a value into the variable or constant declared.

DECLARING VARIABLES

We can declare a variable of any data type either native to the ORACLE or native to PL/SQL. Variables are declared in the DECLARE section of the PL/SQL block. Declaration involves the name of the variable followed by its data type. All statement must end with a semicolon (;) which is the delimiter in PL/SQL. To assign a value to the variable the assignment operator (:=) is used.

The general syntax is [ := ];

Ex: pay NUMBER (6,2);

in_stack BOOLEAN;

name VARCHAR2 (30);

room CHAR (2);

date_of_purchase DATE;

ASSIGNING A VALUE TO A VARIABLE:

A value can be assigned to the variable in any one of the following two ways.

v Using the assignment operator :=

Ex: tax := price * tax_rate

pay := basic + da.

v Selecting or fetching table data values in to variables.

Ex: SELECT sal INTO pay

FROM Employee

WHERE emp_name = ‘SMITH’;

DECLARING A CONSTANT:

Declaring a constant is similar to declaring a variable except that you have to add

the key word CONSTANT and immediately assign a value to it. Thereafter, no further assignment to the constants is possible.

Ex: pf_percent CONSTANT NUMBER (3,2) := 8.33;

PICKING UP A VARIABLE’S PARAMETERS FROM A TABLE CELL

The basic building block of a table is a cell (i.e. table’s column). While creating a table user attaches certain attributes like data type and constraints. These attributes can be passed on to the variables being created in PL/SQL. This simplifies the declaration of variables and constants.

For this purpose, the %TYPE attribute is used in the declaration of a variable when the variable’s attributes must be picked from a table field (i.e. column).

Ex: current_sal employee.sal % TYPE

In the above example, current_sal is the variable of PL/SQL block. It gets the data type

and constraints of the column (field) sal belong to the table Employee. Declaring a variable

with the %TYPE attribute has two advantages

v You do not need to know the data type of the table column

v If you change the parameters of the table column, the variable’s parameters will

change as well.

PL/SQL allows you to use the %TYPE attribute in a nesting variable declaration. The following example illustrates several variables defined on earlier %TYPE declarations in a nesting fashion.

Ex: Dept_sales INTEGER;

Area_sales dept_sales %TYPE0;

Group_sales area_sales %TYPE;

Regional_sales area_sales %TYPE;

Corporate_sales regional_sales %TYPE;

In case, variables for the entire row of a table need to be declared, then instead

of declaring them individually, %ROWTYPE is used.

Ex: emp_row_var employee %ROWTYPE;

Here, the variable emp_row_var will be a composite variable, consisting of the column

names of the table as its member. To refer to a specific, say ‘sal’; the following statement will be used.

emp_row_var.sal := 5000;

AN IDENTIFIER IN PL/SQL BLOCK:

The name of any ORACLE object (variable, memory variable, constant, record, cursor etc) is known as an Identifier. The following laws have to be followed while working with identifiers.

An identifier cannot be declared twice in the same block

The same identifier can be delcared in two different blocks.

· In the second law, the two identifiers are unique and any change in one does not affect the other.

2.2

PL/SQL OPERATORS

Operators are the glue that holds expressions together. PL/SQL operators can be divided into

the following categories.

· Arithmetic operators

· Comparison operators

· Logical operators

· String operators

PL/SQL operators are either unary (i.e. they act on one value/variable) or binary (they act on two values/variables)

1) ARITHMETIC OPERATORS:

Arithmetic operators are used for mathematical computations. They are

+ Addition

- Subtraction or Negation ( Ex: -5)

* Multiplication

/ Division

** Exponentiation operator (example 10**5 = 10^5)

2) COMPARISON OPERATORS:

Comparison operators return a BOOLEAN result, either TRUE or FALSE. They are

= Equality operator 5=3

!= Inequality operator a!=b

<> Inequality operator 5<>3

-= Inequality operator ‘john’ -= ’johny’

< Less than operator a

> Greater than operator a>b

<= Less than or equal to a<=b

>= Greater than or equal to a>=b

In addition to this PL/SQL also provides some other comparison operators like LIKE, IN,

BETWEEN, IS NULL etc.

LIKE: Pattern-matching operator.

It is used to compare a character string against a pattern. Two wild card characters are defined for use with LIKE, the % (percentage sign) and ( _ ) underscore. The % sign matches any number of characters in a string and ( _ ) matches exactly one.

Ex-1: new%matches with newyork, newjersey etc (i.e. any string beginning with

the word new).

Ex-2: ‘_ _ _day’ matches with Sunday, Monday and Friday and It will not

match with other days like ‘Tuesday’, ‘Wednesday’, ‘Thursday’ and

‘Saturday’.

IN: Checks to see if a value lies within a specified list of values. The syntax is

Syntax: The_value [NOT] IN (value1, value2, value3……)

Ex: 3 IN (4, 8, 7, 5, 3, and 2) Returns TRUE.

Sun NOT IN ( ‘sat’, ‘mon’, ‘tue’, ‘wed’, ‘sun’) Returns TRUE.

BETWEEN: Checks to see if a value lies with in a specified range of value.

Syntax: the_value [NOT] BETWEEN low_end AND high_end.

Ex: 5 BETWEEN –5 AND 10. Returns TRUE

4 NOT BETWEEN 3 AND 4 Returns FALSE.

IS NULL: Checks to see if a value is NULL.

Syntax: the_value IS [NOT] NULL

Ex: If balance IS NULL then

If acc_id IS NOT NULL then

3) LOGICAL OPERATORS.

PL/SQL implements 3 logical operations AND, OR and NOT. The NOT operator is unary operator and is typically used to negate the result of a comparison expression, where as the AND and OR operators are typically used to link together multiple comparisons.

v A AND B is true only if A returns TRUE and B returns TRUE else it is FALSE.

v A OR B is TRUE if either of A or B is TRUE. And it is FALSE if both A and B

are FASLE.

v NOT A Returns TRUE if A is FALSE

Returns FALSE if A is TRUE.

Ex: (5 = 5) AND (4<20 and="">=2) Returns TRUE

(5=5) OR (5!=4) Returns TRUE.

‘mon’ IN ( ‘sun’, ‘sat’) OR (2 = 2) Returns TRUE.

4) STRING OPERATORS:

PL/SQL has two operators specially designed to operate only on character string type data. These are LIKE and ( || ) Concatenation operator. LIKE is a comparison operator and is used to compare strings and it is discussed in the previous session. Concatenation operator has following syntax.

Synatx: String_1 || string_2

String_1 and string_2 both are strings and can be a string constants, string variables or string expressions. The concatenation operator returns a resultant string consisting of all the characters in string_1 followed by all the characters in string_2.

Ex : ‘Chandra’ || ’shekhar’ Returns ‘Chandrashekhar’

A=’Engineering’ B=’College’ C=VARCHAR2 (50)

C=A || ‘ ‘ || B Returns a value to variable C as ‘Engineering College’.

NOTE-1: PL/SQL string comparisons are always case sensitive, i.e. ‘aaa’ not equal to

‘AAA’.

NOTE-2: ORACLE has some built in functions that are designed to convert from one

data type to another data type.

To_date: Converts a character string into date

To_number: Converts a character string to a number.

To_char: Converts either a number or date to character string.

Ex: To_date (‘1/1/92’, ‘mm/dd/yy/’); Returns 01-jan-1992.

To_date (‘1-1-1998’, ‘mm-dd-yyyy’); Returns 01/01/1998.

To_date (‘Jan 1, 2001’,’mm dd, yyyy’); Returns Saturday, January 01, 2001.

To_date (‘1/1/02’, ‘mm/dd/rr’); Returns Tue, Jan 01, 2002.

To_number (‘123.99’, ‘999D99’); Returns 123.99

To_number ( ‘$1,232.95’, ‘$9G999D99’); Returns $1, 232.99

To_char (123.99, ‘999D99’); Returns 123.99.

CONDITIONAL CONTROL IN PL/SQL :

In PL/SQL, the if statement allows you to control the execution of a block of

code. In PL/SQL we can use the following if forms

v IF condition THEN statements END IF;

v IF condition THEN statements

ELSE statements

END IF;

v IF condition THEN statements

ELSE IF condition THEN

Statements

ELSE statements

END IF

END IF;

ITERATIVE CONTROL IN PL/SQL :

PL/SQL provides iterative control and execution of PL/SQL statements in the

block. This is the ability to repeat or skip sections of a code block. Following are

the four types of iterative statements provided by the PL/SQL

· The Loop statement

· The WHILE Loop statement

· The GOTO statement

· FOR Loop

i. LOOP STATEMENT:

A loop repeats a sequence of statements. The format is as follows.

LOOP

Statements

END LOOP;

The one or more PL/SQL statements can be written between the key words LOOP and END LOOP. Once a LOOP begins to run, it will go on forever. Hence loops are always accompanied by a conditional statements that keeps control on the number of times it is executed. We can also build user defined exists from a loop, where required.

Ex: LOOP

Cntr : = cntr + 1;

IF cntr > 100

EXIT;

END IF;

END LOOP;

EXIT statement brings the control out of loop if the condition is satisfied.

ii. WHILE LOOP :

The WHILE loop enables you to evaluate a condition before a sequence of statements would be executed. If condition is TRUE then sequence of statements are executed. This is different from the FOR loop where you must execute the loop atleast once. The syntax for the WHILE loop is as follows:

Syntax: WHILE < Condition is TRUE >

LOOP

< Statements >

END LOOP;

Ex : DECLARE

Count NUMBER(2) : = 0;

BEGIN

WHILE count < = 10

LOOP

Count : = count + 1;

Message('while loop executes');

END LOOP;

END;

EXIT and EXIT WHEN statement:

EXIT and EXIT WHEN statements enable you to escape out of the control of a loop. The format of the EXIT statement is as follows :

Syntax: EXIT;

EXIT WHEN statements has following syntax

Syntax: EXIT WHEN ;

EXIT WHEN statement enables you to specify the condition required to exit the execution of the loop. In this case no if statement is required.

Ex-1: IF count > = 10 EXIT;

Ex-2: EXIT WHEN count > = 10;

iii. THE GOTO STATEMENT :

The GOTO statement allows you to change the flow of control within a PL/SQL

block. The syntax is as follows

Syntax: GOTO ;

The label is surrounded by double brackets (<< >>) and label must not have a semi colon after the label name. The label name does not contain a semi colon because it is not a PL/SQL statement. But rather an identifier of a block of PL/SQL code. You must have at least one statement after the label otherwise an error will result. The GOTO destination must be in the same block, at the same level as or higher than the GOTO statement itself.

Ex: IF result = 'fail' THEN

GOTO failed_stud

END IF;

Message ('student is failed');

The entry point of the destination block is defined within << >> as shown above, i.e. labels are written within the symbol << >>. Notice that <> is a label and it is not ended with semicolon ( ; ).

iv. FOR LOOP:

FOR loop will allow you to execute a block of code repeatedly until some condition occurs. The syntax of FOR loop is as follows.

Syntax: FOR loop_index IN [ REVERSE] low_value .. High_value LOOP

Statements to execute

END LOOP;

The loop_index is defined by oracle as a local variable of type integer. REVERSE allows you to execute the loop in reverse order. The low_value .. High_value is the range to execute the loop. These can be constants or variables. The line must be terminated with loop with no semicolon at the end of this line. You can list the statements to be executed until the loop is executed is evaluated to false.

Ex: FOR v_count IN 1 .. 5 LOOP

Message ('for loop executes');

END LOOP;

In the above example the message 'for loop executes' is displayed five times.

We can terminate the FOR loop permanently using EXIT statement based on some BOOLEAN condition. Nesting of FOR loop can also be allowed in PL/SQL. The outer loop executed once, then the inner loop is executed as many times as the range indicates, and then the control is returned to the outer loop until its range expires.

Ex: FOR out_count IN 1..2 LOOP

FOR in_count IN 1..2 LOOP

Message ('nested for loop');

END LOOP;

In the above example the message 'nested for loop' is displayed four times.

Let us discuss some examples from the understanding how to write a PL/SQL block structure. Here we assume that a table called "EMP" is created and the datas are already inserted into it.

Table name : EMP

Create table EMP

( emp_no NUMBER (3),

name VARCHAR2 (15),

salary NUMBER (6,2),

dept VARCHAR2 (15),

div VARCHAR2 (2) );

EXAMPLE-1:

DECLARE

num NUMBER (3);

sal emp.salary %TYPE;

emp_name emp.name %TYPE;

count NUMBER (2) : = 1;

starting_emp CONSTANT NUMBER(3) : = 134;

BEGIN

SELECT name, salary INTO emp_name, sal

FROM EMP

WHERE emp_no = starting_emp;

WHILE sal < 4000.00

LOOP

Count : = count + 1;

SELECT emp_no, name, salary INTO

Num, emp_name, sal FROM EMP

WHERE emp_no > 2150;

END LOOP;

Commit;

END;

In the above example there are five statements in the declaration part. The num is a integer type, sal and emp_name takes the similar data type of the salary and name columns of EMP table respectively. Count is a variable of type integer and takes initial value 1. Starting_emp is a constant and it is of integer type with immediately assigned value 134.

Between BEGIN and END key words, there are some SQL executable statements used for manipulating the table data. The SELECT statement extracts data stored in name and salary columns of EMP table corresponding to the employee having employee number 134. It stores those values In the variables emp_name and sal respectively.

If sal less than 4000 then the statements within the loop will be executed. Within the loop, there are two SQL statements, the first one increments the count value by 1 and the second statement is a SELECT statement. The commit statement commits the changes made to that table. The END statement terminates the PL/SQL block.

EXAMPLE-2:

This example assumes the existence of table accounts created by using the following SQL statements.

Create table Accounts

(accnt_id NUMBER(3),

name VARCHAR2(25),

bal NUMBER(6,2) );

PL/SQL block:

DECLARE

acct_balance NUMBER(6,2);

acct CONSTANT NUMBER(3) : = 312;

debit_amt CONSTANT NUMBER(5,2) : = 500.00;

BEGIN

SELECT bal INTO acct_balance FROM Accounts

WHERE accnt_id = acct;

IF acct_balance = debit_amt THEN

UPDATE Accounts

SET bal : = bal - debit_amt WHERE accnt_id = acct;

ELSE

Message ('insufficient amount in account');

END IF;

END;

The above example illustrates the use of IF .. THEN .. ELSE.. END IF condition control statements.

Declaration part declares one variable and two constants. The SELECT statement extracts the amount in the bal column of Accounts table corresponding to account number 312, and stores that in a variable acct_balance.

If statement checks acct_balance for sufficient amount before debiting. It updates the table Accounts if it has sufficient amount in the balance, else it displays a message intimating insufficient fund in the account of specified accnt_id.

EXAMPLE-3:

This example assumes two tables, which are created by following statements.

Create table Inventory

( prod_no NUMBER (6),

product VARCHAR2 (15),

quantity NUMBER (5) );

Create table Purchase_record

( mesg VARCHAR2 (50),

d_ate DATE );

PL/SQL block :

DECLARE

num_in_stack NUMBER(5);

BEGIN

SELECT quantity INTO num_in_stack

FROM Inventory WHERE product = 'gasket';

IF num_in_stack > 0 THEN

UPDATE Inventory SET quantity : = quantity - 1

WHERE product = 'gasket';

INSERT INTO Purchase_record

VALUES (' One gasket purchased', sysdate);

ELSE

INSERT INTO Purchase_record

VALUES ('no gasket availabel',sysdate);

Message ( 'there are no more gasket in stack' );

END IF;

Commit;

END;

The above block of PL/SQL code does the following;

It determines how many gaskets are left in stack.

If the number left in staff is greater than zero, it updates the inventory to reflect the sale of a gasket.

It stores the fact that a gasket was purchased on a certain date.

If the stock available is zero, it stores the fact that there are no more

gaskets for sale on the date on which such a situation occurred.

ERROR HANDLING IN PL/SQL :

PL/SQL has the capability of dealing with the errors that arise while executing a PL/SQL block of code. It has a number of conditions that are pre programmed in to it that are recognized as error conditions. These are called internally defined exceptions. You can also program PL/SQL to recognize user-defined exceptions.

There are two different types Error conditions ( Exceptions).

· user defined error conditions / exceptions.

· Predetermined internal PL/SQL exceptions.

1) USER DEFINED EXCEPTIONS:

User can write a set of code, which is to be executed while error occurs when executing a PL/SQL block of code. These set of code are called user-defined exceptions, and these are placed in the last section of PL/SQL block called EXCEPTIONS.

The method used to recognise user-defined exceptions is as follows

v Declare a user defined exception in the declaration section of PL/SQL block.

v In the main program block for the conditions that needs special attention, execute a RAISE statement.

v Reference the declared exception with an error handling routine in EXCEPTION section of PL/SQL block.

RAISE statement acts like CALL statement of high level languages. It has general format

RAISE < name of exception >

When RAISE statement is executed, it stops the normal processing of PL/SQL block

of code and control passes to an error handler block of the code at the end of PL/SQL

program block ( EXCEPTION section ).

An exception declaration declares a name for user defined error conditions that the PL/SQL code block recognizes. It can only appear in the DECLARE section of the PL/SQL code which preceedes the key word BEGIN.

EXAMPLE :

DECLARE

---------------

zero_commission Exception;

---------------

BEGIN

-----------------

IF commission = 0 THEN

RAISE zero_commission;

------------------------

EXCEPTION

WHEN zero_commission THEN

Process the error

END;

Exception handler (error handler block ) is written between the key words EXCEPTION and END. The exception handling part of a PL/SQL code is optional. This block of code specifies what action has to be taken when the named exception condition occurs.

The naming convention for exception name are exactly the same as those for variables or constants. All the rules for accessing an exception from PL/SQL blocks are same as those for variables and constants. However, it should be noted that exceptions cannot be passed as arguments to functions or procedures like variables or constants.

2) PREDETERMINED INTERNAL PL/SQL EXCEPTIONS:

The ORACLE server defines several errors with standard names. Although every ORACLE error has a number, the error must be referred by name. PL/SQL has predefined some common ORACLE errors and exceptions. Some of them are given below:

· NO_DATA_FOUND Raised when a select statement returns zero rows.

· TOO_MANY_ROWS Raised when a select statement returns more than one rows.

· VALUE_ERROR Raised when there is either a data type mismatch or if the size is smaller than required size.

· INVALID_NUMBER Raised when conversion of a number to a character string failed.

· ZERO_DIVIDE Raised when attempted to divide by zero.

· PROGRAM_ERROR Raised if PL/SQL encounters an internal problem.

· STORAGE_ERROR Raised if PL/SQL runs out of memory or if memory if corrupted.

· DUP_VAL_ON_INDEX Raised when attempted to insert or update a duplicate into a column that has unique index.

· INVALID_CURSOR Raised when illegal cursor operation was attempted.

· CURSOR_ALREADY_OPEN Raised when attempted to open a cursor that was previously opened.

· NOT_LOGGED_ON Raised when a database call was made without being logged into ORACLE.

· LOGIN_DENIED Raised when login to ORACLE failed because of invalid username and password.

· OTHERS This will be raised when the all other exceptions failed to catch the errors.

It is possible to use WHEN OTHERS clause in the exception handling part of the PL/SQL block. It will take care of all exceptions that are not taken care of in the code.

The syntax for exception handling portion of PL/SQL block is as follows:

EXCEPTION

WHEN exception_1 THEN Statements;

WHEN exception_2 THEN Statements;

- - --- ---- -- ---

END;

In this syntax, exception_1 and exception_2 are the names of exceptions (may be predefined or user-defined ). Statements in the PL/SQl code that will be executed if the exception name is satisfied.

EXAMPLE-1:

This example writes PL/SQL code for validating accnt_id of Accounts table so that it must not be left blank, if it is blank cursor should not be allowed to move to the next field.

DECLARE

no_value exception;

BEGIN

IF : Accounts.accnt_id IS NULL THEN

RAISE no_value;

ELSE

next_field;

END IF;

EXCEPTION

WHEN no_value THEN

Message ( 'account id cannot be, blank Please enter valid data !!! ' );

go_field ( : system.cursor_field );

END;

In the above example accnt_id field of Accounts table is checked for NULL value. If it is NULL, then RAISE statement calls exception handler no_value. This exception name no_value is declared in DECLARE section and defined in the EXCEPTION section of PL/SQL block by using WHEN statement. no_value is a user-defined exception.

EXAMPLE-2:

DECLARE

balance Accounts.bal %TYPE;

acount_num Accounts.accnt_id %TYPE;

BEGIN

SELECT accnt_id bal INTO account_num, balance

FROM Accounts WHERE accnt_id > 0000;

EXCEPTION

WHEN no_data_found THEN

Message ('empty table');

END;

In the above example exception is used in the PL/SQL block. This exception is predefined internal PL/SQL exception (NO_DATA_FOUND).

Therefore, it does not require declaration in DECLARE section and RAISE statement in BEGIN … END portion of the block. Even though it is not raised, the ORACLE server will raise this exception when there is no data in bal and accnt_id field.

PL/SQL FUNCTIONS AND PROCEDURES :

PL/SQL allows you to define functions and procedures. These are similare to functions and procedures defined in any other languages, and they are defined as one PL/SQL block.

FUNCTIONS :

The syntax for defining a function is as follows :

FUNCTION name [ (argument-list) ] RETURN data-type {IS, AS}

Variable-declarations

BEGIN

Program-code

[ EXCEPTION

error-handling-code]

END;

In this syntax,

name The name you want to give the function.

argument-list List of input and/or output parameters for the functions.

data-type. The data type of the function's return value.

Variable-declarations Where you declare any variables that are local to the function.

program-code Where you write PL/SQL statements that make up the function.

error-handling-code Where you write any error handling routine.

Notice that the function block is similar to the PL/SQL block that we discussed earlier.

The keyword DECLARE has been replaced by FUNCTION header, which names the function, describes the parameter and indicates the return type.

Functions can be called by using name( argument list )

Example:

FUNCTION check(b_exp in BOOLEAN,

True_number in NUMBER,

False_number in NUMBER)

RETURN NUMBER IS

BEGIN

IF b_exp THEN RETURN true_number;

ELSE

RETURN false_number;

END IF;

END;

The above function can be called as follows.

Check ( 2 > 1, 1 , 0)

Check (5 = 0, 1, 0)

PROCEDURES:

The declaration of procedures is almost identical to that of function and the syntax

is given below.

PROCEDURE name [(argument list)] {IS,AS}

Variable declaration

BEGIN

Program code

[EXCEPTION

Error handling code ]

END;

Here name is the name that you want to give the procedure and all other are similar to function declaration. Procedure declaration resembles a function declaration except that there is no data type and key word PROCEDURE is used instead of FUNCTION.

Ex: PROCEDURE swapn (A IN OUT NUMBER, B IN OUT NUMBER) IS

Temp_num NUMBER;

BEGIN

Temp_num : = A;

A : = B;

B : = temp_num;

END;

The above procedure can be called as follows.

Swapn (3,4);

Swapn (-6,7);

DATABASE TRIGGERS :

PL/SQL can be used to write data base triggers. Triggers are used to define code that is executed/fired when certain actions or event occur. At the data base level, triggers can be defined for events such as inserting a record into a table, deleting a record, and updating a record.

The trigger definition consists of following basic parts.

The events that fires the trigger

The database table on which event must occur

An optional condition controlling when the trigger is executed

A PL/SQL block containing the code to be executed when the trigger is fired.

A trigger is a data base object, like a table or an index. When you define a trigger, it becomes a part of the database and it is always is executed when the event for which it is defined occurs.

Syntax for creating a data base trigger is shown below.

CREATE [ or REPLACE ] TRIGGER trigger-name

{ BEFORE | AFTER } verb-list ON table-name

[ FOR EACH ROW [ WHEN condition ] ]

DECLARE

Declarations

BEGIN

PL/SQL code

END;

In the above syntax

REPLACE Is used to recreate if trigger already exists.

trigger-name Is the name of the trigger to be created.

verb-list The SQL verbs that fire the Create, i.e. it may be INSERT,

UPDATE or DELETE.

table-name The table on which the trigger is defined.

condition An optional condition placed on the execution of the triggers.

declarations. Consists of any variable, record or cursor declarations needed

by this PL/SQL blocks.

PL/SQL code PL/SQL code that gets executed when the trigger fires.

EXAMPLE:

CREATE TRIGGER check_salary

BEGORE insert or update of S AL, JOB on EMP

FOR EACH ROW WHEN ( new. Job != 'director')

DECLARE

minsal NUMBER;

maxsal NUMBER;

BEGIN

SELECT min_sal, max_sal INTO minsal, maxsal

FROM salary-mast WHERE JOB = :new.job;

IF ( :new-sal < minsal or :new.sal > maxsal ) THEN

Message ( 'salary out of range' );

END IF;

END;

3.15 CURSOR IN PL/SQL:

PL/SQL cursors provide a way for your program to select multiple rows of data from the database and then to process each row individually. Cursors are PL/SQL constructs that enable you to process, one row at a time, the results of a multi row query.

ORACLE uses work areas to execute SQL statements, PL/SQL allows user to name private work areas and access the stored information. The PL/SQL constructs to identify each and every work area used by SQL is called a Cursor.

There are 2 types of cursors.

· Implicit cursors

· Explicit cursors

Implicit cursors are declared by ORACLE for each UPDATE, DELETE and INSERT SQL commands. Explicit cursors are declared and used by the user to process multiple row, returned by SELECT statement.

The set of rows returned by a query is called the Active Set. Its size depends on the number of rows that meet the search criteria of the SQL query. The data that is stored in the cursor is called the Active Data Set.

ORACLE cursor is a mechanism used to easily process multiple rows of data. Cursors contain a pointer that keeps track of current row being accessed, which enables your program to process the rows at a time.

EXAMPLE:

When a user executes the following SELECT statement

SELECT emp_no, emp_name, job, salary

FROM employee

WHERE dept = 'physics'

The resultant dataset will be displayed as follows

1234 A. N. Sharanu Asst. Professor 22,000.00

1345 N. Bharath Senior Lecturer 17,000.00

1400 M. Mala Lab Incharge 9,000.00

Table3.1

1) EXPLICIT CURSOR MANAGEMENT:

The following are the steps to using explicitly defined cursors within PL/SQL

Declare the cursor

Open the cursor

Fetch data from the cursor

Close the cursor

Declaring the cursor :

Declaring a cursor enables you to define the cursor and assign a name to it. It has following syntax.

CURSOR cursor-name

IS SELECT statement

Ex: CURSOR c_name IS

SELECT emp_name FROM Emp WHERE dept = 'physics'

Opening a cursor:

Opening a cursor executes the query and identifies the active set that contains all the rows, which meet the query search criteria.

Syntax :

OPEN cursor_name

Ex:

OPEN c_name

Open statement retrieves the records from the database and places it in the cursor (private SQL area).

Fetching data from cursor:

The fetch statement retrieves the rows from the active set one row at a time. The fetch statement is used usually used in conjunction with iterative process ( looping statements ). In iterative process the cursor advances to the next row in the active set each time the fetch command is executed. The fetch command is the only means to navigate through the active set.

Syntax : FETCH cursor-name INTO record-list

Record-list is the list of variables that will receive the columns (fields ) from the active set.

Ex: LOOP

-----------

------------

FETCH c_name INTO name;

-----------

END LOOP;

Closing a cursor :

Closing statement closes/deactivates/disables the previously opened cursor and makes the active set undefined. Once it is closed, you cannot perform any operations on it. Once a cursor is closed, the user can reopen the cursor by using Open statement.

Syntax : CLOSE cursor_name

Ex: CLOSE c_name;

EXAMPLE-1 :

The HRD manager has decided to raise the salary for all the employees in the physics department by 0.05. whenever any such raise is given to the employees, a record for the same is maintained in the emp_raise table ( the data table definitions are given below ). Write a PL/SQL block to update the salary of each employee and insert the record in the emp_raise table.

Tabe: employee

emp_code varchar (10)

emp_name varchar (10)

dept varchar (15)

job varchar (15)

salary number (6,2)

Table: emp_raise

emp_code Varchar(10)

raise_date Date

raise_amt Number(6,2)

Solution:

DECLARE

CURSOR c_emp IS

SELECT emp_code, salary FROM employee

WHERE dept = 'physics';

str_emp_code employee.emp_code %TYPE;

num_salary employee.salary %TYPE;

BEGIN

OPEN c_emp;

LOOP

FETCH c_emp INTO str_emp_code, num_salary;

UPDATE employee SET salary : = num_salary + (num_salary * 0.05)

WHERE emp_code = str_emp_code;

INSERT INTO emp_raise

VALUES ( str_emp_code, sysdate, num_salary * 0.05 );

END LOOP;

Commit;

CLOSE c_emp;

END;

2) EXPLICIT CURSOR ATTRIBUTES:

ORACLE provides certain attributes/cursor variables to control the execution of the cursor. Whenever any cursor (explicit or implicit ) is opened and used, ORACLE creates a set of four system variables via which ORACLE keep track of the "current status" of the cursor. Programmers can access these variables. They are

v %NOT FOUND: Evaluates to TRUE if the last fetch is failed i.e. no more rows are left.

Syntax: cursor_name %NOT FOUND

v %FOUND: Evaluates to TRUE, when last fetch succeeded

Syntax: cursor_name %FOUND

v %ISOPEN: Evaluates to TRUE, if the cursor is opened, otherwise evaluates to

FALSE. Syntax: cursor_name %ISOPEN

v %ROWCOUNT: Returns the number of rows fetched.

Syntax: cursor_name %ROWCOUNT.

EXAMPLE :

DECLARE

v_emp_name varchar2(32);

v_salary_rate number(6,2);

v_payroll_total number(9,2);

v_pay_type char;

CURSOR c_emp IS

SELECT emp_name, pay_rate, pay_type FROM employee

WHERE emp_dept = 'physics'

BEGIN

IF c_name %ISOPEN THEN

RAISE

not_opened

ELSE

OPEN c_emp;

LOOP

FETCH c_emp INTO v_emp_name, v_salary_rate, v_pay_type;

EXIT WHEN c_emp % NOTFOUND;

IF v_pay_type = 'S' THEN

v_payroll_total : = (v_salary_rate * 1.25 );

ELSE

v_payroll_total : = (v_salary_rate * 40);

END IF;

INSERT INTO weekly_salary VALUES ( 'v_payroll_total' );

END LOOP;

CLOSE c_emp;

EXCEPTION

WHEN not_opened

Message ( 'cursor is not opened' );

END;

B.Tech KTU

Click here to Login

Data Base Management Systems(Module 3)

Download

X

Ex: tax := price * tax_rate

ELSE

Download

0 comments:

Post a Comment

Subjects

Popular Posts

Total Pageviews

Contact Form

Most Trending

B.Tech KTU

Click here to Login

Data Base Management Systems(Module 3)

Download

X

Ex: tax := price * tax_rate

ELSE

Download

0 comments:

Post a Comment

Subjects

Popular Posts

Total Pageviews

Subscribe To

Contact Form

Most Trending