Oracle SQL Online Training Program Complete SQL Course

Difference Between Case and Decode

Difference Between Case and Decode


Watch and Share with your friends

What are type of View in Oracle

What is View in PL SQL

A view is a oracle database object, it can be created from one or Multiple tables or combination of  multiple tables and views . These tables are called base tables.

View is also called as virtual table or Logical table.

View always displays the output of a query which written during create a view. We can say

Views display all the data from the base tables. 

View takes only the definition of view and query which is associate with view. That information stored in the data dictionary.

That is the main reason creating a view does not take much storage space in database.

Why we need to create a view.

Suppose you have a bank Customer table which have different branches of data like Mumbai, Delhi Indore, Hyderabad, Chennai.

In this one table you have all customers records like Customer name, account no, amount, city, branch etc .

 These all customer related data only branch manager can access and modify on customer table.

but here we store all data in one customer table so any Branch manager can see and modify  other branches of data. 

Now you have a requirement that manager can see and modify only respective branch of customers data not other then else.

Means branch of Mumbai manager can access and modify only Mumbai customer’s data not other then else.

For this kind of requirement you don’t need to create separate database tables for all branches.

You only need to create View for each Branches on customer table and give access to respective branches Managers.

It means

Using view you can provide an additional level of table security by restricting access to a set of rows and columns of a table.

I hope friends here get clear idea about views and why we need to create view.

Now how to create view.

Creating a view is very simple.

Let’s take a look on view syntax.

CREATE or REPLACE VIEW view_name AS
 SOURCE QUERY

Or

CREATE  VIEW view_name AS
SELECT column_name(s) FROM table_name
WHERE condition

What are Advantages of views?

1.  View does not take much space on database.

2. View provides additional level of table security by hide some of columns and rows in the tables.

3. View can also create to show Summarize data from various tables which can be used to generate reports.

4. Restrict the access of a table so that nobody can insert the rows into the table.

5. Simplify Complex Queries  

 

What is Parameterized Cursor in Oracle

What is Parameterized Cursor in Oracle.
In simple words a cursor with parameter is called parameterized cursor.
PL/SQL allows developer to pass parameters into cursors.
Same as like we can pass parameter into function and procedure.
PL/SQL Parameterized cursor pass the parameters into a cursor and use them in to query. Cursor becomes more reusable using Cursor parameters.

PL/SQL Parameterized cursor define only data type of parameter and not need to define it's length.

In Parameterized cursor scope of the parameters are local within program only.

Optionally, we can also give a default value for the parameter, which will take effect if no value is passed to the cursor.

Key point

1. Scope of the parameters are locally
2. You can assign default value to a cursor parameter.
3. Cursor becomes more reusable with Cursor parameters.
4.PL/SQL Parameterized cursor define only data type of parameter and not need to define it's length.

What is Autonomous transaction in Oracle

An autonomous transaction is available from Oracle 8i. It is a very cool, useful, unique and powerful feature in oracle.
An autonomous transaction is an independent transaction that is initiated by another transaction. It must contain at least one Structured Query Language (SQL) statement.
When an autonomous transaction is called, the original transaction (calling transaction) is temporarily suspended.
The autonomous transaction must commit or roll back before it returns control to the calling transaction.
Once changes have been made by an autonomous transaction, those changes are visible to other transactions in the database.
Autonomous transactions can be nested. 
For use Autonomous Transactions feature in program we have to use 
PRAGMA AUTONOMOUS_TRANSACTION Keyword in Program
When to use Autonomous Transactions?
• Logging mechanism
you need to log an error to your database log table. On the other hand, you need to roll back your core transaction because of the error. And you don't want to roll back over other log entries.
• Commits and rollbacks in your database triggers
If you define a trigger as an autonomous transaction, then you can commit and/or rollback in that code.
• Software usage meter
You want to keep track of how often a program is called during an application session. This information is not dependent on, and cannot affect, the transaction being processed in the application.

Mutation Error
Using autonomous transaction we can avoid mutation error.