Присвоить значение переменной oracle

Обновлено: 03.07.2024

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

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

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

Типы переменных PL/SQL

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

Скалярные ( Scalar ) - скалярные типы данных содержат одно значение. К скалярным типам данных относятся все основные типы данных сервера Oracle, за исключением Boolean , которые не могут быть назначены для столбцов.

CLOB (character large object) - позволяет хранить большие блоки символьных данных;
BLOB ( binary large objects) - служит для хранения больших бинарных объектов;
BFILE ( binary file ) - служит для хранения больших бинарных объектов за пределами Базы Данных;
NCLOB (national-language character large objects) - служит для хранения больших single-byte или fixed-width NCHAR Unicode данных;
EXPR - любое значение, которое подходит по смыслу и типу данных.

Примечание: в PL/SQL также используются связываемые переменные (bind variable) и переменные операционной системы, но они не относятся к PL/SQL.

Объявление переменных

Все переменные PL/SQL необходимо объявлять в декларативном разделе - DECLARE . Рассмотрим синтаксис объявления переменной:

Identifier - имя переменной. После объявления имени переменой на нее можно ссылаться по имени из любой части программы.

CONSTANT - это переменная, значение которой нельзя изменить. Эта переменная должна быть инициализирована. Инициализация в данном контексте означает, что значение переменной при создании должно быть определенно, то есть значение этой переменной необходимо присвоить в разделе DECLARE . Инициализация переменной производится с помощью оператора присвоения (:=) или ключевого слова DEFAULT .

Datatype - определяет тип данных: скалярный, составной, ссылочный и LOB.

NOT NULL - ограничение, которое говорит о том, что значение переменой не может быть пустым. Эта переменная должна быть инициализирована.

Рассмотрим пример объявления некоторых типов переменных.

Создайте новую форму и сохраните под именем Var.fmb.
Перейдите в узел "Блоки Данных". Правым щелчком мыши вызовите всплывающее меню и выберите пункт "Редактор Разметки".
Разместите новую кнопку на канве. После того как вы разместите кнопку на канве, Oracle Forms автоматически создаст блок.
Выделите кнопку и вызовите всплывающее меню. Во всплывающем меню выберите пункт Универсальные триггеры | WHEN-BUTTON-PRESSED . В появившемся PL/SQL-редакторе наберите и скомпилируйте нижеприведенный код:

Height - переменная численного типа с пятью знаками до запятой и двумя после, хранит значение высоты;
Age - переменная численного типа без определения разряда, хранит значение возраста;
Weight - константа численного типа. Значение инициализации 74.5;
Name - переменная символьного типа , хранит значение имени. Максимальный размер - десять символов;
S_name CHAR (20) - переменная символьного типа , хранит значение фамилии. Максимальный размер - двадцать символов;
F_name - значение символьного типа . Не может быть пустым, инициализировано значением "Васильевич";
Man - переменная логического типа , инициализирована в TRUE ; значение TRUE или FALSE указывать без кавычек. Переменная типа Boolean может принимать только три типа значений:
- TRUE ;
- FALSE ;
- NULL .
В PL/SQL существует множество зарезервированных слов (табл. 2), которые не могут быть использованы для объявления переменных. Также не следует называть переменные именами столбцов. Ниже приведен пример неправильного объявления переменных:

Объявление переменных с атрибутом %TYPE

Префикс %TYPE используется для объявления переменных, ассоциированных с ранее объявленными переменными или столбцами Базы Данных. Основное преимущество префикса в том, что при объявлении переменная, которая впоследствии будет ассоциирована со столбцом Базы Данных, должна быть точно объявлена (имя, разряд) во избежание ошибок. Вы не можете помнить все значения типов данных столбцов таблицы, разрядность численных типов и размерность символьных полей, поэтому понимание использования этого префикса очень важно. Ниже приведен синтаксис и пример объявления префикса:

t_city - переменная типа city . Переменная символьного типа , хранит значение - "Мариуполь";

pr_key - хранит значение столбца таблицы about.pr_key .
Примечание: NOT NULL столбцы не могут быть объявлены с префиксом %TYPE .
Область видимости переменной

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

В этом разделе мы рассмотрели основные типы данных, применяемых в PL/SQL, а также правила и способы их объявления.

Обращение к элементам Oracle Forms в операторах PL/SQL

Для того чтобы обращаться к элементам, блокам, окнам и параметрам, нужно перед названием объекта поставить двоеточие. Ниже приведены основные примеры обращения к объектам Oracle Forms :
- :parameter.param_name - обращение к параметру. Слово parameter обязательно.
- :block_name.item_name - обращение к элементу блока. Block_name - это идентификатор блока данных, которому принадлежит элемент, к коему мы обращаемся. Если в форме элемент один, то название блока можно будет опустить, в противном случае вы получите ошибку - "Неверная переменная привязки".
- :block - обращение к блоку. В этом случае никаких вспомогательных префиксов не требуется.
- :column_name - обращение к элементу. Если элемент в форме один, к нему можно обращаться без указания имени блока.
- :system.variable_name - обращение к системной переменной . Слово SYSTEM является обязательным.
- :global.global_name - обращение к глобальной переменной. Слово global является обязательным.
Косвенное обращение к элементам

В Oracle Forms существует два вида непрямого обращения к элементам:

Процедура COPY

COPY - копирует указанное значение в указанный элемент. Ниже рассмотрено два вида обращения к элементу.

: about.age:=20 - это прямое обращение, элементу age присваивается значение 20.

COPY(20, 'about.age') - непрямое обращение, в элемент age помещается значение 20.

Функция NAME_IN

NAME_IN - возвращает содержимое элемента или переменной. Эта функция может быть вложена в другие функции. Ниже приведены примеры обращения к элементам с помощью функции NAME_IN :

Функция NAME_IN может использоваться совместно с процедурой COPY , например, копирование в элемент REF указанного значения:

Косвенное обращение часто используется в программных модулях и библиотеках, где прямое обращение к элементам нежелательно или недопустимо.

Комментарии

Каждая программа, претендующая на простоту, изящность и понимание, должна содержать пояснительный текст - комментарии. Для того чтобы программа была понятной и доступной, не всегда хватает исчерпывающих имен переменных или именованных блоков, поэтому в PL/SQL, как и в других языках программирования, можно использовать комментарии. В PL/SQL существует два вида комментариев:

Однострочные комментарии

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

однострочного комментария, который объясняет назначение процедуры sum_price :

Многострочные комментарии

В случае когда необходимо закомментировать большой фрагмент программы, расстановка однострочных комментариев займет приличное время, так как они удобны скорее для небольших фрагментов кода. Многострочный комментарий полезен для написания больших пояснений к тексту программы. В многострочном комментарии текст размещается между парой символов (/*) и (*/) - весь текст, размещенный между этой парой символов считается закомментированным. Ниже приведен пример многострочного комментария:

Используйте комментарии, это поможет не только вам ориентироваться в вашем же коде, но и тем разработчикам, которым, возможно, придется дорабатывать или изучать вашу программу.

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

Объявление переменной PL/SQL

Когда вы объявляете переменную, PL/SQL выделяет память для хранения ее значения и присваивает выделенной области памяти имя, по которому это значение можно извлекать и изменять. В объявлении также задается тип данных переменной; он используется для проверки присваиваемых ей значений. Базовый синтаксис объявления переменной или константы:

Здесь имя — имя переменной или константы, тип_данных — тип или подтип данных PL/SQL, определяющий, какие значения могут присваиваться переменной. При желании можно включить в объявление выражение NOT NULL ; если такой переменной не присвоено значение, то база данных инициирует исключение. Секция значение_по_умолчанию инициализирует переменную начальным значением; ее присутствие обязательно только при объявлении констант. Если переменная объявляется с условием NOT NULL , то при объявлении ей должно быть присвоено начальное значение.

Примеры объявления переменных разных типов:

Конструкция DEFAULT (см. l_right_now в приведенном примере) и конструкция с оператором присваивания ( l_favorite_flavor в приведенном примере) эквивалентны и взаимозаменяемы. Какой из двух вариантов использовать? Мы предпочитаем для констант использовать оператор присваивания (:=), а для инициализации переменных — ключевое слово DEFAULT . При объявлении константы задается не значение по умолчанию, а значение, которое не может быть изменено впоследствии, поэтому DEFAULT кажется неуместным.

Объявление константы PL/SQL

Между объявлениями переменных и констант существует два различия: объявление константы содержит ключевое слово CONSTANT , и в нем обязательно задается ее значение, которое не может быть изменено впоследствии:

Несколько примеров объявления констант:

Неименованная константа представляет собой литеральное значение — например, 2 или 'Bobby McGee'. Литерал не обладает именем, но имеет тип данных, который не объявляется, а определяется непосредственно на основании значения.

NOT NULL

Если переменной присваивается значение по умолчанию, вы также можете указать, что переменная всегда должна оставаться определенной (отличной от NULL ). Для этого в объявление включается выражение NOT NULL . Например, следующее объявление инициализирует переменную company_name и указывает, что переменная всегда должна оставаться отличной от NULL :

При попытке выполнения следующей операции в программе будет инициировано исключение VALUE_ERROR :

Кроме того, следующее объявление приводит к ошибке компиляции, так как в объявлении не указано исходное значение:

Объявления с привязкой

При объявлении переменной тип данных очень часто задается явно:

В Oracle также существует другой метод объявления переменных, называемый объявлением с привязкой (anchored declaration). Он особенно удобен в тех случаях, когда значение переменной присваивается из другого источника данных, например из строки таблицы.

Объявляя «привязанную» переменную, вы устанавливаете ее тип данных на основании типа уже определенной структуры данных. Таковой может являться другая переменная PL/SQL, заранее определенный тип или подтип ( TYPE или SUBTYPE ), таблица базы данных либо конкретный столбец таблицы.

В PL/SQL существует два вида привязки.
- Скалярная привязка. С помощью атрибута %TYPE переменная определяется на основании типа столбца таблицы или другой скалярной переменной PL/SQL.
- Привязка к записи. Используя атрибут %ROWTYPE , можно определить переменную на основе таблицы или заранее определенного явного курсора PL/SQL.
Синтаксис объявления переменной с привязкой:

Здесь имя_переменной — это имя объявляемой переменной, тип_атрибута — либо имя ранее объявленной переменной PL/SQL, либо спецификация столбца таблицы в формате таблица.столбец.

Привязка разрешается на стадии компиляции кода и не приводит к увеличению времени выполнения. Кроме того, привязка устанавливает зависимость между программным кодом и привязываемым элементом (таблицей, курсором или пакетом, содержащим переменную). Это означает, что при изменении данного элемента привязанный к нему программный код помечается как недействительный ( INVALID ). При повторной компиляции привязка выполняется заново, и таким образом код согласуется с измененным элементом.

На рис. 1 показано, как тип данных определяется на основе столбца таблицы базы данных и переменной PL/SQL.

Рис. 1. Атрибут TYPE при объявлении с привязкой

Пример привязки переменной к столбцу таблицы:

Аналогичным образом выполняется привязка к переменной PL/SQL; обычно это делается для того, чтобы избежать избыточных объявлений одного и того же жестко закодированного типа данных. В таких случаях в пакете обычно создается переменная, на которую затем ссылаются в программах с помощью атрибута %TYPE . (Также можно создавать в пакете подтипы SUBTYPE ; эта тема рассматривается далее в этой главе.) В следующем примере приведен фрагмент кода пакета, упрощающего использование Oracle Advanced Queuing (AQ):

Наличие объявлений с привязкой свидетельствует о том, что PL/SQL является не просто процедурным языком программирования, а разработан как расширение языка Oracle SQL. Корпорация Oracle приложила большие усилия к тому, чтобы интегрировать программные конструкции PL/SQL с базами данных, для работы с которыми используется SQL.

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

Привязка к курсорам и таблицам

Мы уже рассмотрели примеры объявления переменных с привязкой к столбцу базы данных и другой переменной PL/SQL. Теперь давайте посмотрим, как используется атрибут привязки % ROWTYPE .

Допустим, нам нужно выбрать одну строку из таблицы books. Вместо того чтобы с помощью атрибута %TYPE объявлять для каждого столбца таблицы отдельную переменную, можно воспользоваться атрибутом %ROWTYPE :

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

Наконец, следующий пример демонстрирует неявное использование атрибута %ROWTYPE в объявлении записи book_rec цикла FOR :

Преимущества объявлений с привязкой

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

Как переменной присвоить результат select-а
есть вот такой select. select add_months(trunc(sysdate,'MM'),-1) + level -1 x from dual connect.

Инициализация переменной через результат запроса
Здравствуйте. Можно ли в PL/SQL задать значение переменной через запрос? Например как-то так.

Как строке присвоить результат запроса
Есть запрос QueryClientsALLMan который возвращает одно значение поля Name из таблицы В VBA.

SQL. узнать результат выполнения запроса
Дана реляционная модель базы данных Таблица Customer содержит информацию о клиентах.
Во-первых, запрос должен возвращать только id
Во-вторых, запрос должен возвращать ровно одну строку.
В-третьих, нужно читать синтаксис языка, а не изобретать самопальные конструкции
В-четвертых, нужно определить переменную, желательно, типа поля id
Правильный синтаксис при соблюдении перечисленных выше условий такой:
А если запрос возвращает несколько строк, то нужно действовать как-то иначе, например, присваивать переменной значения в цикле. Или передавать значения в коллекцию. Или писать их в другую таблицу. Все зависит от поставленной задачи. Ниже приводится вывод значений в переменную в цикле через неявный курсор.
Насколько я понимаю, вместо :dealno нужно подставить значение. Поскольку это варчар, что будет что-то типа 'ABCDE' (строка в апострофах) AGK
после етого скрипта нужно разместить другой скрипт которий возьмет в работу переменную var_id
подскажите как ето сделать Обычно все пишут в одном блоке. Или делают процедуру, которая выполняет все действия. Очень неопределенный вопрос.
Если есть два блкока, из них всегда можно сделать один блок
Добавлено через 2 минуты
Или, например, из первого блока сделать функцию, которая возвращает значение переменной, а из второго блока - процедуру, которая принимает и обрабатывает это значение как ето сделать?
a.id и DEALID должни подставлятся(константа которая определяется в начале), чтоби не подвисала база
Селект, обычно, не может вызываться просто так в анонимном блоке.
Его надо либо вызывать интерактивно (в каком-то средстве разработки или просмотра БД), и тогда Вы видите результат на экране, либо его результат надо вставлять в какую-то другую таблицу. Если объем информации выводимых записей мал, его можно вывести в спул (на экран компьютера в Вашей сесии), но на это лучше не ориентироваться.
Ваш второй селект написан с ошибками.
1. Нельзя использовать служебные слова (например, SUM) в качестве алиасов. При попытке запустить селект будет ошибка.
2. Очень дурной тон использовать неявное преобразование типов. У Вас дата задается в виде строки, а ее надо задать в виде даты, иначе правильность селекта будет зависеть от NLS-настроек компьютера. Например, у меня Ваш селект просто не пройдет.

В принципе, можно первый селект засунуть в конструкцию WITH, а во втором селекте взять значение поля id из этой конструкции. Тогда можно будет просто запустить один селект, в котором будут выполнены обе части Вашей задачи.

Кажется, жизнь налаживается. Продолжаем разбираться с переменными. Итак, еще раз внимательнее посмотрим, как их правильно записывают и, как и где инициализируют. Итак, давайте рассмотрим такую последовательность:

Теперь напишем неименованный блок и посмотрим все ли правильно мы написали:

Получаем такой вывод SQL*Plus:

Действительно, первые три переменных просто вывелись на экран. Это числовые переменные разных типов и точности. А вот четвертую переменную стоит рассмотреть по ближе. Это CONSTANT переменная, то есть константа. Кроме того, она имеет тип REAL, являющимся разновидностью NUMBER. При таком типе объявления переменная должна сразу же получать значение, то есть инициализироваться! Все константы должны инициализироваться при объявлении! Запомните.

Пятая и шестая переменные - это просто строковые литералы разных типов, это я думаю понятно. Седьмая переменная - это строковый литерал, но с хитростью! Он не может иметь значение NULL! Так обычно определяют первичный ключ в таблице. Здесь тоже самое просто применяется, при объявлении переменной!

А, вот восьмая переменная - это, как говорят, отдельная песня! Это BOOLEAN переменная, да еще с грифом NOT NULL! Вообще в PL/SQL тип BOOLEAN привнесен, как мне кажется, немного искусственно для того, чтобы он был полноценным языком программирования. Почему? Как вам, наверное, известно, а если нет, то говорю, в Oracle, как и во многих SQL серверах в таблицах нельзя объявить тип поля (столбца) BOOLEAN! В этом смысле я обычно применяю поле VARCHAR2(1) и просто записываю туда либо T, либо F! А не иметь в самом PL/SQL типа BOOLEAN, было бы не очень красиво! По этому он есть. Что самое интересное, это я вывел экспериментально, TRUE соответствует 1, а FALSE соответствует 0! Прямо как в языке C++! Собственно чего и следовало ожидать!

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

Кстати, все правила работы с BOOLEAN переменными и константами, те же что я приводил в "Шаг 17 - Составные операторы в условии WHERE"! Есть еще функция XOR она выглядит, для троичной логики так:

Вот таким образом строится работа для функции XOR, для AND, OR, NOT смотрите шаг 17. Пока мы с вами делали, линейные алгоритмы блоков. В следующем шаге будем рассматривать способы ветвления и правила работы со строками. Пока закрепляйте пройденное. :)

Читайте также: