PL/SQL

  • Архитектура языка PL/SQL и для чего ее знать? Знакомьтесь, DIANA

    Разработчики PL/SQL редко проявляют интерес к архитектуре языка PL/SQL. Типичный представитель нашего сообщества склонен изучить базовый синтаксис PL/SQL, написать свою программу и отправиться домой, чтобы провести побольше времени с семьей или друзьями. И это очень правильный подход!

    Тем не менее, на наш взгляд, каждому разработчику желательно хотя бы в общих чертах разбираться в архитектуре PL/SQL. Это позволит не только повысить эффективность использования памяти в программах, но и добиться от программ и приложений того, что на первый взгляд сделать невозможно.

  • Безопасность базы данных Oracle и программирование PL/SQL

    Безопасность БД Oracle и кодинг на PL/SQLМногие разработчики PL/SQL считают, что о безопасности должны заботиться только администраторы баз данных и специалисты по безопасности. Действительно, некоторы­ми аспектами безопасности (например, управлением пользователями и привилегиями) должны заниматься администраторы. Но было бы серьезной ошибкой полагать, что разработчиков PL/SQL эта тема вообще не касается. Во-первых, безопасность не явля­ется проблемой, которая решается один раз; это средство достижения цели, о котором приходится помнить постоянно. Во-вторых, многие администраторы направляют свои усилия на защиты базы данных в целом, а не на программирование средств безопасности конкретного приложения.

  • Большие объекты данных в PL/SQL: BLOB, CLOB, NCLOB и BFILE

    PL/SQL: BLOB, CLOB, NCLOB и BFILEOracle и PL/SQL поддерживают несколько разновидностей типов данных, предназначенных специально для работы с большими объектами (LOB, Large OBjects). Такие объекты позволяют хранить огромные (от 8 до 128 терабайт) объемы двоичных (например, графических) или текстовых данных.

    До выхода Oracle9i Release2 в объектах LOB можно было хранить до 4 Гбайт данных. Начиная с Oracle10g, ограничение было повышено до величины от 8 до 128 терабайт (конкретное значение зависит от размера блока вашей базы данных).

  • Введение в язык программирования PL/SQL

    Введение в язык программирования PL/SQL OraclePL/SQL — это сокращение от «Procedural Language extensions to the Structured Query Language», что в переводе с английского означает «процедурные языковые расширения для SQL». SQL — повсеместно распространенный язык для выборки и обновления информации (вопреки названию) в реляционных базах данных. Компания Oracle Corporation разработала PL/SQL для преодоления некоторых ограничений SQL, а также для того, чтобы предоставить более полное совершенное решение для разработчиков ответственных приложений баз данных Oracle. В этой статье вы познакомитесь с PL/SQL, его происхождением и разными версиями. В блоге приведена краткая сводка PL/SQL в последней версии Oracle, Oracle Database 12с. Ну а завершим ознакомление с языком списком дополнительных ресурсов для разработчиков PL/SQL и некоторыми рекомендациями.

  • Встроенные методы коллекций PL/SQL

    PL/SQL предоставляет для создаваемых вами коллекций множество встроенных функций и процедур, называемых методами коллекций. Эти методы предназначены для получения информации о содержимом коллекции и ее изменения. Их полный список приведен в табл. 1.

  • Вывод данных в PL/SQL: DBMS_OUTPUT, чтение и запись в буфер

    DBMS_OUTPUT, чтение и запись в буфер в PL/SQLБольшинство программ PL/SQL работает только с базой данных Oracle через SQL. Однако время от времени возникает необходимость в передаче информации из PL/ SQL во внешнюю среду или чтении информации из внешнего источника (экран, файл и т. д.) в PL/SQL.

    Пакет DBMS_OUTPUT предоставляет средства вывода информации из программ в буфер. Далее содержимое буфера читается и обрабатывается другой программой PL/SQL или управляющей средой. Пакет DBMS_OUTPUT чаще всего используется для простого вывода информации на экран.

  • Вызов Java-программ из PL/SQL: процедуры, loadjava, JSP, DBMS_JAVA и др.

    Язык Java изначально был спроектирован и продвигался компанией Sun Microsystems. В наши дни его продвигают практически все, кроме Microsoft. Java предоставляет исклю­чительно разнообразный набор программных средств, многие из которых недоступны в PL/SQL напрямую. В этой статье представлены основы создания и использования хранимых процедур Java в Oracle. Также вы узнаете, как создавать и пользоваться функциональностью JSP из PL/SQL.

  • Вызов пользовательских функций в SQL и PL/SQL

    Пользовательские функции в SQL и PL/SQLOracle позволяет вызывать пользовательские функции в коде SQL. Фактически это позволяет адаптировать язык SQL под требования конкретных приложений.

    Каждый раз, когда исполнительное ядро SQL вызывает функцию PL/SQL, оно должно «переключаться» на исполнительное ядро PL/SQL. При многократном вызове функции затраты на переключение контекстов могут быть весьма значи­тельными.

  • Глобализация и локализация приложений PL/SQL: основные понятия

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

  • Глобализация и локализация программ PL/SQL: Дата и время

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

  • Детализированный аудит FGA для кодера PL/SQL: безопасность и производительность

    Детализированный аудит FGA в PL/SQL для безопасностиДетализированный аудит (FGA, Fine-Grained Auditing) предоставляет механизм для регистрации факта выдачи пользователями определенных команд и выполнения некоторых условий. При этом регистрируется команда, введенная пользователем, а также другая информация: время, терминал и т. д.

    Под традиционным аудитом в Oracle понимается механизм регистрации того, какая схема выполнила то или иное действие: пользователь Joe выполняет процедуру X, пользователь John выполнил выборку данных из таблицы Y и т. д. Протокол всех этих действий, называемый журналом аудита, хранится в таблице AUD$ в схеме SYS, доступной для всех пользователей через несколько представлений словаря данных — например, DBA_AUDIT_TRAIL. Журналы аудита также могут записываться в файлы операционной системы вместо таблиц базы данных. Независимо от того, где хранится эта информация, главный недостаток традиционного аудита остается неизменным: он следит за тем, кто выполнил ту или иную команду, а не за тем, что при этом было сделано. Например, из журнала аудита можно узнать, что пользователь Joe получил данные из таблицы ACCOUNTS, но какие именно записи — остается неизвестным. Если вы хотите знать измененные значения, приходится устанавливать для таблиц триггеры DML и сохранять значения в таблице, определенной вами. Но поскольку определить триггер для команды SELECT не удастся, для ситуации с Joe этот вариант не подойдет.

  • Детализированный аудит Oracle и программирование PL/SQL

    Программирование PL/SQL: аудит базы данных OracleДетализированный аудит (FGA, Fine-Grained Auditing) предоставляет механизм для регистрации факта выдачи пользователями определенных команд и выполнения не­которых условий. При этом регистрируется команда, введенная пользователем, а также другая информация: время, терминал и т. д.

    Под традиционным аудитом в Oracle понимается механизм регистрации того, какая схема выполнила то или иное действие: пользователь Joe выполняет процедуру X, поль­зователь John выполнил выборку данных из таблицы Y и т. д. Протокол всех этих дей­ствий, называемый журналом аудита, хранится в таблице AUD$ в схеме SYS, доступной для всех пользователей через несколько представлений словаря данных — например, DBA_AUDIT_TRAIL. Журналы аудита также могут записываться в файлы операционной системы вместо таблиц базы данных.

  • Динамический SQL и NDS команды PL/SQL на примерах

    команды PL/SQL, EXECUTE IMMEDIATE, OPEN FOR в PL/SQLТермином «динамический SQL» обозначаются команды SQL, которые конструируются и вызываются непосредственно во время выполнения программы. Статическими называются жестко закодированные команды SQL, которые не изменяются с момента компиляции программы. «Динамическим PL/SQL» называют целые блоки кода PL/SQL, которые строятся динамически, а затем компилируются и выполняются.

  • Знакомство с коллекциями PL/SQL

    Коллекции PL/SQLКоллекцией называется структура данных, по своей функциональности сходная со списком или одномерным массивом. В сущности, коллекция — ближайший аналог традиционных массивов в программах PL/SQL. Эта статья поможет вам решить, какой из трех разных типов коллекций (ассоциативный массив, вложенная таблица, VARRAY) лучше всего соответствует потребностям вашей программы. Также вы познакомитесь с примерами определения этих структур и работы с ними.

  • Знакомство с оперативной заменой PL/SQL (SET edition)

    ALTER SESSION SET edition ORACLE в PL/SQLОдним из самых значительных усовершенствований Oracle11g Release 2 является оперативная замена — новый элемент технологий высокой доступности Oracle. Эта функция позволяет заменять компоненты баз данных во время использования приложения; иначе говоря, Oracle позволяет изменять приложения PL/SQL на ходу. Оперативная замена сводит к минимуму (а то и полностью устраняет) простои в работе приложения.

  • Использование DBMS_SQL вместо динамического PL/SQL

    DBMS SQL или SQL NDSЕсли перед вами станет вопрос, какую из описанных технологий следует выбрать, начать следует со встроенного динамического SQL (вместо DBMS_SQL), поскольку он гораздо проще в применении, а программный код получается более коротким и обычно содержит меньшее количество ошибок. Кроме того, такой код проще в сопровождении, а обычно и выполняется более эффективно.

    И все же в некоторых ситуациях приходится использовать пакет DBMS_SQL. Эти ситуации описаны ниже.

  • Использование записей (records) в PL/SQL

    Records (записи) в языке программирования PL/SQLЗапись (record) представляет собой составную структуру данных; другими словами, запись состоит из нескольких полей, каждое из которых обладает собственным значением. Записи в программах PL/SQL очень похожи на строки в таблицах баз данных. Запись как целое не имеет собственного значения; однако значение имеет каждый ее компонент, или поле, а объединение их в единую запись позволяет хранить и обрабатывать все значения как одно целое. Записи сильно упрощают работу программиста, а переход от объявлений уровня полей к уровню записей повышает эффективность написания кода.

  • Использование параметров в PL/SQL на примерах

    использование параметров на языке PL/SQLДля передачи информации между модулем и вызывающим блоком PL/SQL используются параметры.

    Параметры модуля, являющиеся частью его заголовка (или сигнатуры), являются не менее важными компонентами модуля, чем находящиеся в нем исполняемые команды. Заголовок программы иногда называется контрактом между автором и пользователями. Конечно, автор должен позаботиться о том, чтобы модуль выполнял свою задачу. Но ведь модули создаются именно для того, чтобы их можно было вызывать повторно — в идеале более чем из одного модуля. Если список параметров плохо составлен, то другим программистам будет сложно применять такой модуль. В таком случае уже будет неважно, насколько хорошо он написан.

  • Использование Юникода в программах PL/SQL на примерах

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

  • Как Oracle выполняет код PL/SQL: на заметку программисту

    Прежде чем переходить к описанию механизма выполнения программ PL/SQL, необходимо определить пару важных понятий:

    •  Исполняющее ядро PL/SQL (виртуальная машина PL/SQL). Так называется компонент Oracle, выполняющий байт-код программ PL/SQL, который при необходимости вызывает сервер базы данных и возвращает результаты вызывающей среде. Исполняющее ядро написано на языке C. Традиционно оно включалось в некоторые клиентские инструменты, например Oracle Forms, где оно открывает сеанс подключения к удаленной базе данных и взаимодействует с ядром SQL через сетевой протокол.
    • Сеанс (Oracle). Для большей части (серверного) кода PL/SQL сеансом называется процесс и пространство памяти, связанные с аутентифицированным пользователем. Каждый сеанс имеет собственную область памяти, в которой хранятся данные выполняемой программы. Сеансы начинаются с момента входа пользователя в систему и завершаются при выходе из нее. Для получения информации о текущих сеансах используется представление V$SESSI0N. Oracle поддерживает для сеанса различные виды памяти, включая PGA (Process Global Area), UGA (User Global Area) и CGA (Call Global Area); они будут рассматриваться далее в этом разделе.

    Рассмотрим несколько способов выполнения простейшей программы из очень популярной клиентской среды SQL *Plus. Это прекрасный пример приложения, ориентированного на выполнение в рамках сеанса и предоставляющего доступ к окружению PL/SQL, которое входит в состав сервера базы данных. (Об SQL*Plus и его использовании для выполнения кода PL/SQL рассказывается в этом блоге.) Конечно, при желании к серверу можно обращаться с вызовами из других приложений (например, из клиентских программ Oracle и даже из таких языков, как Perl, C или Java). Происходящее на сервере практически не зависит от клиентского окружения.

    При выполнении кода PL/SQL из SQI?Plus задействован анонимный блок верхнего уровня. Вы, вероятно, знаете, что команда EXECUTE в SQL *Plus преобразует вызов программы в анонимный блок, но известно ли вам, что команда SQL CALL тоже генерирует упрощенную разновидность анонимного блока? По сути, прежде чем в Oracle9i появилась возможность непосредственного вызова PL/SQL из SQL, все такого рода вызовы производились с использованием анонимных блоков.

     

    Пример

    Начнем с простейшего анонимного блока:

    BEGIN
       NULL;
    END;

    Что происходит при его отправке серверу Oracle? Чтобы ответить на этот вопрос, об­ратимся к Рис. 1.

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

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

    Выполнение тривиального анонимного блока

    Рис. 1. Выполнение тривиального анонимного блока

     

    1. Компилятор PL/SQL пытается откомпилировать полученный анонимный блок в байт-код. На первом этапе компилятор проверяет синтаксис и убеждается в том, что программа соответствует грамматике языка. В нашем тривиальном примере код не содержит ни одного идентификатора, а только ключевые слова языка. Если компиляция проходит успешно, Oracle помещает байт-код блока в общую область памяти, а если нет — возвращает сообщения об ошибках сеансу SQLfPlus.
    2. Исполняющее ядро PL/SQL интерпретирует байт-код и возвращает сеансу SQLfPlus признак успешного или неудачного завершения сеанса.

    Добавим в анонимный блок SQL-запрос и посмотрим, как изменится процесс его выполнения. На Рис. 2 показаны некоторые компоненты сервера Oracle, участвующие в выполнении команд SQL.

    В этом примере значение столбца извлекается из встроенной таблицы DUAL.

    Убедившись в том, что код PL/SQL не содержит синтаксических ошибок, компилятор PL/SQL передает код SQL компилятору SQL. Аналогичным образом при вызове программ PL/SQL из команд SQL компилятор SQL передает вызовы PL/SQL компилятору PL/SQL. Компилятор SQL приводит выражения к непосредственно исполняемому виду, анализирует возможности применения кэшированных результатов функций (начиная с Oraclellg), проверяет семантику и синтаксис, проводит разрешение имен и определяет оптимальный план выполнения. Все эти действия являются частью фазы разбора SQL-выражения и предшествуют подстановкам связанных переменных, выполнению и выборке команд SQL.

    Хотя PL/SQL использует компилятор SQL совместно с базой данных, это не означает, что в PL/SQL доступны все функции SQL. Например, SQL поддерживает функцию NVL2:

    SELECT NVL2(NULL, 1, 2) FROM DUAL;

    Однако попытка вызова NVL2 непосредственно из PL/SQL приводит к ошибке (PLS- 00201):

    SQL> EXEC DBMS_OUTPUT.PUT_LINE(NVL2(NULL, 1, 2));
       2 BEGIN DBMS_OUTPUT.PUT_LINE (NVL2(NULL, 1, 2)); END;
    
    *
    ERROR at line 1:
    ORA-06550: line 1, column 28:
    PLS-00201: identifier 'NVL2' must be declared
    ORA-06550: line 1, column 7:
    PL/SQL: Statement ignored

     

    Выполнение анонимного блока, содержащего команду SQL

    Рис. 2. Выполнение анонимного блока, содержащего команду SQL

    При компиляции программы PL/SQL встроенные команды SQL подвергаются незначительным преобразованиям: из них удаляются секции INTO, на место локальных переменных программы подставляются связанные значения, удаляются комментарии, а многие идентификаторы (ключевые слова, имена столбцов, имена таблиц и т. д., но не рекомендации и литералы) преобразуются в верхний регистр. Например, если myvar является локальной переменной, PL/SQL преобразует команду select dummy into str from dual where dummy = myvar к следующему виду:

    select dummy into str from dual where dummy = myvar

    к следующему виду:

    SELECT DUMMY FROM DUAL WHERE DUMMY = :B1

    Из кода PL/SQL могут совершаться внешние вызовы двух типов:

    • Хранимые процедуры Java. Стандартная установка сервера баз данных включает не только виртуальную машину PL/SQL, но и виртуальную машину Java. Вы можете написать спецификацию вызова PL/SQL, логика которого реализуется в виде статического класса Java.
    •  Внешние процедуры. Исполняемую часть подпрограммы PL/SQL также можно реализовать в коде C. Во время работы база данных будет выполнять код в особом процессе и пространстве памяти, отделенных от главного сервера базы данных. Вы отвечаете за поддержку двоичных файлов и наличие копии у каждого узла RAS.

     

    Ограничения компилятора

    При компиляции очень большой программы PL/SQL от сервера можно получить сообщение об ошибке PLS-00123. Это означает, что компилятор вышел за пределы максимально допустимого количества узлов в дереве разбора. В таком случае программу проще всего разбить на несколько меньших подпрограмм или изменить ее архитектуру (например, использовать временную таблицу вместо 10 000 параметров). Трудно предсказать, сколько узлов понадобится для реализации программы, потому что не каждый узел соответствует какой-либо легко идентифицируемой единице исходного кода (лексеме, строке кода и т. д.).

    По данным Oracle, «типичная» хранимая программа создает в среднем четыре узла на каждую строку кода, что дает следующие приблизительные ограничения:

    Тип программы PL/SQL Приблизительный максимальный размер исходного кода в байтах
    Тело пакета или типа; автономные функции и процедуры 256 Мбайт
    Сигнатура (заголовок) отдельной функции или процедуры 128 Kбайт
    Спецификация пакета или типа данных, анонимный блок 128 Kбайт

    Приведенные значения весьма приблизительны; в реальных программах возможны значительные отклонения в обоих направлениях.

    Некоторые из других документированных ограничений компилятора PL/SQL перечислены в следующей таблице.

    Тип программы PL/SQL Максимальное значение
    (приблизительное)
    Уровни вложения блоков 255
    Параметры, передаваемые процедуре или функции 65 536
    Уровни вложения записей 64
    Ссылки на объекты в программах 65 536
    Количество обработчиков исключений в одной программе 65 536
    Точность NUMBER (в знаках) 38
    Размер VARCHAR2 (в байтах) 32 767

     

Page 3 of 9