Introduction to Oracle

Oracle is most widely database used in the world. It runs virtually every kind of computer. It functions virtually identically on all the machines, so when we learn it on one, we can use it on any other. This fact makes knowledgeable oracle user and developers very much in demand, and makes oracle knowledge and skills very portable.

Oracle can easily be used for simple operations such as entering multiple data and running standard reports. But an approach would ignore its great power; it would be like be buying a high performance racing car, and then pulling it around with a horse.

Oracle is an Object-relational database. A relational database is an extremely simple way of thinking and managing the data used in business. It is nothing more than a collection of tables of data. We all encounter table every day: weather reports, stocks charts, sports scores. These all tables, with column headings and rows of information simply presented. Even so the relational approach can be sophisticated and powerful enough for even the most complex of business. An object relational database supports all of the features of a relational database while also supporting object-oriented concepts and features.

Oracle has grown from its humble beginnings as one of a number of databases available in the 1970s to the market leader of today. In its early days, Oracle Corporation was known more as an aggressive sales and promotion organization than a technology supplier. Over the years, the Oracle database has grown in depth and quality, and its technical capabilities now are generally recognized as the most advanced. With each release, Oracle has added more power and features to its already solid base while improving the manageability.

Features of Oracle

To give some structure to the broad spectrum of the Oracle database, we’ve organized the features into the following sections:

All flavors of the Oracle database include different languages and interfaces that allow programmers to access and manipulate the data in the database. Database programming features usually interest two groups: developers building Oracle-based applications that will be sold commercially and IT organizations within companies that custom-develop applications unique to their businesses.

The Internet and corporate intranets have created a growing demand for storage and manipulation of nontraditional data types within the database. There is a need for extensions to the standard functionality of a database for storing and manipulating image, audio, video, spatial, and time series information. These capabilities are enabled through extensions to standard SQL.

Database users connect to the database by establishing a network connection. You can also link database servers via network connections. Oracle provides a number of features to establish connections between users and the database and/or between Database servers.

The popularity of Internet and intranet applications has led to a change in deployment from client/server (with fat clients running a significant piece of the application) to three-tier architecture (with a browser supplying everything needed on a thin client). The Oracle Application Server provides a means of implementing the middle tier of a three-tier solution for web-based applications, component-based applications, and enterprise application integration.

Data within an organization is often spread among multiple databases for reasons of both capacity and organizational responsibility. Users may want to query this distributed data or update it as if it existed within a single database.

Heterogeneous Services allow non-Oracle data and services to be accessed from an Oracle database through generic connectivity via ODBC and OLE-DB included with the database.

Moving data from one Oracle database to another is often a requirement when using distributed databases, or when a user wants to implement multiple copies of the same database in multiple locations to reduce network traffic or increase data availability. You can export data and data dictionaries (metadata) from one database and import them into another.

Databases tasks implemented in parallel speed up querying, tuning, and maintenance of the database. By breaking up a single task into smaller tasks and assigning each subtask to an independent process, you can dramatically improve the performance of certain types of database operation. Examples of query features implemented in parallel include:

• Table scans

• Nested loops

• Sort merge joins

• GROUP BYs

• NOT IN sub queries (anti-joins)

• User-defined functions

• Index scans

• Select distinct UNION and UNION ALL

• Hash joins

• ORDER BY and aggregation

• Bitmap star joins

• Partition-wise joins

• Stored procedures (PL/SQL, Java, external routines)

Oracle has also added some performance enhancements that specifically apply to data warehousing applications.

As part of every Database Server, Oracle provides the Oracle Enterprise Manager (EM), a database management tool framework with a graphical interface used to manage database users, instances, and features (such as replication) that can provide additional information about the Oracle environment. EM can also manage Oracle’s Application Server, Collaboration Suite, and E-Business Suite.

As every database administrator knows, backing up a database is a rather mundane but necessary task. An improper backup makes recovery difficult, if not impossible. Unfortunately, people often realize the extreme importance of this everyday task only when it is too late—usually after losing business-critical data due to a failure of a related system.

Database availability depends upon the reliability and management of the database, the underlying operating system, and the specific hardware components of the system. Oracle has improved availability by reducing backup and recovery times. It has done this through:

• Providing online and parallel backup and recovery.

• Improving the management of online data through range partitioning.

Oracle SQL

What Can SQL do?

SQL Statement

SELECT

Data retrieval

INSERT

UPDATE

DELETE

MERGE

Data manipulating language(DML)

CREATE

ALTER

DROP

RENAME

TRUNCATE

Data definition language(DDL)

COMMIT

ROLLBACK

SAVEPOINT

Transaction control

GRANT

REVOKE

Data control language(DCL)

Statement

Description

SELECT

Retrieves data from database

INSERT

UPDATE

DELETE

MERGE

Enter new rows, changes existing rows and removes unwanted rows from tables in the database, respectively.

CREATE

ALTER

DROP

RENAME

TRUNCATE

Set up changes and removes data structures from the tables.

COMMIT

ROLLBACK

SAVEPOINT

Manages the changes made by DML statements. Changes to the data can be grouped together into logical transactions.

GRANT

REVOKE

Gives or remove access rights to both the Oracle database and the structure with in it.

SELECT Statement

SELECT [ALL|DISTINCT] columns/expr [AS new name]

FROM table

[WHERE conditions]

[GROUP BY fields]

[HAVING conditions]

[ORDER BY column/integer [ASC|DESC]]

SELECT

Specifies the columns or fields to be included in the result set.

DISTINCT

FROM

Identifies the tables or views from which the data will be retrieved.

WHERE

The predicate(s) that determine which rows will be retrieved.

GROUP BY

Use to group output by the field with duplicate values and apply group functions to the grouped data.

HAVING

Use to place a condition on results of group function calculations.

ORDER BY

Determines the sequence of the rows (Default order is ascending).

SELECT ename, age, add as “Address”, sal “Salary”

 FROM emp

WHERE sal>10000

ORDER BY sal DESC;

Operators in SQL

Oracle SQL Functions

ABS (n), ROUND (n [, m]), TRUNC (n [, m])

CONCAT (char1, char2), INITCAP (char), LOWER (char), LTRIM (char [, set]), LPAD (char, n [, char2])        

AVG ([DISTINCT | ALL] n), COUNT (* | [DISTINCT | ALL] expr), MAX ([DISTINCT | ALL] expr), MIN ([DISTINCT | ALL] expr), STDDEV ([DISTINCT | ALL] n), SUM ([DISTINCT | ALL] n), VARIANCE ([DISTINCT | ALL] n)

TRUNC (d [,fmt]), SYSDATE, ROUND (d[,fmt]), NEXT_DAY (d,char), NEW_TIME (d, a, b), MONTHS_BETWEEN (d, e)

The SYSDATE function returns the current date and time from Oracle. If you want to only manipulate the CURRENT DATE, and you are not selecting other columns from a table, you may issue a SELECT statement using the Oracle one row table:

SELECT SYSDATE FROM DUAL;

Database Transaction

Database Objects

Table

Creating a Table

CREATE TABLE [Schema].table_name

(

column_name datatype [DEFAULT expr][column_constraint]

……….

[table_constraint]

);

CREATE TABLE emp

(rollno number(5) CONSTRAINT emp_rollno_pk PRIMARY KEY,

name varchar2(20) CONSTRAINT emp_name_nn NOT NULL,

age number(2),

CONSTRAINT emp_age_chk CHECK(age>20));

Constraint

Joining Tables

Types of joins

View

Advantages of Views

Creating a View

CREATE [OR REPLACE][FORCE/NOFORCE]

VIEW view_name

[(alias (, alias)….)] AS sub query

[WITH CHECK OPTION [CONSTRAINT constraint_name]]

[WITH READ ONLY [CONSTRAINT constraint_name]];

OR REPLACE

Recreates the view if it exist.

FORCE

Creates the view regardless of whether or not the base table exists.

NOFORCE

Creates the view only if the base table exists.

Alias

Specify name for expression selected by the view query.

WITH CHECK OPTION

Specifies that only rows accessible to the view can be inserted or updated.

WITH READ ONLY

Ensures that no DML operation can be performed on this view.

CREATE  VIEW  v1

SELECT ename, job, deptno

FROM emp

Where deptno=20;

Removing a View

DROP VIEW view_name;

DROP VIEW v1;

Sequence

Creating a Sequence

CREATE SEQUENCE sequence_name

[INCREMENT BY n]

[START WITH n]

[{MAXVALUE n/NOMAXVALUE}]  

[{MINVALUE n/NOMINVALUE}]

[{CYCLE/NOCYCLE}]

[{CACHE n/NOCACHE}]

INCREMENT BY n

Interval by which the sequence is incremented or decremented (default value is 1).

START WITH n

First no. to be generated (default is 1).

MAXVALUE

Max. value to be generated.

NOMAXVALUE

Specifies a max. value of 10^27 for an increasing sequence and -1 for a decreasing sequence.

MINVALUE

Min. value to be generated.

NOMINVALUE

Specifies a min. value of 1 for an increasing sequence and – (10^26) for a decreasing sequence.

CYCLE/NOCYCLE

Specifies whether the sequence continues to generate values after reaching its max. or min. values(default is NOCYCLE).

CACHE/NOCACHE

Specifies how many values the Oracle server preallocates and keep in memory(default is 20).

CREATE SEQUENCE seq

INCREMENT BY 10

START WITH 100

MAXVALUE 1000  

CACHE 50;

Removing a Sequence

DROP SEQUENCE sequence_name;

DROP SEQUENCE seq;

Index

Creating an Index

CREATE INDEX index_name

ON table_name(column,column…..);

CREATE INDEX CustNameIdx

ON CUSTOMER(Name);

Removing an Index

DROP INDEX index_name;

DROP INDEX CustNameIdx;

Synonym

Creating an Synonym

CREATE [PUBLIC] SYNONYM synonym_name                     FOR  object;

CREATE PUBLIC SYNONYM loc

FOR smith. location;

Removing an Synonym

DROP SYNONYM  synonym_name;

DROP SYNONYM loc;

Error handling

Grant & Revoke statement

Enterprise Manager Console

Application Logic

SQL*Plus

SQL*Plus Prompt

Oracle PL/SQL

           In block- structured and procedural units of code, making PL/SQL a

           powerful transaction processing language.                                              

PL/SQL Environment

Benefits of PL/SQL

PL/SQL Block Structure

DBMS_OUTPUT.PUT_LINE

SET SERVEROUTPUT ON

DEFINE p_annual_sal = 60000

DECLARE

v_sal NUMBER(9,2) := &p_annual_sal;

BEGIN

v_sal := v_sal/12;

DBMS_OUTPUT.PUT_LINE (’The monthly salary is ’ ||  TO_CHAR(v_sal));

END;

Handling Variables in PL/SQL

Types of Variables

Declaring PL/SQL Variables

identifier [CONSTANT] datatype [NOT NULL]

[:= | DEFAULT expr];

DECLARE

v_hiredate   DATE;

v_deptno     NUMBER(2)  NOT NULL  :=  10;

v_location  VARCHAR2(13)  :=  ’Atlanta’;

c_comm      CONSTANT  NUMBER  :=  1400;                         v_mgr          NUMBER(6) DEFAULT 100;

SELECT Statements in PL/SQL

SELECT select_list

INTO {variable_name[, variable_name]...

| record_name}

FROM table

[WHERE condition];

Operators in PL/SQL

Operator

Operation

**

Exponentiation

+, -

Identity, negation

*, /

Multiplication, division

+, -, ||

Addition, subtraction, concatenation

=, <, >, <=, >=, <>, !=,

~=, ^=, IS NULL, LIKE,

BETWEEN, IN

Comparison

NOT

Logical negation

AND

Conjunction

OR

Inclusion

Controlling PL/SQL Flow of Execution

IF condition THEN

Statements;

[ELSIF condition THEN

Statements;]

ELSE

Statements;]

END IF;

CASE selector

WHEN expression1 THEN result1

WHEN expression2 THEN result2

...

WHEN expressionN THEN resultN

[ELSE resultN+1;]

END;

LOOP

statement1;

. . .

EXIT [WHEN condition];

END LOOP;

or  NULL);

WHILE condition LOOP

statement1;

statement2;

. . .

END LOOP;

FOR counter IN [REVERSE]

lower_bound..upper_bound LOOP

statement1;

statement2;

. . .

END LOOP;

Composite Data Types

TYPE type_name IS RECORD                       (field_declaration[, field_declaration]…);                  identifier type_name;

Where field_declaration is:

field_name {field_type | variable%TYPE

| table.column%TYPE | table%ROWTYPE}

[[NOT NULL] {:= | DEFAULT} expr]

TYPE emp_record_type IS RECORD

(last_name VARCHAR2(25),

job_id VARCHAR2(10),

salary NUMBER(8,2));

emp_record emp_record_type;

The %TYPE Attribute

The ROW%TYPE Attribute

TYPE type_name IS TABLE OF

{column_type|variable%TYPE

|table.column%TYPE}[NOT NULL]

| table.%ROWTYPE

[INDEX BY BINARY_INTEGER];

Identifier type_name;

TYPE ename_table_type IS TABLE OF

Employees.last_name%TYPE

INDEX BY BINARY_INTEGER;

ename_table  ename_table_type;

Trapping Exceptions

DECLARE num_row number_table%ROWTYPE;                                        BEGIN                                                                                                                              select *  into num_row  from number_table; dbms_output.put_line(1/num_row.num);                                               EXCEPTION                                                                                                     WHEN NO_DATA_FOUND THEN                                            dbms_output.put_line('No data!');                                                                        WHEN TOO_MANY_ROWS THEN                                       dbms_output.put_line('Too many!');                                                                    WHEN OTHERS THEN                                                   dbms_output.put_line(‘Error’);                                                                              end;

Transaction Control Statements

PL/SQL Blocks

Procedure

Creating Procedures

CREATE [OR REPLACE] PROCEDURE procedure_name

 [(parameter1 [mode1] datatype1,

  parameter2 [mode2] datatype2,

  . . .)]

IS|AS

PL/SQL Block; 

Create or replace  query_emp(p_id IN emp.empno%TYPE, p_name OUT emp.ename%TYPE)                                       BEGIN

SELECT ename, sal, deptno INTO p_name, p_sal, p_deptno  from emp                                                                                 Where empno=p_id;                                                                 END;                                                                             ISQL*PLUS-Execute query_emp(1046,:name);                     PRINT NAME

Removing Procedure

Function

Creating Function

CREATE [OR REPLACE] FUNCTION function_name

 [(parameter1 [mode1] datatype1,

  parameter2 [mode2] datatype2,

  . . .)]

RETURN datatype

IS|AS

PL/SQL Block; 

CREATE [OR REPLACE] FUNCTION squareFunc ( num IN number )

RETURN number

IS

BEGIN

return num*num;

END;

BEGIN                                                                dbms_output.put_line(squareFunc(3.5));                                                                  END;

Removing Function

Database Trigger

Types of Triggers

Creating DML Triggers

CREATE [OR REPLACE] TRIGGER trigger_name

timing

event1 [OR event2 OR event3]

ON table/view name

[REFERENCING OLD AS old | NEW AS new]

[FOR EACH ROW]

[WHEN (condition)]

trigger_body

Trigger_name

Is the name of the trigger

timing

Indicates the time when the trigger fires in relationto the triggering event: BEFORE,AFTER,INSTEAD OF

Event

Identifies data manipulation operation that causes the

trigger to fire: INSERT, UPDATE, DELETE

Table name

Indicates the table associated with the trigger

REFERENCING

Specifies correlation names for the old and new values of the current row (The defaults are OLD and NEW)

FOR EACH

ROW

Designates that the trigger is a row trigger

WHEN

Specifies the trigger restriction

trigger body

Is the trigger body that defines the action performed by the trigger, beginning with either DECLARE or BEGIN, ending with END, or a call to a procedure

CREATE OR REPLACE TRIGGER secure_emp

BEFORE INSERT ON employees

BEGIN

IF (TO_CHAR (SYSDATE,’DY’) IN (’SAT’,’SUN’)) OR

(TO_CHAR (SYSDATE,’HH24: MI’)

NOT BETWEEN ’08:00’ AND ’18:00’)

THEN RAISE_APPLICATION_ERROR (-20500,’you may Insert into EMPLOYEES table only During business hours.’);

END IF;

END;

Disabling and Dropping Triggers

table is dropped.

                        

Cursor

CURSOR cursor_name is SELECT statement;

OPEN cursor_name;

FETCH cursor_name INTO variable;

CLOSE cursor_name;

v_empid        number;

Cursor cur_sample is

Select empid from employee

where grade > 4;

Begin

        open cur_sample;

        loop

        fetch cur_sample into v_empid;

        exit when cur_sample%notfound;

               update employee

                set salary = salary + 500

                where empid = v_empid;

        end loop;

End;

SQL Cursor Attributes

Cursor FOR Loops

FOR record_name IN cursor_name LOOP

statement 1;

statement 2;

................

END LOOP;

DECLARE

  CURSOR emp_cursor IS

    SELECT ename, deptno

    FROM   emp;

BEGIN

  FOR emp_record IN emp_cursor LOOP

         -- implicit open and implicit fetch occur

    IF emp_record.deptno = 30 THEN

      ...

  END LOOP; -- implicit close occurs

END;

Package

Scope of the Construct

Description

Placement within the Package

Public

Can be referenced from any Oracle server environment

Declared within the package specification and may be defined within the package body

Private

Can be referenced only by

other constructs which are

part of the same package

Declared and defined within the package body

Creating the Package Specification

CREATE [OR REPLACE] PACKAGE package_name

IS|AS

public type and item declarations

subprogram specifications

END package_name;

CREATE OR REPLACE PACKAGE comm_package IS

g_comm NUMBER := 0.10; --initialized to 0.10

PROCEDURE reset_comm

(p_comm IN NUMBER);

END comm_package;

Creating the Package Body

CREATE [OR REPLACE] PACKAGE BODY package_name

IS|AS

private type and item declarations

subprogram bodies

END package_name;

CREATE OR REPLACE PACKAGE BODY comm_package

IS

FUNCTION validate_comm (p_comm IN NUMBER)

RETURN BOOLEAN

IS

v_max_comm NUMBER;

BEGIN

SELECT MAX(commission_pct)

INTO v_max_comm

FROM employees;

IF p_comm > v_max_comm THEN RETURN(FALSE);

ELSE RETURN(TRUE);

END IF;

END validate_comm;

Removing Packages

I took assistance and consulted from the underneath named sources of information:

ww.oracle.com

     www.wikipedia.com