Oracle : Different types of Objects

PL/SQL stands for Procedural Language extension of SQL. It is a combination of SQL along with the procedural features of programming languages.

Following are the some objects in oracle .We must know at least each object definition. So, provided here.

Functions :

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

Functions are the group of the SQL / PL-SQL statements which performs the task and return value/values to the calling environment.

Procedures:

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

It is a named PL/SQL block which performs one or more specific task.

Difference between procedure and functions -

Function must returns value while procedure may or may not returns value.

Function can be called in SQL statements we cannot call procedure in the SQL statements.

Packages :

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

A package is a object that groups logically related PL/SQL types, items, and subprograms.

Triggers :

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

A trigger is a pl/sql block structure which is fired automatically when Insert, Delete, Update is executed on a database table.

Cursors :

-----------

A cursor is a temporary work area created in the memory when a SQL statement is executed. A cursor can hold more than one row, but can process only one row at a time.

 Views:

-----------

A view is a virtual table consisting of a stored query, it contains no data.

Materialized views :

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

Every time we use a view oracle has to execute the sql statement defined for that view (called view resolution), it must be done each time the view is used. If the view is complex this can take sometime, this is where a materialized views comes in, unlike a view it contains space and storage just like a regular table.

 Sequences :

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

Sequences are database objects from which multiple users can generate unique integers. You can use sequences to automatically generate primary key values.

Synonyms :

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

A synonym is an alias for a schema object.

Constraints :

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

Constraints apply specific rules to data, ensuring the data is in required format. There are a number of different kinds of constraints -

• Check
• Not NULL
• Primary key
• Unique
• Foreign Key
• Default

 Indexes :

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

Indexes are optional structures associated with tables and clusters that allow SQL statements to execute more quickly against a table.