add

About Me

My photo
Oracle Apps - Techno Functional consultant

Sunday, August 19

Oracle SQL and PL/SQL



ORACLE



Chapter 1 : Introduction

Structure query language (SQL), Is this set commends that all programs and users must
use to access data within the oracle database.
Dr.E.F codd was published in June 1970.RDBMS-Relational Database Management
System.

Chapter 2 :  SQL 

      SQL Statements

Four types

a.      DDL(Data Definition Language)
b.      DML (Data Manipulation Language)
c.       DCL (Data Control Language)
d.      TPL (Transactions Control Language)


 DDL                          DML                           DCL                           TCL

Create                         select                           select                           commit
Alter                           insert                           grand                          rollback         
Drop                           update                                    revoke                         savepoint                   
Grant                          delete                                                             
Revoke                       lock table                   


Chapter 3: SQL*Plus


  1. SQL*Plus is Oracle's command-line interpreter.
  2. The `start' menu, SQL*Plus is listed under programs > oracle > application  
  3. Development > SQL Plus.
  4. You will be prompted for your username and password.
  5. You can try to use scott for the username, and tiger for the password.
  6. You are now connected to a shared database, on which you have an account (called a               
     schema ).



Table :1
outlines the main Oracle SQL data types


Table 1: The main SQL data types.
Type description
Oracle SQL
MySQL SQL
variable-length char. string
VARCHAR2(l)
VARCHAR(l)
fixed-length char. string
CHAR(l)
CHAR(l)
Number
NUMBER(p,s)
NUMERIC(p,s)
Currency
NUMBER(10,2)
NUMERIC(10,2)
Date
DATE
DATE



1 length.
2 precision, scale.


The NUMBER (p, s) type takes two arguments; precision and scale. The precision of a
number its number of significant decimal digits its scale is the number of digits after the
decimal point.

SQL Works

Ø  Query in data .
Ø  Inserting, updating, deleting and rows in a table.
Ø  Creating, replacing, altering, and dropping objects.
Ø  Controlling access to the database and its objects.
Ø  Guaranteeing database consistency and integrity.


Chapter 4 : TABLE

Create table – [Table Name: empl]

create table empl(eno number(3),ename varchar2(8),DOB date,Basic number(5),
hra number(4), namt number(5));

Table created.

Table View

desc empl
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ENO                                                NUMBER(3)
 ENAME                                          VARCHAR2(8)
 DOB                                                DATE
 BASIC                                             NUMBER(5)
 HRA                                                NUMBER(4)
 NAMT                                             NUMBER(5)

Alter Table

Additional width changes(modify)

 alter table empl modify(eno number(5));

Table altered.


Table Show

desc empl;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ENO                                                NUMBER(5)
 ENAME                                          VARCHAR2(8)
 DOB                                                DATE
 BASIC                                             NUMBER(5)
 HRA                                                NUMBER(4)
 NAMT                                             NUMBER(5)

 Add Column

Alter Table  empl add(grade varchar2(2));
Table Altered.

Table show
 
desc empl;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ENO                                                NUMBER(4)
 ENAME                                          VARCHAR2(8)
 DOB                                                DATE
 BASIC                                             NUMBER(5)
 HRA                                                NUMBER(4)
 NAMT                                             NUMBER(5)
 GRADE                                           VARCHAR2(2)


Create Record- [Type-1]

 insert into empl values(&eno,'&ename','&dob',&basic,&hra,&namt,&grade);
Enter value for eno: 100
Enter value for ename: Scott
Enter value for dob: 17-oct-83
Enter value for basic: 5000
Enter value for hra: 500
Enter value for namt: 0
Enter value for grade: null
old   1: insert into empl values(&eno,'&ename','&dob',&basic,&hra,&namt,&grade)
new   1: insert into empl values(100,'Scott','17-oct-83',5000,500,0,null)

1 row created.
/

Enter value for eno: 101
Enter value for ename: adler
Enter value for dob: 12-jan-88
Enter value for basic: 6000
Enter value for hra: 600
Enter value for namt: 0
Enter value for grade: null
old   1: insert into empl values(&eno ,’&ename','&dob',&basic,&hra,&namt,&grade)
new   1: insert into empl values(101,'adler','12-jan-88',6000,600,0,null)

1 row created.

Create Record- [Type-2]

insert into empl values(103,'sandy','14-may-78',7000,700,0,null);

1 row created.

Select a record

select * from empl;

ENO    ENAME          DOB               BASIC                        HRA       NAMT GR
---------- -------- --------- ---------- ---------- ---------- ----------------------------
100      Scott                17-OCT-83      5000                500          0
101      adler                            12-JAN-88      6000                600          0
103      sandy                          14-MAY-78    7000                700          0


Table Rename

create table old(ename char(10),sal number(5));

Table created.

create table new as (select * from old);
Table created.

desc new;

 Name                          Null?    Type
 ----------------------------------------- -------- -------
 ENAME                     CHAR(10)
 SAL                           NUMBER(5)

desc old;

 Name                          Null?       Type
 ----------------------------------------- -------- -------
 ENAME                     CHAR(10)
 SAL                           NUMBER(5)

drop table old;

Table dropped.


Delete table

drop table empl;

Table dropped.


Delete Record

delete from empl where ename='sandy';
1 row deleted.

select * from empl;

ENO    ENAME          DOB               BASIC                        HRA   NAMT GR
---------- -------- --------- ---------- ---------- ---------- -------------------------
100      Scott                17-OCT-83       5000               500          0
101      adler                            12-JAN-88        6000              600          0


Roll back & Savepoint

 delete from empl where hra=600;
1 row deleted.

1.Savepoint

savepoint c;
Savepoint created.

select * from empl;

       ENO ENAME    DOB            BASIC        HRA       NAMT GR
       ---------- -------- --------- ---------- ---------- ---------- --
       100 Scott    17-OCT-83       5000        500          0

2. Roll back
roll back to c;
Rollback complete.

select * from empl;

       ENO ENAME    DOB            BASIC        HRA       NAMT GR
      ---------- -------- --------- ---------- ---------- ---------- --
       100 Scott    17-OCT-83       5000        500          0
       101 adler      12-JAN-88       6000        600          0




Select Particular Column

select ename,dob from empl;

ENAME   DOB
--------     ---------
Scott      17-OCT-83
adler         12-JAN-88




Record Update

 update empl set ename='kumar' where eno=103;

1 row updated.

 select * from empl;

ENO    ENAME          DOB               BASIC                        HRA     NAMT GR
---------- -------- --------- ---------- ----------  ---------------------------------- --
100      Scott                17-OCT-83       5000               500          0
101      adler                12-JAN-88       6000               600          0
103      kumar              14-MAY-78     7000               700          0


Chapter 5:Where clause



    Common Comparison operators
a.      Operator (=)
 select * from empl where ename = 'sandy';

       ENO ENAME    DOB            BASIC        HRA       NAMT GR
     ---------- -------- --------- ---------- ---------- ---------- --
       103 sandy      14-MAY-78       7000        700          0


b.      Operator (! = (or) ^= (or) <>)

 select * from empl where ename != 'sandy';

       ENO ENAME    DOB      BASIC    HRA    NAMT GR
      ---------- -------- ---------    ---------- ---------- ----------    --
       100 Scott    17-OCT-83       5000      500          0
       101 adler       12-JAN-88        6000      600          0
       102 ravi       27-JUL-07        4500      400          0


c.       Less than ( < )

 select * from empl where basic < 5000;

       ENO ENAME    DOB            BASIC        HRA       NAMT GR
      ---------- -------- --------- ---------- ---------- ---------- --
       102 ravi     27-JUL-07       4500        400          0

d.      Greater than ( > )

  select * from empl where basic >5000;

       ENO ENAME    DOB        BASIC   HRA    NAMT   GR
      ----    ------        -------          --------- ---------- ----------  --
       101   adler       12-JAN-88      6000       600          0
       103   sandy        14-MAY-78     7000       700          0

e.       Less then or equal to (<=)

 select * from empl where basic <=5000;

       ENO ENAME    DOB            BASIC        HRA       NAMT   GR
      ---------- -------- --------- ---------- ---------- ---------- -------------------
       100 Scott    17-OCT-83       5000        500          0
       102 ravi     27-JUL-07         4500         400          0

f.       Greater than or equal to

   select * from empl where basic >= 5000;

     ENO         ENAME    DOB            BASIC        HRA       NAMT GR
    ---------- -------- --------- ---------- ---------- ---------- ---------------
     100           Scott    17-OCT-83       5000        500           0
     101           adler       12-JAN-88        6000        600           0
     103           sandy       14-MAY-78       7000        700           0


g.      In


 select * from empl where basic in (5000,7000);

       ENO    ENAME    DOB         BASIC     HRA     NAMT GR
      ---------- -------- --------- ---------- ---------- ---------- --
       100      Scott     17-OCT-83      5000        500          0
       103       sandy         14-MAY-78     7000        700          0


h.      Not in

 select * from empl where basic not in (5000,7000);

       ENO  ENAME   DOB           BASIC      HRA    NAMT GR
       ----     --------    ---------         ---------- ---------- ---------- --
       101      adler      12-JAN-88      6000        600          0
       102      ravi      27-JUL-07      4500        400          0

i.        Between A and B

 select * from empl where hra between 100 and 500;

       ENO ENAME    DOB            BASIC        HRA       NAMT GR
       ---------- -------- --------- ---------- ---------- ---------- --
       100 Scott    17-OCT-83       5000        500          0
       102 ravi     27-JUL-07         4500        400           0


j.        Not Between  A and B

 select * from empl where hra between 100 and 500;

       ENO   ENAME    DOB            BASIC        HRA       NAMT GR
      ---------- -------- --------- ---------- ---------- ---------- -----------------
       100     Scott         17-OCT-83     5000        500          0
       102     ravi           27-JUL-07       4500        400          0




k.      Like ‘%adler%’

   select * from empl where ename like '%adler%';

  ENO ENAME    DOB            BASIC        HRA       NAMT GR
----- -------- --------- ---------- ---------- ---------- ----------------------
  101 adler      12-JAN-88       6000        600          0

Order by

select * from empl order by dob desc,ename;

ENO    ENAME          DOB               BASIC    HRA       NAMT GR
-----------------------------------------------------------------------------------
101      adler                12-JAN-88       6000        600          0
100      Scott                17-OCT-83      5000        500          0
103      sandy               14-MAY-78     7000        700          0


Chapter 6:  Functions
·         Group Functions
·         single Row function

Group function             single Row function






 

Count                                 a. number
 Sum                                   b. char
 Avg                                   c.  date
 Max                                   d. Misellances
 Min


Group Functions

a. Count:

            select count(eno)from empl;

COUNT(ENO)
------------------
         2
b.Sum:

            select sum(Basic)from empl;

SUM(BASIC)
-----------------
  11000

c. Avg:

select avg(hra)from empl;

  AVG(HRA)
-----------------
   550

d. Max:

 select max(basic) from empl;

MAX(BASIC)
------------------
  6000

e. Min:

select min(HRA)from empl;

  MIN(HRA)
  ---------------
       500



Single Row Function


Number Data Type

a. Addition (+)

 select basic + hra from empl;

 BASIC+HRA
------------------
      5500
      6600
      7700


b.      Subtraction (-)

select basic - hra from empl;

 BASIC-HRA
------------------
      4500
      5400
      6300



c.       Multiplication (*)

select basic * hra from empl;

  BASIC*HRA
 -----------------
   2500000
   3600000
   4900000

d.      Division (/)

select basic /100 from empl;

 BASIC/100
 --------------
        50
        60
        70

Number function

a. Abs
            select abs(-24)from dual;

 ABS(-24)
------------
        24



b. Ceil
            select ceil(25.17)from dual;

CEIL(25.17)
 -----------
         26




c. Floor
 select floor(16.8)from dual;

FLOOR(16.8)
----------------
         16


d. Mod
 select mod(7,2)from dual;

  MOD (7,2)
  ----------
         1


e. Round

 select round(25.7)from dual;

ROUND(25.7)
-----------
         26


f . Sqrt

select sqrt(25)from dual;

  SQRT(25)
  ----------
         5

g. Power
select power(2,3)from dual;

POWER(2,3)
----------
         8



h. Sign:
select sign(12) from dual;

  SIGN(12)
  ----------
         1




Character Data Type

a. Concat

            select concat('Sandy','Kumar')from dual;

    CONCAT('
    -------------
    SandyKumar

b. Initcap

select initcap('computer')from dual;

 INITCAP(
 -------------
Computer

c. Length

            select length('Shanthi')from dual;

LENGTH('SHANTHI')
----------------------------
                7

d. lower

select lower('HEENA')from dual;

LOWER
-----------
Heena



E. Replace
 select replace('Scott','S','Boy')from dual;

REPLACE
-------
Boycott






6. Substr
 select substr('WELCOME',2,3)from dual;

SUB
---
ELC


7. Upper
 select upper('welcome')from dual;

UPPER('
-------
WELCOME




8.LTrim

 select LTrim('dur         ')from dual;

LTRIM('DUR')
------------
Dur


9.RTim

 select RTrim('      dur')from dual;

RTRIM('DU
---------
      Dur


10.LPad:

 select LPAD('page1',15,'*')from dual;

LPAD('PAGE1',15
---------------
**********page1
11. ASCII

 select ascii('R')from dual;

ASCII('R')
----------
        82

Common Function Date Data Type

1.SYSdate

 select sysdate from dual;

SYSDATE
---------
20-JUL-07



2.Last_day

 select last_day(sysdate)from dual;

LAST_DAY(
---------
31-JUL-07



3. Add months

 select add_months(sysdate,2)from dual;

ADD_MONTH
---------
20-SEP-07

4. Months_between

 select months_between(sysdate,'12-mar-07')from dual;


MONTHS_BETWEEN(SYSDATE,'12-MAR-07')
-----------------------------------
                         4.27143407


5. Next_day

 select next_day(sysdate, 'monday')from dual;

NEXT_DAY(
---------
23-JUL-07




Special format date data type

1.Y or YY or YYY:

  select to_char(sysdate,'yyyy')from dual;

TO_C
 ----
2007

2. SYEAR or YEAR

 select to_char(sysdate,'SYEAR')from dual;

TO_CHAR(SYSDATE,'SYEAR')
-------------------------------------------
 TWO THOUSAND SEVEN



3.Q:

 select to_char(sysdate,'Q')from dual;

T
-
3

4.MM:

 select to_char(sysdate,'mm')from dual;

TO
--
07

5.RM(Roman Numarical month)

 select to_char(sysdate,'RM'
  2  )from dual;

TO_C
----
VII

6.Month

 select to_char(sysdate,'month')from dual;

TO_CHAR(S
---------
July

7.WW(week of year)

 select to_char(sysdate,'WW')from dual;

TO
--
29

8.W(week of the month)

 select to_char(sysdate,'W')from dual;

T
-
3

9.DDD(day of the year)

 select to_char(sysdate,'DDD')from dual;

TO_
---
201

10.DD (day of the month)

 select to_char(sysdate,'DD')from dual;

TO
--
20

11.D (day of the week)
 select to_char(sysdate,'D')from dual;

T
-
6
12.DY(abbreviated name of the day)

 select to_char(sysdate,'DY')from dual;

TO_
---
FRI

13.HH or HH12(hour of day)

 select to_char(sysdate,'HH')from dual;

TO
--
11

select to_char(sysdate,'HH12')from dual

TO
--
11

14.HH24(hour of day using)

 select to_char(sysdate,'HH24')from dual

TO
--
11

15.MI(minutes)
 select to_char(sysdate,'MI')from dual;
TO
--
06

16.SS(seconds)

 select to_char(sysdate,'SS')from dual;

TO
--
48


Converting from one column type to another


1.To char

 select to_char (8897) from dual;

TO_C
----
8897

2. To number

 select to_number ('8897') from dual;

TO_NUMBER('8897')
-----------------
             8897
3. To_date

  select to_date('20-jul-07')from dual;

TO_DATE('
---------
20-JUL-07



Chapter 7:    Sub query



select * from empl;

  ENO ENAME    DOB            BASIC        HRA       NAMT GR
----- -------- --------- ---------- ---------- ---------- ----------------------
  100    Scott     17-OCT-83       5000        500          0
  101    adler        12-JAN-88        6000        600          0
  103    sandy         14-MAY-78       7000        700          0
  102    ravi       27-JUL-07         4500        400          0
  104    geetha   22-SEP-83         4500        200          0
  105   geetha    13-AUG-91       5000        300          0
  106   adlerk      28-JUN-87         6500        400          0


In :

  select * from empl where eno in (select eno from empl where eno=101);

       ENO    ENAME       DOB              BASIC        HRA       NAMT GR
      ---------- -------- --------- ---------- ---------- ---------- ---------------------
       101        adler         12-JAN-88            6000        600          0


Not in
   select * from empl where eno not in (select eno from empl where eno=101);

ENO    ENAME    DOB            BASIC        HRA       NAMT GR
  ---- -------- --------- ---------- ---------- ---------- -------------------------
 100     Scott        17-OCT-83       5000        500           0
 103     sandy             14-MAY-78      7000        700           0
 102     ravi           27-JUL-07        4500        400           0
 104     geetha       22-SEP-83        4500        200           0
 105     geetha      13-AUG-91       5000        300           0
 106     adlerk         28-JUN-87        6500        400           0




Greater than

select * from empl where basic > (select basic from empl where ename='adler');

  ENO    ENAME    DOB            BASIC        HRA       NAMT GR
----- -------- --------- ---------- ---------- ---------- ------------------------
  103      sandy         14-MAY-78       7000          700           0
  106      adlerk      28-JUN-87        6500          400           0


Greater than or equal to

select * from empl where basic >=(select basic from empl where ename='adler');

  ENO    ENAME    DOB            BASIC        HRA       NAMT GR
----- -------- --------- ---------- ---------- ---------- -------------------------
  101      adler         12-JAN-88        6000         600           0
  103      sandy          14-MAY-78       7000         700          0
  106      adlerk       28-JUN-87        6500         400           0


Less than

select * from empl where basic < (select basic from empl where basic=6000);

 ENO     ENAME        DOB            BASIC        HRA       NAMT GR
------ -------- --------- ---------- ---------- ---------- ---------------------------
   100      Scott        17-OCT-83       5000          500             0
   102      ravi           27-JUL-07        4500          400             0
   104      geetha      22-SEP-83        4500           200             0
   105      geetha     13-AUG-91        5000           300            0


Less than or equal to

  select * from empl where basic <= (select basic from empl where basic=6000);

   ENO ENAME    DOB            BASIC   HRA       NAMT GR
  ------ -------- --------- ---------- ---------- ---------- ----------------------
   100 Scott    17-OCT-83         5000        500            0
   101 adler       12-JAN-88          6000        600            0
   102 ravi       27-JUL-07          4500        400            0
   104 geetha   22-SEP-83          4500        200            0
   105 geetha   13-AUG-91        5000        300            0




Update condition

 update emp1 set sal=sal*1.15 where job='clerk' and deptno=40;

1 row updated.

 select * from emp1;
    


EMPNO   ENAME    JOB            MGR        HDATE                 SAL        DEPTNO CITY      PHONE
----------------------------------------------------------------------------------------------------------
100        john       manager     1207       05-JAN-07       5000         10

102       mohan    clerk          1980        17-JUL-07       3000         20

103        shan      Gm            4576        21-JUN-07       7000         30




EMPNO   ENAME      JOB            MGR       HDATE                 SAL      DEPTNO       CITY      PHONE
-------------------------------------------------------------------------------------------------------------------------------
104        guna        Am           5689      12-JAN-07       6500       40            7         34566
104        badler        clerk         2345      12-JAN-85       3450       40
105       shan        Manager    45          23-JUL-87       6500        20            3       234563



Excises:

Create table salary:
Field:eno,ename,date,dest,deptno,loc,basic,hra,pf,da,loan,net
Insert 12 records

  1. calculate net salary
  2. find out the employees with the same job as name(eg:Ravi)
  3. List all the employees who earn more than all employees in sales department?
  4. List the employees with either the same job in all departments?
  5. List the employee in dept with the same job as anyone working in Chennai
  6. Calculate how many members greater than 10000 salary and list out details.
  7. Update to basic field increment RS. 500 in below 10000 salaries.




Chapter 8:   Set of operators



Joins & subquries

Union
Union all
Intersect
Minus







 select * from cust;
       CNO CNAME        DEPTNO
      ---------- -------- ----------
       101 ramesh           10
       102 suresh           20
       103 feena            30
       104 durga            20
       105 heena            30
       106 sudha            10
       107 fg                   50
 

 select * from dept;

    DEPTNO DNAME          LOC
     ---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON
 

 














Union

 Two output queries merge(duplicate elements discard)

   select deptno from dept union select deptno from cust;
   
DEPTNO
   ----------
        10
        20
        30
        40
        50


Union all

Duplicate elements display
 select deptno from dept union all  select deptno from cust;

    DEPTNO
    ----------
        10
        20
        30
        10
        20
        30

Intersect
Common values display.

 select deptno from dept intersect select deptno from cust;

    DEPTNO
     ----------
        10
        20
        30

Minus

[Different values display]  Minus operators return all distinct rows. Selected only by first query and not by second query.

select deptno from dept minus select deptno from cust;

    DEPTNO
     ----------
        40



Chapter 9: Join Two Tables



Joining two tables together

Two table’s values displayed.
To define relationship between multiple table and with in a single command.






 select * from dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH           DALLAS
        30 SALES                   CHICAGO
        40 OPERATIONS      BOSTON
 

 select * from cust;

       CNO CNAME        DEPTNO
       ---------- --------        ----------
       101 ramesh                 10
       102 suresh                   20
       103 feena                    30
       104 kala                      40
 
 








Create Object

Object –Redselect red .deptno,blue .deptno from dept red,cust blue where blue .deptno=red.deptno;

 
  DEPTNO  DEPTNO
    ----------   ----------
        10         10
        20         20
        30         30
        40         40










Chapter 10 Types of  Joins

Simple  join---   a. Equi join & Non equi join
                          b. Outer join
                          c. Self Join


Equi join:

 select * from z;

       AGE        DNO
    ---------- ----------
        16         10
        18         20
        21         30
        15         40

 select * from x;

        NO     NAME     DNO
     ---------- --------    ----------
       100        sandy               10
       101        deepa          20
       102        badler            30
       104        kalai            50

select x1.dno,name,age,z1.dno from x x1,z z1 where x1.dno=z1.dno;

       DNO    NAME    AGE      DNO
       ---------- -------- ---------- ----------
        10            sandy           16         10
        20           deepa       18         20
        30           badler         21         30

Non Equi join




Outer Join
  (+) operator can appear only in the where cause, and can be applied only to a column of a table or    view.
 

select x1.dno,name,z1.dno from x x1,y z1 where x1.dno(+)=z1.dno;

   
   DNO NAME            DNO
       ---------- -------- ----------
                                  10
        20 deepa            20
        20 heena            20
        20 deepa            20
        20 heena            20
        30 badler             30
                                  40

Self Join

Type of join where the join operation takes place in the same table.



select s.name ||'works fro' || z.dname from x s ,y z;

S.NAME||'WORKSFRO'||Z.DNAME
---------------------------
sandyworks fromanager
deepaworks fromanager
badlerworks fromanager
adlerworks fromanager
vijworks fromanager
kalaiworks fromanager
heenaworks fromanager
sandyworks froclerk
deepaworks froclerk
badlerworks froclerk
adlerworks froclerk


Chapter 11

Views, Synonyms

Views :( an Exiting table copy of fields)

A view is a subset of data one or more table.

create view em as select name,dno from x;

View created.

select * from em;

NAME            DNO
--------       ----------
deepa              20
badler               30
adler                 60
vij                  34
kalai               50
heena             20

6 rows selected.
 insert into em values('mohan',10);

1 row inserted.

Synonyms

 Synonym is a data base object. That is used as an alias name
 for any object.

Advantages:

·         Simplify SQL statements
·         Hide the read identify of an object.
·         Are useful in database links.

create synonym a2 for x;

Synonym created.

 select * from a2;


   NO     NAME     DNO
  ------- -------- ----------
  101    deepa         20
  102    badler           30
            adler             60
      3    vij               34
  104   kalai            50
  105   heena          20
           mohan         10

7 rows selected.

Sequences

Sequence are a set of data base object which can generate sequential integer values.


 create sequence aa                                          //  aa-sequence name
  2   start with 2                                               // starting number
  3   minvalue 1                                                // for loop initialize
  4  increment by 1                                          // increment value
  5   maxvalue 10                                             //maximum value up to 10
  6   cycle                                                         //Looping
  7   cache 5;                                                    //Memory Allocation (i.e) 5 records must be kept access 
              Fast

Sequence created.

select aa.nextval from dual;

   NEXTVAL
   ----------
         2
 /
   NEXTVAL
   ----------
         3
:
:
 /
 NEXTVAL
 ----------
         1

 insert into x(no,name,dno)values(aa.nextval,'meena',56);

1 row created.

 select * from x;

NO       NAME            DNO
---------- -------- ----------------
5          meena             56
101      deepa              20
102      badler               30
            adler                 60
3          vij                  34
104      kalai               50
105      heena             20
            mohan           10
 2         meena            56

9 rows selected.


Clusters

          Cluster are an optional method of storing table data
 create cluster clum(dno number(3));
  
Cluster created.

  create index red on cluster clum;

 Index created.

 create table m1(dno number(3),name varchar2(6)) cluster clum(dno);

Table created.
 create table n(dno number(3),age number(5))cluster clum(dno);

Table created.
select rowid from cluster clum;






 select * from m1;

       DNO NAME
      ---------- ------
        10 anu
        20 deepa
        30 banu
        40 ravi
 

 select * from n;

       DNO        AGE
      ---------- ----------
        10         23
        20         17
        30         18
        50         22
 
 


ROWID
------------------
AAAGFjAABAAAIV0AAA
AAAGFjAABAAAIV1AAA
AAAGFjAABAAAIV2AAA
AAAGFjAABAAAIV3AAA
AAAGFjAABAAAIV4AAA





 select * from cluster clum;

       DNO
    ----------
        10
        20
        30
        40
        50



Chapter 10

Locking


            Oracle automatically locks the resources on behalf of the transaction
1.      Row level lock
2.      Table level lock

Row level lock
 select * from x where no='100' for update of dno;

        NO NAME    DNO
       ------- -------- ----------
       100     ar           43

Table level lock

 lock table x share update mode;

Table(s) Locked.



Chapter 11

Oracle partitioning


    Partition is introduced in oracle. It is very fast.

 Create  table sales (oid number,oamt number(6,2))partition by range(oid)(partition p1
 values less than(100),partition p2 values less than(200)));
Table created.

 insert into sales values(&oid,&oamt);
Enter value for oid: 103
Enter value for oamt: 300
old   1: insert into sales values(&oid,&oamt)
new   1: insert into sales values(103,300)

1 row created.

 /
Enter value for oid: 40
Enter value for oamt: 250
old   1: insert into sales values(&oid,&oamt)
new   1: insert into sales values(40,250)

1 row created.

 select * from sales;

       OID       OAMT
     ---------- ----------
        40        250
        60        270
       103        300
       120        180

select * from sales partition(p1);

       OID       OAMT
     ---------- ----------
        40        250
        60        270

 select * from sales partition(p2);
       OID       OAMT
    ---------- ----------
       103        300
       120        180

Collection of arrays

A collection is an order group of elements of the same data type. An element can be
determined by its unique subscript.

  1. Varray


 create or Replace type b1 as VArray(5) of number(3);
  2  /

Type created.

 create table pen(pno number,rate b1);

Table created.

insert into pen values(2,b1(34,56,87,65));

1 row created.

 insert into pen values(1,b1(23,45,65,32));

1 row created.

select * from pen;

   PNO
----------
RATE
-----------------------------------------------------------------
         2
B1(34, 56, 87, 65)

         1
B1(23, 45, 65, 32)

Error
 insert into pen values(4,b1(34,56,87,65,52,32));
 insert into pen values(4,b1(34,56,87,65,52,32))
                          *
ERROR at line 1:
ORA-22909: exceeded maximum VARRAY limit


Chapter 12

PL-SQL

PL-SQL is a block structured language. The basic unit of pl-SQL is called a block which
contains declarative statements. Executable statements and error handling statements. The
blocks can be nested in one or more blocks.
1.      Declarative
2.      Executable
3.      error handling
syntax:

[< Block header>]
[ declare
   < Constants>
   < Variables>
   < Cursors>
   < User defined exceptions>]
Begin
<PL/ SQL statements>
[ exception< Exception handling>]
end;

  1. Welcome statements

 begin
  2  dbms_output.put_line('Welcome');
  3  end;
  4  /

PL/SQL procedure successfully completed.

 set serveroutput on;
 /

Welcome

PL/SQL procedure successfully completed.

Lab Exercises
  1. print in your address
  2. print 5 countries name


2.
 declare
  2  s number default 10;
  3  begin
  4  dbms_output.put_line(s);
  5  s:=20;
  6  dbms_output.put_line(s);
  7  end;
  8  /
output:
10
20

PL/SQL procedure successfully completed.

3. Print the output
 declare
  2  x number;
  3  begin
  4  x:=35;
  5  dbms_output.put_line(x);
  6  end;
  7  /
35

PL/SQL procedure successfully completed.



1.      Enter the input
 declare
  2  x number;
  3  begin
  4  x:=&x;
  5  dbms_output.put_line(x);
  6  end;
  7  /
Enter value for x: 24
old   4: x:=&x;
new   4: x:=24;
24

PL/SQL procedure successfully completed.
Lab Exercises
  1. Enter student marks (min 5 marks).


2.      Add two numbers
 declare
  2  x number;
  3  y number;
  4  begin
  5  x:=&x;
  6  y:=&y;
  7  dbms_output.put_line(x+y);
  8  end;
  9  /
Enter value for x: 20
old  12: &x;
new  12: 20;
Enter value for y: 10
old  13: y:=&y;
new  13: y:=10;
30

PL/SQL procedure successfully completed.
Lab Exercises
  1. Find out c values c=a+b,
                                        c=a-b,
                                        c=a*b, c=a/b ?
  1. calculate area?
  2. calculate l* b*h?
  3. calculate  z=p*q - r + s / t *u ?
  4. calculate student marksheet,(tot,avg) ?



3.     
If condition
Syntax:
If condition then
   //statements;
End if;
 
If condition                                                                      
                                                                                               
 declare
  2  s number;
  3  begin
  4   s:=&s;
  5  if s>0 then
  6  dbms_output.put_line('The number is positive');
  7  end if;
  8  end;
  9  /
Enter value for s: 36
old   4:  s:=&s;                                                                        
new   4:  s:=36;
The number is positive


If.. then ..else condition
Syntax:
If condition then
   //statements;
Else
   //statements;
End if;
 
 
PL/SQL procedure successfully completed.
4.      If…then …else condition
 declare
  2  s number;
  3  begin
  4  s:=&s;
  5  if s>0 then
  6  dbms_output.put_line('The number is positive');
  7  else
  8   dbms_output.put_line('The number is negative');
  9  end if;
 10  end;
 11  /
Enter value for s: -23
old   4: s:=&s;
new   4: s:=-23;
The number is negative

PL/SQL procedure successfully completed.

Lab Exercises
  1. Find out the values greatest 2 numbers.
        2.  Create Mark Sheet? How to calculate the Total, Average, Result, Grade?
 
Chapter 13

Iterative control
  1. loop
  2. while
  3. for loop
Loop

 declare
  2  a number:=&a;                                                                 
  3  begin
Loop:
Syntax:
Loop
//   statements
End loop
 
  4  loop
  5  dbms_output.put_line(a);
  6  a:=a+1;
  7  if a>10 then
  8  exit;
  9  end if;
 10  end loop;
 11  end;
 12  /
Enter value for a: 2
old   2: a number:=&a;
new   2: a number:=2;
2
3
4
5
6
7
8
9
10

PL/SQL procedure successfully completed.

Lab Exercises
  1. Calculate  1+2+3….n;
  2. Calculate  square of 1+2+3….n;


While loop

  declare
While Loop:
Syntax:
While Condition Loop
//   statements
End loop
 
  2   x integer:=1;
  3   y integer:=0;                                                                    
  4  begin
  5  while x<=5
  6  loop
  7  y:=y+x;
  8  x:=x+1;
  9  end loop;
 10  dbms_output.put_line(y);
 11  end;
 12  /
15

PL/SQL procedure successfully completed.

FOR  LOOP:

 declare
  2  x number :=50;
  3  begin
  4  dbms_output.put_line(x);
  5  for x in 2..5 loop
  6     dbms_output.put_line(x);
  7   end loop;
  8  dbms_output.put_line(x);
  9  end;
 10  /
50
2
3
4
5
50

PL/SQL procedure successfully completed.





Chapter 14

Run
Set serveroutput on;
/

 
Cursors
 PL-SQL uses cursor for management of SQL select statement.
Cursor are chunks of memory allocated to process these statements  
              
Type of cursors
  1. implement cursor
  2. Explicit cursor
Implement cursor
When the executable part of PL/SQL block issues a SQL command, PL/SQL create an implicit cursor which has the identifier SQL,PL/SQL manages this cursors.
Explicit cursor
The query is executed and the returned rows are manipulated within the cursor. Explicit cursor can be of two type.
  1. static cursor
  2. dynamic cursors.
static cursor
 static cursor or a type of cursors when the select statements given at compile time itself.
dynamic cursors
dynamic cursors or the name suggests or a set of cursor where the records from  the tables are selected at run time rather then compile time.
Attributes
  1. %Row count –the attribute yields the number of  rows affected by an INSERT,UPDATE, OR     
                                DELETE statements, or return by a SELECT into command.
  1. %Found- this attributes yields TRUE if ands INSERT,UPDATE, or DELETE statements                     
                     Affected one or more rows or SELECT INTO statement returned one or more rows.
                    Otherwise it false.
  1. %NotFound- This attributes is the logical opposite of %found. it yields true if and   
                             INSERT, UPDATE, OR DELETE statements affected no rows, are a select  
                            Into statement return no rows otherwise it yields false.
  1. % Isopen- this attributes always yields FALSE because closes the SQL cursor automatically after executing is associated SQL statements.

Example 1:

    declare
  2  n number(5);
Output:
In the emp1 table 1 record was deleted.
 
  3  begin                                                                                
  4  delete emp1 where empno=103;
  5  if sql%found then
  6  dbms_output.put_line('Record deleted');
  7  else
  8  dbms_output.put_line('no matching rows');
  9  end if;
 10  end;
 11  /

PL/SQL procedure successfully completed.


Cursor using for loop
 declare
  2    cursor c1 is select ename,sal from emp;
  3   begin                
  4   for i in c1
  5   loop
  6   insert into final values(i.ename,i.sal);                          //final (table name) using two parameters.
  7   end loop;                                                                        name,salary.
  8   end;
  9  /

PL/SQL procedure successfully completed.

select * from final;

NAME         SALARY
-------- ----------
badler           4000
heena          5000
SMITH           800
ALLEN          1600
WARD           1250
JONES          2975
MARTIN         1250
BLAKE          2850
CLARK          2450
SCOTT          3000
KING           5000

NAME         SALARY
-------- ----------
TURNER         1500
ADAMS          1100
JAMES           950
FORD           3000
MILLER         1300

Cursor using Attributes
  declare
  2   cursor c1 is select ename,job from emp;
  3   mname varchar2(10);
  4   njob varchar2(12);
  5   begin
  6   open c1;
  7   loop
  8   fetch c1 into mname,njob;
  9   exit when c1 %notfound;
 10  dbms_output.put_line(mname ||'   welcome    '||njob);
 11   end loop;
 12   close c1;
 13   end;
 14  /
SMITH   welcome    CLERK
ALLEN   welcome    SALESMAN
WARD   welcome    SALESMAN
JONES   welcome    MANAGER
MARTIN   welcome    SALESMAN
BLAKE   welcome    MANAGER
CLARK   welcome    MANAGER
SCOTT   welcome    ANALYST
KING   welcome    PRESIDENT
TURNER   welcome    SALESMAN
ADAMS   welcome    CLERK
JAMES   welcome    CLERK
FORD   welcome    ANALYST
MILLER   welcome    CLERK

PL/SQL procedure successfully completed.

Chapter 15

Composite data type


declare
 type tree is record(a varchar2(10),b number);
 red tree;
 cursor c1 is select pname,pcode from product;
 begin
open c1;
loop
fetch c1 into red;
exit when c1 %notfound;
dbms_output.put_line(red.a ||'is a'||red.b);
end loop;
close c1;
end ;
/
honeyis a10
riceis a20
luxis a15

PL/SQL procedure successfully completed.


Chapter 16
Exception
 An error is an up normal condition that arises during the exception of a program. Errors arise from design faults, Mistakes, hardware failures and other sources.
3 types
1. Predefined exception
2. user defined exception
3. un defined exception

1. Predefined exception

    1. Access_into_null
    2. Collection _is_null
    3. Cursor _Already_open
    4. Dup_val_on_index
    5. Invalid_cursor
    6. Invalid_number
    7. Login _denied
    8. No_data_found
    9. Program_error
    10. Too_many_rows
    11. Value_error
    12. Zero_divide

Error program
 declare
  2  x number:=&x;
  3  y number:=&y;
  4  begin
  5  dbms_output.put_line('Results'||x/y);
  6  end;
  7  /
 set serveroutput on;
 /
Enter value for x: 7
old   2: x number:=&x;
new   2: x number:=7;
Enter value for y: 0
old   3: y number:=&y;
new   3: y number:=0;
declare
*
ERROR at line 1:
ORA-01476: divisor is equal to zero
ORA-06512: at line 5

Error Avoid Program

 declare
  2  x number:=&x;
  3  y number:=&y;
  4  begin
  5  dbms_output.put_line('Results'||x/y);
  6  exception
  7  when Zero_divide then
  8  dbms_output.put_line('Value is divided by zero');
  9  end;
 10  /
Enter value for x: 7
old   2: x number:=&x;
new   2: x number:=7;
Enter value for y: 0
old   3: y number:=&y;
new   3: y number:=0;
Value is divided by zero

PL/SQL procedure successfully completed.

Chapter 17

Procedures and Functions
         PL/ SQL provide sophisticated language constructs to program procedures and functions as stand- alone PL/ SQL blocks. They can be called from other PL/ SQL blocks, other procedures and functions. The syntax for a procedure

create[ or replace] procedure< procedure name>[(< list of parameters>)] is
  < declarations>
 Begin
< sequence of statements>
[ exception< exception handling routines>]
End [< procedure name>];

Example for procedure1:

create or replace procedure disp_ename(eno in number)is mname varchar2(20);
    begin
    select name into mname from x where dno=eno;
    dbms_output.put_line('the name is'||mname);
    end;
    /

Procedure created.

exec disp_ename(29);
the name isarun

PL/SQL procedure successfully completed.




Example for procedure 2:

declare
  2  a varchar2(100):='hello';
  3  b varchar2(100);
  4  procedure red(a in varchar2,b out varchar2)as
  5  begin
  6  b:=a||'+ '||a;
  7  exception
  8  when value_error then
  9  dbms_output.put_line('wrong');
 10  end;
 11  begin
 12  red(a,b);
 13  dbms_output.put_line(a ||'-'||b);
 14  end;
 15  /
hello-hello+hello

PL/SQL procedure successfully completed.


Create new table:
 create table polic(eid number(3),ename varchar2(13),age number(2),dest varchar2(6),
  2  doj date,salary number(6),expe number(2),awards varchar2(12));

Table created.

 desc polic
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EID                                                  NUMBER(3)
 ENAME                                          VARCHAR2(13)
 AGE                                                 NUMBER(2)
 DEST                                               VARCHAR2(6)
 DOJ                                                  DATE
 SALARY                                          NUMBER(6)
 EXPE                                               NUMBER(2)
 AWARDS                                        VARCHAR2(12)

Create a procedure Insert

 create or replace procedure inst is
  2  eid number(3);
  3  ename varchar2(13);
  4  age number(2);
  5  dest varchar2(6);
  6  doj date;
  7  salary number(6);
  8  expe number(2);
  9  awards varchar2(12);
 10  begin
 11  insert into polic values(&eno,'&ename',&age,'&dest','&doj',&salary,&expe,'&awards');
 12  end;
 13  /
Enter value for eno: 1
Enter value for ename: ganash
Enter value for age: 30
Enter value for dest: sp
Enter value for doj: 12-jan-02
Enter value for salary: 8000
Enter value for expe: 6
Enter value for awards: anna
old  11: insert into polic values(&eno,'&ename',&age,'&dest','&doj',&salary,&expe,'&awards');
new  11: insert into polic values(1,'ganash',30,'sp','12-jan-02',8000,6,'anna');

Procedure created.

 /
Enter value for eno: 2
Enter value for ename: veena
Enter value for age: 29
Enter value for dest: pc
Enter value for doj: 22-mar-98
Enter value for salary: 9000
Enter value for expe: 7
Enter value for awards: chiefminster
old  11: insert into polic values(&eno,'&ename',&age,'&dest','&doj',&salary,&expe,'&awards');
new  11: insert into polic values(2,'veena',29,'pc','22-mar-98',9000,7,'chiefminster');

Procedure created.

Create a procedure display

 create or replace procedure disp is
  2  name1 varchar2(15);
  3  ag1 number(2);
  4  dest1 varchar2(6);
  5  doj1 date;
  6  sal1 number(5);
  7  expe1 number(2);
  8  award varchar2(12);
  9  a number(3):=&a;
 10  begin
 11  select ename into name1 from polic where eid=a;
 12  select age into ag1 from polic where eid=a;
 13  select dest into dest1  from polic where eid=a;
 14  select doj into doj1  from polic where eid=a;
 15  select salary into sal1  from polic where eid=a;
 16  select expe into expe1  from polic where eid=a;
 17  select awards into award  from polic where eid=a;
 18  dbms_output.put_line('the name is:'||name1);
 19   dbms_output.put_line('the age is:'||ag1);
 20   dbms_output.put_line('the dest is:'||dest1);
 21   dbms_output.put_line('the doj is:'||doj1);
 22   dbms_output.put_line('the salary is:'||sal1);
 23   dbms_output.put_line('the expe is:'||expe1);
 24   dbms_output.put_line('the awards is:'||award);
 25  end;
 26  /
Enter value for a: 1
old   9: a number(3):=&a;
new   9: a number(3):=1;

Procedure created.


A function can be specified in an

create[ or replace] function< function name>[(< list of parameters>)]
 return< data type> is

 declare
  2  function square(a number)
  3  return number as b number;
  4  begin
  5  b:=a*a;
  6  return b;
  7  end;
  8  begin
  9  dbms_output.put_line(square(10));
 10  end;
 11  /
100

PL/SQL procedure successfully completed.

A procedure can be deleted
Syntax :

drop procedure< procedure name>
( drop function< function name>).

Package

A package is a database object that groups logically related PL/SQL types, objects, and subprograms.
Packages usually have two parts,
  1. A specification
  2. A body

 Although sometimes the body is unnecessary. The specification is the interface to your applications; it declares the types, variables, constants, exceptions, cursors, and subprograms available for use.
The bodyfully defines cursors and subprograms, and so implements the specification.
Syntax:
CREATE PACKAGE name AS  -- specification (visible part)
   -- public type and object declarations
   -- subprogram specifications
END [name];

CREATE PACKAGE BODY name AS  -- body (hidden part)
   -- private type and object declarations
   -- subprogram bodies
[BEGIN
   -- initialization statements]
END [name];

To create packages and store them permanentlyin an Oracle database, you use the CREATE PACKAGE and CREATE PACKAGE BODY statements, which you can execute interactively from SQL*Plus or Server Manager.
Advantages of Packages
Packages offer several advantages: modularity, easier application design, information hiding, added functionality, and better performance.





CREATE PACKAGE app1 as
  2   TYPE EmpRecTyp IS RECORD (emp_id INTEGER, salary REAL);
  3   CURSOR desc_salary RETURN EmpRecTyp;
  4   PROCEDURE hire_employee (empno number,
  5      ename  VARCHAR2,
  6      job    VARCHAR2,
  7      mgr    NUMBER,
  8      sal    NUMBER,
  9      comm   NUMBER,
 10      deptno NUMBER);
 11   end app1;
 12  /

Package created.

create or replace package body app1 as
  2      CURSOR desc_salary RETURN EmpRecTyp IS
  3         SELECT empno, sal FROM emp ORDER BY sal DESC;
  4      PROCEDURE hire_employee (empno number,
  5       ename  VARCHAR2,
  6       job    VARCHAR2,
  7       mgr    NUMBER,
  8       sal    NUMBER,
  9       comm   NUMBER,
 10       deptno NUMBER) IS
 11    BEGIN
 12       INSERT INTO emp VALUES (empno,ename, job,
 13            mgr, SYSDATE, sal, comm, deptno);
 14      END hire_employee;
 15   end app1;
 16  /

Package body created.

Package is executed

 EXECUTE app1.hire_employee(121,'malar','clerk',870,2000,67,10);

PL/SQL procedure successfully completed.

 select * from emp;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM
---------- ---------- --------- ---------- --------- ---------- ----------
    DEPTNO
----------
      7369 SMITH      CLERK           7902 17-DEC-80        800
        20

      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300
        30

      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500
        30


     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM
---------- ---------- --------- ---------- --------- ---------- ----------
    DEPTNO
----------
      7566 JONES      MANAGER         7839 02-APR-81       2975
        20

      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400
        30

      7698 BLAKE      MANAGER         7839 01-MAY-81       2850
        30


     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM
---------- ---------- --------- ---------- --------- ---------- ----------
    DEPTNO
----------
      7782 CLARK      MANAGER         7839 09-JUN-81       2450
        10

      7788 SCOTT      ANALYST         7566 19-APR-87       3000
        20

      7839 KING       PRESIDENT            17-NOV-81       5000
        10


     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM
---------- ---------- --------- ---------- --------- ---------- ----------
    DEPTNO
----------
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0
        30

      7876 ADAMS      CLERK           7788 23-MAY-87       1100
        20

      7900 JAMES      CLERK           7698 03-DEC-81        950
        30


     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM
---------- ---------- --------- ---------- --------- ---------- ----------
    DEPTNO
----------
      7902 FORD       ANALYST         7566 03-DEC-81       3000
        20

      7934 MILLER     CLERK           7782 23-JAN-82       1300
        10

       121 malar      clerk            870 09-AUG-07       2000         67
        10


15 rows selected.

Delete a package

drop package app1;

Package dropped.



Trigger

A database trigger is stored PL/SQL block. That is associated with a table .triggers are automatically executed when a specified SQL statement is issued against a table.

Triggers are mainly following purpose:
  1. To automatically generate a values.
  2. To provide auditing.



Types of triggers

 Before or After



select * from z;

no rows selected

SQL> desc z;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 AGE                                                NUMBER(3)
 DNO                                                NUMBER(4)

 insert into z values(29,75);

1 row created.

 create or replace trigger red before insert on z
  2  begin
  3  dbms_output.put_line('inserting record');
  4  end;
  5  /

Trigger created.

insert into z values(52,90);

inserting record

1 row created.



Check Constraints

Often columns in a table must have values that are within a certain range or that satisfy certain conditions.
 The syntax for a check constraint is

[constraint< name>]check(< condition>)

If a check constraint is specified as a column constraint, the condition can only refer that column.

EXAMPLE 1: you enter name field values only uppercase letters

SQL> create table samp(ename varchar2(16)constraint check_name check(ename=upper(ename)));

Table created.

// input is wrong
 insert into samp values('&ename');
Enter value for ename: jaya
old   1: insert into samp values('&ename')
new   1: insert into samp values('jaya')
insert into samp values('jaya')
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.CHECK_NAME) violated

// input is correct

 insert into samp values('ANAND');

1 row created.


EXAMPLE 2:
you enter sal field values  minimum 500 .
you enter deptno  field values  between 10 to 100 .


 create table samp1(sal number(5,2)constraint check_sal check(sal>=500),deptno number(3)
  2  constraint check_deptno check(deptno between 10 and 100));

Table created.

number( o, d): Numeric data type for integers and reals.
 o = overall number of digits,
 d = number of digits to the right of the decimal point.

Examples: number( 8), number( 5,2)
 Note that,e. g.,number( 5,2) cannot contain anything larger than 999.99 without resulting in an error. Data types derived from number are int[ eger], dec[ imal], smallint and real.

//Error
 insert into samp1 values(4000,60);
insert into samp1 values(4000,60)
                         *
ERROR at line 1:
ORA-01438: value larger than specified precision allows for this column

//Error
SQL> insert into samp1 values(400,150);
insert into samp1 values(400,150)
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.CHECK_DEPTNO) violated

Correct statements

 insert into samp1 values(600,60);

1 row created.

Foreign Key Constraints
A foreign key constraint( or referential integrity constraint) can be specified as a column constraint or as a table constraint:
[ constraint< name>][ foreign key(<column( s)>)]
references<table>[(<column( s)>)]
[on delete cascade]

 This constraint specifies a column or a list of columns as a foreign key of the referencing table. The referencing table is called the child- table, and the referenced table is called the parent- table.

  create table emp1(EMPNO number(4)constraint pk_emp1 primary key,ename varchar2(10),
  2   deptno number(3));

Table created.

 desc emp1;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMPNO                                     NOT NULL NUMBER(4)
 ENAME                                              VARCHAR2(10)
 DEPTNO                                             NUMBER(3)

 /
Enter value for empno: 101
Enter value for ename: sandyu
Enter value for deptno: 20
old   1: insert into emp1 values(&empno,'&ename',&deptno)
new   1: insert into emp1 values(101,'sandyu',20)

1 row created.
/
.
.
 /         /// Error data because using primary key. Avoid duplicate values.
Enter value for empno: 101
Enter value for ename: feena
Enter value for deptno: 50
old   1: insert into emp1 values(&empno,'&ename',&deptno)
new   1: insert into emp1 values(101,'feena',50)
insert into emp1 values(101,'feena',50)
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.PK_EMP1) violated

SQL> select * from emp1;

     EMPNO ENAME DEPTNO
     ---------- ----------  ----------
       100       kalai              10
       101       sandyu                20
       102       mani              30
       103       heena             40
 Oracle

No comments: