Oracle PL/SQL

PROCEDURE

A procedure in PL/SQL is a named PL/SQL block that performs a specific task or a set of tasks. Procedures can have input parameters, output parameters, or both. They are used to encapsulate a series of SQL and PL/SQL statements that can be executed as a single unit.

Example of a simple PL/SQL procedure:

CREATE OR REPLACE PROCEDURE procedure_name(param1 datatype, param2 datatype, ...) IS
BEGIN
  -- Procedure logic here
END;

FUNCTION

A function in PL/SQL is similar to a procedure, but it returns **a single value **. Functions are typically used to perform computations and return results that can be used in SQL queries or within other PL/SQL blocks. Functions are also used to encapsulate logic and promote code reusability.

Example of a simple PL/SQL function:

CREATE OR REPLACE FUNCTION function_name(param1 datatype, param2 datatype, ...)
RETURN return_datatype IS
  result return_datatype;
BEGIN
  -- Function logic here
  RETURN result;
END;

PACKAGE

A package in PL/SQL is a collection of related procedures, functions, variables, constants, and cursors organized into a single unit.

Packages are used to group related functionality together, making it easier to manage and maintain large PL/SQL applications.

A package has two main components: package specification (header) and package body. The specification declares the public elements that are accessible to other PL/SQL blocks, while the body defines the implementation of those elements.

SQL Syntax:

CREATE OR REPLACE PACKAGE package_name AS
  -- Declarations of procedures, functions, etc.
END;

CREATE OR REPLACE PACKAGE BODY package_name AS
  -- Implementations of procedures, functions, etc.
END;

Example of a simple PL/SQL package:

CREATE OR REPLACE PACKAGE my_package AS
  FUNCTION function1(param1 NUMBER) RETURN NUMBER;
  PROCEDURE procedure1(param1 NUMBER);
  -- other declarations
END;

CREATE OR REPLACE PACKAGE BODY my_package AS
  FUNCTION function1(param1 NUMBER) RETURN NUMBER IS
  BEGIN
    -- Function logic here
    RETURN param1 * 2;
  END;

  PROCEDURE procedure1(param1 NUMBER) IS
  BEGIN
    -- Procedure logic here
    DBMS_OUTPUT.PUT_LINE('Result: ' || param1);
  END;
  -- other implementations
END;

CURSOR

A cursor in PL/SQL is a temporary work area that holds the results of a SELECT statement. Cursors allow you to process the individual rows returned by a query one by one.

There are two types of cursors: implicit and explicit.

Implicit cursors are automatically created for all SQL statements in PL/SQL, while

explicit cursors are **user-defined and provide more control over the query execution and result retrieval.

DECLARE
  cursor_name CURSOR FOR SELECT column1, column2, ... FROM table_name;
  variable1 datatype;
  variable2 datatype;
  ...
BEGIN
  OPEN cursor_name;
  LOOP
    FETCH cursor_name INTO variable1, variable2, ...;
    EXIT WHEN cursor_name%NOTFOUND;
    -- Process the data in variables
  END LOOP;
  CLOSE cursor_name;
END;

Example of a simple explicit cursor in PL/SQL:

DECLARE
  CURSOR c_emp IS
    SELECT employee_id, first_name, last_name FROM employees;
  emp_rec c_emp%ROWTYPE;
BEGIN
  OPEN c_emp;
  LOOP
    FETCH c_emp INTO emp_rec;
    EXIT WHEN c_emp%NOTFOUND;
    -- Process the data in emp_rec
    DBMS_OUTPUT.PUT_LINE(emp_rec.first_name || ' ' || emp_rec.last_name);
  END LOOP;
  CLOSE c_emp;
END;

EXCEPTIONS

Oracle Exception page

PL/SQL exceptions are used to handle errors or exceptional conditions that occur during the execution of a program.

BEGIN
  -- Code that may raise an exception
EXCEPTION
  WHEN exception_name1 THEN
    -- Exception handling for exception_name1
  WHEN exception_name2 THEN
    -- Exception handling for exception_name2
  ...
  WHEN OTHERS THEN
    -- Default exception handling
END;

Example of using exceptions in PL/SQL:

DECLARE
  num1 NUMBER := 10;
  num2 NUMBER := 0;
  result NUMBER;
BEGIN
  IF num2 = 0 THEN
    RAISE ZERO_DIVIDE;
  END IF;
  
  result := num1 / num2;
EXCEPTION
  WHEN ZERO_DIVIDE THEN
    DBMS_OUTPUT.PUT_LINE('Error: Division by zero');
END;

VIEWS

A view in PL/SQL is a virtual table that is based on the result of a SELECT query. Views allow you to simplify complex queries, encapsulate business logic, and present a customized or summarized view of data from one or more underlying tables.

Views DO NOT store data themselves; they retrieve the data from the underlying tables whenever they are queried.

They provide an additional layer of abstraction, enhancing security and ease of use.

CREATE [OR REPLACE] [FORCE | NOFORCE] VIEW view_name [(column1, column2, ...)]
AS
SELECT column1, column2, ...
FROM table1, table2, ...
WHERE condition;

Example of creating a view in PL/SQL:

CREATE OR REPLACE VIEW my_view AS
SELECT employee_id, first_name, last_name
FROM employees
WHERE department_id = 10;

--Call from SQL
SELECT * from my_view;

MATERIALIZED VIEWS

MATERIALIZED VIEWS DETAILS

A materialized view is a database object that stores the results of a query in a physical table. Unlike regular views, materialized views actually contain the data retrieved from the query.

CREATE MATERIALIZED VIEW mv_name [REFRESH [FAST | COMPLETE | FORCE] ON COMMIT]
AS
SELECT column1, column2, ...
FROM table1, table2, ...
WHERE condition;

TRIGGER

TRIGGER DETAILS

A trigger is a named PL/SQL block associated with a table, view, schema, or the database itself and automatically executed when specific events occur. Triggers are used to enforce business rules, maintain data integrity, and perform automatic actions.

CREATE [OR REPLACE] TRIGGER trigger_name
[BEFORE | AFTER] {INSERT | UPDATE | DELETE | DDL_EVENT}
[ON table_or_view_name | DATABASE]
[FOR EACH ROW | FOR EACH STATEMENT]
[WHEN (condition)]
DECLARE
  -- Declaration section (optional)
BEGIN
  -- Trigger body (PL/SQL code)
EXCEPTION
  -- Exception handling (optional)
END;