PL SQL Introduction
PL/SQL, or Procedural Language/Structured Query Language, is a powerful programming language used in Oracle databases to enhance data management and application development. With its seamless integration into the Oracle environment, PL/SQL empowers developers to create efficient, robust, and secure applications that interact with databases. This versatile language allows you to define functions, procedures, triggers, and packages, enabling you to efficiently manage data, automate tasks, and enforce data integrity.
PL SQL Concepts
1. Variables:
- Explanation: Variables are used to store data in PL/SQL.
- Example:
```sql
DECLARE
v_name VARCHAR2(50);
BEGIN
v_name := 'John';
END;
```
2. Constants:
- Explanation: Constants are values that don't change once defined.
- Example:
```sql
DECLARE
PI CONSTANT NUMBER := 3.14159;
BEGIN
DBMS_OUTPUT.PUT_LINE('Value of PI: ' || PI);
END;
```
3. IF Statement:
- Explanation: The `IF` statement is used for conditional execution.
- Example:
```sql
IF x > 10 THEN
DBMS_OUTPUT.PUT_LINE('x is greater than 10');
END IF;
```
4. LOOP:
- Explanation: The `LOOP` construct is used for indefinite looping.
- Example:
```sql
LOOP
-- Code here
EXIT WHEN condition;
END LOOP;
```
5. FOR LOOP:
- Explanation: The `FOR` loop is used for looping a specific number of times.
- Example:
```sql
FOR i IN 1..5 LOOP
-- Code here
END LOOP;
```
6. WHILE LOOP:
- Explanation: The `WHILE` loop is used for looping while a condition is true.
- Example:
```sql
WHILE x < 10 LOOP
-- Code here
END LOOP;
``'
7. Cursors:
- Explanation: Cursors are used to fetch and process rows from a result set.
- Example:
```sql
DECLARE
CURSOR c1 IS SELECT name FROM employees;
BEGIN
FOR rec IN c1 LOOP
DBMS_OUTPUT.PUT_LINE(rec.name);
END LOOP;
END;
```
8. Exceptions:
- Explanation: Exceptions handle errors or exceptional conditions.
- Example:
```sql
BEGIN
-- Code that might raise an exception
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('No data found.');
END;
```
9. Procedures:
- Explanation: Procedures are named PL/SQL blocks that can be called.
- Example:
```sql
CREATE OR REPLACE PROCEDURE greet(name IN VARCHAR2) AS
BEGIN
DBMS_OUTPUT.PUT_LINE('Hello, ' || name);
END greet;
```
10. Functions:
- Explanation: Functions return a value and are used within SQL statements.
- Example:
```sql
CREATE OR REPLACE FUNCTION add_numbers(x NUMBER, y NUMBER) RETURN NUMBER IS
BEGIN
RETURN x + y;
END add_numbers;
```
11. Triggers:
- Explanation: Triggers are PL/SQL blocks that automatically execute when a specific event occurs.
- Example
```sql
CREATE OR REPLACE TRIGGER audit_employee
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
-- Audit employee data
END;
```
12. Packages:
- Explanation: Packages are collections of procedures, functions, and variables
- Example
```sql
CREATE OR REPLACE PACKAGE my_package AS
PROCEDURE proc1;
FUNCTION func1 RETURN NUMBER;
END my_package;
```
These are
some essential concepts in PL/SQL. Each concept serves a specific purpose in
developing Oracle database applications.
