Procedure VS Functions

Datatypes in Oracle PL/SQL

A scalar data type can have subtypes. A subtype is a data type that is a subset of another data type, which is its base type. A subtype has the same valid operations as its base type. A data type and its subtypes comprise a data type family.

PL/SQL predefines many types and subtypes in the package STANDARD and lets you define your own subtypes.

The PL/SQL scalar data types are:

•          The SQL data types
•          BOOLEAN
•          PLS_INTEGER
•          BINARY_INTEGER
•          REF CURSOR
•          User-defined subtypes

BOOLEAN:
                We cannot pass a Boolean value to DBMS_OUTPUT.PUT or DBMS_OUTPUT.PUT_LINE functions. To print Boolean value, we need to use CASE or IF Statement.

PLS_INTEGER & BINARY_INTEGER:
                PLS_INTEGER requires less storage
                PLS_INTEGER uses hardware arithmetic, so they are faster than NUMBER operations, which uses Library Arithmetic
                SIMPLE _INTEGER is the sub type of PLS_INTEGER . Is it the PLS_INTEGER value with NOT NULL constraint
                BINARY_INTEGER is the subtype of NUMBER, which uses Library Arithmetic
                Predefined PLS_INTEGER Subtypes are

•          NATURAL
•          NATURALN
•          POSITIVE
•          POSITIVEN
•          SIMPLE_INTEGER

Exception Handling

Predefined Exceptions :

•          NO_DATA_FOUND
•          CASE_NOT_FOUND
•          COLLECTION_IS_NULL
•          INVALID_CURSOR
•          LOGIN_DENIED
•          DUP_VAL_ON_INDEX
•          ROWTYPE_MISMATCH
•          TOO_MANY_ROWS
•          TIMEOUT_ON_RESOURCE
•          ZERO_DIVIDE

User Defined Exceptions :

Declaration :

                DECLARE
                                EXCEPTION_NAME EXCEPTION;

Scope:

                We cannot declare an exception twice in the same block. However, declare the same exception in different blocks.

Associating a PL/SQL  exception with an error number : PRAGMA EXCEPTION_INIT

                DECLARE
                                EXCEPTION_NAME EXCEPTION;
                                PRAGMA_EXCEPTION (EXCEPTION_NAME,-60);
                BEGIN
                                /* Code that causes ORA-00060 error*/

                EXCEPTION
                                WHEN EXCEPTION_NAME           THEN
                                /*  Handle the error  */

                END  ;                /

Defining Our Own Error Messages : Procedure RAISE_APPLICATION_ERROR

                It lets you define the user defined ORA - error messages from stored programs. In that way we can report errors to our application and avoid returning unhandled exceptions.

                RAISE_APPLICATION_ERROR(error_number,message[,TRUE/FALSE]);
                                where error_number is a negative integer between -20000 and -20999, and message is a character string up to 2048 bytes long.

                RAISE_APPLICATION_ERROR is the part of DBMS_STANDARD package

Index Organized Tables

Typical tables are heap organized. It has a stable physical location. It means, when we insert a new record it will be added to the next row of the table .

Index organized tables builds Table & Index together.
These are good for

• Small tables
• Exact match (Look up )

Create table state_lookup_IOT
(
stateid number,
State varchar(20),
City varchar(20),
primary key (id)
)
ORGANIZATION INDEX ;