Wykład 5

Zaawansowane programowanie na serwerze bazy danych

Streszczenie

Zaprezentujemy szereg dodatkowych możliwości programowania na serwerze bazy danych Oracle.

Do tej pory wykonywane instrukcje SQL musiały być znane z góry dla całej skompilowanej jednostki programowej. Oracle dostarcza możliwości ustalania tekstu wykonywanej instrukcji dynamicznie. Dwie dostępne opcje są przedstawione w punkcie 5.1.

Sytuacją, którą trudno zrealizować za pomocą konwencjonalnych środków bazodanowych, jest konieczność planowania i wykonywania w określonym czasie wcześniej przygotowanych fragmentów kodu. Zadania takie są realizowane w ramach automatycznie otwieranych sesji na koncie użytkownika, który zlecił te zadania. Określanie terminowych zadań do wykonywania na bazie danych jest realizowane za pomocą pakietu DBMS_JOB omawianego w punkcie 5.2.

Przy wykonywaniu aplikacji po stronie serwera jest ona ograniczona do korzystania z danych zapisanych w bazie danych, do których aplikacja ma uprawnienia. Nie ma ona możliwości innej komunikacji niż przez same dane w bazie danych z innymi aplikacjami działającymi na tym samym serwerze bazy danych bądź ogólniej na tym samym komputerze. Może się więc okazać wygodna możliwość komunikacji z innymi sesjami otwartymi na tym samym serwerze bazy danych, jak również z innymi programami poprzez pliki w systemie plików komputera, na którym znajduje się serwer bazy danych. Do realizacji tych celów służą odpowiednio pakiety DBMS_PIPE i UTL_FILE omawiane odpowiednio w punktach 5.3 i 5.4.

W punkcie 5.5 pokażemy, w jaki sposób można w aplikacjach po stronie serwera korzystać z bibliotek podprogramów kompilowanych niezależnie. Do tego celu służy w Oracle obiekt bazy danych o nazwie biblioteka (ang. Library).

W punkcie 5.6 wspomnimy o możliwości przechowywania kodu języka programowania Java w bazie danych i wykonywania go na serwerze bazy danych.

Zawartość punktów 5.7 do 5.9 stanowi dodatek wyjaśniający zagadnienie dostępu do bazy danych z programów zewnętrznych za pomocą odpowiednio metody osadzania instrukcji SQL oraz metody korzystania z pomocniczych bibliotek (jak JDBC) zapewniających dostęp do bazy danych.
 

5.1 Dynamiczny SQL

Do tej pory mieliśmy do czynienia wyłącznie z sytuacją, w której instrukcje SQL są w całości znane w chwili kompilacji jednostki programowej. W praktyce są jednak sytuacje, gdy w chwili kompilacji programu nie znamy pełnej postaci instrukcji SQL. Szczegóły mogą być ustalane w trakcie działania programu, jak np. z której tabeli wyświetlić wiersze.

Oto prosty przykład ze Standardu SQL, w którym realizowana jest nie określona w chwili kompilacji programu zewnętrznego instrukcja SQL:

SQLsource VARCHAR2(5000);
SQLprepped VARCHAR2(5000);
.....
/* Wczytaj na zmienną SQLsource tekst instrukcji SQL, którą chcesz wykonać */
EXEC SQL PREPARE SQLprepped FROM SQLsource;  -- skompiluj
EXEC SQL EXECUTE SQLprepped;                 -- wykonaj
......

Po wykonaniu instrukcji SQL na systemowych zmiennych SQLSTATE i SQLCODE znajduje się informacja o statusie wykonania tej instrukcji.

Standard języka SQL dostarcza zbioru instrukcji jak SQL PREPARE i SQL EXECUTE noszących razem nazwę dynamicznego SQL.

Oracle ma swoją wersję dynamicznego SQL zrealizowaną za pomocą pakietu PL/SQL o nazwie DBMS_SQL. Najpierw rozważymy prosty przykład. Załóżmy, że chcemy wykonać instrukcję

DROP TABLE nazwa_tabeli;

-- nazwa_tabeli jest parametrem
id_kursora INTEGER;
wart INTEGER;
polecenie VARCHAR2(250);
BEGIN
   polecenie := 'DROP TABLE '|| nazwa_tabeli;
   id_kursora := DBMS_SQL.OPEN_CURSOR;
   DBMS_SQL.PARSE(id_kursora, polecenie, NATIVE);
   wart := DBMS_SQL.EXECUTE(id_kursora);
   DBMS_SQL.CLOSE_CURSOR(id_kursora);
   ....

• c jest identyfikatorem kursora,
• statement jest napisem instrukcji SQL do sparsowania,
• language_flag jest parametrem wskazującym stosowaną metodę parsowania: V6, V7, NATIVE (metoda stosowana w bieżącej bazie danych).

Następująca procedura powoduje wykonanie instrukcji SQL podanej jako jej parametr napis. Może to być dowolna instrukcja SQL, a więc na przykład instrukcja CREATE TABLE.

CREATE OR REPLACE PROCEDURE Wykonaj(napis IN varchar2) AS
kursor INTEGER;
ret INTEGER;
BEGIN
   kursor := DBMS_SQL.OPEN_CURSOR;

   DBMS_SQL.PARSE(kursor, napis, DBMS_SQL.native);
   ret := DBMS_SQL.EXECUTE(kursor);
   DBMS_SQL.CLOSE_CURSOR(kursor);
END;

CREATE PROCEDURE Insert_into_table (
table_name VARCHAR2,
deptnumber NUMBER,
deptname VARCHAR2,
location VARCHAR2) IS
cur_hdl INTEGER;
stmt_str VARCHAR2(200);
rows_processed BINARY_INTEGER;
BEGIN
   stmt_str := 'INSERT INTO ' || table_name ||' VALUES (:deptno, :dname, :loc)';
   -- wyznaczenie instrukcji SQL do wykonania
   cur_hdl := DBMS_SQL.OPEN_CURSOR;   -- otwarcie kursora
   DBMS_SQL.PARSE(cur_hdl, stmt_str, DBMS_SQL.native);   -- sparsowanie instrukcji INSERT
  DBMS_SQL.BIND_VARIABLE (cur_hdl, ':deptno', deptnumber);
  DBMS_SQL.BIND_VARIABLE (cur_hdl, ':dname', deptname);
  DBMS_SQL.BIND_VARIABLE (cur_hdl, ':loc', location);
  -- związanie zmiennych w instrukcji INSERT
  rows_processed := DBMS_SQL.EXECUTE(cur_hdl);   -- wykonanie INSERT
  DBMS_SQL.CLOSE_CURSOR(cur_hdl);   -- zamknięcie kursora
END;

• c jest identyfikatorem kursora,
• name jest nazwą zmiennej wiązania,
• value jest wartością przypisywaną zmiennej wiązania.

Przy wykonywaniu zapytań schemat postępowania jest trochę bardziej skomplikowany. Mianowicie obejmuje on:

1. określenie kolumn, którym mają zostać przypisane wartości przekazywane z bazy danych przez SELECT (dla każdej wynikowej kolumny wywołanie procedury DEFINE_COLUMN);
2. wykonanie instrukcji SELECT (procedura EXECUTE);
3. sprowadzenie wierszy (procedura FETCH_ROWS);
4. odczytanie wartości w kolumnie (procedura COLUMN_VALUE).

Oto sygnatury tych podprogramów:

gdzie

• c jest identyfikatorem kursora,
• pozycja - jest względnym numerem kolumny na liście SELECT,
• kolumna - jest wartością definiowanej kolumny. Typ danych tej wartości determinuje typ definiowanej kolumny przez DEFINE_COLUMN,
• wart jest wartością przypisywaną zmiennej.

Oto procedura zawierająca pętlę LOOP wypisująca na ekranie wiersze tabeli przekazanej jako parametr tabela zawierającej kolumny id, name i birthdate.

PROCEDURE Process(tabela IN VARCHAR2) AS
id NUMBER;
name VARCHAR2(30);
birthdate DATE;
kursor INTEGER;
ignore INTEGER;
BEGIN
  kursor := DBMS_SQL.OPEN_CURSOR;
  DBMS_SQL.PARSE(source_cursor, 'SELECT Id, Name, Birthdate FROM ' || tabela, DBMS_SQL.NATIVE);
  DBMS_SQL.DEFINE_COLUMN(kursor, 1, id);
  DBMS_SQL.DEFINE_COLUMN(kursor, 2, name);
  DBMS_SQL.DEFINE_COLUMN(kursor, 3, birthdate);
  ignore := DBMS_SQL.EXECUTE(kursor);
  LOOP
    IF DBMS_SQL.FETCH_ROWS(kursor)>0 THEN
       DBMS_SQL.COLUMN_VALUE(kursor, 1, id);
       DBMS_SQL.COLUMN_VALUE(kursor, 2, name);
       DBMS_SQL.COLUMN_VALUE(kursor, 3, birthdate);
       DBMS_OUTPUT.PUT_LINE(TO_CHAR(id)||’,’||name||’,’||TO_CHAR(birth_date));
    ELSE
      EXIT;
    END IF;
  END LOOP;
  DBMS_SQL.CLOSE_CURSOR(kursor);
END;

Instrukcja:

Dwa poniższe przykłady ilustrują użycie naturalnego dynamicznego SQL. Pierwszy przykład dotyczy wykonywania instrukcji innych niż SELECT.

DECLARE
sql_stmt VARCHAR2(100);
plsql_block VARCHAR2(200);
my_deptno NUMBER(2) := 50;
my_dname VARCHAR2(15) := 'PERSON';
my_loc VARCHAR2(15) := 'DALLAS';
emp_rec emp%ROWTYPE;
BEGIN
  sql_stmt := 'INSERT INTO Dept VALUES (:1, :2, :3)';
  EXECUTE IMMEDIATE sql_stmt
  USING my_deptno, my_dname, my_loc;

  sql_stmt := 'SELECT * FROM Emp WHERE Empno = :id';
  EXECUTE IMMEDIATE sql_stmt INTO emp_rec USING 7788;
  EXECUTE IMMEDIATE 'DELETE FROM Dept WHERE Deptno = :n' USING my_deptno;
  EXECUTE IMMEDIATE 'CREATE TABLE Bonus (Id NUMBER, Am NUMBER)';
END;

DECLARE
TYPE EmpCurTyp IS REF CURSOR;
emp_cv EmpCurTyp;
emp_rec emp%ROWTYPE;
sql_stmt VARCHAR2(100);
my_job VARCHAR2(15) := 'CLERK';
BEGIN
  sql_stmt := 'SELECT * FROM Emp WHERE Job = :j';
  OPEN emp_cv FOR sql_stmt USING my_job;
  LOOP
    FETCH emp_cv INTO emp_rec;
    EXIT WHEN emp_cv%NOTFOUND;
    ... -- process record
  END LOOP;
  CLOSE emp_cv;
END;

Do zalet DBMS_SQL należą możliwość jego użycia w programach po stronie klienta, możliwość użycia dowolnej liczby zmiennych wiązania, wspomaganie wykonywania instrukcji SQL, których rozmiar jest większy niż 32kB oraz możliwość wielokrotnego użycia instrukcji SQL po początkowym przygotowaniu instrukcji przez procedurę DBMS_SQL.Parse.
 

5.2  Zlecanie zadań do wykonania - pakiet DBMS_JOB

Na ogół, kod PL/SQL jest wykonywany w ramach pewnej otwartej sesji. Można także zaplanować periodyczne wykonywanie kodu PL/SQL na serwerze bazy danych poza jakąkolwiek sesją. Tego typu złożone do wykonania zadania są realizowane na serwerze bazy danych za pomocą specjalnych procesów działających w tle o nazwie SNP.
 

DBMS_JOB.SUBMIT(   job OUT BINARY_INTEGER,   what IN VARCHAR2,   next_date IN DATE DEFAULT SYSDATE,   interval IN VARCHAR2 DEFAULT 'null',   no_parse IN BOOLEAN DEFAULT FALSE)

gdzie:

• job - numer identyfikujący przekazane zadanie,
• what – procedura PL/SQL do periodycznego wykonywania,
• next_date - kiedy ma być wykonane zadanie,
• interval - albo null (tylko jedno wykonanie) albo następny punkt czasowy,
• no_parse - parsowanie dopiero przy pierwszym wykonaniu.

Przykład

Generowanie statystyk dla optymalizatora (raz na dzień).

VARIABLE jobno number;
BEGIN
  DBMS_JOB.SUBMIT(:jobno,
        'dbms_ddl.analyze_object(''TABLE'', ''KASA'', ''KONTA'', ''ESTIMATE'', NULL, 50);',
          SYSDATE, 'SYSDATE + 1');
  COMMIT;
END;
/
PRINT jobno

JOB_QUEUE_PROCESSES = n

5.3 Komunikacja z innymi sesjami - pakiet DBMS_PIPE

Pakiet DBMS_PIPE umożliwia komunikację między dwiema lub więcej sesjami w tej samej instancji Oracle poprzez dostęp w obu sesjach do wspólnego potoku (ang. pipe). Potoki nie są objęte mechanizmem transakcji. Potok jest albo prywatny albo publiczny; albo definiowany jawnie albo niejawnie – gdy pojawi się do niego odwołanie.

Każdy publiczny potok działa asynchronicznie. Dowolna liczba użytkowników może wprowadzać do niego komunikaty pod warunkiem posiadania uprawnienia EXECUTE do pakietu DBMS_PIPE. Dowolny użytkownik może pobrać komunikat z potoku i wówczas komunikat zostaje usunięty z potoku i nie może już w szczególności zostać odczytany przez innych użytkowników.

Oto funkcja tworząca nowy potok:

Przed wprowadzeniem komunikatu do potoku sesja buduje pełny komunikat z części w swoim lokalnym buforze za pomocą procedury:

Następnie za pomocą instrukcji:

Usunięcie potoku następuje za pomocą funkcji:

Potoku można użyć do wprowadzania do niego komunikatów np. pochodzących z procesu debugowania. Zewnętrzny program w C może odczytywać te komunikaty i wyświetlać lub drukować.

CREATE OR REPLACE PROCEDURE Debug(msg VARCHAR2) AS
status NUMBER;
BEGIN
  DBMS_PIPE.PACK_MESSAGE(LENGTH(msg));
  DBMS_PIPE.PACK_MESSAGE(msg);
  status := DBMS_PIPE.SEND_MESSAGE('plsql_debug');
  IF status != 0 THEN
    Raise_application_error(-20099, 'Błąd debug');
  END IF;
END Debug;

-- Początek nieskończonej pętli:
FOR (;;)
{
EXEC SQL EXECUTE
DECLARE
len INTEGER;
typ INTEGER;
sta INTEGER;
chr VARCHAR2(2000);
BEGIN
  chr := '';
  sta := DBMS_PIPE.RECEIVE_MESSAGE('plsql_debug');
  IF sta = 0 THEN
    DBMS_PIPE.UNPACK_MESSAGE(len);
    DBMS_PIPE.UNPACK_MESSAGE(chr);
  END IF;
  :status := sta; :retval := chr; :msg_length := len;
END;
END-EXEC;
IF (status == 0)
  printf("\n%.*s\n", msg_length, retval);
ELSE
  printf("sytuacja wyjątkowa o wartości %d\n", status);
}
}

5.4 Komunikacja przez system plików - pakiet UTL_FILE

Pakiet UTL_FILE umożliwia zapis i odczyt plików tekstowych z kodu PL/SQL wykonywanego przez serwer bazy danych. Wynikiem funkcji otwarcia pliku jest tak zwany uchwyt tego pliku będący rekordem następującego typu:

Procedura GET_LINE umieszcza w buforze buffer kolejny fragment pliku tekstowego o uchwycie file_handle.

PROCEDURE Trace(nazwa_pliku VARCHAR2, komunikat VARCHAR2)
IS
dir VARCHAR2(200);
filename VARCHAR2(200);
vText VARCHAR2(200);
file UTL_FILE.FILE_TYPE;
BEGIN
  dir := 'd:\orant\debug';
  BEGIN
    filename := nazwa_pliku;
    file := UTL_FILE.FOPEN(dir, filename, 'a');
  /* gdy plik o podanej nazwie nie istnieje, tworzony jest nowy plik i otwierany w trybie 'w' */
  EXCEPTION /* wyjątki przy FOPEN */
  WHEN UTL_FILE.Invalid_path THEN
    Raise_application_error(-20001,'niepoprawny katalog');
  WHEN others THEN
    Raise_application_error(-20002,'błąd w fopen');
END;
vText := TO_CHAR(Sysdate, 'DD.MM HH24:MI:SS')||'->'||msg;
BEGIN
  UTL_FILE.PUT_LINE(file, vText);
  EXCEPTION /* wyjątki przy PUT_LINE */
  WHEN UTL_FILE.Write_Error THEN
    Raise_application_error(-20003,'błąd OS podczas zapisu do pliku');
  WHEN others THEN
    Raise_application_error(-20004, 'niespodziewany błąd w PUT_LINE');
END;
BEGIN
  UTL_FILE.FCLOSE(file);
  EXCEPTION /* wyjątki przy fclose */
  WHEN UTL_FILE.Write_Error THEN
    Raise_application_error(-20005, 'błąd OS podczas zamykania pliku');
  WHEN others THEN
    Raise_application_error(-20006, 'niespodziewany błąd w FCLOSE');
END;
EXCEPTION /* wyjątki głównego bloku */
WHEN others THEN
  Raise_application_error(-20007, 'niespodziewany błąd: ' || sqlerrm);
END Trace;

UTL_FILE_DIR = c:\orant\plsql\kowalski
UTL_FILE_DIR = c:\orant\plsql\nowacki

5.5 Biblioteki (obiekty typu LIBRARY)

W kodzie PL/SQL można wykorzystywać podprogramy zewnętrznych bibliotek. W tym celu trzeba utworzyć obiekt biblioteki – rejestrując zewnętrzną bibliotekę na serwerze Oracle’a, a następnie należy utworzyć funkcję PL/SQL odpowiadającą funkcji zdefiniowanej w zewnętrznej bibliotece. Na przykład, instrukcja:

CREATE LIBRARY Odbc AS 'c:\windows\system\odbc.dll';

CREATE OR REPLACE FUNCTION SQL_Exec_direct(
-- wykonaj instrukcję SQL używając ODBC
   sql_handle BINARY_INTEGER,
   sql_stat VARCHAR2(2000),
   sql_length INTEGER)
RETURN VARCHAR2
AS EXTERNAL
LIBRARY ODBC
NAME SQLExecDirect
LANGUAGE C;

... polecenie := 'DELETE FROM Customers';
kod_wynikowy := SQL_Exec_direct(1, polecenie, LENGTH(polecenie));
/* wykonanie funkcji */

5.6 Java składowana w bazie danych

Kod Javy może być zapisywany w bazie danych oraz może być realizowany na serwerze bazy danych tak jak kod języka SQL – w ramach tej samej sesji i tej samej transakcji. Tworzenie połączenia z bazą danych jest realizowane za pomocą wbudowanego sterownika JDBC:

Connection conn = new OracleDriver().defaultConnection();

import java.sql.*;
import java.io.*;
import oracle.jdbc.driver.*;
public class GenericDrop {
public static void dropIt (String object_type, String object_name)
 throws SQLException {
   // Połącz się z bazą danych Oracle używając sterownika JDBC
   Connection conn = new OracleDriver().defaultConnection();
   // Zbuduj instrukcję SQL
   String sql = "DROP " + object_type + " " + object_name;
   try {
     Statement stmt = conn.createStatement();
     stmt.executeUpdate(sql);
     stmt.close();
    }
   catch (SQLException e) {System.err.println(e.getMessage());}
 }
}

Następny krok to załadowanie utworzonej klasy do bazy danych:

loadjava -user scott/tiger GenericDrop

Aby móc skorzystać z metody DropIt (statycznej metody w klasie GenericDrop), trzeba utworzyć dla niej procedurę lub funkcję, czyli inaczej mówiąc podać specyfikację jej wywołania. Specyfikacja wywołania określa, jakich typów danych SQL są argumenty i jaki jest typ wyniku (w przypadku funkcji).

CREATE OR REPLACE PROCEDURE DropIt(obj_type VARCHAR2, obj_name VARCHAR2)
AS LANGUAGE JAVA
NAME 'GenericDrop.dropIt(java.lang.String, java.lang.String)';

CALL DropIt('TABLE', 'Emp');

CREATE AND COMPILE JAVA SOURCE NAMED "Hello" AS
public class hello {
   public static String world() {
      return "Hello World"; }
};

CREATE OR REPLACE FUNCTION HelloWorld RETURN VARCHAR2 AS
LANGUAGE JAVA NAME 'hello.world() return java.lang.string';

myString VARCHAR2;
CALL HelloWorld() INTO :myString;
PRINT myString;

SELECT HelloWorld()
 FROM Dual;

5.7 Osadzony SQL

Jedną z metod dostępu do bazy danych z programu zewnętrznego jest osadzanie instrukcji SQL wprost w tekście programu języka. Nazwy zmiennych języka zewnętrznego w instrukcji osadzonego SQL muszą być poprzedzone dwukropkiem.

EXEC SQL BEGIN DECLARE SECTION;
SQLSTATE CHAR(5);
num_prac NUMBER(4);
EXEC SQL END DECLARE SECTION;
. . . . . . . . .
EXEC SQL DELETE FROM Emp WHERE Emp.Empno = :num_prac ;
IF SQLSTATE = '00000'
THEN . . . . . . ; /* operacja usuwania powiodła się */
ELSE . . . . . . ; /* wystąpił wyjątek */

Każdy program osadzonego SQL powinien zawierać zmienną zewnętrzną o nazwie SQLCODE lub SQLSTATE. Po wykonaniu każdej instrukcji SQL do programu na zmiennej SQLCODE lub SQLSTATE (lub obu) jest przekazywana wartość statusu wykonania instrukcji SQL. Zmienne zewnętrzne i kolumny SQL mogą mieć takie same nazwy.

Są dwie możliwości obsługi wyjątków:

1. Po każdym wykonaniu instrukcji SQL sprawdzać wartości zmiennych SQLSTATE i SQLCODE.

2. Użyć instrukcji:

EXEC SQL WHENEVER warunek akcja;

Każda kolejna instrukcja WHENEVER zastępuje poprzednią instrukcję WHENEVER dla danego warunku.

Wrócimy teraz do przykładu osadzonego SQL dla Oracle. Poniższy program jest napisany w języku Pro*C, czyli języku C rozszerzonym o instrukcje osadzonego SQL i PL/SQL. Deklarowane zmienne mogą być używane zarówno w instrukcjach C (bez dwukropków), jak i w instrukcjach SQL i PL/SQL (z dwukropkami). Wartości typu VARCHAR są w instrukcjach języka C traktowane w specjalny sposób – jako rekordy złożone z napisu i jego długości. Na przykład, deklaracja zmiennej

VARCHAR emp_name(15);

struct{
unsigned short len;
unsigned char arr(15);
} emp_name;

EXEC SQL BEGIN DECLARE SECTION
VARCHAR username(20);
VARCHAR password(20);
VARCHAR emp_name(15);
float salary;
float commission;
EXEC SQL END DECLARE SECTION;
EXEC SQL INCLUDE SQLca;
main(){
  /* Wczytaj w C wartości username i password */
  ........
  EXEC SQL WHENEVER SQLERROR GOTO SQLerror;
  EXEC SQL CONNECT :username IDENTIFIED BY :password;
  EXEC SQL DECLARE Salespeople CURSOR FOR
  SELECT Ename, Sal, Comm
  FROM Emp
  WHERE Job LIKE 'SALES%';
  EXEC SQL OPEN Salespeople;
  EXEC SQL WHENEVER NOT FOUND GOTO end_of_fetch;
  printf("\nSalesperson Salary Commission\n");
  printf("-------------- ------- --------------\n");
  for ( ; ; ) {
    EXEC SQL FETCH salespeople
    INTO :emp_name, :salary, :commission;
    emp_name.arr(emp_name.len) = '\0';
    printf("%-11s%9.2f%13.2f\n",emp_name.arr,salary,commission);
  }
end_of_fetch: EXEC SQL CLOSE salespeople;
EXEC SQL COMMIT WORK RELEASE;
exit(0);
SQLerror: printf("\n\n% .70s \n\n", SQLca.SQLerrm.SQLerrmc);
EXEC SQL WHENEVER SQLERROR CONTINUE;
EXEC SQL ROLLBACK WORK RELEASE;
exit(1);
}

5.8 Interfejs poziomu wywołań

Standard określa interfejs poziomu wywołań w postaci sygnatury biblioteki funkcji do wykonywania operacji na bazie danych. Rozważane języki programowania to Ada, C, COBOL, FORTRAN, MUMPS, Pascal i PL/I. Oto przykład w języku C wykonania dynamicznie tworzonej w programie zewnętrznym instrukcji SQL za pomocą wywołania funkcji o nazwie SQLExecDirect:

char sqlsource[255];
........
/* Wpisz tekst instrukcji SQL, którą chcesz wykonać, na zmienną sqlsource */
rc = SQLExecDirect(hstmt, (SQLCHAR *) sqlsource, SQL_NTS);

Oto spis czynności, które należy wykonać przy wykonywaniu instrukcji SQL za pomocą biblioteki funkcji OCI:

1. Przygotowanie instrukcji do wykonania - OCIStmtPrepare();
2. Wiązanie argumentów z parametrami - związać adres zmiennej wejściowej z każdym parametrem w instrukcji - OCIBindByPos(), OCIBindByName;
3. Wykonanie instrukcji (również instrukcji DDL) OCIStmtExecute();
4. Opisanie elementów wyniku (dla zapytań i gdy liczba tych elementów i ich typy danych nie są znane w czasie kompilacji programu) - PCIParamGet() i OCIAttrGet();
5. Definiowanie zmiennych wyjściowych (dla zapytań) - OCIDefineByPos();
6. Sprowadzanie wyniku zapytania - OCIStmtFetch().

Podstawowym pojęciem jest uchwyt (handle), który jest wskaźnikiem do struktury danych alokowanej i utrzymywanej przez bibliotekę OCI. Funkcje OCI mają dosyć skomplikowane sygnatury. Ich omówienie wykracza poza zakres tego wykładu. W charakterze przykładu podamy tylko wywołanie funkcji OCILogon, za pomocą którego nastąpi zalogowanie się do bazy danych określonej przez wartość zmiennej dbname na konto scott/tiger.

OCILogon(envhp, errhp, &svchp, "scott", nameLen, "tiger", passwdLen, dbname, dbnameLen)

• envhp - uchwyt do struktury danych środowiska;
• errrhp - uchwyt do struktury danych błędów;
• svchp - uchwyt do usługi (inicjalizowany przez samo OCILogon).

5.9 JDBC – interfejs poziomu wywołań z języka Java

W związku z popularnością języka Java w dziedzinie aplikacji bazodanowych, zostały opracowane dwie wersje dostępu do bazy danych z programów języka Java, w postaci:

1. języka osadzonego w Javie o nazwie SQLJ;
2. interfejsu poziomu wywołań z języka Java o nazwie JDBC.

Szczególnie interfejs JDBC stał się bardzo popularny i jest używany między innymi w:

1. programach aplikacyjnych napisanych w Javie po stronie klienta;
2. appletach, czyli małych programach Javy realizowanych na przeglądarce użytkownika;
3. serwletach, czyli programach Javy spełniających rolę małych serwerów aplikacyjnych, to jest programów realizujących po stronie serwera przez sieć zlecenia użytkowników;
4. procedurach i funkcjach składowanych w bazie danych.

JDBC jest zbiorem klas i interfejsów w Javie, które umożliwiają dostęp do bazy danych z programów napisanych w Javie. Klasy te i interfejsy są częścią pakietu java.sql. W JDBC sterowniki źródeł danych są zarządzane przez klasę DriverManager, która utrzymuje listę wszystkich aktualnie załadowanych sterowników. Sterownik jest programem, który tłumaczy wywołania JDBC na wywołania specyficzne dla serwera baz danych. Klasa DriverManager ma metody RegisterDriver, DeregisterDriver i GetDrivers, które umożliwiają dynamiczne dodawanie i usuwanie sterowników.

Przed podłączeniem się do odpowiedniego źródła danych należy załadować odpowiedni sterownik JDBC. Realizuje się to albo używając metody RegisterDriver

DriverManager.RegisterDriver(new oracle.jdbc.driver.OracleDriver());

Class.ForName("oracle.jdbc.driver.OracleDriver");

// połącz się z bazą danych
Connection con=DriverManager.getConnection(url, "scott","tiger");
Statement stmt = con.createStatement();
// zbuduj obiekt reprezentujący instrukcję SQL
String query = "SELECT Ename, Sal FROM Emp";
ResultSet rs = stmt.executeQuery(query);
try { // obsłuż wyjątki
  // przejdź po wszystkich wierszach wynikowych
  while (rs.next()) {
    String s = rs.getString("Ename");
    Int n = rs.getFloat("Sal");
    System.out.println(s + ": " + n);
  }
} catch(SQLException ex) {
  System.out.println(ex.getMessage () + ex.getSQLState () + ex.getErrorCode ());
}

url = "jdbc:oracle:thin:@xeon.pjwstk.waw.pl:1521:ORCL"

url = "jdbc:odbc:mojaBD"

5.10 Podsumowanie

Wykład ten dokonał przeglądu możliwości programowania w samej bazie danych jak i w bezpośrednim środowisku związanym z bazą danych (na przykładzie serwera Oracle).
 

5.11 Słownik pojęć

dynamiczny SQL - konstrukcje języka SQL umożliwiające przygotowywanie i wykonywanie instrukcji SQL w trybie dynamicznym tzn. w trakcie realizacji kodu programu.

DBMS_JOB - pakiet służący do zlecania serwerowi bazy danych określonych zadań poza sesją zlecającego użytkownika.

UTL_FILE - pakiet umożliwiający zapis i odczyt plików tekstowych z kodu PL/SQL wykonywanego przez serwer bazy danych.

biblioteka - obiekt reprezentujący zewnętrzną bibliotekę programów na serwerze Oracle.

Java na serwerze bazy danych - kod Javy może być zapisywany w bazie danych oraz może być realizowany na serwerze bazy danych tak jak kod języka SQL – w ramach tej samej sesji i tej samej transakcji.

osadzony SQL - metoda dostępu do bazy danych z programu zewnętrznego przez osadzanie instrukcji SQL wprost w tekście programu języka programowania.

interfejs poziomu wywołań - metoda dostępu do bazy danych z programu zewnętrznego przez użycie specjalnej biblioteki funkcji do wykonywania operacji na bazie danych.

JDBC - standardowy interfejs poziomu wywołań z języka Java.
 

5.12 Sprawdzenie wiedzy

5.13 Zadania

1. Zaznajom się z materiałem zawartym w materiałach Oracle (dostępne ze strony http://tahiti.oracle.com/):

1. PL/SQL User's Guide and Reference, w szczególności z:
   • 7. Performing SQL Operations with Native Dynamic SQL
   • 9. Using PL/SQL Packages
   • 11. Tuning PL/SQL Applications for Performance
2. Database Application Developer's Guide - Fundamentals, w szczególności z:
   • 8. Coding Dynamic SQL

2. Opracuj aplikację działającą po stronie serwera bazy danych, która wykorzystuje co najmniej dwa pakiety opisane w tym wykładzie.

Strona przygotowana przez Lecha Banachowskiego, PJWSTK, 08/06/06 .