Wartość NULL oznacza , ze dane pole w bazie danych lub zmienna w kodzie PL/SQL nie zostały zainicjowane... O ile powszechnie przy porównaniach stosowane są operatory IS NULL lub IS NOT NULL, inne ciekawsze przypadki nie zawsze są prawidłowo obsługiwane.
Zwróćmy uwagę że wartości NULL nie są porównywalne między sobą ani tez z innymi niepustymi wartościami... Oracle wg mnie powinien potraktować użycie NULL w warunkach po klauzuli WHERE przy operatorach porównujących jako błąd , wszelkie warunki typu a = NULL, a < NULL.
Dla operatorów arytmetycznych jest przyjęta zasada, jeśli co najmniej jeden argument IS NULL, to wartość też jest NULL. Dlatego zapytanie
SELECT 1 + NULL FROM dual zwróci wynik NULL
a zapytanie
SELECT NULL/0 FROM dual zwróci też NULL bez generowania wyjątku.
Zatem też poniższe zapytanie nie zwróci wyjątku, jeżeli co najmniej jeden ze składników sumy w mianowniku będzie niezainicjowany, mimo, że pozostałe będą sumować się do 0
SELECT (a+b+c+d) / e FROM tabela
jeśli którekolwiek z pól bazie miało wartość NULL, wynik będzie także NULL.
W wyrażeniach tekstowych należy zwrócić uwagę, że pusty tekst traktowany jest jako niezainicjowana wartość, co więcej długość takiego pola wynosi NULL, a nie zero
SELECT NVL(LENGTH(''), -1) FROM DUAL --zwraca -1
Zatem wszystkie poniższe trzy zapytania nie zwrócą żadnego rekordu, w każdym mamy porównanie względem wartości NULL
SELECT * FROM HR.EMPLOYEES WHERE LAST_NAME = ''
SELECT * FROM HR.EMPLOYEES WHERE LENGTH(LAST_NAME) =0
SELECT * FROM HR.EMPLOYEES WHERE LENGTH(LAST_NAME) < 1
Poniższe zapytanie nie pokaże rekordów, dla których pole COMMISSION_PCT ma wartość NULL
SELECT * FROM HR.EMPLOYEES WHERE COMMISSION_PCT IN (0.1, 0.2, NULL)
Prawidłowe wygląda następująco
SELECT * FROM HR.EMPLOYEES WHERE COMMISSION_PCT IN (0.1, 0.2) OR COMMISSION_PCT IS NULL
Z operatorem NOT IN sprawa wygląda ciekawiej, jeśli jedna wartość na liście jest pusta, to zapytanie nic nie zwróci
SELECT * FROM HR.EMPLOYEES WHERE COMMISSION_PCT NOT IN (0.1, 0.2, NULL)
Prawidłowe wygląda następująco
SELECT * FROM HR.EMPLOYEES WHERE COMMISSION_PCT NOT IN (0.1, 0.2) AND COMMISSION_PCT IS NOT NULL
Zgodnie z powyższą zasadą to zapytanie też nie zwróci rekordów - NULL został przemycony w pustym tekście
SELECT * FROM HR.EMPLOYEES WHERE LAST_NAME NOT IN ('Lorentz', '')
Blog programisty w Oracle PL/SQL
Jest to blog eksperymentatora programisty w PL/SQL dla Oracle. Wszystkie kody tutaj zamieszczone mogą być dowolnie wykorzystywane i zmieniane. A jeśli Ktoś z Gości znajdzie błąd, będę niezwykle wdzięczny...
Zapisz
Zapisz
Szukaj na tym blogu
niedziela, 2 kwietnia 2017
wtorek, 31 stycznia 2017
Ściągawka dla Kolegi - dyrektora handlowego w pewnej firmie IT - zastosowanie złączeń kartezjańskich
Zainspirowany bardzo bogatym korposłownictwem z zakresu prezentacji handlowych rozwiązań i usług postanowiłem napisać ściągawkę do wykorzystania przez przesympatycznego prelegenta. Przykład też pokazuje w jaki sposób zamienić elementy listy na kolejne wiersze w strukturze zbliżonej do tabeli. Świadome pominięcie warunków złączenia ma na celu wygenerowanie wszystkich możliwych połączeń bardzo profesjonalnych sformułowań..
SELECT TABLE_1.COLUMN_VALUE || ' ' ||
TABLE_2.COLUMN_VALUE || ' ' ||
TABLE_3.COLUMN_VALUE || ' ' ||
TABLE_4.COLUMN_VALUE || ' ' ||
TABLE_5.COLUMN_VALUE || ' ' ||
TABLE_6.COLUMN_VALUE || ' ' ||
TABLE_7.COLUMN_VALUE
FROM
TABLE(SYS.ODCIVARCHAR2LIST('Przestrzeganie najwyższych standardów podczas ',
'Ciągłe doskonalenie procesu',
'Wieloletnie doświadczenie w zakresie',
'Unikalny zbiór kompetencji naszego zespołu w zakresie')) TABLE_1,
TABLE(SYS.ODCIVARCHAR2LIST( 'analizy', 'tworzenia', 'projektowania', 'testowania', 'wdrażania' )) TABLE_2,
TABLE(SYS.ODCIVARCHAR2LIST( 'skalowalnych ', 'wydajnych', 'niezawodnych' , 'optymalnie wspierających biznes') ) TABLE_3,
TABLE(SYS.ODCIVARCHAR2LIST( 'aplikacji', 'systemów', 'kompleksowych rozwiązań') ) TABLE_4,
TABLE(SYS.ODCIVARCHAR2LIST( 'w największych bankach', 'w kluczowych organach administracji państwowej', 'w największych zakładach produkcyjnych', 'w największych sieciach handlowych') ) TABLE_5,
TABLE(SYS.ODCIVARCHAR2LIST( 'potwierdza', 'zapewnia', 'trafnie weryfikuje') ) TABLE_6,
TABLE(SYS.ODCIVARCHAR2LIST( 'wybór naszej firmy jako gwarancję sukcesu ',
'korzyści z długofalowej współpracy', 'terminowe wdrożenie systemu', 'uzyskanie przewagi technologiczną dzięki naszej firmie') ) TABLE_7
SELECT TABLE_1.COLUMN_VALUE || ' ' ||
TABLE_2.COLUMN_VALUE || ' ' ||
TABLE_3.COLUMN_VALUE || ' ' ||
TABLE_4.COLUMN_VALUE || ' ' ||
TABLE_5.COLUMN_VALUE || ' ' ||
TABLE_6.COLUMN_VALUE || ' ' ||
TABLE_7.COLUMN_VALUE
FROM
TABLE(SYS.ODCIVARCHAR2LIST('Przestrzeganie najwyższych standardów podczas ',
'Ciągłe doskonalenie procesu',
'Wieloletnie doświadczenie w zakresie',
'Unikalny zbiór kompetencji naszego zespołu w zakresie')) TABLE_1,
TABLE(SYS.ODCIVARCHAR2LIST( 'analizy', 'tworzenia', 'projektowania', 'testowania', 'wdrażania' )) TABLE_2,
TABLE(SYS.ODCIVARCHAR2LIST( 'skalowalnych ', 'wydajnych', 'niezawodnych' , 'optymalnie wspierających biznes') ) TABLE_3,
TABLE(SYS.ODCIVARCHAR2LIST( 'aplikacji', 'systemów', 'kompleksowych rozwiązań') ) TABLE_4,
TABLE(SYS.ODCIVARCHAR2LIST( 'w największych bankach', 'w kluczowych organach administracji państwowej', 'w największych zakładach produkcyjnych', 'w największych sieciach handlowych') ) TABLE_5,
TABLE(SYS.ODCIVARCHAR2LIST( 'potwierdza', 'zapewnia', 'trafnie weryfikuje') ) TABLE_6,
TABLE(SYS.ODCIVARCHAR2LIST( 'wybór naszej firmy jako gwarancję sukcesu ',
'korzyści z długofalowej współpracy', 'terminowe wdrożenie systemu', 'uzyskanie przewagi technologiczną dzięki naszej firmie') ) TABLE_7
środa, 19 października 2016
Pseudominizacja numeru PESEL - przykład rozwiązania
Problem pseudominizacjidanych występuje często kiedy dane z systemów zawierających dane osobowe lub dane wrażliwe musimy udostępnić osobom trzecim, ukrywając te dane i możliwość identyfikacji osób
Założenia algorytmu:
PCKG_ANONYMIZE_DATA.PDB_CLEARINTERNALDATA;
END;
Jak to działa??
Poniższe zapytanie generuje nowy PESEL dla Jarosława K.
SELECT PCKG_ANONYMIZE_DATA.FDB_GenerateNewPESEL('49061804592') FROM DUAL
Otrzymamy 49061899990
Wywołajmy teraz funkcję 5 razy
SELECT PCKG_ANONYMIZE_DATA.FDB_GenerateNewPESEL('49061804592') FROM DUAL
CONNECT BY LEVEL <= 5
Jako wynik otrzymamy
49061899976
49061899952
49061899938
49061899914
49061899891
Czyli widzimy, że dla kolejnych nr PESELw obrębie daty urodzenia generowane są kolejne nowe PESELE, ale nie jest sprawdzana unikalność nr PESEL na wejściu..
Czy nowe nr PESEL zapewniają pełną pseudonimizację ze względu na ten nr. Nie.., Jeśli np wiemy , ze w bazie było dwóch klientów w wieku po 80 lat, to możemy z dużym prawdopodobieństwem ich zidentyfikować, jeśli maja różne daty urodzenia. Dla lat, w których jest dużo klientów w poszczególnych dniach będzie na podstawie tylko nr PESEL niemożliwe
CREATE OR REPLACE PACKAGE PCKG_ANONYMIZE_DATA IS
PROCEDURE PDB_ClearInternalData;
FUNCTION FDB_GenerateNewPESEL(pc_PESEL VARCHAR2) RETURN VARCHAR2 ;
PRAGMA RESTRICT_REFERENCES (FDB_GenerateNewPESEL, WNDS);
END;
/
CREATE OR REPLACE PACKAGE BODY PCKG_ANONYMIZE_DATA
IS
TYPE T_NUMBERS IS TABLE OF VARCHAR2 (4)
INDEX BY VARCHAR2 (6);
Vt_Numbers T_NUMBERS;
cn_DayIndex CONSTANT NUMBER (4) := 9999;
/************************************************************************/
PROCEDURE PDB_ClearInternalData
IS
BEGIN
Vt_Numbers.DELETE;
END;
/************************************************************************/
FUNCTION FDB_GenerateNewPESEL (pc_PESEL VARCHAR2 )
RETURN VARCHAR2
IS
TYPE t_Weights IS VARRAY (10) OF SIMPLE_INTEGER;
vt_Weights t_weights
:= t_weights (1,3,7,9,1,3,7,9,1,3);
vn_CheckSum SIMPLE_INTEGER := 0;
vc_PESEL VARCHAR2 (15);
vc_DayIndex NUMBER (4) := '0';
vn_Plec NUMBER (1);
BEGIN
vc_Pesel := SUBSTR (pc_PESEL, 1, 6);
vn_Plec := SUBSTR (pc_PESEL, 10, 1);
--------------------
IF vt_Numbers.exists(vc_PESEL ) THEN
vc_DayIndex := NVL (Vt_Numbers (vc_Pesel) - 1, cn_DayIndex);
ELSE
vc_DayIndex := cn_DayIndex;
END IF;
--------------------
IF MOD (vc_DayIndex, 2) <> MOD (vn_Plec, 2) THEN
vc_DayIndex := vc_DayIndex - 1;
END IF;
--------------------
Vt_Numbers (vc_Pesel) := vc_DayIndex;
vc_Pesel :=vc_Pesel || LPAD (TO_CHAR (vc_DayIndex), 4, '0');
--------------------
FOR i IN 1 .. vt_Weights.COUNT
LOOP
vn_CheckSum :=
vn_CheckSum
+ TO_NUMBER (SUBSTR (vc_PESEL, i, 1)) * vt_Weights (i);
END LOOP;
--------------------
IF MOD (vn_CheckSum, 10) = 0 THEN vc_PESEL := vc_PESEL || '0';
ELSE
vc_PESEL := vc_PESEL || (10 - MOD (vn_CheckSum, 10));
END IF;
--------------------
RETURN vc_Pesel;
/*EXCEPTION WHEN OTHERS THEN
RETURN NULL;*/
END;
/*******************************************************************/
BEGIN
Vt_Numbers.DELETE;
END;
/
Założenia algorytmu:
- nowy PESEL dla danej osoby zachowuje informacje o płci i o dacie urodzenia
- numer ewidencyjny w obrębie daty urodzenia jest numerowany od stałej cn_DayIndex w dół
- zakłada się, że parametry są poprawne - parametr pc_PESEL nie jest walidowany
- na wejściu algorytmu powinien być zbiór SELECT DISTINCT PESEL FROM....
- przed wykonaniem anonimizacji należy wykonać blok:
PCKG_ANONYMIZE_DATA.PDB_CLEARINTERNALDATA;
END;
Jak to działa??
Poniższe zapytanie generuje nowy PESEL dla Jarosława K.
SELECT PCKG_ANONYMIZE_DATA.FDB_GenerateNewPESEL('49061804592') FROM DUAL
Otrzymamy 49061899990
Wywołajmy teraz funkcję 5 razy
SELECT PCKG_ANONYMIZE_DATA.FDB_GenerateNewPESEL('49061804592') FROM DUAL
CONNECT BY LEVEL <= 5
Jako wynik otrzymamy
49061899976
49061899952
49061899938
49061899914
49061899891
Czyli widzimy, że dla kolejnych nr PESELw obrębie daty urodzenia generowane są kolejne nowe PESELE, ale nie jest sprawdzana unikalność nr PESEL na wejściu..
Czy nowe nr PESEL zapewniają pełną pseudonimizację ze względu na ten nr. Nie.., Jeśli np wiemy , ze w bazie było dwóch klientów w wieku po 80 lat, to możemy z dużym prawdopodobieństwem ich zidentyfikować, jeśli maja różne daty urodzenia. Dla lat, w których jest dużo klientów w poszczególnych dniach będzie na podstawie tylko nr PESEL niemożliwe
CREATE OR REPLACE PACKAGE PCKG_ANONYMIZE_DATA IS
PROCEDURE PDB_ClearInternalData;
FUNCTION FDB_GenerateNewPESEL(pc_PESEL VARCHAR2) RETURN VARCHAR2 ;
PRAGMA RESTRICT_REFERENCES (FDB_GenerateNewPESEL, WNDS);
END;
/
CREATE OR REPLACE PACKAGE BODY PCKG_ANONYMIZE_DATA
IS
TYPE T_NUMBERS IS TABLE OF VARCHAR2 (4)
INDEX BY VARCHAR2 (6);
Vt_Numbers T_NUMBERS;
cn_DayIndex CONSTANT NUMBER (4) := 9999;
/************************************************************************/
PROCEDURE PDB_ClearInternalData
IS
BEGIN
Vt_Numbers.DELETE;
END;
/************************************************************************/
FUNCTION FDB_GenerateNewPESEL (pc_PESEL VARCHAR2 )
RETURN VARCHAR2
IS
TYPE t_Weights IS VARRAY (10) OF SIMPLE_INTEGER;
vt_Weights t_weights
:= t_weights (1,3,7,9,1,3,7,9,1,3);
vn_CheckSum SIMPLE_INTEGER := 0;
vc_PESEL VARCHAR2 (15);
vc_DayIndex NUMBER (4) := '0';
vn_Plec NUMBER (1);
BEGIN
vc_Pesel := SUBSTR (pc_PESEL, 1, 6);
vn_Plec := SUBSTR (pc_PESEL, 10, 1);
--------------------
IF vt_Numbers.exists(vc_PESEL ) THEN
vc_DayIndex := NVL (Vt_Numbers (vc_Pesel) - 1, cn_DayIndex);
ELSE
vc_DayIndex := cn_DayIndex;
END IF;
--------------------
IF MOD (vc_DayIndex, 2) <> MOD (vn_Plec, 2) THEN
vc_DayIndex := vc_DayIndex - 1;
END IF;
--------------------
Vt_Numbers (vc_Pesel) := vc_DayIndex;
vc_Pesel :=vc_Pesel || LPAD (TO_CHAR (vc_DayIndex), 4, '0');
--------------------
FOR i IN 1 .. vt_Weights.COUNT
LOOP
vn_CheckSum :=
vn_CheckSum
+ TO_NUMBER (SUBSTR (vc_PESEL, i, 1)) * vt_Weights (i);
END LOOP;
--------------------
IF MOD (vn_CheckSum, 10) = 0 THEN vc_PESEL := vc_PESEL || '0';
ELSE
vc_PESEL := vc_PESEL || (10 - MOD (vn_CheckSum, 10));
END IF;
--------------------
RETURN vc_Pesel;
/*EXCEPTION WHEN OTHERS THEN
RETURN NULL;*/
END;
/*******************************************************************/
BEGIN
Vt_Numbers.DELETE;
END;
/
piątek, 1 lipca 2016
Obliczanie liczby pi w SQL
Kolejnym zastosowaniem klauzuli MODEL będzie napisanie prostego zapytania wyliczającego liczbę pi ( ludolfinę ) z zadaną dokładnością z wykorzystaniem algorytmu Leibniza

Zapytanie zwraca cztery kolumny, druga to wartość liczby PI obliczona z funkcji arcus sinus, trzecia
to przybliżona wartość pi obliczona ze wzoru Leibniza, czwarta to moduł różnicy pomiędzy kolumną drugą a trzecią. Zwiększając dokładność o kolejne rzędy wielkości odczujemy istotne wydłużenie działania zapytania. Iterowanie zakończy się w momencie spełnienia jednego z warunków: osiągniemy zakładaną dokładność lub liczba iteracji osiągnie wartość umieszczona w nawiasie po słowie kluczowym ITERATE .
Jak możemy się łatwo przekonać obserwując ostatnia kolumnę, jest to algorytm wolnozbieżny, jego zastosowanie ma tylko charakter edukacyjny, aby pokazać możliwość obsługi ciągów liczbowych przez iteracyjną klauzulę MODEL ..
SELECT
d + 1 "Nr iteracji",
PI_ASIN "Pi z funkcji asin",
4 *PI_COMPUTED "Wartość obliczona",
DIFF "Różnica"
FROM (SELECT 0 d FROM DUAL)
MODEL
DIMENSION BY ( 0 d)
MEASURES (
CAST( 0 AS NUMBER) PI_ASIN,
CAST( 0 AS NUMBER) PI_COMPUTED,
CAST( 0 AS NUMBER) DIFF )
RULES
ITERATE (1000000000) UNTIL (ITERATION_NUMBER >1 AND DIFF [ITERATION_NUMBER ] < 0.01)
(
PI_ASIN[ ITERATION_NUMBER ] =ASIN(1)*2,
PI_COMPUTED[ ITERATION_NUMBER] =
CASE ITERATION_NUMBER
WHEN 0 THEN 1
ELSE
PI_COMPUTED[ ITERATION_NUMBER -1 ] + POWER( -1, ITERATION_NUMBER) / (2*ITERATION_NUMBER +1)
END,
DIFF [ ITERATION_NUMBER] = ABS(4*PI_COMPUTED[ ITERATION_NUMBER ] - PI_ASIN[ITERATION_NUMBER] ))
Zapytanie zwraca cztery kolumny, druga to wartość liczby PI obliczona z funkcji arcus sinus, trzecia
to przybliżona wartość pi obliczona ze wzoru Leibniza, czwarta to moduł różnicy pomiędzy kolumną drugą a trzecią. Zwiększając dokładność o kolejne rzędy wielkości odczujemy istotne wydłużenie działania zapytania. Iterowanie zakończy się w momencie spełnienia jednego z warunków: osiągniemy zakładaną dokładność lub liczba iteracji osiągnie wartość umieszczona w nawiasie po słowie kluczowym ITERATE .
Jak możemy się łatwo przekonać obserwując ostatnia kolumnę, jest to algorytm wolnozbieżny, jego zastosowanie ma tylko charakter edukacyjny, aby pokazać możliwość obsługi ciągów liczbowych przez iteracyjną klauzulę MODEL ..
SELECT
d + 1 "Nr iteracji",
PI_ASIN "Pi z funkcji asin",
4 *PI_COMPUTED "Wartość obliczona",
DIFF "Różnica"
FROM (SELECT 0 d FROM DUAL)
MODEL
DIMENSION BY ( 0 d)
MEASURES (
CAST( 0 AS NUMBER) PI_ASIN,
CAST( 0 AS NUMBER) PI_COMPUTED,
CAST( 0 AS NUMBER) DIFF )
RULES
ITERATE (1000000000) UNTIL (ITERATION_NUMBER >1 AND DIFF [ITERATION_NUMBER ] < 0.01)
(
PI_ASIN[ ITERATION_NUMBER ] =ASIN(1)*2,
PI_COMPUTED[ ITERATION_NUMBER] =
CASE ITERATION_NUMBER
WHEN 0 THEN 1
ELSE
PI_COMPUTED[ ITERATION_NUMBER -1 ] + POWER( -1, ITERATION_NUMBER) / (2*ITERATION_NUMBER +1)
END,
DIFF [ ITERATION_NUMBER] = ABS(4*PI_COMPUTED[ ITERATION_NUMBER ] - PI_ASIN[ITERATION_NUMBER] ))
wtorek, 21 czerwca 2016
Wyszukiwanie kodów błędów Oracle w tekście
Poniższe zapytanie ma za zadanie wyszukiwać w wejściowym tekście różnych kodów błędów Oracle. Kody błędów wyszukiwane są za pomocą wyrażeń regularnych. Użycie funkcji uprościło zapytanie i umożliwiło wykonanie DISTINCT
WITH FUNCTION PARSE(ARG VARCHAR2) RETURN VARCHAR2 AS
vc_Wynik VARCHAR2(100 CHAR);
BEGIN
SELECT LISTAGG(BLAD, ',') WITHIN GROUP( ORDER BY 1) INTO vc_Wynik
FROM
(SELECT DISTINCT REGEXP_SUBSTR( arg, 'ORA[[:punct:]][[:digit:]]{4,5}',1, LEVEL) BLAD FROM DUAL
CONNECT BY LEVEL <= REGEXP_COUNT( arg, 'ORA[[:punct:]][[:digit:]]{4,5}'));
RETURN vc_Wynik;
END;
SELECT PARSE( 'pierwszy ORA-33344 Drugi
ORA-2342 Trzeci ORA ORA-66666--') FROM DUAL
Subskrybuj:
Posty (Atom)