Scribd
Upload
2 views2 pages

PL/SQL Overview and Key Concepts

PL/SQL is a procedural language that offers advantages such as performance improvement, reusability, and modularity. It is divided into anonymous blocks, which do not store in the database and execute once, and named blocks, which are stored and can be reused. The structure of PL/SQL includes optional declaration, mandatory execution, and exception handling sections, along with limitations like fetching only one record at a time and the use of different variable types.

Uploaded by

koushikkotireddy
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
2 views2 pages

PL/SQL Overview and Key Concepts

PL/SQL is a procedural language that offers advantages such as performance improvement, reusability, and modularity. It is divided into anonymous blocks, which do not store in the database and execute once, and named blocks, which are stored and can be reused. The structure of PL/SQL includes optional declaration, mandatory execution, and exception handling sections, along with limitations like fetching only one record at a time and the use of different variable types.

Uploaded by

koushikkotireddy
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd

PLSQL

-------

PROCEDURAL LANGUAGE -- WHAT TO DO & HOW TO DO

ADVANTAGES
-----------
a) PERFORMANCE IMPROVEMENT
b) REUSABILITY
c) RUN TIME ERRORS
d) MODULARITY

PLSQL IS DIVIDED INTO 2TYPES


______________________________

1)ANONYMOUS BLOCKS/NAMELESS BLOCK

--DOESN'T STORE IN DB
--ONLY ONE TIME EXECUTION TAKES PLACE
--BOTH COMPILATION & EXECUTION TAKES PLACE AT A TIME

2)NAMED BLOCKS -- a)PROCEDURE b)FUNCTIONS c) PACKAGES d)TYPE e) TRIGGERS

--STORED IN DB
--HAVE A NAME
-- REUSABILITY IS MORE
-- COMPILATION IS DONE FIRST THEN EXECUTION WILL BE TAKE PLACE LATER

PLSQL BLOCK/STRUCTURE OF PLSQL:


-------------------------------

SET SERVEROUTPUT ON;

DECLARE(OPTIONAL) FOR DECLARING VARIABLES,CURSORS,COLLECTIONS,USER


DEFINED EXCEPTIONS,PRAGMA

BEGIN(MAIN EXECUTION BLOCK) SQL STATEMENTS(DML,TCL,DQL) + PROCEDURAL STATEMENTS +


(MANDATORY BLOCK) CONTROL STATMENT,CONDITION

EXCEPTION(OPTIONAL) SQL STATEMENTS(DML,TCL,DQL) + PROCEDURAL STATEMENTS

END;(MANDATORY)
/

LIMITATIONS OF PLSQL
---------------------

1)A SELECT STATEMENT CAN FETCH ONLY ONE RECORD/ROW/COLUMN VALUE

a)TOO_MANY_ROWS EXCEPTION WILL BE RAISED WHEN MULTIPLE RECORDS FETCHING TAKES


PLACE
b)CURSORS WILL HELP TO FETCH MULTIPLE ROWS FROM THE TABLE

2)VARIABLES HEPLFUL IN STORING THE DATA COMING FROM SQL ENGINE

a)SCALAR -- which can hold only single data/value


b)RECORD -- WHICH CAN HAVE MULTIPLE DATATYPES(COLLECTIONS)
3) DYNAMIC DATATYPE ASSIGNED TO VARIABLES

a)%TYPE -- CAN HOLD DATATYPE & ITS SIZE DYNAMICALLY FOR SINGLE COLUMN VALUE

b)%ROWTYPE -- CAN HOLD ENTIRE STRUCTURE OF THE TABLE FOR SINGLE ROW

You might also like