Добавил:
Upload Опубликованный материал нарушает ваши авторские права? Сообщите нам.
Вуз: Предмет: Файл:
Programming PL SQL.doc
Скачиваний:
3
Добавлен:
01.07.2025
Размер:
5 Мб
Скачать
☆

1.4.2.2 Table functions

A table function is a function that returns a result set (in the form of a PL/SQL collection) and can be called in the FROM clause of a query. Table functions were available in a very limited fashion in Oracle8i, but Oracle9i has greatly expanded the scope and usefulness of these functions. In Oracle9i it is now possible for you to write functions that do the following:

Return rows from a result set in "pipelined fashion"

This means that data is returned while the function is still executing.

Participate fully in parallel query execution

In other words, the function can be run simultaneously within multiple "slave processes" against different, partitioned elements of data.

Here is an example of the header of a pipelined table function that can run in parallel, defined so that all the rows for a given department go to the same slave process, and all rows are delivered consecutively:

CREATE OR REPLACE FUNCTION my_transform_fn (

p_input_rows in employee_info.recur_t )

RETURN employee_info.transformed_t

PIPELINED

CLUSTER P_INPUT_ROWS BY (department)

PARALLEL_ENABLE

( PARTITION P_INPUT_ROWS BY HASH (department))

You can find additional information about table functions, pipelining, and parallel execution in Chapter 16.

1.4.2.3 New and improved datatypes

Oracle now offers dramatically improved support for timestamps, time zone management, and interval calculations (the amount of time between two dates or timestamps). The following block showcases a number of aspects of these new datatypes and associated functions:

DECLARE

boss_free TIMESTAMP(0) WITH TIME ZONE;

steven_leaves TIMESTAMP(0) WITH TIME ZONE;

window_for_conversation INTERVAL DAY(3) TO SECOND(3);

BEGIN

boss_free := TO_TIMESTAMP_TZ (

'29-JAN-2002 12:00:00.0 US/Pacific PST',

'DD-MON-YYYY HH24:MI:SSXFF TZR TZD' );

steven_leaves := TO_TIMESTAMP_TZ (

'29-JAN-2002 16:45:00.0 US/Central CST',

'DD-MON-YYYY HH24:MI:SSXFF TZR TZD' );

window_for_conversation := steven_leaves - boss_free;

DBMS_OUTPUT.PUT_LINE (

TO_CHAR (window_for_conversation, 'HH:MI:SSXFF' ));

-- Implicit conversion from TimeZone to Date...

DBMS_OUTPUT.PUT_LINE ( ADD_MONTHS (boss_free, -5 ) );

END;

Chapter 10 explores these new datatypes in detail.

On a completely different front, Oracle has now incorporated XML functionality directly into the database with the addition of the XMLType datatype (implemented as an object type). Oracle has committed itself to supporting XML, and with its own XML native datatype hopes to blur the lines between SQL and XML. In Oracle9i, you can issue SQL queries against XML documents stored in table columns. You can also use XPath syntax to search the contents of those documents.

Here is an example of inserting an XML document into a table (notice the use of the CREATEXML method of the XMLType object type to convert a string to an XML document):

INSERT INTO env_analysis

VALUES ('ACME SILVERPLATING',

TO_DATE ('15-02-2001', 'DD-MM-YYYY'),

XMLTYPE.CREATEXML (

'<?xml version="1.0"?>

<report>

<site>1105 5th Street</site>

<substance>PCP</substance>

<level>1054</level>

</report>'));

The following CREATE statement utilizes XPath syntax to create a function-based index on the first 26 characters of the customer's name on a purchase order:

CREATE UNIQUE INDEX i_purchase_order_reference

ON purchaseorder p (

SUBSTR(XMLTYPE.GETSTRINGVAL(

XMLTYPE.EXTRACT(

p.PODOCUMENT,

'/PurchaseOrder/Customer/text( )')),1,26)

);

Chapter 12 provides a lot more detail on the XMLType datatype.

Соседние файлы в предмете [НЕСОРТИРОВАННОЕ]