Shulou(Shulou.com)06/01 Report--

[TechTarget China original] this article is the fourth in a series of new features of Oracle Database 12c, and the author will focus on some new features that are very useful to developers. Related reading: summary of new features of Oracle database 12c (1), new features of Oracle database 12c (2), new features of Oracle database 12c (3)

The fourth part includes:

How to truncate a main table to restrict records of Top-N query results when a child table contains data improved extended data types for various enhanced session-level sequence WITH statements

Truncation table CASCADE

In previous versions, truncation of a master table was not provided when the child table referenced a master table and where records existed in the child table. On the other hand, the TRUNCATE TABLE with CASCADE operation in 12c can truncate the records in the main table, recursively truncate the child tables automatically, and refer to them as DELETE ON CASCADE obeying foreign keys. Because this applies to all child tables, there is no CAP for the number of recursive levels, which can be grandchild tables, great-grandchild tables, and so on.

This enhancement rejects the premise of truncating all child table records before truncating a master table. The new CASCADE statement can also be applied to table partitions and child table partitions.

SQL > TRUNCATE TABLE CASCADE

SQL > TRUNCATE TABLE PARTITION CASCADE

If the ON DELETE CASCADE option is not defined for the foreign key of the child table, an ORA-14705 error is thrown.

Restrict records for Top-N query results

In previous versions, there were a variety of indirect means to get Top-N query results for top or bottom records. In 12c, the process is simplified and made more straightforward with a new FETCH FIRST | NEXT | PERCENT statement. To retrieve the top 10 payroll records from the EMP table, you can use the following new SQL statement:

SQL > SELECT eno,ename,sal FROM emp ORDER BY SAL DESC

FETCH FIRST 10 ROWS ONLY

The following example gets all similar records for the top N. For example, if the salary value in row 10 is 5000 and there are other employees whose salaries meet the top N criteria, they will also be obtained by the WITH TIES statement.

SQL > SELECT eno,ename,sal FROM emp ORDER BY SAL DESC

FETCH FIRST 10 ROWS ONLY WITH TIES

The following example restricts getting the top 10% records from the EMP table:

SQL > SELECT eno,ename,sal FROM emp ORDER BY SAL DESC

FETCH FIRST 10 PERCENT ROWS ONLY

The following example ignores the first five records and displays the next five records of the table:

SQL > SELECT eno,ename,sal FROM emp ORDER BY SAL DESC

OFFSET 5 ROWS FETCH NEXT 5 ROWS ONLY

All of these limitations can also be applied to PL/SQL blocks.

BEGIN

SELECT sal BULK COLLECT INTO sal_v FROM EMP

FETCH FIRST 100 ROWS ONLY

END

Various enhancements to SQL*Plus

The implicit result of SQL*Plus: in 12c, SQL*Plus returns the result from an implicit cursor of a PL/SQL block without actually binding a RefCursor. This new dbms_sql.return_result procedure will return and format the results specified by the SELECT statement query in the PL/SQL block. The following code describes this usage:

SQL > CREATE PROCEDURE mp1

Res1 sys_refcursor

BEGIN

Open res1 for SELECT eno,ename,sal FROM emp

Dbms_sql.return_result (res1)

END

SQL > execute mp1

When this process is performed, the formatted record is returned on the SQL*Plus.

Show invisible fields: in the first part of this series, I have explained the new features of invisible fields. When fields are defined as invisible, they will not be displayed when describing the table structure. However, you can display information about invisible fields by making the following settings at the SQL*Plus prompt:

SQL > SET COLINVISIBLE ON | OFF

The above settings are valid only for the DESCRIBE command. It currently does not have an effect on the results of SELECT statements on invisible fields.

Session level sequence

New session-level database sequences can now be created in 12c to support session-level sequence values. The types of these sequences are most suitable for global temporary tables with session levels.

The session-level sequence produces a unique range of values that are limited within, rather than beyond, the session. Once the session is terminated, the state of the session sequence also disappears. The following example explains the creation of a session-level sequence:

SQL > CREATE SEQUENCE my_seq START WITH 1 INCREMENT BY 1 SESSION

SQL > ALTER SEQUENCE my_seq GLOBAL | SESSION

For session-level sequences, CACHE, NOCACHE, ORDER, or NOORDER statements are ignored.

Improvement of WITH statement

In 12c, you can use SQL to run PL/SQL functions or procedures faster, which are defined and declared by the WITH statement of the SQL statement. The following example shows how to define and declare a procedure or function in a WITH statement:

WITH

PROCEDURE | FUNCTION test1 (…)

BEGIN

END

SELECT FROM table_name

/

Although you cannot use the with statement directly in the PL/SQL unit, it can be referenced by a dynamic SQL in the PL/SQL unit.

Extended data type

In 12c, the size of data types such as VARCHAR2, NAVARCHAR2, and RAW expands from 4K and 2K bytes to 32K bytes compared to previous versions. Whenever possible, the size of extended characters reduces the use of the LOB data type. To enable extended character size, you must set the initial database parameter of MAX_STRING_SIZE to EXTENDED.

To use extended character types, you need to perform the following procedures:

1. Shut down the database

two。 Restart the database in upgrade mode

3. Change parameter: ALTER SYSTEM SET MAX_STRING_SIZE=EXTENDED

4. Execute utl32k.sql as sysdba: SQL > @? / rdbms/admin/utl32k.sql

5. Shut down the database

6. Restart the database in read-write mode

Compared to LOB data types, in ASSM tablespace management, fields of extended data types are stored in SecureFiles LOB, while in non-ASSM tablespace management, they are stored in BasciFiles LOB.

Note: once changed, you can no longer change the settings back to STANDARD.

Original link: http://www.searchdatabase.com.cn/showcontent_75994.htm

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.