《Oracle PL/SQL开发指南》学习笔记16——Oracle PL/SQL Development Overview （Review Section，Mastery Check）

这章学的有点尴尬，基本上是看不懂啊！

本来觉得对Oracle有点熟悉了，这章告诉我并非如此。

12C New SQL Features

Review Section
This section has described the following points about Oracle Database 12c new SQL features:
1. Oracle Database 12c goes beyond simply referring to environment variables in LIBRARY path statements, and lets you use a virtual DIRECTORY.
2. Oracle Database 12c lets you define explicit and implicit valid-time (VT) dimensions to improve flashback controls for the DBA.
3. Oracle Database 12c expands the role of the LEFT OUTER JOIN to include multiple tables on the left side of the join.
4. Oracle Database 12c introduces the CROSS APPLY, OUTER APPLY, and LATERAL syntax for working with nested tables.
5. Oracle Database 12c supports default columns that can hold the .nextval and .currval pseudocolumns for named sequences.
6. Oracle Database 12c introduces identity columns that maintain auto-incrementing sequences for surrogate keys.
7. Oracle Database 12c adds the ON NULL clause to default values, which eliminates manual overrides with explicit null values when inserting or updating tables.
8. Oracle Database 12c lets you set a parameter to increase the length of VARCHAR2, NVARCHAR2, and RAW data types to 32,767 bytes, which is equivalent to their size in PL/SQL.
9. Oracle Database 12c enables definer or invoker rights models for views through the BEQUEATH keyword.

 

 

12C New PL/SQL Features

Review Section
This section has described the following points about Oracle Database 12c new PL/SQL features:
1. Oracle Database 12c enables you to cache results from invoker rights functions.
2. Oracle Database 12c lets you white list the callers of stored functions, procedures, packages, and object types.
3. Oracle Database 12c provides native client API support for PL/SQL data types.
4. Oracle Database 12c provides new error stack management through the utl_call_stack package.
5. Oracle Database 12c lets you expand the full text of views that depend on views with the new expand_sql_text procedure in the dbms_utility package.
6.The dbms_sql package adds a new formal schema, which lets it resolve unqualified object names.
7. Oracle Database 12c supports embedding PL/SQL functions inside SQL WITH clause statements.
8. Oracle Database 12c adds the ability to use local PL/SQL data types in local SQL statements.
9. Oracle Database 12c supports implicit binding of the PL/SQL REF CURSOR data type in ODP.NET.

 

Mastery Check

The mastery check is a series of true-or-false and multiple-choice questions that let you confirm how well you understand the material in the chapter. You may check Appendix I for answers to these questions.

1. Valid-time (VT) indicates the point at which transactions commit.
False. Valid-time (VT) indicates the point at which a business event occurs, and is unrelated to the transaction-time (TT).
2. It is possible to define a default column that uses the .nextval pseudocolumn for a sequence.
True. Oracle Database 12c lets you define a default column that uses the .nextval pseudocolumn.
3. It is possible to define a default column that uses the .currval pseudocolumn for a sequence.
True. Oracle Database 12c lets you define a default column that uses the .currval pseudocolumn.
4. The .currval pseudocolumn no longer has a dependency on a preceding .nextval pseudocolumn call in a session.
False. Oracle Database 12c does not remove the session dependency that the .currval pseudocolumn has on the .nextval pseudocolumn.
5. Oracle Database 12c doesn’t provide a means to prevent the entry of an explicit null in an INSERT statement, which means you can still override a DEFAULT column value.
False. Oracle Database 12c does provide an ON NULL clause that prevents the insertion or update of an explicit null when a default value has been specified.
6. Identity columns let you automatically number the values of a surrogate key column.
True. Oracle Database 12c lets you define an identity column in any table that uses an implicitly generated sequence.
7. VARCHAR2, NVARCHAR2, and RAW data types are now always 32,767 bytes in the Oracle
Database 12c database.
False. Oracle Database 12c provides a max_string_size parameter, which lets you set it to EXTENDED when you want VARCHAR2, NVARCHAR2, and RAW columns to hold 32,767 bytes. Data types remain capped at the prior maximum size when the max_string_size parameter is set to STANDARD.
8. A PL/SQL function can return a PL/SQL associative array directly into a SQL statement with the changes introduced in Oracle 12c.
True. Oracle Database 12c lets you consume a PL/SQL associative array in a SQL statement when you meet three conditions. First, the data type must be defined in a PL/SQL package.
Second, there must be a local variable that uses that data type. Third, the SQL statement must be embedded within the PL/SQL block.
9. Oracle Database 12c now supports top-n query results without an offset value.
True. Oracle Database 12c lets you create top-n query with or without OFFSET values.

10. You can embed a PL/SQL function inside a query’s WITH clause and call it from external programs.
True. Oracle Database 12c lets you implement a PL/SQL function inside a WITH clause.
Unfortunately, the semicolon required to terminate statements and blocks causes a conflict with the SQLTERMINATOR and limits your ability to reading the statement within a preconfigured SQL*Plus session. You must embed the query inside a view to call it from other program units.

Multiple Choice:
11. Which of the following keywords work when you define a view? (Multiple answers possible)
A. The AUTHID DEFINER keywords
B. The BEQUEATH INVOKER keywords
C. The AUTHID CURRENT_USER keywords
D. The BEQUEATH DEFINER keywords
E. All of the above
B and D are correct. The BEQUEATH keyword may precede either the INVOKER keyword or DEFINER keyword. The AUTHID keyword may precede either DEFINER or CURRENT_USER but only for functions, procedures, packages, and object types.
12. Which of the following are correct about caching invoker rights functions? (Multiple answers possible)
A. A different result set exists for each invoker.
B. The same result set exists for each invoker.
C. A cached invoker rights function must be deterministic.
D. A cached invoker rights function may be non-deterministic.
E. All of the above.
A and D are correct. Oracle Database 12c lets you create cached invoker rights functions.
They implicitly use the current user to distinguish between cached result sets, and that means different results are kept for each invoker.
13. Which of the following support expanding the SQL text of LONG columns into CLOB columns when working with the CDB_, DBA_, ALL_, and USER_VIEWS in the Oracle Database 12c database? (Multiple answers possible)
A. You can use the to_lob built-in function to convert LONG data types to CLOB data types.
B. You can use the to_clob built-in function to convert LONG data types to CLOB data types.
C. You can use the dbms_sql package to convert LONG data types to VARCHAR2 data types.
D. You can use the length built-in function to discover the size of a LONG data type.
E. You can use the dbms_lob package to create a temporary CLOB data type.

C, D, and E are correct. While not an Oracle Database 12c feature, you need to convert views from their native LONG data type to a CLOB before you can use the new expand_sql_text procedure in the dbms_utility package. You convert a LONG to a CLOB through a three-step process unless you like to read all the characters one-by-one in a loop. If you read character by character in your LONG to CLOB procedure, you don’t need to fetch the LONG data type into a local variable to size it before calling the conversion procedure. The first step gets the size of the LONG data value with the LENGTH built-in,
which means you query the administrative view twice. The second step requires you to convert LONG to a VARCHAR2 by using the result of the LENGTH built-in and the define_column_long procedure from the dbms_sql package. The third step uses the VARCHAR2 as a call parameter to the dbms_lob.write procedure from the dbms_lob package to create a CLOB from the VARCHAR2.
14. Which of the following is true about which PL/SQL data types you can access in an embedded SQL statement? (Multiple answers possible)
A. The PL/SQL data type must be declared in a package.
B. The SQL statement needs to be embedded in the PL/SQL block where the type is defined.
C. The PL/SQL data type must be locally defined.
D. The PL/SQL data type may be a return from a PL/SQL function.
E. All of the above.
A, B, and C are correct. Oracle Database 12c lets you consume a PL/SQL associative array in a SQL statement when you meet three conditions. First, the data type must be defined in a PL/SQL package. Second, there must be a local variable that uses that data type. Third, the SQL statement must be embedded within the PL/SQL block.
15. Which of the following lets you access a surrogate primary key from an identity column for use in a subsequent INSERT statement as a foreign key value?
A. RETURN INTO
B. RETURNING INTO
C. .nextval
D. .currval
E. None of the above
B is correct. The RETURNING INTO clause lets you capture the value from an identity column.