Лабораторная работа №3. Пакеты, хранимые процедуры на языке Java, пользовательские агрегатные функции.
Задание 1. Пакеты
1.1 Создать пакеты для получения и модификации (insert, update, delete) данных таблиц, созданных в предыдущих лабораторных работах.
Бригада | Вариант |
1,2,7,8,13 | Отдельные пакеты для модификации данных каждой категории таблиц (справочники, сущности, факты). Общий пакет для получения данных из всех таблиц. |
3,4,9,10,14 | Отдельный пакет для всех операций над данными для каждой таблицы. |
5,6,11,12,15,16 | Общий пакет для модификации данных во всех таблицах. Отдельные пакеты для получения данных для каждой таблицы. |
Процедуры добавления строки в таблицу должны возвращать в выходном параметре идентификатор добавленной строки.
Для получения данных использовать:
Бригада | Вариант |
1,6,7,12,13,16 | Пакетный курсор с типом таблицы. |
2,5,8,11,14 | Хранимую процедуру, возвращающую курсор типа таблицы. |
3,4,9,10,15 | Хранимую процедуру, возвращающую REF CURSOR. |
Также необходимо создать пакет контроля с двумя процедурами:
- процедура - установка разрешения на модификацию данных (1 или 0). функция – возврат установленного разрешения.
Процедуры модификации данных должны выполняться, только если в пакете контроля предварительно установлено разрешение на модификацию. В противном случае должна генерироваться ошибка.
Призовой балл
Создание пакета, выполняющего генерацию кода для подготовки необходимых пакетов работы с данными.
1.2. Проверить работу процедур пакетов под отдельным пользователем, которому выданы права только на выполнение пакетов, но не таблицы.
1.3. Для созданных пакетов, с помощью запросов к представлениям словаря данных, привести следующую информацию
Бригада | Вариант |
1,3,5,10,13, | Объекты (имя и тип), от которых зависят созданные пакеты. |
4,7,9,12,14,16 | Перечень процедур пакетов, с указанием списка аргументов (название и тип). Список аргументов должен быть объединен в одну строку. |
2,6,8,11,15 | Перечень процедур пакетов, имеющих выходные параметры. |
Задание 2. Пользовательские агрегатные функции и хранимые процедуры на языке Java.
2.1. Подготовить тестовую таблицу с данными, в составе следующих столбцов:
- Идентификатор. Несколько столбцов дальнейшего выполнения запросов с группированием по ним. Строковый столбец.
При генерации данных значение строковый столбец должен формироваться как конкатенация случайного набора токенов, составленных из букв латинского алфавита, разделенных пробелами. Т. е. в столбце должны содержаться строки вида «abc bbbb vvv» , «nnn abc» и. т.д. Содержание данных должно быть таким, чтобы созданная в следующем пункте агрегатная функция что-то возвращала.
2.2. Реализовать пользовательскую агрегатную функцию результатом, которой является:
Бригада | Вариант |
1,7,13 | Токен, присутствующий максимальное число раз в элементах группы. |
2,8,14 | Токен максимальной длины, присутствующий во всех элементах группы. |
3,9,15 | Количество различных токенов, встречающихся во всех элементах группы. |
4,10,16 | Количество символов встречающихся во всех элементах группы. |
5,11 | Строка из символов английского алфавита, не встречающихся ни в одном из элементов. |
6,12 | Все токены, начинающиеся с определенной буквы и конкатенированные в порядке возрастания. |
Функцию завершения агрегата реализовать как хранимую процедуру на Java.
Проверить корректность работы запроса с созданной агрегатной функцией. Проверить корректную работу агрегата при обработке null значений.
Призовой балл
Возможность при использовании агрегата задавать символ, который будет разделителем для токенов (помимо пробела). Т. е. если захотим обрабатывать строковые данные вида «aaa;bbb;ccc» должна быть возможность как-то «указать» агрегату, что для разделения токенов нужно использовать символ “;”. «Хак» вида replace(str,’;’,’ ‘) не предлагать.
2.3. Исследовать поведение выполнения запроса для разных объемов данных:
Бригада | Вариант |
1,2,3,4,5 | Изменение количества строк в таблице (100, 500, 1000); статистики CPU Used by this session, Session logical reads |
6,7,8,9,10 | Изменение количества символов в строковом столбце (50, 150, 250); статистики DB time, Session logical reads |
11,12,13,14,15,16 | Изменение количества строк в таблице (100, 500) и изменение количества символов в строковом столбце (50, 150); статистики CPU Used by this session, Session logical reads |
Призовой балл
sql-сценарий, автоматизирующий генерацию разных вариантов тестовых данных и сбора статистики выполнения.
Указания к выполнению.
Общие примеры кода приведены в лекции №3.
1.Элементы пакета
1.1 Процедура добавления строки в таблицу.
При установке значения идентификатора строки через триггер, то чтобы получить его значение в выходной параметр процедуры, используется следующая конструкция оператора insert:
procedure ins(v_name in varchar2,v_id out number)
as
begin
insert into tab(name)
values (v_name)
returning id into v_id;
end;
1.2 Возвращение данных таблицы через пакет
1.2.1 Пакетные курсоры
Объявление курсора в спецификации пакета:
create or replace package test_pkg as
CURSOR tab_cur (v_name IN tab. name%TYPE) RETURN tab%ROWTYPE;
end test_pkg;
Определение запроса курсора в теле пакета:
create or replace
package body test_pkg as
cursor tab_cur (v_name in tab. name%type) return tab%rowtype
is
select t.* from tab t
where t. name = v_name
order by 1;
end test_pkg;
Использование курсора в PL/SQL сценарии:
set SERVEROUTPUT ON
declare
--v_t переменная, тип которой соответствует типу строки курсора
-- test_pkg. tab_cur
v_t test_pkg. tab_cur%rowtype;
begin
--открываем курсор и передаем параметр запроса
open test_pkg. tab_cur('a');
loop
--в цикле получаем очередную строку из курсора в переменную v_t
exit when test_pkg. tab_cur%notfound;
fetch test_pkg. tab_cur into v_t;
dbms_output. put_line(v_t. name||' '||v_t. value);
end loop;
--закрываем курсор
close test_pkg. tab_cur;
end;
1.2.2 Процедуры, возвращающие курсор
Для типизированного курсора, необходимо в спецификации пакета определить тип, либо использовать глобально определенный тип.
create or replace package test_pkg as
--тип строки курсора
type tab_rct is ref cursor return tab%rowtype;
procedure get_tab(v_name in tab. name%type, v_cur out tab_rct);
end test_pkg;
Процедура, возвращающая курсор в теле пакета определяется как:
create or replace
package body test_pkg as
procedure get_tab (v_name in tab. name%type, v_cur out tab_rct)
as
begin
open v_cur for
select t.* from tab t
where t. name = v_name
order by 1;
end;
end test_pkg;
Использование процедуры:
set SERVEROUTPUT ON
declare
--v_t переменная, тип которой соответствует типу строки курсора
v_t tab%rowtype;
--v_cur курсорная переменная
v_cur test_pkg. tab_rct;
begin
test_pkg. get_tab('b',v_cur);
loop
exit when v_cur%notfound;
fetch v_cur into v_t;
dbms_output. put_line(v_t. name||' '||v_t. value);
end loop;
close v_cur;
end;
При использовании нетипизированного курсора пакет и его использование выглядит следующим образом:
create or replace package test_pkg as
procedure get_tab(v_name in tab. name%type, v_cur out sys_refcursor);
end test_pkg;
create or replace
package body test_pkg as
procedure get_tab (v_name in tab. name%type, v_cur out sys_refcursor)
as
begin
open v_cur for
select t.* from tab t
where t. name = v_name
order by 1;
end;
end test_pkg;
Использование процедуры:
set SERVEROUTPUT ON
declare
--v_t переменная, тип которой соответствует типу строки курсора
v_t tab%rowtype;
v_cur sys_refcursor;
begin
test_pkg. get_tab('b',v_cur);
loop
exit when v_cur%notfound;
fetch v_cur into v_t;
dbms_output. put_line(v_t. name||' '||v_t. value);
end loop;
close v_cur;
end;
Отличие типизированных от нетипизированных курсоров заключается в том, что при использовании типизированного, проверка соответствия типа курсора и строки запроса, для которого он открывается (open v_cur for select t.* from tab t) осуществляется на этапе компиляции пакета.
При использовании нетипизированного курсора несоответствие типов может возникнуть на этапе выполнения операции
fetch v_cur into v_t;
что вызовет ошибку при выполнении сценария.
В том случае, если используется нетипизированный курсор и тип строки не известен (или может изменяться от вызова к вызову), то для получения данных из такого курсора используются процедуры пакета dbms_sql (пример - http:///questions/10321571/oracle-select-a-specific-column-from-a-ref-cursor).
1.3 Получение информации об объектах программного кода
1.3.1. Список объектов программного кода
Представления dba_procedures/all_procedures/user_procedures
список объектов программного кода
- всей б. д. / dba_ всех доступных пользователю / all_ принадлежащих пользователю / user_
object_name – имя объекта - хранимой процедуры/ функции/пакета/объектного типа;
procedure_name – имя хранимой процедуры, входящей в пакет или тип. Для «отдельной» хранимой процедуры, значение – null;
object_type – тип объекта программного кода;
aggregate – является ли агрегатной функцией
authid – выполняется с правами создателя (definer) или вызвавшего пользователя.
1.3.2. Исходный код
Представления dba_source/all_ source /user_ source.
name - имя объекта;
type – его тип;
line – номер строки исходного кода;
text – сама строка.
1.3.3. Параметры хранимых процедур
Представление dba_arguments/all_ arguments /user_ arguments
object_name – хранимая процедура или функция;
package_name – пакет или объектный тип, в который она входит;
argument_name - имя параметра;
position – номер в списке вызова ;
sequence – номер параметра;
data_type – тип данных;
in_out – входной или выходной или входной и выходной;
data_length – размерность.
1.3.4. Зависимости объектов
Представление dba_dependencies/all_ dependencies /user_ dependencies
2. Передача табличной переменной в хранимую процедуру на Java.
Т. к. накопление данных для агрегирования выполняется во вложенной таблице
например create type str_array is table of varchar2(4000);
то из терминальной функции нужно передавать эту вложенную таблицу в Java процедуру.
Пример кода:
CREATE OR REPLACE AND COMPILE
JAVA SOURCE NAMED "passing_tab"
AS
import java. io.*;
import java. sql.*;
import java. math.*;
import oracle. sql.*;
import oracle. jdbc. driver.*;
public class passing_tab extends Object{
private static void show_array_info( oracle. sql. ARRAY p_in )
throws SQLException{
System. out. println( "Array is of type " +
p_in. getSQLTypeName() );
System. out. println( "Array is of type code " +
p_in. getBaseType() );
System. out. println( "Array is of length " +
p_in. length() );
}
public static java. lang. String
pass_str_array( oracle. sql. ARRAY p_in)
throws java. sql. SQLException, IOException{
show_array_info( p_in );
String[] values = (String[])p_in. getArray();
for( int i = 0; i < p_in. length(); i++ )
System. out. println( "p_in["+i+"] = " + values[i] );
return values[0];
}
}
Функция обертка:
create function pass( p_in in str_array) return varchar2 as language java
name 'passing_tab. pass_str_array(oracle. sql. ARRAY) return java. lang. String';
Пример использования функции:
set SERVEROUTPUT ON
declare
v_str str_array;
begin
v_str := str_array();
v_str. extend();
v_str(v_str. count) := 'begin';
v_str. extend();
v_str(v_str. count) := 'end';
dbms_output. put_line(pass(v_str));
end;
