Create Views ,Sequences in oracle,Simple Views,Complex Views

CREATING OTHER SCHEMA OBJECTS

There are several objects in a database in addition to tables.In this blog you will learn

About indexes,and synonyms.

You can present  logical separation of data by creating views of tables.

With views,you can represent and hide data from tables.

You can  use  sequence to produce unique numbers.

A View  contains no data of it’s own but  is like a interface through which  data from tables can be analysed or altered.

Ø  Creating indexes

Ø  Creating synonyms

Ø  Creating sequences

    CREATING VIEWS

·         Simple view

·         Complex views

Feature	Simple	Complex
No.of tables	One	One or more
Contain functions	No	Yes
Contain group of data	No	Yes
DML operation through a view	Yes	Not always

  Simple view:

SQL> create or replace view emp_view as select employee_id,last_name,salary from employees;

View created.

SQL> desc emp_view;

 Name                                      Null?    Type

 ----------------------------------------- -------- ----------------------------

 EMPLOYEE_ID                               NOT NULL NUMBER(6)

 LAST_NAME                                 NOT NULL VARCHAR2(25)

 SALARY                                             NUMBER(8,2)

  Complex view:

create or replace view emp_view as select department_id,max(salary) max_salary from employees group by department_id;

View created.

v  Retrieving data from view:

Select * from emp_view;

v  Modifying  a view:

SQL> create or replace view emp_view as

  2  select first_name||last_name name,salary from employees

  3  where department_id=50;

View created.

v  Rules for performing DML operation on a view:

You can not delete a row from a view which contains:

·         Group function

·         Group by clause

·         A distinct keyword

·         A pseudo column ROWNUM keyword

v  Creating views with options:

·         With check option

SQL> create or replace view emp_view as

  2  select first_name||last_name name,salary from employees

  3  where department_id=50 with check option;

View created.

·         With read only

SQL> create or replace view emp_view as

  2  select first_name||last_name name,salary from employees

  3  where department_id=50 with read only;

View created.

SQL> update emp_view set salary=800000 where salary=2800;

update emp_view set salary=800000 where salary=2800

                    *

ERROR at line 1:

ORA-01733: virtual column not allowed here

CREATING INDEX:

SQL> create index ind_t4_name on t4(name);

Index created.

CREATING SEQUENCE:

SQL> Create Sequence T4_Seq

  2 Start With 101

  3  Maxvalue 5000

  4  Minvalue 101

  5  Increment By 10

  6  Nocycle

  7* Nocache

Sequence created.

v  NEXTVAL:

SQL> select t4_seq.nextval from dual;

   NEXTVAL

----------

       101

v  CURRVAL:

SQL> select t4_seq.currval from dual;

   CURRVAL

----------

       101

CREATING SYNONYM:

SQL> create synonym syn_emp for hr.employees;

Synonym created.