SQL Essentials Training & Certification
- 10k Enrolled Learners
- Self Paced
PL/SQL is a procedural language that overcomes the shortcomings faced by Structured Query Language. It is an extension of SQL and we can even use SQL queries without any hassle in any PL/SQL application or program. In this PL/SQL tutorial, we will go through the basic concepts of PL/SQL in detail. The following topics are covered in this article.
It stands for procedural language extension to the structured query language. Oracle created PL/SQL that extends some limitations of SQL to provide a more comprehensive solution for building mission-critical applications running on the oracle database.
PL/SQL provides the functionality of a procedural language such as decision making, iteration, etc.
Using a single command, PL/SQL can execute a number of queries.
We can also reuse PL/SQL units such as functions, triggers, procedures, etc that are stored in the database after the creation.
PL/SQL also has an exception handling block that handles the exceptions in PL/SQL.
Extensive error checking is also possible using PL/SQL
|SQL is a single query that is used to perform DDL and DML operations||PL/SQL is a block of codes that is used to define an entire program or procedure/function, etc|
|It does not really define how things need to be done, rather defines what needs to be done||PL/SQL defines how things need to be done|
|It executes a single statement||It executes a block of statements at once.|
|SQL is mainly used to manipulate the data||PL/SQL, on the other hand, is used to create applications|
|It cannot contain PL/SQL code||Since it is a SQL extension, it can contain SQL code in it|
PL/SQL typically organizes the code into blocks. The code block with no name is known as an anonymous block. It is known as the anonymous block because it is not saved in the oracle database. Let us take a look at an anonymous block in PL/SQL.
[DECLARE] declaration statements; [BEGIN] execution statements; [EXCEPTION] exception statements; END; /
Looking at the diagram shown above, we can see that the block structure is divided into four parts, i.e declaration, begin, exception and end. Let us try to understand how the block structure works in PL/SQL. Out of all these sections, the execution section is mandatory and rest all are optional.
DECLARE keyword is used to for the declaration section is used to declare data types and structures such as variables, functions, etc.
BEGIN keyword is used for the execution section. It is mandatory and contains all the statements that need to be executed. This block is where the business logic is defined, we can use both procedural or SQL statements in this block.
The EXCEPTION keyword is used for the exception section. It contains all the exception statements.
END keyword marks the end of the block and the backward slash ‘/’ tells the tool that you are using(Oracle Database Tool) to execute the PL/SQL block.
Here is a simple example to show how we can use the PL/SQL code.
BEGIN NULL; END; /
The variable in PL/SQL is basically a name that varies or temporary storage location that supports a particular data type. Let us take a look at how we can use the variables in a PL/SQL program.
PL/SQL follows the following rules for naming variables.
The variable cannot be more than 31 characters
The name of the variable should start with an ASCII character. Since PL/SQL is case-sensitive, an uppercase letter and a lowercase letter will be different variables.
After the first character, there has to be a special character($,_ ) or any number.
Use the following naming conventions listed below to use the variables.
Let’s try to understand how variable declaration is done in PL/SQL
The declaration includes the variable name followed by the data type and separated by a semicolon. Following is an example to show how you can declare a variable in PL/SQL.
DECLARE v_name VARCHAR(25); n_age NUMBER(3); BEGIN NULL; END;
You can also add the length of the data type as we have done in the example above.
The anchor basically refers to the use of the %TYPE keyword that to declare a variable with the data type associated with a column’s data type of a particular column in a table.
Take a look at an example to understand this. Suppose we have a table EMPLOYEES, we can use the anchors in the following way.
DECLARE v_name EMPLOYEE.NAME%TYPE; n_age EMPLOYEE.AGE%TYPE; BEGIN NULL; END; /
Variable assignment is quite easy, we can use the assignment operator to assign values to a variable. The following example shows how we can assign values to a variable.
DECLARE v_name VARCHAR(20); n_course VARCHAR(10); BEGIN v_name = "edureka"; v_course = "sql"; END; /
We can initialize a value for the variable in the declaration section too. The following example shows how we can initialize values to a variable.
DECLARE v_name VARCHAR(20) = "edureka"; n_course VARCHAR(10) = "sql"; BEGIN NULL; END; /
A function in PL/SQL is basically a named block that returns a value. It is also known as a subroutine or a subprogram, the following syntax shows how we can use functions in PL/SQL.
CREATE [OR REPLACE] FUNCTION function_name [( parameter_1 [IN] [OUT] data_type, parameter_2 [IN] [OUT] data_type, parameter_N [IN] [OUT] data_type] RETURN return_data_type IS BEGIN statements return return_data_type; EXCEPTION END; /
First of all, you must specify a function name after the keyword. The function name has to start with a verb. A function may have none, one or more parameters that we specify in parameters. We have to specify the data type of each parameter explicitly, and then comes the mode which can either of the following.
IN – The IN parameter is a read-only parameter.
OUT – It is a write-only parameter
IN OUT – The IN OUT parameter is both read-write parameter.
Here is a simple example to show how we use functions in PL/SQL.
CREATE OR REPLACE FUNCTION try_parse( iv_number IN VARCHAR2) RETURN NUMBER IS BEGIN RETURN to_number(iv_number); EXCEPTION WHEN others THEN RETURN NULL; END;
Calling A Function
Let us try to call the function that we have made in an anonymous block in the following example.
SET SERVEROUTPUT ON SIZE 1000000; DECLARE n_x number; n_y number; n_z number; BEGIN n_x := try_parse('256'); n_y := try_parse('29.72'); n_z := try_parse('pqrs'); DBMS_OUTPUT.PUT_LINE(n_x); DBMS_OUTPUT.PUT_LINE(n_y); DBMS_OUTPUT.PUT_LINE(n_z); END; /
A procedure is basically a block that does a specific task. Using a procedure we can wrap or encapsulate complex business logic and reuse them in both application and database layer.
Let us take a look at a simple example to understand how the procedure works in PL/SQL
CREATE OR REPLACE PROCEDURE adjust_salary( in_employee_id IN EMPLOYEES.EMPLOYEE_ID%TYPE, in_percent IN NUMBER ) IS BEGIN -- update employee's salary UPDATE employees SET salary = salary + salary * in_percent / 100 WHERE employee_id = in_employee_id; END;
In the above example, we have two parameters, the procedure adjusts the salary by a given percentage and the UPDATE keyword updates the value in the salary information.
The section before the keyword IS is called the procedure header. The following are a few pointers one must be familiar with while working with procedures.
schema – It is the optional name of the schema that the procedure belongs to.
name – The name of the procedure which should start with a verb.
parameters – It is the optional list of parameters.
AUTHID – It determines whether the procedure will execute with the privilege of the current user or the original owner of the procedure.
Everything that comes after the IS keyword is called the procedure body. We have the declaration, exception and execution statements in the procedure body. Unlike the function, the RETURN keyword in a procedure is used to halt the execution and return the control to the caller.
Calling A Procedure
Let us see how we can call a procedure in PL/SQL.
We can call the procedures with no parameters with just using the EXEC keyword and procedure name. Now that we know how we can work with procedures, let us try to understand how nested blocks are used in PL/SQL.
A nested block is nothing but a combination of one or more PL/SQL blocks to get better control over the execution and exceptional handling for the program.
Here is a simple example of a nested block.
SET SERVEROUTPUT ON SIZE 1000000; DECLARE n_emp_id EMPLOYEES.EMPLOYEE_ID%TYPE := &emp_id1; BEGIN DECLARE n_emp_id employees.employee_id%TYPE := &emp_id2; v_name employees.first_name%TYPE; BEGIN SELECT first_name INTO v_name FROM employees WHERE employee_id = n_emp_id; DBMS_OUTPUT.PUT_LINE('First name of employee ' || n_emp_id || ' is ' || v_name); EXCEPTION WHEN no_data_found THEN DBMS_OUTPUT.PUT_LINE('Employee ' || n_emp_id || ' not found'); END; END; /
The outer PL/SQL block in the above example is known as the parent block or enclosing block, the inner block, on the other hand, is known as the child block or the enclosed block.
It is not a great idea to use the variables with the same names in both the blocks because during the execution the child block variable will override the parent block variable. It happens because PL/SQL gives first priority to the variable inside its own block.
We can overcome this issue with the block label that helps us make references to variables inside blocks using a label.
Here is a simple example to show how we can use a block label.
<<block_label>> DECLARE ... BEGIN ... END;
Using a block label helps to improve the readability of the code, gain better control and make references to the blocks. Now that we know how we can work with nested blocks, let us try to understand how the IF STATEMENT works in PL/SQL.
PL/SQL has three IF STATEMENTS
IF-THEN – It is the simplest IF STATEMENT if the condition is true the statements will execute, if the condition is false, it does nothing.
IF-THEN-ELSE – In this, the ELSE clause is added for an alternative sequence of statements.
IF-THEN-ELSEIF – It allows us to execute multiple test conditions in a sequence.
IF condition THEN sequence_of_statements; END IF;
IF condition THEN sequence_of_if_statements; ELSE sequence_of_else_statements; END IF;
IF condition1 THEN sequence_of_statements1 ELSIF condition2 THEN sequence_of_statements2 ELSE sequence_of_statements3 END IF;
The CASE statement basically helps in executing a sequence of statements based on a selector. A selector, in this case, can be anything, it can be a variable, function or a simple expression. Here is a simple example to show the syntax of the CASE statement in PL/SQL.
[<<label_name>>] CASE [TRUE | selector] WHEN expression1 THEN sequence_of_statements1; WHEN expression2 THEN sequence_of_statements2; ... WHEN expressionN THEN sequence_of_statementsN; [ELSE sequence_of_statementsN+1;] END CASE [label_name];
In the above syntax, after the CASE keyword comes the selector. PL/SQL will evaluate the selector only once to determine which statement needs to be executed.
Followed by the selector is the WHEN keyword. If the expression satisfies the selector then the corresponding statement after THEN keyword gets executed.
A loop statement in PL/SQL is an iterative statement that allows you to execute a sequence of statements multiple times. Here is a simple example to show the syntax of a loop statement in PL/SQL.
LOOP sequence_of_statements; END LOOP;
There has to be at least one executable statement in between the LOOP and END LOOP keyword.
Loop with EXIT Statement
The EXIT and EXIT when statements allow you exit the loop. EXIT WHEN statement terminates the loop conditionally while EXIT terminates the execution unconditionally.
LOOP ... EXIT WHEN condition; END LOOP;
A loop label is used to qualify the name of the loop counter variable when used in a nested loop. Following is the syntax of a loop label.
<<label>> LOOP sequence_of_statements; END LOOP label;
We can use the WHILE loop statement when the number of executions are not defined until the execution starts. The following syntax is used for a WHILE loop statement in PL/SQL.
WHILE condition LOOP sequence_of_statements; END LOOP;
The condition in the syntax is a boolean value or expression that evaluates to be either TRUE, FALSE or NULL. If the condition is TRUE, the statements will be executed, if it is FALSE, the execution stops and the control goes to the next executable statement.
A FOR loop statement in PL/SQL allows us to execute a sequence of statements for a definite number of times. Following is the syntax to use FOR loop statement in PL/SQL
FOR loop_counter IN [REVERSE] lower_bound .. higher_bound LOOP sequence_of_statements; END LOOP;
PL/SQL creates a local variable loop_counter automatically with an INTEGER data type for the loop so that you don’t have to declare it explicitly. The lowerbound..higherbound is the range over which the loop iterates. Also, you must have at least one executable statement between LOOP and END LOOP keywords.
In PL/SQL any kind of error is treated as an exception. An exception can be treated as a special condition that can change or alter the execution flow. In PL/SQL, there are two types of exceptions.
System Exception – It is raised by the PL/SQL run-time when it detects an error.
Programmer-Defined Exception – These exceptions are defined by the programmer in a specific application.
Defining An Exception
An exception in PL/SQL has to be declared before it can be raised. We can define the exception using the EXCEPTION keyword like we have done in the example below.
To raise an exception, we use the RAISE keyword.
So that was all about PL/SQL, I hope this article has helped you in adding value to your knowledge. For more information on SQL or Databases, you can refer to our comprehensive reading list here: Databases Edureka.
If you wish to get a structured training on MySQL, then check out our MySQL DBA Certification Training which comes with instructor-led live training and real-life project experience. This training will help you understand MySQL in-depth and help you achieve mastery over the subject.
Got a question for us? Please mention it in the comments section of ”PL/SQL Tutorial” and I will get back to you.