The Project | About Us | Contribute | Donations | License
HOME My courses Completed
◄ Previous
◄

SQL Statements in PL/SQL

Next ►
►

In this section, we will explore how to use SQL statements within PL/SQL. This is a fundamental skill, as PL/SQL is designed to extend SQL's capabilities by adding procedural constructs. By the end of this section, you will be able to embed SQL statements in your PL/SQL code to interact with the database effectively.

Key Concepts

  1. Embedding SQL in PL/SQL: Learn how to include SQL statements within PL/SQL blocks.
  2. Data Manipulation Language (DML): Understand how to use INSERT, UPDATE, DELETE, and SELECT statements.
  3. Transaction Control: Learn how to manage transactions using COMMIT, ROLLBACK, and SAVEPOINT.
  4. Data Definition Language (DDL): Understand how to execute DDL statements like CREATE, ALTER, and DROP within PL/SQL.

Embedding SQL in PL/SQL

PL/SQL allows you to embed SQL statements directly within its blocks. Here’s a simple example:

BEGIN
    -- SQL statement embedded in PL/SQL
    INSERT INTO employees (employee_id, first_name, last_name, email, hire_date, job_id)
    VALUES (1001, 'John', 'Doe', '[email protected]', SYSDATE, 'IT_PROG');
END;
/

Explanation:

  • BEGIN...END: This marks the start and end of a PL/SQL block.
  • INSERT INTO: This is a SQL statement that inserts a new row into the employees table.

Data Manipulation Language (DML)

INSERT Statement

The INSERT statement is used to add new rows to a table.

BEGIN
    INSERT INTO employees (employee_id, first_name, last_name, email, hire_date, job_id)
    VALUES (1002, 'Jane', 'Smith', '[email protected]', SYSDATE, 'HR_REP');
END;
/

UPDATE Statement

The UPDATE statement modifies existing rows in a table.

BEGIN
    UPDATE employees
    SET email = '[email protected]'
    WHERE employee_id = 1002;
END;
/

DELETE Statement

The DELETE statement removes rows from a table.

BEGIN
    DELETE FROM employees
    WHERE employee_id = 1002;
END;
/

SELECT INTO Statement

The SELECT INTO statement retrieves data from the database and stores it into PL/SQL variables.

DECLARE
    v_first_name employees.first_name%TYPE;
    v_last_name employees.last_name%TYPE;
BEGIN
    SELECT first_name, last_name
    INTO v_first_name, v_last_name
    FROM employees
    WHERE employee_id = 1001;
    
    DBMS_OUTPUT.PUT_LINE('Employee Name: ' || v_first_name || ' ' || v_last_name);
END;
/

Explanation:

  • DECLARE: This section is used to declare variables.
  • v_first_name, v_last_name: Variables to store the retrieved data.
  • SELECT INTO: Retrieves data from the employees table and stores it in the declared variables.
  • DBMS_OUTPUT.PUT_LINE: Outputs the retrieved data.

Transaction Control

COMMIT

The COMMIT statement saves all changes made during the current transaction.

BEGIN
    UPDATE employees
    SET job_id = 'SA_REP'
    WHERE employee_id = 1001;
    
    COMMIT;
END;
/

ROLLBACK

The ROLLBACK statement undoes all changes made during the current transaction.

BEGIN
    UPDATE employees
    SET job_id = 'SA_REP'
    WHERE employee_id = 1001;
    
    ROLLBACK;
END;
/

SAVEPOINT

The SAVEPOINT statement sets a point within a transaction to which you can later roll back.

BEGIN
    SAVEPOINT before_update;
    
    UPDATE employees
    SET job_id = 'SA_REP'
    WHERE employee_id = 1001;
    
    -- Rollback to the savepoint
    ROLLBACK TO before_update;
END;
/

Data Definition Language (DDL)

DDL statements can also be executed within PL/SQL using the EXECUTE IMMEDIATE statement.

CREATE TABLE

BEGIN
    EXECUTE IMMEDIATE 'CREATE TABLE test_table (id NUMBER, name VARCHAR2(50))';
END;
/

ALTER TABLE

BEGIN
    EXECUTE IMMEDIATE 'ALTER TABLE test_table ADD (email VARCHAR2(100))';
END;
/

DROP TABLE

BEGIN
    EXECUTE IMMEDIATE 'DROP TABLE test_table';
END;
/

Practical Exercises

Exercise 1: Insert and Select

  1. Task: Insert a new employee into the employees table and then retrieve and display the employee's name.
  2. Solution:
DECLARE
    v_first_name employees.first_name%TYPE;
    v_last_name employees.last_name%TYPE;
BEGIN
    INSERT INTO employees (employee_id, first_name, last_name, email, hire_date, job_id)
    VALUES (1003, 'Alice', 'Johnson', '[email protected]', SYSDATE, 'IT_PROG');
    
    SELECT first_name, last_name
    INTO v_first_name, v_last_name
    FROM employees
    WHERE employee_id = 1003;
    
    DBMS_OUTPUT.PUT_LINE('Employee Name: ' || v_first_name || ' ' || v_last_name);
END;
/

Exercise 2: Update and Rollback

  1. Task: Update an employee's job ID and then roll back the change.
  2. Solution:
BEGIN
    UPDATE employees
    SET job_id = 'SA_REP'
    WHERE employee_id = 1003;
    
    ROLLBACK;
END;
/

Common Mistakes and Tips

  • Forgetting to COMMIT: Always remember to commit your changes if you want them to be permanent.
  • Incorrect Data Types: Ensure that the data types of your variables match the columns in the database.
  • Handling NULL Values: Be cautious when dealing with NULL values in your SQL statements.

Conclusion

In this section, you learned how to embed SQL statements within PL/SQL blocks, manipulate data using DML statements, control transactions, and execute DDL statements. These skills are essential for interacting with the database effectively using PL/SQL. In the next section, we will delve deeper into cursors, which are crucial for handling query results in PL/SQL.

◄ Previous
◄
Next ►
►
The Project | About Us | Contribute | Donations | License
© Copyright 2024. All rights reserved