Лабораторная работа №3. Пакеты, хранимые процедуры на языке Java, пользовательские агрегатные функции. Указания к выполнению.
Общие примеры кода приведены в лекции №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;
