Introduction to PL/SQL tutorial

PL/SQL tutorial

PL/SQL:

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

PL/SQL Environment

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

PL/SQL TUTORIAL : Block Types


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

PL/SQL TUTORIAL

Test the Output of PL/SQL Block

Enable output in SQL Developer by clicking the Enable DBMS Output button on the DBMS Output tab:

Then use 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:

PL/SQL Tutorial: testing the output


Subscribe to get more about PL/SQL Tutorial and Share if You like.

One Reply to “Introduction to PL/SQL tutorial”

Leave a Reply