Standardowe funkcje SQL w usłudze ArcGIS Online

Usługa ArcGIS Online wymaga od deweloperów korzystania ze standardowych zapytań SQL podczas wykonywania zapytań dotyczących hostowanych warstw obiektowych. Użycie standardowych funkcji zapytań SQL pomaga w zapobieganiu atakom polegającym na wprowadzaniu kodu SQL. Wszystkie aplikacje ArcGIS obsługują standardowe zapytania SQL.

Ograniczenia związane z zapytaniami standardowymi

  • Korzystanie z zapytań standardowych jest konfigurowane dla całej instytucji, nie można go wyłączyć dla niektórych warstw.
  • Standardowe zapytania nie są obsługiwane dla połączeń między różnymi przestrzeniami roboczymi.
  • Zapytania podrzędne w postaci klauzuli WHERE, na przykład POP_2010 = (SELECT min(POP_2010) FROM counties, nie są obsługiwane.
  • Zapytania usług baz danych, na przykład Microsoft Azure SQL Database, nie są obsługiwane.

Funkcje SQL, które są obsługiwane w usłudze ArcGIS Online

Jeśli jesteś deweloperem aplikacji obecnie używającym składni klauzuli WHERE specyficznej dla określonej bazy danych, musisz zaktualizować klauzule WHERE w kodzie aplikacji, tak aby zastosowana została standardowa składnia SQL obsługiwana w usłudze ArcGIS Online. Poniższe tabele zawierają obsługiwane funkcje SQL oraz składnię każdej z nich. Gdy następujące funkcje i składnie są używane w Twoich aplikacjach, usługa ArcGIS Online przekształca je, aby były zgodne z wymogami bazy danych używanej przez warstwę obiektową.

Funkcje daty

FunkcjaOpis

CURRENT_DATE()

Zwraca bieżącą datę jako czas UTC.

Wyświetlana wartość zależy od używanego klienta. W usłudze ArcGIS Online daty są wyświetlane zgodnie ze strefą czasową danej instytucji lub profilu.

CURRENT_TIME()

Zwraca datę i czas w formacie UTC (w godzinach, minutach i sekundach).

Wyświetlana wartość zależy od używanego klienta. W usłudze ArcGIS Online czas jest wyświetlany jako czas lokalny danej instytucji lub profilu.

CURRENT_TIMESTAMP()

Zwraca datę i czas w formacie UTC (w godzinach, minutach, sekundach i milisekundach).

Wyświetlana wartość zależy od używanego klienta. W usłudze ArcGIS Online czas jest wyświetlany jako czas lokalny danej instytucji lub profilu.

EXTRACT(<unit> FROM <date>)

Zwraca jedną część (<unit>) podanej wartości <date>. Możliwe wartości <unit> to na przykład year, month, day, hour i minute.

W poniższych przykładach wyodrębniane są różne jednostki z wartość daty i godziny 21.12.2016 15:11:

  • EXTRACT(MONTH FROM TIMESTAMP '2016-12-21 15:11:00') — zwraca 12.
  • EXTRACT(DAY FROM TIMESTAMP '2016-12-21 15:11:00') — zwraca 21.
  • EXTRACT(HOUR FROM TIMESTAMP '2016-12-21 15:11:00') — zwraca 15.

Funkcje liczbowe

FunkcjaOpis

ABS(<number>)

Zwraca wartość bezwzględną (dodatnią) podanej liczby.

CAST(<number> AS FLOAT | INT)

Przekształca liczbę na liczbę innego typu. FLOAT przekształca podaną liczbę na liczbę zmiennoprzecinkową podwójnej precyzji, natomiast funkcja INT przekształca ją na liczbę całkowitą.

W pierwszym przykładzie poniżej liczba jest rzutowana na liczbę całkowitą. Z tego względu wynikiem jest 1424. W drugim przykładzie liczba całkowita jest rzutowana na liczbę zmiennoprzecinkową, a wynikiem jest liczba z miejscami po przecinku: 1424,0

  • CAST(1424.49 AS INT)
  • CAST(1424 AS FLOAT

CEILING(<number>)

Zwraca najmniejszą liczbę całkowitą większą od podanej lub jej równą.

W następującym przykładzie zwracana jest wartość 13:

CEILING(12.93)

COS(<number>)

Zwraca wartość funkcji trygonometrycznej cosinus liczby <number>. Przyjmuje się, że liczba jest kątem wyrażonym w radianach.

FLOOR(<number>)

Zwraca największą liczbę całkowitą równą podanej liczbie lub od niej mniejszą.

W następującym przykładzie zwracana jest wartość 12.

FLOOR(12.93)

LOG(<number>)

Logarytm naturalny podanej liczby.

LOG10(<number>)

Logarytm dziesiętny podanej liczby.

MOD(<number>, <n>)

Zwraca resztę z dzielenia dzielnej (<liczba>) przez dzielnik <n>. Zarówno parametr <n>, jak i <liczba> muszą być liczbami całkowitymi.

Przykłady:

  • MOD(10, 4) — wynik wynosi 2.
  • MOD(CAST(DBLFIELD AS INT), 4)DBLFIELD to pole typu liczba zmiennoprzecinkowa podwójnej precyzji, dlatego konieczne jest zastosowanie funkcji CAST, która przekształca liczbę zmiennoprzecinkową podwójnej precyzji na liczbę całkowitą.

NULLIF(<number>, <value>)

Zwraca null, jeśli podana liczba jest równa podanej wartości. Funkcja NULLIF jest często stosowana, aby zapobiec wystąpieniu błędu dzielenia przez zero przez ustawienie wartości <value> równej 0.

Za każdym razem, gdy podczas wykonywania obliczeń napotykane jest pole o wartości null będące argumentem, wynikiem obliczenia jest wartość null.

Na przykład: użytkownik chce obliczyć wartość pola zmiennoprzecinkowego podwójnej precyzji jako TOTALPOP podzielone przez POP18. Jeżeli dla jakiegokolwiek obiektu wartość pola POP18 jest równa zero, obliczenie nie zostanie wykonane i wyświetlony zostanie komunikat o błędzie dzielenia przez zero. Można również utworzyć filtr umożliwiający ukrycie rekordów, w których wartość pola POP18 jest równa zero, a następnie wykonać odpowiednie obliczenia. Zastosowanie funkcji NULLIF jest szybszą metodą.

TOTALPOP / NULLIF(POP18, 0) — zwraca null, jeśli POP18 jest równe zero. W przeciwnym razie zwracana jest wartość TOTALPOP / POP18.

POWER(<number> , <y>)

Zwraca wartość podanej liczby podniesionej do podanej potęgi (<y>).

W następującym przykładzie zwracana jest wartość 32 768.

POWER(8,5)

ROUND(<number> , <length>)

Zaokrągla podaną liczbę do podanej liczby miejsc po przecinku.

Jeżeli wartość <length> jest dodatnia, liczba zostaje zaokrąglona do podanej liczby miejsc po prawej stronie przecinka dziesiętnego. W przypadku gdy liczba <length> jest ujemna, podana wartość <number> jest zaokrąglana po lewej stronie przecinka dziesiętnego.

Poniżej podano przykłady:

  • ROUND(10.9934,2) — zwraca 10,99.
  • ROUND(10.9964,2) — zwraca 11,00.
  • ROUND(111.0,-2) — zwraca 100,00.

SIN(<number>)

Zwraca wartość funkcji trygonometrycznej sinus dla podanej liczby <number>. Przyjmuje się, że liczba ta jest kątem wyrażonym w radianach.

TAN(<number>)

Zwraca wartość funkcji trygonometrycznej tangens dla podanej liczby <number>. Przyjmuje się, że liczba ta jest kątem wyrażonym w radianach.

TRUNCATE(<number>,<decimal_place>)

Przycina wartość <number> do podanej liczby <decimal_place>.

Jeśli <decimal_place> jest dodatnia, wartość zostaje przycięta do podanej liczby miejsc po przecinku. W przypadku gdy liczba <decimal_place> jest ujemna, wartość <number> jest przycinana po lewej stronie przecinka dziesiętnego.

W pierwszym przykładzie obcinane są cyfry po prawej stronie przecinka dziesiętnego, tak aby uwzględniane były tylko dwie cyfry, w związku z czym wyświetlana jest wartość 111,99. W drugim przykładzie obcinane są cyfry z lewej strony przecinka dziesiętnego, w związku z czym wyświetlana jest wartość 100,00.

  • TRUNCATE(111.996,2)
  • TRUNCATE(111.996,-2)

Funkcje ciągów znakowych

FunkcjaOpis

CAST(<string> AS DATE | TIME)

Przekształca ciąg znakowy na datę lub czas, jeśli wartość ciągu znakowego jest w obsługiwanym formacie.

Jeśli ciąg znakowy jest w formacie 'MM/DD/RRRR hh:mm:ss' lub 'RRRR-MM-DD', można przekształcić go w datę. Jeśli ciąg znakowy jest w formacie 'HH:MM:SS', można przekształcić go w typ danych obsługujący tylko czas (TIME).

Na przykład następujący ciąg znakowy można przekształcić w datę:

CAST('1988-05-30' AS DATE)

CHAR_LENGTH(<string>)

Zwraca liczbę znaków w podanym ciągu znakowym. Wynik ma postać liczby całkowitej.

Na przykład następująca instrukcja zwraca wartość 8:

CHAR_LENGTH('Redlands')

CONCAT(<string1>, <string2>)

Łączy dwie wartości ciągu znakowego.

Można podać tylko dwa ciągi. Aby połączyć więcej niż dwa ciągi znakowe, należy zagnieździć kilka funkcji CONCAT, jak przedstawiono poniżej.

W pierwszym przykładzie poniżej łączone są litery A i B. W drugim przykładzie przedstawiono zagnieżdżoną funkcję CONCAT, która łączy trzy wartości znakowe: A, : i B.

  • CONCAT('A', 'B') — wynik wynosi 'AB'.
  • CONCAT('A', CONCAT(':', 'B')) — wynik wynosi 'A:B'.

Wartości puste są przekształcane do pustych ciągów znakowych.

CURRENT_USER

Gdy w zapytaniu jest zawarta funkcja CURRENT_USER., działa ona podobnie do zmiennej; nazwa użytkownika uzyskującego dostęp do hostowanej warstwy obiektowej lub widoku hostowanej warstwy obiektowej jest identyfikowana i używana w zapytaniu.

Jeśli na przykład użytkownik planner3 jest użytkownikiem zalogowanym w instytucji, aby uzyskać dostęp do widoku hostowanej warstwy obiektowej zawierającego następującą definicję zapytania, tylko obiekty, dla których pole staffmember zawiera wartość planner3, są zwracane do użytkownika nawiązującego połączenie:

staffmember=current_user

W następnym przykładzie w polu staffmember jest przechowywane wiele wartości. Następująca klauzula where powoduje znalezienie nazwy bieżącego użytkownika w wartości tekstowej w polu staffmember, nawet jeśli w tym polu przechowywane jest wiele nazw użytkowników:

where=position(current_user in staffmember)>0

POSITION(<substring>, <string>)

Zwraca pozycję pierwszego wystąpienia podanego podciągu w podanym ciągu znakowym. Jeżeli podany podciąg nie zostanie znaleziony, wynik wynosi 0.

W pierwszym przykładzie poniżej wynikiem jest 5, ponieważ pierwsza litera (b) podciągu (boat) jest piątą literą w ciągu znakowym (Sailboat). W drugim przykładzie wynikiem jest 0, ponieważ podciąg (motor) nie występuje w ciągu znakowym.

  • POSITION('boat', 'Sailboat')
  • POSITION('motor', 'Sailboat')

SUBSTRING(<string>, <start>, <length>)

Zwraca część wartości ciągu znakowego; <start> to indeks liczby całkowitej określający, gdzie rozpoczynają się zwracane znaki, a <length> jest liczbą zwracanych znaków.

Zobacz następujące przykłady:

  • SUBSTRING('Sailboat', 5, 4) — wynik wynosi 'boat'.
  • SUBSTRING('Sailboat', 1, 4) — wynik wynosi 'Sail'.
  • SUBSTRING('Sailboat', 5, 100) — wynik wynosi 'boat'.

TRIM(BOTH | LEADING | TRAILING ' ' FROM <string>)

Zwraca ciąg znakowy, który jest podanym ciągiem z usuniętymi wszystkimi spacjami początkowymi i końcowymi.

W poniższym przykładzie przed i po ciągu znakowym San Bernardino znajduje się spacja. Słowo kluczowe BOTH jest używane do usuwania ciągu spacji (wskazywanego za pomocą dwóch pojedynczych cudzysłowów ze spacją między nimi) na początku i końcu ciągu tekstowego:

TRIM(BOTH ' ' FROM ' San Bernardino ')

Zwracany jest ciąg znakowy 'San Bernardino'.

UPPER(<string>)

Zwraca ciąg znakowy, w którym wszystkie znaki są zapisane wielkimi literami.

W tym przykładzie wszystkie litery w ciągu znakowym Sailboat są przekształcane w wielkie litery, czego wynikiem jest ciąg znakowy 'SAILBOAT':

UPPER('Sailboat')

LOWER(<string>)

Zwraca ciąg znakowy, w którym wszystkie znaki są zapisane małymi literami.

W następnym przykładzie zwracany jest ciąg znakowy 'sailboat':

LOWER('Sailboat')