Introduction to Oracle PL/SQL: A Step-by-Step Guide for Beginners

Introduction to Oracle PL/SQL

SQL enables a user to communicate with database but in straight forward manner. You can use SQL commands to query the database and modify tables within the database. When you write an SQL statement, you are telling the database what you want to do, not how to do it. 

Though it is so much powerful in handling data and various database objects, it lacks some of the basic functionalities provided by other programming languages like conditional checking, branching, looping etc.

To overcome the disadvantages of SQL, Oracle provides PL/SQL (Procedural Language / Structured Query Language). 

PL/SQL is a super set of SQL. This means it supports all the functionalities provided by SQL along with its own procedural capabilities. Any SQL statement can be used in PL/SQL program.

Note: SQL data definition statements such as CREATE TABLE are not allowed in PL/SQL because PL/SQL code is compiled. And, at compile time, it cannot refer to objects that do not yet exist.

Advantages / Benefits of PL/SQL:

1) Procedural Capabilities: It enables a programmer to control an execution of a     program based on some conditions and user inputs.

Procedural Language Control Structures allow you to do the following:

• Execute a sequence of statements conditionally (conditional checking)
• Execute a sequence of statements iteratively in a loop (looping)
• Process individually the rows returned by a multiple-row query with an explicit cursor
• branching

2) Support to Variables:

PL/SQL supports declaration and user of variables which can be used to store results of a query or some expressions. They can be used in an SQL or PL/SQL statements.

3) Errors Handling: Pl/SQL permits to handle errors as required

The Error handling functionality in PL/SQL allows you to do the following:

• When an error occurs, user friendly messages can be displayed.
• Execution of program can be controlled instead of program terminating abruptly.
• User-defined error conditions can be declared and processed with exception-handling routines

4) User Defined Functions:

It supports user defined functions and procedural. It helps in Modularized Program Development.

• Group logically related statements within blocks.
• Break down a complex problem into a set of manageable, well-defined, logical modules and implement the modules with blocks.

5) Sharing of Code:

It allows user to store compile code in Oracle server. This makes the code to be accessed and shared by any application that can interact with an Oracle database. This code can be executed from other programming languages such as JAVA.

6) Portability:

• Programs written in PL/SQL can be moved or transferred and executed from any host environment (operating system or platform) that supports the Oracle server and PL/SQL. In other words, PL/SQL programs can run anywhere the Oracle server can run; you do not need to make any changes for each new environment.

• You can also move code between the Oracle server and your application. You can write portable program packages and create libraries that can be reused in different environments.

7) Improved Performance (Efficient Execution):

PL/SQL can improve the performance of an application.

PL/SQL can be used to group SQL statements together within a single block and to send the entire block to the server in a single call, thereby reducing networking traffic. 

Without PL/SQL, the SQL statements are sent to the Oracle server one at a time. Each SQL statement results in another call to the Oracle server and higher performance overhead. In a networked environment, the overhead can become significant.

The Generic PL/SQL block (PL/SQL block structure):

          DECLARE                                                                   (optional)

                        Variables, cursor, user-defined exception

BEGIN                                                                     (Compulsory)

          SQL statements;

          PL/SQL statements:

EXCEPTION                                                                (optional)

Action to perform when error or exception occurs

END;                                                                                 (Compulsory)

PL/SQL is a block-structured language, meaning that programs can be divided into structural logical blocks of code that describes processes, which have to be applied to data A single PL/SQL code block consists of a set of SQL statements, grouped together and send to Oracle Engine entirely.  

A block is called a named block, if it is given some name to identify. Eg function, procedures

A block is called an anonymous block, if it is not given any name.

A PL/SQL block has a definite structure. It consists of up to three sections: declarative (optional), executable (required), and exception handling (optional). The following table describes the three sections:  

SECTION	DESCRIPTION	INCLUSION
DECLARATIVE	This section starts with keyword ‘declare’. All variables, constants, cursors, and user- defined exceptions that are referenced in the executable and declarative sections are defined in this section. Initialization is also done here.	OPTIONAL (If block does not require any variable or cursors then section can be omitted)
EXECUTABLE	It starts with keyword ‘begin’. It contains SQL statements to manipulate data in the database and PL/SQL statements to manipulate data in the block. Application Logic (using looping, branching, conditional checking) is written in this section. It forms the body of the PL/SQL block.	COMPULSORY
ERROR HANDLING	This section starts with keyword ‘exception’. Specifies the actions to perform when errors and abnormal conditions arise in the executable section	OPTIONAL (If block does not handle any exception explicitly, then this section can be omitted)
END;	To terminate the block.	COMPULSORY

Example 1:

DECLARE

                       name varchar2(30);

BEGIN

                      name := '&name';

                      DBMS_OUTPUT.PUT_LINE (' Hello  ' || name);

END;

Example 2:

BEGIN

                      DBMS_OUTPUT.PUT_LINE (' Hello WORLD');

END;

What is Variable?

Ø  With PL/SQL you can declare variables and then use them in any SQL and PL/SQL. Variables are declared in Declarative section of the PL/SQL block.

Ø  They must be assigned valid data types.

Ø  They can also be initialized if required.

Ø  Variables name must begin with a character & can be followed by maximum of 29 other characters.

Ø  Reserved words cannot be used as variable names.

Ø  Variables must be separated from each other by at least one space or by a punctuation mark.

Ø  Case is not significant when declaring variable names.

Ø  A space cannot be used in a variable name.

 Variables can be used for the following:

• Temporary storage of data: Data can be temporarily stored in one or more variables for use when validating data input and for processing later in the data flow process.

• Manipulation of stored values: Variables can be used for calculations and other data manipulations without accessing the database.

• Reusability: After they are declared, variables can be used repeatedly in an application simply by referencing them in other statements, including other declarative statements.

Declaring the variable

Syntax:        <variableName>  <datatype> [NOT NULL]:= <initialValue>;

                 Salary number (7, 2) NOT NULL:= 0;

• You can declare variables in the declarative part of any PL/SQL block, subprogram, or package. Declarations allocate storage space for a value, specify its data type any valid data type supported by PL/SQL, and name the storage location so that you can reference it.

• If variable needs to be initialized, then it can be done by using ‘:=’ assignment operator.

• No space is allowed between assignment operator (:=).

• If you use the NOT NULL constraint, you must assign a value and it cannot have NULL value during the program execution.

Examples:

        No NUMBER (3);

        City CHAR (15);

        Name VARCHAR2 (20);

        Counter NUMBER (2) NOT NULL:=0;

 Declaring a constant:

• A constant is also used to store values like a variable. But, the value stored in the constant cannot be changed during the program execution.

• In constant declarations, the keyword CONSTANT must be specified before the data type.

• A constant must be initialized in its declaration; otherwise, you get an error at compile time.

Syntax:

                <constantName>  CONSTANT <datatype> := <intialValue>;

Example:

                pi    CONSTANT  NUMBER (3,2):= 3.14;

 

Assigning a Value:

A variable can be assigned a value in one of the three ways:

1) By using assignment operator (:=)

Syntax:        <variableName> := value;

                       eg.        No := 1;                                  constant value

                                    Area := pi * r * r;                     expression               

                                    Val := val_exist(1);               return value of function

A value can be constant value, or result of some expression, or return value of some function.

2) By reading from the keyboard:

Syntax:        <variableName> := &variableName;

                        eg.       no := &no;

                                    name:= ‘&name’;

Whenever ‘&’ is encountered, a value is read from the keyboard and assigned to variable.

3) Selecting or fetching table data values into variables:

Syntax:        SELECT column1, column2, ------ columnN INTO var1, var2, ----- varN

                       FROM <tableName>

                       WHERE <condition>;

 

Column1 --- columnN:	is a list of at least one column and can include SQL expressions, row functions, or group functions.
Var1 --- varN:	is the variable that holds the retrieved value.
tableName;	specifies the database table name.
Where condition   (it is optional)	is composed of column names, expressions, constants, and comparison operators, including PL/SQL variables and constants
INTO	Compulsory when select statement is used in PL/SQL
Specify the same number of variables in the INTO clause as database columns in the SELECT clause. You must specify one variable for each item selected, and the order of the variables must correspond with the items selected and be sure that their data types are compatible. Use group functions, such as SUM, in a SQL statement, because group functions apply to groups of rows in a table. Queries must return one and only one row. A query that returns more than one row or no row generates an error.

Example:

 DECLARE

                       v_sum_sal number (10,2);

                       v_deptno number := 10;

 BEGIN

                       SELECT SUM(sal) INTO v_sum_sal  FROM emp

                       WHERE deptno = v_deptno;

                       DBMS_OUTPUT.PUT_LINE ('The sum salary is ' ||TO_CHAR(v_sum_sal));

 END;

 

The %TYPE Attribute (Anchored data type):

Ø  When you declare PL/SQL variables to hold column values, you must ensure that the variable is of the correct data type and precision. If it is not, a PL/SQL error will occur during execution.

Ø  Rather than hard coding the data type and precision of a variable, you can use the %TYPE attribute to declare a variable according to another previously declared variable or database column which means that datatype for variable is determined based on the datatype of other object.

Ø  The %TYPE attribute is most often used when the value stored in the variable will be derived from a table in the database.

Ø  To use the attribute in place of the data type that is required in the variable declaration, prefix it with the database table and column name. If referring to a previously declared variable, prefix the variable name to the attribute.

Ø  You can also declare a variable according to another previously declared variable by prefixing the variable name to the attribute.

Ø  PL/SQL determines the data type and size of the variable when the block is compiled so that such variables are always compatible with the column that is used to populate it.

Ø  This is a definite advantage for writing and maintaining code, because if datatype of column changes, then the datatype of variable will also change automatically at run time. There is no need to be concerned with column data type changes made at the database level. This provides data independence, reduces maintenance costs, and allows programs to adapt as the database changes to meet new business needs.

Syntax:

                <variableName> object%TYPE [NOT NULL] := intialValue;

Example of table.columnName

                Salary           Emp.sal%type;

                v_name        employees.last_name%TYPE;

Example of declaring variable according to previously declared variable

                 v_balance               NUMBER(7,2);

                 v_min_balance        v_balance%TYPE := 10;

Note: A NOT NULL database column constraint does not apply to variables that are declared using %TYPE. Therefore, if you declare a variable using the %TYPE attribute that uses a database column defined as NOT NULL, you can assign the NULL value to the variable.

Displaying Messages:

To display message or any output on the screen in PL/SQL following statement is used

Syntax:                      dbms_outout.put_line(message);

A dbms_output is a package, which provides function to collect the information in a buffer.

A put_line is a procedure which displays messages on the screen.

A message is a character string to be displayed. To display data of other datatype, they must be concatenated with some other string.

To display message, the SERVEROUTPUT should be set to ON. SERVEROUTPUT is a SQL*PLUS environment parameter that displays the information passed as a parameter to the put_line function.

        Example:

dbms_output.put_line(‘Hello’);                                          

Output:

Hello

dbms_output.put_line(‘Square of ’ || 2 || ‘ is ‘ || 4 );  

Output:      

Square of 2 is 4

 Comments:

Ø  Comments are strictly informational and do not enforce any conditions or behavior on behavioral logic or data. Well-placed comments are extremely valuable for code readability and future code maintenance.

Ø  Comment code to document each phase and to assist debugging.

Ø  Comments are statements that will not get executed even though they are present in the program code. They are ignored.   

Two types of comments in PL/SQL

1)    Single line comment (--): Two dashes or two hyphen (--) if the comment is on a single line,

2)    Multi Line comment (/* */): enclose the comment between the symbols /* and */ if the comment spans several lines.                          

Creating & Executing a PL/SQL block:

Open any editor like Notepad. Write the program code in a file and save it with .sql extension.

Or user EDIT command on SQL prompt to open notepad from SQL*Plus environment.

Syntax: EDIT filename                   example: EDIT d:/PLSQL/first.sql

To execute this block, use any of the following commands on prompt.

                  RUN filename                      RUN D:/PLSQL/first.sql

                  START filename                  START D:/PLSQL/first.sql

                  @ filename                           @ D:/PLSQL/first.sql

Control Structures:

The logical flow of statements within the PL/SQL block can be changed or controlled with a number of control structures. They are classified into the following categories:

Ø  Conditional Control

Ø  Iterative Control

Ø  Sequential Control 

Conditional Control:

IF Statements

The structure of the PL/SQL IF statement is similar to the structure of IF statements in other procedural languages. It allows PL/SQL to perform actions selectively based on conditions.

                IF condition  THEN

                            statements;

                [ELSIF condition THEN

                            statements;]

                [ELSE

                            statements;]

                END IF;

In the syntax:

Condition is a Boolean variable or expression (TRUE, FALSE, or NULL). (It is associated with a sequence of statements, which is executed only if the expression generates TRUE.)

THEN is a clause that associates the Boolean expression that precedes it with the sequence of statements that follows it.

Statements can be one or more PL/SQL or SQL statements. (They may include further IF statements containing several nested IF, ELSE, and ELSIF statements.)

ELSIF is a keyword that introduces a Boolean expression. (If the first condition generates FALSE or NULL then the ELSIF keyword introduces additional conditions.)

ELSE is a keyword that executes the sequence of statements that follows it if all the conditions generates FALSE or NULL. 

There are 3 forms of IF

Simple IF:      IF condition THEN

                                    Statements;

                       END IF;

IF Else:          IF condition THEN

                                    Statements;

                      ELSE

                                    Statements;

                      END IF;

ELSE IF Ladder: As specified above