Курсоры

Пример

Пакетированные подпрограммы

Упреждающие объявления также позволяют вам группировать ло­­­ги­чес­ки связанные подпрограммы в пакет. Спецификации под­прог­рамм идут в спецификацию пакета, а тела подпрограмм - в тело паке­та, где они не видимы приложениям. Пакеты скрывают детали реализа­ции. Вы можете определять в теле пакета подпрограммы, не объявляя их спе­цификаций в спецификации пакета. Однако такие подпрограммы мож­но вызывать только изнутри пакета.

package emp_actions is -- спецификация пакета

procedure hire_employee (empno number, ename char,...);

procedure fire_employee (emp_id number);

end emp_actions;

package body emp_actions is -- тело пакета

procedure hire_employee (empno number, ename char,...) is

begin insert into emp values (empno, ename,...); end hire_employee;

procedure fire_employee (emp_id number) is

begin delete from emp where empno = emp_id; end fire_employee;

end emp_actions;

Под курсором понимается набор записей, получаемый при выпол­не­нии пред­став­ле­ния или запроса. Существуют явные (объявляемые поль­зо­ва­телем и возвращающие несколько строк) и неявные (не требующие объ­яв­ления) курсоры. Если курсор явно не объявлен, то SQL‑оператор автома­ти­чес­ки объявляет неявный курсор со следующими ограничениями: нель­зя использовать операторы Open, Fecth, Close; при выборке нескольких строк генерируется исключение Too_many_Rows.

Оператор объявления курсора:

Cursor <имя курсора> [(<имя параметра> [in] <тип>[:=<значение>],...)]

[Return <тип, возвращаемой записи (строки)>] Is <select‑оператор>;

Оператор объявления переменной курсора:

Type <имя типа курсора> is ref cursor [return <тип возвращаемой записи>];

<имя переменной курсора> <имя типа курсора>; --объявление переменной

Внимание! Переменные курсоры не могут быть объявлены в па­ке­те, не могут исполь­зовать­ся в динамических SQL‑операторах вместо кур­со­ра в цикле For‑in‑loop и для определения типа столбца в опера­то­рах Create table/view и в коллек­циях.

Переменную курсора можно передавать в качестве параметра. Если тип переменной курсора определен в другом пакете, то он уточняется именем пакета.

Оператор открытия курсора:

Open <имя курсора> [(<значение параметра>,...)]

Open <имя переменной курсора> for <select‑оператор>

Правила вызова и использования параметров курсора и подпрог­рам­мы аналогичны.

Атри­бу­­ты для курсора и переменной курсора (указываются пос­ле имени курсора): %isopen (true, если курсор отк­рыт), %found/%notfound (найдена/не найдена строка), %rowcount (но­мер текущей строки).

Оператор открытия курсора: Close<имя курсора>.

Оператор чтения очередной строки курсора:

Fetch<имя курсора или переменной курсора>

into{<имя переменной>,... | <имя переменной‑записи>}

Пример. Таблице Data_Table хранятся данные, собранные в ходе ла­бо­ратор­ных экспериментов. Нужно проанализировать данные из экспе­римента 1 и поместить их в таблицу базы данных temp.

declare

num1 data_table.n1%type; num2 data_table.n2%type; --что и столбцы таблицы

num3 data_table.n3%type; result temp. num_col1%type;

cursor c1 is select n1, n2, n3 from data_table where exper_num = 1;

begin open c1;

loop fetch c1 into num1, num2, num3; exit when c1%notfound;

result:= num2/(num1 + num3); insert into temp values (result);

end loop; close c1; commit;

end;

Пример извлечения пяти самых высокооплачиваемых сотрудников.

declare cursor c1 is select ename, empno, sal from emp order by sal desc;

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;

insert into temp values (my_sal, my_empno, my_ename);

commit;

end loop; close c1;

end;

Цикл For с курсором является самым простым способом работы с явным курсором.

Пример. Проанализировать данные, собранные в ходе лабора­тор­ного эксперимента 1, и поместить результаты в табли­цу Temp. Пе­ре­менная c1rec, используемая как индекс в курсорном цикле For, неявно объявляется как запись, хранящая все элементы данных, возвращаемые Fetch.

declare result temp. num_col1%type;

cursor c1 is select n1, n2, n3 from data_table where exper_num = 1;

begin for c1rec in c1 loop

result:= c1rec.n2 / (c1rec.n1 + c1rec.n3); insert into temp values (result);

end loop; commit;

end;

Дата добавления: 2014-12-10