Using SQL Commands in PL/SQL

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

1. 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;
   

2. 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;