Why Indexes?

Basic use of index is to find data in a table more quickly by speeding up the query searches. Consider an employees table with 1 million records. If I query the table to extract data for 1 employee based on emp_id, all the records are compared for the emp_id. This approach will be very time taking in real time when many queries are searching for records. Thus we should use indexes to speed up the searching process.

CASE statement is used to select one out of many set of statements to execute.

Types of CASE statements:

How about this.. you can create a composite data structure in pl/sql which can hold an entire row's data. Records are made up of one or more fields of same or different datatypes, same as a row in a table.

Example:

I had some difficulty working with these 3 commands back when I was learning SQL particularly when deciding which one to use. SO, I've dedicated this blog to differentiating the three of them.

DELETE: used to remove rows selectively from a table. Delete is a DML command, so it will cause any delete triggers on the table to fire. You may need to explicitly commit after deleting from a table to make changes permanent.

So folks, I’m not going to explain the trigger syntax here as its same for all types of triggers. I’ll start off with INSTEAD OF trigger straight away.

INSTEAD OF triggers hold a special functionality as they can be used to update views which are normally not updateable and perform insert, update, delete, merge operations on them.

Introduction

Module overloading is a concept used across many programming languages mainly because it’s a very powerful feature which improves the usability of your software.

Another cool and powerful feature that gives us the independence of carrying out a specific task as a separate transaction while inside the main transaction. The main transaction merely suspends for the duration the autonomous transaction runs.

It has great applications and uses while writing pl/sql code.

What is the difference between dynamic sql and static sql?

Dynamic sql refers to statements that are constructed and executed at runtime. Whereas static sql statements are constructed when the pl/sql block containing the statement is compiled.

Functions are a great and fun way to implement any functionality in our code. Not only are they easy to code and include almost anywhere we need them, their reusability and the large acceptance of return datatypes make them a coder's superpower.

One of the benefits of user defined functions is that they can be included in sql queries just like any other oracle predefined functions.