Exceptions
Review Section
This section has described the following points about exception handling:
1. Oracle Database 12c provides two custom built-in functions, SQLCODE and SQLERRM.
2. Oracle supports predefined exceptions in the STANDARD package and a generic exception handler—OTHERS.
3. PL/SQL supports user-defined exceptions, which throw a positive 1 as an error value unless you use the precompiler PRAGMA EXCEPTION_INIT to set a numeric exception value.
4. PL/SQL supports dynamic user-defined exceptions by calling the RAISE_APPLICATION_ERROR function, which allows you to define an error message.
Bulk Operations
Review Section
This section has described the following points about bulk operations:
1. The BULK COLLECT INTO clause lets you perform a bulk collect, and it provides you with the LIMIT clause to set the maximum size of rows processed through bulk operations.
2. The FORALL statement lets you process a group of rows by managing a collection assignment.
Functions, Procedures, and Packages
Review Section
This section has described the following points about packages:
1. Functions are local and stand-alone named blocks that return a value and can be used as right operands in assignments.
2. Procedures are local and stand-alone named blocks that don't return a value, which means procedures must be called by anonymous blocks, functions, or procedures.
3. Functions and procedures are pass-by-value program units when all their parameters use the default IN-only mode of operation.
4. Functions and procedures are pass-by-reference program units when one or more of their formal parameters use an IN OUT or OUT-only mode of operation.
5. Packages hold related functions, procedures, and data types; they also support overloading of functions and procedures.
6. Bodiless packages support data type and shared cursor definitions.
Transaction Scope
Review Section
This section has described the following points about transaction scope:
1. Oracle Database is always in transaction mode, which differs from other databases such as MySQL.
2. Transaction scope ensures that either all or nothing happens when inserting, updating, or deleting data from two or more tables.
3. You should always set a SAVEPOINT before attempting to insert, update, or delete data from two or more tables.
4. You should always roll back transactions when one part of a multiple-part transaction fails to ensure ACID-compliant transaction (see Appendix A for a more complete description of ACID compliance).
Mastery Check
True or False:
1. A basic block in PL/SQL must have at least a null statement to compile.
True. Any PL/SQL block must include at least one statement, like a NULL; statement. The NULL; statement is advantageous for testing whether the blocks of code are organized correctly before embedding logic.
2. The elsif statement lets you branch execution in an if statement.
False. The elsif statement keyword lets you branch execution, not the ELSEIF keyword.
3. The DECLARE block is where you put all variable, cursor, and local function and procedure implementations.
True. The DECLARE block is where you put all variable, cursor, and local function and procedure implementations.
4. An EXCEPTION block is where you put handling for errors raised in the declaration block of the same anonymous or named program unit.
False. The EXCEPTION block is where you handle exceptions from the execution section, but it can’t handle exceptions raised in the declaration block.
5. The colon and equal sign set (:=) is the only assignment operator in PL/SQL.
False. The := operator is the only right-to-left assignment operator in PL/SQL, but you can use the SELECT-INTO statement or BULK COLLECT INTO statement to perform left-to-right assignments.
6. You need to provide forward-referencing stubs for local functions or procedures to avoid a procedure or function “not declared in this scope” error.
True. You need to provide forward-referencing stubs to avoid a forward reference.
7. Oracle supports both simple and searched case statements.
True. You can implement a simple or searched case statement.
8. Oracle supports SQL and PL/SQL collections as parameter and return value data types.
True. You can have a function that returns a SQL or PL/SQL collection. You must call functions that return a PL/SQL collection inside another PL/SQL program unit. Functions that return a SQL collection work in either SQL or PL/SQL.
9. Packages let you define overloaded functions and procedures.
True. Oracle supports overloaded functions and procedures in packages, which is an object-oriented feature.
10. Database triggers run between the first phase of a DML statement and the COMMIT statement.
True. Database triggers run between the first phase of a DML statement and the COMMIT statement. The COMMIT statement ends the transaction and is the second phase of a twophase commit (2PC).
Multiple Choice:
11. Which parameter modes are supported in Oracle PL/SQL? (Multiple answers possible)
A. IN
B. INOUT
C. OUT
D. IN OUT
E. All of the above
A, C, and D are correct. The IN mode works for pass-by-value parameters, and IN OUT and OUT-only mode work for pass-by-reference parameters. There isn’t an INOUT mode.
12. Which of the following are valid loop structures in PL/SQL? (Multiple answers possible)
A. A simple loop
B. A FOR loop
C. A WHILE loop
D. An UNTIL loop
E. All of the above
A, B, and C are correct. Oracle supports a range and cursor FOR loop, a WHILE loop, and a simple loop.
13. A simple case statement works with which of the following data types? (Multiple answers possible)
A. A TEXT data type
B. A VARCHAR2 data type
C. A NCHAR data type
D. A CHAR data type
E. A DATE data type
B, C, and D are correct. Oracle supports a VARCHAR2, NCHAR, or CHAR data type in a simple case statement.
14. Which of the following isn’t a keyword in PL/SQL?
A. RECORD
B. REVERSE
C. CURSOR
D. LIMIT
E. STRUCTURE
E is correct. Oracle doesn’t support a STRUCTURE keyword. It does support the RECORD, REVERSE, CURSOR, and LIMIT keywords.
15. Which of the following isn’t a cursor attribute?
A. %FOUND
B. %ISOPEN
C. %TYPE
D. %NOTFOUND
E. %ROWCOUNT
C is correct. %TYPE is a column anchoring attribute, not a cursor attribute.
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
