Работа с обзорами (представлениями) в Oracle

По своему назначению и функциональным возможностям обзоры в Oracle аналогичны обзорам в InterBase.

Синтаксис обзора имеет следующий вид.

create [or replace] [[no] force] view [<schema>.]<view_name> [{(<list_vnames>) | <obj_description>}] as <select_constr> [<with_constr>];

force – формирует обзор даже если таблицы или другие объекты, на которые он ссылается, не существуют или недоступны. По умолчанию принято no force.

<schema> - имя схемы.

<view_name> - имя обзора.

<list_vnames> - список имен выбираемых полей. Если не приведен, то используются имена, перечисленные в операторе select. Список имен должен согласовываться со списком в операторе select.

<list_vnames> ::= <vname>[,<list_vnames>]

<vname> - имя выбираемого поля.

<obj_description> ::= of [<schema>.]<type_name> [with object identifier {default | (<list_attr>)}]

<obj_description> - задает тип <type_name> возвращаемого объекта, ранее описанного в схеме <schema>. Описание объектных данных см. в технической документации Oracle.

<select_constr> - любой синтаксически корректный оператор выборки select.

<with_constr> ::= {read only | check option [constraint <t_constr>]}

 read only – запрещает внесение изменений с использованием обзора (insert, update, delete).

check option [constraint <t_constr>] – разрешает внесение изменений с использованием обзора (insert, update, delete), если это возможно, то есть измененные данные удовлетворяют условиям выборки.

Триггеры для обзоров аналогичны триггерам для таблиц за исключением того, что вместо опции after или before используется опция instead of.

7.4. Дан­ные BLOB

BLOB пред­став­ля­ет со­бой тип дан­ных с ди­на­ми­че­ски из­ме­няе­мым раз­ме­ром, для это­го ти­па не ука­зы­ва­ют­ся ни раз­ме­ры, ни спо­соб ко­ди­ро­ва­ния. BLOB мож­но ис­поль­зо­вать для хра­не­ния объ­ек­тов раз­ной при­ро­ды и на­зна­че­ния, вклю­чая:

• Рас­тро­вые изо­бра­же­ния.

• Век­тор­ные ри­сун­ки.

• Зву­ки, ви­део и дру­гую ин­фор­ма­цию муль­ти­ме­диа.

• Текст и дан­ные, вклю­чая до­ку­мен­ты боль­шо­го объ­е­ма.

Дос­туп к дан­ным BLOB осу­ще­ст­в­ля­ет­ся так же, как к дан­ным, хра­ни­мым в дру­гих фор­ма­тах. В то же вре­мя, учи­ты­вая не­оп­ре­де­лен­ный раз­мер дан­ных, про­це­ду­ры чте­ния и за­пи­си дан­ных BLOB име­ют не­ко­то­рую спе­ци­фи­ку в раз­лич­ных СУБД.

7.5. Функ­ции поль­зо­ва­те­ля (UDF)

На­зна­че­ние и воз­мож­но­сти ис­поль­зо­ва­ния

Поль­зо­ва­тель­ские функ­ции (User defined functions – UDF) – это про­грам­мы на ба­зо­вом язы­ке для вы­пол­не­ния в при­ло­же­ни­ях, час­то ис­поль­зуе­мых при ра­бо­те с ба­зой дан­ных за­дач. UDF по­мо­га­ют обес­пе­чить мо­дуль­ность при­ло­же­ний, вы­де­ляя их в от­дель­ные мо­ду­ли мно­го­крат­но­го ис­поль­зо­ва­ния.

Поль­зо­ва­тель­ские функ­ции не яв­ля­ют­ся ча­стью ба­зы. Они по­ме­ща­ют­ся в от­дель­ную биб­лио­те­ку (DLL), на­хо­дя­щую­ся на той же ма­ши­не, что и ба­за дан­ных. Что­бы сде­лать функ­ции дос­туп­ны­ми в ба­зе, их на­до в ней объ­я­вить. По­сле это­го об­ра­ще­ние к ним осу­ще­ст­в­ля­ет­ся точ­но так же, как к стан­дарт­ным функ­ци­ям InterBase. Дру­ги­ми сло­ва­ми, вы­зов этих функ­ций мо­жет про­из­во­дить­ся вез­де, где син­так­си­сом SQL пре­ду­смот­ре­но ис­поль­зо­ва­ние вы­ра­же­ний.

Что­бы мож­но бы­ло ис­поль­зо­вать внеш­нюю функ­цию, она долж­на быть объ­яв­ле­на в ба­зе как внеш­няя функ­ция, а реа­ли­зую­щий ее про­грамм­ный код по­ме­щен в биб­лио­те­ку, ука­зан­ную в объ­яв­ле­нии.

Объ­яв­ле­ние внеш­ней функ­ции

Как толь­ко UDF бы­ла на­пи­са­на и от­ком­пи­ли­ро­ва­на в биб­лио­те­ку, она долж­на быть объ­яв­ле­на в ба­зе дан­ных, где пред­по­ла­га­ет­ся ее ис­поль­зо­вать. Для это­го пре­ду­смот­ре­на ко­ман­да declare external function.

Ка­ж­дая функ­ция в ба­зе дан­ных долж­на быть объ­яв­ле­на от­дель­но. По­ка точ­ка вхо­да и имя мо­ду­ля (биб­лио­те­ки) и путь к не­му не из­ме­ня­ет­ся, нет не­об­хо­ди­мо­сти в пе­ре­объ­яв­ле­нии функ­ции, да­же ес­ли са­ма функ­ция из­ме­ня­ет­ся.

declare external function<name> <list_datatype>
returns {<datatype>[by value] | cstring(<int>)} [free_it]
entry_point 'entryname' module_name 'modulename';

<list_datatype> ::= {<datatype> | cstring(<int>)} [,<list_datatype>]

Ес­ли UDF воз­вра­ща­ет зна­че­ние по ссыл­ке, а для фор­ми­руе­мо­го зна­че­ния ис­поль­зу­ет ди­на­ми­че­ски за­пра­ши­вае­мую па­мять, то не­об­хо­ди­мо по­за­бо­тить­ся о ее ос­во­бо­ж­де­нии. Для ука­за­ния этого ис­поль­зу­ет­ся кон­ст­рук­ция free_it.

<name> - имя UDF для ис­поль­зо­ва­ния в ко­ман­дах SQL; мо­жет от­ли­чать­ся от име­ни функ­ции (в биб­лио­те­ке) ука­зан­ной по­сле клю­че­во­го сло­ва entry_point

<datatype> - тип дан­ных вход­но­го па­ра­мет­ра или воз­вра­щае­мо­го зна­че­ния.

Все вход­ные па­ра­мет­ры пе­ре­да­ют­ся UDF по ссыл­ке.

Воз­вра­щае­мое зна­че­ние мо­жет пе­ре­да­вать­ся как по ссыл­ке, так и по зна­че­нию.

Ис­поль­зо­ва­ние эле­мен­тов мас­си­ва за­пре­ще­но

returns - оп­ре­де­ля­ет воз­вра­щае­мое функ­ци­ей зна­че­ние

by value - ука­зы­ва­ет, что воз­врат про­из­во­дит­ся по зна­че­нию. Ес­ли кон­ст­рук­ция опу­ще­на, то воз­врат про­из­во­дит­ся по ссыл­ке

cstring (<int>) - ука­зы­ва­ет, что UDF воз­вра­ща­ет стро­ку, длиной не более указанной и за­кан­чи­ваю­щую­ся ог­ра­ни­чи­те­лем \0

free_it - ос­во­бо­ж­да­ет па­мять, за­ня­тую воз­вра­щае­мым зна­че­ни­ем по­сле вы­пол­не­ния UDF. Следует ис­поль­зо­вать толь­ко, ес­ли па­мять в UDF вы­де­ля­лась ди­на­ми­че­ски

'entryname' - за­клю­чен­ное в апострофы имя UDF, как оно за­пи­са­но в биб­лио­те­ке

'modulename' - спе­ци­фи­ка­ция фай­ла, иден­ти­фи­ци­рую­щая биб­лио­те­ку, ко­то­рая со­дер­жит UDF. Текст дол­жен за­клю­чать­ся в апострофах.

Биб­лио­те­ка долж­на по­сто­ян­но на­хо­дить­ся на сер­ве­ре; путь дол­жен от­но­сить­ся к ме­сто­по­ло­же­нию биб­лио­те­ки на сер­ве­ре.

Соз­да­ние поль­зо­ва­тель­ских функ­ций

Ме­ха­низм User Defined Functions (UDF), по­зво­ля­ет пи­сать поль­зо­ва­тель­ские функ­ции на лю­бом ком­пи­ли­рую­щем ин­ст­ру­мен­те раз­ра­бот­ки (ба­зо­вом язы­ке). Та­кие функ­ции вы­пол­ня­ют­ся на сер­ве­ре, при­чем в рам­ках про­цес­са сер­ве­ра, что по­вы­ша­ет ско­рость их вы­зо­ва прак­ти­че­ски до уров­ня ско­ро­сти вы­зо­ва стан­дарт­ных SQL-функ­ций.

UDF мо­гут быть на­пи­са­ны, во­об­ще го­во­ря, на лю­бом язы­ке. Здесь мы рас­смот­рим под­го­тов­ку функ­ций на C (C++). Пре­ж­де все­го, от­ме­тим, что все пе­ре­да­вае­мые в UDF па­ра­мет­ры все­гда пе­ре­да­ют­ся по ссыл­ке, то есть долж­ны быть объ­яв­ле­ны в функ­ции как ука­за­те­ли. Воз­вра­щае­мое зна­че­ние мо­жет пе­ре­да­вать­ся и по ссыл­ке и по зна­че­нию. Ранние версии InterBase включали толь­ко ба­зо­вый на­бор функ­ций для ис­поль­зо­ва­ния в SQL-вы­ра­же­ни­ях, что делало разработку таких функций практически обязательной, не случайно в комплект поставки входила небольшая библиотека UDF. В более поздних версиях набор встроенных функций значительно расширен, кроме того появилась возможность писать пользовательские функции средствами языка SQL, позволяя в таких функциях работать с объектами базы данных.

Тем не менее, такие функции могут быть весьма полезными в различных приложениях.

Рас­смот­рим про­цесс соз­да­ния биб­лио­те­ки функ­ций поль­зо­ва­те­ля. Де­та­ли про­цес­са за­ви­сят, во­об­ще го­во­ря, от ис­поль­зуе­мо­го ком­пи­ля­то­ра. По­стро­им та­кую биб­лио­те­ку для Windows в сре­де C++Builder. Вос­поль­зу­ем­ся DLL Wizard’ом, не­об­хо­ди­мым для кор­рект­ной на­строй­ки ком­по­нов­щи­ка в фай­ле про­ек­та. В дру­гих ком­пи­ля­то­рах про­ект бу­дет стро­ить­ся ина­че, но сам текст про­грамм бу­дет иден­ти­чен. В на­ча­ле тек­ста под­клю­ча­ет­ся за­го­ло­воч­ный файл <windows.h>, за ко­то­рым объ­яв­ля­ют­ся на­ши функ­ции (их объ­яв­ле­ния мож­но так­же по­мес­тить в свой за­го­ло­воч­ный файл).

//----------------------------------------
#include <windows.h>
extern "C" long __declspec(dllexport)
if_i(double* m,long* a,long* b);
extern "C" double * __declspec(dllexport)
if_d(double* m,double* a,double* b);
…
#pragma argsused
int WINAPI DllEntryPoint(HINSTANCE hinst,
unsigned long reason, void* lpReserved)
{
return 1;
}
//-----------------------------------------
. . . . . .

В объ­яв­ле­нии функ­ций сто­ит об­ра­тить вни­ма­ние на кон­ст­рук­ции extern "C" и __declspec(dllexport). Эти кон­ст­рук­ции обес­пе­чи­ва­ют кор­рект­ность хра­не­ния имен и вы­зо­ва функ­ций из соз­да­вае­мой DLL. Да­лее идет текст са­мих функ­ций. Ос­та­но­вим­ся на нем не­сколь­ко под­роб­нее.

Пер­вая функ­ция if_i яв­ля­ет­ся ана­ло­гом ус­лов­ной опе­ра­ции C. Она воз­вра­ща­ет вто­рой ар­гу­мент, ес­ли зна­че­ние пер­во­го по­ло­жи­тель­но, и тре­тий – в про­тив­ном слу­чае.

long __declspec(dllexport)
if_i(double* m,long* a,long* b)
{return (*m>0)?*a:*b;}

Со­от­вет­ст­вую­щее ей объ­яв­ле­ние внеш­ней функ­ции в ба­зе бу­дет иметь та­кой вид:

declare external function iif double precision, integer, integer
returns integer by value entry_point ‘_if_I’
module_name ‘MyDLL.dll’;

Име­на функ­ции в ба­зе и в биб­лио­те­ке мо­гут и не сов­па­дать. Кро­ме то­го, нуж­но пом­нить, что име­на функ­ций в биб­лио­те­ке за­ви­сят от ре­ги­ст­ра, а в ба­зе – нет.

Отметим, что в настоящее время подобная функция введена как iif(<cond>, <res_true>, < res_false >) в состав встроенных функций.

Как пример функции, которая может быть полезна в ряде приложений, прежде всего бухгалтерских, можно привести функцию, возвращающую текстовое представление чисел («сумма прописью»). 

Внеш­нии функ­ции в oracle

Объявления внешней функции в Oracle осуществляется в два этапа. На первом этапе объявляется библиотека, содержащая требуемые функции. На втором объявляются сами внешние функции.

Объявление библиотеки имеет следующий вид.

create [or replace] library [<schema>.]<ext_lib> is '<file_spec>';

<ext_lib> - задает имя библиотеки

<file_spec> - задает путь и имя библиотеки

Объявление внешней функции имеет следующий вид.

create [or replace] function [<schema>.]<func_name> [(<list_par>)] return <datatype> as <call_spec>;

<call_spec> ::= [{as | is}] language {c library <ext_lib> | java name '< ext_lib>';

"<efunc_name> - задает имя функции в библиотеке.