Oracle 11g nowe cechy cz. 2 | PL/SQL - IT School - IT School

<span class=tytulTooltip>Szkolenie z podstaw programowania arkusza kalkulacyjnego.</span>
Szkolenie przeprowadzono rzetelnie i z ogromnym zaangażowaniem. Wiedza została przekazana w sposób profesjonalny i interesujący, a umiejętności zdobyte podczas kursu zostały wykorzystane w pracy zawodowej.
<span class=tytulTooltip>7 szkoleń zamkniętych z obsługi programu Microsoft Excel.</span> Zrealizowane przez IT School szkolenia spełniły nasze oczekiwania zarówno pod względem merytorycznym, jak i organizacyjnym. Zostały dopasowane do potrzeb firmy oraz oczekiwań poszczególnych uczestników.
<span class=tytulTooltip>Szkolenia z zakresu: Microsoft Excel VBA, Microsoft Access, Microsoft PowerPoint i SQL Oracle.</span> Szkolenia zostały przeprowadzone terminowo i z należytą starannością dostosowując proces dydaktyczny do możliwości percepcyjnych uczestników. Trenerzy wykazali się dużą wiedzą oraz ogromnym zaangażowaniem, co sprawiło, że szkolenia zostały bardzo dobrze ocenione przez uczestników.
<span class=tytulTooltip>Szkolenia z zakresu: Microsoft Excel VBA, Microsoft Access, Microsoft PowerPoint i SQL Oracle.</span> Szkolenia zostały przeprowadzone terminowo i z należytą starannością dostosowując proces dydaktyczny do możliwości percepcyjnych uczestników. Trenerzy wykazali się dużą wiedzą oraz ogromnym zaangażowaniem, co sprawiło, że szkolenia zostały bardzo dobrze ocenione przez uczestników.
<span class=tytulTooltip>Szkolenie z „Microsoft Excel - zaawansowana obsługa arkusza kalkulacyjnego” oraz „Microsoft Excel - podstawowa obsługa arkusza kalkulacyjnego”.</span> Podczas szkoleń trener potrafił w sposób interesujący przekazać posiadaną wiedzę, reprezentując wysoki poziom przygotowania merytorycznego podpartego własnym doświadczeniem. Cenimy sobie możliwość kontaktu poszkoleniowego z trenerem, który pomaga w rozwiązywaniu pojawiających się problemów.
<span class=tytulTooltip>Szkolenia z MS Project Professional 2010</span>Zajęcia szkoleniowe prowadzili trenerzy o wysokim poziomie kompetencji z bardzo dobrym przygotowaniem merytorycznym, potrafili przekazać wiedzę w sposób komunikatywny i interesujący dla uczestników. Powyższa opinia jest potwierdzona wynikami ankiet zadowolenia uczestników po szkoleniu. Chcielibyśmy również zwrócić uwagę na stronę organizacyjną zajęć. Firma wykazała się dużą elastycznością i otwartością w doborze terminów, trenerów oraz w kwestii przygotowania materiałów szkoleniowych, które cieszyły się dobrą opinią.
<span class=tytulTooltip>Szkolenia MS Excel oraz MS Access</span> Zawartość merytoryczna oraz sposób przeprowadzenia obu szkoleń zostały wysoko ocenione przez uczestników.
Polecamy współpracę w zakresie szkoleń z firmą IT School.
<span class=tytulTooltip>Cykl szkoleń dot. Microsoft Office Excel</span> Kursy przeprowadzono w sposób profesjonalny i rzetelny, a zdobyta wiedza podwyższyła kompetencje pracowników. Trenerzy wykazali się doświadczeniem, zaangażowaniem oraz ogromną cierpliwością, co spełniło wszelkie nasze oczekiwania .
Oracle 11g nowe cechy cz. 2 | PL/SQL

29265

GODZIN SZKOLEŃ

10034

PRZESZKOLONYCH OSÓB

3994

DNI NA RYNKU

PL/SQL

Podstawowym językiem tworzenia oprogramowania na bazie danych Oracle od zawsze był PL/SQL. Oracle 11g dostarcza kilka ciekawych rozwiązań, którymi warto się przyjrzeć rozważając migracje dotychczasowych aplikacji.

trigger following clause

Wiele razy w swojej karierze zmuszony byłem do aktualizacji bieżących rozwiązań opartych na triggerach ze względu na ewolucję procesów biznesowych aplikacji. Pociągało to za sobą konieczność modyfikacji kodu konkretnego triggera dla zachowania sekwencyjności wykonywanych operacji. Z punktu widzenia dbania o kod i utrzymywania go w należytym porządku oraz zgodnie ze wszelkimi dobrymi praktykami programowania należałoby wówczas stworzyć kolejny trigger przeznaczony do obsługi nowej funkcjonalności. W poprzednich wersjach bazy tego typu rozwiązanie było wykluczone ze względu na nieprzewidywalność kolejności uruchamiania dwóch takich samych triggerów. Baza danych Oracle 11g pozwala nam na wyspecyfikowanie klauzuli, w której możemy określić kolejność uruchamiania stworzonych triggerów.

Przykład:
CREATE OR REPLACE
TRIGGER TRIG_GEN_PASS
BEFORE INSERT ON EMPLOYEES
FOR EACH ROW
BEGIN
:new.default_pass := to_char(:new.hire_date, ‘YYYYMMDY’) || upper(substr(:new.first_name, 1, 2)) ||
initcap(substr(:new.last_name, 1, 2));
END;
/
CREATE OR REPLACE
TRIGGER TRIG_HASH_PASS
BEFORE INSERT ON EMPLOYEES
FOR EACH ROW
follows trig_gen_pass
BEGIN
:new.default_pass := DBMS_OBFUSCATION_TOOLKIT.MD5(INPUT_STRING=>:new.default_pass);
END;
/

W pierwszym triggerze zostało wygenerowane standardowe hasło nowo dodawanego pracownika. Trigger nr 2 natomiast zawiera prosty kod hashujący wygenerowane wcześniej hasło. W powyższym przykładzie moglibyśmy dodać między tymi dwoma triggerami jeszcze jeden (w środku), którego funkcjonalność polegałaby na przesłaniu e-mailem wygenerowanego hasła. Dzięki takiemu rozbiciu kodu mogę łatwiej zarządzać poszczególnymi etapami obsługi dodania nowego pracownika. Mogę również wyłączać poszczególne etapy („alter trigger disable clause”), dla celów deweloperskich, lub testowych.

Compound trigger

Jest to nowość w Oracle 11g, dla której bez problemu znajdzie zastosowanie każdy deweloper PL/SQL. Wiele razy, aby zaimplementować jakąś funkcjonalność była potrzeba stworzenia kilku triggerów – „before statement”, „after each row” i „after statement”. W dodatku, każda z faz zależała od poprzedniej w bezpośredni sposób. Jeżeli był wymagany przepływ danych między triggerami, musieliśmy kombinować ze zmiennymi pakietowymi lub innymi sposobami zachowywania spójności między poszczególnymi wywołaniami. Obecnie możemy stworzyć jeden trigger, w którym da się wyszczególnić określone sekcje: przed wykonaniem polecenia, przed wykonaniem poszczególnych wierszy, po wykonaniu poszczególnych wierszy, po wykonaniu polecenia no i sekcja deklaracyjna triggera. Tego typu triggery mogą działać oczywiście tylko przy poleceniach DML. Na pierwszy rzut oka widać jednak ich przydatność – konstrukcja pozwala poprawić wydajność implementacji określonych funkcjonalności, jak również usprawnia zarządzanie i obsługę kodu.

Przykład:
CREATE OR REPLACE TRIGGER Check_Employee_Salary_Raise
FOR UPDATE OF Salary ON Employees
COMPOUND TRIGGER
Ten_Percent CONSTANT NUMBER := 0.1;
TYPE Salaries_t IS TABLE OF Employees.Salary%TYPE;
Avg_Salaries Salaries_t;
TYPE Department_IDs_t IS TABLE OF Employees.Department_ID%TYPE;
Department_IDs Department_IDs_t;
TYPE Department_Salaries_t IS TABLE OF Employees.Salary%TYPE
INDEX BY VARCHAR2(80);
Department_Avg_Salaries Department_Salaries_t;
BEFORE STATEMENT IS
BEGIN
SELECT AVG(e.Salary), NVL(e.Department_ID, -1)
BULK COLLECT INTO Avg_Salaries, Department_IDs
FROM Employees e
GROUP BY e.Department_ID;
FOR j IN 1..Department_IDs.COUNT() LOOP
Department_Avg_Salaries(Department_IDs(j)) := Avg_Salaries(j);
END LOOP;
END BEFORE STATEMENT;
AFTER EACH ROW IS
BEGIN
IF :NEW.Salary – :Old.Salary >
Ten_Percent*Department_Avg_Salaries(:NEW.Department_ID)
THEN
Raise_Application_Error(-20000, ‘Raise too big’);
END IF;
END AFTER EACH ROW;
END Check_Employee_Salary_Raise;

Powyższy przykład został zaczerpnięty z dokumentacji do bazy Oracle11g. Widać na nim użycie compound trigger w celu uniknięcia mutowania tabeli – nie możemy dać podwyżki pracownikowi, jeśli przekracza ona 10% wartości średnich zarobków w departamencie, w którym pracuje.

Function Result Cache

Funkcjonalność ta jest przydatna, gdy odwołujemy się często do funkcji, która zwraca zazwyczaj ten sam wynik, dla tych samych parametrów wejściowych. Możemy określić, żeby wynik funkcji został zapisany w buforze, dzięki temu każde kolejne wywołanie funkcji z określonymi parametrami będzie pobierało wynik z bufora. Parametr „relies_on” określa tabele, z których korzysta funkcja w swoim ciele – jeżeli zawartość tabeli ulegnie zmianie, zbuforowana zawartość ulegnie inwalidacji. Dokładniejszy opis działania bufora „Result Cache” zostanie opisana w późniejszej części tego dokumentu. Jeżeli chcemy użyć w funkcji opcji „function result cache”, musimy trzymać się określonych restrykcji:

  • Funkcja nie może być wywoływana z prawami wywołującego
  • Funkcja nie może być użyta w bloku anonimowym
  • Funkcja nie może posiadać parametrów OUT
  • Funkcja nie może przyjmować parametrów złożonych
  • Funkcja nie może zwracać złożonego typu danych
Przykład:
CREATE OR REPLACE
FUNCTION F_GET_STATUS(p_dept_name departments.department_name%TYPE) RETURN VARCHAR2
result_cache relies_on(employees, departments)
as
v_sal employees.salary%TYPE;
v_ret VARCHAR2(3);
BEGIN
select avg(salary) into v_sal
from employees e, departments d
where e.department_id= d.department_id
and d.department_name= p_dept_name;
if v_sal > 8600 then
v_ret := ‘YES’;
elsif v_sal is null then
v_ret := ‘NOT’;
raise no_data_found;
else
v_ret := ‘NO’;
end if;
return v_ret;
exception
when no_data_found then
dbms_output.put_line(‘Nie ma takiego departmentu jak ‘ || p_dept_name);
return v_ret;
END F_GET_STATUS;
/
SQL> select f_get_status(‘IT’)
from dual;
2
F_GET_STATUS(‘IT’)
————————–
NO
SQL> select status
2 from v$result_cache_objects
3 where name like ‘%F_GET_STATUS%’
4 and TYPE=’Result’
5 /
STATUS
———
Published
SQL> update departments
2 set department_name=initcap(department_name);
27 wierszy zostalo zmodyfikowanych.
SQL> commit;
Zatwierdzanie zostalo ukonczone.
SQL> select status
2 from v$result_cache_objects
3 where name like ‘%F_GET_STATUS%’
4 and TYPE=’Result’
5 /
STATUS
———
Invalid

Jak widać w powyższym przykładzie, zaraz po odwołaniu się do funkcji otrzymaliśmy opublikowany wynik w naszym buforze, z którego będzie korzystało każde następne wywołanie funkcji z takim samym parametrem wejściowym. Jednak po modyfikacji tabeli, na której oparta była funkcja (wskazane przez relies_on), nasz zbuforowany wynik uległ inwalidacji – dzięki temu mamy pewność otrzymywania wiarygodnych danych. Zupełnie inaczej ma się sprawa, gdy nie użyjemy klauzuli relies_on – w takim
przypadku możemy otrzymać nieprawdziwy wynik, ponieważ Oracle nie będzie miał informacji o zależności zbuforowanego wyniku funkcji od zawartości tabel, na których opierają się nasze wyliczenia.

Wyrażenia regularne

Wyrażenia regularne stanowią bardzo potężne narzędzie w rękach programisty. Szczególnie istotne jest ich zastosowanie podczas wyrafinowanych opcji przeszukiwania ciągów znakowych lub walidacji danych. Obsługa wyrażań regularnych została dodana do bazy Oracle w wersji 10g. W wersji 11g została ona rozszerzona o nowe funkcjonalności – do funkcji REGEXP_INSTR została dodana możliwość znalezienia Ntego sub wyrażenia. Znajdziemy też zupełnie nową funkcję REGEXP_COUNT, która oczywiście jak nie trudno się domyśleć, pozwala na zliczenie trafień na podstawie zadanego wzorca.

„Continue” oraz „simple_integer”

Jest to jedna z opcji, która została dodana do składni PL/SQL po wielu latach… Zdecydowana większość języków programowania posiada tę klauzulę, która pozawala na kontynuowanie pętli nadrzędnej przy spełnieniu określonych warunków.

Przykład:
CREATE OR REPLACE
PROCEDURE P_CONT_EXAMPLE AS
v_sum simple_integer := 0;
BEGIN
< < BEFORE_LOOP >  >
for i in (select department_id, department_name from departments) loop
v_sum := 0;
for j in (select salary from employees where department_id=i.department_id) loop
v_sum := v_sum + j.salary;
if v_sum>19000 then
dbms_output.put_line(i.department_name || ‘ ‘ || v_sum);
continue BEFORE_LOOP;
end if;
end loop;
end loop;
END P_CONT_EXAMPLE;

Przy okazji trywialnego przykładu użycia klauzuli continue zdecydowałem się pokazać również nowy typ danych dodany do języka PL/SQL. Simple_integer jest pod typem, typu pls_integer – ma dokładnie taki sam zakres jak pls_integer, różnica polega jednak na tym, że gdy dojdziemy do końca zakresu typu simple_integer nastąpi „przekręcenie” wartości zmiennej do najmniejszej wartości zakresu. Simple_integer jest również zadeklarowany jako not null, wymaga więc zainicjalizowania wartością, już na etapie deklaracji w naszym programie.

Dynamiczny SQL

Oracle ulepszył obsługę dynamicznego SQL zarówno za pomocą pakietu DBMS_SQL, jak również natywnego dynamicznego SQL (execute immediate). Oto kilka rozszerzeń dodanych do obsługi tej przydatnej funkcjonalności:

  • Natywny dynamiczny SQL może przyjmować teraz 32kB kodu.
  • DBMS_SQL.PARSE zostało przeciążone dla używania CLOB.
  • DBMS_SQL wspiera teraz abstrakcyjne typy danych, takie jak kolekcje.
  • DBMS_SQL pozwala używać mechanizmu BULK BINDING używając typów zdefiniowanych przez użytkownika.
Natywna kompilacja

Możliwość natywnej kompilacji procedur PL/SQL, była moim zdaniem zawsze jednym z największych plusów bazy Oracle. Możliwość przechowywania procedur w postaci bibliotek języka C dawała przecież zawsze nadzieję, na znacznie szybsze wykonanie kodu, zwłaszcza opartego na dużej liczbie obliczeń. Jednak wielokrotnie korzystanie z tego mechanizmu było ograniczone na wielu środowiskach. Wynikało to z faktu, że do tej pory skompilowany kod PL/SQL do postaci biblioteki języka C przechowywany był na serwerze, a co za tym szło wymagany był kompilator C dostępny dla właściciela bazy danych. Takie rozwiązanie z punktu widzenia bezpieczeństwa było niedopuszczalne na pewnych środowiskach. Tak więc funkcjonalność bardzo często pozostawała niewykorzystana. Oracle 11g ulepszył obsługę natywnej kompilacji PL/SQL – począwszy od tej wersji bazy danych biblioteki C nie są przetrzymywane na serwerze lecz bezpośrednio w bazie danych, co likwiduje konieczność dostępu do kompilatora C.

Przykład:
SQL> alter session set plsql_code_type=NATIVE;
Sesja zostala zmieniona.
SQL> alter function f_get_status compile;
Funkcja zostala zmieniona.
SQL> select plsql_code_type
2 from DBA_PLSQL_OBJECT_SETTINGS
3 where name=’F_GET_STATUS’;
PLSQL_CODE_TYPE
——————————————————————————–
NATIVE

Jak widać na powyższym przykładzie, aby skompilować procedurę w trybie NATIVE, wystarczy ustawić jeden parametr sesji, oraz wykonać kompilację. Powyższa procedura, nie powoduje powstania żadnych plików na serwerze. Wszystko pozostaje wewnątrz bazy danych.

Kosmetyka

W PL/SQL zobaczymy również kilka poprawek kosmetycznych, które może nie wpływają jakoś znacznie na poprawę wydajności lub funkcjonalności systemu, ale na pewno są miłym ukłonem w stronę użytkownika. Tego typu poprawkami jest np. możliwość używania notacji mieszanej podczas wywoływania funkcji w wyrażeniu SQL, czy też opcja zastosowania przypisania „wprost” wartości sekwencji.