An index is a object which is used to improve performance during retrieval of records.

CREATE [UNIQUE] INDEX index_name 

ON employee[emp_id, emp_name,dept_id]

[COMPUTE STATISTICS]

The UNIQUE keyword is used when combined values of the index should be unique.

The COMPUTE STATISTICS during the creation of index optimizes the plan of execution of the SQL statement and improves performance.

The CACHE option means how many sequences will be stored in memory for access by the application objects. The performance is faster. However in case of the database is down the data is memory is lost for the sequence.

The NO CACHE option means values are not stored in memory. So there might be some performance issue. 

A database trigger is procedural code that is automatically executed in response to certain events on a particular table or view in a database. The trigger is mostly used for maintaining the integrity of the information on the database.

Syntax:

CREATE OR REPLACE TRIGGER [Trigger Name] [Before / After / Instead Of]

ON [schema].[table]

<PL/SQL subprogram>

Types of PL/SQL triggers : 

> Row trigger          - The trigger fires for each ROW affected.

> Statement trigger - The trigger is fired once when the condition is matched

> Before and After trigger - The BEFORE trigger run the trigger action before the insert, update or delete statement. The AFTER trigger runs the trigger action after the insert, update or delete statement is executes.

A package is a collection of related PL/SQL objects. The package contains a body and a specification. The package specification has the declaration which is public and can be used in the PL/SQL sub programs inside the package.

The package body holds the implementation of all the PL/SQL objects declared in the specification.

Example of a PL/SQL Package.

CREATE OR REPLACE PACKAGE emp_data AS 

PROCEDURE add_employee (

      ename VARCHAR2,

      job VARCHAR2,

      mgr NUMBER,

      sal NUMBER,

      deptno NUMBER);

END emp_actions;

CREATE OR REPLACE PACKAGE BODY emp_data AS 

PROCEDURE add_employee (

       ename VARCHAR2,

       job VARCHAR2,

       mgr NUMBER,

       sal NUMBER,

       deptno NUMBER) IS

BEGIN

         INSERT INTO emp VALUES (empno_seq.NEXTVAL, ename, job, mgr, SYSDATE, comm, deptno);

END add_employee;

END emp_data;

Advantages of  PL/SQL packages :

Packages are easier for application designing, encapsulating data, additional functionality and better performance. An application has various modules which can be placed in packages and handled easier.

Named PL/SQL blocks of code which can be invoked using parameters are called PL/SQL sub programs.

Advantages of PL/SQL subprograms are

- The application makes a single call to the database to run a block of statements which improves performance against running SQL multiple times. This will reduce the number of calls between the database and the application.

- PL/SQL is secure since the code resides inside the database thus hiding internal database details from the application. The application will only make a call to the PL/SQL sub program

- PL/SQL and SQL go hand in hand so there would be no need of any translation required between PL/SQL and SQL.

- %FOUND

 This is a Boolean variable which evaluates to TRUE if the last row is successfully fetched.

- %NOTFOUND

 This is a Boolean variable which evaluates to TRUE if the last row is not successfully fetched. This means there are no more rows to fetch.

- %ROWCOUNT

 Returns the number of rows fetched by the cursor.

- %ISOPEN

If the cursor is open, it evaluates to TRUE else FALSE.

Cursors help you manipulate the information retrieved by select statements. This can be done by assigning a name to the cursor.

Example:

CURSOR emp_cur 

IS 

SELECT emp_number from employee_tbl where employee_name = name_in;

Types of Cursors:

Implicit cursors- These cursors are not declared by the programmer. They are issued when the SQL statement is executed. The open, close and fetching is done by itself.

Example:

UPDATE employee SET salary = salary * 2.1;

Here, an implicit cursor is issued to identify the set of rows in the table which would be affected by the update.

Explicit cursors- These cursors are defined by programmer. They are used in queries that return multiple rows.

Example:

CURSOR emp_cur 

IS 

SELECT emp_number from employee_tbl where employee_name = name_in;

A record data type represents a data type for that row in a database table. It lets u define your own records and not your own fields.