PL/SQL Data Types

PL/SQL data types are the formats used to store and manipulate data in PL/SQL programs.
PL/SQL provides a wide range of data types, including numeric, character, date, and boolean
data types. Each data type has its own specific characteristics and is used to store specific types
of data.

PL/SQL provides various data types to store and manipulate data.

1. Numeric Data Types

 INTEGER
 NUMBER
 FLOAT

DECLARE

num1 NUMBER := 40;

num2 INTEGER := 50;

BEGIN

DBMS_OUTPUT.PUT_LINE('Sum: ' || (num1 + num2));

END;

Output:

Sum: 90

2. Character Data Types

 CHAR
 VARCHAR2
 LONG

DECLARE

name VARCHAR2(20) := 'IshaRukhsar';

BEGIN

DBMS_OUTPUT.PUT_LINE('Name: ' || name);

END;

Output:
Name: Isha Rukhsar

3. Date Data Types

DATE

DECLARE

hire_date DATE := SYSTIMESTAMP;

BEGIN

DBMS_OUTPUT.PUT_LINE('Hire Date: ' || hire_date);

END;

Output:

Hire Date: 2022-01-01 12:00:00

Variables and Constants

Variables and constants are used to store and manipulate data in PL/SQL programs. Variables
are declared using the VARCHAR2, NUMBER, DATE, etc. data types, and are used to store
values that can be changed during the execution of the program. Constants, on the other hand,
are declared using the CONSTANT keyword, and are used to store values that remain unchanged
during the execution of the program.

Variables

Variables are declared using the VARCHAR2, NUMBER, DATE, etc. data types.

DECLARE

name VARCHAR2(20);

BEGIN

name := 'John Doe';

DBMS_OUTPUT.PUT_LINE('Name: ' || name);

END;

Output:

Name: John Doe

Constants
Constants are declared using the CONSTANT keyword.

DECLARE

CONSTANT pi NUMBER := 3.14159;

BEGIN

DBMS_OUTPUT.PUT_LINE('Value of pi: ' || pi);

END;

Output:

Value of pi: 3.14159

Control Structures
Control structures are used to control the flow of a PL/SQL program. They include conditional
statements (such as IF and CASE statements), loops (such as FOR and WHILE loops), and
exception handling statements (such as EXCEPTION and RAISE statements). Control structures
allow the programmer to specify the order in which statements are executed, and to handle errors
and exceptions that may occur during the execution of the program.

Control structures are used to control the flow of a PL/SQL program.

Conditional Statements:

IF-THEN-ELSE:

IF condition THEN

statements;

ELSIF condition THEN

statements;

ELSE

statements;

END IF;
CASE Statement:

CASE variable

WHEN value1 THEN statements;

WHEN value2 THEN statements;

ELSE statements;

END CASE;

I. IF Statement

The IF statement is used to execute a block of code if a condition is true.

DECLARE

grade NUMBER := 85;

BEGIN

IF grade >= 80 THEN

DBMS_OUTPUT.PUT_LINE('Grade is A');

ELSE

DBMS_OUTPUT.PUT_LINE('Grade is not A');

END IF;

END;

Output:

Grade is A

II. LOOP Statement

The LOOP statement is used to execute a block of code repeatedly.

DECLARE

i NUMBER := 1;
BEGIN

LOOP

DBMS_OUTPUT.PUT_LINE('Iteration ' || i);

i := i + 1;

EXIT WHEN i > 5;

END LOOP;

END;

Output:

Iteration 1

Iteration 2

Iteration 3

Iteration 4

Iteration 5

III. CASE Statement

The CASE statement is used to execute a block of code based on a condition.

DECLARE

grade NUMBER := 85;

BEGIN

CASE

WHEN grade >= 90 THEN

DBMS_OUTPUT.PUT_LINE('Grade is A');

WHEN grade >= 80 THEN

DBMS_OUTPUT.PUT_LINE('Grade is B');

ELSE

DBMS_OUTPUT.PUT_LINE('Grade is not A or B');

 END CASE;

END;

Output:

Grade is B

 Cursors

Cursors are used to retrieve and manipulate data from a database. A cursor is a control structure
that allows the programmer to iterate over the rows of a query result set. Cursors can be declared
explicitly using the CURSOR keyword, or implicitly using the SELECT statement. Cursors
provide a way to process large amounts of data in a PL/SQL program.

A. Explicit Cursor

An explicit cursor is declared using the CURSOR keyword.

DECLARE

CURSOR emp_cursor IS

SELECT employee_id, name FROM employees;

emp_record emp_cursor%ROWTYPE;

BEGIN

OPEN emp_cursor;

LOOP

FETCH emp_cursor INTO emp_record;

EXIT WHEN emp_cursor%NOTFOUND;

DBMS_OUTPUT.PUT_LINE('Employee ID: ' || emp_record.employee_id || ', Name: ' ||

emp_record.name);

END LOOP;

CLOSE emp_cursor;

END;

Output:

Employee ID: 101, Name: John Doe

Employee ID: 102, Name: Jane Smith

B. Implicit Cursor

An implicit cursor is used with the SELECT statement.

DECLARE

emp_record employees%ROWTYPE;

BEGIN

SELECT * INTO emp_record FROM employees WHERE employee_id = 101;

DBMS_OUTPUT.PUT_LINE('Employee ID: ' || emp_record.employee_id || ', Name: ' ||

emp_record.name);

END;

Output:

Employee ID: 101, Name: John Doe

Functions
Functions are used to perform a specific task and return a value. A function is a named block of
code that takes zero or more arguments and returns a value. Functions can be used to encapsulate
complex logic and to provide a way to reuse code. Functions can be declared using the CREATE
FUNCTION statement.

Functions are used to perform a specific task and return a value.

Function Example

CREATE OR REPLACE FUNCTION get_employee_name(p_employee_id NUMBER)

RETURN VARCHAR2 IS

v_name VARCHAR2(20);

BEGIN

SELECT name INTO v_name FROM employees

Triggers
A trigger is a named block of code that is executed automatically when a specific event occurs.
Trigger Example

CREATE OR REPLACE TRIGGER update_employee_salary

AFTER UPDATE OF salary ON employees

FOR EACH ROW

BEGIN

DBMS_OUTPUT.PUT_LINE('Updated salary for employee ' || :NEW.employee_id);

END;

Triggering a Trigger

UPDATE employees SET salary = salary * 1.1 WHERE employee_id = 301;

Output:

Updated salary for employee 301