Oracle PL/SQL Tutorial

What do you know about Oracle PL/SQL?

As is said earlier, PL/SQL is one of the Oracle products and first developed by Oracle corporations in the year 1980. This product is a combination of procedural and RDBMS database language. You can also mention PL/SQL is a portable and high-level database transaction processing language. The PL/SQL offers a built-in and OS independent database programming environment. More importantly, PL/SQL is also known as the Command-line Sequential query language Plus interface. With the help of PL/SQL, it’s also possible to make direct calls from an external database programming language. The PL/SQL general syntax is created on the base of ADA and Pascal programming languages. This type of database product is available in ten times in-memory database and IBM database version2. 

Important key features of Oracle PL/SQL:

Below are the important key features of Oracle PL/SQL:

1. Oracle PL/SQL is tightly integrated with Sequential query language or SQL.

2. This language helps the developer to perform extensive error checking.

3. Oracle PL/SQL language supports to work with numerous data types.

4. This PL/SQL provides a wide variety of structural programming methods.

5. With the help of PL/SQL supports a structured programming facility through multiple database functions and procedures.

6. This PL/SQL offers development based web application servers and also generates web server pages.

7. As is said earlier PL/SQL is a combination of procedural and query database language. So this enables us to work with object-oriented programming. (Object-oriented is a procedural programming language).

Why we need Oracle PL/SQL?

The following are the very important advantages of Oracle PL/SQL:

1. SQL or sequential query language is the standard and popular database language. So PL/SQL integrates with this sequential query language database. The Oracle PL/SQL offers both dynamic and static query language. The static language supports Data management language operations and data transaction control system from PL/SQL database blocks. In a dynamic language, the SQL supports embedded dynamic database query language statements in PL/SQL.

2. The Oracle PL/SQL allows users to send an entire database of blocks of statements one at a time. This reduces networking data traffic and supports high-performance applications.

3. The Oracle PL/SQL offers high productivity to database programmers, so you perform operations like querying the data, data transmission, and data updation. 

4. With the help of PL/SQL, users can save time on web designing and debugging features. Also, you can perform exception handling, data hiding, object-oriented data type management, and data encapsulation methods.

5. The applications which are written in PL/SQL are highly portable.

6. The Oracle PL/SQL also provides high-level data security.

7. Oracle PL/SQL supports object-oriented programming and developing web applications.

The architecture of Oracle PL/SQL:

The architecture of Oracle PL/SQL explains the basic components and work nature. The below diagram will illustrate this:

1. PL/SQL Block

2. PL/SQL engine 

3. Database server

Let me explain them one by one;

1. PL/SQL block:

This is one of the basic components that consists of only PL/SQL codes. This also comprises different data sections which will divide the PL/SQL codes logically. These types of logical codes are mainly used for declaring purpose, data processing, exception handling to handle errors. This block also contains only SQL instructions used to interact with the database servers. The below are important blocks of PL/SQL database:

a. Anonymous block

b. Database functions 

c. Libraries 

d. procedural –oriented

e. Package body management 

f. package specifications and triggers

g. Type and type of body 

2. PL/SQL engines:

This type of PL/SQL engine is one of the important components of Oracle PL/SQL and here actual data processing step has taken place. This component separates the PL/SQL data units and SQL is a part of the inputs. The whole PL/SQL data units will be handled by the PL/SQL engine. Here the SQL part will be transferred into the database server where the actual interaction takes place. It can be installed on both the database server and the application server. 

3. Database server:

This is the important component in the PL/SQL unit used to store the actual datasets. This PL/SQL database server uses the SQL used to establish a communication with the database server. It consists of an SQL executor that inputs the SQL statement and executes the same. 

The major differences between SQL and PL/SQL:

Now it’s time to know the major differences between SQL and PL/SQL:

First, let’s explain about SQL:

1. SQL:

a. SQL stands for sequential query language used to perform various database operations like DML and DDL.

b. SQL statement is declarative, and that specifies how to be done with database management.

c. SQL database statements are executed as a single statement at a time.

d. SQL is mainly used for data manipulation purposes.

e. Very easy to interacts with a database server.

Now PL/SQL turn:

2. PL/SQL:

a. PL/SQL is a type of block that consists of codes. These codes are used to write the whole program blocks/ functions and procedures. 

b. PL/SQL is a procedural programming language that specifies how SQL statements need to be used.

c. PL/SQL executes the whole block at a time.

d. PL/SQL is mainly used to create any database applications.

e. This doesn’t establish any interaction with the database server.

f. PL/SQL is an extension of the query language (SQL) and so this contains only query statements.

Oracle PL/SQL basic syntax:

In this section, we are going to explain the basic syntax which we are going to use in the Oracle PL/SQL. Let me explain them one by one:

1. Declaration statement: In this section, the SQL statement will starts with the keyword DECLARE. This is an optional keyword that defines all the variables, subprograms, other elements, and cursors to be used while writing the program codes.

2. SQL executable commands: In general, this statement is enclosed between the keywords like BEGIN and END. This one is considered a mandatory section as it consists of both PL and SQL query statements of the program code. This declaration statement consists of at least one executable line of program codes, and this can be used sometimes as a NULL command where there is nothing to execute.

3. Exception handling method: This section of the PL/SQL statement starts with the keyword EXCEPTION. It is used to handle the errors in the program while executing them 

Oracle PL SQL Training

• Master Your Craft
• Lifetime LMS & Faculty Access
• 24/7 online expert support
• Real-world & Project Based Learning

Explore Curriculum

The following program code is syntax for PL/SQL statement:

DECLARE statement 

    

BEGIN execution

  

END;

// Continuation statement, this is also known as a nested statement.

Oracle PL/SQL delimiters:

Oracle PL/SQL statement consists of various characters with special meaning: let me mention a few of them,

+, -, *, /    = this is nothing but Addition, subtraction, multiplication, and division symbol.

%            = this is an attribute indicator.

‘              = this symbol is a character string delimiter.

.              = This is a component selector 

(,)            = This is an expression or list delimiter 

:              = this indicates a host indicator 

“              = Quoted identifier symbol 

=            = This is a relational operator 

First Oracle PL/SQL program: “Hello world” 

So now it’s time to execute our First oracle PL/SQL statement:

DECLARE statement 

    Message varchar2 (10): = ‘Hello world!’;

BEGIN 

   Dbms_output. Put_line (message); //to display the output 

END statement; 

/     // this is to end the execution code


OUTPUT:

Hello World 

PL/SQL procedure statement successfully completed.

Now it’s time to know the PL/SQL comments:

Comments in PL/SQL program are the explanatory statement, and with the help of these comment lines, anyone can better understand the source code. All programming languages follow some sort of comments. The PL/SQL program supports both single-line and multi-line comments.

For example:

DECLARE statement 

  ---    Variable declaration 

Message varchar1 (10): = “Hello world!” ;

BEGIN statement 

/* 

*

*/ PL/SQL executable statements //multiline comment 

Dbms_output. Put_line (message);

 END;

/

Oracle PL/SQL data types:

The Oracle PL/SQL supports variable types, constants, and parameters types. In this section, we are going to explain important data types that support Oracle PL/SQL programming. 

Let me explain them one by one:

1. Scalar: this data type holds Number, DATE, or Boolean data, and this is also a single value with no internal component.

2. Large Object Boolean or (LOB): This is a type of pointer object which are used to store data items separately other than the data set. The data items can be of type text, video clips, images, and sound waveforms. 

3. Composite data type: this type of data type consists of various internal components that can be accessed individually. For example collection of data records.

4. Reference type: This is a type of pointer which refers to other data value.

 Now let me explain the subtypes of Scalar:

1. Numeric type: this type of data value holds arithmetic operations.

2. Character type: this is an alphanumeric data type that represents the single characters and string of characters.

3. Boolean: Boolean data type consists of logical operations.

4. Date time: this scalar data type consists of dates and times value.

                                       Explore Oracle Goldengate Sample Resumes Download & Edit, Get Noticed by Top Employers! 

Subscribe to our youtube channel to get new updates..!

Subscribe

The following are the important syntax which is used to represent numeric value:

1. PLS_INTEGER // this represents the integer data type

2. BINARY_OPERATOR // this represents the only binary set of operators.

3. BINARY_FLOAT // this is a single-precision IEEE format floating-point number.

4. BINARY_DOUBLE 

5. NUMBER (prec, scale)

6. DECIMAL (prec, scale)

The below is a simple program:

DECLARE Statement

  NUM1 INTEGER_VALUE;

   NUM2 REAL_VALUE;

  NUM3 DOUBLE_PRESECION;

BEGIN statement

    NULL_VALUE;

END statement;

/  

Output: 

PL/SQL procedures are successfully completed.

PL/SQL user-defined subtypes:

A subtype is another data type; this is known as the base type. This subtype consists of the same valid operations. This PL/SQL user-defined several subtypes in packages like STANDARD, CHARACTER, and INTEGER.

SUBTYPE CHARACTER IS CHAR;  //this consists of only character type

SUBTYPE INTEGER IS NUMBER (40, 0);

Programming example:

DECLARE statement 

  SUBTYPE name IS char (20); //subtype is character data type

  SUBTYPE message IS varchar1 (100); // subtype is variable type 

Salutation name;

Greetings message;

BEGIN statement 

Salutation: = ‘Reader_name ‘;

Greetings: = ‘welcome to the world of Oracle PL/SQL’;

Dbms_output.put_line (‘Hello’   II  salutation II greetings);

END statement;

/

Output:

Hello reader welcome to the world of Oracle PL/SQL

PL/SQL procedure successfully completed.

Oracle PL/SQL variable:

In this section, we are going to discuss variables, variables are nothing but a name given to the storage area that our program manipulates. Each PL/SQL variable type consists of a data type that specifies the length, size, and layout of the memory value. The name of Oracle PL/SQL consists of letters followed by dollar signs, underscores, numerals, letters, and number signs. 

Note: Oracle PL/SQL should not exceed more than 30 characters. 

Syntax to declare a variable:

Variable_name [CONSTANT_VALUE] datatype [NOT NULL VALUE] [: = I DEFAULT initial value]

Where variable name is a valid identifier used in PL/SQL, data type represents the type of data that is used to store data values. 

Example:

Sales number (10, 2);

PI CONSTANT double precision: = 3.14; //pi value 

Name varchar1 (20);

Address varchar1 (100);

Initializing a variable:

Here there are two ways you can initialize the variable:

1. The default keyword

2. The assignment operator

Example:

Counter binary_integer: = 0;

Greetings varchar2 (10) default ‘have a great year’;

Here there are two types of variables:

1. Local variables: these local variables are declared in the inner block of the program and not accessible to any outer blocks. 

2. Global variables: here variables are declared in the outermost package or block.

Oracle PL/SQL operators:

An operator is a symbol, which is used to perform specific mathematical or logical operations. The Oracle PL/SQL contains built-in operators and supports the following important operators:

1. Arithmetic operators

2. Logical operators

3. String operators

4. Relational operators 

5. Comparison operators

Let me explain them one by one:

1. Arithmetic operators: the following are a few examples for arithmetic operators, 

+      = used to add two operands 

_      = used to subtract operands 

*= used to multiply any two operands

/   = divides numerators by de numerator value 

**= this is an exponential operator and used to add powers 

2. Relational operators: A relational operator is used to compare two expressions or values and returns the Boolean result. 

For example:

=:  checks if any two operands are equal or not, if yes then the condition will become true.

!=: checks if the values of any two operands are equal or not, if the given values are not equal, then the condition becomes true.

>: checks if the value of the left operand is greater than the value of the right operand, if yes, the condition will become true.

< checks="" if="" the="" value="" of="" the="" left="" operand="" is="" less="" than="" the="" value="" of="" the="" right="" operand="" if="" yes="" then="" the="" condition="" becomes="" true="" p="">

3. Comparison operator:

Comparison operators are used to comparing one expression to another. The examples are TRUE, FALSE, or NULL.

The comparison operators are LIKE, BETWEEN, IN, IS NULL.

4. Logical operators:

Following are the logical operators supported by Oracle PL/SQL. All these operators work on the Boolean operand and produce the Boolean results.

 Example:  AND, OR, NOT.

5. String operators: 

Following are the string operators like fixed-length string, variable-length strings, and Character large objects.

Oracle PL/SQL conditional statements:

The conditional statements are also known as decision-making statements and used to specify one or more conditional statements in the program and also helps to test them properly.

The below diagram explains the conditional statement:

Now we are going to explain the different conditional statements:

1. IF-THEN statement:

The IF statement composes the sequence of statements which are enclosed by the keywords like THEN and END IF. If the condition is true, the program will get executed, and if the condition is FALSE, the IF statement will terminate the program.

2. IF-THEN-ELSE statement:

this IF statement adds the keyword like ELSE by adding an alternative sequence to a statement. IF the condition is FALSE or reaches NULL, then these alternative sequence statements will be executed. 

3. IF-THEN-ELSEIF statement:

This statement allows users to choose between several alternatives.

4. CASE statement: 

This statement is like an IF conditional statement, the CASE statement selects one of the statements to be executed. 

5. Searched CASE statement: 

This searched CASE statement contains no selector, and WHEN clauses contain any search condition that produces Boolean values.

6. Nested IF-THEN-ELSE: 

Here you can use one IF-THEN or IF-THEN-ELSEIF statement inside another IF-THEN or IF-THEN-ELSEIF statement. 

Oracle PL/SQL- loops:

In general, most programming languages offer various control structures and allows us to execute complicated paths in any program. The main purpose of using the loop statement allows us to execute a group of statements multiple times and supports different types of loop statements.

The following figure explains the loop structure:

Loops types:

1. PL/SQL BASIC LOOP statement: in this type of loop structure, the sequence of the conditional statement is enclosed between the LOOP and the END loop. At each loop iteration, the sequences of control statements are executed and then resume the loop top statement.

2. PL/SQL WHILE LOOP statement: this loop statement repeats the control statements and group of statements while executing the program if the given condition is true. This WHILE loop conditional statement tests the condition before executing the loop body.

3. PL/SQL FOR LOOP: this executes any statement multiple times and also abbreviates the codes that manage loop variables. 

4. Nested Loops in PL/SQL: here you can make use of one or more loops inside any other loop, while and for loop conditional statements. 

For example:

DECLARE statement 

i   number (1);

j   number (1);

BEGIN 

  << outer_loop>>

  FOR i IN 1…..5 LOOP 

     << inner_loop>>

   FOR j IN 1…5 LOOP 

       Dbms_output.put_line (‘i is:  ‘II  i   II’ and j  is: ‘II j);

END loop inner_loop;

END loop outer_loop;

END;

/

Loop Control statements:

The LOOP control statement changes the execution from its normal loop sequence. The Oracle PL/SQL supports the following loop control statements.

1. EXIT statement: This EXIT control statement completes both the loop and control conditional that is passes to the statements once after the END LOOP.

2. CONTINUE statement: this loop control statement causes the loop to skip the body and immediately resets the conditions. 

4. GOTO statement: this control statement transfers the loop controls to the labeled statement. In general, it is advised not to use any GOTO statement.

Learn Business And Creative Skills. Get Started Today! Enroll to Learn Oracle Project Accounting Training.

Oracle PL/SQL- Arrays:

Arrays are nothing but a special kind of data structure which is also known as VARRAY. As we already know that arrays are used to store the data values or elements. All VARRYAS on PL/SQL consists of contiguous memory locations. The below diagram illustrates the PL/SQL arrays:

Each data element in a varray holds an index and this also has a maximum size that can be modified dynamically. 

The basic syntax to create Varray type:

CREATE OR REPLACE TYPE varray_type_name  IS VARRAY (n) of .

Where:

1. Varray_type_name is the valid attribute name.

2. “n” is the number of elements used in the varray.

3. element_type is the data type of the given elements in an array.

Oracle PL/SQL- Functions:

The Oracle PL/SQL function is nothing but some kind of actions can be performed while executing the program. A function is also the same as a procedure but it doesn’t return values.

Creating a Function in PL/SQL:

Here only the standalone functions can be created using the CREATE FUNCTION. The basic syntax is as follows:

CREATE [OR REPLACE] FUNCTION function_name

[(parameter_name [IN  I OUT I IN OUT] type [,……..])]

RETURN return_datatype

{IS I AS}

BEGIN statement 

< function_body>

END statement [FUNCTION_name];

Where:

1.function_name defines the name of the function.

2. [OR REPLACE] option allows you to modify an already existing function.

3. The optional parameter contains the list of names, modes, and types of the parameters. Where IN represents the value that will be called outside and OUT represents the parameter that is used to return any value outside of the function.

4. Every function must contain the return statement.

5. The RETURN clause defines the type of data that will be used to return from the function.

6. Function_body holds the executable part of the control statements.

7. Here the AS keyword is used instead of the IS keyword to create any standalone function. 

Programming example:

CREATE OR REPLACE FUNCTION totalcustomers

RETURN number IS 

Total number (3) := 0;

BEGIN 

SELECT count (*) into total 

FROM customers;

RETURN total1;

END;

/

Explore PL/SQL Sample Resumes Download & Edit, Get Noticed by Top Employers! 

Oracle PL/SQL: Cursors 

A cursor is nothing but a pointer to the context area. The Oracle PL/SQL controls the Context area using cursors. A cursor contains the rows which are returned by a SQL statement. The set of rows that the cursor holds are known as the active set. There are two types of cursors available:

1. Implicit cursors 

2. Explicit cursors

Let me explain them one by one:

1. Implicit cursors: 

Implicit cursors are automatically created by Oracle development, whenever SQL statements are executed when there is no explicit cursors exist in the statement. One more important point to remember, programmers cannot control the implicit cursors and the information. Whenever any DML statements like INSERT, DELETE, and UPDATE is issued, and an implicit cursor is associated with the statement. 

2. Explicit cursors: 

These explicit cursors are programmer-defined cursor types to gain more control over the Context area. Here the explicit cursors are defined in the declaration section of the Oracle PL/SQL block. This is created on a SELECT statement and returns more than one row. 

The syntax to create explicit cursors:

CURSOR cursor_name IS select_statement;

The following are the important cursor type:

1. % FOUND: this returns TRUE, if an INSERT, UPDATE, or DELETE statement affects one or more rows.

2. %NOTOUND: this is the opposite of %FOUND, and this returns the TRUE if an INSERT, UPDATE, or DELETE statement affects no rows in function.

3. %ISOPEN: this cursor type always returns the FALSE for any implicit cursors, this is because the ORACLE closes the SQL cursor automatically after once it executes the SQL statement. 

4. %ROWCOUNT: this cursor type returns the number of rows that are affected by INSERT, DELETE, or UPDATE statement.

Conclusion :

From this article, you people may get an idea about what are the important concepts that will come under the Oracle PL/SQL technology. This is an Oracle product used to manage a Relational database management system and query language statements. I hope this article may help a few of you to learn and gain in-depth knowledge of this technology.