Wikibooks: Oracle Database/PL/SQL

• Format: Book
• Language: English
• Subjects: DML
• Online Access: https://en.wikibooks.org/wiki/Oracle_Database/PL/SQL

=PL/SQL= =Introduction= PL/SQL stands for Procedural Language extension of SQL. It is a combination of SQL along with the procedural features of programming languages and it enhances the capabilities of SQL by injecting the procedural functionality like conditional or looping statements into the set oriented SQL structure. =Advantages of PL/SQL= Procedural Language Capability PL/SQL consists of procedural language constructs such as conditional statements (if else statements) and loops like (FOR loops). Block Structures PL/SQL consists of blocks of code which can be nested within each other. Each block forms a unit of a task or a logical module. PL/SQL Blocks can be stored in the database and reused. Better Performance PL/SQL engine processes multiple SQL statements simultaneously as a single block thereby reducing network traffic. Exception Handling PL/SQL handles exceptions (or errors) effectively during the execution of a PL/SQL program. Once an exception is caught specific actions can be taken depending upon the type of the exception or it can be displayed to the user with a message. =Limitation= PL/SQL can only use SELECT DML(INSERT UPDATE DELETE) and TC(COMMIT ROLLBACK SAVEPOINT) statements DDL (CREATE ALTER DROP) and DCL(GRANT REVOKE) cannot be used directly. Any DDL/DCL however can be executed from PL/SQL when embedded in an EXECUTE IMMEDIATE statement. =Basic Structure= Each PL/SQL program consists of SQL and PL/SQL statements which form a PL/SQL block. A PL/SQL Block consists of three sections Declaration Section This section is optional and it starts with the reserved keyword DECLARE. This section is used to declare any placeholders like variables constants records and cursors which are used to manipulate data in the execution section. Execution Section This section is mandatory and it starts with the reserved keyword BEGIN and ends with END. This section is where the program logic is written to perform any task. The programmatic constructs like loops conditional statement and SQL statements form the ...