Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
What is PL/SQL?
It is a procedural extension language (PL) for the structured query language (SQL) and the Oracle relational database. It provides a built-in, interpreted and OS independent programming environment.
Apart from Oracle it also available Times Ten in-memory database and IBM DB2.
The Syntax of PL/SQL is based on ADA and Pascal programming languages. PL/SQL is tightly integrated with SQL.
PL/SQL offers the following −
- Extensive error checking.
- Numerous data types.
- Variety of programming structures.
PL/SQL supports the following −
Structured programming through functions.
Procedures and object-oriented programming.
Development of web applications and server pages.
PL/SQL block structure
It defines the type of block (procedure, function, anonymous) and the way it is called, it declares any variables used in the block, it uses variables and other PL/SQL objects to perform actions and handle any problems that arise during execution of the block.
PL/SQL is not case sensitive. It uses the same data-types as SQL like Boolean, record, table, array and LOB. It allows reference data-types %type and %row type.
Comments
- /* and */ for multiline.
- -- for a single line.
Uses
- = is used for assignment.
- ; is used to indicate end of line (instruction).
Example
Given below is an example for the PL/SQL −
DECLARE
current_bldg_code VARCHAR2(5);
CURSOR location_cursor IS
SELECT room, capacity
FROM location
WHERE bldg_code = current_bldg_code
FOR UPDATE of capacity;
location_row location_cursor%rowtype;
BEGIN
current_bldg_code := ‘LIB’;
FOR location_row IN location_cursor LOOP
UPDATE location
Set capacity = capacity +1
WHERE CURRENT OF location_cursor;
END LOOP;
COMMIT;
END;
2K+ Views
