Встроенный SQL

Переменные связи

Переменные связи (bind-переменные) служат для передачи значений в СУБД. Эти переменные могут использоваться во фразе WHERE для вычисления условия, в операторах INSERT и DELETE для определения устанавливаемых значений.

Переменные связи, так же как и INTO-переменные, перед применением должны быть предварительно объявлены. Переменные связи при указании их в SQL-операторе предваряются символом "двоеточие".

Например:

EXEC SQL INSERT INTO tbl2 (f1,f2,f3) 
         VALUES (:f1,:f2,:f3);

Курсоры

Под курсором, как правило, понимают получаемый при выполнении запроса результирующий набор и связанный с ним указатель текущей записи. Курсор - это объект, связанный с определенной областью памяти. Существуют явные и неявные курсоры.

Явный курсор имеет имя и перед использованием должен быть объявлен. Неявный курсор создается автоматически и его нельзя повторно открыть без перекомпиляции оператора запроса.

Объявление курсора выполняется оператором DECLARE CURSOR, в котором фраза FOR определяет запрос, ассоциируемый с данным курсором.

Например, оператор

EXEC SQL DECLARE c1 CURSOR FOR 
     SELECT f1,f2,f3 FROM tbl1 WHERE f2>100;

создает курсор c1 на базе таблицы tbl1. При объявлении курсора выполнения запроса не происходит. Выполнение запроса и создание курсора инициируется оператором OPEN CURSOR.

Например, оператор

EXEC SQL OPEN CURSOR с1;

создаст курсор, выполнив определенный в нем оператор SELECT.

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

Для извлечения данных из курсора используется оператор FETCH

Например, оператор

EXEC SQL FETCH c1 INTO :f1,:f2,:f3;

извлекает значения текущей строки курсора в INTO-переменные.

Для освобождения памяти, выделенной под курсор, его следует закрыть, выполнив оператор CLOSE CURSOR.

Например:

EXEC SQL CLOSE CURSOR с1;

Обработка NULL-значений

Для работы с NULL-значениями предусмотрены индикаторные переменные, которые могут использоваться для:

Если в результате выполнения оператора FETCH или оператора SELECT (возвращающего одну строку) извлекаемые данные принимают значение NULL, то, во-первых, считается, что SQL-оператор выполнен с ошибкой, а во-вторых, в INTO-переменную будет записано значение, отличное от NULL (зависит от типа переменной). Для предотвращения таких ситуаций применяются индикаторные переменные, указываемые после INTO-переменной через символ двоеточия (или INDICATOR:). Если индикаторная переменная принимает отрицательное значение, значит, столбец содержит значение NULL. По умолчанию до выполнения оператора индикаторной переменной присваивается значение 0.

Например:

Если в операторе INSERT или UPDATE требуется указать, что используемая переменная связи может содержать значение NULL, то после этой переменной через двоеточие следует записать индикаторную переменную. В этом случае при отрицательном значении индикаторной переменной в базу данных будет занесено значение NULL.

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

Позиционированные операторы

Для обновления курсора в операторах DELETE и UPDATE может использоваться фраза WHERE CURRENT OF, определяющая, что действие относится к текущей строке курсора. Такой оператор называется позиционированным , и к нему предъявляются следующие требования:

Например:

EXEC SQL DECLARE c1 CURSOR FOR SELECT f1,f2 FROM tbl1;
EXEC SQL OPEN CURSOR c1;
EXEC SQL FETCH c1 INTO :f1,:f2;
EXEC SQL UPDATE tbl1 SET f2=f2*1.3 
         WHERE CURRENT OF c1;

Позиционированный оператор DELETE удобно использовать для удаления из таблицы группы строк, предварительно выбранных в курсор.

Обработка ошибок

Стандартом SQL-92 определено две переменных, которые позволяют получать информацию о выполняемом SQL-операторе:

Как и другие переменные, используемые во встроенном SQL, переменные SQLSTATE и SQLCODE предварительно должны быть объявлены. Однако переменная SQLCODE может быть создана по умолчанию, если нет объявления другой переменной, получающей информацию о завершении выполнения SQL-оператора.

После выполнения SQL-оператора данные о статусе и коде выполнения автоматически записываются СУБД в эти переменные.

Статус выполнения SQL-оператора может быть определен как:

Предупреждение 'NOT FOUND' указывает, что SQL-оператор не содержал ошибки, но не вернул ожидаемого результата. Например, сформированный результирующий набор не содержит ни одной строки, или оператор UPDATE не изменил ни одной строки.

Переменные SQLCODE и SQLSTATE очень часто используются в операторе while для завершения цикла и для выхода из него в случае возникновения ошибки.

Встроенный SQL поддерживает оператор WHENEVER, определяющий действия, которые будут выполнены при возникновении описанной ситуации. Этот оператор следует указывать до выполнения того SQL-оператора, чья обработка ошибок будет "перехватываться".

Оператор WHENEVER влияет на все выполняемые SQL-операторы.

Например:

EXEC SQL WHENEVER SQLERROR GOTO Err_1;
EXEC SQL WHENEVER NOT FOUND CONTINUE;
EXEC SQL WHENEVER SQLWARNING CONTINUE;
EXEC SQL OPEN CURSOR c1;
EXEC SQL FETCH c1 INTO :f1,:f2,:f3;
:
err_1: std::cout<<'' SQLERROR'';
:
EXEC SQL CLOSE CURSOR c1;

Оператор WHENEVER определяет или метку, на которую будет выполнен переход при возникновении ошибки, или действие типа CONTINUE (продолжение выполнения), или процедуру обработки ошибок.

OCCI-интерфейс для Oracle

Кроме использования встроенного SQL, реализующего унифицированный стандартный механизм доступа к любым базам данных из практически любых языков программирования, существуют механизмы, ориентированные на конкретные базы данных и на конкретные языки программирования. Одним из таких механизмов является OCCI-интерфейс для работы с СУБД Oracle.

OCCI-интерфейс (Oracle C++ Call Interface) - это API, предоставляющее разработчику приложений С++ средства доступа к базе данных Oracle, включающие методы подключения к базе данных, методы для получения метаданных и методы для извлечения и изменения данных.

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

OCCI-интерфейс реализован как динамическая библиотека, которая может быть загружена при выполнении приложения-клиента.

Эти элементы необходимы для неявного встраивания операторов SQL или PL/SQL в основной язык программирования.

На следующей схеме представлен процесс получения выполнимого приложения с использованием OCCI-библиотеки.

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

Пример:

#include <iostream.h>
#include <occi.h>           // Библиотека располагается
                                  // в каталоге [ORACLE_HOME]\oci\include
using namespace oracle::occi;     // Для доступа к пространству имен occi


using namespace std;
class occi_select
{
  private:
  Environment *env;              // Переменная окружения
  Connection *conn;              // Переменная соединения
  Statement *stmt;               // Переменная оператора
  public:
  occi_select (string user, string passwd, string db)         // Конструктор
  {                                                           // Соединение с БД
    env = Environment::createEnvironment (Environment::DEFAULT);
    conn = env->createConnection (user, passwd, db);
  }


  ~occi_select ()                // Деструктор
  {
    env->terminateConnection (conn);         // Освобождение соединения
    Environment::terminateEnvironment (env);
  }
  // Добавление строки с динамическим связыванием
  void insertRowBind(int c1, string c2)
  {
    string sqlStmt = "INSERT INTO tbl1 VALUES (:x, :y)";
    stmt=conn->createStatement (sqlStmt);
    try{
    stmt->setInt (1, c1);                   // Определение значения для первого столбца
    stmt->setString (2, c2);
    stmt->executeUpdate ();
    }catch(SQLException ex)
    {  cout<<ex.getMessage() << endl;  }
    conn->terminateStatement (stmt);       // Освобождение оператора
  }
  // Добавление строки в таблицу
  void insertRow ()
  {
    string sqlStmt = "INSERT INTO tbl1 VALUES (6, 'ABC')";
    stmt = conn->createStatement (sqlStmt);
    try{
    stmt->executeUpdate ();
    }catch(SQLException ex)
    {  cout<<ex.getMessage() << endl;  }
    conn->terminateStatement (stmt);
  }
// Изменение строки
  void updateRow (int c1, string c2)
  {
    string sqlStmt =
      "UPDATE tbl1 SET f1 = :x WHERE f2 = :y";
    stmt = conn->createStatement (sqlStmt);
    try{
    stmt->setString (1, c2);
    stmt->setInt (2, c1);
    stmt->executeUpdate ();
    }catch(SQLException ex)
    {  cout<<ex.getMessage() << endl;  }
    conn->terminateStatement (stmt);
  }
// Удаление строки
  void deleteRow (int c1, string c2)
  {
    string sqlStmt =
      "DELETE FROM tbl1 WHERE f1= :x AND f2 = :y";
    stmt = conn->createStatement (sqlStmt);
    try{
    stmt->setInt (1, c1);
    stmt->setString (2, c2);
    stmt->executeUpdate ();
    }catch(SQLException ex)
    {  cout<<ex.getMessage() << endl;  }
    conn->terminateStatement (stmt);
  }
// Отображение всех строк
  void displayResultSet ()
  {
    string sqlStmt = "SELECT f1, f2 FROM tbl1";
    stmt = conn->createStatement (sqlStmt);
    ResultSet *rs = stmt->executeQuery ();
  try{
  while (rs->next ())
  {
      cout << "f1: " << rs->getInt (1) << " f2: "
        << rs->getString (2) << endl;
    }
    }catch(SQLException ex)
    {
      cout<<"Error number: "<< ex.getErrorCode() << endl;
      cout<<ex.getMessage() << endl;
    }
    stmt->closeResultSet (rs);                  // Освобождение результирующего набора
    conn->terminateStatement (stmt);
  }
};                                              // Конец кода класса occi_select
int main (void)
{
  string user = "SCOTT";
  string passwd = "TIGER";
  string db = "";
  occi_select *occi1 = new occi_select (user, passwd, db);


  occi1-> displayResultSet ();             // Отображение всех записей
  occi1->insertRow ();                     // Вставка строки
  occi1-> displayResultSet ();
  occi1->insertRowBind(6, "ABC");          // Вставка строки с использованием
                                           // динамического связывания
  occi1-> displayResultSet ();
  occi1->deleteRow (6, "ABC");             // Удаление строки
  occi1->updateRow (4, "EEE");             // Обновление строки
  occi1-> displayResultSet ();
  delete (occi1);                          // Освобождение OCCI-объекта
}