Anna Zatwarnicka

Bazy danych
IV rok informatyki
studia dzienne

Procedury ("solo" i w pakietach) oraz procedury składowane w Javie

1. Procedury składowane  napisane w PL/SQL-u
a. Znacie to Państwo z poprzedniego semestru. A jeżeli przypadkiem zdążyliście zapomnieć - coż.... najwyższa pora odświerzyć sobie wiadomości:

SQL> CREATE OR REPLACE PROCEDURE ......

Literatura: dowolne książki z Oracle'a.

b. Przypominam, że procedura komunikuje się ze światem za pomocą: parametrów (dane wejściowe do procedury), komunikacja z użytkownikiem odbywa się za pomocą wyświetlanych na ekranie komunikatów (dbms_output.put_line()). By wykonać niektóre operacje na danych w tabelach, konieczne staje się użycie kursorów. Można (często trzeba!) używać zmiennych.
Procedury można łączyć w pakiety (package).

c. Tak napisane procedury można wywołać (i wykonać) w środowisku SQLPLUS. I to jest pierwsze zadanie, jakie macie Państwo wykonać: napisac dowolną (sensowną!!!) procedurę w PL/SQL-u i wywołać ją w tym środowisku.

d. Gdybyście natomiast Państwo chcieli tak napisaną procedurę "normalnie" wywołać w aplikacji Javy/JSP to.... będzie kłopot. Ani przy wykorzystywaniu Statement, ani preparedStatement (chyba że poprzez SELECT).
Zaradzić temu może wywołanie procedury (samej, lub zawartej w pakiecie) w nastepujący sposób:

W programie należy miec obiekt klasy Connection (to Państwo już wiecie), i na rzecz tego obiektu trzeba wywołać metodę Javy prepareCall(). Wywołanie tegoż, tworzy tzw. instancję obiektu CallableStatement.

Do tej pory pisaliscie Państwo (wersja najprostrza):

//tak było w poprzednich projektach - teraz nie!!!!
Connection conn;
Statement stmt;
stmt = conn.executeQuery(....);
//tak było w poprzednich projektach - teraz nie!!!!

Do wywołania procedur składowanych należy użyć:

Connection conn; //czyli połączenie
CallableStatement wyrazenie_do_proc_skladowanej = conn.prepareCall();

Jako parametr procedury prepareCall() będzie żądanie zdalego wywołania procedury w PL/SQL-u,czyli:

{call nazwaProcedury(?,?,?)}

i wszystko razem:

Connection conn;
CallableStatement wyrazenie_do_proc_skladowanej = conn.prepareCall("{call nazwaProcedury(?,?,?)}");

Parametry wejściowe procedury składowanej
Czyli takie, które odpowiadają parametron IN w procedurach PL/SQL-a. Państwo, którzy w poprzednich projektach używali obiektów klasy PreparedStatement znaja już owe dziwnie wyglądajace pytajniki. Są to parametry wywołania - parametry wejściowe. Ustawia się je z wykorzystaniem metod setInt(), setString(), setFloat() i innych; muszą być wywołane na rzecz obiektu klasy CallableStatement (u nas ten obiekt to: wyrazenie_do_proc_skladowanej). Przykład:

wyrazenie_do_proc_skladowanej.setInt(numer_parametru, wartość_parametru);
wyrazenie_do_proc_skladowanej.setString(numer_parametru, wartość_parametru);

Przykład szczegółowy:

wyrazenie_do_proc_skladowanej.setInt(1, 5);
wyrazenie_do_proc_skladowanej.setString(2, "AlaMaKota");

Parametry wyjściowe procedury składowanej
Tak, procedura może mieć również parametry wyjściowe....Są to parametry, które procedura dostaje na wejście, zmienia w trakcie swojego działania i które nastęnie przekazuje na wyjście, gdy konczy swoje działanie. NALEŻY UŻYWAĆ ICH WTEDY, GDY W PROCEDURZE PL/SQL-owej MACIE PAŃSTWO PARAMETRY WYJŚCIOWE (WŁAŚNIE OUT)!!! Można wykorzystać te parametry wyjściowe do "przechwycenia" tego, co procedura PL/SQL-owa miałaby wypisywać na ekranie: to, co wypisywałaby na ekranie należy (w procedurze PL/SQL) przypisać do zmiennej typu łańcuchowego, która jest parametrem wyjściowym (OUT). Przy wywołaniu procedury trzeba tam, co prawada wpisać cośkolwiek, ale jak procedura się wykona, można pobrać wartość tego parametru i już z procedury Javy wypisać. Przydatne metody: na rzecz obiektu ResultStatement metoda next() - już ja Państwo wykorzystywaliscie, oraz metody GetTypZmiennej(), np. GetString(), GetInt().
Powiązanie z tzw. instancją obiektu dokonywane jest za pomoca metody registerOutParameter() na rzecz obiektu klasy CallableStatement dla każdego parametru!!!. Przykład ogólny:

wyrazenie_do_proc_skladowanej.registerOutParameter(nr_parametru, typ_Oracle);

Przykłady szczegółowe:

wyrazenie_do_proc_skladowanej.registerOutParameter(3, OracleTypes.CHAR);
wyrazenie_do_proc_skladowanej.registerOutParameter(1, OracleTypes.NUMBER); //itp...

lub (na naszej Javie):

wyrazenie_do_proc_skladowanej.registerOutParameter(1, java.sql.Types.INTEGER);

lub:
najpierw zaimportować pakiet java.sql.*,
a potem używać już tylko:

wyrazenie_do_proc_skladowanej.registerOutParameter(1, Types.INTEGER);

Jeżeli już jest obiekt CallableStatemet można wywołać procedurę za pomocą executeUpdate() wywołaną na rzecz obiektu CallableStatement. Przykład:

wyrazenie_do_proc_skladowanej.executeUpdate();

Pobranie uprzednio wprowadzonych parametrów za pomocą getInt(), getString() itp.

TERMIN OSTATECZNY ZALICZENIA TEJ CZĘŚCI - OSTATNIE ZAJĘCIA!

2. Składowane procedury Javy (ang. Java stored procedures)
Java jest wbudowana w Oracle'a i przez to znacznie zwiększone są możliwości programistów, którzy mogą wykorzystywać to, co Java oferuje: całe mnóstwo róznorodnych klas. Znacznie poprawić można w ten sposób chociażby pobieranie informacji od użytkownika: można zbadać, czy podał dane odpowiedniego typu i odpowiedniej wielkości (wiecie Państwo, jak to zrobić, było na zajęciach wstępnych z Javy).

Żeby było ciekawiej(!), procedury w Javie można napisać na dwa sposoby:

Sposób 1 (nie zawsze skuteczny):
a. Najpierw utworzyć (w jakimś edytorze w shellu) klasę, która zawiera potrzebne własności i metody (czyli: funkcjonalność)
Następnie skompilować tę klasę i załadować ją do bazy danych.

public class MojaKlasaZProceduramiWbudowanymi
{
//pierwsza procedura wbudowana
public static String SklejDane(String imie, String nazwisko)
{
    String dane = imie+nazwisko;
    return dane;
}
//druga procedura wbudowana
public static int ObliczWiek (int DataUrodzenia)
{
    return 2004 - DataUrodzenia;
}
}//klasa

b. Tak napisaną procedurę należy skompilować:

javac MojaKlasaZProceduramiWbudowanymi.java

c. Następnie załadować do bazy danych (w shellu):
- za pomocą polecenia loadjava

loadjava -user uzytkownik/haslo MojaKlasaZProceduramiWbudowanymi.class

- w wyniku działania instrukcji CREATE JAVA (w środowisku SQLPLUS'a - troszkę bardziej pogmartwane)
tworzymy alias dysku:

SQL> CREATE OR REPLACE DIRECTORY dysk_c AS 'c:\';

i umieszczamy tam plik Javy:

SQL> CREATE OR REPLACE JAVA CLASS USING
   2    BFILE (dysk_c, 'MojaKlasaZProceduramiWbudowanymi.class')
   3   /

Sposób 2 (zalecany!):
W środowisku SQLPLUS Oracle'a można pisać  również procedury w języku Java.
Nie wymaga to tworzenia pliku z definicją klasy.

SQL> CREATE OR REPLACE JAVA SOURCE NAMED "MojaKlasaZProceduramiWbudowanymi" AS
   2  public class MojaKlasaZProceduramiWbudowanymi
   3  {
   4     public static String SklejDane(....)
   5      {....}
   .....
   9  } //klasa
   10 /

Co dalej?
Dla tak napisanych procedur nalezy jeszcze stworzyć tzw. osłony (ang. wrappers) w PL/SQL:

CREATE OR REPLACE FUNCTION Proc_ObliczWiek(int DataUrodzenia)
RETURN NUMBER AS LANGUAGE JAVA
NAME 'MojaKlasaZProceduramiWbudowanymi.ObliczWiek(int DataUrodzenia) return int';

Wywołanie (nareszcie!):

- Z poziomu SQLPLUS-a:
Tak, jak zwykłe procedury napisane w PL/SQL-u (co i tak wygląda nieco... dziwnie):

SQL> SELECT Proc_ObliczWiek(1990) FROM dual;
... i tu bedzie wynik działania tej procedury ...

- Z poziomu aplikacji Javy/JSP:

Sposób 1.
Składowane procedury Javy moga być wywoływane na rzecz obiektu klasy PreparedStatement. Tak samo, jak  są wywoływane instrukcje wprowadzania danych do bazy (czyli tak, jak Państwo robiliście poprzednio przy okazji wywoływania instrukcji Oracle'a INSERT INTO. ALE TYLKO WTEDY, GDY WYWOŁUJECIE JE PAŃSTWO ZA POMOCA SELECT!!!!!!!

Przykład ogólny:

PreparedStatement wyrazenie = conn.prepareStatement("select procedura(parametr) from dual");

Sposób 2.
Składowane procedury Javy są wywoływane za pomca metody prepareCall() obiektu klasy Connection - dokładnie jak w punkcie 1 d. Różnica: właściwie nie wywołujecie Państwo procedury, lecz funkcję, która jest osłoną dla Waszej procedury.

Przykład ogólny:

CallableStatement wyrazenie = conn.prepareCall("{call osłona_procedury_Javy(?,?)}");

Przykład szczegółowy:

CallableStatement wyrazenie = conn.prepareCall("{call osłona_procedury_Javy(?,?)}");
wyrazenie.setString(...);





Pytania dla dociekliwych:
1. Proszę pamiętać o parametrach wyjściowych!
2. Te procedury Javy (metody) zwracaja pewne wartości! Co z tym zrobić?

TERMIN OSTATECZNY ZALICZENIA TEJ CZĘŚCI - OSTATNIE ZAJĘCIA!

Hmmm... czy coś tu nie tak?
Być może zauważyli Państwo (oby!), że te procedury Javy nie maja nic wspólnego z bazą danych.
Java w procedurach służy przeważnie do obliczeń i sprawdzeń, jeżeli istnieje potrzeba wykonania w procedurach Javy zapytań do bazy danych, robi się to za pomoca SQLJ. Tego Państwo ćwiczyć nie będziecie (nie było na wykładach), ale dobrze, żebyście wiedzieli że takie cóś istnieje i do czego to służy.
To jest bardzo ciekawe - pętelka: PL/SQL (Oracle)-Java-Oracle(SQLJ)...

3. Podsumowując:

TERMIN OSTATECZNY ZALICZENIA TEJ CZĘŚCI - OSTATNIE ZAJĘCIA!

Państwa praca składać się będzie z:
- przykładowej procedury napisanej w PL/SQL-u wywołanej w środowisku Oracle
- przykładowej procedury napisanej w PL/SQL-u wywołanej w aplikacji Javy/JSP
- przykładowej procedury napisanej w Javie (na Oracle'u) wywołanej w środowisku Oracle
- przykładowej procedury napisanej w Javie (na Oracle'u) wywołanej w aplikacji Javy/JSP

NIE TRZEBA PAMIĘTAĆ O PANI KRYSI! wyjątkowo.

Należy uważnie poczytać materiały, być może wspomóc się literaturą (wyszukiwarki polecam!).

TERMIN OSTATECZNY ZALICZENIA TEJ CZĘŚCI - OSTATNIE ZAJĘCIA!

4. Materiały pomocnicze:

  Książki w moim posiadaniu:
 TERMIN OSTATECZNY ZALICZENIA TEJ CZĘŚCI - OSTATNIE ZAJĘCIA!

opracowała: Anna Zatwarnicka