Лекция №1: Стандарты языка sql

Вид материала

Лекция

Содержание Лекция №9 : Встроенный SQL Exec sql begin declare section Exec sql end declare section Основная программа Переменные во встроенном SQL Exec sql begin declare section Применение INTO-переменных Переменные связи Явный курсор Объявление курсора Обработка NULL-значений Exec sql begin declare section Позиционированные операторы Обработка ошибок EXEC SQL WHENEVER SQLERROR GOTO Err_1 OCCI-интерфейс для Oracle Environment::terminateEnvironment (env)

Подобный материал:

• Задачи курса Основы языка sql (и его расширения, t-sql, используемого sql server 2000), 22.95kb.
• Отчёт по курсовой работе на тему «Лабораторный практикум по изучению языка структурированных, 1960.59kb.
• Бобровски С. Oracle7 и вычисления клиент/сервер, 1004.32kb.
• Тема «Введение в язык sql», 148.85kb.
• Уважаемые пользователи босс-кадровик на платформе Microsoft, 34.05kb.
• Установка sql express 2005, 24.56kb.
• Здопомогою мови sql можна створювати запити до реляційних баз даних (таких як Access),, 276.28kb.
• Программа курса: Модуль Краткий обзор sql server Что такое сервер sql server Интегрирование, 35.73kb.
• Курс лекций "Базы данных и субд" Логинова С. А. Лекция 13. Язык sql. Команды dml. Команды, 133.93kb.
• Курс 2778. Создание запросов на языке Microsoft sql server 2005 Transact-sql. Курс, 16.57kb.

Лекция №9 : Встроенный SQL

Статический SQL

Статический SQL встраивается в виде препроцессорной обработки в традиционные языки программирования.

Операторы SQL обрабатываются прекомпилятором.

В SQL-операторах могут использоваться переменные из прикладной программы.


Операторы:

DECLARE CURSOR определяет запрос
OPEN и CLOSE начинает и завершает процесс обработки.


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

main() // Включение структуры для обработки ошибок {    EXEC SQL INCLUDE SQLCA; // Объявление хост-переменных (связи и INTO-переменные)      EXEC SQL BEGIN DECLARE SECTION;
int NumIndID;      int NumID;	// Эти переменные      // указываются после символа :
char Sal[10];      EXEC SQL END DECLARE SECTION; // Обработка ошибок      EXEC SQL WHENEVER SQLERROR GOTO err_1;      EXEC SQL WHENEVER NOT FOUND GOTO err_2; // Запрос параметров printf ("Type individual number: "); scanf("%d",&NumIndID); // Выполнение SQL запроса EXEC SQL SELECT NumID, Sal FROM tbl1      WHERE NumIndID =: NumIndID      INTO :NumID, :Sal; // Отображение результата      std::cout<< "Number: "<     exit(0); err_1:      std::cout<<'' SQLERROR'';      exit(1); err_2:      std::cout<<'' NOT FOUND";      exit(1); }

Типы данных преобразуются автоматически для каждой СУБД.

СУБД возвращает информацию об ошибках через специальные переменные: структуру SQL Communication AREA (SQLCA), переменную SQLSTATE или SQLCODE.

Теперь рассмотрим более подробно синтаксис языка встроенного SQL.

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

• встраивание в код некоторого языка программирования SQL-операторов (статический SQL);
  
• формирование в процессе выполнения программы на некотором языке программирования кода SQL-операторов и дальнейшего их выполнения (динамический SQL);
  
• вызов из программ, выполненных на других языках программирования, SQL-модулей, которые представляют собой код на языке SQL;
  
• использование API (Application Programming Interface), позволяющего реализовывать работу с базой данных через предоставляемый набор функций. API может быть целевым, предоставленным производителем коммерческой СУБД для работы именно с этой базой данных, или межплатформенным, реализующим унифицированные средства доступа к СУБД различных производителей. К такому API относятся ODBC (Open DataBase Connectivity) и SQL/CLI (SQL Call Level Interface).
  

Основная программа

Основной программой, или HOST-программой, называется программа, в которую встраиваются SQL-операторы.

Встраиваемый SQL-оператор указывается после фразы EXEC SQL.

Стандартом SQL-92 предусмотрена возможность встраивания SQL-операторов в следующие языки программирования: C, Pascal, Java (SQLJ), Ada, Cobol, Fortran, PL/1, M.

На следующей схеме изображен процесс выполнения программы, содержащей операторы встроенного SQL.



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

Таким образом, выполнение SQL-оператора было последовательно заменено на вызов внешней процедуры, которая затем была связана с библиотекой СУБД. Поэтому на этапе выполнения библиотечные вызовы будут передаваться непосредственно СУБД.

При выполнении программы со встроенным SQL могут быть использованы привилегии как выполняющего, так и владеющего пользователя (того, кто создал выполнимый файл). В современных коммерческих СУБД, как правило, относительно хранимых в базе данных модулей, существует возможность назначения привилегии EXECUTE таким образом, чтобы при выполнении модуля использовались привилегии его владельца.

Переменные во встроенном SQL

Во встроенном SQL можно использовать переменные основного языка программирования. Они применяются:

• в выражениях;
  
• как INTO-переменные;
  
• как переменные связи (bind-переменные);
  
• как индикаторные переменные.
  

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

Перед тем как использовать такую переменную, она должна быть объявлена в разделе объявления SQL-переменных. Объявление SQL-переменных указывается между парой операторов EXEC SQL BEGIN DECLARE SECTION; и EXEC SQL END DECLARE SECTION;

В основной программе может быть произвольное число разделов с объявляемыми переменными, но они могут быть указаны только в тех местах, в которых синтаксис основного языка допускает выполнять объявление переменных. Внутри операторов EXEC SQL BEGIN DECLARE SECTION и EXEC SQL END DECLARE SECTION синтаксис объявления переменных также соответствует языку программирования, в который встраиваются SQL-операторы.


Например:

// Код для объявления переменных на языке С++:
     EXEC SQL BEGIN DECLARE SECTION;
     int var1;
     int var2;
     char var3[10];
     EXEC SQL END DECLARE SECTION;
// Код для объявления переменных на языке Pascal:
     EXEC SQL BEGIN DECLARE SECTION      var
     var1: integer;
     var2: integer;
     var3: array (1..10) of char;
     EXEC SQL END DECLARE SECTION;

Применение INTO-переменных

INTO-переменные служат для извлечения данных из результирующего набора в переменные основного языка программирования. Какая бы технология доступа к БД ни использовалась в приложении, после формирования результирующего набора данные для дальнейшей обработки (изменения, отображения, печати и т.п.) всегда должны быть извлечены в переменные, с которыми может работать основной язык программирования. Во встроенном SQL оператор SELECT сразу может указать имена переменных, в которые будут занесены результаты запроса. Такие переменные называются INTO-переменными.

Для использования INTO-переменных существуют следующие ограничения:

• результирующий набор гарантированно должен возвращать только одну строку;
  
• тип каждой INTO-переменной должен соответствовать типу столбца, значение которого записывается в эту переменную.
  

Результирующий набор будет гарантированно возвращать только одну строку в следующих случаях:

• при использовании в предикате значения поля, являющегося уникальным в силу объявления его как PRIMARY KEY или UNIQUE;
  
• при агрегировании данных всей таблицы, когда в списке полей указывается агрегирующая функция, а фраза GROUP BY отсутствует;
  
• если структура используемых таблиц и синтаксис оператора SELECT однозначно определяют возвращаемую строку.
  

Например:

EXEC SQL SELECT f1,f2,f3 FROM tbl1 INTO :var1, var2, var3 WHERE f1=1;

Имена INTO-переменных могут совпадать с именами полей, так как это разные идентификаторы, отличающиеся наличием у INTO-переменных символа двоеточие.

Переменные связи Переменные связи (bind-переменные) служат для передачи значений в СУБД. Эти переменные могут использоваться во фразе WHERE для вычисления условия, в операторах INSERT и DELETE для определения устанавливаемых значений. Переменные связи, также как и INTO-переменные, перед применением должны быть предварительно объявлены. Переменные связи при указании их в SQL-операторе предваряются символом двоеточие. Например: EXEC SQL INSERT INTO tbl2 (f1,f2,f3) VALUES (:f1,f2,f3); Курсоры Под курсором, как правило, понимают получаемый при выполнении запроса результирующий набор и связанный с ним указатель текущей записи. Курсор - это объект, связанный с определенной областью памяти. Существуют явные и неявные курсоры. Явный курсор имеет имя и перед использованием должен быть объявлен. Неявный курсор создается автоматически и его нельзя повторно открыть без перекомпиляции оператора запроса. Объявление курсора выполняется оператором DECLARE CURSOR, в котором фраза FOR определяет запрос, ассоциируемый с данным курсором. Например, оператор EXEC SQL DECLARE c1CURSOR 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-значениями предусмотрены индикаторные переменные, которые могут использоваться для: определения извлекаемого NULL-значения; внесения NULL-значения в таблицу; фиксирования усекаемых строк. Если в результате выполнения оператора FETCH или оператора SELECT (возвращающего одну строку) извлекаемые данные принимают значение NULL, то, во-первых, считается, что SQL-оператор выполнен с ошибкой, а во-вторых, в INTO-переменную будет записано значение, отличное от NULL (зависит от типа переменной). Для предотвращения таких ситуаций применяются индикаторные переменные, указываемые после INTO-переменной через символ двоеточия (или INDICATOR:). Если индикаторная переменная принимает отрицательное значение, значит, столбец содержит значение NULL. По умолчанию до выполнения оператора индикаторной переменной присваивается значение 0. Например: EXEC SQL BEGIN DECLARE SECTION; int indf2; int indf3; EXEC SQL END DECLARE SECTION; EXEC SQL FETCH c1 INTO :f1,:f2 INDICATOR:indf2, :f3:indf3; if ((indf2<0) OR (indf3<0)) then std::cout<< "NULL value"; Если в операторе INSERT или UPDATE требуется указать, что используемая переменная связи может содержать значение NULL, то после этой переменной через двоеточие следует записать индикаторную переменную. В этом случае при отрицательном значении индикаторной переменной в базу данных будет занесено значение NULL. При фиксировании усекаемых строк в индикаторную переменную записывается первоначальная длина строки, а сама строка записывается переменную основного языка с усечением. Позиционированные операторы Для обновления курсора в операторах DELETE и UPDATE может использоваться фраза WHERE CURRENT OF, определяющая, что действие относится к текущей строке курсора. Такой оператор называется позиционированным, и к нему предъявляются следующие требования: и курсор, и оператор должны использовать только одну и ту же таблицу; в запросе, используемом для создания курсора, не должно быть фраз UNION и ORDER BY; курсор должен удовлетворять критериям обновляемого курсора (например, не применять агрегирующие функции). Например: 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-операторе: переменная SQLSTATE имеет тип char(5) и содержит информацию о классе (два старших символа) и подклассе (3 младших символа), описывающих состояние выполненного SQL-оператора; переменная SQLCODE имеет целочисленный тип и содержит код завершения последнего выполненного SQL-оператора. Как и другие переменные, используемые во встроенном SQL, переменные SQLSTATE и SQLCODE предварительно должны быть объявлены. Однако переменная SQLCODE может быть создана по умолчанию, если нет объявления другой переменной, получающей информацию о завершении выполнения SQL-оператора. После выполнения SQL-оператора данные о статусе и коде выполнения автоматически записываются СУБД в эти переменные. Статус выполнения SQL-оператора может быть определен как: успешное завершение. Соответствует в SQLSTATE коду '00000' (класс '00'). SCLCODE в этом случае тоже равна 0; успешное завершение с предупреждением. Класс состояния '02' в SQLSTATE определяет предупреждение 'NOT FOUND'; класс состояния '01'указывает предупреждение, более точно специфицируемое подклассом; завершение с ошибкой. Классы '03' и последующие в SQLSTATE описывают различные ошибочные ситуации (подклассы специфицируют как стандартные ситуации, так и определяемые приложением). Предупреждение 'NOT FOUND' указывает, что SQL-оператор не содержал ошибки, но не вернул ожидаемого результата. Например, сформированный результирующий набор не содержит ни одной строки, или оператор UPDATE не изменил ни одной строки. Переменные SCLCODE и 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 #include using namespace oracle::occi;   // Библиотека располагается   // в каталоге [ORACLE_HOME]\oci\include   // Для доступа к пространству имен 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<     conn->terminateStatement (stmt);   // Освобождение оператора   }   // Добавление строки в таблицу   void insertRow ()   {     string sqlStmt = "INSERT INTO tbl1 VALUES (6, 'ABC')";     stmt = conn->createStatement (sqlStmt);     try{     stmt->executeUpdate ();     }catch(SQLException ex)     {  cout<    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<    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<    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<    }     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 ();   occi ->insertRowBind(6, "ABC");   occi1-> displayResultSet ();   occi ->deleteRow (6, "ABC");   occi1->updateRow (4, "EEE");   occi1-> displayResultSet ();   delete (occi1); }   // Отображение всех записей   // Вставка строки   // Вставка строки с использованием   // динамического связывания   // Удаление строки   // Обновление строки   // Освобождение OCCI-объекта