Oracle SQL and PL/SQL Lecture Notes

Introduction to PL/SQL

• PL/SQL: Procedural Language extension to SQL.
• Requirement: Basic SQL knowledge is necessary.
• Difference: PL/SQL is used for procedural programming, allowing conditional logic and looping, whereas SQL is used for querying and managing databases.

Agenda

1. Introduction to PL/SQL
2. Use of PL/SQL
3. Architecture of PL/SQL
4. Benefits over SQL
5. Subprograms

What is PL/SQL?

• Definition: Procedural language extension to SQL.
• Usage: Allows for procedural logic, control structures like loops and conditions.
• Oracle Proprietary Language: Similar to TSQL in SQL Server, BCH in Teradata.

Architecture of PL/SQL

• PL/SQL Engine: PL/SQL block is processed by the Oracle database using the PL/SQL engine.
• Execution: Segregates procedural code to the procedural executor and SQL statements to the SQL executor.
• Context Switching: Switching between the procedural engine and SQL engine might cause performance issues.

Benefits of PL/SQL

• Improved Performance: By using procedural logic, performance can be enhanced.
• Modularize Code: Facilitates writing modular and reusable blocks of code.

PL/SQL Block Structure

• Anonymous Block: No name, executed manually, not stored in the database.
• Named Block: Stored in the database as objects; includes procedures, functions, packages, and triggers.
• Basic Structure: Declare -> Begin -> Exception -> End

Anonymous Block Execution

• Variables: Declared using DECLARE section.
• Execution: Code lies between BEGIN and END.
• Exceptions: Optional section for handling errors.

Data Types and Variable Declarations

• Assignment Operator: := used for assigning values.
• Constants: Use CONSTANT keyword to prevent changing values.
• Data Types: Uses SQL and PL/SQL data types.

Control Structures

• If Statements: Used for conditional logic.
• Case Statements: Similar to if, but for multiple conditions.

Loops in PL/SQL

• Simple Loop: Basic looping structure with an exit condition.
• While Loop: Continues as long as a condition is true.
• For Loop: Iterates over a specified range.

Cursors

• Implicit Cursors: Managed by Oracle for single-row queries.
• Explicit Cursors: User-defined cursors for multi-row queries.
• Cursor Attributes: %FOUND, %NOTFOUND, %ROWCOUNT, %ISOPEN.

Procedures and Functions

• Procedures: Subprograms that perform an action, may or may not return a value.
• Functions: Subprograms that return a single value.
• Parameters: IN, OUT, INOUT parameters to pass and retrieve data.

Packages

• Definition: Group logically related functions, procedures, variables, and other PL/SQL constructs.
• Benefits: Modularity, encapsulation, and performance improvement.

Triggers

• DML Triggers: Automatically executed in response to DML events.
• DDL Triggers: Responds to DDL events.
• Instead of Triggers: Used with views to perform DML operations.

Exception Handling

• Predefined Exceptions: Handled by Oracle, e.g., NO_DATA_FOUND, TOO_MANY_ROWS.
• User-Defined Exceptions: Custom exceptions defined by the user.

Collections

• Purpose: Improve performance and store ordered groups of elements.
• Types: VARRAY, Nested Table, Associative Array.
• Use: Bulk operations and complex data manipulation.

Dynamic SQL

• Usage: Execute SQL dynamically at runtime using EXECUTE IMMEDIATE.
• Applications: Dynamic table creations, DDL operations.

Autonomous Transactions

• Definition: Transactions that can operate independently of the main transaction.
• Use Case: Commit/rollback specific operations without affecting the main transaction.

Global Temporary Tables

• Usage: Store temporary data in a session-specific manner.
• Types: On commit delete rows and on commit preserve rows.

These notes cover the essentials of Oracle's SQL and PL/SQL programming, including their uses, architecture, and practical applications in database management and procedural logic.