Процедуры в access

Процедуры и функции, их вызов и передача параметров

Основными компонентами программы на VBA являются процедуры и функции, Они представляют собой фрагменты программного кода, заключенные между операторами Sub и End Sub или между операторами Function и End Function. Вот как может выглядеть процедура VBA:

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

Для того чтобы использовать написанную вами процедуру или функцию, необходимо вызвать ее. Как же осуществляется вызов процедур и функций? Процедуру с непустым списком аргументов можно вызвать только из другой процедуры или функции, использовав ее имя со списком фактических значений аргументов в качестве одного из операторов VBA. Функцию же можно вызвать не только с помощью отдельного оператора VBA, но и поместив ее имя со списком фактических значений аргументов прямо в формулу или выражение в программе на VBA или, например, прямо в формулу в вычисляемых полях запросов, форм и отчетов Access. Процедура с пустым списком аргументов (так называемый командный макрос) может быть вызвана не только из другой процедуры или функции, но и с помощью комбинации клавиш быстрого вызова, команд раскрывающихся меню или кнопок панелей инструментов. Можно также связать такую процедуру с выполнением самых различных событий: например, с открытием формы или отчета, со щелчком мышью по кнопке в форме, с воздействием на элементы управления форм, в частности элементы управления ActiveX. Такие процедуры называют процедурами обработки событий. Понятно, что функции или процедуры, нуждающиеся в передаче им аргументов, таким способом вызвать нельзя. Подробнее о том, как создаются процедуры обработки событий, будет рассказано в разд. «Программирование в формах и отчетах» данной главы.

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

Вот примеры вызова процедуры под именем CrossRC с передачей ей двух аргументов (константы и выражения):

А вот пример вызова двух функций — Left и Mid, и использования возвращаемого ими значения в выражении:

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

Способ передачи параметров процедуре или функции указывается при описании ее аргументов: имени аргумента может предшествовать явный описатель способа передачи. Описатель ByRef задает передачу по ссылке, a ByVal — по значению. Если же явное указание способа передачи параметра отсутствует, то по умолчанию подразумевается передача по ссылке.

Поясним сказанное на примере. Пусть имеются следующие описания двух процедур:

Вспомогательная процедура Examplel использует в качестве формальных аргументов три переменные, описанные по-разному. Далее в теле этой процедуры каждый из них увеличивается на единицу, а затем их значения выводятся на экран с помощью функции MsgBox. Основная процедура Main устанавливает значения переменных a, b и с, а затем передает их в качестве (фактических) аргументов процедуре Examplel. При этом первый аргумент передается по ссылке (действует умолчание), второй — по значению, а третий — снова по ссылке. После возврата из процедуры Examplel основная процедура также выводит на экран значения трех переменных, передававшихся в качестве аргументов. Всего на экран выводится шесть значений:

• сначала это числа 11, 21 и 31 (все полученные значения увеличены на 1 и выводятся процедурой Examplel);
• затем это числа 11, 20 и 31 (эти значения выводятся процедурой Main, причем переменные, переданные по ссылке, увеличились, а переменная, переданная по значению — нет).

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

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

Если при этом имя модуля состоит из нескольких слов, следует заключить это имя в квадратные скобки. Например, если модуль называется «Графические процедуры», а процедура — «Крестик», вызов может выглядеть следующим образом:

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

Вызов хранимых процедур. Работа с транзакциями

Хранимые процедуры в Microsoft Access

В завершение этой лекции рассмотрим хранимые процедуры в Microsoft Access. Хранимые процедуры? А разве Microsoft Access их поддерживает? По правде говоря, нет. Мы не можем создавать в MSAccess такие процедуры, как мы это делали в MS SQL Server, синтаксис не будет содержать ключевого слова «PROC» или «PROCEDURE», и вообще, это совсем не так называется! Но база данных способна хранить SQL-запросы, и если их запускать из внешнего приложения, то функциональность уже будет напоминать саму концепцию хранимых процедур.

Открываем базу BDTur_firm2.mdb. В окне базы данных переключаемся на вкладку «Запросы» и дважды щелкаем на заголовке «Создание запроса в режиме конструктора» (рис. 7.14):

В появившемся окне добавления таблицы выбираем «Туристы» и нажимаем кнопку «OK» (рис. 7.15).

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

Добавим сортировку по столбцу «Фамилия». В поле «Сортировка» из выпадающего списка выбираем значение «по возрастанию» (рис. 7.17).

Можно просмотреть SQL-конструкцию готового запроса. В главном меню выбираем «Вид Режим SQL». Окно конструктора изменяет свой вид — в нем появляется текст запроса:

Сохраняем запрос, называя его «Сортировка_туристы». Дважды щелкнув на нем в окне базы данных, запускаем — записи таблицы отсортированы (рис. 7.18).

Займемся теперь созданием приложения, которое будет запускать этот запрос как хранимую процедуру. Создайте новый Windows-проект и назовите его «Stored_Procedure_MSAccess». Перетаскиваем на форму элемент управления ListBox , его свойству Dock устанавливаем значение «Fill» . Подключаем пространство имен для работы с базой данных:

В классе формы определяем строку connectionString :

В конструкторе формы создаем объекты ADO .NET, причем в свойстве CommandType объекта Command задаем тип запроса StoredProcedure :

Весь код уже достаточно хорошо знаком — мы его применяли для запуска хранимых процедур MS SQL Server. Запускаем приложение — на форму выводится результат запроса (рис. 7.19).

В программном обеспечении к курсу вы найдете приложение Stored_ Procedure_MSAccess (CodeGlava3Stored_Procedure_MSAccess).

Работа с хранимыми процедурами MS Access в VB.NET. Часть 1

В самых недавних релизах Microsoft Access большие усилия были затрачены для придания этому продукту полноты набора функций системы реляционной базы данных. Хранимые процедуры — функциональность, обычно связываемая с промышленными системами баз данных, такими как SQL Server, теперь могут быть найдены в Access. Хранимые процедуры стали доступны, начиная с Access 2000, и они являются естественными для Jet 4 Database Engine. Если вы знакомы с использованием хранимых процедур в SQL Server, то вы сможете применять их, оставаясь дома так же, как вы пользуетесь Access. Однако, нужно иметь в виду некоторые ограничения. Я буду обсуждать их позже.

Эта статья будет разделена на две части. Первая часть в деталях опишет, как создавать хранимые процедуры в Access с помощью ADO.NET и Visual Basic.NET. Вторая часть покажет, как использовать хранимые процедуры, созданные в первой части, настраивая уровень доступа к данным, который может быть смоделирован и использован в ваших приложениях. Код в этой статье был проверен в Access 2002, но он, так же, будет работать и в Access 2000.

Как хранимые процедуры работают в Access?

В отличие от других объектов в Access хранимые процедуры не имеют интерфейса и не могут быть созданы или использованы через Access User Interface (пользовательский интерфейс Access). Получить их в вашей базе данных можно только одним способом — просто кодировать их. Я покажу, как это делается в ADO.NET.
Когда хранимая процедура добавляется в базу данных Access, Jet Engine преобразовывает синтаксис хранимой процедуры в объект запроса. Для Access-разработчика это может прозвучать как ненужная работа в кодировании запроса. Однако у этого есть свои преимущества. Рассмотрим приложение, которое разделяется на две разные версии, одна из которых поддерживает базу данных Access, а другая — базу данных SQL Server. Применение хранимых процедур облегчит написание кода на уровне приложения базы данных, так как отличия между различными версиями программы очень малы.

Создание хранимых процедур

Для демонстрации сначала я покажу, как создать SQL-операторы для создания хранимых процедур. В конце статьи я покажу весь код, необходимый для выполнения этих операторов в базе данных. Используется база данных Northwind, которая поставляется вместе с Access, будут созданы четыре хранимых процедуры. Все они будут применены к таблице Products (Продукты), начнем с самой простой; выберем все данные из каждой строки таблицы. Чтобы создать хранимую процедуру, выполните следующий оператор в базе данных:

Оператор: «CREATE PROC procCustomerList» является частью, которая в действительности создает хранимую процедуру. Частью, которая следует за «AS» может быть любой действительный SQL-оператор.
Часто в хранимой процедуре вы хотите передать значение, которое будет использовано в запросе. Учитывайте, что вы можете захотеть удалить запись, основываясь на уникальном ProductID. Следующая хранимая процедура показывает, как просто это делается:

В первой строке обратите внимание на скобку сразу после объявления CREATE PROC. Это параметр, объявленный как значение Long. Вот куда вы добавляете переменную для удаления записи.
Следующие два оператора показывают, как создать хранимые процедуры для добавления и обновления данных в таблице Products. Обратите внимание, что для краткости включены не все поля:

Заметьте, что параметры разделены запятыми, если их определено более одного.

Ограничения

Здесь вы можете наткнуться на некоторые ограничения, особенно если использовали мощный SQL Server.

• Нельзя использовать исходящие параметры.
• Не используйте символ «@». Символ «@» часто применяется в Transact SQL (SQL Server), где он представляет локальные переменные. Access не всегда обрабатывает этот символ и иногда будет выбрасывать его. Это может послужить причиной скрытых ошибок, которые приведут к раннему облысению.
• В Access недоступны временные таблицы.
• Я подозреваю, что многие опции, доступные в Transact SQL не доступны в Access, так как он не совместим с Transact SQL.

Реализовать хранимые процедуры / функции в Access 2007

Всем привет! Народ, помогите пожалуйста. В общем у меня есть база данных в SQL-сервере.

Я создал такую же в аксесе. Там 1 таблица всего. В SQL-сервере у меня есть пользовательская функция, которую я написал. 2-й запрос вызывает эту функцию и всё отлично считается и выдаётся результат.

Подскажите пожалуйста как в аксессе сделать хоть какой-нибудь аналог хранимой процедуры / функции.
Я пробовал на VBA макрос сделать, но что-то не получилось (((.

Может тут есть кто может мне по шагам разжевать как правильно написать в Access 2007 пользовательскую функцию (аналог Create Function T-SQL). И как её правильно вызвать, чтобы она отработала? С синтаксисом VBA я уже разобрался. Т.е. переписал уже код T-SQL на VBA. Проблема в том, что наверно не знаю как правильно макрос сохранить и вызвать.

Был бы очень признателен

Связать хранимые процедуры в параметрами из MS SQL Server в MS Access
Здравствуйте! Есть хранимая процедура, написанная в SQL Server CREATE PROC ZAKAZCHIK @city.

Хранимые процедуры и функции
Ребята, дайте пожалуйста 1 пример, как описывать хранимую процедуру, и 1 пример, как описывать.

Хранимые функции/процедуры
Добрый день. Нахожусь в процессе освоения MySQL, в виду автоматизации процесса тестирования.

Хранимые процедуры и функции MySQL
Помогите!! Придумать не менее трёх хранимых процедур и функций по этой физической модели бд. Сама.

Понял, спасибо. Запросы с параметром тут тоже будут. Проблема ещё в том что например в SQL-сервере у меня во втором запросе используется динамический скроллируемый курсор.

Результат первого запроса — таблица со значениями. Во втором запросе эти значения нужно просуммировать. Для этого я и вводил курсор и вызывал функцию из 1-го запроса. Непонятно как для начала процедуру оформить, а уже потом курсор

Хорошо.
Есть вот такая задача. Автомобиль движется по городу, и у него GPS-маячок есть, который передаёт в базу его координаты (долготу и широту), и другие данные.

Выкладываю архив с резервной копией БД. CalculateTheDistance2 — функция, которая вычисляет по формуле расстояние меджу 2-мя точками на карте по их долготе и широте. Соответственно у функции 4 входных параметра, а возвращает она 1 параметр — расстояние.

Общий пробег машины — запрос, который проходит по всей таблице GpsHistory и суммирует все расстояния между всеми точками. Поэтому и курсор я там использовал, чтобы на тех данных, для которых прописан курсор циклически выполнялась сумма.
В исходнике там это видно. Как все эти дела организовать без использования функции — я себе не представляю.
В Access я бы не полез, если бы в будущем не предполагался запрос с параметрами (параметры — DeviceID и время).

Access позволяет делать запрос с параметрами, чтобы например пользователь вводил значения DeviceID, дату в маленьком всплывающем окне. Получалась бы таблица, и уже функция проводила суммирование именно по этой таблице, а не по всей таблице GpsHistory. Вот в чём загвоздка.

Сейчас пока главное что мне нужно — правильно составить макрос — ну т.е. сделать аналог функции SQL-сервера. И правильно вызвать эту функцию. Или подскажите как тут обойтись без использования функции/ макроса. Синтаксис VBA я уже знаю. Детали пока не ясны.

Надеюсь что описал доступно. Буду рад любой помощи.

Лекция 6 Тема: Запросы. Вычисления и групповые операциию

6.1. Вычисляемые поля.

6.2. Создание выражений с помощью Построителя выражений.

6.3. Обзор встроенных функций СУБД MS Access.

6.4. Итоговые запросы.

6.5. Перекрестные запросы.

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

В QBE СУБД MS Access такие возможности предоставляются через вычисляемые поля и групповые операции.

6.1. Вычисляемые поля

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

Стоимость: Товары! Цена * Количество * (1-Скидка)

КоличествоМужчин: Sum(IIf(Пол = «м»; 1; 0))

Стоимость, КоличествоМужчин и ФИО являются именами вычисляемых полей и отображаются в режиме таблицы в заголовке столбца, символ двоеточие исполняет роль разделителя между именем вычисляемого поля и выражением.

Если имя таблицы или поля содержит пробелы, то его идентификатор обязан в выражении заключаться в квадратные скобки, например

Стоимость: Цена*[Количество товара]

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

Обновить вычисленные результаты вручную невозможно.

Для построения сложных выражений в СУБД MS Access входит утилита, называемая Построитель выражений.

6.2. Создание выражений с помощью Построителя выражений

Построитель выражений можно запустить щелкнув по клавише Построить на панели инструментов Конструктор запросов или выбрав в контекстном меню поля бланка запроса QBE команду меню Построить….

Рис.6.1. Диалоговое окно Построитель выражений

Рис.6.2. Диалоговое окно Построителя выражений
со сформированным выражением

6.3. Обзор встроенных функций СУБД MS Access

СУБД MS Access содержит более 100 встроенных функций (рис.6.3), которые можно использовать при формировании вычисляемого поля или при задании условия отбора.

Рис.6.3. Диалоговое окно Построителя выражений
со списком встроенных функций

Все встроенные функции в Построителе выражений сгруппированы по функциональному назначению.

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

Обзор всех встроенных функций выходит за рамки лекции, поэтому рассмотрим наиболее используемые.

Таблица 6.1

Функции категории Дата/время

Возвращает значение дня месяца от 1 до 31

Возвращает значение месяца от 1 до 12

Возвращает название месяца соответствующего номеру месяца: 1 – январь, 2 – февраль, и т. д. Если значение аргумента флаг Истина, то функция возвращает аббревиатуру месяца: 1 – янв, 2 – фев и т. д.

Возвращает значение года от 100 до 9999

Если аргумент число не указан, возвращает значение дня недели от 1 (воскресенье) до 7 (суббота). Если аргумент число имеет значение 0, то возвращает значение дня недели от 1 (понедельник) до 7 (воскресенье)

Возвращает целое число от 0 од 23, представляющее значение часа

Возвращает числовое значение в зависимости от значения аргумента интервал:

«q» – квартал (от 1 до 4);

«m» – месяц (от 1 до 12);

«yyyy» – год (от 100 до 9999);

«ww» – неделя (от 1 до 53);

и т. п. (см. справку по функции).

Возвращает текущую системную дату

Функции категории Проверка

Возвращает значение Истина если значение переменной (значение поля в источнике данных) равно Null

Возвращает значение Истина если значение переменной (значение поля в источнике данных) имеет один из числовых типов данных

Функции категории Управление

IIf(условие; выр1; выр2)

Возвращает значение выражения выр1 если значение аргумента условие Истина и возвращает значение выражения выр2 если значение аргумента условие Ложь. Данная функция аналогична функции ЕСЛИ в MS Excel.

Продолжение таблицы 6.1

Функции категории Текстовые

Возвращает n левых символов аргумента текст

Возвращает n правых символов аргумента текст

Mid(текст; нач_поз[; n])

Возвращает n символов начиная с позиции нач_поз аргумента текст. Если аргумент n не указан, то возвращает все символы до конца строки начиная с позиции нач_поз аргумента текст.

Возвращает количество символов (длину строки) в аргументе текст

Возвращает строковое значение аргумента текст без начальных пробелов

Возвращает строковое значение аргумента текст без заключительных пробелов

Возвращает строковое значение аргумента текст без начальных и заключительных пробелов

Возвращает строковое значение аргумента число

Возвращает значение аргумента переменная в формате заданным аргументом формат

6.3. Итоговые запросы

При анализе данных очень часто интересуют не отдельные записи, а итоговые значения по группам данных, например:

— количество сделок с Партнерами за определенный промежуток времени;

— средний объем продаж по каждому месяцу за предыдущий год.

Ответы на такие вопросы дает итоговый запрос.

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

По умолчанию для каждого поля, занесенного в бланк запроса, устанавливается значение Группировка (итоги не подводятся).

Для подведения итогов необходимо заменить установку Группировка на конкретную итоговую функцию. В СУБД MS Access предусмотрено 9 функций (табл. 6.2), обеспечивающих выполнение групповых операций.

Таблица 6.2

Функции категории Статистические

Возвращает сумму набора значений

Возвращает среднее арифметическое набора значений

Возвращает наименьшее значение из набора значений

Возвращает наибольшее значение из набора значений

Возвращает количество записей в наборе значений отличных от Null

Возвращает первое значение поля в группе

Возвращает последнее значение поля в группе

Возвращает среднеквадратичное отклонение набора значений

Возвращает дисперсию набора значений

В раскрывающемся списке строки Групповая операция имеется установка Выражение. Данная установка применяется, когда в выражении (строка Поле) используется несколько итоговых функций.

В раскрывающемся списке строки Групповая операция имеется установка Условие. Данная установка применяется, когда в строке Условие отбора записано условие выборки, но данные столбца (поля) не должны участвовать в групповой операции.

Для решения более сложных статистических задач в СУБД MS Access предусмотрен специальный тип запроса – перекрестный запросов.

6.4. Перекрестные запросы

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

Для построения перекрестного запроса достаточно трех полей базового источника. По повторяющимся значениям одного поля формируются названия заголовков строк итоговой (сводной) таблицы (рис.6.4). По повторяющимся значениям другого поля формируются названия заголовков столбцов итоговой (сводной) таблицы. Результаты статистической обработки по третьему полю отображаются в ячейках сводной таблицы (область значений). Пример перекрестного запроса в режиме конструктора представлен на рис.6.5, а результаты выполнения запроса на рис. 6.6.

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

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