WebClub - Всероссийский Клуб Веб-разработчиков
WebClub.RU » Архив » Процедурное расширение языка SQL - PL/SQL

Процедурное расширение языка SQL - PL/SQL


Дата публикации: 25-03-2013

Oracle Server - полнофункциональная реляционная СУБД, которая идеально подходит для архитектур клиент/сервер и интернет/интранет. Особенности внутренней архитектуры Oracle ориентированы на обеспечение готовности, максимальной пропускной способности, безопасности и эффективного использования ресурсов.

Oracle также присущи черты, связанные с используемым языком программирования, которые способствуют ускорению разработки и улучшению эффективности серверной части приложений:

Один из основных компонентов Oracle Server - его процессор PL/SQL. (PL - Procedural Language - процедурный язык.)

PL/SQL - язык Oracle четвертого поколения, объединяющий структурированные элементы процедурного языка программирования с языком SQL, разработанный специально для организации вычислений в среде клиент/сервер. Он позволяет передать на сервер программный блок PL/SQL, содержаший логику приложения, как оператор SQL, одним запросом. Используя PL/SQL, можно значительно уменьшить объем обработки в клиентской части приложения и нагрузку на сеть. Например, может понадобиться выполнить различные наборы операторов SQL в зависимости от результата некоторого запроса. Запрос, последующие операторы SQL и операторы условного управления могут быть включены в один блок PL/SQL и пересланы серверу за одно обращение к сети.

При этом вся логика приложений делится на клиентскую и серверную части. Серверная часть может быть релаизована в виде функций, хранимых процедур и пакетов.

Функции. Часть логики приложения ориентированной на выполнение конкретного комплекса операций на сервере, результат которых возвращается в виде значения функции. Откомпилированные функции и их исходные тексты содержатся в базе данных.

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

Пакеты. Часть логики приложений: фукций и пакетов, предназначеных для решениях задач в рамках одного модуля (подсистемы) АИС.

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

Декларативная целостность. Ограничения активизируются сервером всякий раз, когда записи вставляются, обновляются или удаляются. В дополнение к ограничениям ссылочной целостности, которые проверяют соответствие первичного и внешнего ключей, можно также накладывать ограничения на значения, содержащиеся в столбцах таблицы. Поддержка целостности на сервере уменьшает размер кода клиентской части, необходимого для проверки допустимости данных, и увеличивает устойчивость бизнес- модели, определенной в базе данных.

Список, зарезервированных слов PL/SQL

Язык PL/SQL также включает зарезервированные слова, имеющие определенное значение в операторах PL/SQL.

 

ABORT

DEFINITION

NOT

TADAUTH

ACCEPT

DELAY

NULL

TABLE

ACCESS

DELETE

NUMBER

TABLES

ADD

DELTA

NUMBER_BASE

TASK

ALL

DESC

OF

TERMINATE

ALTER

DIGITS

ON

THEN

AND

DISPOSE

OPEN

TO

ANY

DISTINCT

OPTION

TRUE

ARRAY

DO

OR

TYPE

ARRAYLEN

DROP

ORDER

UNION

AS

ELSE

OTHERS

UNIQUE

ASC

ELSIF

OUT

UPDATE

ASSERT

END

PACKAGE

USE

ASSIGN

ENTRY

PARTITION

VALUES

AT

EXCEPTION

PCTFREE

VARCHAR

AUTHORIZATION

EXCEPTION_INIT

PRAGMA

VARCHAR2

AVG

EXISTS

PRIOR

VARIANCE

BEGIN

EXIT

PRIVATE

VIEW

BETWEEN

FALSE

PROCEDURE

VIEWS

BODY

FETCH

PUBLIC

WHEN

BOOLEAN

FOR

RAISE

WHERE

BY

FORM

RANGE

WHILE

CASE

FROM

REAL

WITH

CHAR

FUNCTION

RECORD

WORK

CHAR_BASE

GENERIC

RELEASE

XOR

CHECK

GOTO

REM

 

CLOSE

GRANT

RENAME

 

CLUSTER

GROUP

RESOURCE

CLUSTERS

HAVING

RETURN

 

COLAUTH

IDENTIFIED

REVERSE

 

COLUMNS

IF

REVOKE

 

COMMIT

IN

ROLLBACK

COMPRESS

INDEX

ROWNUM

 

CONNECT

INDEXES

ROWTYPE

CONSTANT

INDICATOR

RUN

 

COUNT

INSERT

SAVEPOINT

CRASH

INTEGER

SCHEMA

 

CREATE

INTERSECT

SELECT

 

CURRENT

INTO

SEPARATE

CURSOR

IS

SET

 

DATABASE

LEVEL

SIZE

 

DATA_BASE

LIKE

SPACE

 

DATE

LIMITED

SQL

 

DBA

LOOP

SQLCODE

 

DEBUGOFF

MAX

SQLERRM

 

DEBUGON

MIN

START

 

DECIMAL

MINUS

STATEMENT

DECLARE

MOD

STDDEV

 

DEFAULT

NEW

SUBTYPE

 
 

NOCOMPRESS

SUM

 

Функции

Числовые функции

 

Функция

Возвращаемое значение

ABS(n)

Абсолютное значение величины п.

CEIL(n)

Наименьшее целое, большее или равное п,

COS(n)

Косинус п (угла, выраженного в радианах).

COSH(n)

Гиперболический косинус п.

ЕХР(я)

e в степени n.

FLOOR(n)

Наибольшее целое, меньшее или рапное п.

LN(n)

Натуральный логарифм п, где п>0.

LOG(m,n)

Логарифм м по основанию т.

MOD(m.n)

Остаток от деления т на п.

POWER(w.n)

т в степени п.

ROUND(n[,m])

п, округленное до т позиций после десятичной точки. По умолчанию т равно нулю.

SIGN(n)

Если.п<0,-1;еслии=0, 0; еслип>0, 1. ,

SIN(n)

Синус л (угла, выраженного в радианах).

SINHM

Гиперболический синус.

SQRT(n)

Квадратный корень отп. Если п<0, возвращает значение NULL.

TAN(n)

Тангенс я (угла, выраженного в радианах).

TANH(n) .

Гиперболический тангенс п.

TRUNC(n[,m])

п, усеченное до т позиций после от десятичной точки. По умолчанию т равно нулю.

Символьные функции

Символьные функции, возвращающие символьные значения:

 

Функция 1

Возвращаемое значение

CHR(n)

Символ с кодом п.

СОNСАТ(сhar1,char2)

Конкатенация символьных строк char1 и char2.

INITCAP(char)

Символьная строка сhar, первые буквы всех слов в которой преобразованы в прописные.

LOWER(char)

Символьная строка char, осе буквы которой преобразованы d строчные.

LPAD(char1.n [,char2})

Символьная строка chur1, которая дополняется слева последовательностью символов из char2 так, чтобы общая длина строки стала равна п. Значение chur2 по умолчанию -" (один пробел). Если часть многобайтового символа не помещается в добавляемой строке, то конец строки заполняется пробелами.

LTRIM(char[,set])

Символьная строка char, в которой удалены все символы от начала вплоть до первого символа, которого нет в строке set. Значение set по умолчанию - '' (один пробел).

NLS_INITCAP(char[,nls_sort])

Символьная строка char, в которой первые буквы всех слов преобразованы в прописные. Параметр nIs_sort определяет последовательность сортировки.

NLS_LOWER(char[,nls_sort])

Символьная строка char, все буквы которой преобразованы в строчные. Параметр tils-sort определяет последовательность сортировки.

NLS_UPPER(char[,nls_sort])

Символьная строка char, все буквы которой преобразованы в прописные. Параметр nts_sort определяет последовательность сортировки.

REPLACE(char, search_string [,replacement_string])

Символьная строка char, в которой все фрагменты search_string заменены на replacement_string. Если параметр replacement_string не определен, все фрагменты search-string удаляются.

RPAD(char1.n[,char2])

Символьная строка charl, которая дополнена справа последовательностью символов из chur2 так, что общая длина строки равна n. Если часть многобайтового символа не помещается в добавляемой строке, то конец строки заполняется пробелами.

RTRIM(char[,set])

Символьная строка char, в которой удалены все символы справа вплоть до первого символа, которого нет в строке set. Значение параметра set по умолчанию -1 1 (один пробел).

SOUNDEX(char)

Символьная строка, содержащая фонетическое представление для char, на английском языке.

SUBSTR(char,m[,n])

Фрагмент символьной строки char, начинающийся с символа т, длиной п символов (до конца строки, если параметр п не указан).

SUBSTRB(char,m[,n])

Фрагмент символьной строки char, начинающийся с символа т, длиной л байтов (до конца строки, если параметр п не указан).

TRANSLATE(char,from, to)

Символьная строка char, в которой все символы, встречающиеся в строке from, заменены на соответствующие символы из to.

UPPER(char)

Символьная строка char, в которой все буквы преобразованы в прописные.

Символьные функции, возвращающие числовые значения

 

Функция

Возвращаемое значение

ASCll(char)

Возвращает десятичный код первого символа строки char в кодировке, принятой в базе данных. (Код ASCII в системах, использующих кодировку ASCII). Возвращает значение первого байта многобайтового символа.

INSTR(charl.char2[,n[,m]])

Позиция первого символа m-ого фрагмента строки charl, совпадающего со строкой char2, начиная с n-ого символа. По умолчанию n u m равны 1. Номер символа отсчитывается от первого символа строки charl, даже когда п> 1.

INSTRB(charl.char2[,n[,m]])

Позиция первого символа т-ого фрагмента строки charl, совпадающего со строкой char2, начиная с m-ого байта. По умолчанию п и т равны 1. Номер байта отсчитывается от первого символа строки charl, даже когда л> 1. .

LENGTH(char)

Длина строки char в символах.

LENGTHB(c/iar)

Длина строки char в байтах.

NLSSORT(char1,char2[,n[,m]])

Зависящее от национального языка значение, используемое при сортировке строки char.

Групповые функции

 

Функция

Возвращаемое значение

AVG([DISTINCT|ALL]n)

Среднее значение от n, нулевые значения опускаются.

COUNT([ALL]*)

Число строк, извлекаемых в запросе или подзапросе.

COUNT(IDISTINCT|ALL] expr)

Число строк, для которых expr принимает не пустое значение.

MAX([D1STINCT|ALL] expr)

Максимальное значение выражения eхрr.

MIN((DISTINCT|ALL] expr)

Минимальное значение выражения eхрr.

STDDEV([DISTINCT|ALL] n)

Стандартное отклонение величины л, нулевые значения опускаются.

SUM([DISTINCT|ALL] n)

Сумма значений n

VARIANCE([DIST1NCTIALL]n)

Дисперсия величины п, нулевые значения опускаются.

Функции работы с датами

 

Функция

Возвращаемое значение

ADD-MONTHS (d,n)

Дата d плюс n месяцев.

LAST-DAY (d)

Последнее число месяца, указанного в d

MONTHS-BETWEEN (d,e)

Число месяцев между датами d1 и d2.

NEW-TIME (d,a,b)

Дата и время в часовом поясе a, соответствующие дате и времени в часовом поясе b, при этом d,a и b значения типа CHAR, определяющие часовые пояса.

NEW-DAY (d,char)

Дата первого после даты (/дня недели, название которого записано в с1шг.

SYSDATE

Текущая дата и время.

Усечение и округление дат

 

Функция

Возвращаемое значение

ROUND(d[,fmt])

Дата d, округленная до единиц, указанных в форматной маске.

TRUNC(d[,fmt])

Дата d, усеченная по форматной маске fmt.

Форматные маски дат для функций ROUND и TRUNC.

В таблице перечислены форматные маски, которые можно использовать в функциях ROUND и TRUNC. По умолчанию используется форматная маска "DD".

 

Форматная маска

Возвращаемое значение

CC или SCC

Первый день столетия

SYYYY или YYYY или YYY или YY или Y или YEAR или SYEAR

Первый день года ( округляется до 1 июля)

Q

Первый день квартала (округляется до 16 числа второго месяца квартала)

MONTH или MON или MM или RM

Первый день месяца (округляется до 16 числа)

WW или IW

Тот же день недели, что и первый день текущего года

W

Тот же день недели, что и первый день текущего месяца

DDD или DDD или J

День

DAY или DY или D

Первый день недели

HH HH12 HH24

Час

MI

Минута

Функции преобразования

 

Функция

Возвращаемое значение

CHARTOROWID(char)

Char преобразуется из типа данных CHAR

в тип данных ROWID

CONVERT( char, dest_char_set [,source_char_set])

Преобразует символьную строку из набора символов source_char_set в набор символов dest_char_set

HEXTORAW ( char)

Преобразует значение char, содержащее шестнадцатиричные цифры, в значение типа данных RAW

RAWTOHEX ( raw)

Преобразует raw в символьное значение, содержащее его шестнадцатиричный эквивалент

ROWIDTOCHAR (rowid)

Преобразует значение типа ROWID в значение типа CHAR

TO_CHAR ( expr [,fmt [,'nls_num_fmt']])

Преобразует значение expr типа DATE или NUMBER в значение типа CHAR по формату форматной маски fmt. Если fmt отсутствует, значения типа DATE преобразуются по формату, заданному по умолчанию, и значения типа NUMBER- в значение типа CHAR с шириной, достаточной для того, чтобы вместить все значащие цифры. Значение 'nls_num_fmt' определяет связанные с языком форматные маски. В Trusted ORACLE преобразует значения MLS или MLS_LABEL в значение типа VARCHAR2

TO_DATE ( char[,fmt [,'nls_lang']])

Преобразует char в значение типа DATE с помощью форматной маски fmt. Если fmt опускается, используется форматная маска для даты, принятая по умолчанию.'nls_ang' задает язык, используемый в названиях месяцев и дней

TO_MULTI_BYTE ( char)

Преобразует однобайтовые символы, имеющие многобайтовые эквиваленты, в соответствующие многобайтовые символы

TO_NUMBER (char [,fmt [,'nls_lang']])

Преобразует char, содержащее число в формате, указанном параметром fmt, в значение типа NUMBER. 'nls_lang' задает язык, определяющий символы валют и числовые разделители

TO_SINGLE_BYTE ( char)

Преобразует многобайтовые символы, имеющие однобайтовые эквиваленты, в соответствующие однобайтовые символы

Форматные маски.

Этот раздел описывет форматные маски дат и чисел.

Форматные маски дат в TO_CHAR и TO_DATE.

Элементы форматной маски даты перечислены в приведенной ниже таблице. Любую комбинацию этих элементов можно использовать как аргумент fmt функций TO_CHAR или TO_DATE. По умолчанию fmt равен 'DD-MON-YY'.

Элемент формата

Возвращаемое значение

SCC или CC

Столетие; если указано 'S' то перед датами до нашей эры ставится '-'.

YYYY или SYYYY

Год; если указано'S' то перед датами до нашей эры ставится '-'.

YYY или YY или Y] Последние 3, 2, или1 цифра года.

IYYY

4 цифры года по стандарту ISO.

IYY или IY или I] Последние 3, 2, или1 цифра года по стандарту ISO.

Y,YYY

Год с запятой в указанной позиции.

SYEAR или YEAR

Год, записанный словами, а не цифрами; если указано'S' то перед датами до нашей эры ставится '-'.

RR

Последние 2 цифры года; для указания года в других столетиях.

BC или AD

BC- до нашей эры(до н.э.); AD - нашей эры

B.C. или A.D.

B.C.- до нашей эры(до н.э.); A.D. - нашей эры

Q

Квартал (1, 2, 3, 4;JAN-MAR=1).

MM

Месяц(01-12; JAN=1).

RM

Нумерация месяцев римскими цифрами(I-XII; JAN=I).

MONTH

Название месяца, дополненное пробелами до 9-ти символов.

MON

Сокращенное название месяца.

WW или W

Неделя года (1-52) или месяца (1-5).

IW

Неделя года (1-52 или 1-53) по стандарту ISO.

DDD или DD или D

День года (1-366) или месяца (1-31) или недели (1-7).

DAY

Название дня, дополненное пробелами до 9-ти символов.

DY

Сокращенное название дня.

J

Дата юлианского календаря; число дней, считая с первого января 4712 года до н.э.

AM или PM

AM -до полудня,PM- после полудня

A.M. или P.M.

A.M. -до полудня,P.M.- после полудня

HH или HH12

Час дня (1-12).

HH24

Час дня (0-23).

MI

Минута (0-59)

SS или SSSSS

Секунда (0-59) или количество секунд после полуночи (0-86399).

-/,.;:

Знаки пунктуации.

"...текст..."

Текст воспр в возвращенном значении.

 

Префикы и суффиксы элементов формата даты

К элементам формата даты можно добавлять следующие префиксы:

 

FM

"Режим заполнения".Подавляет заполнение пробелами, когда стоит перед MONTH или DAY

FX

"Точный формат". Этот модификатор задает точное соответствие символьного аргумента и форматной маски даты в функции TO_DATE.

К элементам формата даты можно добавлятть следующие суффиксы:

 

TH

Порядковый номер ("DDTH" для "4TH").

SP

Номер, записанный словами ("DDSP" для "FOUR").

SPTH и THSP

Порядковый номер, записанный словами ("DDSPTH" для "FOURTH").

Прописные и строчные буквы в элементах формата даты.

Следующие строки задают вывод прописными буквами, вывод прописными буквами только начальных букв слов, или вывод строчными буквами.

 

Прописные

Прописная начальная

Строчные

DAY

Day

.day

DY

Dy

.dy

MONTH

Month

.month

MON

Mon

.mon

YEAR

Year

.year

AM

Am

.am

PM

Pm

.pm

A.M.

A.m.

a.m.

P.M.

P.m.

p.m.

Если к элементу формата даты добавляется префикс или суффикс, то регистр (прописные, строчные буквы) определяется элементом формаиа , а не префиксом или суффиксом. Например, 'ddTH ' задает "04th" а не "04TH".

Элементы формата числа для TO_CHAR

В следующей таблице перечислены элементы формата числа. Комбинацию этих элементов можно использовать как аргумент fnu функции TO_CHAR.

 

Элемент

формата

Пример

Описание

9

'999'

Количество девяток указывает число возвращаемых значащих цифр.

0

'0999'

Добавляет нули перед числом.

$

'$9999'

Добавляет знак доллара перед числом.

В

'В9999'

Заменяет нулевые значения пробелами.

Ml

'99999MI'

Возвращает знак '-' после отрицательных значении.

S

S9999

Возвращает знак '+' для положительных значений и знак '-' для отрицательных значений в указанную позицию.

PR

'9999PR'

Возвращает отрицательные значения в <угловых скобках>.

D

99D99

Возвращает символ, представляющий десятичную точку, в указанную позицию.

С

9G999

Возвращает символ разделения цифр на группы в указанную позицию.

С

С999

Возвращает международной знак валюты в указанную позицию.

L

L999

Возвращает знак местной валюты в указанную позицию.

,

'9,999'

Возвращает запятую в указанную позицию.

.

'99.99'

Возвращает точку в указанную позицию.

V

'999V99'

Умножает значение на 10n, где n количество девяток после 'V'.

ЕЕЕЕ

'9.999ЕЕЕЕ'

Возвращает значение в нормализованной форме. В fnu должно быть ровно четыре буквы 'Е'.

RN или rn

RN

Возвращает римские цифры прописными или строчными буквами (целое число в диапазоне от 1 до 3999).

DATE

'DATE'

Возвращает значение, преобразованное из даты юлианского календаря в формат 'MM/DD/YY'.

Другие функции

 

Функция

Возвращаемое значение

DECODE (expr, search1, return1,

[search2, return2, ]...[default])

Если expr равно search, возвращается соответствующий результат return. Если совпадающей пары не найдено, возвращается default.

DUMP(expr[, return_format [, start_position[, length]]])

Expr во внутреннем формате Oracle

GREATEST(expr[, expr]...)

Наибольшее значение expr

LEAST(expr[, expr]...)

Наименьшее значение expr

NVL(expr1, expr2)

Возвращает expr2, если expr1 имеет пустое значение, в противном случае возвращает expr1.

UID

Целое число, которое уникально идентифицирует текущего пользователя.

USER

Имя текущего пользователя ORACLE.

USERENV(option)

Возвращает информацию о текущем сеансе. Аргументы помещаются в одиночных кавычках. Аргументы: ENTRYID, SESSIONSID, TERMINAL, LANGUAGE или LABEL.

VSIZE(expr)

Длина в байтах внутреннего проедставления для expr.

Подведем некоторые итоги: гибкость СУБД Oracle во многом определяется тем, что отдельные блоки кода PL/SQL программ можно хранить как объекты базы данных в формате хранимых процедур, функций и пакетов. Т.е. часть кода программы храниться там, где обрабатывается !!, т.е. на сервере.

Пакет - совокупность функций и процедур, объединенных по общему функциональному признаку, в тело пакетов входят процедуры и функции.

Процедура - объект базы данных обеспечивающий выполнение конкретных действий с передаваемыми параметрами процедуры.

Функция - объект базы данных обеспечивающий выполнение конкретных действий над параметрами функции и возвращающая результат такой обработки.

Для создания функций, процедур, пакетов базы данных используются следующие команды:

CREATE FUNCTION

Создает автономную хранимую функцию.

CREATE PACKAGE

Создает спецификацию для хранимого пакета.

CREATE PACKAGE BODY

Создает тело хранимого пакета.

CREATE PROCEDURE

Создает автономную хранимую процедуру

Приведем примеры реализации пакетов, функций и процедур.

/* ******Пакет обработки ошибок ********************* */  
create or replace package app_err as  
/* Получение текста аварийного завершения */  
    function get_err  return varchar2;  
/* Установка текста аварийного завершения */  
    procedure set_err ( error_text in varchar2);  
/* Установка текста аварийного завершения и само завершение */  
    procedure raise_err ( error_text in varchar2);  
 end app_err;  
create or replace package body app_err as  
 app_err_text varchar2(32767);  
 /* *********************************************************** */  
 function get_err return varchar2  
 is  
   A varchar (32767);  
 Begin  
   A := app_err_text;  
   app_err_text := null;  
   Return ( A );  
 End;  
 /* *********************************************************** */  
 procedure set_err(error_text in varchar2)  
    is  
 Begin  
    app_err_text := error_text;  
 End;  
 /* ************************************************************** */  
 procedure raise_err ( error_text in varchar2)  
 is  
 Begin  
    app_err_text := error_text;  
    raise_application_error (-20000, error_text);  
 End;  
 end app_err;  
/* ** Функция осуществляющая расшифровку пароля пользователя в БД ** */  
create or replace function password return varchar2 is  
  name   varchar2(23);  
  pass   varchar2(23);  
  begin  
  select ORANAME, CRYPT_PASSWORD  
  into name, pass  
  from USERS  
  where USER_ORANAME = user;  
  pass   := encrypt( pass, name );  
  return( pass );  
  end;  
/*Удаляется публичный синоним*/  
drop public synonym password;  
/*Создается публичный синоним*/  
create public synonym password for password;  
/* устанавливаем привелегии*/  
grant all on password to admin;  

Популярное

Не так давно в сети появился новый сервис, под названием Dead Man Zero. Этот сервис сделал...
Рынок социальных площадок уже давно стал стабильным. Несмотря на то, что время от времени...
Artisteer 4 – единственный в своем роде продукт, позволяющий автоматизировать работу над созданием...
Март 2017 (1)
Февраль 2017 (3)
Январь 2017 (1)
Август 2016 (1)
Май 2016 (2)
Ноябрь 2015 (1)

Карта сайта: 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41

Друзья сайта

Хотите продать свой сайт?
- Мы быстро и удобно для Вас сможем его купить:
  • Заявка на продажу сайта
  • Раcсматриваем цены на каждый сайт в индивидуальном порядке.

    Случайная цитата

    Edward V Berard:

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

    Опрос

    Какой текстовый редактор Вы используете?

    OpenOffice
    AbiWord
    Notepad++
    UltraEdit
    PSPad
    Microsoft Office
    Microsoft Блокнот
    Другой...