Skip to main content

PL/SQL introduction

  • 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.

PL/SQL


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
  • Portability
    –Exception handling

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


Anonymous


[DECLARE]
BEGIN
   --statements
[EXCEPTION]
END;

Procedure

PROCEDURE name
IS
BEGIN
  --statements
[EXCEPTION]
END;

Function

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;

Output:

 

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.