Creating SEQUENCES and VIEWS in

ORACLE
A sequence is used to generate numbers in sequence. You can use sequences to insert unique values
in Primary Key and Unique Key columns of tables. To create a sequence give the CREATE
SEQUENCE statement.

CREATING SEQUENCES
create sequence bills
     start with 1
     increment by 1
     minvalue 1
     maxvalue 100
     cycle
     cache 10';

 The above statement creates a sequence bills it will start with 1 and increment by 1. It’s maxvalue is
100 i.e. after 100 numbers are generated it will stop if you say NOCYCLE, otherwise if you mention
CYCLE then again it will start with no. 1. You can also specify NOMAXVALUE in that case the
sequence will generate infinite numbers.

The CACHE option is used to cache sequence numbers in System Global Area (SGA). If you say
CACHE 10 then Oracle will cache next 10 numbers in SGA. If you access a sequence number then
oracle will first try to get the number from cache, if it is not found then it reads the next number from
disk. Since reading the disk is time consuming rather than reading from SGA it is always
recommended to cache sequence numbers in SGA. If you say NOCACHE then Oracle will not cache
any numbers in SGA and every time you access the sequence number it reads the number from disk.

 Accessing Sequence Numbers.

To generate Sequence Numbers you can use NEXTVAL and CURRVAL for example to get the next
sequence number of bills sequence type the following command.

Select bills.nextval from dual;

BILLS
-----
1

NEXTVAL gives the next number in sequence. Whereas,  CURRVAL returns the current number of
the sequence. This is very handy in situations where you have insert records in Master Detail tables.
For example to insert a record in SALES master table and SALES_DETAILS detail table.
insert into sales (billno,custname,amt)
   values (bills.nextval,’Sami’,2300);

insert into sales_details (billno,itemname,qty,rate) values

     (bills.currval,’Onida’,10,13400);

Sequences are usually used as DEFAULT Values for table columns to automatically insert unique
numbers.
For Example,

create table invoices (invoice_no number(10) default bills.nextval,

       invoice_date date default sysdate,
       customer varchar2(100),
       invoice_amt number(12,2));

Now whenever you insert rows into invoices table ommiting invoice_no as follows

insert into invoices (customer,invoice_amt) values    ('A to Z Traders',5000);

Oracle will insert invoice_no from  bills sequence

 
ALTERING SEQUENCES
To alter sequences use ALTER SEQUENCE statement. For example, to alter the bill sequence
MAXVALUE give the following command.

ALTER SEQUENCE BILLS

     MAXVALUE 200;

Except Starting Value, you can alter any other parameter of a sequence. To change START WITH
parameter you have to drop and recreate the sequence.

DROPPING SEQUENCES
To drop sequences use DROP SEQUENCE command. For example, to drop bills sequence give the
following statement

drop sequence bills;

Listing Information About Sequences
To see how many sequences are there in your schema and what are there settings give the following
command.

select * from user_sequences;

Views
Views are known as logical tables. They represent the data of one of more tables. A view derives its
data from the tables on which it is based. These tables are called base tables. Views can be based on
actual tables or another view also.

Whatever DML operations you performed on a view they actually affect the base table of the view.
You can treat views same as any other table. You can Query, Insert, Update and delete from views,
just as any other table.

Views are very powerful and handy since they can be treated just like any other table but do not
occupy the space of a table.

The following sections explain how to create, replace, and drop views using SQL commands.

Creating Views
Suppose we have EMP and DEPT table. To see the empno, ename, sal, deptno, department name and
location we have to give a join query like this.

select e.empno,e.ename,e.sal,e.deptno,d.dname,d.loc
        From emp e, dept d where e.deptno=d.deptno;

So everytime we want to see emp details and department names where they are working we have to
give a long join query. Instead of giving this join query again and again, we can create a view on
these table by using a CREATE VIEW command given below

create view emp_det as select e.empno,

e.ename,e.sal,e.deptno,d.dname,d.loc
        from emp e, dept d where e.deptno=d.deptno;

Now to see the employee details and department names we don’t have to give a join query, we can
just type the following simple query.

select * from emp_det;

This will show same result as you have type the long join query.  Now you can treat this EMP_DET
view same as  any other table.
For example, suppose all the employee working in Department No. 10 belongs to accounts
department and most of the time you deal with these people. So every time you  have to give a DML
or Select statement you have to give a WHERE condition like .....WHERE DEPTNO=10. To avoid
this, you can create a view as given below

CREATE VIEW accounts_staff AS

    SELECT Empno, Ename, Deptno
    FROM Emp
    WHERE Deptno = 10
    WITH CHECK OPTION CONSTRAINT ica_Accounts_cnst;

Now to see the account people you don’t have to give a query with where condition you can just type
the following query.

select * from accounts_staff;

select sum(sal) from accounst_staff;

select max(sal) from accounts_staff;

As you can see how views make  things easier.

The query that defines the ACCOUNTS_STAFF view references only rows in department 10.
Furthermore, WITH CHECK OPTION creates the view with the constraint that INSERT and
UPDATE statements issued against the view are not allowed to create or result in rows that the view
cannot select.

Considering the example above, the following INSERT statement successfully inserts a row into the
EMP table through the ACCOUNTS_STAFF view:

INSERT INTO Accounts_staff VALUES (110, 'ASHI', 10);

However, the following INSERT statement is rolled back and returns an error because it attempts to
insert a row for department number 30, which could not be selected using the ACCOUNTS_STAFF
view:

INSERT INTO Accounts_staff VALUES (111, 'SAMI', 30);

 
Dropping Views
Use the SQL command DROP VIEW to drop a view. For example:

DROP VIEW Accounts_staff;

Listing Information about VIEWS.

To see how many views are there in your schema. Give the following query.

select * from user_views;

To see which columns are updatable in join views.

Data Dictionaries which shows which columns are updatable.

View Name  Description 
USER_UPDATABLE_COLUMNS  Shows all columns in all tables and views in the user's schema
that are modifiable 
DBA_UPDATABLE_COLUMNS  Shows all columns in all tables and views in the DBA schema
that are modifiable 
ALL_UPDATABLE_VIEWS  Shows all columns in all tables and views that are modifiable 

If you are in doubt whether a view is modifiable, then you can SELECT from the view
USER_UPDATABLE_COLUMNS to see if it is. For example:

SELECT * FROM USER_UPDATABLE_COLUMNS WHERE TABLE_NAME =

'EMP_DEPT_VIEW';

This might return:

OWNER       TABLE_NAME    COLUMN_NAM      UPD

----------  ----------    ----------      ---
SCOTT       EMP_DEPT      EMPNO           NO
SCOTT       EMP_DEPT      ENAME           NO
SCOTT       EMP_DEPT      DEPTNO          NO
SCOTT       EMP_DEPT      DNAME           NO
SCOTT       EMP_DEPT      LOC             NO

5 rows selected.