
Oracle PL/SQL by Example- P9
Mô tả tài liệu
Tham khảo tài liệu 'oracle pl/sql by example- p9', công nghệ thông tin, cơ sở dữ liệu phục vụ nhu cầu học tập, nghiên cứu và làm việc hiệu quả
Tóm tắt nội dung
. Use collections of records
PL/SQL also lets you define a collection of records (such as an
associative array whose element type is a cursor-based record, as shown in the following
name_tab(v_counter).first_name);
name_tab(v_counter).last_name);
name_tab(v_counter).first_name
name_tab(v_counter).last_name
Notice that to reference each row of the array, you use the counter variable, just like in all previ-
However, because each row of this table is a record, you must also reference indi-
16.3.1 Use Collections of Records
Instead of using an associative array, use a nested table.
name_tab(v_counter).first_name);
name_tab(v_counter).last_name);
In the preceding script,name_tab is declared as a nested table.
name_tab(v_counter).first_name);
name_tab(v_counter).last_name);
Instead of using a cursor-based record, use a
TYPE student_rec_type IS RECORD
TYPE name_type IS TABLE OF student_rec_type
name_tab(v_counter).enrollments := name_rec.total;
name_tab(v_counter).first_name);
name_tab(v_counter).last_name);
In the declaration portion of the script, the cursor SELECT statement has been modified so that for
statement so that only the first four records of the index-by table are displayed on the screen.
1) Create an associative array with the element type of a user-defined record.
Instead of using an associative array, use a nested table.
Instead of using a nested table, use a varray.
. EXECUTE IMMEDIATE statements
SELECT statements are called static because they do not change from execution
Now, consider a different type of PL/SQL application in which SQL statements
Because these SQL statements
convenient to use than the Oracle-supplied package DBMS_SQL, which has
Statements
. Use the EXECUTE IMMEDIATE statement
Generally, dynamic SQL statements are built by your program and are stored as character strings
These strings must contain valid SQL statements
Consider the following dynamic SQL statement:
This SELECT statement returns a student’s first and last name for a given student ID.
statements:
To process dynamic SQL statements, you use EXECUTE IMMEDIATE or OPEN-FOR, FETCH,
EXECUTE IMMEDIATE is used for single-row SELECT statements, all
To improve the performance of dynamic SQL statements you can also use BULK EXECUTE IMMEDI-
ATE, BULK FETCH, FORALL, and COLLECT INTO statements.
THE EXECUTE IMMEDIATE STATEMENT
The EXECUTE IMMEDIATE statement parses a dynamic statement or a PL/SQL block for imme-
dynamic_SQL_string is a string that contains a valid SQL statement or a PL/SQL block.
SELECT statement.
This clause is used when a dynamic SQL statement returns a single row
similar to a static SELECT INTO statement.
ments whose values are passed to the dynamic SQL statement or PL/SQL block.
of bind arguments that store values returned by the dynamic SQL statement or PL/SQL block.
When an EXECUTE IMMEDIATE statement contains both USING and RETURNING INTO clauses, the
v_zip VARCHAR2(5) := '11106';
v_total_students NUMBER;
v_new_zip VARCHAR2(5);
v_student_id NUMBER := 151;
-- Create table MY_STUDENT
sql_stmt := 'CREATE TABLE my_student '||
'AS SELECT * FROM student WHERE zip = '||v_zip;
-- Select total number of records from MY_STUDENT table
INTO v_total_students;
DBMS_OUTPUT.PUT_LINE ('Students added: '||v_total_students);
-- Update record in MY_STUDENT table
sql_stmt := 'UPDATE my_student SET zip = 11105 WHERE student_id =
EXECUTE IMMEDIATE sql_stmt USING v_student_id RETURNING INTO
DBMS_OUTPUT.PUT_LINE ('New zip code: '||v_new_zip);
This script contains several examples of dynamic SQL.
First, you create the table MY_STUDENT and populate it with records for a specified value of
It is important to note that the variable v_zip is concatenated with the CREATE state-
Second, you select the total number of students added to the MY_STUDENT table and display
You use the INTO option with the EXECUTE IMMEDIATE statement because
the SELECT statement returns a single row.
Third, you create a simple PL/SQL block in which you select the current date and display it on
Because the PL/SQL block does not contain any bind arguments, the EXECUTE
Finally, you update the MY_STUDENT table for a given student ID and return a new value of
zip code using the RETURNING statement.
student ID to the UPDATE statement at runtime, and the RETURNING INTO option allows you
to pass a new value of zip code from the UPDATE statement into your program.
EXECUTE IMMEDIATE Statements
v_zip VARCHAR2(5) := '11106';
v_total_students NUMBER;
EXECUTE IMMEDIATE 'DROP TABLE my_student';
-- Create table MY_STUDENT
sql_stmt := 'CREATE TABLE my_student '||
EXECUTE IMMEDIATE sql_stmt USING v_zip;
-- Select total number of records from MY_STUDENT table
INTO v_total_students;
DBMS_OUTPUT.PUT_LINE ('Students added: '|| v_total_students);
First, you drop the MY_STUDENT table created in the previous version of the example.
you re-create the MY_STUDENT table, but in this case, you use a bind argument to pass a value
of zip code to the CREATE statement at runtime.
A CREATE TABLE statement is a data definition statement.
In this version, you pass the table name as a bind argument to the SELECT statement.
v_zip VARCHAR2(5) := '11106';
v_total_students NUMBER;
-- Create table MY_STUDENT
sql_stmt := 'CREATE TABLE my_student '||
'AS SELECT * FROM student '|| 'WHERE zip ='|| v_zip;
-- Select total number of records from MY_STUDENT table
EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM :my_table'
INTO v_total_students
DBMS_OUTPUT.PUT_LINE ('Students added: '|| v_total_students);
This example causes an error because you cannot pass names of schema objects to dynamic SQL
example with the SELECT statement:
EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM '||my_table
INTO v_total_students;
As mentioned earlier, a dynamic SQL string can contain any SQL statement or PL/SQL block.
However, unlike static SQL statements, a dynamic SQL statement should not be terminated with
Consider a different version of the same example in which the SELECT statement is terminated
If you created the MY_STUDENT table based on the corrected version of the preceding script, you
EXECUTE IMMEDIATE Statements
v_zip VARCHAR2(5) := '11106';
v_total_students NUMBER;
-- Create table MY_STUDENT
sql_stmt := 'CREATE TABLE my_student '||
'AS SELECT * FROM student '|| 'WHERE zip = '||v_zip;
-- Select total number of records from MY_STUDENT table
INTO v_total_students;
DBMS_OUTPUT.PUT_LINE ('Students added: '|| v_total_students);
The semicolon added to the SELECT statement is treated as an invalid character when the state-
A somewhat similar error is generated when a PL/SQL block is
In some cases you may need to pass a NULL value to a dynamic SQL statement as a value for a
SQL and the EXECUTE IMMEDIATE statement:
PL/SQL: Statement ignored
of the SQL types.
To pass a NULL value to the dynamic SQL statement, you should modify this
EXECUTE IMMEDIATE Statements
Because the variable v_null has not been initialized, its value
17.1.1 Use the EXECUTE IMMEDIATE Statement
Create the following PL/SQL script:
USING v_student_id;
ANSWER: The declaration portion of the script declares the string that contains the dynamic SQL
statement, and three variables to hold student’s ID, first name, and last name, respectively.
executable portion of the script contains a dynamic SQL statement with one bind argument that
is used to pass the value of the student ID to the SELECT statement at runtime.
variables contain results returned by the SELECT statement.
able v_student_id, which is used to pass a value to the SELECT statement at runtime.
two DBMS_OUTPUT.PUT_LINE statements are used to display the results of the SELECT statement
new 3: v_student_id NUMBER := 105;
B) Modify the script so that the student’s address (street, city, state, and zip code) is displayed on the
v_zip VARCHAR2(5);
USING v_student_id;
DBMS_OUTPUT.PUT_LINE ('Zip Code: '||v_zip);
v_zip.
Next, you modify the dynamic SQL statement so that it can return the student’s address.
DBMS_OUTPUT.PUT_LINE statements to display the student’s address on the screen.
new 3: v_student_id NUMBER := 105;
so that v_zip and v_state were misplaced while the SELECT statement remained
v_zip VARCHAR2(5);
-- variables v_state and v_zip are misplaced
USING v_student_id;
DBMS_OUTPUT.PUT_LINE ('Zip Code: '||v_zip);
new 3: v_student_id NUMBER := 105;
ORA-06502: PL/SQL: numeric or value error
This error is generated because the variable v_state can hold up to two characters.
C) Modify the script created in the previous exercise (ch17_1b.sql) so that the SELECT statement can
table name used in the SELECT statement at runtime.
v_table_name VARCHAR2(20) := '&sv_table_name';
v_zip VARCHAR2(5);
' FROM '||v_table_name||' a, zipcode b' ||
' AND '||v_table_name||'_id = :1';
DBMS_OUTPUT.PUT_LINE ('Zip Code: '||v_zip);
The declaration portion of the script contains a new variable,v_table_name, which holds the
In addition, the variable v_student_id has
been replaced by the variable v_id because it is not known in advance which table, STUDENT or
The executable portion of the script contains a modified dynamic SQL statement.
statement does not contain any information specific to the STUDENT or INSTRUCTOR tables.
other words, the dynamic SQL statement used by the previous version (ch17_1b.sql)
' FROM '||v_table_name||' a, zipcode b' ||
' AND '||v_table_name||'_id = :1';
The table name (student) has been replaced by the variable v_table_name in the FROM and
statements that are not based on a specific table, because you do not always know in advance
Enter value for sv_table_name: student
old 3: v_table_name VARCHAR2(20) := '&sv_table_name';
new 3: v_table_name VARCHAR2(20) := 'student';
old 3: v_table_name VARCHAR2(20) := '&sv_table_name';
new 3: v_table_name VARCHAR2(20) := 'instructor';
Statements
. Use OPEN-FOR, FETCH, and CLOSE statements
The OPEN-FOR, FETCH, and CLOSE statements are used for multirow queries or cursors.
In the case of dynamic SQL, the OPEN-FOR statement has an optional USING clause that allows
OPEN cursor_variable FOR dynamic_SQL_string
'SELECT first_name, last_name FROM student '|| 'WHERE zip = :1'
USING v_zip;
In this code fragment, you define a weak cursor type, student_cur_type.
a cursor variable student_cur based on the REF CURSOR type specified in the preceding
At runtime, the student_cur variable is associated with the SELECT statement that
returns the first and last names of students for a given value of zip.
As mentioned earlier, the FETCH statement returns a single row from the result set into a list of
variables defined in a PL/SQL block and moves the cursor to the next row.
statement is as follows:
Adding the previous example, you fetch the student’s first and last names into variables speci-
records to process using the EXIT WHEN statement.
'SELECT first_name, last_name FROM student '|| 'WHERE zip = :1'
USING v_zip;
The CLOSE statement disassociates the cursor variable with the multirow query.
CLOSE statement is as follows:
'SELECT first_name, last_name FROM student '|| 'WHERE zip = :1'
USING v_zip;
new 5: v_zip VARCHAR2(5) := '11236';
17.2.1 Use OPEN-FOR, FETCH, and CLOSE Statements
Create the following PL/SQL script:
v_zip VARCHAR2(5);
sql_stmt := 'SELECT zip, COUNT(*) total'||
DBMS_OUTPUT.PUT_LINE ('Zip code: '||v_zip||
Consider the use of spaces in the SQL statements generated dynamically.
string that holds the dynamic SQL statement consists of three concatenated strings, where each string is
sql_stmt := 'SELECT zip, COUNT(*) total'||
If no space is added after the STUDENT table, the resulting SELECT statement
define a string variable to hold a dynamic SQL statement, and two variables,v_zip and
v_total, to hold data returned by the cursor.
In the executable portion of the script, you generate a dynamic SQL statement, associate it with
fetch values of zip code and total number of students into the variables v_zip and v_total,
B) Modify the script you just created (ch17_2a.sql) so that the SELECT statement can be run against
either the STUDENT or INSTRUCTOR table.
in the SELECT statement at runtime.
v_table_name VARCHAR2(20) := '&sv_table_name';
v_zip VARCHAR2(5);
DBMS_OUTPUT.PUT_LINE ('Totals from '||v_table_name||
sql_stmt := 'SELECT zip, COUNT(*) total'||
' FROM '||v_table_name||' '||
DBMS_OUTPUT.PUT_LINE ('Zip code: '||v_zip||
In this version of the script, you add a variable,v_table_name, to hold the name of a table
You also add a DBMS_OUTPUT.PUT_LINE table to display a message stating
Next, you modify the dynamic SQL statement as
sql_stmt := 'SELECT zip, COUNT(*) total'||
' FROM '||v_table_name||' '||
The variable v_table_name has been inserted in place of the actual table name (STUDENT).
Note that you concatenate a space to the variable v_table_name so that the SELECT state-
The first run is based on the STUDENT table,
Enter value for sv_table_name: student
old 5: v_table_name VARCHAR2(20) := '&sv_table_name';
new 5: v_table_name VARCHAR2(20) := 'student';
Totals from student table
old 5: v_table_name VARCHAR2(20) := '&sv_table_name';
new 5: v_table_name VARCHAR2(20) := 'instructor';
So far you have seen that values returned by the dynamic SQL statements are stored in individual
order of the corresponding columns returned by the SELECT statement.
somewhat cumbersome when a dynamic SQL statement returns more than a few columns.
result, PL/SQL allows you to store values returned by the dynamic SELECT statements in the vari-
v_table_name VARCHAR2(20) := '&sv_table_name';
DBMS_OUTPUT.PUT_LINE ('Totals from '||v_table_name||
sql_stmt := 'SELECT zip, COUNT(*) total'||
' FROM '||v_table_name||' '||
Enter value for sv_table_name: student
old 10: v_table_name VARCHAR2(20) := '&sv_table_name';
new 10: v_table_name VARCHAR2(20) := 'student';
Totals from student table
old 10: v_table_name VARCHAR2(20) := '&sv_table_name';
new 10: v_table_name VARCHAR2(20) := 'instructor';
the material on native dynamic SQL and puts it to use within the context of stored procedures.
. The FORALL statement
SQL statements to the SQL engine, which returns results to the PL/SQL engine.
statement affects four or more rows, bulk SQL may improve performance signif-
Bulk SQL supports batch processing of SQL statements and their results.
It consists of two features—the FORALL statement and the BULK COLLECT
The FORALL Statement
. Use the FORALL statement
This INSERT statement is sent from PL/SQL to SQL ten times.
If you replace the FOR loop with the FORALL statement, the INSERT state-
The FORALL statement has the following structure (the reserved words in brackets are
SQL_STATEMENT [SAVE EXCEPTIONS];
The FORALL statement has an implicitly defined loop counter variable associated with it.
of a particular collection, which is either a nested table or an associative array.
. When the collection referenced by the VALUES OF clause is empty, the FORALL statement
Next, SQL_STATEMENT is either a static or dynamic INSERT, UPDATE, or DELETE statement
the FORALL statement to continue even when SQL_STATEMENT causes an exception.
Consider the following example that illustrates how the FORALL statement may be used.
example, as well as other examples in this chapter, uses a test table created specifically for this
CREATE TABLE test (row_num NUMBER, row_text VARCHAR2(10));
TYPE row_num_type IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
TYPE row_text_type IS TABLE OF VARCHAR2(10) INDEX BY PLS_INTEGER;
('There are '||v_total||' rows in the TEST table');
As mentioned earlier, when SQL statements are used with FORALL statements, they reference
So, in this script, you define two collection types as associative arrays and
FORALL statement.
In this example, you compare the execution times of the INSERT statements
TYPE row_num_type IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
TYPE row_text_type IS TABLE OF VARCHAR2(10) INDEX BY PLS_INTEGER;
The FORALL Statement
DBMS_OUTPUT.PUT_LINE ('Duration of the FORALL statement: '||
To calculate execution times of the FOR loop and the FORALL statement, you employ the
As mentioned previously, the SAVE EXCEPTIONS option enables the FORALL statement to
continue even when the corresponding SQL statement causes an exception.
are stored in the cursor attribute called SQL%BULK_EXCEPTIONS.
EXCEPTIONS cursor attribute is a collection of records in which each record consists of two
iteration of the FORALL statement during which an exception was encountered, and the
The number of exceptions that occurred during the execution of the FORALL statement can be
Next, consider an example of the FORALL statement with the SAVE EXCEPTIONS option:
TYPE row_num_type IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
TYPE row_text_type IS TABLE OF VARCHAR2(11) INDEX BY PLS_INTEGER;
-- Modify 1, 5, and 7 elements of the V_ROW_TEXT collection
-- These rows will cause exception in the FORALL statement
The FORALL Statement
SQL%BULK_EXCEPTIONS.COUNT LOOP
SQL%BULK_EXCEPTIONS(i).error_index||' caused error '||i||
': '||SQL%BULK_EXCEPTIONS(i).ERROR_CODE||' '||
SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE));
which, in this case, is the INSERT statement that uses collection elements.
the COUNT method on the SQL%BULK_EXCEPTIONS collection.
exception information such as which record number caused an exception and the error message
To display the number of the record that caused an exception,
TYPE row_num_type IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
TYPE row_text_type IS TABLE OF VARCHAR2(10) INDEX BY PLS_INTEGER;
('There are '||v_total||' rows in the TEST table');
the FORALL statement iterates seven times, and seven rows are added to the TEST table.
The FORALL Statement
The VALUES OF option specifies that the values of the loop counter in the FORALL statement
a group of indexes that the FORALL statement can loop through.
CREATE TABLE TEST_EXC (row_num NUMBER, row_text VARCHAR2(50));
TYPE row_num_type IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
TYPE row_text_type IS TABLE OF VARCHAR2(11) INDEX BY PLS_INTEGER;
-- Modify 1, 5, and 7 elements of the V_ROW_TEXT collection
-- These rows will cause exception in the FORALL statement
-- Populate V_EXC_IND_TAB collection to be used in the VALUES
SQL%BULK_EXCEPTIONS.COUNT LOOP
exc_ind_tab(i) := SQL%BULK_EXCEPTIONS(i).error_index;
-- Insert records that caused exceptions in the TEST_EXC
The newly created table is used to store records that cause
exceptions when they are inserted into the TEST table.
Next, you modify the first, fifth, and seventh elements of the row_text_tab table to cause
exceptions in the FORALL statement.
exc_ind_tab collection with index values of rows that caused the exceptions.
SQL%BULK_EXCEPTION collection.
After this script is executed, the TEST and TEST_EXC tables contain the following records:
The FORALL Statement
18.1.1 Use the FORALL Statement
late this table using the FORALL statement.
available for the FORALL statement, such as SAVE EXCEPTIONS, INDICES OF, and VALUES OF.
Create the MY_ZIPCODE table as follows:
This statement creates an empty MY_ZIPCODE table because the criterion specified in the WHERE clause
Create the following PL/SQL script:
TYPE string_type IS TABLE OF VARCHAR2(100) INDEX BY PLS_INTEGER;
-- statement
zip_tab(v_counter) := rec.zip;
state_tab(v_counter) := rec.state;
cr_by_tab(v_counter) := rec.created_by;
cr_date_tab(v_counter) := rec.created_date;
mod_by_tab(v_counter) := rec.modified_by;
mod_date_tab(v_counter) := rec.modified_date;
-- Check how many records were added to MY_ZIPCODE table
(v_total||' records were added to MY_ZIPCODE table');
ANSWER: This script populates the MY_ZIPCODE table with records selected from the ZIPCODE
To enable use of the FORALL statement, it employs seven collections.
The script uses cursor FOR loop to populate
the individual collections and then uses them with the FORALL statement to populate the
Finally, it checks how many records were added to the MY_ZIPCODE table
19 records were added to MY_ZIPCODE table
B) Modify the previous version of the script as follows: Select data from the ZIPCODE table for a
Modify the selected records so that they will cause various exceptions
in the FORALL statement.
Modify the FORALL statement so that it does not fail when an exception
TYPE string_type IS TABLE OF VARCHAR2(100) INDEX BY PLS_INTEGER;
-- statement
zip_tab(v_counter) := rec.zip;
state_tab(v_counter) := rec.state;
cr_by_tab(v_counter) := rec.created_by;
cr_date_tab(v_counter) := rec.created_date;
mod_by_tab(v_counter) := rec.modified_by;
mod_date_tab(v_counter) := rec.modified_date;
FORALL i in 1..zip_tab.COUNT SAVE EXCEPTIONS
-- Check how many records were added to MY_ZIPCODE table
(v_total||' records were added to MY_ZIPCODE table');
SQL%BULK_EXCEPTIONS.COUNT LOOP
SQL%BULK_EXCEPTIONS(i).error_index||' caused error '||i||
': '||SQL%BULK_EXCEPTIONS(i).ERROR_CODE||' '||
SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE));
In this script, you declare a user-defined exception and associate an Oracle error number with it
loop against the ZIPCODE table, and then you modify them so that they cause exceptions in the
FORALL statement.
For example, the first record of the zip_tab collection is set to NULL.
causes a constraint violation because the ZIP column in the MY_ZIPCODE table has a NOT NULL
Then, you add the SAVE EXCEPTIONS clause to the FORALL statement
and an exception-handling section to the PL/SQL block.
statement in the exception-handling section.
This statement is added so that records that are
inserted successfully by the FORALL statement are committed when control of the execution is
C) Modify the previous version of the script as follows: Do not modify records selected from the
ZIPCODE table so that no exceptions are raised.
TYPE string_type IS TABLE OF VARCHAR2(100) INDEX BY PLS_INTEGER;
-- statement
zip_tab(v_counter) := rec.zip;
state_tab(v_counter) := rec.state;
cr_by_tab(v_counter) := rec.created_by;
cr_date_tab(v_counter) := rec.created_date;
mod_by_tab(v_counter) := rec.modified_by;
mod_date_tab(v_counter) := rec.modified_date;
FORALL i IN INDICES OF zip_tab SAVE EXCEPTIONS
-- Check how many records were added to MY_ZIPCODE table
(v_total||' records were added to MY_ZIPCODE table');
SQL%BULK_EXCEPTIONS.COUNT LOOP
SQL%BULK_EXCEPTIONS(i).error_index||' caused error '||i||
': '||SQL%BULK_EXCEPTIONS(i).ERROR_CODE||' '||
SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE));
2 records were added to MY_ZIPCODE table
D) Modify the second version of the script, ch18_1b.sql, as follows: Insert records that cause excep-
ANSWER: The MY_ZIPCODE_EXC table may be created as follows:
cause exceptions in the FORALL statements can be inserted into this table.
TYPE string_type IS TABLE OF VARCHAR2(100) INDEX BY PLS_INTEGER;
-- statement
zip_tab(v_counter) := rec.zip;
state_tab(v_counter) := rec.state;
cr_by_tab(v_counter) := rec.created_by;
cr_date_tab(v_counter) := rec.created_date;
mod_by_tab(v_counter) := rec.modified_by;
mod_date_tab(v_counter) := rec.modified_date;
FORALL i in 1..zip_tab.COUNT SAVE EXCEPTIONS
-- Check how many records were added to MY_ZIPCODE table
(v_total||' records were added to MY_ZIPCODE table');
-- Populate V_EXC_IND_TAB collection to be used in the VALUES
SQL%BULK_EXCEPTIONS.COUNT LOOP
exc_ind_tab(i) := SQL%BULK_EXCEPTIONS(i).error_index;
-- Insert records that caused exceptions in the MY_ZIPCODE_EXC
In this version of the script, you modify the exception-handling section so that records causing
exceptions in the FORALL statement are inserted into the MY_ZIPCODE_EXC table created earlier.
First, you populate the collection EXC_IND_TAB with subscripts of records that caused exceptions
in the FORALL statement.
After the script is executed, the MY_ZIPCODE_EXC table contains
1.1.1 Use PL/SQL Anonymous Blocks
1.2.2 Use the DBMS_OUTPUT.PUT_LINE Statement
LAB 2.1 PL/SQL Programming Fundamentals
2.1.1 Make Use of PL/SQL Language Components
2.1.2 Make Use of PL/SQL Variables
2.1.4 Make Use of Identifiers in PL/SQL
LAB 3.1 Making Use of DML in PL/SQL
3.1.2 Use DML in a PL/SQL Block
3.1.3 Make Use of a Sequence in a PL/SQL Block
3.2.1 Make Use of COMMIT, ROLLBACK, and SAVEPOINT in a PL/SQL Block
LAB 4.1 IF Statements
4.1.1 Use the IF-THEN Statement
4.1.2 Use the IF-THEN-ELSE Statement
LAB 4.2 ELSIF Statements
4.2.1 Use the ELSIF Statement
LAB 4.3 Nested IF Statements
4.3.1 Use Nested IF Statements
LAB 5.1 CASE Statements
5.1.1 Use the CASE Statement
5.1.2 Use the Searched CASE Statement
LAB 7.1 The CONTINUE Statement
7.1.1 Use the CONTINUE Statement
11.1.1 Make Use of Record Types
11.2.1 Use a Cursor FOR Loop
13.2.1 Use Row and Statement Triggers
LAB 15.1 PL/SQL Tables
15.1.2 Use Nested Tables
LAB 16.1 Record Types
16.1.1 Use Table-Based and Cursor-Based Records
LAB 16.3 Collections of Records
16.3.1 Use Collections of Records
LAB 17.1 EXECUTE IMMEDIATE Statements
17.1.1 Use the EXECUTE IMMEDIATE Statement
LAB 17.2 OPEN-FOR, FETCH, and CLOSE Statements
17.2.1 Use OPEN-FOR, FETCH, and CLOSE Statements
LAB 18.1 The FORALL Statement
18.1.1 Use the FORALL Statement
18.2.1 Use the BULK COLLECT Statement
20.1.3 Invoke Functions in SQL Statements
LAB 21.2 Cursor Variables
21.2.1 Make Use of Cursor Variables
23.1.2 Use Object Types with Collections
LAB 24.1 Making Use of Oracle Supplied Packages to Profile PL/SQL, Access Files, and Schedule Jobs