PL/SQL: Integrating SQL Commands

PL/SQL is a powerful extension of SQL that allows procedural programming alongside SQL queries. It enables SQL and PL/SQL commands to be used together seamlessly within a PL/SQL block without requiring separate worksheets.

DML and Transaction Control in PL/SQL

DML operations (INSERT, UPDATE, DELETE) and transaction control statements (COMMIT, ROLLBACK) can be used within PL/SQL.
DDL (Data Definition Language) and DCL (Data Control Language) commands (e.g., CREATE TABLE, GRANT) cannot be used directly in PL/SQL but can be executed using Dynamic SQL.

Transactions in PL/SQL

A transaction starts when a DML statement is executed and ends with a COMMIT, ROLLBACK, or system failure.
PL/SQL allows multiple transactions within a single block.
End of a PL/SQL block does not end a transaction unless explicitly committed or rolled back.

Example

DECLARE
    v_name VARCHAR2(50);
    v_salary employees.salary%TYPE;
BEGIN
    SELECT last_name, salary
    INTO v_name, v_salary
    FROM employees
    WHERE employee_id = 100;

    DBMS_OUTPUT.PUT_LINE('The salary of ' || v_name || ' is: ' || v_salary);
END;

Handling Errors in SELECT INTO

If the query returns more than one row, an error occurs (ORA-01422: exact fetch returns more than requested number of rows).
To avoid errors, ensure the WHERE clause returns only one row or use CURSORS.

Naming Conventions & Ambiguities

Variable names should not match column, table, or function names to avoid confusion.
In a SELECT statement, variables take precedence over functions and table names.
In a WHERE clause, column names take precedence over variables.

DECLARE
    salary employees.salary%TYPE; -- Variable has same name as column
BEGIN
    SELECT salary INTO salary FROM employees WHERE employee_id = 130;
END;

Using Database Sequences in PL/SQL

Sequences are used to generate auto-incremented unique values, especially for primary keys like employee_id.
Unlike some databases that provide built-in auto-increment features, Oracle uses sequences for this purpose.
Sequences ensure uniqueness but do not guarantee consecutive numbers.

Creating a Sequence:

CREATE SEQUENCE employee_id_seq START WITH 207 INCREMENT BY 1;

NEXTVAL: Retrieves the next value of the sequence. → employee_id_seq.NEXTVAL
CURRVAL: Retrieves the last generated sequence value. → employee_id_seq.CURRVAL

Using Sequences in PL/SQL

Traditional Method (Before Oracle 11g)

 DECLARE
     v_seq_num NUMBER;
 BEGIN
     SELECT employee_id_seq.NEXTVAL INTO v_seq_num FROM DUAL;
     DBMS_OUTPUT.PUT_LINE('Generated Sequence Value: ' || v_seq_num);
 END;

New Method (Oracle 11g and Later)

Sequences can be directly assigned to variables or used inside statements.

No need for SELECT INTO and DUAL table.

  DECLARE
      v_seq_num NUMBER := employee_id_seq.NEXTVAL;
  BEGIN
      DBMS_OUTPUT.PUT_LINE('Generated Sequence Value: ' || v_seq_num);
  END;

Even if NEXTVAL is called multiple times in a single INSERT, it increments only once per statement.

  BEGIN
    FOR i IN 1..10 LOOP
      INSERT INTO employees_copy 
        (employee_id,first_name,last_name,email,hire_date,job_id,salary)
      VALUES 
        (employee_id_seq.nextval,'employee#'||employee_id_seq.nextval,'temp_emp','abc@xmail.com',sysdate,'IT_PROG',1000);
    END LOOP;
  END;

Using SQL Commands in PL/SQL : 2