PLSQL Tutorial For Beginners - Learn PL SQL Data Types

Online Certification in Applied Data Science, Machine Learning and Edge AI By E&ICT Academy, IIT Guwahati 6 months

PLSQL Tutorial For Beginners – Learn PL SQL Data Types

Written By The IoT Academy
Published on June 17th, 2024

Welcome to our complete PLSQL tutorial for beginners! Whether you are new to PLSQL or want to learn more, this guide will teach you the basics of PLSQL (Procedural Language/Structured Query Language). PL/SQL is an extension of SQL used by Oracle that adds powerful tools for handling errors, repeating tasks, and making decisions in the database. This tutorial covers important topics for emerging PLSQL developers like data types, primary keys, and loops, as well as advanced topics like cursors, collections, and exception handling. With simple examples and clear explanations, you will learn about PL SQL to write strong code and create advanced database applications.

Introduction to PLSQL

PL/SQL is Oracle’s way to add more tools to SQL. It lets you write code that runs inside Oracle databases. This code can do things like handle errors, run tasks repeatedly, and make decisions based on conditions. PL/SQL makes also it easier to manage and use databases by allowing developers to bundle SQL queries into procedures and functions that can be reused. It is popular for building applications that need to handle lots of data efficiently as well as manage complex rules and processes within Oracle databases. For those interested to learn PL SQL in deep, a PLSQL tutorial can be very helpful.

PL SQL Data Types

PLSQL types define the type of data that a variable can store. Understanding these data types is fundamental to writing efficient and robust PLSQL code. Here are some commonly used PLSQL data types:

VARCHAR2: Stores variable-length character strings.

NUMBER: Stores numeric data.

DATE: Store date and time information.

BOOLEAN: Stores Boolean values (TRUE, FALSE, or NULL).

PLSQL Record: Stores related data items under a single name.

PLSQL Table: Stores data temporarily, similar to arrays.

Each data type has specific rules regarding storage requirements and allowable values, ensuring data integrity and efficient storage management within Oracle databases.

Primary Key in PLSQL

In PL/SQL, a primary key is a rule that makes sure each record in a table is unique and cannot be empty. It helps to prevent duplicate records and ensures that every entry is identifiable. By setting a primary key, you make it easy to find and link data between tables. This rule is important for keeping the database accurate and organized. Primary keys are usually set up when you create or change tables using the `CREATE TABLE` or `ALTER TABLE` commands. For a more detailed understanding, you might consider following a PLSQL tutorial.

Basic Loop in PL SQL

In PL/SQL, a loop is a fundamental control structure that allows you to execute a block of code repeatedly. Here is a basic example of different types of loops in PL/SQL: simple loops, WHILE loops, and FOR loops.

Simple Loop

A simple loop repeatedly executes a block of code until an `EXIT` statement is encountered.

WHILE Loop

A WHILE loop repeatedly executes a block of code as long as a specified condition is true.

FOR Loop

A FOR loop executes a block of code a fixed number of times.ExplanationHere is a simple explanation of the loops in this PLSQL tutorial:

Simple Loop:
Begins with the `LOOP` keyword.
Executes the block of code within the loop.
The `EXIT WHEN` statement is used to exit the loop when the condition is met.
WHILE Loop:
Begins with the `WHILE` keyword followed by a condition.
Continues to execute the block of code as long as the condition evaluates to true.
FOR Loop:
Begins with the `FOR` keyword followed by a loop variable and a range (`IN start..end`).
Executes the block of code for each value in the specified range.

These are the basic types of loops in PL/SQL. Each type is useful for different situations, depending on how many times you need to execute the block of code and whether you know the number of iterations in advance.Advanced PL SQL ConceptsPL/SQL (Procedural Language/Structured Query Language) offers many advanced features that extend its capabilities beyond basic loops and simple SQL operations. For a deeper understanding of these concepts, you can explore below PLSQL tutorial:

Cursors

Cursors are used to fetch and manipulate data row-by-row from a result set.Implicit CursorsPL/SQL implicitly declares a cursor for all DML and `SELECT INTO` statements.Explicit CursorsExplicit cursors allow more control over the context area.

Collections

Collections in PL/SQL are arrays, which can be of types like associative arrays, nested tables, or VARRAYs.Associative ArraysNested Tables

Exception Handling

PL/SQL provides a robust exception-handling mechanism to manage runtime errors.

Dynamic SQL

Dynamic SQL enables you to build and execute SQL statements dynamically at runtime.

Bulk Operations

Bulk operations help in processing large volumes of data efficiently.Bulk CollectFORALL Statement

PL/SQL Packages

Packages allow you to group related PL/SQL types, items, and subprograms.Package SpecificationPackage BodyPLSQL ExamplesLet’s illustrate the use of PLSQL with a simple example of this PLSQL tutorial:

DECLARE

counter NUMBER := 1;

BEGIN

LOOP

DBMS_OUTPUT.PUT_LINE(‘Counter value: ‘ || counter);

counter := counter + 1;

— Exit the loop when counter reaches 10

EXIT WHEN counter > 10;

END LOOP;

END;

DECLARE

counter NUMBER := 1;

BEGIN

WHILE counter <= 10 LOOP

DBMS_OUTPUT.PUT_LINE(‘Counter value: ‘ || counter);

counter := counter + 1;

END LOOP;

END;

BEGIN

FOR counter IN 1..10 LOOP

DBMS_OUTPUT.PUT_LINE(‘Counter value: ‘ || counter);

END LOOP;

END;

DECLARE

employee_name VARCHAR2(100);

BEGIN

SELECT name INTO employee_name FROM employees WHERE employee_id = 101;

DBMS_OUTPUT.PUT_LINE(‘Employee Name: ‘ || employee_name);

END;

DECLARE

CURSOR emp_cursor IS

SELECT employee_id, name FROM employees;

employee_id employees.employee_id%TYPE;

employee_name employees.name%TYPE;

BEGIN

OPEN emp_cursor;

LOOP

FETCH emp_cursor INTO employee_id, employee_name;

EXIT WHEN emp_cursor%NOTFOUND;

DBMS_OUTPUT.PUT_LINE(‘Employee ID: ‘ || employee_id || ‘ Employee Name: ‘ || employee_name);

END LOOP;

CLOSE emp_cursor;

END;

DECLARE

TYPE num_table IS TABLE OF NUMBER INDEX BY PLS_INTEGER;

numbers num_table;

BEGIN

numbers(1) := 100;

numbers(2) := 200;

numbers(3) := 300;

FOR i IN 1..3 LOOP

DBMS_OUTPUT.PUT_LINE(‘Element ‘ || i || ‘ = ‘ || numbers(i));

END LOOP;

END;

DECLARE

TYPE string_table IS TABLE OF VARCHAR2(100);

names string_table := string_table();

BEGIN

names.EXTEND(3);

names(1) := ‘John’;

names(2) := ‘Jane’;

names(3) := ‘Doe’;

FOR i IN 1..names.COUNT LOOP

DBMS_OUTPUT.PUT_LINE(‘Name ‘ || i || ‘ = ‘ || names(i));

END LOOP;

END;

DECLARE

l_number NUMBER;

BEGIN

l_number := 10 / 0; — This will raise a ZERO_DIVIDE exception

EXCEPTION

WHEN ZERO_DIVIDE THEN

DBMS_OUTPUT.PUT_LINE(‘Attempt to divide by zero.’);

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE(‘An unexpected error occurred: ‘ || SQLERRM);

END;

DECLARE

sql_stmt VARCHAR2(200);

table_name VARCHAR2(50) := ’employees’;

v_count NUMBER;

BEGIN

sql_stmt := ‘SELECT COUNT(*) FROM ‘ || table_name;

EXECUTE IMMEDIATE sql_stmt INTO v_count;

DBMS_OUTPUT.PUT_LINE(‘Total rows in ‘ || table_name || ‘: ‘ || v_count);

END;

DECLARE

TYPE emp_table IS TABLE OF employees%ROWTYPE;

emp_records emp_table;

BEGIN

SELECT * BULK COLLECT INTO emp_records FROM employees WHERE department_id = 10;

FOR i IN 1..emp_records.COUNT LOOP

DBMS_OUTPUT.PUT_LINE(‘Employee ID: ‘ || emp_records(i).employee_id || ‘, Name: ‘ || emp_records(i).name);

END LOOP;

END;

DECLARE

TYPE emp_ids IS TABLE OF employees.employee_id%TYPE;

employee_ids emp_ids := emp_ids(101, 102, 103);

BEGIN

FORALL i IN 1..employee_ids.COUNT

UPDATE employees SET salary = salary * 1.1 WHERE employee_id = employee_ids(i);

END;

CREATE OR REPLACE PACKAGE emp_package IS

PROCEDURE update_salary(p_emp_id NUMBER, p_salary NUMBER);

FUNCTION get_employee_name(p_emp_id NUMBER) RETURN VARCHAR2;

END emp_package;

CREATE OR REPLACE PACKAGE BODY emp_package IS

PROCEDURE update_salary(p_emp_id NUMBER, p_salary NUMBER) IS

BEGIN

UPDATE employees SET salary = p_salary WHERE employee_id = p_emp_id;

END update_salary;

FUNCTION get_employee_name(p_emp_id NUMBER) RETURN VARCHAR2 IS

v_name VARCHAR2(100);

BEGIN

SELECT name INTO v_name FROM employees WHERE employee_id = p_emp_id;

RETURN v_name;

END get_employee_name;

END emp_package;

— PLSQL block to calculate the factorial of a number

DECLARE

num NUMBER := 5;

factorial NUMBER := 1;

BEGIN

FOR i IN 1..num LOOP

factorial := factorial * i;

END LOOP;

DBMS_OUTPUT.PUT_LINE(‘Factorial of ‘ || num || ‘ is ‘ || factorial);

END;

This example demonstrates using of variables, loops, and output statements in PLSQL to compute the factorial of a number.

Also Read: What is GraphQL Query Language – All About API Language

Conclusion

In this PL SQL tutorial, we covered the basics like data types, primary keys, and loops. We also looked at advanced topics such as cursors, collections, exception handling, dynamic SQL, bulk operations, and packages. With practical examples and simple explanations, this PLSQL tutorial helps beginners learn to write strong PLSQL code. By practicing these concepts, you can build powerful database applications that manage large amounts of data and complex rules in Oracle databases. So, keep learning and exploring to master PLSQL and use its features in your projects fully.

Frequently Asked Questions

Q. What is the difference between PLSQL and SQL?

Ans. SQL (Structured Query Language) is used to communicate with and manipulate databases, while PLSQL adds procedural constructs to SQL, allowing for more complex processing within the database.

Q. Is PLSQL from Oracle?

Ans. Yes, PLSQL is Oracle Corporation’s proprietary extension for SQL and is tightly integrated with Oracle databases.

About The Author:

The IoT Academy

The IoT Academy as a reputed ed-tech training institute is imparting online / Offline training in emerging technologies such as Data Science, Machine Learning, IoT, Deep Learning, and more. We believe in making revolutionary attempt in changing the course of making online education accessible and dynamic.

PLSQL Tutorial For Beginners – Learn PL SQL Data Types

Introduction to PLSQL

PL SQL Data Types

Primary Key in PLSQL

Basic Loop in PL SQL

Conclusion

Frequently Asked Questions

8.4 LPA

12900+

26 LPA

500+

91%