SQL And PLSQL OVERVIEW

Here is the **HTML version** of your formatted “SQL & PL/SQL Overview” document. It includes structured sections, syntax-highlighted code blocks, and well-organized content. --- ### ✅ \[Downloadable HTML File Option] If you'd like, I can also give you a `.html` file to upload directly to your Blogspot or view offline. Just let me know. --- ### 📄 Full HTML Code: ```html

SQL & PL/SQL Overview

🔖 Audience

This tutorial is for software professionals who want to learn PL/SQL in a simple and practical way. After finishing this, you'll understand key PL/SQL concepts and be ready to advance further in database development.

✉️ Prerequisites

• What a database is
• Programming fundamentals
• SQL basics

Tip: Knowledge of any programming language is helpful.

What is a Database?

There are two main types of databases:

1. Relational Databases (RDBMS)
   • Examples: Oracle DB, MySQL, SQL Server, IBM DB2
2. Non-Relational Databases (NoSQL)
   • Examples: MongoDB, Cassandra, Apache HBase

Oracle DB Versions

Common versions: 8i, 10g, 12c

Key Features

• Manageability
• High Availability
• Performance
• Security (Roles and Grants)

SQL (Structured Query Language)

• Pronounced as "SEQUEL"
• Used for accessing RDBMS
• Set-oriented: handles multiple records at once
• Case-insensitive
• Does not support control structures
• Can be embedded in other languages

📒 SQL Command Types

1. DDL (Data Definition Language)

Used to define and manage database objects like tables and views.

• CREATE: Create database objects
• ALTER: Modify existing objects
• DROP: Delete objects
• TRUNCATE: Remove all rows quickly
• COMMENT: Add descriptive notes

CREATE TABLE NEWTAB (SNO NUMBER(5), SNAME VARCHAR2(10));
ALTER TABLE NEWTAB ADD (ID NUMBER(5));
DROP TABLE NEWTAB;

2. Flashback & Recycle Bin

• FLASHBACK TABLE: Restore dropped table
• PURGE: Permanently delete from recycle bin

SQL Data Types

Data Type	Description
NUMBER	Stores numeric values (up to 38 digits)
CHAR	Fixed-length strings (up to 2000 bytes)
VARCHAR2	Variable-length strings (up to 4000 bytes)
DATE	Stores date/time values
TIMESTAMP	Includes fractional seconds
TIMESTAMP WITH TIMEZONE	Stores time zone info
LONG, RAW	Stores large or binary data
CLOB, BLOB, BFILE	Stores text, images, or external files
ROWID	Stores physical row address
NCLOB	Supports multilingual data

PL/SQL Overview

PL/SQL is Oracle's procedural extension to SQL that allows for programming logic and control structures.

Key Features

• Modular programming
• Supports loops, IF statements
• Error handling with EXCEPTION block
• Tightly integrated with SQL

Block Structure

DECLARE
BEGIN
  -- code
EXCEPTION
END;

Examples

Print Message:

BEGIN
  DBMS_OUTPUT.PUT_LINE('Welcome to PL/SQL');
END;

Using Constants:

DECLARE
  PI CONSTANT NUMBER(5,3) := 3.142;
  radius NUMBER := 5;
  area NUMBER;
BEGIN
  area := PI * radius * radius;
  DBMS_OUTPUT.PUT_LINE('Area: ' || area);
END;

Bind Variables

Allow runtime input for flexible programs.

DECLARE
  A NUMBER := &n;
  B NUMBER := &m;
BEGIN
  DBMS_OUTPUT.PUT_LINE(A + B);
END;

%TYPE and %ROWTYPE

%TYPE: Assigns data type from table column to variable.

vname emp.ename%TYPE;

%ROWTYPE: Assigns all column types from a table.

vrow emp%ROWTYPE;

Nested Blocks

BEGIN
  DECLARE
    v_inner NUMBER := 10;
  BEGIN
    DBMS_OUTPUT.PUT_LINE(v_inner);
  END;
END;

Lexical Units in PL/SQL

• Identifiers: Names of variables, tables, etc.
• Reserved Words: BEGIN, END, etc.
• Delimiters: Special characters like `;`
• Literals: Fixed values like 'Hello', 123
• Comments: -- or /* */

``` --- Would you like this in a downloadable `.html` file? I can provide it directly.