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.

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:

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

Oracle SQL Commands: Part 9

Reactions

Comments