What is PL/SQL & Tutorial? Learning Path – Be Productive with [Oracle]

Last updated on 09th Jul 2020, Blog, Tutorials

• PL/SQL tutorial provides basic and advanced concepts of SQL. Our PL/SQL tutorial is designed for beginners and professionals.
• PL/SQL is a block structured language that can have multiple blocks in it.
• Our PL/SQL tutorial includes all topics of PL/SQL language such as conditional statements, loops, arrays, string, exceptions, collections, records, triggers, functions, procedures, cursors etc. There are also given PL/SQL interview questions and quizzes to help you better understand the PL/SQL language.
• SQL stands for Structured Query Language i.e. used to perform operations on the records stored in databases such as inserting records, updating records, deleting records, creating, modifying and dropping tables, views etc.

What is PL/SQL?

• PL/SQL is a block structured language. The programs of PL/SQL are logical blocks that can contain any number of nested sub-blocks. Pl/SQL stands for “Procedural Language extension of SQL” that is used in Oracle. PL/SQL is integrated with the Oracle database (since version 7).
• The functionalities of PL/SQL are usually extended after each release of the Oracle database. Although PL/SQL is closely integrated with SQL language, yet it adds some programming constraints that are not available in SQL.

PL/SQL Functionalities

• PL/SQL includes procedural language elements like conditions and loops. It allows declaration of constants and variables, procedures and functions, types and variables of those types and triggers. It can support Array and handle exceptions (runtime errors).
• After the implementation of version 8 of Oracle database have included features associated with object orientation. You can create PL/SQL units like procedures, functions, packages, types and triggers, etc. which are stored in the database for reuse by applications.
• With PL/SQL, you can use SQL statements to manipulate Oracle data and flow of control statements to process the data.
• The PL/SQL is known for its combination of data manipulating power of SQL with data processing power of procedural languages. It inherits the robustness, security, and portability of the Oracle Database.
• PL/SQL is not case sensitive so you are free to use lower case letters or upper case letters except within string and character literals. A line of PL/SQL text contains groups of characters known as lexical units. It can be classified as follows:

• Delimiters
• Identifiers
• Literals
• Comments

Section 1. Getting started with PL/SQL

• What is PL/SQL –  introduce you to PL/SQL programming language and its architecture.
• Anonymous Block – explain PL/SQL anonymous blocks and shows you how to execute an anonymous block in SQL*Plus and Oracle SQL Developer tools.
• Data Types – give you a brief overview of PL/SQL data types including number, Boolean, character, and datetime.
• Variables – introduce you to PL/SQL variables and show you how to manipulate variables in programs efficiently.
• Comments – use single-line or multi-line comments to document your code to make it more readable and maintainable.
• Constants – learn how to declare constants that hold values that remain unchanged throughout the execution of the program.

Section 2. Conditional control

• IF statements – introduce you various IF statements to either execute or skip a sequence of statements based on a condition.
• CASE statements – learn how to choose one sequence of statements out of many possible sequences to execute.
• GOTO – explain the GOTO statement and show how to use it to transfer control to a labeled block or statement.
• NULL statement – show you how to use the NULL statement to make the code more clear.

Section 3. Iterative processing with loops

• Basic LOOP statement – show you how to use the basic LOOP statement to execute a sequence of code multiple times.
• Numeric FOR LOOP statement – learn how to execute a sequence of statements a fixed number of times.
• WHILE loop – execute a sequence of statements as long as a specified condition is TRUE.
• CONTINUE – use the CONTINUE statement to skip the current iteration of the loop and immediately continue the next iteration.

Section 4.  Select Into

• SELECT INTO – learn how to fetch a single row from a table into variables.

Section 5. Exception handlers

• Exception –  show you how to handle exceptions in a block.
• Raise exceptions – learn how to raise an exception explicitly with the RAISE statement.
• Using raise_application_error – raise an exception with a user-defined error message.
• Exception propagation – learn about how PL/SQL propagates an unhandled exception from the current block to its enclosing block.
• Handling other unhandled exceptions – show you how to use the SQLCODE and SQLERRM functions to handle other unhandled exceptions.

Section 6. Records

• Record – learn how to use record type to make your code more efficiently by shifting operations from field-level to record-level.

Section 7. Cursors

• Cursor – introduces you to cursors including implicit and explicit cursors and shows you how to use them effectively to fetch data from a table.
• Cursor FOR LOOP – show you how to use the cursor FOR LOOP statement to fetch and process each row from a cursor.
• Cursor with parameters – learn how to use the cursor with parameters to fetch data according to the input arguments passed to the cursor each time it is opened.
• Cursor Variables with REF CURSOR – guide you on how to use the cursor variable with ref cursor.
• Updatable cursor – introduce you to the Oracle updatable cursor to update data in the table.

Section 8. Stored procedures and Functions

• Procedure – a step-by-step guide to create, compile and execute a procedure from Oracle SQL Developer.
• Implicit statement results  – learn how to return one or more result sets from a procedure.
• Function – show you how to develop a PL/SQL function and explain to you various ways to call a function.
• Cursor variables – learn how to use cursor variables using REF CURSOR type.

Section 9. Packages

• Introduction to the PL/SQL package – introduce you to PL/SQL packages and explain to you the advantages of using them in your application development.
• Package specification – show you step by step how to create a package specification.
• Package body – learn how to create a package body.
• Drop Package – illustrate how to drop a PL/SQL package.

Section 10. Triggers

• Triggers – introduce you to the Oracle trigger and show you how to create a new trigger.
• Statement-level triggers – learn how to use statement-level triggers to enforce additional security to the transaction.
• Row-level triggers – show you how to use row-level triggers for data-related activities.
• INSTEAD OF triggers – learn how to use the INSTEAD OF triggers to update tables via their views which cannot be modified directly through DML statements.
• Disable triggers – show you to disable a trigger or all triggers of a table.
• Enable triggers – describe steps to enable a trigger or all triggers of a table.
• Drop Triggers – guide you on how to drop a trigger from the database.
• How to fix the mutating table error – learn about the mutating table error and how to fix it using a compound trigger.

Section 11. PL/SQL Collections

• Associative Arrays – introduce you to associative arrays including declaring associative arrays, populating values, and iterating over array elements.
• Nested Tables – learn about nested tables.
• VARRAY – learn about variable-sized arrays and how to manipulate its elements effectively.

This section is targeted as a good starting point for those who are new to PL/SQL. However, if you are very familiar with the language and also want to glance through these tutorials as a refresher, you may even find something useful that you haven’t seen before.

• PL/SQL Block Structure – introduces you to PL/SQL block structure and shows you how to develop the first running PL/SQL program.
• PL/SQL Variables – shows you how to work with PL/SQL variables including declaring, naming and assigning variables.
• PL/SQL Function – explains what PL/SQL functions are and shows you how to create PL/SQL functions.
• PL/SQL Procedure – discusses PL/SQL procedures and shows you how to create PL/SQL procedures.
• PL/SQL Nested Block – explains what a PL/SQL nested block is and how to apply it in PL/SQL programming.
• PL/SQL IF Statement – introduces you to various forms of the PL/SQL IF statement including IF-THEN, IF-THEN-ELSE and IF-THEN-ELSIF statement.
• PL/SQL CASE Statement – shows you how to use PL/SQL CASE statement and PL/SQL searched CASE statement.
• PL/SQL LOOP Statement – guides you how to use PL/SQL LOOP statements to execute a block of code repeatedly.
• PL/SQL WHILE Loop Statement – executes a sequence of statements with a condition that is checked at the beginning of each iteration with the  WHILE loop statement.
• PL/SQL FOR Loop Statement – shows you how to execute a sequence of statements in the fixed number of times with FOR loop statement.
• PL/SQL Exception Handling – teaches you how to handle exceptions properly in PL/SQL as well as shows you how to define your own exception and raise it in your code.
• PL/SQL Record – explains the PL/SQL record and shows you how to use records to manage your data more effectively.
• PL/SQL Cursor – covers PL/SQL cursor concept and walks you through how to use a cursor to loop through a set of rows and process each row individually.
• PL/SQL Packages – shows you how to create a PL/SQL package that is a group of related functions, procedures, types, etc.

PL/SQL ProcedureThe PL/SQL stored procedure or simply a procedure is a PL/SQL block which performs one or more specific tasks. It is just like procedures in other programming languages.The procedure contains a header and a body.Header: The header contains the name of the procedure and the parameters or variables passed to the procedure.Body: The body contains a declaration section, execution section and exception section similar to a general PL/SQL block.How to pass parameters in procedure:When you want to create a procedure or function, you have to define parameters .There is three ways to pass parameters in procedure:IN parameters: The IN parameter can be referenced by the procedure or function. The value of the parameter cannot be overwritten by the procedure or the function.OUT parameters: The OUT parameter cannot be referenced by the procedure or function, but the value of the parameter can be overwritten by the procedure or function.INOUT parameters: The INOUT parameter can be referenced by the procedure or function and the value of the parameter can be overwritten by the procedure or function.PL/SQL Create ProcedureSyntax for creating procedure:CREATE [OR REPLACE] PROCEDURE procedure_name      [ (parameter [,parameter]) ]  IS      [declaration_section]  BEGIN      executable_section  [EXCEPTION      exception_section]  END [procedure_name];  Create procedure exampleIn this example, we are going to insert record in user table. So you need to create user table first.Table creation:create table user(id number(10) primary key,name varchar2(100));  Now write the procedure code to insert record in user table.Procedure Code:create or replace procedure “INSERTUSER”    (id IN NUMBER,    name IN VARCHAR2)    is    begin    insert into user values(id,name);    end;      /       Output:Procedure created. PL/SQL program to call procedureLet’s see the code to call above created procedure.BEGIN       insertuser(101,’Rahul’);     dbms_output.put_line(‘record inserted successfully’);    END;    /    Now, see the “USER” table, you will see one record is inserted.IDName101RahulPL/SQL Drop ProcedureSyntax for drop procedureDROP PROCEDURE procedure_name;   Example of drop procedureDROP PROCEDURE pro1;

PL/SQL Advantages

PL/SQL offers the following advantages:

• Reduces network traffic This one is great advantages of PL/SQL. Because PL/SQL nature is entire block of SQL statements execute into oracle engine all at once so it’s main benefit is reducing the network traffic.
• Procedural language support PL/SQL is a development tools not only for data manipulation futures but also provide the conditional checking, looping or branching operations same as like other programming language.
• Error handling PL/SQL is dealing with error handling, It’s permits the smart way handling the errors and giving user friendly error messages, when the errors are encountered.
• Declare variable PL/SQL gives you control to declare variables and access them within the block. The declared variables can be used at the time of query processing.
• Intermediate Calculation Calculations in PL/SQL done quickly and efficiently without using Oracle engines. This improves the transaction performance.
• Portable application Applications are written in PL/SQL are portable in any Operating system. PL/SQL applications are independence program to run any computer.

PL/SQL Block Structure

What is PL/SQL block?

• PL/SQL block structure divided into three logical blocks. First, BEGIN block and END; keywords are compulsory. However, the other two blocks DECLARE and EXCEPTION are optional block. Technically, END; is not a block, it is only keyword to end of PL/SQL program.
• PL/SQL code is not executed in single line format like SQL. It is always executed by a grouping of code into a single segment called blocks.
• PL/SQL block structure follows the divide-and-conquer approach to solve the problem stepwise.

PL/SQL block Structure

PL/SQL Block Structure

DECLARE

Variables and constants are declared, initialized within this section.

Variables and Constants: In this block, declare and initialize variables (and constants). You must have to declare variables and constants in the declarative block before referencing them in a procedural statement.

Declare Variables and Assigning values: You can define a variable name, data type of a variable, and its size. Date type can be CHAR, VARCHAR2, DATE, NUMBER, INT, or any other.

Declare Constants and Assigning values: Constants are declared the same as a variable, but you have to add the CONSTANT keyword before defining the data type. Once you define, a constant value, you can’t change the value.

designation CONSTANT VARCHAR2(30) := ‘Web Developer’;

BEGIN

• BEGIN block is a procedural statement block which will implement the actual programming logic. This section contains conditional statements (if…else), looping statements (for, while) and Branching Statements (goto), etc.

EXCEPTION

• PL/SQL easily detects a user-defined or predefined error condition. PL/SQL is famous for smartly handling errors by giving suitable user-friendly messages. Errors can be rise due to the wrong syntax, bad logical, or not passing validation rules.
• You can also define exception in your declarative block, and later you can execute it by RAISE statement.

DECLARE

    check_exist EXCEPTION; — declare exception type

    …

BEGIN

    ….

    RAISE check_exist;     — raise exception

    ….

EXCEPTION

   WHEN check_exist THEN   — execute raise exception    

   ….. 

END;

/

Note

• BEGIN block, and END; keyword are compulsory of any PL/SQL program.
• Whereas, the DECLARE and EXCEPTION block are optional.