Performing SQL Operations from PL/SQL - Oracle PL/SQL Tutorial

Overview of SQL Support in PL/SQL

By extending SQL, PL/SQL offers a unique combination of power and ease of use. You can manipulate Oracle data flexibly and safely because PL/SQL fully supports all SQL data manipulation statements (except EXPLAIN PLAN), transaction control statements, functions, pseudo columns, and operators. PL/SQL also conforms to the current ANSI/ISO SQL standard.

• Data Manipulation

To manipulate Oracle data you can include DML operations, such as INSERT, UPDATE, and DELETE statements, directly in PL/SQL programs, without any special notation. You can also include the SQL COMMIT statement directly in a PL/SQL program.

• Transaction Control

Oracle is transaction-oriented; that is, Oracle uses transactions to ensure data integrity. A transaction is a series of SQL data manipulation statements that does a logical unit of work
You use the COMMIT, ROLLBACK, SAVEPOINT, and SET TRANSACTION commands to control transactions. COMMIT makes permanent any database changes made during the current transaction. ROLLBACK ends the current transaction and undoes any changes made since the transaction began. SAVEPOINT marks the current point in the processing of a transaction. Used with ROLLBACK, SAVEPOINT undoes part of a transaction. SET TRANSACTION sets transaction properties such as read-write access and isolation level.

SQL Functions

Example: Calling the SQL COUNT Function in PL/SQL

DECLARE
job_count NUMBER;
emp_count NUMBER;
BEGIN
SELECT COUNT(DISTINCT job_id) INTO job_count FROM employees;
SELECT COUNT(*) INTO emp_count FROM employees;
END;
/

SQL Pseudocolumns

PL/SQL recognizes the SQL pseudo columns CURRVAL, LEVEL, NEXTVAL, ROWID, and ROWNUM. However, there are limitations on the use of pseudo columns, including the restriction on the use of some pseudo columns in assignments or conditional tests.

• CURRVAL and NEXTVAL – CURRVAL returns the current value in a specified sequence. A reference to NEXTVAL stores the current sequence number in CURRVAL. NEXTVAL increments the sequence and returns the next value. To get the current or next value in a sequence, use dot notation:

sequence_name.CURRVAL
sequence_name.NEXTVAL

• LEVEL – Use LEVEL with the SELECT CONNECT BY statement to organize rows from a database table into a tree structure.

• ROWID – ROWID returns the rowid (binary address) of a row in a database table. You can use variables of type UROWID to store rowids in a readable format.

• ROWNUM – ROWNUM returns a number indicating the order in which a row was selected from a table.

SQL Operators

• Comparison Operators

• Set Operators

Set operators combine the results of two queries into one result. INTERSECT returns all distinct rows selected by both queries. MINUS returns all distinct rows selected by the first query but not by the second. UNION returns all distinct rows selected by either query. UNION ALL returns all rows selected by either query, including all duplicates.

• Row Operators

Row operators return or reference particular rows. ALL retain duplicate rows in the result of a query or in an aggregate expression. DISTINCT eliminates duplicate rows from the result of a query or from an aggregate expression. PRIOR refers to the parent row of the current row returned by a tree-structured query.

Managing Cursors in PL/SQL

PL/SQL uses implicit and explicit cursors. PL/SQL declares a cursor implicitly for all SQL data manipulation statements, including queries that return only one row. If you want precise control over query processing, you can declare an explicit cursor in the declarative part of any PL/SQL block, PL/SQL subprogram, PL/SQL package. You must declare an explicit cursor for queries that return more than one row.

• Implicit Cursors – Implicit cursors are managed automatically by PL/SQL so you are not required to write any code to handle these cursors. However, you can track information about the execution of an implicit cursor through its cursor attributes.

• Explicit Cursors – When you need precise control over query processing, you can explicitly declare a cursor in the declarative part of any PL/SQL block, subprogram, or package.

Querying Data with PL/SQL

• Selecting At Most One Row: SELECT INTO Statement

If you expect a query to only return one row, you can write a regular SQL SELECT statement with an additional INTO clause specifying the PL/SQL variable to hold the result.

• Selecting Multiple Rows: BULK COLLECT Clause

If you need to bring a large quantity of data into local PL/SQL variables, rather than looping through a result set one row at a time, you can use the BULK COLLECT clause.

• Looping Through Multiple Rows: Cursor FOR Loop

The iterator variable for the FOR loop does not need to be declared in advance. It is a %ROWTYPE record whose field names match the column names from the query, and that exists only during the loop. When you use expressions rather than explicit column names, use column aliases so that you can refer to the corresponding values inside the loop.

• Performing Complicated Query Processing: Explicit Cursors

For full control over query processing, you can use explicit cursors in combination with the OPEN, FETCH, and CLOSE statements.

• Querying Data with PL/SQL: Implicit Cursor FOR Loop

With PL/SQL, it is very simple to issue a query, retrieve each row of the result into a %ROWTYPE record, and process each row in a loop:
■ You include the text of the query directly in the FOR loop.
■ PL/SQL creates a record variable with fields corresponding to the columns of the result set.
■ You refer to the fields of this record variable inside the loop. You can perform tests and calculations, display output, or store the results somewhere else.

• Querying Data with PL/SQL: Explicit Cursor FOR Loops

If you need to reference the same query from different parts of the same procedure, you can declare a cursor that specifies the query, and process the results using a FOR loop.

• Defining Aliases for Expression Values in a Cursor FOR Loop

In a cursor FOR loop, PL/SQL creates a %ROWTYPE record with fields corresponding to columns in the result set. The fields have the same names as corresponding columns in the SELECT list.
The select list might contain an expression, such as a column plus a constant, or two columns concatenated together. If so, use a column alias to give unique names to the appropriate columns.

Using Subqueries

A subquery is a query (usually enclosed by parentheses) that appears within another SQL data manipulation statement. The statement acts upon the single value or set of values returned by the subquery. For example:
■ You can use a subquery to find the MAX(), MIN(), or AVG() value for a column, and use that single value in comparison in a WHERE clause.
■ You can use a subquery to find a set of values, and use these values in an IN or NOT IN comparison in a WHERE clause. This technique can avoid joins.
■ You can filter a set of values with a subquery, and apply other operations like ORDER BY and GROUP BY in the outer query.
■ You can use a subquery in place of a table name, in the FROM clause of a query. This technique lets you join a table with a small set of rows from another table, instead of joining the entire tables.
■ You can create a table or insert into a table, using a set of rows defined by a subquery.

Cursor Expressions

A cursor expression returns a nested cursor. Each row in the result set can contain values as usual, plus cursors produced by subqueries involving the other values in the row. A single query can return a large set of related values retrieved from multiple tables. You can process the result set with nested loops that fetch first from the rows of the result set, than from any nested cursors within those rows.
PL/SQL supports queries with cursor expressions as part of cursor declarations, REF CURSOR declarations and ref cursor variables. You can also use cursor expressions in dynamic SQL queries. Here is the syntax:
 

CURSOR(subquery)

A nested cursor is implicitly opened when the containing row is fetched from the parent cursor. The nested cursor is closed only when:
■ The nested cursor is explicitly closed by the user
■ The parent cursor is re-executed
■ The parent cursor is closed
■ The parent cursor is canceled
■ An error arises during a fetch on one of its parent cursors. The nested cursor is closed as part of the clean-up.

Transaction Processing in PL/SQL

Transaction processing with PL/SQL using SQL COMMIT, SAVEPOINT, and ROLLBACK statements that ensure the consistency of a database.

• COMMIT – The COMMIT statement ends the current transaction, making any changes made during that transaction permanent, and visible to other users.

• ROLLBACK – The ROLLBACK statement ends the current transaction and undoes any changes made during that transaction. If you make a mistake, such as deleting the wrong row from a table, a rollback restores the original data. If you cannot finish a transaction because an exception is raised or a SQL statement fails, a rollback lets you take corrective action and perhaps start over.

• SAVEPOINT – SAVEPOINT names and marks the current point in the processing of a transaction. Savepoints let you roll back part of a transaction instead of the whole transaction. The number of active savepoints for each session is unlimited.

Autonomous Transactions

An autonomous transaction is an independent transaction started by another transaction, the main transaction. Autonomous transactions do SQL operations and commit or rollback, without committing or rolling back the main transaction.
For example, if you write auditing data to a log table, you want to commit the audit data even if the operation you are auditing later fails; if something goes wrong recording the audit data, you do not want the main operation to be rolled back.

Advantages of Autonomous Transactions

Once started, an autonomous transaction is fully independent. It shares no locks, resources, or commit-dependencies with the main transaction. You can log events, increment retry counters, and so on, even if the main transaction rolls back.
Autonomous transactions help you build modular, reusable software components. You can encapsulate autonomous transactions within stored procedures. A calling application does not need to know whether operations done by that stored procedure succeeded or failed.