ГЛАВА 8
----------------------------------------------------------------
ИСПОЛНИТЕЛЬНЫЕ ОКРУЖЕНИЯ
Каждый инструмент несет с собой дух, которым он был создан.
Вернер Гейзенберг
Вы можете использовать PL/SQL с различными инструментами
разработки приложений, включая
* Интерфейсы вызовов ORACLE (OCI)
* Прекомпиляторы ORACLE
* SQL*DBA
* SQL*Forms
* SQL*Menu
* SQL*Module
* SQL*Plus
* SQL*ReportWriter
В этой главе рассказывается, как использовать PL/SQL в среде
SQL*Plus, прекомпиляторов ORACLE и интерфейсов OCI.
Замечание: PL/SQL не поддерживает ввода-вывода, поэтому в
интерактивных инструментах, каких как SQL*Plus, вы должны
использовать таблицу базы данных, чтобы сохранять свои
результирующие значения.
Исполнительные окружения 8-1
----------------
Среда SQL*Plus
Если у вас есть опция обработки транзакций, то вы можете
использовать PL/SQL в среде SQL*Plus несколькими способами:
* Ввести и сохранить блок PL/SQL.
* Ввести и выполнить блок PL/SQL.
* Создать скрипт, содержащий блоки PL/SQL.
* Загрузить и выполнить скрипт, содержащий блоки PL/SQL.
* Вызвать хранимую подпрограмму.
Ввод анонимного блока
---------------------
Каждый блок PL/SQL начинается с зарезервированного слова
DECLARE, или, если блок не имеет декларативной части, со слова
BEGIN. Ввод любого из этих слов в ответ на подсказку SQL*Plus
(SQL>) заставляет SQL*Plus:
* очистить буфер SQL
* войти в режим ввода (INPUT)
* игнорировать точки с запятой (терминаторы предложений SQL)
Поскольку SQL*Plus требует, чтобы блок PL/SQL начинался со слов
DECLARE или BEGIN, вы не можете начать блок с метки блока.
Начав ввод блока, вы вводите его целиком, строку за строкой.
Ввод одиночной точки в очередной строке заканчивает ввод блока и
сохраняет его в буфере SQL.
Вы можете сохранить ваш блок PL/SQL в текстовом файле (скрипте)
с помощью следующей команды:
SQL> SAVE имя_файла
Для редактирования этого файла можно использовать построчный
редактор SQL*Plus. Описание этого редактора содержится в
документе SQL*Plus User's Guide. Закончив редактирование файла,
вы можете снова сохранить его, введя следующую команду:
SQL> SAVE имя_файла REPLACE
Выполнение анонимного блока
---------------------------
Заканчивая ввод блока PL/SQL, вы не обязаны вводить одиночную
точку. Если вместо точки ввести в очередной строке одиночную
наклонную черту (/), то блок PL/SQL сохраняется в буфере SQL и
выполняется.
Если буфер SQL содержит блок PL/SQL, то для его выполнения
введите команду RUN или просто наклонную черту:
SQL> RUN
или
SQL> /
8-2 Руководство пользователя и справочник по PL/SQL
После выполнения блока PL/SQL вы вернетесь на подсказку
SQL*Plus. Буфер SQL не очищается до тех пор, пока вы не начнете
ввод очередного предложения SQL или блока PL/SQL.
Создание скрипта
----------------
Вы можете использовать ваш привычный текстовый редактор для
создания текстовых файлов, содержащих блоки PL/SQL. Хотя в
блоке PL/SQL нельзя использовать предложений SQL*Plus, вы можете
в вашем текстовом файле (скрипте) перемешивать блоки PL/SQL с
предложениями SQL*Plus. В следующем примере блок PL/SQL
находится среди предложений SQL*Plus, подготавливающих и
выдающих отчет. Заметьте, что каждый блок PL/SQL должен
заканчиваться наклонной чертой (/).
CLEAR BREAKS;
CLEAR COLUMNS;
COLUMN ENAME HEADING NAME;
TTITLE 'CLERICAL STAFF';
DECLARE
avg_sal NUMBER(7,2);
BEGIN
SELECT AVG(sal) INTO avg_sal FROM emp;
IF avg_sal > 1500 THEN
UPDATE emp SET sal = sal * 1.05 WHERE job LIKE 'CLERK%';
END IF;
END;
/
SELECT ENAME, SAL FROM EMP WHERE JOB LIKE 'CLERK%';
Два предложения CLEAR сбрасывают возможные установки, оставшиеся
от предыдущего отчета. Предложение COLUMN изменяет заголовок
столбца ENAME в отчете на NAME. Предложение TTITLE задает
заголовок, который будет выдаваться на каждой странице отчета.
Наклонная черта после END выполняет блок PL/SQL.
Исполнительные окружения 8-3
Загрузка и выполнение скрипта
-----------------------------
Чтобы загрузить скрипт PL/SQL в буфер SQL*Plus, используйте
следующую команду:
SQL> GET имя_файла;
После загрузки скрипта в буфер SQL вы можете выполнить его, как
описано выше в секции "Выполнение анонимного блока". Если
хотите, вы можете загрузить и выполнить скрипт одной командой:
SQL> START имя_файла;
В блоке PL/SQL можно использовать преимущества средства
подстановки переменных SQL*Plus. Если вы используете это
средство, то SQL*Plus запрашивает у вас значение каждой
переменной, имеющей префикс &, перед выполнением блока PL/SQL.
Например, перед выполнением следующего блока SQL*Plus запросит у
вас значение num_rows:
BEGIN
...
FOR i in 1..&num_rows LOOP
...
END LOOP;
END;
Вызов хранимых подпрограмм
--------------------------
Из SQL*Plus можно вызывать независимые и пакетированные
подпрограммы, хранящиеся в локальной или удаленной базе данных.
В следующем примере вызывается локальная независимая процедура
create_dept:
SQL> EXECUTE create_dept('ADVERTISING', 'NEW YORK');
Этот вызов эквивалентен следующему вызову из анонимного блока
PL/SQL:
SQL> BEGIN create_dept('ADVERTISING', 'NEW YORK'); END;
В следующем примере связь базы данных newyork используется при
вызове удаленной хранимой процедуры raise_salary:
SQL> EXECUTE raise_salary@newyork(7499, 1500);
Вы можете создавать синонимы, чтобы обеспечить прозрачность
адресации для удаленных независимых процедур.
8-4 Руководство пользователя и справочник по PL/SQL
----------------
Среда прекомпилятора ORACLE
Прекомпиляторы ORACLE позволяют встраивать блоки PL/SQL в
программы, написанные на любом из следующих языков высокого
уровня:
* Ada
* C
* COBOL
* FORTRAN
* Pascal
* PL/I
Такие программы и языки называются соответственно
ХОСТ-ПРОГРАММАМИ и ХОСТ-ЯЗЫКАМИ.
Написав программу, вы выполняете прекомпиляцию исходного файла.
Прекомпилятор проверяет программу на синтаксические ошибки, а
затем генерирует модифицированный исходный файл, который можно
компилировать, редактировать и выполнять обычным образом.
Встраивание блоков PL/SQL
-------------------------
Прекомпиляторы рассматривают блок PL/SQL как единое встроенное
предложение SQL. Следовательно, вы можете размещать блок PL/SQL
в любом месте программы, где можно поместить встроенное
предложение SQL. Вы должны поместить блок PL/SQL между ключевыми
словами EXEC SQL EXECUTE и END-EXEC:
EXEC SQL EXECUTE
BEGIN
...
END;
END-EXEC;
Не забывайте, что ключевое слово END-EXEC должно заканчиваться
терминатором, присущим хост-языку, в котором вы пишете.
Исполнительные окружения 8-5
Использование хост-переменных
-----------------------------
ХОСТ-ПЕРЕМЕННЫЕ используются для передачи данных из
хост-программы в блок PL/SQL и для передачи данных и информации
состояния из блока PL/SQL в хост-программу. Как хост-язык, так
и PL/SQL могут устанавливать и обращаться к значению
хост-переменной.
Значение ВХОДНОЙ хост-переменной устанавливается хост-программой
и используется ORACLE. Обратно, значение ВЫХОДНОЙ
хост-переменной устанавливается ORACLE и используется
хост-программой.
При обращении к хост-переменной в блоке PL/SQL перед ее именем
кодируется двоеточие, которое сообщает прекомпилятору, что это -
обращение к хост-переменной, а не к переменной PL/SQL или к
объекту базы данных.
Примеры
Следующая программа на Pro*C иллюстрирует использование
хост-переменных в блоке PL/SQL. Программа запрашивает у
пользователя имя сотрудника и передает его встроенному блоку
PL/SQL, который использует это имя в запросе по базе данных
ORACLE. Результаты запроса передаются обратно в хост-программу,
которая распечатывает их.
-- доступен на диске в файле EXAMP9
#include
EXEC SQL BEGIN DECLARE SECTION;
VARCHAR empname[11];
VARCHAR jobtype[9];
VARCHAR hired[9];
int salary;
int dept;
int worked_longer;
int higher_sal;
int total_in_dept;
VARCHAR uid[20];
VARCHAR pwd[20];
EXEC SQL END DECLARE SECTION;
EXEC SQL INCLUDE SQLCA;
main()
{
/* Set up userid and password */
strcpy (uid.arr,"scott");
uid.len = strlen(uid.arr);
strcpy (pwd.arr,"tiger");
pwd.len = strlen(pwd.arr);
printf("\n\n\tEmbedded PL/SQL Demo\n\n");
8-6 Руководство пользователя и справочник по PL/SQL
printf("Trying to connect...");
EXEC SQL WHENEVER SQLERROR GOTO errprint;
/* Connect to ORACLE */
EXEC SQL CONNECT :uid IDENTIFIED BY :pwd;
printf(" connected.\n");
for (;;) /* Loop infinitely */
{
printf("\n** Name of employee? ( to end) ");
gets(empname.arr); /* Get the name */
if (strlen(empname.arr) == 0) /* No name entered */
{
EXEC SQL COMMIT WORK RELEASE; /* so log off */
exit(0); /* and exit */
}
empname.len = strlen(empname.arr);
jobtype.len = 9;
hired.len = 9;
/* ---------------------------------- */
/* ----- Begin the PL/SQL Block ----- */
/* ---------------------------------- */
EXEC SQL EXECUTE
BEGIN
SELECT job, hiredate, sal, deptno
INTO :jobtype, :hired, :salary, :dept FROM emp
WHERE ename = UPPER(:empname);
/* Get number of people whose length of */
/* service is longer */
SELECT COUNT(*) INTO :worked_longer FROM emp
WHERE hiredate < :hired;
/* Get number of people with a higher salary */
SELECT COUNT(*) INTO :higher_sal FROM emp
WHERE sal > :salary;
/* Get number of people in the same department*/
SELECT COUNT(*) INTO :total_in_dept FROM emp
WHERE deptno = :dept;
END;
END-EXEC;
/* -------------------------------- */
/* ----- End the PL/SQL Block ----- */
/* -------------------------------- */
/* Properly terminate character strings */
/* returned by ORACLE */
jobtype.arr[jobtype.len] = '\0';
hired.arr[hired.len] = '\0';
Исполнительные окружения 8-7
/* Display all the information */
printf("\n%s's job is: %s\n", empname.arr,
jobtype.arr);
printf("Hired on: %s\n", hired.arr);
printf(" %d people have served longer\n",
worked_longer);
printf("Salary is: %d\n", salary);
printf(" %d people have a higher salary\n",
higher_sal);
printf("Department number is: %d\n", dept);
printf(" %d people in the department\n",
total_in_dept);
} /* End of loop */
errprint:
/* We end up here if an error occurs */
EXEC SQL WHENEVER SQLERROR CONTINUE;
printf("\n\n>>>>> Error during execution:\n");
/* Print ORACLE error message and log off database */
printf("%s\n", sqlca.sqlerrm.sqlerrmc);
EXEC SQL ROLLBACK RELEASE;
exit(1);
}
Заметьте, что хост-переменная empname устанавливается перед тем,
как войти в блок PL/SQL, а остальные хост-переменные (jobtype,
hired и т.д.) принимают значения внутри блока. Все необходимые
преобразования данных между типами данных ORACLE и стандартными
типами данных хост-языка выполняются автоматически.
8-8 Руководство пользователя и справочник по PL/SQL
Следующий пример на Pro*C показывает, как можно реализовать на
PL/SQL две банковские транзакции:
-- доступен на диске в файле EXAMP10
#include
EXEC SQL BEGIN DECLARE SECTION;
int acct, amount;
VARCHAR tran_type[10];
VARCHAR status[65];
VARCHAR uid[20];
VARCHAR pwd[20];
EXEC SQL END DECLARE SECTION;
EXEC SQL INCLUDE SQLCA;
main()
{
strcpy(uid.arr,"scott");
uid.len=strlen(uid.arr);
strcpy(pwd.arr,"tiger");
pwd.len=strlen(pwd.arr);
printf("\n\n\tEmbedded PL/SQL Demo\n\n");
printf("Trying to connect...");
EXEC SQL WHENEVER SQLERROR GOTO errprint;
EXEC SQL CONNECT :uid IDENTIFIED BY :pwd;
printf(" connected.\n");
for (;;) /* Loop infinitely */
{
printf("\n\n** What is the account number? (-1 to end)");
scanf("%d", &acct);
if (acct == -1) /* Need to disconnect from ORACLE */
{ /* and end program if acct is -1 */
EXEC SQL COMMIT RELEASE;
exit(0);
}
printf("\n What type of transaction? (C)redit or \
(D)ebit ");
scanf("%s", &tran_type.arr);
tran_type.len = 1; /* Only want the first character */
printf("\n What is the transaction amount? (Whole \
dollars only) ");
scanf("%d", &amount);
Исполнительные окружения 8-9
/* ---------------------------------- */
/* ----- Begin the PL/SQL block ----- */
/* ---------------------------------- */
EXEC SQL EXECUTE
DECLARE
old_bal NUMBER(11,2);
no_account EXCEPTION;
BEGIN
:tran_type := UPPER(:tran_type);
IF :tran_type = 'C' THEN -- credit the account
UPDATE accounts SET bal = bal + :amount
WHERE account_id = :acct;
IF SQL%ROWCOUNT = 0 THEN -- no rows affected
RAISE no_account;
ELSE
:status := 'Credit complete.';
END IF;
ELSIF :trantype = 'D' THEN -- debit the account
SELECT bal INTO old_bal FROM accounts
WHERE account_id = :acct;
IF old_bal >= :amount THEN -- there's enough
-- money
UPDATE accounts SET bal = bal - :amount
WHERE account_id = :acct;
:status := 'Debit complete.';
ELSE
:status := 'Not enough funds.';
END IF;
ELSE
:status := :tran_type || ' is not a legal
transaction.';
END IF;
COMMIT;
EXCEPTION
WHEN NO_DATA_FOUND OR no_account THEN
:status := 'Account does not exist.';
WHEN OTHERS THEN
:status := 'Error: ' || SQLERRM(SQLCODE);
END;
END-EXEC;
/* -------------------------------- */
/* ----- End the PL/SQL block ----- */
/* -------------------------------- */
8-10 Руководство пользователя и справочник по PL/SQL
status.arr[status.len] = '\0'; -- null terminate
printf("\n\n Status: %s", status.arr);
} /* End of loop */
errprint:
EXEC SQL WHENEVER SQLERROR CONTINUE;
printf("\n\n>>>> Error during execution:\n");
printf("%s\n",sqlca.sqlerrm.sqlerrmc);
EXEC SQL ROLLBACK RELEASE;
exit(1);
}
Использование индикаторных переменных
-------------------------------------
Вы можете ассоциировать с каждой хост-переменной необязательную
индикаторную переменную. ИНДИКАТОРНАЯ ПЕРЕМЕННАЯ - это
целочисленная переменная, которая отражает состояние значения
хост-переменной. Двумя главными назначениями индикаторных
переменных являются:
* присваивание пустых значений входным хост-переменным
* определение пустых или усеченных значений у выходных
хост-переменных
Для входной хост-переменной (т.е. переменной, устанавливаемой в
хост-языке и опрашиваемой в ORACLE) индикаторная переменная
может принимать следующие значения:
-1 ORACLE игнорирует значение хост-переменной и
присваивает пустое значение столбцу базы данных.
>= 0 ORACLE присваивает столбцу базы данных текущее
значение хост-переменной.
Для выходной хост-переменной (т.е. переменной, устанавливаемой в
ORACLE и опрашиваемой в хост-языке) индикаторная переменная
может принимать следующие значения:
-1 Столбец базы данных содержит NULL, поэтому значение
хост-переменной не определено.
0 Значение базы данных присвоено хост-переменной без
усечения.
>0 Значение из базы данных усечено при присваивании
хост-переменной. Положительное целое, возвращаемое
в индикаторной переменной, представляет собой
оригинальную длину строкового значения.
Индикаторная переменная должна быть определена в секции
деклараций (DECLARE SECTION) как двухбайтовая целочисленная
переменная. В предложениях SQL она должна задаваться через
двоеточие за именем соответствующей ей хост-переменной.
Хост-языку необходимы индикаторные переменные, потому что он не
может манипулировать пустыми значениями. Благодаря индикаторным
переменным встроенный блок PL/SQL может принимать от
хост-программы пустые значения и возвращать хост-программе
пустые или усеченные значения.
Исполнительные окружения 8-11
Следующий пример программы Pro*COBOL показывает, как при помощи
индикаторной переменной можно отслеживать значение
хост-переменной.
* начало встроенного блока PL/SQL
EXEC SQL EXECUTE
BEGIN
SELECT ENAME, COMM
INTO :EMP-ENAME, :COMMISSION:COMM-IND
FROM EMP
WHERE EMPNO = :EMP-NUMBER
-- остаток блока PL/SQL
...
END;
END-EXEC.
* конец встроенного блока PL/SQL
MOVE COMMISSION TO COMMISSION-OUT.
DISPLAY "Commission: "
WITH NO ADVANCING.
IF COMM-IND = -1
* если возвращенное значение индикаторной переменной равно -1,
* то значение ее выходной хост-переменной есть null
DISPLAY "N/A"
ELSE
DISPLAY COMMISSION-OUT.
Внутри блока PL/SQL индикаторная переменная должна задаваться
через двоеточие за именем соответствующей ей хост-переменной.
В блоке PL/SQL нельзя обращаться к индикаторной переменной
индивидуально. Более того, если вы обращаетесь к
хост-переменной вместе с ее индикаторной переменной, вы должны
использовать этот способ обращения всюду в данном блоке. Иными
словами, если вы пишете :переменная:индикатор, вы не можете
писать просто :переменная в другом месте этого же блока. Это
вытекает из того факта, что PL/SQL трактует запись
:переменная:индикатор как единственную переменную.
В следующем примере, так как индикаторная переменная job_ind
появляется вместе со своей хост-переменной job_host в
предложении SELECT, она должна кодироваться в том же сочетании и
в предложении IF:
EXEC SQL EXECUTE
BEGIN
...
SELECT ename, job
INTO :ename_host, :job_host:job_ind
FROM emp
WHERE empno = :empno_host;
...
IF :job_host:job_ind IS NULL THEN
RAISE status_unknown;
END IF;
...
END;
END-EXEC;
Хотя вы не можете обращаться непосредственно к индикаторным
переменным в блоке PL/SQL, PL/SQL проверяет значение этих
переменных при входе в блок и корректно устанавливает их
значения при выходе из блока.
8-12 Руководство пользователя и справочник по PL/SQL
Пустые значения
При входе в блок, если индикаторная переменная имеет значение
-1, PL/SQL автоматически присваивает значение NULL
соответствующей хост-переменной. При выходе из блока, если
хост-переменная имеет значение NULL, PL/SQL автоматически
присваивает индикаторной переменной значение -1.
В следующем примере, если ename_ind имеет значение -1 перед
входом в блок PL/SQL, то будет возбуждено исключение
name_missing.
EXEC SQL EXECUTE
BEGIN
...
IF :ename_host:ename_ind IS NULL THEN
RAISE name_missing;
END IF;
...
EXCEPTION
WHEN name_missing THEN
...
END;
END-EXEC;
Усеченные значения
PL/SQL не возбуждает исключения, когда присваивание значения
хост-переменной вызывает усечение. Однако, если при этом
используется индикаторная переменная, то PL/SQL сохраняет в ней
длину значения перед усечением.
В следующем примере хост-программа может выяснить, путем
проверки значения ename_ind, было ли усечение при присваивании
значения переменной ename_host:
EXEC SQL EXECUTE
DECLARE
new_ename CHAR(10);
...
BEGIN
...
:ename_host:ename_ind := new_ename;
...
END;
END_EXEC;
Исполнительные окружения 8-13
Использование псевдотипа VARCHAR
--------------------------------
Прекомпиляторы ORACLE позволяют использовать псевдотип VARCHAR
для объявления символьных строк переменной длины. (ПСЕВДОТИП -
это тип данных, не присущий хост-языку). Переменнпя VARCHAR
хранится как 2-байтовое поле длины, за которым следует
n-байтовая строка символов, где n может принимать значения от 1
до 65533. Например, прекомпилятор Pro*C расширяет декларацию
EXEC SQL BEGIN DECLARE SECTION
VARCHAR my_ename[10]
EXEC SQL END DECLARE SECTION;
в следующую структуру данных:
struct {
unsigned short len;
unsigned char arr[10];
} my_ename;
Чтобы получить длину переменной VARCHAR, вы просто обращаетесь к
ее полю длины (len). Вам не приходится использовать строковую
функцию или алгоритм подсчета символов.
ORACLE автоматически устанавливает поле длины каждой выходной
хост-переменной типа VARCHAR. Однако для того, чтобы
использовать ВЫХОДНУЮ хост-переменную VARCHAR в блоке PL/SQL, вы
должны инициализировать ее поле длины ПЕРЕД входом в блок
PL/SQL. Поэтому установите поле длины равным объявленному
(максимальному) значению, как показано в следующем примере на
Pro*C:
EXEC SQL BEGIN DECLARE SECTION;
int my_empno;
VARCHAR my_ename[10] /* declare variable-length string */
float my_sal;
...
EXEC SQL END DECLARE SECTION;
...
my_ename.len = 10; /* initialize length field */
EXEC SQL EXECUTE
BEGIN
SELECT ename, sal INTO :my_ename, :my_sal FROM emp
WHERE empno = :my_empno;
...
END;
END-EXEC;
...
Для дополнительной информации о псевдотипе VARCHAR обратитесь к
соответствующему Дополнению в руководстве по прекомпилятору
ORACLE.
8-14 Руководство пользователя и справочник по PL/SQL
Использование предложения DECLARE TABLE
---------------------------------------
Если ваш встроенный блок PL/SQL обращается к таблице, которой
еще не существует, то вы получите ошибку, когда прекомпилятор
попытается проверить ссылки на таблицы. Чтобы избежать такой
ошибки прекомпиляции, используйте предложение DECLARE TABLE,
чтобы сообщить прекомпилятору структуру таблицы. В следующем
примере таблица dept объявляется для прекомпилятора Pro*C:
EXEC SQL DECLARE dept TABLE
(deptno NUMBER(2),
dname CHAR(14),
loc CHAR(13));
Если вы используете DECLARE TABLE для определения таблицы, уже
существующей в базе данных, то прекомпилятор использует ваше
определение, игнорируя словарь данных базы данных ORACLE.
Заметьте, что предложение DECLARE TABLE нельзя использовать
внутри блока PL/SQL.
Использование опции SQLCHECK
Прекомпиляторы ORACLE могут помочь вам отладить программу,
проверяя синтаксис и семантику встроенных предложений SQL и
блоков PL/SQL. Вы управляете уровнем этого контроля,
специфицируя в командной строке опцию прекомпилятора SQLCHECK.
Вы можете специфицировать следующие значения для SQLCHECK:
* SEMANTICS
* SYNTAX
* NONE
Однако, если вы используете в вашей программе встроенные блоки
PL/SQL, то вы должны всегда специфицировать SQLCHECK=SEMANTICS.
При этом прекомпилятор проверяет синтаксис и семантику
предложений манипулирования данными SQL, типов данных
хост-переменных и блоков PL/SQL.
Информацию, необходимую для семантического контроля,
прекомпилятор берет из встроенных предложений DECLARE TABLE,
или, если вы специфицируете опцию командной строки USERID, путем
соединения с ORACLE и опроса словаря данных. Вам нет
необходимости соединяться с ORACLE, если все таблицы базы
данных, к которым обращаются ваши предложения SQL и блоки
PL/SQL, определены в предложениях DECLARE TABLE.
Исполнительные окружения 8-15
Использование хост-массивов с таблицами PL/SQL
----------------------------------------------
В блок PL/SQL можно передавать хост-массивы и индикаторные
массивы. Их можно индексировать переменной PL/SQL типа
BINARY_INTEGER или хост-переменной, совместимой с этим типом.
Обычно в PL/SQL передается весь массив, но вы можете
использовать предложение ARRAYLEN, обсуждаемое ниже, чтобы
специфицировать меньший размер массива.
Более того, с помощью вызова процедуры вы можете присвоить все
значения в хост-массиве строкам таблицы PL/SQL. Если индекс
массива меняется в диапазоне m..n, то диапазон индекса
соответствующей таблицы PL/SQL будет всегда 1..n-m+1. Например,
если индекс массива лежит в диапазоне 5..10, то диапазон индекса
соответствующей таблицы PL/SQL будет 1..6.
В следующем примере на Pro*C хост-массив с именем salary
передается в блок PL/SQL, который использует его в вызове
функции. Среди формальных параметров функции есть таблица
PL/SQL с именем num_tab. Вызов функции заставляет PL/SQL
присвоить все значения хост-массива, соответствующего
фактическому параметру salary, строкам формального параметра
num_tab.
#include
main()
{
/* Объявить хост-массив */
EXEC SQL BEGIN DECLARE SECTION;
...
float salary[100];
EXEC SQL END DECLARE SECTION;
/* Заполнить хост-массив */
...
EXEC SQL EXECUTE
DECLARE
TYPE NumTabTyp IS TABLE OF REAL
INDEX BY BINARY INTEGER:
mid_salary REAL;
n BINARY INTEGER := 100;
FUNCTION median (num_tab NumTabTyp, n INTEGER)
RETURN REAL IS
BEGIN
... -- вычислить среднее по таблице
END;
BEGIN
mid_salary := median(:salary, n); -- передать массив
...
END;
END-EXEC;
...
}
8-16 Руководство пользователя и справочник по PL/SQL
Вы можете также использовать вызов процедуры для того, чтобы
присвоить значения всех строк таблицы PL/SQL соответствующим
элементам хост-массива. См. пример программы 6 в приложении B.
На рис.8-1 показаны допустимые преобразования типов между
строками таблицы PL/SQL и элементами хост-массива. Например,
хост-массив типа LONG совместим с таблицей PL/SQL типа VARCHAR2,
LONG, RAW или LONG RAW. Стоит отметить, что он несовместим с
таблицей PL/SQL типа CHAR.
Рис.8-1
Допустимые преобразования типов
Хост-массив
г=======T====T====T========T======T====T=====T========¬
¦CHAR ¦DATE¦LONG¦LONG RAW¦NUMBER¦RAW ¦ROWID¦VARCHAR2¦
г========+=======+====+====+========+======+====+=====+========¦
Таблица ¦CHAR ¦ Да ¦ ¦ ¦ ¦ ¦ ¦ ¦ ¦
PL/SQL ¦--------+-------+----+----+--------+------+----+-----+--------¦
¦DATE ¦ ¦ Да ¦ ¦ ¦ ¦ ¦ ¦ ¦
¦--------+-------+----+----+--------+------+----+-----+--------¦
¦LONG ¦ ¦ ¦ Да ¦ Да ¦ ¦ Да ¦ ¦ Да ¦
¦--------+-------+----+----+--------+------+----+-----+--------¦
¦LONG RAW¦ ¦ ¦ Да ¦ Да ¦ ¦ Да ¦ ¦ Да ¦
¦--------+-------+----+----+--------+------+----+-----+--------¦
¦NUMBER ¦ ¦ ¦ ¦ ¦ Да ¦ ¦ ¦ ¦
¦--------+-------+----+----+--------+------+----+-----+--------¦
¦RAW ¦ ¦ ¦ Да ¦ Да ¦ ¦ Да ¦ ¦ Да ¦
¦--------+-------+----+----+--------+------+----+-----+--------¦
¦ROWID ¦ ¦ ¦ ¦ ¦ ¦ ¦ Да ¦ ¦
¦--------+-------+----+----+--------+------+----+-----+--------¦
¦VARCHAR2¦ ¦ ¦ Да ¦ Да ¦ ¦ Да ¦ ¦ Да ¦
L========¦=======¦====¦====¦========¦======¦====¦=====¦========-
Прекомпиляторы ORACLE не проверяют вашего использования
хост-массивов. В частности, не выполняется никакой проверки
индекса на диапазон.
Исполнительные окружения 8-17
Предложение ARRAYLEN
Предположим, что вы должны передать входной хост-массив в блок
PL/SQL для обработки. По умолчанию при привязывании такого
массива прекомпилятор ORACLE использует его объявленные размеры.
Однако, если вы не хотите обрабатывать весь массив, выы можете
использовать предложение ARRAYLEN, чтобы специфицировать меньший
размер. ARRAYLEN ассоциирует хост-массив с хост-переменной, в
которой хранится значение размерности массива.
Повторим последний пример, но на этот раз используем ARRAYLEN,
чтобы перекрыть умалчиваемый размер хост-массива salary:
#include
main()
{
/* Объявить хост-массив */
EXEC SQL BEGIN DECLARE SECTION;
...
float salary[100];
int my_dim;
EXEC SQL ARRAYLEN salary (my_dim);
EXEC SQL END DECLARE SECTION;
/* Заполнить хост-массив */
...
/* Установить меньший размер массива */
my_dim = 25;
EXEC SQL EXECUTE
DECLARE
TYPE NumTabTyp IS TABLE OF REAL
INDEX BY BINARY INTEGER:
mid_salary REAL;
n BINARY INTEGER := 100;
FUNCTION median (num_tab NumTabTyp, n INTEGER)
RETURN REAL IS
BEGIN
... -- вычислить среднее по таблице
END;
BEGIN
mid_salary := median(:salary, :my_dim); --передать массив
...
END;
END-EXEC;
...
}
Лишь 25 элементов массива передаются блоку PL/SQL, потому что
предложение ARRAYLEN ассоциировало размер массива с переменной
my_dim, а ее текущее значение равно 25. Как следствие, когда
блок PL/SQL передается в ORACLE для исполнения, пересылается
массив меньшего размера. Это экономит время, а в сетевом
окружении уменьшает трафик.
8-18 Руководство пользователя и справочник по PL/SQL
Следующий пример на Pro*C показывает, как присваиваются значения
элементам хост-массива и как адресовать индивидуальные элементы
хост-массива:
#include
...
main()
{
/* Объявить хост-массив и индикаторный массив */
EXEC SQL BEGIN DECLARE SECTION;
float sal [100];
float comm [100];
short icomm [100];
int i = 50;
EXEC SQL END DECLARE SECTION;
EXEC SQL EXECUTE
DECLARE
TYPE SalTabTyp IS TABLE OF emp.sal%TYPE
INDEX BY BINARY INTEGER:
TYPE CommTabTyp IS TABLE OF emp.comm%TYPE
INDEX BY BINARY INTEGER:
sal_tab SalTabTyp;
comm_tab CommTabTyp;
j BINARY INTEGER := 0;
...
BEGIN
/* Загрузить оклады и комиссионные в таблицы PL/SQL */
FOR emprec IN (SELECT sal, comm FROM emp) LOOP
j := j + 1;
sal_tab(j) := emprec.sal;
comm_tab(j) := emprec.comm;
END LOOP;
/* Обработать таблицы PL/SQL */
...
/* Присвоить значения строк хост-массивам */
WHILE :i > 0 LOOP
:sal(:i) := sal_tab(:i);
/* если comm пусто, icomm получит значение -1 */
:comm:icomm(:i) := comm_tab(:i);
:i := :i - 1;
END LOOP;
END;
END-EXEC;
...
}
Исполнительные окружения 8-19
Использование динамического SQL
-------------------------------
В отличие от статического SQL, предложения динамического SQL не
встраиваются в вашу исходную программу. Вместо этого они
хранятся в символьных строках, которые ваша программа вводит
(или генерирует) во время выполнения. Такие строки могут
вводиться интерактивно или считываться из файла.
Прекомпиляторы ORACLE рассматривают блок PL/SQL как одно
предложение SQL. Поэтому, как и предложение SQL, блок PL/SQL
может быть сохранен в строковой хост-переменной для последующей
обработки командами динамического SQL.
Не забывайте, что в таком блоке PL/SQL нельзя использовать
однострочные комментарии (об этом говорилось в главе 2).
Следующие подразделы кратко иллюстрируют использование PL/SQL с
динамическим SQL при помощи методов 1, 2 и 4. Для
дополнительной информации обратитесь к документу Programmer's
Guide to the ORACLE Precompilers.
Метод 1
Если ваш блок PL/SQL не содержит хост-переменных, вы можете
использовать метод 1, чтобы выполнить строку PL/SQL обычным
способом. В следующем примере на Pro*C вы запрашиваете у
пользователя текст блока PL/SQL, сохраняете его в строковой
хост-переменной с именем user_block, а затем выполняете его:
main()
{
printf("\nEnter a PL/SQL block: ");
scanf("%s", user_block);
EXEC SQL EXECUTE IMMEDIATE :user_block;
...
Если вы сохраняете блок PL/SQL в строковой переменной, опустите
фразу EXEC SQL EXECUTE, фразу END-EXEC и терминатор предложения.
8-20 Руководство пользователя и справочник по PL/SQL
Метод 2
Если ваш блок PL/SQL содержит заранее известное число входных и
выходных хост-переменных, вы можете использовать метод 2, чтобы
сначала подготовить, а затем выполнить строку PL/SQL. В примере
на Pro*C ниже, вы запрашиваете блок PL/SQL у пользователя,
сохраняете его в строковой переменной user_block, затем
подготавливаете и выполняете ее. На этот раз блок PL/SQL
использует хост-переменную с именем my_empno.
main()
{
printf("\nEnter a PL/SQL block: ");
scanf("%s", user_block);
EXEC SQL PREPARE my_block FROM :user_block;
EXEC SQL EXECUTE my_block USING :my_empno;
...
Заметим, что my_block - это идентификатор, используемый
прекомпилятором, но не хост-переменная и не просто переменная.
Прекомпилятор рассматривает все хост-переменные PL/SQL как
ВХОДНЫЕ, независимо от того, как они используются в блоке
PL/SQL. Поэтому вы должны указать в фразе USING ВСЕ
хост-переменные.
Когда строка PL/SQL исполняется, хост-переменные из фразы USING
замещают соответствующие шаблоны в строке, подготовленной через
PREPARE. Хотя прекомпилятор трактует все хост-переменные PL/SQL
как входные, значения присваиваются корректно. Входные значения
(из программы) присваиваются входным хост-переменным, а выходные
значения (например, из столбцов таблиц) присваиваются выходным
хост-переменным.
Метод 4
Если ваш блок PL/SQL содержит заранее неизвестное число входных
или выходных хост-переменных, вы должны использовать метод 4.
Чтобы использовать метод 4, вы организуете "связной дескриптор"
для всех входных и выходных хост-переменных. Выполнение
предложения DESCRIBE BIND VARIABLES сохраняет в связном
дескрипторе информацию о входных и выходных хост-переменных.
Для полного обсуждения метода 4 обратитесь к соответствующему
дополнению к документу Programmer's Guide to the ORACLE
Precompilers.
Исполнительные окружения 8-21
Имитация динамического SQL
--------------------------
Без динамического SQL вы не можете использовать переменные
PL/SQL в запросе для спецификации имен выбираемых столбцов. В
следующем примере, если в таблице EMP есть столбец с именем
COLX, то переменной my_ename будет присвоено значение этого
столбца, а не столбца ename, как хотелось. Если же в таблице
EMP нет столбца с именем COLX, то переменной my_ename будет
присвоено значение переменной PL/SQL colx (т.е. символьная
строка 'ename', а не значение столбца ename).
DECLARE
colx CHAR(10);
my_ename CHAR(10);
...
BEGIN
colx := 'ename';
SELECT colx INTO my_ename FROM emp WHERE ...
...
END;
Однако, в этом случае, вы можете имитировать динамический SQL,
используя функцию DECODE. В следующем примере возвращаемые
данные зависят от значения переменной my_column, задающей имя
столбца:
DECLARE
my_column CHAR(10);
my_data emp.ename%TYPE;
BEGIN
...
my_column := 'hiredate';
...
SELECT DECODE(my_column, 'ename', ename,
'hiredate', TO_CHAR(hiredate, 'ddmmyy'), 'empno', empno)
INTO my_data FROM emp WHERE ... ;
END;
Данные, которые возвращает DECODE, всегда преобразуются к типу
данных первого результирующего выражения. В примере выше,
первое результирующее выражение - ename, и, так как ename имеет
тип CHAR, ВСЕ возвращаемые данные будут приводиться к типу CHAR.
Даже если my_column задает числовой столбец (как empno), его
значение будет неявно преобразовываться в символьную строку.
Таким образом, объявление my_data как emp.ename%TYPE правильно.
Вы можете использовать этот способ и в любой другой среде.
Например, он будет работать в SQL*Forms, SQL*Menu и SQL*Plus.
8-22 Руководство пользователя и справочник по PL/SQL
Вызов хранимых подпрограмм
--------------------------
Чтобы вызвать хранимую подпрограмму из хост-программы, вы должны
использовать анонимный блок PL/SQL. В следующем примере
вызывается независимая процедура create_dept:
EXEC SQL EXECUTE
BEGIN
create_dept(:number, :name, :location);
END;
END-EXEC;
Заметьте, что фактические параметры number, name и location
являются хост-переменными.
В следующем примере процедура creatd_dept является частью пакета
с именем emp_actions, и вы должны использовать квалифицированную
ссылку при вызове:
EXEC SQL EXECUTE
BEGIN
emp_actions.create_dept(:number, :name, :location);
END;
END-EXEC;
----------------
Среда OCI
В интерфейсе вызовов ORACLE (OCI) блоки PL/SQL обрабатываются
так же, как и предложения SQL, за исключением одного небольшого,
но важного отличия. В блоке PL/SQL вы должны использовать вызов
OBNDRV, а не ODEFIN, для связывания всех переменных в
предложении SQL или PL/SQL. Это касается как входных, так и
выходных переменных. Вызов ODEFIN НЕ поддерживается для блоков
PL/SQL.
В PL/SQL все запросы должны иметь фразу INTO, содержащую
заместители (хост-переменные и/или переменные PL/SQL) для всех
выбираемых элементов списка SELECT. Например, следующее
предложение SELECT некорректно в блоке PL/SQL:
SELECT ename, sal FROM emp;
Вместо этого оно должно кодироваться следующим образом:
SELECT ename, sal INTO :my_ename, :my_sal FROM emp;
Здесь my_ename и my_sal - заместители, соответствующие
выбираемым столбцам ENAME и SAL из списка SELECT. Вы должны
выполнить привязку этих заместителей, используя вызов OBNDRV.
Кроме того, все заместители, используемые в блоках PL/SQL,
должны быть поименованы (как my_ename в примере выше).
Нумерованные заместители, такие как 10, и соответствующий вызов
OBNDRN, ПОКА НЕ ПОДДЕРЖИВАЮТСЯ для блоков PL/SQL.
Исполнительные окружения 8-23
Полный пример
Следующая программа, написанная на C, демонстрирует пример
реализации двух банковских транзакций на PL/SQL. Листинги
включаемых файлов ocidfn.h и ocidem.h приведены в документе
Programmer's Guide to the ORACLE Call Interfaces.
#include
#include
#include
#define ANSI
#include "ocidfn.h"
#include "ocidem.h"
cda_def cda;
lda_def lda;
char hda[256];
char sqlstm[2048];
void error_handler(struct cda_def *);
main()
{
int acct_number;
char trans_type[1];
float trans_amt;
char status[80];
if (orlon(&lda, hda, "scott/tiger", -1, (char *) 0, -1, -1))
{
printf("Connect failed.\n");
exit(EXIT_FAILURE);
}
if (oopen(&cda, &lda, (char *) 0, -1, -1, (char *) 0, -1))
{
printf("Error opening cursor. Exiting...\n");
exit(EXIT_FAILURE);
}
printf("Connected to ORACLE.\n");
8-24 Руководство пользователя и справочник по PL/SQL
/* Construct a PL/SQL block. */
strcpy(sqlstm, "DECLARE\
old_bal number(9,2);\
err_msg CHAR(70);\
nonexistent EXCEPTION;\
BEGIN\
:xtrans_type := UPPER(:xtrans_type);\
IF :xtrans_type = 'C' THEN\
UPDATE ACCTS SET BAL = BAL + :xtrans_amt\
WHERE ACCTID = :xacct_number;\
IF SQL%ROWCOUNT = 0 THEN\
RAISE nonexistent;\
ELSE\
:xstatus := 'Credit applied';\
END IF;\
ELSIF :xtrans_type = 'D' THEN\
SELECT BAL INTO old_bal FROM accts\
WHERE ACCTID = :xacct_number;\
IF old_bal = :xtrans_amt THEN\
UPDATE ACCTS SET BAL = BAL - :xtrans_amt\
WHERE ACCTID = :xacct_number;\
:xstatus := 'Debit applied';\
ELSE\
:xstatus := 'Insufficient funds';\
END IF;\
ELSE\
:xstatus := 'Invalid type: ' || :xtrans_type;\
END IF;\
COMMIT;\
EXCEPTION\
WHEN NO_DATA_FOUND OR nonexistent THEN\
:xstatus := 'Nonexistent account';\
WHEN OTHERS THEN\
err_msg := SUBSTR(SQLERRM, 1, 70);\
:xstatus := 'Error: ' || err_msg;\
END;");
Исполнительные окружения 8-25
/* Parse the PL/SQL block. */
if (oparse(&cda, sqlstm, -1, 0, 2))
{
error_handler(&cda);
exit(EXIT_FAILURE);
}
/* Bind the status variable. */
if (obndrv(&cda,
":xstatus",
-1,
status,
70,
5,
-1,
(char *) 0,
(char *) 0, -1, -1))
{
error_handler(&cda);
exit(EXIT_FAILURE);
}
/* Bind the transaction type variable. */
if (obndrv(&cda,
":xtrans_type",
-1,
trans_type,
1,
1
-1,
(char *) 0,
(char *) 0, -1, -1))
{
error_handler(&cda);
exit(EXIT_FAILURE);
}
/* Bind the account number. */
if (obndrv(&cda,
":xacct_number",
-1,
&acct_number,
sizeof(int),
3
-1,
(char *) 0,
(char *) 0, -1, -1))
{
8-26 Руководство пользователя и справочник по PL/SQL
error_handler(&cda);
exit(EXIT_FAILURE);
}
/* Bind the transaction amount variable. */
if (obndrv(&cda,
":xtrans_amt",
-1,
&trans_amt,
sizeof(float),
4
-1,
(char *) 0,
(char *) 0, -1, -1))
{
error_handler(&cda);
exit(EXIT_FAILURE);
}
for (;;)
{
printf(\nAccount number: ");
scanf("%d", &acct_number);
fflush(stdin);
if (acct_number == 0)
break;
printf("Transaction type (D or C): ");
scanf("%c", trans_type);
fflush(stdin);
printf("Transaction amount: ");
scanf("%f", &trans_amt);
fflush(stdin);
/* Execute the block. */
if (oexec(&cda))
error_handler(&cda);
printf("%s\n", status);
}
printf("Have a good day!\n");
exit(EXIT_SUCCESS);
}
Исполнительные окружения 8-27
void
error_handler(struct cda_def *cda)
{
char msg[512];
int n;
printf("\n>>> ORACLE Error\n");
n = oerhms(&lda, cda-rc, msg, sizeof(msg));
printf("%.*s\n", n, msg);
printf("Processing OCI function %s\n\n",
oci_func_tab[cda-fc]);
}
Вызов хранимых подпрограмм
--------------------------
Чтобы вызвать хранимую подпрограмму из программы OCI, вы должны
использовать анонимный блок PL/SQL. В следующем примере на C
независимая процедура raise_salary копируется в строковую
переменную plsql_block:
strcpy(plsql_block,"BEGIN raise_salary(:emp_id,:amount); END;");
После этого строка PL/SQL может быть обвязана и выполнена, как и
обычное предложение SQL.
8-28 Руководство пользователя и справочник по PL/SQL