ПРИЛОЖЕНИЕ B
----------------------------------------------------------------
ПРИМЕРЫ ПРОГРАММ
В этом приложении приведено несколько типовых программ PL/SQL,
чтобы помочь вам писать ваши собственные программы. Эти примеры
иллюстрируют следующие важные концепции и возможности PL/SQL:
* циклы FOR
* курсоры
* правила сферы
* пакетная обработка транзакций
* встроенный PL/SQL
* вызов хранимой процедуры
Примеры программ B-1
----------------
Примеры программ, приведенные в этом приложении, а также
множество других примеров, разбросанных по этому руководству,
поставляются с дистрибуцией. Такие примеры сопровождаются
комментарием вида
-- доступен на диске в файле имя_файла
Следующая таблица показывает имена этих файлов и места в данном
документе, где приводится их текст. Однако точные имена и
местоположения файлов зависят от системы.
Ссылка в руководстве Имя файла
-----------------------------------------
Глава 1, стр. 2 EXAMP1
Глава 1, стр. 7 EXAMP2
Глава 1, стр. 8 EXAMP3
Глава 2, стр. 25 EXAMP4
Глава 4, стр. 21 EXAMP5
Глава 4, стр. 22 EXAMP6
Глава 4, стр. 27 EXAMP7
Глава 4, стр. 29 EXAMP8
Глава 8, стр. 6 EXAMP9
Глава 8, стр. 9 EXAMP10
Глава 9, стр. 16 EXAMP11
Глава 9, стр. 47 EXAMP12
Глава 9, стр. 76 EXAMP13
Глава 9, стр. 103 EXAMP14
Приложение B, стр. 11 SAMPLE1
Приложение B, стр. 13 SAMPLE2
Приложение B, стр. 14 SAMPLE3
Приложение B, стр. 17 SAMPLE4
Приложение B, стр. 20 SAMPLE5
Приложение B, стр. 25 SAMPLE6
Некоторые примеры запускаются интерактивно из SQL*Plus; другие
запускаются из программ Pro*C. Вы можете экспериментировать с
этими примерами под любым учетным именем. Например, примеры на
Pro*C ожидают, что вы будете использовать имя SCOTT/TIGER.
B-2 Руководство пользователя и справочник по PL/SQL
Прежде чем пытаться выполнять примеры, вы должны создать
несколько таблиц в базе данных, а затем загрузить эти таблицы
данными. Это осуществляется запуском двух скриптов SQL*Plus,
EXAMPBLD и EXAMPLOD, поставляемых с PL/SQL и находящихся в
дистрибуции PL/SQL. Относительно точного местоположения этих
скриптов справьтесь в руководстве по инсталляции для вашей
системы.
Создание таблиц
---------------
Ниже приведен листинг скрипта SQL*Plus с именем EXAMPBLD. Этот
скрипт содержит предложения CREATE, которые необходимы для
создания таблиц, используемых во всех примерах настоящего
руководства. Чтобы выполнить этот скрипт, вызовите SQL*Plus, а
затем выдайте следующую команду:
SQL> START EXAMPBLD
Скрипт EXAMPBLD
set compatibility V6
/
drop table accounts
/
create table accounts(
account_id number(4) not null,
bal number(11,2))
/
create unique index accounts_index on accounts (account_id)
/
drop table action
/
create table action(
account_id number(4) not null,
oper_type char(1) not null,
new_value number(11,2),
status char(45),
time_tag date not null)
/
drop table bins
/
create table bins(
bin_num number(2) not null,
part_num number(4),
amt_in_bin number(4))
/
drop table data_table
/
Примеры программ B-3
create table data_table(
exper_num number(2),
n1 number(5),
n2 number(5),
n3 number(5))
/
drop table emp
/
create table emp(
empno number(4) not null,
ename char(10),
job char(9),
mgr number(4),
hiredate date,
sal number(7,2),
comm number(7,2),
deptno number(2))
/
drop table inventory
/
create table inventory(
prod_id number(5) not null,
product char(15),
quantity number(5))
/
drop table journal
/
create table journal(
account_id number(4) not null,
action char(45) not null,
amount number(11,2),
date_tag date not null)
/
drop table num1_tab
/
create table num1_tab(
sequence number(3) not null,
num number(4))
/
drop table num2_tab
/
create table num2_tab(
sequence number(3) not null,
num number(4))
/
drop table purchase_record
/
B-4 Руководство пользователя и справочник по PL/SQL
create table purchase_record(
mesg char(45),
purch_date date)
/
drop table ratio
/
create table ratio(
sample_id number(3) not null,
ratio number)
/
drop table result_table
/
create table result_table(
sample_id number(3) not null,
x number,
y number)
/
drop table sum_tab
/
create table sum_tab(
sequence number(3) not null,
sum number(5))
/
drop table temp
/
create table temp(
num_col1 number(9,4),
num_col2 number(9,4),
char_col char(55))
/
create or replace package personnel as
type charArrayTyp is table of varchar2(10)
index by binary_integer;
type numArrayTyp is table of float
index by binary_integer;
procedure get_employees(
dept_number in integer,
batch_size in integer,
found in out integer,
done_fetch out integer,
emp_name out charArrayTyp,
job_title out charArrayTyp,
salary out numArrayTyp);
end personnel;
/
Примеры программ B-5
create or replace package body personnel as
cursor get_emp (dept_number integer) is
select ename, job, sal from emp
where deptno = dept_number;
procedure get_employees(
dept_number in integer,
batch_size in integer,
found in out integer,
done_fetch out integer,
emp_name out charArrayTyp,
job_title out charArrayTyp,
salary out numArrayTyp) is
begin
if not get_emp%isopen then
open get_emp(dept_number);
end if;
done_fetch := 0;
found := 0;
for i in 1..batch_size loop
fetch get_emp into emp_name(i),
job_title(i), salary(i);
if get_emp%notfound then
close get_emp;
done_fetch := 1;
exit;
else
found := found + 1;
end if;
end loop;
end get_employees;
end personnel;
/
Загрузка данных
---------------
Ниже приведен листинг скрипта SQL*Plus с именем EXAMPLOD. Этот
файл содержит предложения INSERT, которые необходимы для
загрузки (или сброса) таблиц, используемых во всех примерах
настоящего руководства. Чтобы выполнить этот скрипт, вызовите
SQL*Plus под тем же учетным именем, под которым вы выполняли
EXAMPBLD, а затем выдайте следующую команду:
SQL> START EXAMPLOD
Скрипт EXAMPLOD
delete from accounts
/
insert into accounts values (1,1000.00)
/
insert into accounts values (2,2000.00)
/
B-6 Руководство пользователя и справочник по PL/SQL
insert into accounts values (3,1500.00)
/
insert into accounts values (4,6500.00)
/
insert into accounts values (5,500.00)
/
delete from action
/
insert into action values
(3,'u',599,null,sysdate)
/
insert into action values
(6,'i',20099,null, sysdate)
/
insert into action values
(5,'d',null,null, sysdate)
/
insert into action values
(7,'u',1599,null, sysdate)
/
insert into action values
(1,'i',399,null,sysdate)
/
insert into action values
(9,'d',null,null,sysdate)
/
insert into action values
(10,'x',null,null,sysdate)
/
delete from bins
/
insert into bins values (1, 5469, 650)
/
insert into bins values (2, 7243, 450)
/
insert into bins values (3, 5469, 120)
/
insert into bins values (4, 5469, 300)
/
insert into bins values (5, 6085, 415)
/
insert into bins values (6, 5469, 280)
/
insert into bins values (7, 8159, 619)
/
delete from data_table
/
Примеры программ B-7
insert into data_table values
(1, 10, 167, 17)
/
insert into data_table values
(1, 16, 223, 35)
/
insert into data_table values
(2, 34, 547, 2)
/
insert into data_table values
(3, 23, 318, 11)
/
insert into data_table values
(1, 17, 266, 15)
/
insert into data_table values
(1, 20, 117, 9)
/
delete from emp
/
insert into emp values
(7369,'SMITH','CLERK',7902,TO_DATE('12-17-80','MM-DD-YY'),
800,NULL,20)
/ insert into emp values
(7499,'ALLEN','SALESMAN',7698,TO_DATE('02-20-81',
'MM-DD-YY'),1600,300,30)
/
insert into emp values
(7521,'WARD','SALESMAN',7698,TO_DATE('02-22-81',
'MM-DD-YY'),1250,500,30)
/
insert into emp values
(7566,'JONES','MANAGER',7839,TO_DATE('04-02-81',
'MM-DD-YY'),2975,NULL,20)
/
insert into emp values
(7654,'MARTIN','SALESMAN',7698,TO_DATE('09-28-81',
'MM-DD-YY'),1250,1400,30)
/
insert into emp values
(7698,'BLAKE','MANAGER',7839,TO_DATE('05-1-81',
'MM-DD-YY'),2850,NULL,30)
/
B-8 Руководство пользователя и справочник по PL/SQL
insert into emp values
(7782,'CLARK','MANAGER',7839,TO_DATE('06-9-81',
'MM-DD-YY'),2450,NULL,10)
/
insert into emp values
(7788,'SCOTT','ANALYST',7566,SYSDATE-85,3000,NULL,20)
/
insert into emp values
(7839,'KING','PRESIDENT',NULL,TO_DATE('11-17-81',
'MM-DD-YY'),5000,NULL,10)
/
insert into emp values
(7844,'TURNER','SALESMAN',7698,TO_DATE('09-8-81',
'MM-DD-YY'),1500,0,30)
/
insert into emp values
(7876,'ADAMS','CLERK',7788,SYSDATE-51,1100,NULL,20)
/
insert into emp values
(7900,'JAMES','CLERK',7698,TO_DATE('12-3-81',
'MM-DD-YY'),950,NULL,30)
/
insert into emp values
(7902,'FORD','ANALYST',7566,TO_DATE('12-3-81',
'MM-DD-YY'),3000,NULL,20)
/
insert into emp values
(7934,'MILLER','CLERK',7782,TO_DATE('01-23-82',
'MM-DD-YY'),1300,NULL,10)
/
delete from inventory
/
insert into inventory values
(1234, 'TENNIS RACQUET', 3)
/
insert into inventory values
(8159, 'GOLF CLUB', 4)
/
insert into inventory values
(2741, 'SOCCER BALL', 2)
/
delete from journal
/
delete from num1_tab
/
insert into num1_tab values (1, 5)
/
Примеры программ B-9
insert into num1_tab values (2, 7)
/
insert into num1_tab values (3, 4)
/
insert into num1_tab values (4, 9)
/
delete from num2_tab
/
insert into num2_tab values (1, 15)
/
insert into num2_tab values (2, 19)
/
insert into num2_tab values (3, 27)
/
delete from purchase_record
/
delete from ratio
/
delete from result_table
/
insert into result_table values (130, 70, 87)
/
insert into result_table values (131, 77, 194)
/
insert into result_table values (132, 73, 0)
/
insert into result_table values (133, 81, 98)
/
delete from sum_tab
/
delete from temp
/
commit
/
B-10 Руководство пользователя и справочник по PL/SQL
----------------
Пример 1. Цикл FOR
В следующем примере простой цикл FOR используется для вставки
десяти строк в таблицу. Вставляются значения индекса цикла,
переменной-счетчика и одной из двух символьных строк, в
зависимости от четности или нечетности текущего значения
индексной переменной цикла.
Входная таблица
Отсутствует.
Блок PL/SQL
-- доступен на диске в файле SAMPLE1
DECLARE
x NUMBER := 100;
BEGIN
FOR i IN 1..10 LOOP
IF MOD(i,2) = 0 THEN -- i is even
INSERT INTO temp VALUES (i, x, 'i is even');
ELSE
INSERT INTO temp VALUES (i, x, 'i is odd');
END IF;
x := x + 100;
END LOOP;
COMMIT;
END;
Результирующая таблица
SQL> SELECT * FROM temp ORDER BY col1;
COL1 COL2 CHAR_COL
----- ------- ---------
1 100 i is odd
2 200 i is even
3 300 i is odd
4 400 i is even
5 500 i is odd
6 600 i is even
7 700 i is odd
8 800 i is even
9 900 i is odd
10 1000 i is even
10 records selected.
Примеры программ B-11
----------------
Пример 2. Курсоры
В следующем примере курсор используется для извлечения пяти
самых высокооплачиваемых сотрудников из таблицы emp.
Входная таблица
SQL> SELECT ename, empno, sal FROM emp ORDER BY sal DESC;
ENAME EMPNO SAL
---------- ----------- -------
KING 7839 5000
SCOTT 7788 3000
FORD 7902 3000
JONES 7566 2975
BLAKE 7698 2850
CLARK 7782 2450
ALLEN 7499 1600
TURNER 7844 1500
MILLER 7934 1300
WARD 7521 1250
MARTIN 7654 1250
ADAMS 7876 1100
JAMES 7900 950
SMITH 7369 800
14 records selected.
B-12 Руководство пользователя и справочник по PL/SQL
Блок PL/SQL
--доступен на диске в файле SAMPLE2
DECLARE
CURSOR c1 is
SELECT ename, empno, sal FROM emp
ORDER BY sal DESC; -- start with highest paid employee
my_ename CHAR(10);
my_empno NUMBER(4);
my_sal NUMBER(7,2);
BEGIN
OPEN c1;
FOR i IN 1..5 LOOP
FETCH c1 INTO my_ename, my_empno, my_sal;
EXIT WHEN c1%NOTFOUND; /* in case the number requested
is more than the total number
of employees */
INSERT INTO temp VALUES (my_sal, my_empno, my_ename);
COMMIT;
END LOOP;
CLOSE c1;
END;
Результирующая таблица
SQL> SELECT * FROM temp ORDER BY col1 DESC;
COL1 COL2 CHAR_COL
----- ------- ---------
5000 7839 KING
3000 7902 FORD
3000 7788 SCOTT
2975 7566 JONES
2850 7698 BLAKE
Примеры программ B-13
----------------
Пример 3. Правила сферы
Следующий пример иллюстрирует структуру блоков и правила сферы.
Внешний блок объявляет две переменные с именами x и counter, и
четыре раза выполняет цикл. Подблок внутри этого цикла также
объявляет переменную с именем x. Значения, вставляемые в
таблицу TEMP, показывают, что эти две переменные x действительно
различны.
Входная таблица
Не используется.
Блок PL/SQL
-- доступен на диске в файле SAMPLE3
DECLARE
x NUMBER := 0;
counter NUMBER := 0;
BEGIN
FOR i IN 1..4 LOOP
x := x + 1000;
counter := counter + 1;
INSERT INTO temp VALUES (x, counter, 'outer loop');
/* start an inner block */
DECLARE
x NUMBER := 0; -- this is a local version of x
BEGIN
FOR i IN 1..4 LOOP
x := x + 1; -- this increments the local x
counter := counter + 1;
INSERT INTO temp VALUES(x,counter,'inner loop');
END LOOP;
END;
END LOOP;
COMMIT;
END;
B-14 Руководство пользователя и справочник по PL/SQL
Результирующая таблица
SQL> SELECT * FROM temp ORDER BY col2;
COL1 COL2 CHAR_COL
------- -------- -------------
1000 1 outer loop
1 2 inner loop
2 3 inner loop
3 4 inner loop
4 5 inner loop
2000 6 outer loop
1 7 inner loop
2 8 inner loop
3 9 inner loop
4 10 inner loop
3000 11 outer loop
1 12 inner loop
2 13 inner loop
3 14 inner loop
4 15 inner loop
4000 16 outer loop
1 17 inner loop
2 18 inner loop
3 19 inner loop
4 20 inner loop
20 records selected.
Примеры программ B-15
----------------
Пример 4. Пакетная обработка транзакций
В следующем примере таблица accounts модифицируется согласно
инструкциям, содержащимся в таблице action. Каждая строка в
таблице action содержит: номер счета, требуемое действие (I, U
или D для вставки, обновления или удаления), количество, на
которое следует изменить счет (если действие не является
удалением счета), и отметку времени, которая служит для
упорядочения транзакций.
При выполнении вставки, если счет уже существует, вместо вставки
осуществляется обновление счета. При обновлении, если счет не
существует, он создается. При удалении, если счет не
существует, никаких действий не выполняется.
Входные таблицы
SQL> SELECT * FROM accounts ORDER BY account_id;
ACCOUNT_ID BAL
---------- ---------
1 1000
2 2000
3 1500
4 6500
5 500
SQL> SELECT * FROM action ORDER BY time_tag;
ACCOUNT_ID O NEW_VALUE STATUS TIME_TAG
---------- - --------- ------------------------- ---------
3 u 599 18-NOV-88
6 i 20099 18-NOV-88
5 d 18-NOV-88
7 u 1599 18-NOV-88
1 i 399 18-NOV-88
9 d 18-NOV-88
10 x 18-NOV-88
7 records selected.
B-16 Руководство пользователя и справочник по PL/SQL
Блок PL/SQL
-- доступен на диске в файле SAMPLE4
DECLARE
CURSOR c1 IS
SELECT account_id, oper_type, new_value FROM action
ORDER BY time_tag
FOR UPDATE OF status;
BEGIN
FOR acct IN c1 LOOP -- process each row one at a time
acct.oper_type := upper(acct.oper_type);
/*----------------------------------------*
* Process an UPDATE. If the account to *
* be updated doesn't exist, create a new *
* account. *
*----------------------------------------*/
IF acct.oper_type = 'U' THEN
UPDATE accounts SET bal = acct.new_value
WHERE account_id = acct.account_id;
IF SQL%NOTFOUND THEN -- account didn't exist.
-- Create it.
INSERT INTO accounts
VALUES (acct.account_id, acct.new_value);
UPDATE action SET status =
'Update: ID not found. Value inserted.'
WHERE CURRENT OF c1;
ELSE
UPDATE action SET status = 'Update: Success.'
WHERE CURRENT OF c1;
END IF;
/*--------------------------------------------*
* Process an INSERT. If the account already *
* exists, do an update of the account *
* instead. *
*--------------------------------------------*/
ELSIF acct.oper_type = 'I' THEN
BEGIN
INSERT INTO accounts
VALUES (acct.account_id, acct.new_value);
UPDATE action set status = 'Insert: Success.'
WHERE CURRENT OF c1;
Примеры программ B-17
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN -- account already
-- exists
UPDATE accounts SET bal = acct.new_value
WHERE account_id = acct.account_id;
UPDATE action SET status =
'Insert: Acct exists. Updated instead.'
WHERE CURRENT OF c1;
END;
/*--------------------------------------------*
* Process a DELETE. If the account doesn't *
* exist, set the status field to say that *
* the account wasn't found. *
*--------------------------------------------*/
ELSIF acct.oper_type = 'D' THEN
DELETE FROM accounts
WHERE account_id = acct.account_id;
IF SQL%NOTFOUND THEN -- account didn't exist.
UPDATE action SET status =
'Delete: ID not found.'
WHERE CURRENT OF c1;
ELSE
UPDATE action SET status = 'Delete: Success.'
WHERE CURRENT OF c1;
END IF;
/*--------------------------------------------*
* The requested operation is invalid. *
*--------------------------------------------*/
ELSE -- oper_type is invalid
UPDATE action SET status =
'Invalid operation. No action taken.'
WHERE CURRENT OF c1;
END IF;
END LOOP;
COMMIT;
END;
B-18 Руководство пользователя и справочник по PL/SQL
Результирующие таблицы
SQL> SELECT * FROM accounts ORDER BY account_id;
ACCOUNT_ID BAL
---------- ---------
1 399
2 2000
3 599
4 6500
6 20099
7 1599
6 records selected.
SQL> SELECT * FROM action ORDER BY time_tag;
ACCOUNT_ID O NEW_VALUE STATUS TIME_TAG
---------- - --------- ------------------------- ---------
3 u 599 Update: Success. 18-NOV-88
6 i 20099 Insert: Success. 18-NOV-88
5 d Delete: Success. 18-NOV-88
7 u 1599 Update: ID not found. 18-NOV-88
Value inserted.
1 i 399 Insert: Acct exists. 18-NOV-88
Updated instead.
9 d Delete: ID not found. 18-NOV-88
10 x Invalid operation. 18-NOV-88
No action taken.
7 records selected.
Примеры программ B-19
----------------
Пример 5. Встроенный PL/SQL
Следующий пример иллюстрирует, как можно встроить PL/SQL в
хост-язык высокого уровня, такой как C, и демонстрирует
выполнение банковской дебиторской транзакции.
Входная таблица
SQL> SELECT * FROM accounts ORDER BY account_id;
ACCOUNT_ID BAL
---------- ---------
1 1000
2 2000
3 1500
4 6500
5 500
Блок PL/SQL в программе на языке C
/* доступен на диске в файле SAMPLE5 */
#include
char buf[20];
EXEC SQL BEGIN DECLARE SECTION;
int acct;
double debit;
double new_bal;
VARCHAR status[65];
VARCHAR uid[20];
VARCHAR pwd[20];
EXEC SQL END DECLARE SECTION;
EXEC SQL INCLUDE SQLCA;
main()
{
extern double atof();
strcpy (uid.arr,"scott");
uid.len=strlen(uid.arr);
strcpy (pwd.arr,"tiger");
pwd.len=strlen(pwd.arr);
printf("\n\n\tEmbedded PL/SQL Debit Transaction Demo\n\n");
printf("Trying to connect...");
B-20 Руководство пользователя и справочник по PL/SQL
EXEC SQL WHENEVER SQLERROR GOTO errprint;
EXEC SQL CONNECT :uid IDENTIFIED BY :pwd;
printf(" connected.\n");
for (;;) /* Loop infinitely */
{
printf("\n** Debit which account number? (-1 to end) ");
gets(buf);
acct = atoi(buf);
if (acct == -1)
{
EXEC SQL COMMIT WORK RELEASE; /* log off ORACLE first */
exit(0); /* then exit program */
}
printf(" What is the debit amount? ");
gets(buf);
debit = atof(buf);
/* ---------------------------------- */
/* ----- Begin the PL/SQL block ----- */
/* ---------------------------------- */
EXEC SQL EXECUTE
DECLARE
insufficient_funds EXCEPTION;
old_bal NUMBER;
min_bal NUMBER := 500;
BEGIN
SELECT bal INTO old_bal FROM accounts
WHERE account_id = :acct;
-- If the account doesn't exist, the NO_DATA_FOUND
-- exception will be automatically raised.
:new_bal := old_bal - :debit;
IF :new_bal >= min_bal THEN
UPDATE accounts SET bal = :new_bal
WHERE account_id = :acct;
INSERT INTO journal
VALUES (:acct, 'Debit', :debit, sysdate);
:status := 'Transaction completed.';
ELSE
RAISE insufficient_funds;
END IF;
COMMIT;
Примеры программ B-21
EXCEPTION
WHEN NO_DATA_FOUND THEN
:status := 'Account not found.';
:new_bal := -1;
WHEN insufficient_funds THEN
:status := 'Insufficient funds.';
:new_bal := old_bal;
WHEN OTHERS THEN
ROLLBACK;
:status := 'Error: ' || SQLERRM(SQLCODE);
:new_bal := -1;
END;
END-EXEC;
/* -------------------------------- */
/* ----- End the PL/SQL block ----- */
/* -------------------------------- */
status.arr[status.len] = '\0'; /* null terminate
the string */
printf("\n\n Status: %s\n", status.arr);
if (new_bal >= 0)
printf(" Balance is now: $%.2f\n", new_bal);
} /* 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);
}
Интерактивная сессия
Embedded PL/SQL Debit Transaction Demo
Trying to connect... connected.
** Debit which account number? (-1 to end) 1
What is the debit amount? 300
Status: Transaction completed.
Balance is now: $700.00
** Debit which account number? (-1 to end) 1
What is the debit amount? 900
B-22 Руководство пользователя и справочник по PL/SQL
Status: Insufficient funds.
Balance is now: $700.00
** Debit which account number? (-1 to end) 2
What is the debit amount? 500
Status: Transaction completed.
Balance is now: $1500.00
** Debit which account number? (-1 to end) 2
What is the debit amount? 100
Status: Transaction completed.
Balance is now: $1400.00
** Debit which account number? (-1 to end) 99
What is the debit amount? 100
Status: Account not found.
** Debit which account number? (-1 to end) -1
Результирующие таблицы
SQL> SELECT * FROM accounts ORDER BY account_id;
ACCOUNT_ID BAL
---------- ---------
1 700
2 1400
3 1500
4 6500
5 500
SQL> SELECT * FROM journal ORDER BY date_tag;
ACCOUNT_ID ACTION AMOUNT DATE_TAG
---------- --------------------------- --------- ---------
1 Debit 300 28-NOV-88
2 Debit 500 28-NOV-88
3 Debit 100 28-NOV-88
Примеры программ B-23
----------------
Пример 6. Вызов хранимой процедуры
Эта программа Pro*C соединяется с ORACLE, запрашивает у
пользователя номер отдела, а затем вызывает хранимую процедуру с
именем get_employees, находящуюся в пакете с именем personnel.
Эта процедура объявляет три таблицы PL/SQL как формальные
параметры OUT и извлекает в эти таблицы порцию данных о
сотрудниках заданного отдела. Соответствующие фактические
параметры являются хост-массивами. Когда процедура завершается,
она автоматически присваивает все значения строк в таблицах
PL/SQL соответствующим элементам в хост-массивах. Вызывающая
программа обращается к процедуре повторно, распечатывая каждый
раз полученную порцию данных, до тех пор, пока все данные не
будут распечатаны.
Входная таблица
SQL> SELECT ename, empno, sal FROM emp ORDER BY sal DESC;
ENAME EMPNO SAL
---------- ----------- -------
KING 7839 5000
SCOTT 7788 3000
FORD 7902 3000
JONES 7566 2975
BLAKE 7698 2850
CLARK 7782 2450
ALLEN 7499 1600
TURNER 7844 1500
MILLER 7934 1300
WARD 7521 1250
MARTIN 7654 1250
ADAMS 7876 1100
JAMES 7900 950
SMITH 7369 800
14 records selected.
B-24 Руководство пользователя и справочник по PL/SQL
Хранимая процедура
/* доступна на диске в файле SAMPLE6 */
#include
#include
typedef char asciz;
EXEC SQL BEGIN DECLARE SECTION;
/* Define type for null-terminated strings. */
EXEC SQL TYPE asciz IS STRING(20);
asciz username[20];
asciz password[20];
int dept_no; /* which department to query */
char emp_name[10][21];
char job[10][21];
float salary[10];
int done_flag;
int array_size;
int num_ret; /* number of rows returned */
int SQLCODE;
EXEC SQL END DECLARE SECTION;
EXEC SQL INCLUDE SQLCA;
int print_rows(); /* produces program output */
int sqlerror(); /* handles unrecoverable errors */
main()
{
int i;
/* Connect to ORACLE. */
strcpy(username, "SCOTT");
strcpy(password, "TIGER");
EXEC SQL WHENEVER SQLERROR DO sqlerror();
EXEC SQL CONNECT :username IDENTIFIED BY :password;
printf("\nConnected to ORACLE as user: %s\n\n", username);
printf("Enter department number: ");
scanf("%d", &dept_no);
fflush(stdin);
Примеры программ B-25
/* Print column headers. */
printf("\n\n");
printf("%-10.10s%-10.10s%s\n", "Employee", "Job", "Salary");
printf("%-10.10s%-10.10s%s\n", "--------", "---", "------");
/* Set the array size. */
array_size = 10;
done_flag = 0;
num_ret = 0;
/* Array fetch loop - ends when NOT FOUND becomes true. */
for (;;)
{
EXEC SQL EXECUTE
BEGIN personnel.get_employees
(:dept_no, :array_size, :num_ret, :done_flag,
:emp_name, :job, :salary);
END;
END-EXEC;
print_rows(num_ret);
if (done_flag)
break;
}
/* Disconnect from ORACLE. */
EXEC SQL COMMIT WORK RELEASE;
exit(0);
}
print_rows(n)
int n;
{
int i;
if (n == 0)
{
printf("No rows retrieved.\n");
return;
}
for (i = 0; i < n; i++)
printf("%10.10s%10.10s%6.2f\n",
emp_name[i], job[i], salary[i]);
}
B-26 Руководство пользователя и справочник по PL/SQL
sqlerror()
{
EXEC SQL WHENEVER SQLERROR CONTINUE;
printf("\nORACLE error detected:");
printf("\n% .70s \n", sqlca.sqlerrm.sqlerrmc);
EXEC SQL ROLLBACK WORK RELEASE;
exit(1);
}
Интерактивная сессия
Connected to ORACLE as user: SCOTT
Enter department number: 20
Employee Job Salary
-------- -------- ------
SMITH CLERK 800.00
JONES MANAGER 2975.00
SCOTT ANALYST 3000.00
ADAMS CLERK 1100.00
FORD ANALYST 3000.00