Embedding SQL Statements for CRUD Operations

Create, Read, Update, and Delete (CRUD) are the four essential functionalities for managing data in a database. PL/SQL can be seamlessly integrated with DML statements within its code blocks, enabling powerful combinations of procedural programming and SQL statements. 

SELECT INTO Statement

The SELECT INTO statement in a PL/SQL block is used to retrieve data from database tables and store it in a PL/SQL scalar variable or record. General syntax of the statement is as follows. Notice the inclusion of the reserved word INTO: in a PL/SQL block, it specifies the PL/SQL variable or record that will hold the data retrieved by the SELECT statement and is required. 

Here's an example:

It is important to ensure that the data types of the retrieved values match those of their target PL/SQL variables. Additionally, SELECT INTO statements in a PL/SQL block must define a query that returns exactly one row-no more, no less-or an exception will be raised, causing the block's execution to halt. This isn't necessarily an undesirable situation, but it is something you must consider. In the example shown above, observe that the WHERE clause is applied on the primary key column (employee_id), making sure one and only one row returns by the execution of the query. The intention behind this is simple: a SELECT statement executes its query on the database and stores all of its results in the scalar variables specified in the INTO clause.

You can also specify a record in the INTO clause, which is useful for retrieving multiple column values from a single row. Here's an example:

INSERT Statement

After processing, PL/SQL variables and/or records are often inserted into an existing database table. To insert PL/SQL elements into a database table, use the INSERT statement in the executable section with the following syntax:

Before moving on to an example, let's create a pseudo table^[1] by copying the hr.employees table from the sample schema to avoid data disruption:

Now, let's consider the following PL/SQL block. In the declaration section, vr_new_employee is declared as a record with the same structure as the pseudo_employees table. It is then initialized in the executable section with values assigned to some attributes.

DECLARE
    -- Declare a record variable to contain the new employee
    vr_new_employee pseudo_employees%ROWTYPE;

BEGIN
    -- Initialize the record variable
    vr_new_employee.employee_id := 217;
    vr_new_employee.last_name := 'Lee';
    vr_new_employee.email := 'alee';
    vr_new_employee.hire_date := '13-NOV-23';
    vr_new_employee.job_id := 'IT_PROG';
    
    -- Insert the record into pseudo_employees table
    INSERT INTO pseudo_employees VALUES vnt_new_employee;
END;
/

After executing this code block, a new row is inserted into the destination table.

 

UPDATE and DELETE Statement

The UPDATE statement modifies existing rows from a PL/SQL block. It allows you to change one or more column values. Syntax of the UPDATE statement is as follows:

For example, let's update the row we inserted earlier:

SQL's DELETE statement deletes a specific row defined in its WHERE clause. The general syntax of the DELETE statement is as follows:

For example:

Implicit Cursor and Cursor Attributes

SQL is a set-based language, meaning it operates on multiple row values simultaneously as a set. For example, the SQL expression column1 + column2 will result in a new column containing multiple rows, each representing the sum of corresponding column1 and column2 values. As a result, a DML statement typically affects more than one row per execution.

In Oracle database, a cursor acts as a pointer that references a specific memory area called PRIVATE SQL, where the result set of a query is temporarily stored. Used in a PL/SQL code block, it provides a mechanism to access and process the result set row by row. There are two different types of cursors: implicit and explicit. Explicit cursors are those that are explicitly defined by an Oracle developer in the declaration section. This topic will be discussed later with some more details.

Implicit cursors, on the other hand, are automatically created by Oracle for every DML statement executed. While it may be unnecessary to understand how Oracle manages them internally, implicit cursors offer several attributes that are often useful in database application development.

Here is the list of attributes for implicit cursors:

• SQL%FOUND: Returns TRUE if there is one or more rows are in the result set. Otherwise, FALSE.
• SQL%NOTFOUND: Returns TRUE if there is no row in the result set. Otherwise, FALSE.
• SQL%ROWCOUNT: Returns the number of rows in the result set. If there is no row, returns 0.

For example:

---

^[1] Usage of DDL statements is prohibited in the PL/SQL code block. DDL statements deal with schema changes, such as creating or dropping tables, indexes, or users, which can have a significant impact on the database structure. On the other hand, PL/SQL focuses on data manipulations through DML statements, which work with existing database objects. ^↩