Ending Transactions

8.5.1 Implicit Rollbacks

Before running an INSERT, UPDATE, or DELETE statement, the database marks an implicit savepoint (unavailable to you). If the statement fails, the database rolls back to the savepoint. Usually, just the failed SQL statement is rolled back, not the whole transaction. If the statement raises an unhandled exception, the host environment determines what is rolled back.

The database can also roll back single SQL statements to break deadlocks. The database signals an error to a participating transaction and rolls back the current statement in that transaction.

Before running a SQL statement, the database must parse it, that is, examine it to ensure it follows syntax rules and refers to valid schema objects. Errors detected while running a SQL statement cause a roll back, but errors detected while parsing the statement do not.

8.5.2 Ending Transactions

You should explicitly commit or roll back every transaction. If you do not commit or roll back a transaction explicitly, then Oracle determines its final state. For example,

 A user issues a COMMIT or ROLLBACK statement without a SAVEPOINT clause.

In a commit, a user explicitly or implicitly requested that the changes in the transaction be made permanent. Changes made by the transaction are permanent and visible to other users only after a transaction commits.

 A user runs a DDL command such as CREATE, DROP, RENAME, or ALTER.

The database issues an implicit COMMIT statement before and after every DDL statement. If the current transaction contains DML statements, then Oracle Database first commits the transaction and then runs and commits the DDL statement as a new, single-statement transaction.

 A user exits normally from most Oracle Database utilities and tools, causing the current transaction to be implicitly committed.

 A client process terminates abnormally or executes a ROLLBACK statement, causing the transaction to be implicitly or explicitly rolled back.

8.6 SUMMARY

 The SELECT statement in SQL is used to extract data from the one or more tables or views in the database.

 The SELECT INTO statement retrieves values from one or more database tables (as the SQL SELECT statement does) and stores them in variables (which the SQL SELECT statement does not do).

 PL/SQL blocks can be used to manipulate data in the server using INSERT, DELETE and UPDATE statements.

 Oracle performs a set of tasks for executing any SQL statement.

◦ Reserves an area in memory called private SQL area.

◦ Populates this area with appropriate data.

◦ Frees the memory area when execution completes.

 Cursor is a pointer to the private SQL area that stores information about processing a specific SELECT or DML statement.

 For SQL statements, there are two types of cursors:

◦ Implicit cursor

◦ Explicit cursor

 An implicit cursor is a session cursor that is constructed and managed by PL/SQL.

 You cannot control an implicit cursor, but you can get information from its attributes.

 Cursor attributes return information about the state of the cursor.

 A transaction is a logical, atomic unit of work that contains one or more SQL statements.

 Transaction processing is a feature that lets multiple users work on

the database concurrently, and ensures that each user sees a consistent version of data and that all changes are applied in the right order.

 Transaction control is the management of changes made by DML statements and the grouping of DML statements into transactions.

 Transaction control involves using the following statements:

◦ The COMMIT statement ends the current transaction and makes all changes performed in the transaction permanent.

◦ The ROLLBACK statement reverses the work done in the current transaction.

◦ The SAVEPOINT statement identifies a point in a transaction to which you can later roll back.

 Before running an INSERT, UPDATE, or DELETE statement, the database marks an implicit savepoint (unavailable to you). If the statement fails, the database rolls back to the savepoint.

 You should explicitly commit or roll back every transaction. If you do not commit or roll back a transaction explicitly, then Oracle determines its final state.

8.7 REVIEW QUESTIONS

 Explain the concept of implicit cursor in PL/SQL.

 What is a transaction? Explain COMMIT, ROLLBACK and SAVEPOINT in transaction.

 What is a cursor? Discuss the different attributes of a cursor?

8.8 LAB ASSIGNMENT

Consider the following table schema and write a PL/SQL block performing the following:

EMPID ENAME DEPT DESG SALARY

VARCHAR2 VARCHAR2 VARCHAR2 VARCHAR2 NUMBER

 Print the EMPID, ENAME, DEPT, DESG and SALARY of the employee whose name is “Prakash”. If the record is not found then print “There is no employee with name Prakash”.

 Print the details of the employee getting lowest salary.

 Print the details of the employee getting highest salary.

 Print the average salary of the employees belonging to “Finance”

department.

 Delete all the records of employees from the department “HR” getting salary less than 10,000/- and also print the total number of records deleted.

 Modify the salary of the employees in the “Admin” department to give them a hike of 25%.

8.9 BIBLIOGRAPHY, REFERENCES AND FURTHER READING

 Database Management Systems, Third Edition by RamaKrishnan, Gehre. McGraw Hill

 Database System Concepts, Fifth Edition by Silberschatz, Korth, Sudarshan. McGraw Hill

 Murach's Oracle SQL and PL/SQL by Joel Murach. Shroff Publishers & Distributors

 Oracle Database 11g by Satish Asnani. PHI Learning Private Limited

 Oracle 11g: PL/SQL Reference Oracle Press.

 Expert Oracle PL/SQL, By: Ron Hardman,Michael McLaughlin, Tata McGraw-Hill

 SQL, PL/SQL The programming language of Oracle, Bayross Ivan, BPB Publications

 Fundamentals of Database Systems, Elmasri Ramez and Navathe B. Shamkant, Pearson

8.10 ONLINE REFERENCES

O'Reilly "Mastering Oracle SQL"

http://oreilly.com/catalog/mastorasql/

Oracle Database PL/SQL language Reference 11g Release 2 (11.2), part number E25519-05

http://docs.oracle.com/cd/E11882_01/appdev.920/a96590/adg09dyn.htm

Chapter 5 Introducing PL/SQL

http://www.cs.kent.edu/~wfan/link/dbapre/dbatest/54905f.htm Oracle SQL & PL/SQL

http://sql-plsql.blogspot.in/2007/05/oracle-plsql-cursors-with-parameters.html

http://sql-plsql.blogspot.in/2007/03/plsql-introduction.html PL/SQL tutorial

http://plsql-tutorial.com/index.htm

http://www.academictutorials.com/pl-sql/introduction.asp Wikipedia links

http://en.wikipedia.org/wiki/PL/SQL



UNIT - IV

9