PREFACE

“Knowledge is precious but wisdom comes through experience.”   A famous method to explain but I try my best to give presentation and make short report on it.  Philosopher once quoted these words to make people understand the importance of practical knowledge and how to represent this in front of the other people and the very important way is how to explain to them so that they could understand easily.

Theory in any subject is important but that subject knowledge is important if should be understood by the other people in a very simple way by you.

 An Engineering student cannot be perfect engineer or professional without the practical, theoretical knowledge if he /she is not be able to represent that knowledge to you in a very simple way so that you can easily understood. I got the opportunity to make a presentation on the Oracle 9i.  

CONTENTS

S.NO.

CONTENTS

PAGE NO.

1.

About the Organization

6

2.

Introduction

7

3.

What is SQL

9

4.

SQL*Plus

10

5.

DATABASE OBJECTS

14

6.

PL/SQL

32

7.

PL/SQL (Simple and Compound Symbols)

        

41

8.

REFERENCE

42

About the Organization

Introduction

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 the features of a relational database while also supporting object-oriented concepts and features.

Database:-

        

                     A collection of information organized in such a way Data computer program can quickly selected desired field data. Database is an electronic file system. The software which is managing this database is called RDBMS.

ORACLE DATABASE:-

Creating an Oracle Database:-

What is SQL?

SQL SELECT STATEMENT:-

                        want to return by Query.

                      return by Query.

                  table.

What Can SQL do?

SQL*Plus:-

Oracle Data Types

DATABASE OBJECTS:-

Creating Tables:-

A database most often contains one or more tables. Each table is identified by a name (e.g. "Customers" or "Orders"). Tables contain records (rows) with data.

Below is an example of a table called "EMP":

The table above contains three records (one for each person) and five columns (P_Id, LastName, FirstName, Address, and City).

EMPNO    

ENAME      

JOB

MGR      

HIREDATE    

SAL  

DEPTNO

7369

SMITH

CLERK

7902

17-DEC-80        

8000            

20  

 7499        

ALLEN  

MANAGER

7698

20-FEB-81        

16000

30

7521  

WARD

SALESMAN  

7698  

22-FEB-81      

12500

30

SQL Statemnt

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

The result of a SELECT query is a result set that includes the rows and columns that satisfy your query.

SELECT - specifies the columns or fields to be included in the result set

FROM - identifies the tables or views from which the data will be retrieved

WHERE - the predicate(s) that determine which rows will be retrieved

ORDER BY - determines the sequence of the rows

SELECT Statement Syntax

SELECT {ALL|DISTINCT}

columns {AS newname}

FROM table

{WHERE conditions}

{GROUP BY fields}

{HAVING conditions}

ORDER BY column integer {ASC|DESC}

DESCRIBE the table

 Disc  pbowman. employee;

OUTPUT

Name                                          Null?                Type

EMPLOYEE_ID                      NOT NULL        NUMBER(4)

LAST_NAME                                                     VARCHAR2(15)

FIRST_NAME                                                    VARCHAR2(15)

MIDDLE_INITIAL                                             VARCHAR2(1)

JOB_ID                                                                NUMBER(3)

MANAGER_ID                                                   NUMBER(4)

HIRE_DATE                                                       DATE

SALARY                                                             NUMBER(7,2)

COMMISSION                                                    NUMBER(7,2)

DEPARTMENT_ID                                            NUMBER(2)

Inserting:-

INSERT INTO employees (employee_id, last_name, email, hire_date, job_id, salary, commission_pct)

VALUES (207, 'Gregory', 'pgregory@oracle.com', sysdate, 'PU_CLERK', 1.2E3, NULL);

INSERT INTO (SELECT employee_id, last_name, email, hire_date, job_id, salary, commission_pct FROM employees)

VALUES (207, 'Gregory', 'pgregory@oracle.com', sysdate, 'PU_CLERK', 1.2E3, NULL);

Coding Predicates in a SELECT

The Predicates are found in the WHERE clause of the SELECT statement

Used to identify which rows we want Basic predicates compare only 2 values

The result is either True or False. If the value of either operand is null or the result of the subselect is empty, the result of the predicate is unknown.

Example- AND, OR, and NOT

 WHERE {NOT} predicate AND|OR

            {NOT} predicate …

        The result is either TRUE, FALSE, or Unknown about a given row or group         of rows (grouping predicates) If you don’t use parentheses, these operators         are processed in this order: NOT, AND, OR.

          WHERE field {NOT} IN(field1, field2, field3)

Equivalent to = ANY

Field can be a value, set or sub query    

 

            WHERE field {NOT} BETWEEN   field AND field

Logically Comparable to using >= and <=

           WHERE field IS {NOT} NULL

Due to the complexity of null handling, usage of Nulls has been discouraged in  FSU/AIS database table columns

 

If a pattern does not contain the “%” character, the condition can be TRUE only if both operands have the same length

         WHERE field {NOT} LIKE value

Wildcard characters:-

1. % - string of zero or more characters ‘ABC%’

2._ - underscore - any single character ‘A_C’

Examples:-

WHERE ename LIKE ‘MA%’

WHERE ename LIKE ‘SMITH_’

WHERE ename LIKE ‘S_I%’

 

Oracle SQL Functions:-

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

Example:-

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

Example- 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)

Example-TRUNC (d [,fmt]),SYSDATE,ROUND (d[,fmt]),NEXT_DAY(d,char)

NEW_TIME (d, a, b), MONTHS_BETWEEN (d, e)

Example-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;

Group Function:-

Occurs any time detail information is used only for the purpose of deriving summary information

Used to cluster your result into sets, or “groups” of rows that have equal values in the same columns

Use GROUP BY and HAVING

SYNTAX-         SELECT fields FROM tables

                WHERE conditions

GROUP BY fields

HAVING conditions

ORDER BY fields

GROUP BY is used to summarize the columns selected. HAVING optionally follows the GROUP BY and can be used to place a condition on the GROUP

 The HAVING condition can reference:

Functions Commonly Used With Group By

Example:-

 SELECT State ,Count (*) as Total

FROM pbowman.CUSTOMER

GROUP BY State;

Result:-         ST      TOTAL

CA          8

MA          6

MN          1

NY          9

Joining Tables:-

SELECT Employees.Name, Orders.Product

FROM Employees LEFT JOIN Orders ON Employees.Employee_ID=Orders.Employee_ID

SELECT Employees.Name, Orders.Product

FROM Employees RIGHT JOIN Orders ON

Employees.Employee_ID=Orders.Employee_ID    

Example:-

SELECT emp.name, emp.id, payroll.rate FROM emp, payroll

WHERE emp.id=payroll.id;

SELECT emp.name, emp.id, payroll.rat

FROM emp INNER JOIN payroll

ON emp.id=payroll.id

Table Aliases:-

SELECT workers.name, workers.id, payroll.rate

FROM workers, payroll

WHERE workers.id=payroll.id;

SELECT w.name, w.id, p.rate FROM workers w, payroll p WHERE w.id=p.id;

Error handling:-

GRANT:-

GRANT CREATE SESSION TO hr;

GRANT dw_manager TO sh WITH ADMIN OPTION;

REVOKE:-


REVOKE UPDATE ON hr.employees FROM oe;

Restrictions On Column Modifications:-

DROP and ALTER Statements:-

TO_DATE Function:-

Oracle requires dates in a particular format

VIEW:-

    SELECT ename, job, deptno

FROM emp

Where deptno=20

Sequences:-

Example:-

Create SEQUENCE SEQUENCENAME

Increment By N

Start  with N

Max value n/No max value

Min value n/No min value

DROP SEQUENCE SEQUENCENAME

Creating Indexes:-

SYNONYM:-

SYNTAX-

CREATE  PUBLIC SYNONYM  SYNONYMNAME

FOR  USERNAME.TABLENAME

EXAMPLE-

CREATE PUBLIC  SYNONYM  LOC

FOR SMITH.LOCATION

DROP SYNONYM  SYNONYMNAME 

Enterprise Manager Console:-

Application Logic:-

PL/SQL:-

BENIFITS OF PL/SQL:-

1. Easy Maintenance

2. Procedural extension allowing for modularity, variable declaration, 3. loops and logical constructs.

4. Allows for advanced error handling

5. Communicates natively with other oracle database objects.

6. Managed centrally within the Oracle database

7. All have procedural facilities

8. SQL is not functionally complete

9. So top up functionality by embedding SQL in a procedural language

10. PL/SQL techniques are specific to Oracle, Lacks full facilities of a programming language

11.but procedures and functions can be ported to other systems

Advantages of using PL/SQL to access Oracle:-

Using PL/SQL as a programming language:-

BODY OF PL/SQL:-

A PL/SQL block has the following three parts:

Only the Executable part is mandatory; the other two are optional

Example:-

[DECLARE

         ----- declarations]

BEGIN

         ----- statements

[EXCEPTION

         ----- handlers]

END;

Modules in PL/SQL:-

There are 4 types of modules in PL/SQL

 Procedures:-

Example:-

Create or replace  query_emp(p_id IN

emp.empno%TYPE,p_name OUT emp.ename%TYPE)

BEGAIN 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 

FUNCTION:-

Almost exactly like creating a procedure, but you

supply  a return type

Syntax:-

CREATE [OR REPLACE] FUNCTION function_name

 [(parameter1 [mode1] datatype1,parameter2 [mode2] datatype2, . . .)]

RETURN datatype 

IS|AS

PL/SQL Block;

Creating a function:-

create or replace function squareFunc(num in number)

return number

is

BEGIN

return num*num;

End;

Using thefunction:-

BEGIN

dbms_output.put_line(squareFunc(3.5));

END;

PACKAGE:-

-Package Specification

-Package Body

Triggers:-

Cursor overview:-

Trapping Exceptions:-

Example:-

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;

Records in PL/SQL

A PL/SQL record is a data structure composed of multiple pieces of information called fields. To use a record, you must first define it and declare a variable of this type. There are three types of records: table-based, cursor-based, and programmer-defined.

Declaring Records

You define and declare records either in the declaration section of a PL/SQL block or globally, via a package specification.

You do not have to explicitly define table-based or cursor-based records, as they are implicitly defined with the same structure as a table or a cursor. Variables of these types are declared via the %ROWTYPE attribute. The record's fields correspond to the table's columns or the columns in the SELECT list. For example:

DECLARE

   -- Declare table-based record for company table.

   comp_rec company%ROWTYPE

   CURSOR comp_summary_cur IS

      SELECT C.company_id,SUM(S.gross_sales) gross

        FROM company C, sales S

       WHERE C.company_id = S.company_id;

   -- Declare a cursor-based record.

   comp_summary_rec  comp_summary_cur%ROWTYPE;

Programmer-defined records must be explicitly defined with the TYPE statement in the PL/SQL declaration section or in a package specification. Variables of this type can then be declared as shown here:

DECLARE

   TYPE name_rectype IS RECORD(

      prefix       VARCHAR2(15)

      ,first_name  VARCHAR2(30)

      ,middle_name VARCHAR2(30)

      ,sur_name    VARCHAR2(30)

      ,suffix      VARCHAR2(10) );

   TYPE employee_rectype IS RECORD (

      emp_id       NUMBER(10) NOT NULL

      ,mgr_id      NUMBER(10)

      ,dept_no     dept.deptno%TYPE

      ,title       VARCHAR2(20)

      ,name        empname_rectype

      ,hire_date   DATE := SYSDATE

      ,fresh_out   BOOLEAN );

   -- Declare a variable of this type.

   new_emp_rec employee_rectype;

BEGIN

PL/SQL (Simple and Compound Symbols):-

Symbol                         Description

REFERENCE:-

1