Easy Notes Of Database Management System unit-4 @Computer Diploma

Description

4.1 Introduction to PL/SQL

PL/SQL (Procedural Language/Structured Query Language) is Oracle Corporation’s procedural extension to SQL. It combines the data manipulation capabilities of SQL with the procedural programming features of a language like C or Java, allowing for the creation of stored procedures, functions, and triggers.

Advantages of PL/SQL:

• Integration with SQL: Seamlessly integrates with SQL, allowing you to use all SQL data manipulation and transaction control statements.
• Performance: Reduces network traffic by sending a block of statements to the database at once instead of one by one.
• Modularity: Code can be organized into reusable units like procedures and functions.
• Error Handling: Provides robust exception handling to manage runtime errors.
• Security: Stored procedures and functions can enhance security by restricting user access to specific data through a predefined interface.

The PL/SQL Block Structure A PL/SQL block is the fundamental unit of a PL/SQL program. It has three main sections:

• DECLARE: (Optional) Declares variables, constants, cursors, and exceptions.
• BEGIN: (Mandatory) Contains the executable SQL and PL/SQL statements.
• EXCEPTION: (Optional) Contains the exception handlers for handling errors.
• END: (Mandatory) Marks the end of the block.

PL/SQL Data Types PL/SQL supports various data types, including:

• Scalar: NUMBER, VARCHAR2, DATE, BOOLEAN.
• Composite: RECORD, TABLE (like an array).
• Reference: REF CURSOR.
• LOB (Large Object): CLOB, BLOB.

Variable and Constant

• Variable: Declared in the DECLARE section to hold values. Syntax: variable_name datatype := initial_value;.
• Constant: A value that cannot be changed after its initialization. Syntax: constant_name datatype := value;.

4.2 Control Structures

PL/SQL offers control structures to manage the flow of execution within a block.

• Conditional Control:
  • IF...THEN...END IF;: Executes a block of code if a condition is true.
  • IF...THEN...ELSE...END IF;: Executes one block of code if true and another if false.
  • IF...THEN...ELSIF...END IF;: Handles multiple conditional checks.
• Iterative Control:
  • LOOP...END LOOP;: An infinite loop that must be exited with an EXIT or EXIT WHEN statement.
  • FOR...LOOP...END LOOP;: Iterates over a range of numbers or a cursor.
  • WHILE...LOOP...END LOOP;: Continues to loop as long as a condition is true.
• Sequential Control:
  • GOTO: Transfers control unconditionally to a labeled statement. Use is discouraged due to potential for confusing code.

4.3 Exception Handling

Exception handling is the process of dealing with runtime errors gracefully.

• Predefined Exceptions: Errors that are automatically raised by Oracle. Examples include:
  • NO_DATA_FOUND: A SELECT INTO statement returns no rows.
  • TOO_MANY_ROWS: A SELECT INTO statement returns more than one row.
  • DUP_VAL_ON_INDEX: An INSERT or UPDATE statement attempts to create a duplicate value in a unique index.
  • ZERO_DIVIDE: A division by zero occurs.
• User-Defined Exceptions: Declared and raised explicitly by the programmer to handle specific business logic errors.

4.4 Cursors

A cursor is a pointer or a handle to a private SQL work area in memory where Oracle stores the result set of a query.

• Implicit Cursors: Automatically created by Oracle for all DML (INSERT, UPDATE, DELETE) and SELECT INTO statements.
• Explicit Cursors: Manually declared and managed by the programmer for queries that return multiple rows.

Steps for Explicit Cursor:

• DECLARE: Define the cursor using the CURSOR keyword. CURSOR cursor_name IS select_statement;.
• OPEN: Execute the query and load the result set into the cursor’s memory area. OPEN cursor_name;.
• FETCH: Retrieve a single row from the result set into variables. FETCH cursor_name INTO var1, var2;.
• CLOSE: Release the memory used by the cursor. CLOSE cursor_name;.

Cursor FOR Loops: A simplified way to loop through a cursor’s result set without manual OPEN, FETCH, and CLOSE statements.

Parameterized Cursors: Cursors that accept arguments, allowing the same cursor to be reused with different search criteria

4.5 Procedures

A procedure is a named PL/SQL block stored in the database. It is a reusable subprogram that can be executed to perform a specific task, often one that involves data manipulation.

• Advantages: Modularity, reusability, and reduced network traffic.
• CREATE: CREATE OR REPLACE PROCEDURE procedure_name (parameters) IS ... BEGIN ... END;
• EXECUTE:
  • EXECUTE procedure_name(arguments);
  • BEGIN procedure_name(arguments); END;
• DELETE: DROP PROCEDURE procedure_name;

4.6 Functions

A function is similar to a procedure but is designed to return a single value. It can be used within SQL statements.

• Advantages: Can be used in SQL queries, promoting reusability and encapsulation of logic.
• CREATE: CREATE OR REPLACE FUNCTION function_name (parameters) RETURN datatype IS ... BEGIN ... END;
• EXECUTE: Can be called from a SELECT statement or within a PL/SQL block.
  • SELECT function_name(arguments) FROM DUAL;
• DELETE: DROP FUNCTION function_name;

4.7 Database Triggers

A database trigger is a stored PL/SQL program that automatically executes (fires) in response to a specific database event (e.g., INSERT, UPDATE, DELETE) on a table or view.

• Use of Database Triggers:
  • Auditing data changes.
  • Enforcing complex business rules.
  • Maintaining derived data.
  • Replicating data.
• Types of Triggers:
  • BEFORE vs. AFTER: Specifies whether the trigger fires before or after the triggering event.
  • FOR EACH ROW vs. FOR EACH STATEMENT: Determines if the trigger fires for each row affected by the DML statement or once per statement.
• CREATE: CREATE OR REPLACE TRIGGER trigger_name AFTER INSERT ON table_name FOR EACH ROW ... BEGIN ... END;
• DELETE: DROP TRIGGER trigger_name;

Keywords of this Topic: PL/SQL, DECLARE, BEGIN, EXCEPTION, END, CURSOR, PROCEDURE, FUNCTION, TRIGGER, RAISE, SQL%ROWCOUNT, SQL%FOUND, SQL%NOTFOUND, FOR, LOOP, WHILE, IF, THEN, ELSEIF, ROWTYPE, TYPE, TABLE.