Data Manipulation in PL/SQL

Podcast

Play an AI-generated podcast conversation about this lesson

Questions and Answers

What does the UPDATE statement do in PL/SQL?

Merges rows from one table into another
Modifies existing rows in a table (correct)
Removes rows from a table
Adds new rows to a table

What is a key characteristic of the MERGE statement?

It can update the same row multiple times
It requires having SELECT privileges on both tables
It only allows insertion of new rows
It selects rows from one table to update or insert into another table (correct)

Which statement is used to delete rows from a table in PL/SQL?

ADD
REMOVE
DELETE (correct)
DROP

Which command would you use to add a new employee record to the COPY_EMP table?

INSERT (D) Signup and view all the answers

In which scenario would you use the UPDATE statement?

To change the salary of a specific group of employees (D) Signup and view all the answers

What must you have to execute the MERGE statement successfully?

INSERT and UPDATE object privileges, plus SELECT privilege in the source table (A) Signup and view all the answers

Which PL/SQL statement would you use to adjust the salary of employees in a specific job category?

UPDATE (B) Signup and view all the answers

What happens when the DELETE statement is executed in PL/SQL?

It permanently removes specified rows from the table (B) Signup and view all the answers

What is the purpose of the INTO clause in a PL/SQL SELECT statement?

To hold the data retrieved from the SELECT statement (C) Signup and view all the answers

Which of the following DML commands is NOT used to modify the data in a PL/SQL block?

REMOVE (D) Signup and view all the answers

When is it important to use a copy of original tables like COPY_EMP?

When you need to avoid modifying existing tables (B) Signup and view all the answers

Which DML statement would you use to add new rows to a table in PL/SQL?

INSERT (C) Signup and view all the answers

What does the MERGE statement do in PL/SQL?

To merge two tables into one using specified conditions (B) Signup and view all the answers

Which statement best describes implicit cursors in PL/SQL?

They are used for all DML statements (C) Signup and view all the answers

What happens if you attempt to modify a table that is marked as necessary for course progression?

An error will prevent the modification (C) Signup and view all the answers

What is the primary function of DML statements in PL/SQL?

To perform tasks on single or multiple rows of data (B) Signup and view all the answers

What is an implicit cursor in PL/SQL?

A cursor that is automatically managed by the Oracle server. (A) Signup and view all the answers

What happens in a MERGE statement when a match is found?

The existing row is updated with new values. (D) Signup and view all the answers

Which of the following is NOT a characteristic of an explicit cursor?

Managed automatically by the Oracle server. (D) Signup and view all the answers

What PL/SQL code structure helps you determine how many rows were processed by a DML statement?

Cursor attributes. (A) Signup and view all the answers

In the DELETE statement given, what variable is used to target the deletion of rows?

v_deptno. (B) Signup and view all the answers

What does the INSERT clause in the MERGE statement do when there is no match?

Inserts new rows into the copy_emp table with data from the employees table. (C) Signup and view all the answers

Which implicit cursor attribute helps to understand the outcome of the last SQL statement executed?

%ROWCOUNT (C) Signup and view all the answers

What is the primary purpose of cursors in PL/SQL?

To retrieve and manipulate data from the database. (C) Signup and view all the answers

What is the purpose of the SQL%FOUND attribute?

It evaluates to TRUE if at least one row was returned. (A) Signup and view all the answers

Which PL/SQL statement uses SQL%ROWCOUNT correctly?

DELETE FROM copy_emp WHERE department_id = 50; (D) Signup and view all the answers

What does the SQL%NOTFOUND attribute indicate?

No rows were returned by the last SQL statement. (D) Signup and view all the answers

After executing an UPDATE statement, how can you confirm the number of rows updated?

Use the SQL%ROWCOUNT attribute. (B) Signup and view all the answers

Which of these attributes should not be used in SQL statements?

None of the above (D) Signup and view all the answers

In which situation would SQL%ROWCOUNT return a value of zero?

If no rows were deleted by the prior DELETE statement. (C) Signup and view all the answers

During what type of operations should cursor attributes be utilized?

In DML operations such as INSERT, UPDATE, and DELETE. (A) Signup and view all the answers

What would the output of DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || ' rows updated.'); indicate if no rows were updated?

0 rows updated. (C) Signup and view all the answers

Study Notes

Manipulating Data in PL/SQL

PL/SQL blocks can include data manipulation language (DML) statements like INSERT, UPDATE, DELETE, and MERGE.
DML statements enable modifying multiple rows in a table.
INSERT adds new rows.
UPDATE modifies existing rows.
DELETE removes rows.
MERGE selects rows from one table and uses them to update and/or insert new rows into another table, based on a condition.
MERGE statements are deterministic—a target row cannot be updated more than once.
MERGE requires INSERT and UPDATE object privileges in the target table, and SELECT privileges in the source table.

Implicit and Explicit Cursors

Implicit cursors are automatically created by Oracle for each SQL data manipulation statement and single-row queries.
Implicit cursors are named "SQL" and are managed by the Oracle server.
Explicit cursors are defined by PL/SQL programmers for queries returning multiple rows.

Cursor Attributes for Implicit Cursors

Cursor attributes are variables used to monitor the results of cursor actions.
They are prefaced with "SQL" and can only be used in PL/SQL, not SQL statements.
SQL%FOUND is a Boolean variable that returns TRUE if the last executed SQL statement returned at least one row.
SQL%NOTFOUND is a Boolean variable that returns TRUE if the last executed SQL statement returned no rows.
SQL%ROWCOUNT is an integer variable that returns the number of rows affected by the last executed SQL statement.

Using Implicit Cursor Attributes

Example 1: After deleting rows from a table based on a condition, the code prints the number of rows deleted using SQL%ROWCOUNT.
Example 2: After updating several rows in a table based on a condition, the code prints the number of rows updated using SQL%ROWCOUNT.
Good Practice Guideline: SQL%ROWCOUNT can be used to report success or failure of DML operations.

Studying That Suits You

Use AI to generate personalized quizzes and flashcards to suit your learning preferences.

Description

Explore the intricacies of data manipulation in PL/SQL, including how to effectively use DML statements like INSERT, UPDATE, DELETE, and MERGE. Delve into the concepts of implicit and explicit cursors and learn how they function in handling SQL queries.