Oracle передача параметров между сессиями
В PL/SQL в любом случае можно вызвать несколько сеансов и одновременно выполнить процедуру на нескольких сеансах.
Я хочу использовать это в приложениях реального времени, где в приложение входит 250 пользователей. Пользователи подключаются к Oracle через клиентский инструмент. (Power Builder - это инструмент Front End)
Например, если пользователь вызывает хранимую процедуру, эта хранимая процедура должна выполняться 10 раз с разными значениями параметров.
Я не хочу запускать это последовательно один за другим 10 раз в том же сеансе, потому что это может занять много времени.
Я ищу способ, которым я могу запустить хранимую процедуру в 10 разных сеансах одновременно.
Я думал о размещении 10 заданий с использованием DBMS_JOB.SUBMIT, но из-за
тяжелая рабочая нагрузка (250 пользователей * 10 = 2500 заданий могут быть запланированы в планировщике заданий в одно и то же время и т.д.), наша группа DBA ищет другой лучший способ.
Альтернативно для создания JOB вы можете использовать DBMS_PARALLEL_EXECUTE пакет.
Вот некоторые хиты:
Используйте create_chunks_by_sql с by_rowid => FALSE , то есть используя идентификатор и создайте точное количество блоков как необходимое выполнение хранимой процедуры.
В run_task установите parallel_level в требуемую степень parallelism. Это то же самое число, что указано выше или ниже, если вам нужно o дросселировать parallelism.
Передайте вызов
процедура как параметр sql_stmt , например.
Необязательно, как вы видите, можно передать номер фрагмента в процедуру, поэтому вы можете использовать его как threadId .
Здесь полный пример
Создать задачу и 3 куска
Запустить задачу с помощью DOP = 3
Удалить задачу
Чтобы избежать публикации нескольких заданий Oracle, вы можете попробовать использовать William Robertson Parallel PL/SQL launcher.
если вы создаете таблицу " PQ_DRIVER " с 4 разделами и параллельной степенью 4 с одной строкой в каждом разделе и выполняете запрос по строкам " SELECT /*+ PARALLEL(pq,4) */ thread_id FROM pq_driver pq ", который должен убеждать PQ контроллер для установки четырех подчиненных процессов PQ для работы над ним (по одному на раздел). И если вы передадите этот запрос в качестве параметра курсора в конвейерную функцию с поддержкой параллелизма, то не следует ли создавать ситуацию, когда каждая строка обрабатывается отдельным ведомым процессом PQ? Итак, вот способ использования (хорошо, взломать) механизм PQ, чтобы он обрабатывал произвольные вызовы процедур PL/SQL параллельно.
Идея состоит в том, чтобы создать функцию, используя функции PARALLEL_ENABLE и PIPELINED :
Функция execute_command использует autonomous_transaction .
Пакет DBMS_ALERT поддерживает отправку и получение асинхронных уведомлений о событиях (alerts). Это могут быть уведомления об изменении данных в БД, отправленные триггером, или об окончании выполнения некоторой процедуры. Приложение в отдельном сеансе ожидает уведомления, на которые подписалось, и обрабатывает их тем или иным образом, например, отражая наступившие события в пользовательском интерфейсе или выполняя операции с данными, зависящие от наступления события.
Вот основные свойства уведомлений DBMS_ALERT , почерпнутые мной из официальной документации:
Во втором сеансе отправим уведомление myalert и вернемся к первому сеансу, чтобы увидеть результат.
- один сеанс посылает уведомления при помощи DBMS_ALERT.SIGNAL и COMMIT .
- другой сеанс
- подписывается на уведомления при помощи DBMS_ALERT.REGISTER ,
- ожидает уведомления при помощи DBMS_ALERT.WAITONE (или WAITANY ) и обрабатывает их,
- удаляет подписку на уведомления, когда в них больше нет необходимости.
Попробую отправлять разные уведомления из нескольких параллельных сеансов и получать эти уведомления в другом сеансе.
Для этого создам процедуру signaller , которая будет посылать 10 уведомлений bang или boom , выбирая из двух случайным образом (строка 8 ниже). Отправив уведомление, процедура спит случайное число секунд между 1 и 7, имитируя занятость, и затем отправляет следующее уведомление. Для создания процедуры текущий пользователь должен явно (не через роль) получить привилегии EXECUTE на пакеты SYS.DBMS_ALERT и SYS.DBMS_LOCK .
Для получения уведомлений bang и boom создам процедуру consumer с параметром p_sleep - числом секунд между вызовами DBMS_ALERT.WAITANY . На это время consumer будет делать паузы между ожиданиями уведомлений, что приведет к потере некоторых уведомлений, если они отправляются достаточно часто. По умолчанию p_sleep равен 0, что практически устраняет паузы между ожиданиями, и уведомления не должны теряться.
Каждый раз процедура ожидает уведомления не более 10 секунд - см. значение параметра timeout при вызове DBMS_ALERT.WAITANY в строке 11.
Теперь, с помощью DBMS_SCHEDULER , я запущу процедуру signaller параллельно в двух сеансах и процедуру consumer в текущем сеансе:
Как видим, в текущем сеансе приняты все отправленные уведомления. Повторим эксперимент, введя паузу в 10 секунд между ожиданиями уведомлений:
На этот раз часть уведомлений была потеряна, чего и следовало ожидать.
В официальной документации по СУБД Oracle 11gR2 можно подробно познакомиться со всеми процедурами и функциями DBMS_ALERT . А я перейду к экспериментам с пакетом DBMS_PIPE , удалив ненужные теперь процедуры:
Пакет DBMS_PIPE позволяет двум или более сеансам пересылать друг другу данные по именованным каналам (pipes). Вот основные сведения о DBMS_PIPE :
Следующий PL/SQL блок открывает три канала - частный явный, публичный явный и публичный неявный:
Итак, каналы были созданы. Теперь удалю созданные каналы, воспользовавшись DBMS_PIPE.REMOVE_PIPE для явных каналов и прочитав данные из неявного:
Как видим, после удаления каналы остались во вью v$db_pipes . Однако, вызов DBMS_PIPE.REMOVE_PIPE сбросил в 0 размеры каналов и изменил тип канала my_private_pipe с PRIVATE на PUBLIC . Не совсем то, чего можно было ожидать! При этом вызовы функции DBMS_PIPE.REMOVE_PIPE вернули статус 0, следовательно, выполнились без ошибок. В утешение остается заметить, что вью v$db_pipes не упоминается в документации по пакету DBMS_PIPE . И нет необходимости в него смотреть.
Завершая разговор о DBMS_PIPE , замечу, что не все мои эксперименты с этим пакетом прошли гладко и привели к ожидаемому результату. Кто заинтересовался, может подробнее познакомиться с процедурами и функциями DBMS_PIPE по официально документации по СУБД Oracle и продолжить эксперименты.
Мы рассмотрели несколько примеров использования параметров с NDS . Давайте познакомимся с различными правилами и специальными ситуациями, которые могут вам встретиться при передаче параметров.
SQL -запросу могут передаваться только выражения (литералы, переменные, сложные выражения), заменяющие формальные параметры в строке значениями данных. Не допускается передача имен элементов схемы (таблиц, столбцов и т. д.) или целых фрагментов кода SQL (например, условий WHERE ), которые должны строиться посредством конкатенации.
Допустим, вы хотите создать процедуру для очистки заданного представления или таблицы. Первая версия может выглядеть примерно так:
При упрощении процедуры до следующего вида:
Почему же в NDS (как, впрочем, и в пакете DBMS_SQL ) имеется такое ограничение? При передаче строки команде EXECUTE IMMEDIATE исполняющее ядро должно прежде всего выполнить синтаксический анализ команды, чтобы убедиться в правильности ее определения. PL/SQL может определить, что следующая команда определена правильно, даже не зная значения параметра :xyz:
Но корректность следующий команды PL/SQL проверить не сможет:
По этой причине в данной ситуации необходимо использовать конкатенацию:
Дублирование формальных параметров
При выполнении динамической команды SQL связь между формальными и фактическими параметрами устанавливается в соответствии с их позициями. Однако интерпретация одноименных параметров зависит от того, какой код используется — SQL или PL/SQL .
- При выполнении динамической команды SQL (DML - или DDL-строки, не оканчивающейся точкой с запятой) параметр подстановки нужно задать для каждого формального параметра, даже если их имена повторяются.
- При выполнении динамического блока PL/SQL (строки, оканчивающейся точкой с запятой) нужно указать параметр подстановки для каждого уникального формального параметра.
Далее приведен пример динамической команды SQL с повторяющимися формальными параметрами. Особое внимание обратите на повторяющийся параметр подстановки val_in в секции USING :
А вот динамический блок PL/SQL с повторяющимися формальными параметрами — для него в секции USING параметр val_in задан только один раз:
Значения по умолчанию
Как было показано в предыдущих примерах, параметрам IN можно задать значения по умолчанию. Если параметр IN имеет значение по умолчанию, включать этот параметр в вызов программы не обязательно. Значение по умолчанию параметра вычисляется и используется программой только в том случае, если параметр не был включен в список при вызове. Конечно, для всех параметров IN OUT фактические параметры должны включаться в список. Значение по умолчанию определяется для параметра так же, как для объявляемой переменной. Предусмотрены два способа задания значений по умолчанию — с ключевым словом DEFAULT и с оператором присваивания ( := ):
Значения по умолчанию позволяют вызывать программы с разным количеством фактических параметров. Программа использует значения по умолчанию для всех незаданных параметров, а для параметров в списке значения по умолчанию заменяются указанными значениями. Несколько примеров разных вариантов использования связывания по позиции:
В первом вызове оба параметра задаются явно. Во втором вызове задается только первый фактический параметр, поэтому born_at_in присваивается текущая дата и время. В третьем вызове параметры вообще не указаны, поэтому круглые скобки отсутствуют (то же относится и к последнему вызову, в который включены пустые круглые скобки). Оба значения по умолчанию используются в теле процедуры.
Чтобы пропустить начальные параметры, имеющие значения по умолчанию, необходимо использовать связывание по имени. Включая имена формальных параметров, можно указать только те параметры, для которых необходимо передать значения:
Use the ALTER SESSION statement to set or modify any of the conditions or parameters that affect your connection to the database. The statement stays in effect until you disconnect from the database.
To enable and disable the SQL trace facility, you must have ALTER SESSION system privilege.
To enable or disable resumable space allocation, you must have the RESUMABLE system privilege.
You do not need any privileges to perform the other operations of this statement unless otherwise indicated.
The ADVISE clause sends advice to a remote database to force a distributed transaction. The advice appears in the ADVICE column of the DBA_2PC_PENDING view on the remote database (the values are ' C ' for COMMIT , ' R ' for ROLLBACK , and ' ' for NOTHING ). If the transaction becomes in doubt, then the administrator of that database can use this advice to decide whether to commit or roll back the transaction.
You can send different advice to different remote databases by issuing multiple ALTER SESSION statements with the ADVISE clause in a single transaction. Each such statement sends advice to the databases referenced in the following statements in the transaction until another such statement is issued.
CLOSE DATABASE LINK Clause
Specify CLOSE DATABASE LINK to close the database link dblink . When you issue a statement that uses a database link, Oracle Database creates a session for you on the remote database using that link. The connection remains open until you end your local session or until the number of database links for your session exceeds the value of the initialization parameter OPEN_LINKS . If you want to reduce the network overhead associated with keeping the link open, then use this clause to close the link explicitly if you do not plan to use it again in your session.
ENABLE | DISABLE COMMIT IN PROCEDURE
Procedures and stored functions written in PL/SQL can issue COMMIT and ROLLBACK statements. If your application would be disrupted by a COMMIT or ROLLBACK statement not issued directly by the application itself, then specify DISABLE COMMIT IN PROCEDURE clause to prevent procedures and stored functions called during your session from issuing these statements.
You can subsequently allow procedures and stored functions to issue COMMIT and ROLLBACK statements in your session by issuing the ENABLE COMMIT IN PROCEDURE .
Some applications automatically prohibit COMMIT and ROLLBACK statements in procedures and stored functions. Refer to your application documentation for more information.
ENABLE | DISABLE GUARD
The security_clause of ALTER DATABASE lets you prevent anyone other than the SYS user from making any changes to data or database objects on the primary or standby database. This clause lets you override that setting for the current session.
security_clause for more information on the GUARD setting
PARALLEL DML | DDL | QUERY
The PARALLEL parameter determines whether all subsequent DML, DDL, or query statements in the session will be considered for parallel execution. This clause enables you to override the degree of parallelism of tables during the current session without changing the tables themselves. Uncommitted transactions must either be committed or rolled back prior to executing this clause for DML.
Specify ENABLE to execute subsequent statements in the session in parallel. This is the default for DDL and query statements.
DML : DML statements are executed in parallel mode if a parallel hint or a parallel clause is specified.
DDL : DDL statements are executed in parallel mode if a parallel clause is specified.
QUERY : Queries are executed in parallel mode if a parallel hint or a parallel clause is specified.
Restriction on the ENABLE clause
You cannot specify the optional PARALLEL integer with ENABLE .
Specify DISABLE to execute subsequent statements in the session serially. This is the default for DML statements.
DML : DML statements are executed serially.
DDL : DDL statements are executed serially.
QUERY : Queries are executed serially.
Restriction on the DISABLE clause
You cannot specify the optional PARALLEL integer with DISABLE .
FORCE forces parallel execution of subsequent statements in the session. If no parallel clause or hint is specified, then a default degree of parallelism is used. This clause overrides any parallel_clause specified in subsequent statements in the session but is overridden by a parallel hint.
DML : Provided no parallel DML restrictions are violated, subsequent DML statements in the session are executed with the default degree of parallelism, unless a degree is specified in this clause.
DDL : Subsequent DDL statements in the session are executed with the default degree of parallelism, unless a degree is specified in this clause. Resulting database objects will have associated with them the prevailing degree of parallelism.
Specifying FORCE DDL automatically causes all tables created in this session to be created with a default level of parallelism. The effect is the same as if you had specified the parallel_clause (with the default degree) in the CREATE TABLE statement.
QUERY : Subsequent queries are executed with the default degree of parallelism, unless a degree is specified in this clause.
Specify an integer to explicitly specify a degree of parallelism:
For FORCE DDL , the degree overrides any parallel clause in subsequent DDL statements.
For FORCE DML and QUERY , the degree overrides the degree currently stored for the table in the data dictionary.
A degree specified in a statement through a hint will override the degree being forced.
The following types of DML operations are not parallelized regardless of this clause:
Operations on cluster tables
Operations with embedded functions that either write or read database or package states
Operations on tables with triggers that could fire
Operations on tables or schema objects containing object types, or LONG or LOB data types
These clauses let you enable and disable resumable space allocation. This feature allows an operation to be suspended in the event of an out-of-space error condition and to resume automatically from the point of interruption when the error condition is fixed.
Resumable space allocation is fully supported for operations on locally managed tablespaces. Some restrictions apply if you are using dictionary-managed tablespaces. For information on these restrictions, refer to Oracle Database Administrator's Guide .
This clause enables resumable space allocation for the session.
TIMEOUT lets you specify (in seconds) the time during which an operation can remain suspended while waiting for the error condition to be fixed. If the error condition is not fixed within the TIMEOUT period, then Oracle Database aborts the suspended operation.
NAME lets you specify a user-defined text string to help users identify the statements issued during the session while the session is in resumable mode. Oracle Database inserts the text string into the USER_RESUMABLE and DBA_RESUMABLE data dictionary views. If you do not specify NAME , then Oracle Database inserts the default string ' User username ( userid ), Session sessionid , Instance instanceid '.
Oracle Database Reference for information on the data dictionary views
This clause disables resumable space allocation for the session.
SHARD DDL Clauses
These clauses are valid only if you are connected to a sharded database. They let you control whether DDLs issued in the session are issued against the shard catalog database and all shards, or against only the shard catalog database.
If you specify ENABLE SHARD DDL , then DDLs issued in the session are issued against the shard catalog database and all shards. This mode is the default for the SDB user—a user that exists in the shard catalog database and in all shards.
If you specify DISABLE SHARD DDL , then DDLs issued in the session are issued against only the shard catalog database. This mode is the default for a local user—a user that exists only in the shard catalog database.
SYNC WITH PRIMARY
Use this clause to synchronize redo apply on a physical standby database with the primary database. An ALTER SESSION statement with this clause blocks until redo apply has applied all redo data received by the standby at the time the statement is issued. This clause returns an error, and synchronization does not occur, if the redo transport state for the standby database is not SYNCHRONIZED or if redo apply is not active.
Oracle Data Guard Concepts and Administration for more information on this session parameter
Use the alter_session_set_clause to set initialization parameter values or to set an edition for the current session.
You can set two types of parameters using this clause:
Initialization parameters that are dynamic in the scope of the ALTER SESSION statement (listed in "Initialization Parameters and ALTER SESSION" )
You can set values for multiple parameters in the same alter_session_set_clause .
Specify EDITION = edition to set the specified edition as the edition in the database session. You must have the USE object privilege on edition , edition must already have been created, and it must be USABLE .
When this statement is successful, the database discards PL/SQL package state corresponding to editionable packages but retains package state corresponding to packages that are not editionable.
You can also set the edition for the current session at startup with the EDITION parameter of the SQL*Plus CONNECT command. However, you cannot specify an ALTER SESSION SET EDITION statement in a recursive SQL or PL/SQL block.
You can determine the edition in use by the current session with the following query:
CREATE EDITION for more information on editions and Oracle Database PL/SQL Language Reference for information on how editions are designated as USABLE
Use this clause in a multitenant container database (CDB) to switch to the container specified by container_name .
To use this clause, you must be a common user with the SET CONTAINER privilege, either granted commonly or granted locally in container_name .
For container_name , specify one of the following:
CDB$ROOT to switch to the root
PDB$SEED to switch to the seed
A pluggable database (PDB) name to switch to that PDB. You can view the names of the PDBs in a CDB by querying the DBA_PDBS view.
You can determine the container to which the current session is connected by using the SQL*Plus SHOW CON_NAME command or with the following SQL query:
By default, when you switch to a container, the session uses the default service for the container. Specify the SERVICE clause to use a different service for the container. For service_name , specify the name of the service you want to use.
Oracle Database Administrator's Guide for more information on switching to a container
ROW ARCHIVAL VISIBILITY
Use this clause to configure row archival visibility for the session. This clause lets you implement In-Database Archiving, which allows you to designate table rows as active or archived. You can then perform queries on only the active rows within the table.
If you specify ACTIVE , then the database will consider only active rows when performing queries on tables that are enabled for row archival. This is the default.
If you specify ALL , then the database will consider all rows when performing queries on tables that are enabled for row archival.
This clause has no effect on queries on tables that are not enabled for row archival.
The CREATE TABLE ROW ARCHIVAL clause to learn how to enable a new table for row archival
The ALTER TABLE [NO] ROW ARCHIVAL clause to learn how to enable or disable an existing table for row archival
Oracle Database VLDB and Partitioning Guide for more information on In-Database Archiving
Use this clause to set the default collation for the session.
Use collation_name to specify the default collation for the session. You can specify the name of any valid named collation or pseudo-collation. This collation becomes the effective schema default collation . This collation is assigned to tables, views, and materialized views that are subsequently created in any schema for the duration of the session. The default collation for the session does not get propagated to any remote sessions connected to the current session using DB links.
If you specify NONE , then there is no default collation for the session. In this case, the default collation for a particular schema becomes the effective schema default collation for that schema. That default collation is assigned to tables, views, and materialized views that are subsequently created in the schema for the duration of the session.
In either of the preceding cases, you can override the effective schema default collation and assign a default collation to a particular table, materialized view, or view by specifying the DEFAULT COLLATION clause of the CREATE or ALTER statement for the table, materialized view, or view.
The effective schema default collation also affects the DDL statements CREATE FUNCTION , CREATE PACKAGE , CREATE PROCEDURE , CREATE TRIGGER , and CREATE TYPE . Refer to Oracle Database PL/SQL Language Reference for more details on these statements.
You can query the default collation for a session with the following statement:
You can specify the SET DEFAULT_COLLATION clause only if the COMPATIBLE initialization parameter is set to 12.2 or greater, and the MAX_STRING_SIZE initialization parameter is set to EXTENDED .
The DEFAULT COLLATION Clause clause of CREATE USER for more information on the default collation of a schema
The effective schema default collation for a session should not be confused with the session parameter NLS_SORT . The effective schema default collation is used by DDL statements to decide the default data-bound collation of tables, views, and materialized views when they are created. The session parameter NLS_SORT points to a named collation that is used when Oracle executes a query, a DML statement, or PL/SQL code containing a SQL operation whose determined collation is a pseudo-collation, such as USING_NLS_COMP or USING_NLS_SORT . Refer to Oracle Database Globalization Support Guide for more information.
Передача значений NULL
При передаче NULL в качестве параметра подстановки — например, как в команде:
Что же делать, если вам потребуется передать в динамический код значение NULL ? Это можно сделать двумя способами.
Во-первых, значение можно скрыть в переменной, для чего проще всего использовать неинициализированную переменную подходящего типа:
Во-вторых, с помощью функции преобразования типа можно явно преобразовать NULL в типизированное значение:
Для передачи информации между модулем и вызывающим блоком PL/SQL используются параметры.
Параметры модуля, являющиеся частью его заголовка (или сигнатуры), являются не менее важными компонентами модуля, чем находящиеся в нем исполняемые команды. Заголовок программы иногда называется контрактом между автором и пользователями. Конечно, автор должен позаботиться о том, чтобы модуль выполнял свою задачу. Но ведь модули создаются именно для того, чтобы их можно было вызывать повторно — в идеале более чем из одного модуля. Если список параметров плохо составлен, то другим программистам будет сложно применять такой модуль. В таком случае уже будет неважно, насколько хорошо он написан.
Многие разработчики не уделяют должного внимания наборам параметров своих модулей. Следующие рекомендации помогут вам сделать правильный выбор:
- Количество параметров. Если процедура или функция имеет слишком мало параметров, это снижает ее универсальность; с другой стороны, слишком большое количество параметров усложняет ее повторное использование. Конечно, количество параметров в основном определяется требованиями программы, но существуют разные варианты их определения (например, несколько параметров можно объединить в одну запись).
- Типы параметров. При выборе типа необходимо учитывать, для каких целей будут использоваться параметры: только для чтения, только для записи, для чтения и записи.
- Имена параметров. Параметрам следует присваивать простые имена, отражающие их назначение в модуле.
- Значения по умолчанию. Когда параметру следует задать значение по умолчанию, а когда нужно заставить программиста ввести определенное значение?
PL/SQL предоставляет много средств эффективного планирования. В этом разделе представлены все элементы определения параметров.
Режимы передачи параметров в SQL
При передаче значений параметров команды SQL можно использовать один из трех режимов: IN ( только чтение, действует по умолчанию), OUT (только запись) или IN OUT (чтение и запись). При выполнении динамического запроса все параметры команды SQL , за исключением параметра в секции RETURNING , должны передаваться в режиме IN :
Параметры подстановки команды SQL , передаваемые в режимах OUT и IN OUT , используются прежде всего при выполнении динамического PL/SQL . В этом случае режимы передачи параметров соответствуют аналогичным режимам обыкновенных программ PL/SQL , а также использованию переменных в динамических блоках PL/SQL . Несколько общих рекомендаций, касающихся использования секции USING при выполнении динамического PL/SQL :
- В качестве параметра подстановки, передаваемого в режиме IN , может быть задан любой элемент соответствующего типа: литеральное значение, именованная константа, переменная или сложное выражение. Такой элемент сначала вычисляется, а затем передается в динамический блок PL/SQL .
- Для значения параметра динамической команды в режиме OUT или IN OUT следует объявить переменную.
- Значения можно подставлять только вместо тех параметров динамического блока PL/SQL , тип которых поддерживается SQL . Например, если параметр процедуры имеет тип BOOLEAN, его значение нельзя задать или считать с помощью секции USING .
Это ограничение частично снято в версии 12.1 и выше. Теперь разрешается подстановка многих типов PL/SQL , включая типы записей и коллекций, но подстановка BOOLEAN по-прежнему запрещена.
Давайте рассмотрим механизм передачи параметров на примерах. Вот заголовок процедуры с параметрами IN, OUT и IN OUT :
Пример блока с динамическим вызовом этой процедуры:
Поскольку процедура имеет четыре параметра, в секции USING также должно быть указано четыре элемента. Для первых двух параметров, передаваемых в режиме IN , следует задать литеральные значения или выражения, а следующие два элемента должны быть именами переменных, так как для них заданы режимы OUT и IN OUT .
Но что, если два и более формальных параметра имеют одинаковые имена?
Связывание формальных и фактических параметров в PL/SQL
Каким образом при выполнении программы определяется, какому формальному параметру должен соответствовать фактический параметр? PL/SQL предлагает два метода установления такого соответствия:
- по позиции (неявное связывание);
- по имени (явное связывание с указанием имени формального параметра и обозначения = >).
Позиционное связывание
Во всех приводившихся ранее примерах применялось связывание параметров в соответствии с их позицией. При использовании этого способа PL/SQL принимает во внимание относительные позиции параметров, то есть N-й фактический параметр в вызове программы связывается с N-м формальным параметром в заголовке программы. В следующем примере PL/SQL связывает первый фактический параметр : order. company_id с первым формальным параметром company_id_in , а второй фактический параметр 'N' — со вторым формальным параметром status_in :
Метод позиционного связывания параметров, безусловно, является более наглядным и понятным (рис. 3).
Рис. 3. Позиционное связывание параметров
связывание по имени
Чтобы установить соответствие параметров по имени, следует при вызове подпрограммы явно связать формальный параметр с фактическим. Для этого используется комбинация символов =>:
Поскольку имя формального параметра указано явно, PL/SQL уже не нужно учитывать порядок параметров. Поэтому в данном случае при вызове модуля не обязательно перечислять параметры в порядке их следования в заголовке. Функцию total_sales можно вызывать любым из двух способов:
В одном вызове можно комбинировать оба метода связывания фактических и формальных параметров:
При этом все «позиционные» параметры должны быть перечислены перед «именованными», как в приведенном примере. Позиционному методу необходима начальная точка для отсчета позиций, которой может быть только первый параметр. Если разместить «именованные» параметры перед «позиционными», PL/SQL собьется со счета. Оба вызова total_sales , приведенные ниже, недопустимы. В первой команде «именованные» параметры указываются до «позиционных», а во второй используется позиционная запись, но параметры перечислены в неверном порядке. В этом случае PL/SQL пытается преобразовать 'N' к типу NUMBER (для параметра company_id ):
Преимущества связывания по имени
Возникает резонный вопрос: зачем использовать связывание по имени? У него есть два основных преимущества:
- Повышение информативности. При использовании связывания по имени вызов программы содержит сведения о формальных параметрах, с которыми ассоциируются фактические значения. Если вы не знаете, что делают модули, вызываемые приложением, наличие имен формальных параметров помогает понять назначение конкретного программного вызова. В некоторых средах разработки именно по этой причине связывание по имени считается стандартным. Эта причина особенно актуальна в том случае, когда имена формальных параметров строятся по схеме с присоединением суффикса режима. Только взглянув на вызов процедуры или функции, разработчик сразу видит, в каком направлении передаются те или иные данные.
- Гибкость при описании параметров. Вы имеете возможность размещать параметры в удобном для работы порядке (конечно, это не значит, что при вызове аргументы можно перечислять так, как вам заблагорассудится!) и включать в список только те из них, которые действительно необходимы. В сложных приложениях иногда создаются процедуры с десятками параметров. Но как вы знаете, любой параметр, значение которого определяется по умолчанию, при вызове модуля может быть опущен. Разработчик может передать процедуре только те значения, которые необходимы для решения текущей задачи.
Давайте посмотрим, как реализовать все эти преимущества в программе. Возьмем для примера следующий заголовок:
Анализ списка параметров приводит нас к следующим выводам:
- Минимальное количество аргументов, которые должны передаваться business_as_ usual , равно 3. Чтобы определить его, сложите количество параметров IN, не имеющих значения по умолчанию, с количеством параметров OUT и IN OUT.
- При использовании связывания по позиции программа может вызываться с четырьмя или пятью аргументами, потому что последний параметр объявлен в режиме IN со значением по умолчанию.
- Для хранения значений, возвращаемых параметрами OUT и IN OUT, понадобятся как минимум две переменные.
С таким списком параметров программа может вызываться несколькими способами:
- Связывание только по позиции: задаются все фактические параметры. Обратите внимание, как трудно вспомнить, какой формальный параметр связывается с каждым из этих значений (и каков его смысл):
- Связывание только по позиции: минимальное количество фактических параметров. Понять смысл вызова все еще нелегко:
- Связывание только по имени с сохранением исходного порядка. Обратите внимание: смысл вызова business_as_usual вполне понятен и не требует пояснений:
- Все параметры IN пропускаются, и для них используются значения по умолчанию (другая важная возможность связывания по имени):
- Связывание по имени с изменением порядка следования фактических параметров и передачей неполного списка:
- Объединение связывания по имени и по позиции. Список параметров начинается со связывания по позиции, а после перехода на связывание по имени вернуться к позиционному связыванию уже не удастся:
Как видите, механизм передачи аргументов в PL/SQL весьма гибок. Как правило, связывание по имени способствует написанию кода, который лучше читается и создает меньше проблем с сопровождением. От вас потребуется совсем немного: найти и записать имена параметров.
Квалификатор режима параметра NOCOPY
PL/SQL предоставляет возможность изменить определение параметра при помощи конструкции NOCOPY . NOCOPY требует, чтобы компилятор PL/SQL не создавал копии аргументов OUT и IN — и в большинстве случаев компилятор это требование удовлетворяет. NOCOPY используется прежде всего для повышения эффективности передачи больших конструкций (например, коллекций) в аргументах IN OUT .
Initialization Parameters and ALTER SESSION
Some initialization parameter are dynamic in the scope of ALTER SESSION . When you set these parameters using ALTER SESSION , the value you set persists only for the duration of the current session.To determine whether a parameter can be altered using an ALTER SESSION statement, query the ISSES_MODIFIABLE column of the V$PARAMETER dynamic performance view.
Before changing the values of initialization parameters, refer to their full description in Oracle Database Reference .
A number of parameters that can be set using ALTER SESSION are not initialization parameters. You can set them only with ALTER SESSION , not in an initialization parameter file. Those session parameters are described in "Session Parameters and ALTER SESSION" .
Определение параметров
Формальные параметры определяются в списке параметров программы. Синтаксис определения параметров очень близок к синтаксису объявления переменных в разделе объявлений блока PL/SQL . Впрочем, существуют два важных различия : во-первых, для параметров определяется режим передачи, которого нет у объявлений переменных; во-вторых, объявление параметра должно быть неограниченным.
Ограниченное объявление устанавливает некоторые ограничения для значений, которые могут присваиваться переменной, объявленной с этим типом. Например, следующее объявление переменной company_name ограничивает переменную 60 символами:
При объявлении параметра ограничивающая часть опускается:
Формальные и фактические параметры
Очень важно понимать различия между формальными и фактическими параметрами. Формальные параметры представляют собой имена, объявленные в списке параметров заголовка модуля, тогда как фактические параметры — это значения и выражения, которые помещаются в список параметров при вызове модуля.
Различия между формальными и фактическими параметрами нам также поможет понять функция total_sales . Ее заголовок выглядит так:
Формальные параметры total_sales :
company_id_in — первичный ключ (идентификатор компании).
status_in — статус заказов, включаемых в вычисление.
Эти формальные параметры не существуют за пределами модуля. Их можно рассматривать как своего рода «заместителей» реальных значений, передаваемых модулю при его использовании в программе (то есть фактических параметров).
При вызове total_sales необходимо предоставить два аргумента, которыми могут быть переменные, константы или литералы (для параметров в режимах OUT и IN OUT это должны быть переменные). В следующем примере переменная company_id содержит первичный ключ, указывающий на запись компании. В первых трех вызовах total_sales функции передаются жестко запрограммированные значения статуса заказов, а в последнем вызове статус не указан; в этом случае функция присваивает параметру status_in значение по умолчанию, указанное в заголовке:
При вызове total_sales вычисляются значения всех фактических параметров. Результаты вычислений присваиваются соответствующим формальным параметрам внутри функции (обратите внимание: это относится только к параметрам IN и IN OUT; параметры режима OUT не копируются).
Формальные параметры и соответствующие им фактические параметры (указанные при вызове) должны относиться к одинаковым или совместимым типам данных. Во
многих ситуациях PL/SQL выполняет преобразования данных автоматически, однако лучше по возможности обходиться без неявных преобразований. Используйте такие функции, как TO_CHAR и TO_DATE , чтобы точно знать, какие данные получает модуль.
Режимы передачи параметров
При определении параметра также указывается допустимый способ его применения. Он задается с помощью одного из трех указанных в таблице режимов.
Режим | Предназначение | Использование параметров |
IN | Только для чтения | Значение параметра может применяться, но не может быть изменено в модуле. Если режим параметра не задан, используется режим IN |
OUT | Только для записи | В модуле можно присвоить значение параметру, но нельзя использовать его. Впрочем, это «официальное» определение — на самом деле Oracle позволяет читать значение параметра OUT в подпрограмме |
IN OUT | Для чтения и записи | В модуле можно использовать и изменять значение параметра |
Режим параметра указывается непосредственно после его имени, но перед типом данных и необязательным значением по умолчанию. В следующем заголовке процедуры задействованы все три режима передачи параметров:
Процедура predict_activity принимает дату последнего действия ( last_date ) и описание этого действия ( task_desc_inout ). Возвращает она два значения: описание действия (возможно, модифицированное) и дату следующего действия (next_date_out). Поскольку параметр task_desk_inout передается в режиме IN OUT , программа может читать и изменять его значение.
Режим IN
Режим IN используется по умолчанию; если режим параметра не задан, параметр автоматически считается определенным в режиме IN. Тем не менее я рекомендую всегда указывать режим параметра, чтобы предполагаемое использование было явно указано в коде.
В заголовке программы параметрам IN могут присваиваться значения по умолчанию (см. раздел «Значения по умолчанию»).
Фактическим значением параметра IN может быть переменная, именованная константа, литерал или сложное выражение. Все следующие вызовы display_title допустимы:
А если вам потребуется передать данные из своей программы? В таком случае используйте параметр OUT или IN OUT — или рассмотрите возможность преобразования процедуры в функцию.
Режим OUT
Как вы, вероятно, уже поняли, параметр OUT по смыслу противоположен параметру IN. Параметры OUT могут использоваться для возвращения значений из программы вызывающему блоку PL/SQL. Параметр OUT сходен с возвращаемым значением функции, но он включается в список параметров, и количество таких параметров не ограничено (строго говоря, PL/SQL разрешает использовать до 64 000 параметров, но на практике это вряд ли можно считать ограничением).
В программе параметр OUT работает как неинициализированная переменная. Собственно, параметр OUT вообще не содержит никакого значения до успешного завершения вызванной программы (если только вы не использовали ключевое слово NOCOPY . Во время выполнения программы все операции присваивания параметру OUT в действительности выполняются с внутренней копией параметра. Когда программа успешно завершается и возвращает управление вызывающему блоку, значение локальной копии перемещается в параметр OUT. После этого значение становится доступным в вызывающем блоке PL/SQL.
У правил, относящихся к параметрам OUT , есть несколько практических следствий:
- Параметрам OUT нельзя задавать значения по умолчанию. Значение параметра OUT может задаваться только в теле модуля.
- Все операции присваивания параметрам OUT отменяются при инициировании исключения в программе. Так как значение параметра OUT присваивается только в случае успешного завершения программы, все промежуточные присваивания игнорируются. Если обработчик не перехватит исключение и не присвоит значение параметру OUT, параметр останется неизменным. Переменная сохранит значение, которое она имела до вызова программы.
- Фактический параметр, соответствующий формальному параметру OUT, не может быть константой, литералом или выражением. Иначе говоря, он должен поддерживать присваивание.
Как упоминается выше в таблице, Oracle позволяет прочитать значение параметра OUT в подпрограмме. Это значение изначально всегда равно NULL , но после его присваивания в подпрограмме оно становится «видимым», как показывает следующий сценарий:
Режим IN OUT
В параметре IN OUT можно передавать значения программе и возвращать их на сторону вызова (либо исходное, неизменное значение, либо новое значение, заданное в программе). На параметры IN OUT распространяются два ограничения параметров OUT:
- Параметр IN OUT не может быть константой, литералом или выражением.
- Фактический параметр IN OUT должен быть переменной. Он не может быть константой, литералом или выражением, потому что эти форматы не могут использоваться PL/SQL в качестве приемника для размещения исходящих значений.
Других ограничений для параметров IN OUT нет.
Параметры IN OUT могут использоваться в обеих сторонах присваивания, потому что они работают как инициализированные переменные. PL/SQL не теряет значение параметра IN OUT в начале выполнения программы. Это значение может использоваться в программе там, где это необходимо.
Процедура combine_and_format_names объединяет имя и фамилию в заданном формате (« LAST, FIRST » или « FIRST LAST »). Процедура получает имя и фамилию, которые преобразуются к верхнему регистру. В ней продемонстрированы все три режима параметров:
Параметры имени и фамилии должны задаваться в режиме IN OUT . Параметр full_name_ out должен быть параметром OUT , потому что процедура возвращает результат объединения имени и фамилии. Наконец, параметр name_format_in, содержащий форматную строку, объявляется в режиме IN, потому что он описывает способ форматирования, но никак не изменяется в процедуре.
Каждый режим параметров имеет свои характеристики и предназначение. Тщательно выбирайте режим, назначаемый вашим параметрам, чтобы они правильно использовались в модулях.
Определяйте формальные параметры в режимах OUT и IN OUT только в процедурах. Функции должны возвращать всю свою информацию исключительно командой RETURN . Выполнение этой рекомендации упростит понимание и использование ваших подпрограмм. Кроме того, функции с параметрами OUT и IN OUT не могут вызываться из команд SQL .
Session Parameters and ALTER SESSION
The following parameters are session parameters only, not initialization parameters:
Читайте также: