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.
- Check Constraints (CC)
- Primary Key Constraints (PK)
- Foreign Key Constraints (FK)
- Unique Key Constraints (UK)
- 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:
Query
SELECT *
FROM employee
WHERE ROWNUM <5;
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