- Stands for “Procedural Language extension to SQL”
- The PL/SQL programming language was developed by Oracle Corporation in the late 1980s as procedural extension language for SQL and the Oracle relational database.
- Seamlessly integrates procedural constructs with 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
- It offers extensive error checking.
- It offers numerous data types.
- It supports the development of web applications and server pages.
- Improved performance
- Modularized program development
- Integration with Oracle tools
- Applications written in PL/SQL are fully portable.
- It supports object-oriented programming.
Block Structure of PL/SQL
[pastacode lang=”sql” manual=”%5BDECLARE%5D%0A%20%20%20Declaration%20statements%3B%0ABEGIN%0A%20%20%20Execution%20statements%3B%0A%5BEXCEPTION%5D%0A%20%20%20%20%20%20Exception%20handling%20statements%3B%0AEND%3B%0A%2F” message=”PL/SQL Block structure” highlight=”” provider=”manual”/]
Types of Block in PL/SQL
Create an Anonymous Block of PL/SQL
Now you will see how to create an Anonymous block in PL/SQL and how to enable and display the output in PL/SQL.
[pastacode lang=”sql” manual=”DECLARE%0A%20%20%20%20v_fname%20VARCHAR2(20)%3B%0ABEGIN%0A%20%20%20%20SELECT%20first_name%0A%20%20%20%20INTO%20v_name%0A%20%20%20%20FROM%20employees%0A%20%20%20%20WHERE%20employee_Id%20%3D%20100%3B%0AEND%3B” message=”An Anonymous Block of PL/SQL” highlight=”” provider=”manual”/]
Executing the Anonymous block in SQL Developer
Test the Output of PL/SQL Block
Enable output in SQL Developer by clicking the Enable DBMS Output button on the DBMS Output tab:
DBMS_OUTPUT.PUT_LINE to show the output:
DBMS_OUTPUT.PUT_LINE(' The First Name of the Employee is '|| f_name);
Below is the screen shot:
Subscribe to get more about PL/SQL Tutorial and Share if You like.