PLSQL Introduction

What is PL/SQL?

PL/SQL (Procedural Language for SQL) is Oracle's procedural extension of SQL, designed specifically for working within the Oracle database. It adds procedural constructs like loops, conditions, and error handling to SQL. PL/SQL allows developers to combine SQL statements with procedural code, creating reusable units such as procedures, functions, packages, and triggers.

Features of PL/SQL

1. Block Structure: PL/SQL programs are structured into blocks of code, making it easier to manage, maintain, and debug.

·        Anonymous Block: Code that can be executed directly without storing it in the database.

·        Named Block: Procedures, Functions, Packages that are stored in the database.

2. Error Handling: PL/SQL provides robust error handling using exceptions. This feature allows programmers to manage runtime errors effectively.
3. Control Structures: It supports control structures like loops, conditions (IF-THEN-ELSE), and branches that are necessary for procedural logic.
4. Cursors: PL/SQL provides implicit and explicit cursors to retrieve data row-by-row from a result set.
5. Modular Programming: You can break your program into smaller manageable units like procedures, functions, packages, and triggers.
6. Integration with SQL: PL/SQL allows you to execute SQL queries, including DML (Data Manipulation Language) and DDL (Data Definition Language), directly within its code.
7. Triggers: PL/SQL supports triggers that automatically execute code in response to specific events on a table or a view, such as insertions or updates.
8. Packages: It allows grouping procedures, functions, variables, and other PL/SQL code into packages to make code reusable and easier to manage.
9. Portability: PL/SQL programs are portable between Oracle databases on different platforms.

Advantages of PL/SQL

1. Tight Integration with SQL: PL/SQL works natively with SQL, allowing developers to combine procedural and declarative code seamlessly.
2. Increased Performance: By bundling SQL statements into PL/SQL blocks, the number of calls between the application and the database is minimized, reducing network traffic and improving performance.
3. Error Handling: PL/SQL provides robust mechanisms for catching and managing exceptions, which improves the stability and maintainability of applications.
4. Modular Programming: It promotes modularity by enabling the creation of procedures, functions, and packages, which improves code reusability and maintainability.
5. Security: Stored procedures and functions provide an extra layer of security because access to the underlying database can be controlled through controlled execution of PL/SQL code.
6. Support for Complex Business Logic: PL/SQL can handle complex business rules and logic within the database itself, reducing the need for application-side processing and ensuring data consistency.
7. Transaction Management: PL/SQL supports complex transaction management with the ability to COMMIT, ROLLBACK, and SAVEPOINT operations to ensure data integrity.
8. Reduced Network Traffic: Since PL/SQL code executes within the database server, it reduces the need for back-and-forth communication between the client and server, reducing overall network traffic.

Types Of Blocks

·        Anonymous Blocks: These are unnamed PL/SQL code blocks that can be executed without being stored in the database.

·        Named Blocks: These include stored procedures, functions, and packages that are saved in the database for repeated use.

Difference between anonymous blocks and named blocks

Anonymous Block	Named Block (Procedure/Function)
No name (anonymous)	Has a name (Procedure/Function/Package)
Not stored in the database	Stored in the database schema
Executed immediately and discarded after execution	Can be invoked/called multiple times after definition
Not reusable after execution	Reusable and can be invoked by other programs or blocks
Quick, one-time, ad-hoc tasks	Repeated operations, modular programming
No, but can print or perform actions	Yes, functions return value

Structure of PL/SQL Block:

PL/SQL programs are divided into blocks, typically having the following structure:

• Declaration Section: Declare variables, cursors, or exceptions.
• Execution Section: Core logic where SQL statements, control statements, and procedure calls are placed.
• Exception Handling Section: Manage errors or exceptions that may occur during execution.

DECLARE

declaration statements;

BEGIN

executable statements

EXCEPTIONS

exception handling statements

END;

 Example: (set serveroutput on;)

Begin

Dbms_output.put_line(‘welcome to pearldbs’);

End;

Data Types:

Category	Data Types
Numeric	NUMBER, BINARY_INTEGER, PLS_INTEGER, FLOAT, INTEGER, DECIMAL, etc.
Character	CHAR, VARCHAR2, LONG, CLOB, NCLOB, etc.
Date/Time	DATE, TIMESTAMP, INTERVAL YEAR TO MONTH, INTERVAL DAY TO SECOND
Boolean	BOOLEAN
LOBs	BLOB, CLOB, NCLOB, BFILE
Composite	RECORD, Associative Arrays, Nested Tables, Varrays
Cursor	SYS_REFCURSOR
Reference	REF, ROWID, UROWID
User-defined	Objects, User-defined types

Variable Declaration:

Variables

Variables are used to store data that can be changed during the execution of a PL/SQL block or subprogram.

variable_name data_type [:= initial_value];

Constants

Constants are similar to variables, but their value cannot be changed once assigned. Constants must be initialized at the time of declaration.

constant_name CONSTANT data_type := initial_value;

Records

A record is a composite data type that allows you to group multiple related data items of different types together. Each field in the record can hold a different type of data.

1.      %ROWTYPE

record_name table_name%ROWTYPE;

 

2.      User-Defined Record

TYPE record_type IS RECORD (

   field1 data_type,

   field2 data_type,

   fieldN data_type

);

record_name record_type;

• Variables are mutable and hold a single data type.
• Constants are immutable once assigned a value.
• Records allow grouping of different fields (like a table row) and can hold multiple data types.

Example: (set serveroutput on;)

DECLARE

-- Constant declaration

company_name CONSTANT VARCHAR2(50) := 'ABC Corp';

-- Variable declaration

bonus NUMBER := 500;

-- Declaring a custom record type

TYPE employee_bonus_rec IS RECORD (

emp_name VARCHAR2(50),

emp_bonus NUMBER

);

employee_bonus employee_bonus_rec;  -- Record variable of type employee_bonus_rec

-- %ROWTYPE record declaration for the "employees" table

employee_record employees%ROWTYPE;

BEGIN

-- Fetching data into the %ROWTYPE record

SELECT * INTO employee_record FROM employees WHERE employee_id = 101;  -- Assuming employee_id 101 exists in the "employees" table   

--Assigning values to the custom record (user-defined record)

employee_bonus.emp_name := employee_record.first_name || ' ' || employee_record.last_name;

employee_bonus.emp_bonus := bonus;    -- Displaying the values

DBMS_OUTPUT.PUT_LINE('Company: ' || company_name);

DBMS_OUTPUT.PUT_LINE('Employee: ' || employee_bonus.emp_name);

DBMS_OUTPUT.PUT_LINE('Salary: ' || employee_record.salary);

DBMS_OUTPUT.PUT_LINE('Bonus: ' || employee_bonus.emp_bonus);

END;

Sample Table and Data:

CREATE TABLE employees (

   employee_id NUMBER PRIMARY KEY,

   first_name VARCHAR2(50),

   last_name VARCHAR2(50),

   salary NUMBER,

   hire_date DATE

);

 

INSERT INTO employees (employee_id, first_name, last_name, salary, hire_date)

VALUES (1001, 'John', 'Doe', 5000, TO_DATE('2022-01-15', 'YYYY-MM-DD'));

 

INSERT INTO employees (employee_id, first_name, last_name, salary, hire_date)

VALUES (1002, 'Jane', 'Smith', 6000, TO_DATE('2021-06-20', 'YYYY-MM-DD'));

 

INSERT INTO employees (employee_id, first_name, last_name, salary, hire_date)

VALUES (1003, 'Alice', 'Johnson', 5500, TO_DATE('2023-03-10', 'YYYY-MM-DD'));

 

INSERT INTO employees (employee_id, first_name, last_name, salary, hire_date)

VALUES (1004, 'Bob', 'Brown', 7000, TO_DATE('2020-11-25', 'YYYY-MM-DD'));

 

INSERT INTO employees (employee_id, first_name, last_name, salary, hire_date)

VALUES (1005, 'Carol', 'Davis', 6500, TO_DATE('2019-09-12', 'YYYY-MM-DD'));