SQL | Procedures in PL/SQL

PL/SQL (Procedural Language/SQL) is Oracle's proprietary extension to SQL, offering procedural programming capabilities within the Oracle database environment. One of the central features of PL/SQL is the ability to create stored procedures.

A stored procedure is a precompiled collection of one or more SQL statements and optional control-of-flow statements. Procedures can take parameters, return values, and can be invoked as part of a larger SQL or PL/SQL block.

Basic Syntax for Creating a Procedure:

CREATE [OR REPLACE] PROCEDURE procedure_name [(parameter1 [mode1] type1, ...)]
IS | AS
    [declaration_section]
BEGIN
    executable_section
[EXCEPTION
    exception_section]
END procedure_name;

OR REPLACE: Optional keyword that allows you to modify an existing procedure.
parameter: Procedures can have zero or more parameters.
mode: This can be IN, OUT, or IN OUT. If omitted, IN is the default.
- IN: The parameter can be referenced within the procedure but not modified.
- OUT: The parameter can have a value assigned within the procedure.
- IN OUT: The parameter can be referenced and have a value assigned.

Example:

Here's a simple procedure that gets the count of employees in a specific department and returns the count via an OUT parameter:

CREATE OR REPLACE PROCEDURE GetEmployeeCount(p_department_id IN NUMBER, p_count OUT NUMBER) 
IS
BEGIN
    SELECT COUNT(*) INTO p_count 
    FROM employees 
    WHERE department_id = p_department_id;
EXCEPTION
    WHEN OTHERS THEN
        p_count := -1; -- Represents an error condition, for example
END GetEmployeeCount;

To call the procedure from a PL/SQL block:

DECLARE
    v_count NUMBER;
BEGIN
    GetEmployeeCount(10, v_count);
    DBMS_OUTPUT.PUT_LINE('Number of employees: ' || v_count);
END;

Key Points:

Encapsulation: Procedures encapsulate logic, allowing reuse and modularization.
Performance: Since procedures are precompiled and stored in the database, they often execute faster than equivalent SQL or PL/SQL blocks.
Exception Handling: PL/SQL procedures offer robust exception handling capabilities using the EXCEPTION block.
Permissions: Database administrators can grant or revoke permissions to execute a procedure to specific users or roles, thus providing an added layer of security.
Overloading: PL/SQL supports overloading of procedures, meaning you can have multiple procedures with the same name in a package, but with different parameter lists.
Cursors: Within PL/SQL procedures, you can use cursors to handle multi-row SQL queries and manipulate the data row by row.

Remember that while the above explains the basic structure and use of procedures, PL/SQL is a vast language with many features, and there's much more to explore beyond basic procedures. If you're working within the Oracle environment, understanding PL/SQL procedures and their capabilities can be immensely beneficial.

How to create procedures in PL/SQL:
- Procedures are named PL/SQL blocks that can be stored and executed.
```
CREATE OR REPLACE PROCEDURE my_procedure
IS
BEGIN
   -- Procedure logic here
END my_procedure;
```

Writing stored procedures in PL/SQL:

Create reusable and modular code using stored procedures.

CREATE OR REPLACE PROCEDURE calculate_salary(emp_id NUMBER)
IS
   salary NUMBER;
BEGIN
   -- Procedure logic to calculate salary
END calculate_salary;

PL/SQL procedure parameters:

Parameters allow passing values into procedures.

CREATE OR REPLACE PROCEDURE greet_person(p_name VARCHAR2)
IS
BEGIN
   DBMS_OUTPUT.PUT_LINE('Hello, ' || p_name || '!');
END greet_person;

Input and output parameters in PL/SQL procedures:

Specify parameters as IN, OUT, or IN OUT for input, output, or both.

CREATE OR REPLACE PROCEDURE calculate_area(
   p_length IN NUMBER,
   p_width IN NUMBER,
   p_area OUT NUMBER)
IS
BEGIN
   p_area := p_length * p_width;
END calculate_area;

Exception handling in PL/SQL procedures:

Handle errors and exceptions gracefully in procedures.

CREATE OR REPLACE PROCEDURE divide_numbers(
   p_num1 NUMBER,
   p_num2 NUMBER)
IS
BEGIN
   IF p_num2 = 0 THEN
      RAISE_APPLICATION_ERROR(-20000, 'Cannot divide by zero.');
   END IF;
   -- Procedure logic to divide numbers
EXCEPTION
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END divide_numbers;

Calling procedures from SQL in PL/SQL:
- Execute procedures from SQL or other PL/SQL blocks.
```
EXEC my_procedure;
```

Creating and executing PL/SQL procedures:

Define and run PL/SQL procedures.

CREATE OR REPLACE PROCEDURE display_message
IS
BEGIN
   DBMS_OUTPUT.PUT_LINE('This is a PL/SQL procedure.');
END display_message;

EXEC display_message;

Nested procedures in PL/SQL:

Call one procedure from another.

CREATE OR REPLACE PROCEDURE outer_procedure
IS
   PROCEDURE inner_procedure
   IS
   BEGIN
      DBMS_OUTPUT.PUT_LINE('Inner procedure called.');
   END inner_procedure;
BEGIN
   inner_procedure;
END outer_procedure;

PL/SQL procedures with cursors:

Use cursors to process result sets in procedures.

CREATE OR REPLACE PROCEDURE fetch_employees
IS
   CURSOR emp_cursor IS
      SELECT employee_id, employee_name FROM employees;
BEGIN
   FOR emp_rec IN emp_cursor
   LOOP
      DBMS_OUTPUT.PUT_LINE('Employee ID: ' || emp_rec.employee_id ||
                           ', Name: ' || emp_rec.employee_name);
   END LOOP;
END fetch_employees;

Returning values from PL/SQL procedures:

Use OUT parameters or return values for data output.

CREATE OR REPLACE PROCEDURE get_employee_name(
   p_emp_id NUMBER,
   p_emp_name OUT VARCHAR2)
IS
BEGIN
   SELECT employee_name INTO p_emp_name
   FROM employees
   WHERE employee_id = p_emp_id;
END get_employee_name;

Managing transactions in PL/SQL procedures:

Use COMMIT and ROLLBACK statements to manage transactions.

CREATE OR REPLACE PROCEDURE update_salary(emp_id NUMBER, new_salary NUMBER)
IS
BEGIN
   UPDATE employees SET salary = new_salary WHERE employee_id = emp_id;
   COMMIT;
EXCEPTION
   WHEN OTHERS THEN
      ROLLBACK;
      DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END update_salary;

Dynamic SQL in PL/SQL procedures:

Use EXECUTE IMMEDIATE for dynamic SQL within procedures.

CREATE OR REPLACE PROCEDURE dynamic_sql_example(p_table_name VARCHAR2)
IS
   v_sql VARCHAR2(100);
BEGIN
   v_sql := 'SELECT * FROM ' || p_table_name;
   EXECUTE IMMEDIATE v_sql;
END dynamic_sql_example;