Oracle PL/SQL 9i/10g - Introduction

[debasisdas]

This Article contains some useful tips in oracle PL/SQL in 9i/10g which the forum members might find useful

Advantages of PL/SQL
=============== ====

1.Support for SQL
2.Support for object-oriented programming
3.Better performance
4.Higher productivity
5.Full portability
6.Tight integration with Oracle
7.Tight security

Types of Blocks in PL/SQL

1.anonymous Block
2.named Block/stored Procedure

Features of PL/SQL

1.block Structure
2.variable And Constants
3.control Structure
4.exception Handling
5.cursor
6.procedure
7.function
8.package
9.trigger
10.user-defined Types

Basic Structure of PL/SQL Block

DECLARE
<VARIABLE>
<CURSOR>
<EXCEPTION>
...................
...................
BEGIN
<EXECUTABLE STATEMENT>
EXCEPTION
<EXCEPTION HANDLER>
END;
 

PL/SQL--Data Types

1.Scalar--------NUMBER,VARCHAR2 ,DATE,BOOLEAN ...etc.
2.Reference-----REF CURSOR
3.Composite-----RECORDTYPE,NEST ED TABLE,VARRYING ARRAY
4.LOB-----------BLOB,CLOB,NCLOB ,BFILE
5.Anchoring-----%TYPE,%ROWTYPE

The following datatypes are also supported for cross-language compatability.

SMALLINT,NATURA L,NATURALN,POSI TIVE,POSITIVEN,
PLS_INTEGER,DEC ,DECIMAL
BINARY_INTEGER, BINARY_FLOAT,BI NARY_DOUBLE