DBMS Datatypes and Advantages of PL/SQL

Datatypes of PL/SQL

There are various types of datatypes are there

  • Row id Datatypes
  • Character Datatypes
  • Numeric Datatypes
  • Date/Time Datatypes

 

Row id Datatypes:

 
Data type
Syntax 
Oracle 10g
 
Explanation 
 
Rowid  The format of the rowid is:
BBBBBBB.RRRR.FFFFF  
Where BBBBBBB is the block
in the database file;
RRRR is the row in the block;
FFFFF is the database file. 
Fixed-length binary data. Every record in the database has a physical address or rowid. 
Urowid(size)    Universal rowid.  
Where size is optional. 

 

Character Datatypes :

 

Data Type
Syntax 
Oracle 10g Explanation 

char(size) 

Maximum size of 2000 bytes.

Where size is the number of characters to store. 
Fixed-length strings. Space padded. 

nchar(size) 

Maximum size of 2000 bytes.

Where size is the number of characters to store. 
Fixed-length NLS string Space padded. 

nvarchar2(size) 

Maximum size of 4000 bytes.

Where size is the number of characters to store. 
Variable-length NLS string. 

Varchar2(size) 

Maximum size of 4000 bytes.

Where size is the number of characters to store. 
Variable-length string.

Long 

Maximum size of 2GB.

Variable-length strings. (backward compatible)

Raw 

Maximum size of 2000 bytes. 

Variable-length binary strings 

Long Raw 

Maximum size of 2GB.

Variable-length binary strings. (backward 
compatible)

 

Numeric Datatypes :

 

Data Type
Syntax 
Oracle 10g Explanation 
 

number(p,s)

Precision can range from 1 to
38.
Scale can range from -84 to 127.

Where p is the precision and s is the scale.  
For example, number(7,2) is a number that has 5
digits before the decimal and 2 digits after the decimal. 

numeric(p,s)

Precision can range from 1 to 
38. 

Where p is the precision and s is the scale.  
For example, numeric(7,2) is a number that has 5 digits before the decimal and 2 digits after the
decimal. 

dec(p,s)

Precision can range from 1 to 
38. 

Where p is the precision and s is the scale.  
For example, dec(3,1) is a number that has 2 digits
before the decimal and 1 digit after the decimal. 

decimal(p,s)

Precision can range from 1 to 
38.

Where p is the precision and s is the scale.  
For example, decimal(3,1) is a number that has 2 digits before the decimal and 1 digit after the
decimal.

It also contains integer ,float ,int data types to store numeric values .
 

Date/Time Datatypes:

 
Data Type
Syntax 
Oracle 10g Explanation 
 

Date

A date between Jan 1, 4712 
BC and Dec 31, 9999 AD.

 

timestamp
(fractional
seconds
precision) 

Fractional second’s precisionmust be a number between 0
and 9. (default is 6) 

Includes year, month, day, hour, minute, and seconds.  
For example: timestamp(6) 

 

Advantages of PL/SQL

  • PL/SQL is development tool that not only supports SQL data manipulation but also provide facilities of conditional checking, branching and looping.

  • PL/SQL sends an entire block of statements to the Oracle engine at one time. This is turn reduces network traffic. The Oracle engine gets the SQL statements as a single block, and hence it processes this code much faster than if it got the code one sentence at a time. There, is a definite improvement in the performance time of the Oracle engine. As an entire block of code is passed to the DBA at one time for execution, all changes made to the data in the table are done or undone, in one go.

  • PL/SQL also permits dealing with errors as required, and facilitates displaying user-friendly messages, when errors are encountered.

  • PL/SQL allows declaration and use of variables in blocks of code. These variables can be used to store intermediate results of a query for later processing, or calculate values and insert them into an Oracle table later. PL/SQL variables can be used anywhere, either in SQL statements or in PL/SQL blocks.

  • Via PL/SQL, all sorts of calculations can be done quickly and efficiently without the use of the Oracle engine. This considerably improves transaction performance.

  • Applications written in PL/SQL are portable to any computer hardware and operating system, where Oracle is operational. Hence, PL/SQL code blocks written for a DOS version of Oracle wi l l run on its UNIX version, without any modifications at all.

  • Support for SQL

  • Support for object-oriented programming

  • Better performance

  • Higher productivity

  • Full portability

  • Tight integration with Oracle

  • Tight security

 



Share This Page on:


Ask Question