Can you explain the differences between PL/SQL and SQL, and provide an example of when each would be used in an Oracle database environment?

PL/SQL vs SQL: Understanding the Differences

PL/SQL and SQL are both integral parts of the Oracle database environment, but they serve different purposes and are used in different scenarios.

SQL (Structured Query Language)

SQL is a standard language used to interact with relational databases. It is primarily used for querying and manipulating data within a database. SQL statements are used to perform tasks such as retrieving data, inserting new records, updating existing records, and deleting data from tables. SQL is essential for database administrators, developers, and users to manage the database effectively.

Example of SQL Usage:

SELECT * FROM employees WHERE department = 'IT';

In this example, the SQL query retrieves all records from the 'employees' table where the department is 'IT'.

PL/SQL (Procedural Language/SQL)

PL/SQL is an extension of SQL that allows developers to create procedural logic within SQL statements. It enables the writing of complex programs that can be executed within the Oracle database. PL/SQL is used for developing stored procedures, functions, triggers, and packages that provide more advanced functionality and automation within the database environment.

Example of PL/SQL Usage:

CREATE OR REPLACE PROCEDURE calculate_bonus (emp_id IN NUMBER) AS
   bonus NUMBER;
BEGIN
   SELECT salary * 0.1 INTO bonus FROM employees WHERE employee_id = emp_id;
   DBMS_OUTPUT.PUT_LINE('Bonus for employee ' || emp_id || ' is: ' || bonus);
END;
/

In this example, a PL/SQL stored procedure 'calculate_bonus' is created to calculate the bonus for a specific employee based on their salary.

In summary, SQL is used for querying and manipulating data, while PL/SQL is used for creating procedural logic and automation within the Oracle database environment.