SQL is a non procedural language and so cannot do processing of either the data used to formulate the query or process the query. For example a SQL query cannot be placed in a loop or a dynamic SQL statement cannot be constructed by just using SQL alone. One requires a procedural language extension to perform some select data processing tasks in addition to DML capabilities offered bySQL statements. PL/SQL is the procedural language extension of SQL
Exception in SQL queries can occur when a Not Found condition or when a duplicate record found occurs. A Not Found condition occurs when a select query does not find any records as per the specified criterion. For example a select statement with a where clause need not always return records. This amounts to a NOTFOUND condition and is handled in a PL/SQL block. A PL/SQL block has a declare, begin, exception and end. So when a NOTFOUND condition occurs it has to be handled as an Exception in the PL/SQL block.Similarly when a SQL statement returns many rows it causes an exception referred to as DUPLICATE ROWS. Either way, these two exceptions have to be handled in the Exception handler and appropriate processing has to be done such as a raising a form trigger failure and posting an alert message.
In some situations an undefined exception can also occur with any errors other than NOTFOUND or DUPLICATE ROWS. In such cases exceptions can be raised with reference to a specific error number and remedial action.
SQL statements that return multiple rows can also be processed using a CURSOR. The cursor has to be declared, opened and executed record by record. Records can be processed individually based on a specific condition that is expected to occur. A cursor when opened has to be closed.
A cursor has to be fetched into local variables. The advantage of cursors is that one can formulate search expressions which would have been difficult using SQL statements alone.
A typical problem requiring the use of cursors is when one cannot state explicitly using an SQL statement which record in a table has to be updated. In this case the criterion for identifying the record that has to be updated can be stated clearly using procedural language constructs. For example in a table with columns Emp Id, DOB, DOJ if one requires to construct a query to identify employees who are less than 23 years of age when they joined in service.
Using cursors one can easily select the entire record to do the above without having to specify any cumbersome criterion in the where clause and just state the processing in the cursor body using PL/SQL constructs.
By Srinivasa Gopal
Article Source: Exception Handling and Use of Cursors in PL/SQL Blocks
Article Copyright A pen that copies words that are meaningful to life, so that if words become many will be contained into a meaning of one word it is all cheers of everyone
Pages
- Home
- Privacy
- Disclaimer
- Terms Of Service
- Sitemap
- Ketentuan Layanan Penerbit Article Copyright
- Ketentuan Layanan Article Copyright
- Ketentuan layanan untuk penulis
- Room Comment Article Copyright
- Tiktok
- MineShiba
- MineWIN
- MineDoge
- MineBTTC
- MineSIA
- MineTRON
- MineEXEC
- CryptoSense
- FoucetPay
- Coinpayz
- Fishfly
Exception Handling and Use of Cursors in PL/SQL Blocks
Label:
Article Copyright,
Canada,
Daily Devotional,
Israel,
Japan,
Malaysia,
Rusia,
Saudi Arabia,
United States
Subscribe to:
Post Comments (Atom)
Itu semua terjadi karena Carson menabrak pohon
Itu semua terjadi karena Carson menabrak pohon (Ini adalah kisah kolaboratif dengan teman saya NabilaTheGreat InTheCorner, ini adalah kisah...
-
I read a great quote from Picasso (yes, the famous painter) the other day, and it really resonated with my sales philosophy. Here it is: &qu...
-
Greater Noida West, better known as Noida Extension, is emerging as a realty hotspot for residential properties in National Capital Region ...
-
Studi mengidentifikasi hubungan potensial antara bakteri oral dan abses otak Bakteri yang diketahui menyebabkan infeksi mulut juga dapat me...
-
Memulai dengan Divi - Panduan Langkah demi Langkah untuk Menginstal dan Mengaktifkan Tema Divi adalah salah satu multiguna paling populer W...
-
By Ronak Patel Angular was first termed in the year of 2009 through the group of developers in the way of introducing a brand new conc...
-
Dubai Mainland is famous for its business friendly infrastructure and facilities. Dubai mainland company formation is still the first prefer...
-
Medical experts recommend that everybody should get a flu shot before the peak flu season begins, which is from December to Ma...
-
Packaging plays a significant role when it comes to marketing a product. It's what the client sees first, and it will establish their op...
-
Dekorasi Musim Gugur yang Dibuat oleh Saya Saya berjalan ke depan kelas dan mengambil gunting. Berjalan kembali ke meja saya, saya melihat ...
-
Kesengsaraan Seorang Anak Laki-Laki "Tinggalkan. Sekarang." Bahkan ketika Yesaya mengucapkan dua kata ini, dia hampir tidak bisa ...
No comments:
Post a Comment
Informations From: Article copyright