- PL/SQL Stands for “Procedural Language extension to SQL”
- Is Oracle Corporation’s standard data access language for relational databases
- Seamlessly integrates procedural constructs with SQL
- PL/SQL program units are compiled by the Oracle Database server and stored inside the database.
- PL/SQL automatically inherits the robustness, security, and portability of the Oracle Database.
Provides a block structure for executable units of code. Maintenance of code is made easier with such a well-defined structure.
- Provides procedural constructs such as:
- Variables, constants, and data types
- Control structures such as conditional statements and loops
- Reusable program units that are written once and executed many times
Benefits of PL/SQL
- Integration of procedural constructs with SQL
- Improved performance
- Modularized program development
- Integration with Oracle tools
PL/SQL Block Structure
DECLARE (optional) –Variables, cursors, user-defined exceptions BEGIN (mandatory) –SQL statements –PL/SQL statements EXCEPTION (optional) –Actions to perform when errors occur END; (mandatory)
PL/SQL Block Types
[DECLARE] BEGIN --statements [EXCEPTION] END;
PROCEDURE name IS BEGIN --statements [EXCEPTION] END;
FUNCTION name RETURN datatype IS BEGIN --statements RETURN value; [EXCEPTION] END;
Creating Anonymous Block and Executing it
DECLARE v_lname varchar(15); BEGIN SELECT LAST_NAME INTO v_lname from employees where employee_id=110; DBMS_OUTPUT.PUT_LINE('last name: '||v_lname); END;
How to run PL/SQL program
How to enable dbms_output
Development Environment for PL/SQL
- Oracle SQL Developer
- Oracle SQL*Plus
- Oracle JDeveloper IDE/li>
We will use SQL Developer to Write PL/SQL program
Oracle SQL Developer is a free graphical tool that enhances productivity and simplifies database development tasks.