PL/SQL Function

PL/SQL Function is same as PL/SQL Procedure, The only difference is that function must return a value and a procedure may or may not return a value

PL/SQL Procedure

Previously I have posted about PL/SQL Block structure , A piece of code that is organized in a properly defined sequence is called a block

PL/SQL provides two types of blocks

Function- A PL/SQL block that performs a task or set of tasks and returns a value

Procedure- A PL/SQL block that performs a task or set of tasks and may or may not return a value

PL/SQL Exceptions

An error that occurs during execution of the program is called exception, Like other programming languages, PL/SQL offers a way to catch these exceptions and handle them.

There are two types of exceptions in PL/SQL

1. System-Defined Exceptions 
2. User-Defined Exceptions 

PL/SQL For Loop

PL/SQL FOR loop is used when we need to execute set of statements for the specific number of times and loop operates between the start and end counter values. The counter is always incremented by one and once the counter reaches to end integer value, the loop terminates

The syntax of PL/SQL FOR Loop is like this

FOR counter_variable IN start value.. end value LOOP

statements to execute 

END LOOP;

PL/SQL While Loop

PL/SQL WHILE loop is used to execute statements as long as given condition is true and the condition is checked at the beginning of each iteration

The syntax of PL/SQL While loop is like this

WHILE condition

statements to execute

END LOOP;

PL/SQL Basic Exit Loop

In PL/SQL Basic Loop all statements inside the block are executed at least once before loop termination, Basic loop encloses statement between LOOP and END LOOP and there must be an EXIT or EXIT-WHEN condition to terminate the loop

The syntax of Basic Exit loop is like this

LOOP

statements to execute

EXIT; or EXIT-WHEN

END LOOP;

See these examples for better understanding

PL/SQL Loops , Iterative Statement in PL/SQL

Loops are used to repeat execution of a statement or a set of statements multiple times on base of a condition or expression
EXIT and EXIT-WHEN keywords are used to terminate the loop

EXIT- terminates the loop unconditionally and passes control to the next statement after the loop
EXIT-WHEN- terminates the loop when EXIT-WHEN clause is checked and if returns true then the loop is terminated and control is passed to next statement after the loop

The basic syntax of Loop in PL/SQL is like this

LOOP

Set of statements

END LOOP;

PL/SQL CASE Statement, Decision Making Statement in PL/SQL

Like real life in programming sometimes we need to execute some code on a specific condition, PL/SQL CASE statement allows us to execute a sequence of instructions based on a selector (A variable, function, expression etc)
and if selector value is equal to value or expression in WHEN clause then corresponding THEN clause will execute and process the statements

PL/SQL Conditions, IF-ELSE Conditional Statement

Like other programming languages, PL/SQL supports decision making statements, These statements are also called conditional statement

Basic Syntax of IF-ELSE is like this in PL/SQL

IF (Condition 1)

THEN

Statement to execute (if condition 1 is true)

ELSIF (Condition 2)

THEN 

Statement to execute (if condition 2 is true)

ELSE

Statement to execute (if condition 1& 2 both are false)

END IF;

For a better understanding of concept look at these examples

PL/SQL Variables and Constants

A variable in any programming language is the name of space where values are stored and controlled by our code/program

• We can not use reserve keyword as a variable name 
• Variable length should not exceed 30 characters
• The variable name consists of letters followed by other letters, dollar sign, underscore and numerals
• The variable name should be clear and easy to understand

Here we'll learn how to declare and initialize variables in PL/SQL

The basic syntax for declaring a variable in PL/SQL is following

variable_name [CONSTANT] datatype [NOT NULL] [:= | DEFAULT initial_value] 

Here variable_name is the identifier of variable and datatype is a valid PL/SQL datatype. CONSTANT and DEFAULT are keywords used to define constants and set default values of variables

PL/SQL Basic Syntax, Block Structure and Anonymous block

PL/SQL is highly structured language and it's program units are written in the block of code, In this tutorial, we'll learn about basic syntax and the block structure of PL/SQL

A piece of code that is organized in a properly defined sequence is called a Block. A PL/SQL Block consists of 3 parts

DECLARE
<<declaration >>
--Declare Variables,Constants, Cursors and all elements

BEGIN
<<executable statements>>
--SQL, PL/SQL Commands 

EXCEPTION
<<exception handling>>
--Code to handle the exception

END;

PL/SQL Tutorial - What is PL/SQL, Features and Advantages of PL/SQL

PL/SQL is developed by Oracle Corporation to increase/enhance capabilities of SQL, PL/SQL stands for Procedural Language extension to SQL . PL/SQL is highly structured and expressive language and because of its expressive syntax it is very easy to understand and learn

PL/SQL is integrated with Oracle Database and can be called from any other programming language. It is tightly integrated with SQL so it's easy to learn PL/SQL if you have knowledge of SQL

Enable DBMS_OUTPUT in Oracle SQL Developer

I hope all of you'll be familiar with Oracle SQL Developer tool , A tool used by database developers to perform DB related tasks efficiently

DBMS_OUTPUT package of PL/SQL enables user to show/print some debugging information and used by learners to run and check small chunks of pl/sql code

Here we'll see how to enable DBMS_OUTPUT package in SQL Developer