12c,plsql新特性

1.1.4 Improved Oracle SQL and PL/SQL

The following sections describe the improved Oracle SQL and PL/SQL features

1.1.4.1 An Invoker's Rights Function Can Be Result Cached (调用者权限的函数结果也可以被cache）

Through Oracle Database 11g Release 2 (11.2), only definer's rights PL/SQL functions could be result cached. Now, invoker's rights PL/SQL functions can also be result cached. (The identity of the invoking user is implicitly added to the key of the result.)

At times, it may be appropriate to use an invoker's rights PL/SQL function to issue one or moreSELECT statements. This feature improves performance.

See Also:

Oracle Database PL/SQL Language Reference for details

1.1.4.2 An Object of Type LIBRARY Can Be Defined Using an Object of Type DIRECTORY（新增type xxx  is object of library）

In previous releases, an object of the LIBRARY type could only be defined by using an explicit path. However, now theDIRECTORY type can be the single point of maintenance for file system paths. Moreover, using aDIRECTORY type has security benefits. A directory object can be defined using aDIRECTORY type.

Additionally, the definition of an object of the LIBRARY type can now include a credential so that the designated external program can be run as a different operating system user than the owner of the Oracle installation.

These enhancements improve security and portability of an application that uses external procedures.

See Also:

Oracle Database Security Guide for details

1.1.4.3 Enhanced Oracle Native LEFT OUTER JOIN Syntax(单个分区可以用做外连接)

In previous releases of Oracle Database, in a query that performed outer joins of more than two pairs of tables, a single table could be the null-generated table for only one other table. Beginning with Oracle Database 12c, a single table can be the null-generated table for multiple tables.

Prior to Oracle Database 12c, having multiple tables on the left hand side of an outer join was illegal and resulted in anORA-01417 error. The only way to execute such a query was to translate it into ANSI syntax. In Oracle Database 12c, the native syntax for aLEFT OUTER JOIN has been expanded to allow multiple tables on the left hand side. This expansion provides the following benefits:

• Merging of multiple table views on the left hand side of an outer join. Such views can originate from the user query or they may be generated during conversion fromLEFT OUTER JOIN syntax.

• Merging of such views allows more join reordering and, therefore, more optimal execution plans. These views are merged in a heuristic manner without having to go through cost-based query transformation.

• It relieves the application developers from the burden of formulating their queries in terms of views orLEFT OUTER JOIN syntax.

See Also:

Oracle Database SQL Language Reference for details

1.1.4.4 JDBC Support for PL/SQL Data Types as Parameters(JDBC对新数据类型的支持)

The ability for Java and JDBC applications to bind PL/SQL package types and boolean types as parameters is available in this release.

This feature improves ease-of-use, seamless mapping and exchange of PL/SQL types with Java types, and increases Java developer productivity.

1.1.4.5 Mechanism to Restrict the Ability to Reference a PL/SQL Unit to a White List of Database Objects(可以通过定义的数据库对象访问的白名单来限制PLSQL访问数据的能力 accessible by )

It is now possible to mark a schema-level function, procedure, package, or type specification with a white list of allowed callers. The allowed caller may be of any object type that can invoke a PL/SQL subprogram (for example, a trigger, view, table, or index), but it must be in the same schema as the unit that has the white list. The white list is optional but, when used, only the listed objects may reference the unit in question. Cross-schema references to a unit with a white list are, therefore, disallowed even when the reference is attempted from a schema owned by SYS.

This capability supports the robust implementation of a module, consisting of a main unit and helper units, by allowing the helper units to be inaccessible from anywhere except the unit they are intended to help.

See Also:

Oracle Database PL/SQL Language Reference for details

1.1.4.6 Native Client API Support for PL/SQL Package Types and Boolean Types as Parameters(对OCI and JDBC本地编译的客户端API等新增plsql类型和boolean数据类型)

This feature allows database client APIs (for example, OCI and JDBC) to natively describe and bind PL/SQL package types and boolean types. Java and C-based applications can now easily bind and execute PL/SQL functions or procedures with PL/SQL package types or boolean types as parameters.

This feature reduces the complexity of executing PL/SQL functions or procedures from client-side applications.

See Also:

Oracle Database Development Guide for details

1.1.4.7 New PL/SQL DBMS_UTILITY.EXPAND_SQL_TEXT Procedure (用来分析sql查询的涉及到的相关表)

The DBMS_UTILITY.EXPAND_SQL_TEXT procedure accepts a subquery that references views and returns a subquery with the identical meaning that references only tables.

This functionality can help in the analysis of SQL which depends on views with the aim of fixing application logic or resolving performance issues.

See Also:

Oracle Database PL/SQL Packages and Types Reference for details

1.1.4.8 New PL/SQL Package UTL_CALL_STACK(新增UTL_CALL_STACK查看plsql调用栈)

The UTL_CALL_STACK package provides subprograms to return the current call stack for a PL/SQL program.

It is functionally similar to the existing DBMS_UTILITY.FORMAT_CALL_STACK procedure which returns information as a human-readable essay. This new package makes this information available in a structured representation amenable for programmatic analysis.

See Also:

Oracle Database PL/SQL Packages and Types Reference for details

1.1.4.9 New Predefined PL/SQL Inquiry Directives (新的plsql调试指示)

The $$PLSQL_OWNER and $$PLSQL_TYPE predefined PL/SQL inquiry directives are now supported in this release.

Through Oracle Database 11g Release 2 (11.2), the predefined inquiry directives,$$PLSQL_LINE and$$PLSQL_UNIT, allowed diagnostic code to identify the current PL/SQL statement, but with a certain ambiguity. This ambiguity is now removed.

See Also:

Oracle Database PL/SQL Language Reference for details

1.1.4.10 New SCHEMA Parameter for DBMS_SQL.PARSE() Procedure (DBMS_SQL.PARSE()新增参数，schema)

DBMS_SQL.PARSE() procedure has a new SCHEMA parameter. It specifies the schema in which to resolve unqualified object names.

This allows a definer's rights unit to control the name resolution for the dynamic SQL it issues.

See Also:

Oracle Database PL/SQL Packages and Types Reference for details

1.1.4.11 PL/SQL Functions Defined in the SQL WITH Clause（在PL/SQL函数中可以用with语句）

You can define a PL/SQL function in the WITH clause of a subquery and use it as an ordinary function beginning with this release.

The procedural logic needed to support a SQL statement is encapsulated with the SQL statement. This is particularly useful in a read-only database.

Using this construct results in better performance as compared with schema-level functions.

See Also:

Oracle Database SQL Language Reference for details

1.1.4.12 PL/SQL-Specific Data Types Allowed Across the PL/SQL-to-SQL Interface(直接可以用匿名块来调用sql语句)

Through Oracle Database 11g Release 2 (11.2), when PL/SQL invoked SQL, only values with data types supported by SQL could be bound. This restriction applied even when the called SQL was a PL/SQL anonymous block. This restriction is removed in Oracle Database 12c Release 1 (12.1). For example, a PL/SQL subprogram with a formal parameter whose data type isBOOLEAN can now be invoked dynamically using an anonymous block.

Other restrictions are also removed. The table operator can now be used in a PL/SQL program on a collection whose data type is declared in PL/SQL. This also allows the data type to be a PL/SQL associative array. (In prior releases, the collection's data type had to be declared at the schema level.)

The removal of these restrictions increases the power of expression and the usefulness of PL/SQL. In particular, the extended flexibility of the table operator allows code written to run other vendors' stored procedure languages to be easily migrated to PL/SQL.

See Also:

Oracle Database PL/SQL Language Reference for details

1.1.4.13 Precompilers Support for SQL Plan Management （SQL执行计划管理的预编译支持,11g推出的SQL执行计划管理）

There are new command-line options for the generation of plan baseline SQL statements providing control of the name and format of generated SQL files and log files.

This support avoids performance regression of SQL statement execution and provides easier upgrade of precompiler applications.

See Also:

Pro*C/C++ Programmer's Guide for details

1.1.4.14 SQLJ Support for SQL Plan Management (SQLJ对SPM的支持)

The following are new features in this release for SQLJ support for SQL plan management (SPM):

• Command line and property file options for the generation of plan baseline SQL statements.

• Generation of a SQL file containing the statements for creating SPM plans.

• Control in the naming of generated log files and Java files

This new support helps make the upgrade of SQLJ applications easier and helps to avoid performance regression of SQL statement execution.

See Also:

Oracle Database SQLJ Developer's Guide for details

1.1.4.15 Temporal Validity （这个比较新，因为数据库的录入和实际业务活动，默写场景下是不一致的，可以自己定义时间字段的组合）

With Temporal Validity, you can add one or more valid time dimensions to a table using existing columns, or using columns automatically created by the database.

Applications often indicate the validity of a fact recorded in the database with dates or time stamps that are relevant to the underlying business they manage. Examples of such dates include the hire date and termination date of an employee in a Human Resources application, the effective date range of coverage for an insurance policy, or the time duration for a stock price. Temporal Validity reduces the complexity of application code by providing a simple declarative interface to allow applications to manage the validity of rows.

See Also:

Oracle Database Development Guide for details

1.1.4.16 Temporal Validity Flashback Queries

Flashback Query has been extended to support queries on Temporal Validity dimensions. Users can now execute queries with theAS OF andVERSIONS BETWEEN clauses based on one or more valid time periods on the underlying tables. Flashback Queries that combine Temporal Validity and Transaction Time Temporal (tracked using Flashback Data Archive) are called bi-temporal queries.

Users can now query data based on current values (that is, CURRENT in valid time and transaction time), what we know now (that is,AS OF in valid time;CURRENT in transaction time), or what we knew before (that is,AS OF in valid time and transaction time), giving declarative access to all possible views of data based on the two time dimensions. Bi-temporal queries in Oracle Database 12c Release 1 (12.1) provide functionality previously available only with extensive and complex application code.

新特性Code Based Access Control (CBAC）,这个可以精细到每个存储过程的权限，每个存储过程有自己的权限，当然最大不能超过被赋予自己role的权限，在进行动态编译时也会去检查权限。