Other ORACLE database objects

VIEW:

·       You may create tables that contain data that you don’t want to be made available to certain people or groups.

·       Rather than create new tables with redundant data, you can

create a “view” of the table which only presents the data fields that you want to make available.

·       A view is set up with certain privileges for users. A view can also be configured to control how data is manipulated in the table.

·       For example, you may want to set it up so that users can only see the data and not be allowed to update it.

TYPES OF VIEW

There are two types of view:

1.     Read Only view

2.     Updatable view

If a view is used to only look at table data and nothing else the view is called a Read-Only View.

A view that is used to look at table data as well as Insert, Update and Delete table data is called an Updatable View

FUNCTION PERFORM ON VIEW

·       CREATION

·       MANIPULATION

·       DELETION

Examples:-

1)     To create the view, you specify the name and then use a select statement:

SQL> create view employeeVU

2    as select empno, ename, job

3    from employees

4    where deptno=20;

Output:-

View created.

2)     Now check the view:

SQL> select * from employeeVU;

EMPNO ENAME JOB

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

7369 SMITH CLERK

7566 JONES MANAGER

7788 SCOTT ANALYST

7876 ADAMS CLERK

7902 FORD ANALYST

3)     To drop a view:

SQL> drop view employeeVU;

View dropped.

******RENAMING COLUMNS******

SQL> create view      

    emp_sal(empno,ename,manager,tot_sal)

         as select empno,ename,mgr,comm+sal     

    from emp where job like'SALESMAN';

  View created.

Output :-

SQL> select *from emp_sal;

    EMPNO ENAME        MANAGER   TOT_SAL                           

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

     7499     ALLEN           7698                1900                                                           

     7521    WARD            7698                 1750                                                            

     7654    MARTIN          7698                2650                                                           

     7844   TURNER          7698                1500  

Creating a view from multiple tables

An especially handy feature of views is that you can create what looks and acts like a table from multiple tables and functions. You cannot update data in this type of view.

SQL> create view empvu

2 (name, minsal, maxsal, avgsal)

3 as select d.dname, min(e.sal),

4 max(e.sal), avg(e.sal)

5 from emp e, dept d

6 where e.deptno=d.deptno

7 group by d.dname;

View created.

Sequence:

Most applications require automatic generation of a numeric value. Oracle provides an automatic sequence generator of numeric values, which can have maximum value of up to 38 digits. A sequence can be defined to-

            - generate numbers in ascending or descending

            - provide intervals between numbers

            - caching at sequence numbers in memory.

Sequences are numeric column values that are computer generated. The difference between two values of the column also has to be pre-defined at the time of sequence creation.

Sequences generate unique integer which can be used as primary key. Sequence can be given in ascending order or descending order, in which starting value, maximum value, minimum value and increment value can be specified.

Syntax:

CREATE Sequence <sequence-name>

increment by n

start with n

[Maxvalue n] [ Minvalue n]

[cycle / No cycle] [ cache/nocache]

Where sequence-name: is name of sequence.

Increment BY: Specifies interval between sequence numbers. It can be positive or negative but not zero.

MaxValue N:- Maximum value is given by the user in maxvalue, for how long to generate sequence by default it is 1 to 10e²⁶ when ascending order and 10e²⁶ -1 in descending order.

Min value N:-  Specifies the minimum value that the sequence can generate. By default, it is -1 for Descending and 10e²⁷ -1 for Ascending;

Start with: Specifies sequence number to be generated. The default for ascending sequence is minimum value and for descending it is the maximum value.

Cycle:-Creates a sequence and after giving its max value and min value if we want to continue sequence generation, cycle is used.

No cycle:If we do not want to generate a sequence after reaching its maximum or minimum value, No cycle is used. By default it is no cycle.

CACHE: Specifies how many values of the sequence are pre allocated and kept in the memory for faster access. The minimum value for cache is two (2). The cache value must be less than maxvalue minus minvalue and by default cache value is 20.

NOCACHE: The default value ‘nocache’ does not preallocate sequence numbers for faster access.

Example:-

CREATE SEQUENCE seq

INCREMENT BY 1

START WITH 1

Maxvalue 9

Minvalue 1

Cache 2

cycle;

SQL> SELECT seq_deptno.nextval FROM dual;

OUTPUT:

NEXTVAL

----------

10

SELECT seq_deptno.currval FROM dual;

OUTPUT:

CURRVAL

Insert sequence in insert command.

Insert into demo values(seq.nextval,’dolly’);

Synonyms

The main reasons for creating synonyms are:

1.The true name of the owner or table needs to be hidden

2.The original location of the table needs to be hidden. This is in case the database is a large one with many installations at different places.

3. Users have to be provided with a simple and easily

remembered tablename.

The format of the statement of creating a synonyms is as follows:

Syntax:-

CREATE SYNONYM <synonyms name> FOR

<tablename/viewname>;

Example:-

Create synonym emp10 for emp;

you can see all the database objects (including synonyms) that you have created, give the following command;

SELECT * FROM tab;

Similarly, synonyms can also be deleted, if no longer required by using the DROP command. The format of the statement is as follows;

DROP SYNONYM <synonyms name>;

DROP SYNONYM emp10;

Database Links

• Database links are used to connect to a remote server from the local database.
• Database links require SQL * Net to be running on each of the machines involved in the Remote Data access.
• Database links allow users to create a group of distributed database as if they were a single, integrated database.
• You will also find information about direct connection to remote database, such as those used in client-server application.

Sample Architecture for a Database links