Oracle SQL Commands: Part 9

CLAUSES

1. CONSTRAINT CLAUSE
 
The Constraint clause applies specific rules to the data that restrict the values/data entered into the database columns. Constraints help to minimize the amount of validation done at the application level. and can be specified at the time of creation or after the creation of the table. Constraints can be defined at a specific column level called In Line Specification and also at the table level known as Out Of Line Specification.

In line Constraint Syntax

CONSTRAINT constraint_name {UNIQUE|PRIMARY KEY} constraint _state
CONSTRAINT constraint _name CHECK(condition) constraint _state
CONSTRAINT constraint _name [NOT] NULL constraint _state
CONSTRAINT constraint _name REFERENCES [schema.]table [(column)]
     [ON DELETE {CASCADE|SET NULL}]
constraint _state

Out of line Constraint Syntax

CONSTRAINT constraint _name {UNIQUE|PRIMARY KEY}(column [,column…]) constraint _state
CONSTRAINT constraint _name CHECK(condition) constraint _state
CONSTRAINT constraint _name FOREIGN KEY [schema.]table [(column)]
REFERENCES [schema.]table [(column)][ON DELETE {CASCADE|SET NULL}] constraint_state

In Oracle Constraints are divided into six parts.

  1. Check Constraints (CC)
  2. Primary Key Constraints (PK)
  3. Foreign Key Constraints (FK)
  4. Unique Key Constraints (UK)
  5. Not Null Constraints (NN)

2. DROP CLAUSE
 
DROP Table, DROP View, DROP Index, DROP User, DROP Trigger, DROP Procedure, DROP Schema, DROP Function, DROP Sequence and DROP Java; all of these are explained earlier in the 4th and 5th part of this article.
 
PSEUDOCOLUMNS

1. LEVEL PSEUDOCOLUMN

To organize the rows into a tree structure we use LEVEL PSEUDOCOLUMN with the SELECT CONNECTED BY statement. It returns the level number of nodes in a tree structure.

Example: Assume the following table:

Order_id

Cust_id

Cust_Name

Item_Ordered

City

11

0

AAA

H

Delhi

22

2

BBB

U

Jodhpur

33

1

CCC

R

Patiala

44

2

DDD

F

Jaipur

55

6

PPP

S

Haridwar

66

7

TTT

A

Lucknow

77

1

GGG

L

Agra

88

1

HHH

D

Amritsar

99

7

KKK

Y

Mathura

Query

SELECT LEVEL Order_id,Cust_id, Cust_name, City
FROM Customer
START With Order_id
CONNECT TO PRIOR Order_id = Cust_id
ORDER
BY LEVEL;
 

Level

Order_id

Cust_id

Cust_Name

City

1

11

0

AAA

Delhi

2

33

1

CCC

Patiala

2

77

1

GGG

Agra

2

88

1

HHH

Amritsar

3

22

2

BBB

Jodhpur

3

44

2

DDD

Jaipur

4

66

7

TTT

Lucknow

4

99

7

KKK

Mathura

5

55

6

PPP

Haridwar

2. ROWNUM PSEUDOCOLUMN

ROWNUM PSEUDOCOLUMN returns a number that indicates the order in which a row is selected from an existing database table. It returns the number of returned rows. ROWNUM is evaluated after records are selected from the database and before execution of the ORDER BY clause.

Example: Assume the following table:

Stud_id

Stud_name

Email_id

Age

Stream

001

Rahul

[email protected]

17

Science

002

Varun

[email protected]

19

Arts

003

Vikas

[email protected]

18

Science

004

Preeti

[email protected]

17

Arts

005

Sapna

[email protected]

16

Commerce

006

Rajat

[email protected]

19

Arts

007

Shweta

[email protected]

17

Science

008

Deepak

[email protected]

17

Commerce

009

Sneha

[email protected]

18

Science

Query

SELECT *
FROM employee
WHERE ROWNUM <5;
 

Stud_id

Stud_name

Email_id

Age

Stream

001

Rahul

[email protected]

17

Science

002

Varun

[email protected]

19

Arts

003

Vikas

[email protected]

18

Science

004

Preeti

[email protected]

17

Arts

3. CURRVAL and NEXTVAL PSEUDOCOLUMN

In Oracle, CURRVAL PSEUDOCOLUMN is used with Oracle Sequence values. The current value of the sequence is returned by the CURRVAL.

Query

schema.sequence_name.CURRVAL 

In Oracle, NEXTVAL PSEUDOCOLUMN also can be used with Oracle Sequence values. It returns the next value of the sequence and causes the sequence to be incremented by one. NEXTVAL can only be referenced if it is associated with a Sequence.

Query

schema.sequence_name.NEXTVAL

4. ROWID PSEUDOCOLUMN

In Oracle, ROWID PSEUDOCOLUMN is the binary address of the row that contains the data object numbers. Each row in a table has a unique ROWID that identifies the row in the table.

Syntax

SELECT ROWID,  Col1, Col2, Col3, Col4
FROM table_name;

Now it's the end of this article, Thanks for reading.

Previous article: Oracle SQL Commands: Part 8

Up Next
    Ebook Download
    View all
    Learn
    View all
    F11Research & Development, LLC