Scribd
Upload
19 views3 pages

PLSQL Basics

PL/SQL is Oracle's procedural extension for SQL, enabling row-by-row processing and the creation of procedures and triggers. It includes implicit and explicit cursors for handling SQL query results, procedures for performing actions with parameters, and triggers that execute automatically in response to database events. Examples illustrate the use of explicit cursors, procedures, and triggers in PL/SQL programming.

Uploaded by

mayurbenke09
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
19 views3 pages

PLSQL Basics

PL/SQL is Oracle's procedural extension for SQL, enabling row-by-row processing and the creation of procedures and triggers. It includes implicit and explicit cursors for handling SQL query results, procedures for performing actions with parameters, and triggers that execute automatically in response to database events. Examples illustrate the use of explicit cursors, procedures, and triggers in PL/SQL programming.

Uploaded by

mayurbenke09
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

PL/SQL Overview

PL/SQL (Procedural Language/SQL) is Oracle's extension for SQL, combining SQL with procedural

features found in modern programming languages.

1. Cursors

A cursor allows row-by-row processing of SQL query results.

Types:

- Implicit Cursor: Automatically created for single-row queries.

- Explicit Cursor: Manually created for multi-row queries.

Example - Explicit Cursor:

DECLARE

CURSOR emp_cursor IS

SELECT employee_id, first_name FROM employees WHERE department_id = 10;

v_id employees.employee_id%TYPE;

v_name employees.first_name%TYPE;

BEGIN

OPEN emp_cursor;

LOOP

FETCH emp_cursor INTO v_id, v_name;

EXIT WHEN emp_cursor%NOTFOUND;

DBMS_OUTPUT.PUT_LINE('ID: ' || v_id || ', Name: ' || v_name);

END LOOP;

CLOSE emp_cursor;
END;

2. Procedures

Procedures are named PL/SQL blocks that perform actions and may accept input/output

parameters.

Example - Procedure:

CREATE OR REPLACE PROCEDURE greet_employee(p_id IN NUMBER) IS

v_name employees.first_name%TYPE;

BEGIN

SELECT first_name INTO v_name FROM employees WHERE employee_id = p_id;

DBMS_OUTPUT.PUT_LINE('Hello, ' || v_name);

EXCEPTION

WHEN NO_DATA_FOUND THEN

DBMS_OUTPUT.PUT_LINE('Employee not found.');

END;

Calling the Procedure:

BEGIN

greet_employee(100);

END;

3. Triggers

Triggers are PL/SQL blocks that execute automatically in response to table/view events.

Types:

- BEFORE / AFTER
- INSERT / UPDATE / DELETE

- ROW or STATEMENT level

Example - AFTER INSERT Trigger:

CREATE OR REPLACE TRIGGER trg_after_insert_emp

AFTER INSERT ON employees

FOR EACH ROW

BEGIN

DBMS_OUTPUT.PUT_LINE('New employee added: ' || :NEW.first_name);

END;

You might also like