Хранимые процедуры, используемые в вызываемых и подготовленных операторах

Выполнение хранимых процедур при помощи вызываемых операторов JDBC-драйвера СУБД Informix

Для улучшения производительности приложения, работающего с реляционной базой данных, обычно используются хранимые процедуры - определяемые пользователем функции, содержащие ряд SQL-операторов, которые хранятся в базе данных. В данной статье мы продемонстрируем, как обращаться к хранимым процедурам, используя вызываемые операторы (callable statements) JDBC-драйвера СУБД Informix®. JDBC CallableStatement расширяет PreparedStatement и отправляет запрос серверу на выполнение хранимой процедуры для сложных операций, таких как возврат значений или использование входных и выходных параметров хранимых процедур.

Бабита Сонаване, разработчик программного обеспечения, IBM

Бабита Сонаване (Babita Sonavane) - фотографияБабита Сонаване (Babita Sonavane) является разработчиком программного обеспечения в лаборатории IBM India Lab в Индии в Бангалоре. Имеет более пяти лет опыта работы в области ИТ. Занимается поддержкой и разработкой различных функциональных возможностей JDBC- и DB2 JDBC-драйверов СУБД Informix.



Хавьер Сагрера, инженер-программист, IBM

Хавьер Сагрера (Javier Sagrera) - фотографияХавьер Сагрера (Javier Sagrera) работает инженером-программистом в группе Common Client Technologies (CCT). Пришел в группу Informix в 2000 году, более 10 лет занимался разработкой приложений для серверов баз данных Informix и клиентских приложений Informix. В настоящее время он является сотрудником лаборатории IBM UK Bedfont Lab в Великобритании в Лондоне. Обладает обширными знаниями по всем технологиям Microsoft® и является признанным во всем мире экспертом по всем инструментальным средствам разработки для Informix.



21.01.2013

Введение

Основное преимущество использования хранимых процедур по сравнению с одиночными SQL-операторами в приложениях, работающих с реляционными базами данных, заключается в том, что план запроса (или план выполнения) генерируется при создании хранимой процедуры и используется повторно при каждом выполнении хранимой процедуры, что значительно экономит ресурсы на сервере базы данных. Однажды созданные хранимые процедуры могут использоваться любыми клиентскими приложениями (например, JDBC-приложениями) сколько угодно раз без нового плана выполнения.

Правила работы с хранимыми процедурами различны для разных серверов баз данных. Например, такие системы управления базами данных (СУБД), как Informix и DB2®, используют для выполнения хранимых процедур различный SQL-синтаксис. Это усложняет жизнь разработчикам приложений при написании кода, предназначенного для работы с несколькими СУБД. Подход на основе выполняемых операторов дает возможность выполнять хранимые процедуры с использованием одного и того же SQL-синтаксиса для всех СУБД.


Для чего используются хранимые процедуры

Предположим, у нас есть JDBC-приложение, которое должно эффективно выполнять последовательность задач на регулярной основе. Мы можем использовать Java™-метод, но сколько раз понадобится организовать клиент-серверное взаимодействие для отправки и получения данных? Сервер базы данных будет готовить и генерировать план запроса для каждого SQL-оператора, отправляемого приложением, что займет определенное процессорное время. С точки зрения производительности использование простых Java-методов с одиночными SQL-операторами может оказаться не лучшей идеей.

А что если использовать хранимую процедуру, создание которой является одноразовой задачей? Создайте одиночный SQL-вызов, и к нему можно будет обращаться из JDBC-приложения при помощи объекта CallableStatement, выступающего в роли вызывающей стороны для хранимой процедуры на сервере. В хранимой процедуре будет размещена основная часть бизнес-логики. Это поможет упростить код клиентского приложения и ускорит выполнение, поскольку включаемые в хранимую процедуру SQL-операторы готовятся и оптимизируются при ее создании.


Вызов хранимых процедур в JDBC-приложениях

JDBC-драйвер СУБД Informix предоставляет методы Statement, PreparedStatement и CallableStatement, которые используются для выполнения хранимых процедур. Выбор метода зависит от характеристик хранимой процедуры. Например, если хранимая процедура возвращает единственное значение, следует использовать JDBC-объект Statement. В следующей таблице приведены некоторые рекомендации по выбору метода для использования с определенным типом хранимой процедуры.

Таблица 1. В таблице показано, какой JDBC-метод рекомендуется использовать для соответствующего типа хранимой процедуры
Тип хранимой процедурыJDBC-метод
Хранимая процедура без параметров IN или OUTИспользуйте объект Statement
Хранимая процедура с параметрами INИспользуйте объект PreparedStatement
Хранимая процедура с параметрами IN и OUTИспользуйте объект CallableStatement

Ниже мы приведем примеры выполнения хранимых процедур с использованием JDBC-методов СУБД Informix в следующих случаях:

  • Использование Statement без параметров.
  • Использование PreparedStatement при наличии входных параметров.
  • Использование CallableStatement при наличии выходных параметров.
  • Именованные параметры в CallableStatement.
  • Перегружаемые хранимые процедуры.

Для каждого вышеупомянутого случая мы предоставим:

  • Синтаксис, используемый для вызова хранимой процедуры из процедуры JDBC-драйвера СУБД Informix.
  • Схему хранимой процедуры в базе данных.
  • Пример программы JDBC-драйвера с выходной информацией.

Использование объекта Statement без параметров

Синтаксис для выполнения хранимой процедуры без параметров IN или OUT приведен в листинге 1.

Листинг 1. Синтаксис для выполнения хранимой процедуры без параметров IN или OUT
{call имя-процедуры}

Для данного типа хранимой процедуры нет необходимости использовать объект CallableStatement; можно использовать простое JDBC-выражение. В листинге 2 приведено определение хранимой процедуры на Informix SQL. В следующем примере GetCustName возвращает результат, содержащий один столбец данных, являющийся комбинацией имени и фамилии первых пяти человек из таблицы customer.

Листинг 2. Определение хранимой процедуры, использующей объект Statement без параметров
CREATE PROCEDURE GETCUSTNAME() RETURNING LVARCHAR AS NAME;
DEFINE W_NAME LVARCHAR;
FOREACH
SELECT FIRST 5 FNAME  ||  ' ' || LNAME INTO W_NAME
FROM CUSTOMER
RETURN W_NAME WITH RESUME;
END FOREACH;
END PROCEDURE;

ПРИМЕЧАНИЕ. Этот пример хранимой процедуры (и все другие в данной статье) использует таблицы, созданные в демонстрационной базе данных stores_demo, которую можно выбрать во время установки Informix IDS.

JDBC Java-код для выполнения хранимой процедуры приведен в листинге 3.

Листинг 3. JDBC-код, использующий объект Statement без параметров
public static void executeStoredprocNoParams(Connection con) {
        try {
            Statement stmt = con.createStatement();
            ResultSet rs = stmt.executeQuery("{call GETCUSTNAME}");
            while (rs.next()) {
            System.out.println(rs.getString("Name"));
            }
            rs.close();
            stmt.close();
            }
        catch (Exception e) {
            e.printStackTrace();
            }
}

Поскольку хранимая процедура возвращает набор записей, необходимо использовать метод Statement.executeQuery() и извлекать данные при помощи метода ResultSet.next(). В листинге 4 приведена выходная информация данной программы.

Листинг 4. Выходная информация программы, приведенной в листинге 3
$java sample_Stored procedure_1
                
Ludwig          Pauli
Carole          Sadler
Philip          Currie
Anthony         Higgins
Raymond         Vector
George          Watson
                
$

Использование PreparedStatement при наличии входных параметров

Данный тип хранимых процедур используется чаще всего. Хранимая процедура требует указания входных параметров, передающих в нее данные для внутренней обработки. Для работы с данным типом хранимых процедур следует использовать PreparedStatement. Синтаксис приведен в листинге 5.

Листинг 5. Синтаксис для выполнения хранимых процедур, использующих подготовленные операторы, при наличии входных параметров
{call имя-процедуры(?,?,...)}

Хотя для выполнения хранимой процедуры можно использовать синтаксис Informix SQL (например, выполнить процедуру имя-процедуры(?,?)), рекомендуется придерживаться синтаксиса управляющей последовательности SQL.

Каждый символ вопросительного знака (?) соответствует одному входному параметру хранимой процедуры. Они являются заместителями значений, передаваемых в хранимую процедуру.

Чтобы указать значение параметра, можно использовать один из setter-методов класса IfxPreparedStatement (например, pstmt.setInt()). Setter-метод, который можно использовать, определяется типом данных параметра IN. Кроме значения параметра setter-метод принимает позицию параметра в SQL-операторе.

Листинг 6. Пример хранимой процедуры с сигнатурой
UPDMANU(INT MN_CODE, CHAR(10) MN_NAME, DATE MN_UPD);
Листинг 7. Фрагмент Java-кода, используемого в примере, приведенном в листинге 6
pstmt = con.prepareStatement("{call updmanu(?,?,?)}");
pstmt.setInt(1, manu_id);
pstmt.setString(2, manu_code);
pstmt.setDate(3, manu_date);

В листинге 8 приведена хранимая процедура, демонстрирующая способ использования параметров IN.

Листинг 8. Пример хранимой процедуры, демонстрирующей способ использования параметров IN
CREATE PROCEDURE GETORDERS(CUS_ID INT) RETURNING INT AS ORDER_NUM, 
DATE AS ORDER_DATE, LVARCHAR AS SHINSTRUC;
DEFINE W_ORDERN INT;
DEFINE W_ORDERD DATE;
DEFINE W_SHIP LVARCHAR;  
    FOREACH
        SELECT ORDER_NUM,ORDER_DATE,SHIP_INSTRUCT 
        INTO W_ORDERN,W_ORDERD,W_SHIP
    FROM ORDERS WHERE ORDERS.CUSTOMER_NUM=CUS_ID
        RETURN W_ORDERN,W_ORDERD,W_SHIP WITH RESUME;
    END FOREACH;
END PROCEDURE;

Данная хранимая процедура принимает один входной параметр CUS_ID, являющийся значением с типом integer, и возвращает список заказов для этого customer_id. Java-код для вызова данной хранимой процедуры приведен в листинге 9.

Листинг 9. Java-код для выполнения хранимой процедуры, использующей подготовленные операторы с входными параметрами
public static void executeSprocInParams(Connection con, int c_id) {
    try {
        PreparedStatement pstmt = con.prepareStatement("{call getorders(?)}");
        pstmt.setInt(1, c_id);
        ResultSet rs = pstmt.executeQuery();                
        while (rs.next()) {
            System.out.println("Order Number\t: "+rs.getString("order_num"));
            System.out.println("Order Date\t: "+rs.getString("order_date"));
            System.out.println("Instructions\t: "+rs.getString("shinstruc"));
            System.out.println();
            }
        rs.close();
        pstmt.close();
        }
    catch (Exception e) {
        e.printStackTrace();
    }
}

Результаты выполнения метода executeSprocInParams() приведены в листинге 10.

Листинг 10. Результаты работы программы, приведенной в листинге 9
$java sample_Stored procedure_2
Order Number    : 1001
Order Date      : 2008-05-20
Instructions    : express
                
Order Number    : 1003
Order Date      : 2008-05-22
Instructions    : express
                
Order Number    : 1011
Order Date      : 2008-06-18
Instructions    : express
                
Order Number    : 1013
Order Date      : 2008-06-22
Instructions    : express
$

Использование CallableStatement при наличии выходного параметра

Если хранимая процедура требует использования параметров IN или OUT, для обработки этих параметров необходимо использовать JDBC CallableStatement. С параметрами IN и OUT можно использовать только класс IfxCallableStatement (который расширяет Java CallableStatement).

Мы продемонстрируем вызов хранимой процедуры, возвращающей один или несколько параметров OUT. Это параметры, которые хранимая процедура использует для возврата данных вызывающему приложению в виде одиночных значений, а не набора результатов, с которым мы сталкивались ранее. SQL-синтаксис, использующийся для хранимых процедур с параметрами IN/OUT, аналогичен продемонстрированному ранее в листинге 5.

Листинг 11. Синтаксис для выполнения хранимых процедур, использующих вызываемые выражения при наличии выходных параметров
{call имя-процедуры(?,?,...)}

Параметры (IN и OUT) должны следовать в правильном порядке. Значения для параметров OUT должны быть зарегистрированы при помощи метода registerOutParameter() класса CallableStatement. Параметры OUT должны быть указаны в правильном порядке. Аналогично параметру IN первый параметр данного метода указывает порядковый номер (позицию) параметра – например, cstmt.registerOutParameter(2, Types.INTEGER);.

Указываемое в методе registerOutParameter значение параметра OUT должно быть одним из типов данных Informix JDBC, содержащихся в java.sql.Types, который внутренне преобразуется в один из встроенных типов данных IDS.

В данном примере мы будем использовать хранимую процедуру, обращающуюся к таблице items базы данных stores_demo (см. листинг 12).

Листинг 12. Хранимая процедура, демонстрирующая применение параметров OUT
CREATE PROCEDURE GETTOTAL(ORDER_ID INT, OUT TOTALPRICE MONEY);  
LET TOTALPRICE=(SELECT SUM(TOTAL_PRICE) FROM ITEMS WHERE ORDER_NUM=ORDER_ID);  
END PROCEDURE;

Данная хранимая процедура возвращает один параметр OUT (TotalPrice), имеющий тип integer, в зависимости от указанного параметра IN (Order_ID), который тоже имеет тип integer. Возвращаемое в параметре OUT значение является суммой всех элементов из конкретного номера заказа, содержащегося в таблице items.

Листинг 13. Java-код выполнения хранимой процедуры, использующей вызываемые выражения при наличии выходных параметров
public static void executeStoredProcOUTParams(Connection con,int o_id) {
    try {
        CallableStatement cstmt = con.prepareCall("{call GetTotal(?, ?)}");
        cstmt.setInt(1, o_id);
        cstmt.registerOutParameter(2, Types.INTEGER);
        cstmt.execute();
        System.out.println("Total price for order" + o_id +"is $"+cstmt.getInt(2));
    }
    catch (Exception e) {
        e.printStackTrace();
    }
}

Именованные параметры в CallableStatement

В предыдущем примере мы использовали для идентификации каждого параметра хранимой процедуры его позицию в списке. Можно идентифицировать параметры по имени; это упрощает код и облегчает его чтение.

Следующий пример демонстрирует использование именованных параметров в Java-приложении. Отметим, что имена параметров соответствуют именам параметров в определении хранимой процедуры.

Листинг 14. Использование именованных параметров в Java-приложении
public static void executeStoredProcOUTParams(Connection con,int o_id) {
    try {
    CallableStatement cstmt = con.prepareCall("{call GetTotal(?, ?)}");
    cstmt.setInt("Order_ID", o_id);
    cstmt.registerOutParameter("TotalPrice", Types.INTEGER);
    cstmt.execute();
    System.out.println("Total price for order"+ o_id +"is $"+cstmt.getInt("TotalPrice"));
    }
    catch (Exception e) {
        e.printStackTrace();
    }
}

Параметры должны указываться либо по индексу, либо по имени; смешивать эти два метода нельзя. Два приведенных Java-примера выводят суммарную стоимость указанного заказа, как показано ниже:

Листинг 15. Выходная информация двух java-примеров, приведенных в листингах 13 и 14
$java sample_Stored procedure_3
Total price for order 1002 is $1200
$

ПРИМЕЧАНИЕ. Для выполнения хранимой процедуры в этих примерах используется метод execute() класса CallableStatement. Он используется потому, что хранимая процедура не возвращает набор записей. Если бы она возвращала набор записей, нужно было бы использовать метод executeQuery(), как показано в следующем примере.

Листинг 16. Хранимая процедура, иллюстрирующая применение метода executeQuery()
CREATE PROCEDURE GETTOTALBYMANU(CODE CHAR(3),  OUT TOTAL MONEY) 
RETURNING CHAR(3) AS MANU_CODE, CHAR(10) AS MANU_NAME;  
DEFINE W_MANU_CODE CHAR(3);
DEFINE W_MANU_NAME CHAR(10); 
LET TOTAL=(SELECT SUM(TOTAL_PRICE) FROM ITEMS WHERE MANU_CODE=CODE);   
SELECT MANU_CODE,MANU_NAME
  INTO W_MANU_CODE,W_MANU_NAME FROM MANUFACT WHERE MANU_CODE=CODE;
RETURN W_MANU_CODE,W_MANU_NAME;		      
END PROCEDURE;

Метод, приведенный в листинге 17, использует executeQuery() для активизации хранимой процедуры GetTotalByManu.

Листинг 17. Java-код, демонстрирующий применение метода executeQuery()
public static void executeStoredProcOUTParamsResulset(Connection con,String manu_id) {
    try {
        CallableStatement cstmt = con.prepareCall("{ call gettotalbymanu(?,?)}");
        cstmt.setString(1, manu_id);	  	  
        cstmt.registerOutParameter(2, Types.CHAR);
        ResultSet rs = cstmt.executeQuery();
        rs.next();		
        System.out.println("Total for manufacturer '"+rs.getString(2).trim()+
        " ("+rs.getString(1)+") ' is $"+cstmt.getInt(2));
    }
    catch (Exception e) {
        e.printStackTrace();
    }
}

Выходная информация программы, приведенной в листинге 17, показана в листинге 18.

Листинг 18. Выходная информация Java-примера, приведенного в листинге 17
$java sample_Stored procedure_4
Total for manufacturer 'Hero (HRO)' is $2882
$

ПРИМЕЧАНИЕ. Если неизвестно, как была определена хранимая процедура, для получения информации о хранимой процедуре (например, имен и типов параметров) можно использовать подпрограммы JDBC Metadata.

В следующем примере для получения имени и типа процедуры gettotalbymanu используется метод getProcedureColumns().

Листинг 19. Java-код
public static void executeStoredGetOutParams(Connection con,String procname) {
    try {
        DatabaseMetaData dbmd = con.getMetaData();
        ResultSet rs = dbmd.getProcedureColumns("stores7","",procname.toUpperCase(),null);
        while (rs.next())
        if (rs.getShort("COLUMN_TYPE")==DatabaseMetaData.procedureColumnOut) {
        System.out.println("OUT Parame: "+ rs.getString("COLUMN_NAME"));
        System.out.println("Type: "+rs.getString("DATA_TYPE") );		 
        }	  
    }
    catch (Exception e) {
        e.printStackTrace();
    }
}

Альтернативой является проверка хранимой процедуры на наличие параметров OUT при помощи метода CallableStatement.hasOutParameter(). Если она была определена с параметрами OUT, возвращается TRUE (см. листинг 20).

Листинг 20. Пример Java-кода
CallableStatement cstmt = con.prepareCall("{ call gettotalbymanu(?,?)}");
if (((IfxCallableStatement) cstmt).hasOutParameter())
System.out.println("Stored procedure has OUT parameters ");
// выполнить логику

Вызов хранимых процедур с несколькими сигнатурами

Сервер баз данных Informix поддерживает перегружаемые хранимые процедуры (overloaded stored procedures). Можно определить хранимые процедуры с одним и тем же именем, но с различными параметрами (или сигнатурами) для выполнения различных операций в зависимости от принимаемых параметров. В листингах 21 и 22 приведены примеры двух таких процедур.

Листинг 21. Определение хранимой процедуры 1
CREATE PROCEDURE GETORDERS(CUS_ID INT) RETURNING INT 
AS ORDER_NUM, DATE AS ORDER_DATE, LVARCHAR AS SHINSTRUC;
    DEFINE W_ORDERN INT;
    DEFINE W_ORDERD DATE;
    DEFINE W_SHIP LVARCHAR;  
    FOREACH
        SELECT ORDER_NUM,ORDER_DATE,SHIP_INSTRUCT 
        INTO W_ORDERN,W_ORDERD,W_SHIP
FROM ORDERS WHERE ORDERS.CUSTOMER_NUM=CUS_ID
        RETURN W_ORDERN,W_ORDERD,W_SHIP WITH RESUME;
    END FOREACH;
END PROCEDURE;
Листинг 22. Определение хранимой процедуры 2
CREATE PROCEDURE GETORDERS(ORD_DATE DATE) RETURNING INT 
AS ORDER_NUM, DATE AS ORDER_DATE, LVARCHAR AS SHINSTRUC;
    DEFINE W_ORDERN INT;
    DEFINE W_ORDERD DATE;
    DEFINE W_SHIP LVARCHAR;  
    FOREACH
        SELECT ORDER_NUM,ORDER_DATE,SHIP_INSTRUCT 
        INTO W_ORDERN,W_ORDERD,W_SHIP
FROM ORDERS WHERE ORDERS.ORDER_DATE=ORD_DATE
        RETURN W_ORDERN,W_ORDERD,W_SHIP WITH RESUME;
    END FOREACH;
END PROCEDURE;

Обе процедуры имеют одинаковые имена (GETORDERS), но первая использует параметр INT для получения заказов конкретного клиента, а вторая имеет параметр DATE для возврата заказов за определенную дату (см. листинг 23).

Листинг 23. Пример нескольких сигнатур
CREATE PROCEDURE GETORDERS(CUS_ID INT) RETURNING INT 
AS ORDER_NUM, DATE AS ORDER_DATE, LVARCHAR AS SHINSTRUC;
                
CREATE PROCEDURE GETORDERS(ORD_DATE DATE) RETURNING INT 
AS ORDER_NUM, DATE AS ORDER_DATE, LVARCHAR AS SHINSTRUC;

Для выполнения этих хранимых процедур из JDBC-приложения необходимо предоставить тип параметра в синтаксисе SQL, чтобы проинформировать Informix, какие хранимые процедуры вы собираетесь запускать. В качестве заместителя используйте префикс ::datatype, как показано в листинге 24.

Листинг 24. Примеры использования заместителя
{call getorders(?::INT)}
{call getorders(?::DATE)}

В листинге 25 показано выполнение процедуры GETORDERS(DATE).

Листинг 25. Java-код, демонстрирующий использование хранимой процедуры с несколькими сигнатурами
public static void executeSprocInParams_date(Connection con, String o_date) {
    try { 
        PreparedStatement pstmt = con.prepareStatement("{call getorders(?::DATE)}");
        pstmt.setString(1, o_date);
        ResultSet rs = pstmt.executeQuery();
        while (rs.next()) {
            System.out.println("Order Number\t: "+rs.getString("order_num"));
            System.out.println("Order Date\t: "+rs.getString("order_date"));
            System.out.println("Instructions\t: "+rs.getString("shinstruc"));
            System.out.println();
        }
        rs.close();
        pstmt.close();
    }
         catch (Exception e) {
        e.printStackTrace();
    }
}

Заключение

В данной статье были рассмотрены различные способы доступа к простым и сложным хранимым процедурам из JDBC-приложения. В разделе Ресурсы приведены ссылки на документацию по серверу и JDBC, которая поможет исследовать примеры кода и научиться создавать эффективные хранимые процедуры и JDBC-приложения. Данная статья призвана помочь переместить сложную бизнес-логику в хранимые процедуры и экспортировать их из JDBC-приложения.

Ресурсы

Научиться

Получить продукты и технологии

Комментарии

developerWorks: Войти

Обязательные поля отмечены звездочкой (*).


Нужен IBM ID?
Забыли Ваш IBM ID?


Забыли Ваш пароль?
Изменить пароль

Нажимая Отправить, Вы принимаете Условия использования developerWorks.

 


Профиль создается, когда вы первый раз заходите в developerWorks. Информация в вашем профиле (имя, страна / регион, название компании) отображается для всех пользователей и будет сопровождать любой опубликованный вами контент пока вы специально не укажите скрыть название вашей компании. Вы можете обновить ваш IBM аккаунт в любое время.

Вся введенная информация защищена.

Выберите имя, которое будет отображаться на экране



При первом входе в developerWorks для Вас будет создан профиль и Вам нужно будет выбрать Отображаемое имя. Оно будет выводиться рядом с контентом, опубликованным Вами в developerWorks.

Отображаемое имя должно иметь длину от 3 символов до 31 символа. Ваше Имя в системе должно быть уникальным. В качестве имени по соображениям приватности нельзя использовать контактный e-mail.

Обязательные поля отмечены звездочкой (*).

(Отображаемое имя должно иметь длину от 3 символов до 31 символа.)

Нажимая Отправить, Вы принимаете Условия использования developerWorks.

 


Вся введенная информация защищена.


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=40
Zone=Information Management
ArticleID=855696
ArticleTitle=Хранимые процедуры, используемые в вызываемых и подготовленных операторах
publish-date=01212013